存储过程

*   Oracle版本
    *   select * from tab;

    *   create table t1(id number, name varchar2(10), sex varchar2(10));

    *   select * from t1;

    *   --创建存储过程，循环添加大量是数据
    *   create or replace procedure p_name_sex(in name1 varchar,sex1 varchar,name2 varchar,sex2 varchar)
    *   as
    *   begin
    *   for i in 1..1000 loop
    *   if i mod 2 = 1 then
    *   insert into t1 values(i,name1,sex1);
    *   else
    *   insert into t1 values(i,name2,sex2);
    *   end if;
    *   end loop;
    *   commit;
    *   dbms_output.put_line('数据插入成功！');
    *   end;

    *   --调用存储过程
    *   call p_name_sex('张三','男','李四','女');

    *   select * from t1;

    *   --创建函数
    *   create or replace function f_add(a number,b number) return number
    *   is sum1 number;
    *   begin
    *   sum1 :=a+b;
    *   return sum1;
    *   end;

    *   --调用函数
    *   select f_add(3,8) from dual;

*   Mysql版本
    *   create table test.t1 (id int(4),name varchar(20));

    *   DELIMITER //

    *   CREATE PROCEDURE proc4(OUT s int)
        *   begin
            *   select COUNT(*) into s from TEST.t1;
            *   insert into test.t1 values(s,'Derek');
        *   end
        *   //

    *   DELIMITER ;

    *   CALL proc4(@s);

    *   select * from test.t1;

    *   [图片上传失败...(image-a8c216-1543978607039)]

    *   --------

    *   利用存储过程，循环添加大量数据

        [图片上传失败...(image-d08b49-1543978607039)]

    *   *   #创建测试表
        *   DROP TABLE IF EXISTS test.t4;

        *   CREATE TABLE test.t4(
        *   id int(10),
        *   name varchar(20)
        *   )ENGINE INNODB DEFAULT CHARSET utf8 COMMENT '测试表';

        *   select * from test.t4;

        *   #清空数据
        *   TRUNCATE table test.t4;

        *   DROP PROCEDURE IF EXISTS myproc;

        *   ##循环添加大量数据
        *   delimiter //

        *   create procedure myproc()
            *   begin
                *   declare num int;
                *   set num=10;
                *   while num < 20 do
                    *   insert into t4(id, name) values(num,concat("tag", num));
                    *   set num=num+1;
                *   end while;
            *   end
        *   //

        *   delimiter ;

        *   #调用存储过程
        *   call myproc();
            *   *   select * from t4;
    *   存储过程，while 循环

        [图片上传失败...(image-675a9c-1543978607039)]

    *   存储过程 while 循环，if 条件判断

        [图片上传失败...(image-8423eb-1543978607039)]

    *   存储过程，case

        [图片上传失败...(image-cdf194-1543978607039)]

        [图片上传失败...(image-fa9fcd-1543978607039)]

        *   DELIMITER //

        *   CREATE OR REPLACE PROCEDURE p5(IN p_id INT)
        *   BEGIN
            *   DECLARE pp_id INT ;
            *   SET pp_id=p_id+1;
            *   CASE pp_id
                *   WHEN 1 THEN
                    *   INSERT INTO test.t3(id) VALUE ('11') ;
                *   WHEN 2 THEN
                    *   INSERT INTO test.t3(id) VALUE ('12') ;
                *   ELSE
                    *   INSERT INTO test.t3(id) VALUE ('13') ;
            *   END CASE;
        *   END;
        *   //

        *   DELIMITER ;

        *   CALL p5(0);

        *   SELECT * FROM test.t3 ;
    *   循环 loop-leave loop_lable

        [图片上传失败...(image-16264-1543978607039)]

        *   DELIMITER //

        *   CREATE OR REPLACE PROCEDURE p8()
        *   BEGIN
            *   DECLARE X INT ;
            *   SET X=60 ;
            *   lo:LOOP
                *   INSERT INTO test.t3(id) VALUE (X) ;
                *   SET X=X+1 ;
                *   IF X>=70 THEN
                    *   LEAVE lo ;
                *   END IF;
            *   END LOOP;
        *   END;
        *   //

        *   DELIMITER ;

        *   CALL p8();

        *   SELECT * FROM test.t3 ;
    *   循环 repeat until end repeat;
        *   DELIMITER //

        *   CREATE OR REPLACE PROCEDURE p9()
        *   BEGIN
            *   DECLARE v INT DEFAULT 80;
            *   REPEAT
                *   INSERT INTO test.t3(id) VALUE (v) ;
                *   SET v=v+1 ;
                *   UNTIL v>=85
            *   END REPEAT;
        *   END;
        *   //

        *   DELIMITER ;

        *   CALL p9();

        *   SELECT * FROM test.t3 ;
    *   复制整张表
        *   DELIMITER //

        *   CREATE OR REPLACE PROCEDURE p3()
        *   BEGIN
            *   CREATE TABLE test.t3 AS SELECT * FROM ranzhi.sys_user;
        *   END//

        *   DELIMITER ;

        *   CALL p3();

        *   SELECT * FROM test.t3 ;
    *   存储过程，传参 in

        [图片上传失败...(image-61d251-1543978607039)]

        *   INSERT INTO test.t3(id,account) VALUE ('2','zhangsan2');
        *   INSERT INTO test.t3(id,account) VALUE ('3','zhangsan3');
        *   INSERT INTO test.t3(id,account) VALUE ('4','zhangsan4');
        *   INSERT INTO test.t3(id,account) VALUE ('5','zhangsan5');
        *   INSERT INTO test.t3(id,account) VALUE ('6','zhangsan6');

        *   DELIMITER $$

        *   CREATE OR REPLACE PROCEDURE p4(IN p_id INT)
        *   BEGIN
        *   DELETE FROM test.t3 WHERE id = p_id ;
        *   END$$

        *   DELIMITER ;

        *   CALL p4(3);

        *   SELECT * FROM test.t3 ;
    *   变量
        *   1\. 变量定义
            *   局部变量声明一定要放在存储过程体的开始：
            *   DECLAREvariable_name [,variable_name...] datatype [DEFAULT value];
                *   DECLARE l_int int unsigned default 4000000;
                *   DECLARE l_numeric number(8,2) DEFAULT 9.95; DECLARE l_date date DEFAULT '1999-12-31';
                *   DECLARE l_datetime datetime DEFAULT '1999-12-31 23:59:59';
                *   DECLARE l_varchar varchar(255) DEFAULT 'This will not be padded';
        *   2\. 变量赋值
            *   SET 变量名 = 表达式值 [,variable_name = expression ...]
        *   3\. 用户变量
            *   在MySQL客户端使用用户变量:

                [图片上传失败...(image-abbf05-1543978607037)]