mysql存储过程

创建表：

CREATE TABLE t(s1 INT);

/*示例一*/

CREATE PROCEDURE p7()
BEGIN
  SET @a=5;
  SET @b=5;
  INSERT INTO t VALUES(@a);
  SELECT s1*@a FROM t WHERE s1>=@b;
END

调用

CALL p7();
SELECT @a;
SELECT @b;

结果：

25
5
5

/*示例2*/

CREATE PROCEDURE p8()
BEGIN
  DECLARE a INT;
  DECLARE b int;
  SET a=5;
  SET b=5;
  INSERT INTO t VALUES(a);
  SELECT s1*a FROM t WHERE s1>=b;
END

调用

CALL p8();

/*示例3*/

CREATE PROCEDURE p9()
BEGIN
  DECLARE a,b INT DEFAULT 5;
  INSERT INTO t VALUES(a);
  SELECT s1*a FROM t WHERE s1>=b;
END

/*示例4*/

CREATE PROCEDURE p11()
BEGIN
  DECLARE x1 char(5) DEFAULT 'outer';
  BEGIN
    DECLARE x1 CHAR(5) DEFAULT 'inner';
    SELECT x1;
  END;
SELECT x1;
END;

调用

CALL p11();

结果顺序：先内后外

inner
outer

/*示例5*/

CREATE PROCEDURE p12(IN parameter INT)
BEGIN
  DECLARE variable1 INT;
  SET variable1=parameter1+1;
  IF variable1=0 THEN
    INSERT INTO t VALUES(17);
  END IF;
  IF parameter1=0 THEN
    UPDATE t SET s1=s1+1;
  ELSE
    UPDATE t SET s1=s1+2;
  END IF;
END;

调用：

CALL p12(0);
SELECT * FROM t;

/*示例6*/

CREATE PROCEDURE p13(IN parameter1 INT)
BEGIN
  DECLARE variable1 INT;
  SET variable1=parameter1+1;
  CASE variable1
  WHEN 0 THEN INSERT into t VALUES (17);
  WHEN 1 THEN INSERT INTO t VALUES (18);
  ELSE INSERT INTO t VALUES (19);
  END CASE;
END;

调用：

CALL p13(1);
SELECT * FROM t;

/*示例7*/
/*while执行前验证是否合法*/

CREATE PROCEDURE p14()
BEGIN
  DECLARE v INT;
  SET v=0; /*这里一定要初始化，否则默认是null,NULL与任何值运算都为null*/
  WHILE v<5 DO
    INSERT INTO t VALUES(v);
  SET v=v+1;
END;

/*示例8*/
/*REPEAT循环,先执行后判断*/

CREATE PROCEDURE p15()
BEGIN
  DECLARE v INT;
  SET v=0;
  REPEAT
    INSERT INTO t VALUES(v);
    SET v=v+1;
  UNTIL v>=5;
  END REPEAT;
END;

/*示例9*/
/*LOOP循环，其中leave是退出循环*/

CREATE PROCEDURE p15()
BEGIN
  DECLARE v INT;
  SET v=0;
  loop_lable:LOOP /*loop_lable是LOOP标号*/
    INSERT INTO t VALUES(v);
    SET v=v+1;
    IF v>=5 THEN
      LEAVE loop_lable;
    END IF;
  END LOOP;
END;

/*示例10*/
/*leave*/

CREATE PROCEDURE p19(parameter CHAR)
  lable_1:BEGIN
    lable_2:BEGIN
      lable_3:BEGIN
         IF parameter1 IS NOT NULL THEN
           IF parameter1='a' THEN
             SELECT parameter1;
             LEAVE lable_1;
           ELSE BEGIN
              IF parameter1='b' THEN
                SELECT parameter1;
                LEAVE lable_2;
              ELSE
                SELECT parameter1;
                LEAVE lable_3;
              END IF;
            END;
          END IF;
        END IF;
      END;
    END;
END;

/*示例11*/
/*迭代iterate*/

CREATE PROCEDURE p20()
BEGIN
  DECLARE v INT;
  SET v=0;
  loop_lable:LOOP
    IF v=3 THEN
      SET v=v+1;
      ITERATE loop_lable;/*ITERATE类似于continue*/
    END IF;
    INSERT INTO t VALUES (v);
    SET v=v+1;
    IF v>=5 THEN
      LEAVE loop_lable;
     END IF;
   END LOOP;
END;

/*示例12，异常处理*/
/*主表，包括主键*/

CREATE TABLE t2(s1 INT,PRIMARY KEY(s1)) ENGINE=INNODB;

/*从表，包括外键*/

CREATE TABLE t3(s1 INT,KEY(s1),FOREIGN KEY (s1) REFERENCES t2(s1)) ENGINE=INNODB;

 

INSERT INTO t3 VALUES(5);

/*报错，原因主表t2中无s1=5的值*/
[Err] 1452 - Cannot add or update a child row: a foreign key constraint fails (`test`.`t3`, CONSTRAINT `t3_ibfk_1` FOREIGN KEY (`s1`) REFERENCES `t2` (`s1`))

/*创建错误日志*/

CREATE TABLE error_log(error_message CHAR(80));

/*错误日志入库，通过存储过程*/

CREATE PROCEDURE p22(parameter1 INT)
BEGIN
  DECLARE EXIT HANDLER FOR 1452
  INSERT INTO error_log VALUES (CONCAT('Time:',CURRENT_DATE,'.Foreign key Reference Failure For Value=',parameter1));
  INSERT INTO t3 VALUES(parameter1);
END;

调用：

CALL p22(5);

调用插入处理，出现异常，在异常表出入数据，并不在控制台显示错误信息。

/*示例13，异常处理*/
/*CONTINUE 异常处理，遇到错误后继续进行下面的错误*/

CREATE TABLE t4(s1 INT,PRIMARY KEY(s1));

/*创建存储过程*/

CREATE PROCEDURE p23()
BEGIN
  DECLARE CONTINUE HANDLER FOR SQLSTATE '23000' SET @x2=1;
  SET @x=1;
  INSERT INTO t4 VALUES(1);
  SET @x2=2;
  INSERT INTO t4 VALUES(1);
  SET @x=3;
END;

/*示例14，异常处理*/
/*示例14，异常处理*/

CREATE PROCEDURE p24()
BEGIN
  DECLARE Constraint_Violation CONDITION FOR SQLSTATE '23000';
  DECLARE EXIT HANDLER FOR Constraint_Violation ROLLBACK;
  START TRANSACTION;
  INSERT INTO t2 VALUES(1);
  INSERT INTO t2 VALUES(1);
  COMMIT;
END;

 综合示例：

drop procedure if exists insertPerson;#如果存在储存过程则删除
 #delimiter $$ #创建一个储存过程
 create procedure insertPerson()
 begin
  # @Job_Number; 工号
  # @Person_Name; 姓名
  # @Sex; 性别
  # @BU_ID; 部门
  # @Position_Code; 职位
  # @PositionGrade_Code; 职级
  # @ResType_Code; 资源类型
  #@JoinIn_Date; 入职时间
  # @Dimission_Date; 离职时间
  # @Create_Time; 创建时间',
  # @Org_ID; 组织编码',
  # @Status1; 人员状态（0:删除，1：正常，2：被削减，3：离职）',
  # @Verify_Flag; 人员信息验证状态,0-未验证，1-已验证',
  # @person_type; 人员类别',
  # @person_category; 人员类别,1:正式员工;2:试用员工;3:实习生',
  SET @num=200001;
  SET @count=1; #循环次数
  SET @BU_ID=1;
 while @count<1000001 do #如果@a<10001则返回true，继续执行
  SET @Job_Number=CONCAT('E',@num); #工号
  SET @Person_Name=rand_string(3); #姓名，随即赋值
  SET @Sex=FLOOR(RAND()*2); #性别，随即赋值，0或者1
  SET @Status1=FLOOR(RAND()*4);
  SET @person_category=FLOOR(RAND()*3)+1;
  SET @Verify_Flag=FLOOR(RAND()*2);
  SET @Org_ID=FLOOR(RAND()*27+1);
  SET @ResType_Code=FLOOR(RAND()*3+1);
  SET @Create_Time='2013-12-25';
  SET @JoinIn_Date=str_to_date('2013-01-01 00:00:00','%Y-%m-%d %H:%i:%s');
  SET @Dimission_Date=date_add(@JoinIn_Date, interval 400 day);
  insert into jf_t_person(Job_Number,Person_Name,Sex,BU_ID,Position_Code,PositionGrade_Code,ResType_Code,JoinIn_Date,Dimission_Date,Create_Time,Org_ID,Status,Verify_Flag,person_type,person_category)
    values(@Job_Number,@Person_Name,  @Sex,@BU_ID,@Position_Code,@PositionGrade_Code,@ResType_Code,@JoinIn_Date,@Dimission_Date,@Create_Time,@Org_ID,@Status1,@Verify_Flag, @person_type,@person_category); 
    set @count=@count+1;
     if @count%100=0 #每个部门一百人
     then
         set @BU_ID=@BU_ID+1;
      end if;
  SET @num=@num+1;
 end while;
 end
 #delimiter ;

#创建一个随机产生字符串的函数

 set global log_bin_trust_function_creators = 1;
 DROP FUNCTION IF EXISTS rand_string;
 #DELIMITER
 CREATE FUNCTION rand_string(n INT)
 RETURNS VARCHAR(255)
 BEGIN
   DECLARE chars_str varchar(100) DEFAULT'冯海兵白宇贾蓉蓉灰太狼喜洋洋丽丽圆圆隆冬强倩倩玲玲任宏龚正曹彦斌齐如曹天野张王李冬冬赵东东';
  DECLARE return_str varchar(255) DEFAULT 'preTest_';
   DECLARE i INT DEFAULT 0;
         WHILE i < n DO
          SET return_str = concat(return_str,substring(chars_str , FLOOR(1 + RAND()*43 ),1));#52代表在52个字母中随即找出一个
          SET i = i +1;
          END WHILE;
           #SET return_str='preTest_'+return_str;
          RETURN return_str;
 END
 #delimiter ;

#创建一个随机产生字符串的函数，0和1随机出现

 set global log_bin_trust_function_creators = 1;
 DROP FUNCTION IF EXISTS rand_sex;
 #DELIMITER
 CREATE FUNCTION rand_sex(n INT)
 RETURNS VARCHAR(255)
 BEGIN
   DECLARE chars_str varchar(100) DEFAULT '01';
   DECLARE return_str varchar(255) DEFAULT '';
   DECLARE i INT DEFAULT 0;
       WHILE i < n DO
           SET return_str = concat(return_str,substring(chars_str , FLOOR(1 + RAND()*2 ),1));
           SET i = i +1;
              END WHILE;
              RETURN return_str;
 END
 #delimiter ;

 #执行存储过程插入数据

call insertPerson();