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`;