【mysql】存储过程无参，传入参数，传出参数，动态sql，游标的简单例子
1、首先看数据库数据
2、无参存储过程
-- 1.将mysql分隔符从;设置为& DELIMITER & -- 2.如果存在存储过程proc1则删除 DROP PROCEDURE IF EXISTS `proc1` & -- 3.定义存储过程(无参) CREATE PROCEDURE proc1() BEGIN -- 4.执行指定sql SELECT COUNT(*) FROM t1 t WHERE t.name LIKE '%1%'; END -- 5.结束 & -- 6.将mysql分隔符从;设置为; DELIMITER ; -- 7.调用存储过程 CALL proc1(); -- 8.如果存在存储过程proc1则删除 DROP PROCEDURE IF EXISTS proc1;
3、传入参数
-- 1.将mysql分隔符从;设置为& DELIMITER & -- 2.如果存在存储过程proc1则删除 DROP PROCEDURE IF EXISTS `proc1` & -- 3.定义存储过程(传入参数，类型字符串) CREATE PROCEDURE proc1(IN n CHAR) BEGIN -- 4.执行指定sql SELECT COUNT(*) FROM t1 t WHERE t.name LIKE CONCAT('%',n,'%'); END -- 5.结束 & -- 6.将mysql分隔符从;设置为; DELIMITER ; -- 7.设置变量 SET @name='1'; -- 8.调用存储过程，传入参数 CALL proc1(@name); -- 9.如果存在存储过程proc1则删除 DROP PROCEDURE IF EXISTS proc1;
4、传入传出参数
-- 1.将mysql分隔符从;设置为& DELIMITER & -- 2.如果存在存储过程proc1则删除 DROP PROCEDURE IF EXISTS `proc1` & -- 3.定义存储过程(传入参数，类型字符串。传出参数，类型整型) CREATE PROCEDURE proc1(IN n CHAR, OUT c INT) BEGIN -- 4.执行指定sql（into就是把数据放到指定变量里，这里就是放到c） SELECT COUNT(*) INTO c FROM t1 t WHERE t.name LIKE CONCAT('%',n,'%'); END -- 5.结束 & -- 6.将mysql分隔符从;设置为; DELIMITER ; -- 7.设置变量 SET @name='1'; SET @count=0; -- 8.调用存储过程，传入传出参数 CALL proc1(@name, @count); -- 9.打印 SELECT @count; -- 10.如果存在存储过程proc1则删除 DROP PROCEDURE IF EXISTS proc1;
5、动态sql（无参）
-- 1.将mysql分隔符从;设置为& DELIMITER & -- 2.如果存在存储过程proc1则删除 DROP PROCEDURE IF EXISTS `proc1` & -- 3.定义存储过程(无参) CREATE PROCEDURE proc1() BEGIN -- 4.声明变量 DECLARE $sqltext VARCHAR(1000); -- 5.动态sql SET $sqltext = 'SELECT COUNT(*) FROM t1 t where t.name like \'%1%\';'; -- 一直需要这，不然会报错，目前没有找到资料 SET @sqlcounts := $sqltext; -- 6.预编释，stmt预编释变量的名称 PREPARE stmt FROM @sqlcounts; -- 7.执行SQL语句 EXECUTE stmt; -- 8.释放资源 DEALLOCATE PREPARE stmt; END -- 9.结束 & -- 10.将mysql分隔符从;设置为; DELIMITER ; -- 11.调用存储过程，无参 CALL proc1(); -- 12.如果存在存储过程proc1则删除 DROP PROCEDURE IF EXISTS proc1;
6、动态sql，传入传出参数
-- 1.将mysql分隔符从;设置为& DELIMITER & -- 2.如果存在存储过程proc1则删除 DROP PROCEDURE IF EXISTS `proc1` & -- 3.定义存储过程(传入参数，类型字符串。传出参数，类型整型) CREATE PROCEDURE proc1(IN n CHAR, OUT s INT) BEGIN -- 4.声明变量 DECLARE $sqltext VARCHAR(1000); -- 5.动态sql，把sql返回值放到@ret_date中 SET $sqltext = CONCAT('SELECT COUNT(*) into @ret_date FROM t1 t where t.name like \'%', n, '%\''); -- 一直需要这，不然会报错，目前没有找到资料 SET @sqlcounts := $sqltext; -- 6.预编释，stmt预编释变量的名称 PREPARE stmt FROM @sqlcounts; -- 7.执行SQL语句 EXECUTE stmt; -- 8.释放资源 DEALLOCATE PREPARE stmt; -- 9.获取动态SQL语句返回值 SET s = @ret_date; END -- 10.结束 & -- 11.将mysql分隔符从;设置为; DELIMITER ; -- 12.设置变量 SET @name='1'; SET @count=0; -- 13.调用存储过程，传入传出参数 CALL proc1(@name, @count); -- 14.打印 SELECT @count; -- 15.如果存在存储过程proc1则删除 DROP PROCEDURE IF EXISTS proc1;
7、游标，返回指定数据库的所有列信息（数据库名,表名,列名）
-- 1.将mysql分隔符从;设置为& DELIMITER & -- 2.如果存在存储过程proc1则删除 DROP PROCEDURE IF EXISTS `proc1` & -- 3.定义存储过程(传出参数，类型字符串) CREATE DEFINER=`root`@`localhost` PROCEDURE proc1(OUT str VARCHAR(2000)) BEGIN -- 4.声明变量 DECLARE database_name, table_name, column_name CHAR(200); DECLARE resoult_s VARCHAR(2000) DEFAULT ''; -- 5.定义游标结束标识，默认为0 DECLARE stopflag INT DEFAULT 0; -- 6.定义游标，其实就是临时存储sql返回的集合 DECLARE sql_resoult CURSOR FOR SELECT t.TABLE_SCHEMA, t.TABLE_NAME, t.COLUMN_NAME FROM information_schema.COLUMNS t WHERE t.TABLE_SCHEMA='my_test'; -- 7.游标结束就设置为1 DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopflag=1; -- 8.打开游标 OPEN sql_resoult; -- 9.读取游标中数据，存储到指定变量 FETCH sql_resoult INTO database_name, table_name, column_name; -- 10.没有结束继续往下走 WHILE stopflag=0 DO BEGIN -- 11.拼接字符串，不可直接用传出变量设值 IF (resoult_s IS NULL OR resoult_s='') THEN SET resoult_s=CONCAT(database_name, ',', table_name, ',', column_name); ELSE SET resoult_s=CONCAT(resoult_s, ';', database_name, ',', table_name, ',', column_name); END IF; -- 12.读取游标中数据，存储到指定变量。（和9一样） FETCH sql_resoult INTO database_name, table_name, column_name; END; END WHILE; -- 13.关闭游标 CLOSE sql_resoult; -- 14.把数据放到传出参数 SET str=resoult_s; END -- 15.结束 & -- 16.将mysql分隔符从;设置为; DELIMITER ; -- 17.设置变量 SET @str=''; -- 18.调用存储过程 CALL proc1(@str); -- 19.打印 SELECT @str; -- 20.如果存在存储过程proc1则删除 DROP PROCEDURE IF EXISTS proc1;
或者
-- 1.将mysql分隔符从;设置为& DELIMITER & -- 2.如果存在存储过程proc1则删除 DROP PROCEDURE IF EXISTS `proc1` & -- 3.定义存储过程(传出参数，类型字符串) CREATE DEFINER=`root`@`localhost` PROCEDURE proc1(IN database_n CHAR(20), OUT str VARCHAR(2000)) BEGIN -- 4.声明变量 DECLARE database_name, table_name, column_name CHAR(200); DECLARE resoult_s VARCHAR(2000) DEFAULT ''; -- 5.定义游标结束标识，默认为0 DECLARE stopflag INT DEFAULT 0; -- 6.定义游标，其实就是临时存储sql返回的集合 DECLARE sql_resoult CURSOR FOR SELECT t.TABLE_SCHEMA, t.TABLE_NAME, t.COLUMN_NAME FROM information_schema.COLUMNS t; -- 7.游标结束就设置为1 DECLARE CONTINUE HANDLER FOR NOT FOUND SET stopflag=1; -- 8.打开游标 OPEN sql_resoult; -- 9.读取游标中数据，存储到指定变量 FETCH sql_resoult INTO database_name, table_name, column_name; -- 10.没有结束继续往下走 WHILE (stopflag=0) DO BEGIN IF (database_name=database_n) THEN -- 11.拼接字符串，不可直接用传出变量设值 IF (resoult_s IS NULL OR resoult_s='') THEN SET resoult_s=CONCAT(database_name, ',', table_name, ',', column_name); ELSE SET resoult_s=CONCAT(resoult_s, ';', database_name, ',', table_name, ',', column_name); END IF; END IF; -- 12.读取游标中数据，存储到指定变量。（和9一样） FETCH sql_resoult INTO database_name, table_name, column_name; END; END WHILE; -- 13.关闭游标 CLOSE sql_resoult; -- 14.把数据放到传出参数 SET str=resoult_s; END -- 15.结束 & -- 16.将mysql分隔符从;设置为; DELIMITER ; -- 17.设置变量 SET @str=''; SET @database='my_test'; -- 18.调用存储过程 CALL proc1(@database, @str); -- 19.打印 SELECT @str; -- 20.如果存在存储过程proc1则删除 DROP PROCEDURE IF EXISTS proc1;
my_test,t1,name;my_test,t1,city;my_test,t_user,id;my_test,t_user,user_name;my_test,t_user,user_password;my_test,test_replace,id;my_test,test_replace,name;my_test,test_replace,password
参考文章：https://www.cnblogs.com/end/archive/2011/04/01/2002662.html
浙公网安备 33010602011771号