简单mysql存储过程

浅浅的花香味﹌ 2022-05-24 00:59 207阅读 0赞

1.查询结果赋值到变量

--  方式 1
    DECLARE cnt INT DEFAULT 0;
    select count(*) into cnt from test_tbl;
    select cnt;
    --  方式 2
    set @cnt = (select count(*) from test_tbl);
    select @cnt;
    --  方式 3
    select count(*) into @cnt1 from test_tbl;
    select @cnt1;

2.调用存储过程

call proc_add_role();

3.删除存储过程

DROP PROCEDURE IF EXISTS `proc_add_role`;

4.简单例子

DELIMITER ;;
    CREATE PROCEDURE `proc_add_role`()
    BEGIN  
    SET @cnt = (select max(role_group_id) from role_group);  
    SET @cnt1 = @cnt + 1;-- 最大加1
    -- select @cnt;  
    INSERT INTO `role_group` VALUES (@cnt1, '审核员', 'shenhe', 1, 6, 1, '0', 0, 1, NOW(), NOW(), 0);
    
    -- 查询数据并插入新的数据表
    INSERT INTO sys_user_role_group_r (	role_group_id,	sys_user_id,	identity_id,	identity_instance_id)
    SELECT DISTINCT @cnt1,	sur.sys_user_id,	6,	sur.identity_instance_id
    FROM
    sys_user_role_group_r sur
    JOIN role_group_role_r rgr ON sur.role_group_id = rgr.role_group_id
    AND rgr.role_id = 51 
    WHERE
    sur.identity_id = 6;
    END
    ;;
    DELIMITER ;
    call proc_add_role();
    DROP PROCEDURE IF EXISTS `proc_add_role`;

5.使用游标将查询到的结果集分别存的其他数据表中

DELIMITER ;;
    CREATE PROCEDURE `proc_add_data`()
    
    BEGIN
        DECLARE  t varchar(200);   
        DECLARE  p  varchar(128); 
        DECLARE  c  text;
        DECLARE  pt varchar(32);  
        DECLARE  cid varchar(2);
        DECLARE  catid INT;
        -- 遍历数据结束标志
        DECLARE done INT DEFAULT 0;
        -- 游标
        DECLARE cur1 CURSOR FOR (SELECT title,pic,content,publishtime,column_id FROM fmqk WHERE fmqk.column_id='1');
        -- 将结束标志绑定到游标，若没有数据返回,程序继续,并将变量done设为TRUE
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
        -- DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
    
      -- 打开游标
      OPEN cur1;
      REPEAT
        FETCH cur1 INTO t,p,c,pt,cid;
        IF done<>1 then
    			if cid = '1'
    			then
    			set catid=778;
    			elseif cid = '2'
    			then
    			set  catid=779;
    			end if;
      
      insert into liqi_article (`catid`,`title`,`thumb`,`updatetime`) VALUES (catid,t,p,unix_timestamp(pt));
      insert into liqi_article_data (`id`,`content`) VALUES (LAST_INSERT_ID(),c);
      -- 调试输出
      -- select concat('标题 -> ', t);
      END IF;
      UNTIL done=1 END REPEAT;
      CLOSE cur1;
    END
    ;;
    DELIMITER ;
    call proc_add_data();
    DROP PROCEDURE IF EXISTS `proc_add_data`;