【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);
mysql存储过程使用-- 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 DEFAULT0;
-- 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 DEFAULT0;
-- 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;
View Code
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
参考⽂章：
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论