1.存储过程定义:
CREATE PROCEDURE test_pro(IN p_name VARCHAR(30))
BEGIN
select * from user where name = p_name ;
END;
2.删除存储过程:
DROP PROCEDURE IF EXISTS test_pro;
2.存储过程定义变量和引用变量:
DROP PROCEDURE IF EXISTS test_pro;
CREATE PROCEDURE test_pro(IN p_name VARCHAR(30))
BEGIN
-- 定义两个变量接收游标查询出的结果
DECLARE _name VARCHAR(30);
DECLARE _id int;
DECLARE done INT;
-- 定义游标
DECLARE temp CURSOR FOR(
select id,name from user where name = p_name
);
-- 将结束标志绑定到游标
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
-- 打开游标 查询到的数据插入到对应表中
OPEN temp;
cursor_loop:LOOP
FETCH temp INTO _id ,_name;
IF done=1 THEN
LEAVE cursor_loop;
END IF;
INSERT INTO user_copy (`id`, `name`)VALUES (_id , _name);
END LOOP;
CLOSE temp;
END;
3.调用存储过程
SET @p_name =‘张三’;
CALL test_pro(@p_in);
-- 或者这样:call test_pro(‘张三’);
网友评论