MYSQL存储过程（游标）初级语法例子和带有输入输出函数语法测试例子

兰舟轻帆

已于 2024-04-17 16:42:16 修改

阅读量494

点赞数

分类专栏： Oracle/Mysql/SQL Server 文章标签： mysql MYSQL存储过程存储过程

于 2019-07-06 10:26:42 首次发布

本文链接：https://blog.csdn.net/bj_chengrong/article/details/94836248

版权

Oracle/Mysql/SQL Server 专栏收录该内容

22 篇文章 0 订阅

订阅专栏

1、无参存储过程语法

DROP FUNCTION IF EXISTS TEST_PROCEDURE;
CREATE PROCEDURE TEST_PROCEDURE() -- 创建存储过程
/*开始存储过程*/
BEGIN
/*自定义变量*/
DECLARE MY_ID VARCHAR(32); 
DECLARE MY_NAME VARCHAR(50); 
/*自定义控制游标循环变量,默认FALSE*/
DECLARE DONE INT DEFAULT FALSE;
/*定义游标并输入结果集*/
DECLARE MY_CURSOR CURSOR FOR ( SELECT ID, NAME FROM T_PEOPLE );
/*绑定控制变量到游标,游标循环结束自动转TRUE*/
DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE = TRUE;
/*注意：一定要打开游标循环，上面CONTINUE那句语句才起作用*/
/*打开游标*/
OPEN MY_CURSOR; 
/*开始循环体MY_LOOP为自定义循环名,结束循环时用到*/
  MY_LOOP: LOOP 
    /*将游标当前读取行的数据顺序赋予自定义变量*/
    FETCH MY_CURSOR INTO MY_ID, MY_NAME;
    /*判断是否继续循环*/
    IF DONE THEN 
      /*结束循环*/
      LEAVE MYLOOP;
    END IF;
    /*自己要做的事情,在 SQL 中直接使用自定义变量即可*/
    UPDATE T_USER SET C_NAME = MY_NAME WHERE ID = MY_ID AND RTRIM(LTRIM(C_NAME)) = '';
 
    COMMIT;/*提交事务*/
  END LOOP MY_LOOP;/*结束自定义循环体*/
  CLOSE MY_CURSOR;/*关闭游标*/
END;/*结束存储过程*/

-- sql 执行存储过程  
CALL TEST_PROCEDURE();  

-- sql 删除存储过程
DROP PROCEDURE TEST_PROCEDURE;

2、有参（输入输出）存储过程

DROP PROCEDURE IF EXISTS `PRO_TEST_NAME`;
CREATE PROCEDURE PRO_TEST_NAME(IN ID INT,OUT NAME VARCHAR(50))
BEGIN
    IF(ID = 1) THEN 
          SET NAME = 'TEST_0001';
    END IF;
    IF(ID = 2) THEN 
          SET NAME = 'TEST_0002';
    END IF;
END;
 

DROP PROCEDURE IF EXISTS `PRO_INSERT_DATA`;
CREATE PROCEDURE PRO_INSERT_DATA(IN ID INT,IN NAME VARCHAR(10),IN CLASSNO INT,IN BIRTH DATETIME)
BEGIN
     SET @ID = ID;
     SET @NAME = NAME;
     SET @CLASSNO = CLASSNO;
     SET @BIRTH = BIRTH;
     SET @CLASSNAME = NULL;
     CALL PRO_TEST_NAME(@CLASSNO,@CLASSNAME);
     
     SET @INSERTSQL = CONCAT('INSERT INTO TBL_STUDENT VALUES(?,?,?,?)');
     PREPARE STMTINSERT FROM @INSERTSQL;
     EXECUTE STMTINSERT USING @ID,@NAME,@CLASSNAME,@BIRTH;
     DEALLOCATE PREPARE STMTINSERT;
END;
 
CALL PRO_INSERT_DATA(1,'XY',1,'2019-07-06 10:23:35');

在第二个存储过程中

①利用SET声明了参数，调用了第一个存储过程

②在第一个存储过程中的NAME参数是输出参数，所以@CLASSNAME这个参数在调用完第一个过程后就被附值

③最终利用CONCAT拼接SQL语句并传入参数执行SQL语句

调用存储过程 : CALL PRO_INSERT_DATA(1,'S-001',1,'2019-07-06 10:23:35');

3、存储过程例子


CREATE PROCEDURE proc_archive(in i_table_source varchar(40), 

                              in i_table_target varchar(40), 

                              in i_fieldname varchar(40), 

                              in i_keepdays int,

                              in i_archdays int,

                              in i_other_cond varchar(500))

begin

  /*

  入参：

  i_table_source:原表,含dbname

  i_table_target:federated表

  i_fieldname:时间字段

  i_keepdays:保留天数

  i_archdays:每次归档多少天数据

  i_other_cond:数据额外条件(如status in (2,3)不能归档，需要保留),无额外条件则输入'1=1'

  归档日志表archive_log.status字段含义：

  0:成功, 1:现有数据在保留天数内, 2:目标表含有待归档时间范围的数据, 

  3:插入数据和删除数据记录数不同, 4:SQL执行异常，具体错误见remark

  注意：

  有额外条件时，如果历史数据被修改，从不符合归档条件变成符合归档条件，

  因历史表中归档时间段内已经有之前归档的数据(@v_his_num_before>0)，程序会退出，需手动处理

  */

  declare EXIT HANDLER for SQLWARNING,NOT FOUND,SQLEXCEPTION 

  begin 

      GET DIAGNOSTICS CONDITION 1 @p1=RETURNED_SQLSTATE,@p2= MESSAGE_TEXT;

      ROLLBACK;

      insert into archive_log(tab_name,archive_date_begin,archive_date_end,status,insert_rows,delete_rows,remark)

      values(i_table_source,@v_arch_begin,@v_arch_end,4,@v_his_num_after,@v_del_num,concat('error ',@p1,' - ',@p2));

  end;

  /* 获取在线表的最小日期 */

  set @mystmt = concat("select str_to_date(date_format(min(",i_fieldname,"),'%Y%m%d'),'%Y%m%d') into @v_arch_begin from ",i_table_source,' where ',i_other_cond);

  prepare stmt from @mystmt;

  execute stmt;

  deallocate prepare stmt;

 

  set @v_arch_end = date_add(@v_arch_begin,interval i_archdays day);

 

  set @mystmt = concat("select count(*) into @v_his_num_before from ",i_table_target," where ",i_fieldname," >= ? and ",i_fieldname," < ?");

  prepare stmt from @mystmt;

  execute stmt using @v_arch_begin,@v_arch_end;

  deallocate prepare stmt;

  /* 如果在线表的数据低于keepday范围，退出 */

  if timestampdiff(day,@v_arch_begin,now()) <= i_keepdays then

      insert into archive_log(tab_name,archive_date_begin,archive_date_end,status,insert_rows,delete_rows,remark)

      values(i_table_source,@v_arch_begin,@v_arch_end,1,0,0,concat('error, all data in keey days, min ',i_fieldname,': ',@v_arch_begin));

  end if;

  /* 如果历史表所在的日期区间有数据，退出（需要手动排查原因） */

  if @v_his_num_before <> 0 then

      insert into archive_log(tab_name,archive_date_begin,archive_date_end,status,insert_rows,delete_rows,remark)

      values(i_table_source,@v_arch_begin,@v_arch_end,2,0,0,concat('error, data exists,row num:',@v_his_num_before));

  end if;

  

  if (timestampdiff(day,@v_arch_begin,now()) > i_keepdays and @v_his_num_before = 0) then 

      set @mystmt = concat("insert into ",i_table_target," select * from ",i_table_source," where ",i_fieldname," >= ? and ",i_fieldname," < ? and ",i_other_cond);

      prepare stmt from @mystmt;

      execute stmt using @v_arch_begin,@v_arch_end;

      deallocate prepare stmt;

      /* 因为federated引擎不支持事务，数据insert后再select下记录数，与下面的delete记录数对比，相同则提交delete操作 */

      set @mystmt = concat("select count(*) into @v_his_num_after from ",i_table_target," where ",i_fieldname," >= ? and ",i_fieldname," < ?");

      prepare stmt from @mystmt;

      execute stmt using @v_arch_begin,@v_arch_end;

      deallocate prepare stmt;

 

      start transaction;

 

      set @mystmt = concat("delete from ",i_table_source," where ",i_fieldname," >= ? and ",i_fieldname," < ? and ",i_other_cond);

      prepare stmt from @mystmt;

      execute stmt using @v_arch_begin,@v_arch_end;

      set @v_del_num = row_count();

      deallocate prepare stmt;

 

      if @v_del_num = @v_his_num_after then

          commit;

          insert into archive_log(tab_name,archive_date_begin,archive_date_end,status,insert_rows,delete_rows,remark)

          values(i_table_source,@v_arch_begin,@v_arch_end,0,@v_his_num_after,@v_del_num,'success');

      else

          rollback;

          insert into archive_log(tab_name,archive_date_begin,archive_date_end,status,insert_rows,delete_rows,remark)

          values(i_table_source,@v_arch_begin,@v_arch_end,3,@v_his_num_after,@v_del_num,'rollback, inserted rows num not equal to deleted rows num');

      end if;

    end if;

        

end;

3、有参（输入输出）存储过程

/**
 * 1、数据迁移存储过程
 */
DROP PROCEDURE IF EXISTS PRO_DATA_MIGRATION;
CREATE PROCEDURE PRO_DATA_MIGRATION (
	IN I_START_DATE VARCHAR (64),
  IN I_END_DATE VARCHAR (64),
	IN I_NAME_TABLES VARCHAR (5000),
	OUT O_ERROR_MSG VARCHAR (1024)
)
BEGIN 
/* 声明BODY内部操作变量 */
DECLARE I_START_DATE VARCHAR (64) DEFAULT '';
DECLARE I_END_DATE VARCHAR (64) DEFAULT '';
DECLARE I_NAME_TABLES VARCHAR (5000) DEFAULT '';
DECLARE T_ERROR_MSG VARCHAR (1024) DEFAULT '';
/* 自定义控制游标循环变量,默认FALSE */
DECLARE BOO_DONE INT DEFAULT FALSE; 

SET T_ERROR_MSG = 'Factory IP is empty!!!'; 
SET BOO_DONE = TRUE;

IF BOO_DONE = TRUE THEN 
	SET T_ERROR_MSG = 'Factory IP is empty!!!'; 
END IF;
SET O_ERROR_MSG = T_ERROR_MSG;
END;


-- MySQL执行存储过程
CALL PRO_DATA_MIGRATION('2019-11-26 12:23:35','2019-12-26 12:23:35','TM_TEST',@O_ERROR_MSG);
SELECT @O_ERROR_MSG;