mysql 存储过程

注意存储过程中每句sql都要以分号结尾，不然报错。

存储过程中变量的定义必须放在begin...end内部的第一行，不然报错。

存储过程中变量的定义：

　　局部变量：

　　　　关键字：declare，declare 变量名 变量类型  default  默认值；

　　　　作用范围(scope)：begin...end之间有效；

　　　　定义多个相同类型的变量：declare 变量名a,变量名b 变量类型 default 默认值;

　　　　注意：在存储过程或函数中，declare语句要放在begin...end之间最开始的位置，否则会报错。

　　会话变量：

　　　　关键符号：@，不需要声明，会自动根据值类型来确定数据类型

　　　　作用范围(scope)：整个会话过程中有效（类似于全局变量）

 

 存储过程中变量的赋值：

　　set方式：set 变量 = 变量值;（注意：如果等号右边是一个sql语句，sql语句需要用小括号括起来，不然会报错，且该sql只能查询一个字段，多字段时用into方式）

　　into方式：select 字段a,字段b into 字段值aa,字段值bb from 表名 where 条件;

if语句

IF expression THEN
statements;
END IF;

 

存储过程中游标的定义与使用：

　　定义：四个declare要放在begin...end开始的位置，且第四行定义语句不能在第三行定义语句之前。

　　注意：定义需要接收游标数据的变量名 与 游标中查询的字段名 不可以相同，否则将无法赋值。如下面游标所示，查询的字段为operation_code，定义的接收游标的变量为v_station，而不可以定义为operation_code。

declare done boolean default 0;#定义遍历数据结束标志
declare v_station varchar(50);#定义需要接收游标数据的变量
declare cursor_product_stations cursor for select operation_code from t_dd_ppr_process_operation where process_id = v_pprid order by operation_code;#定义游标 在打开游标之前，游标定义的 SQL 语句是不执行的。
declare continue handler for not found set done = 1; #将结束标志绑定到游标

　　

　　使用：游标中数据循环有三种方式，分别为Loop循环，while循环，repeat循环

　　Loop循环：

　　　　　　  open cursor_product_stations;#打开游标
                read_loop:LOOP
                    fetch cursor_product_stations into v_station;#提取游标中的数据
                    if done then
                        leave read_loop;#声明何时结束循环
                    end if;
　　　　　　　　　　　　#业务处理
　　　　　　　　　　　　insert into t_dd_ct_station_status(id,product_snum,unit_code,unit_status,unit_reject_code,create_date) 
                    values (uuid(),Snum,v_station,null,null,sysdate());
                end loop;
            close cursor_product_stations;#关闭游标

　　while循环：

　　　　open cursor_product_stations;#打开游标
            while !done do
                fetch cursor_product_stations into v_station;#提取游标中的数据
                  if !done then
                     insert into t_dd_ct_station_status(id,product_snum,unit_code,unit_status,unit_reject_code,create_date) 
                        values (uuid(),Snum,v_station,null,null,sysdate());
                  end if;    
            end while;
       close cursor_product_stations;#关闭游标

　　repeat循环：

       open cursor_product_stations;#打开游标
            repeat
                fetch cursor_product_stations into v_station;#提取游标中的数据
                   if !done then
                      insert into t_dd_ct_station_status(id,product_snum,unit_code,unit_status,unit_reject_code,create_date) 
                        values (uuid(),Snum,v_station,null,null,sysdate());
                   end if;
                until done
            end repeat;
       close cursor_product_stations;#关闭游标

双游标嵌套

CREATE DEFINER=`root`@`localhost` PROCEDURE `test`()
BEGIN
    DECLARE user1 VARCHAR(100);
    DECLARE user2 VARCHAR(100);
    DECLARE done INT DEFAULT 0;
    DECLARE cursor_user1 CURSOR FOR(SELECT `name` FROM `user`); -- 定于游标1
    DECLARE cursor_user2 CURSOR FOR(SELECT `name` FROM `user`); -- 定义游标2
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; -- 结束标识
    OPEN cursor_user1; -- 开启游标1
    loop_1: LOOP -- 外层循环
    -- -------------------------------------------------------------------------------------
            # 尝试从游标1指向的数据集合获取数据，如果集合已经遍历完毕，done的值被设置为1。
            FETCH cursor_user1 INTO user1;
            IF done = 1 THEN LEAVE loop_1; END IF; -- 外层循环结束条件
            OPEN cursor_user2; -- 开启游标2
            loop_2: LOOP -- 内层循环
            -- -----------------------------------------------------------------------------
                    # 尝试从游标2指向的数据集合获取数据，如果集合已经遍历完毕，done的值被设置为1。
                    FETCH cursor_user2 INTO user2;
                    IF done = 1 THEN LEAVE loop_2; END IF; -- 内层循环结束条件
                    # do something...
            -- -----------------------------------------------------------------------------
            END LOOP loop_2;
            CLOSE cursor_user2; -- 关闭游标2
            SET done = 0; -- important
    -- ------------------------------------------------------------------------------------
    END LOOP loop_1;
    CLOSE cursor_user1; -- 关闭游标1
END

 

CREATE DEFINER=`root`@`localhost` PROCEDURE `test_cursor`()
BEGIN
    declare $group_number,$product_snum,$unit_code,$collection_time,$operator varchar(50);
    declare item_name_id,$item_name,$large_type varchar(50);
    
    -- 游标定义
    declare done boolean default 0;#定义遍历数据结束标志
    declare select_group_number_cursor cursor for select group_number,product_snum,unit_code,collection_time,operator from t_dd_qm_product_testitem_group where deal_flag=0 order by create_date limit 2;#定义游标
    declare unit_item_cursor cursor for select id,item_name,large_type from t_dd_qm_unit_item where unit_code=$unit_code and enabled=1;#定义游标
    declare continue handler for not found set done = 1; #将结束标志绑定到游标    
    
    open select_group_number_cursor;#打开游标
        while !done do
            fetch select_group_number_cursor into $group_number,$product_snum,$unit_code,$collection_time,$operator;#提取游标中的数据
                if !done then
                    open unit_item_cursor;#打开游标
                        while !done do
                            fetch unit_item_cursor into item_name_id,$item_name,$large_type;#提取游标中的数据
                                if !done then
                                    select $group_number,$product_snum,$unit_code,$collection_time,$operator;
                                    select item_name_id,$item_name,$large_type;
                                end if;  
                        end while;
                    close unit_item_cursor;#关闭游标
                    set done = 0;-- 重点在这里
                end if;    
        end while;
    close select_group_number_cursor;#关闭游标    
END

 

 

调用带输出参数的存储过程：参数个数要全，输出参数用带@符号的任意变量名代替

CALL `tcx_12101`.`p_dl_on_line_save_snum_and_station_status`('OP1010', 'ZFXKH/QIANPU/210426001A', '', 111, @ReturnValue1);
select @ReturnValue1;