【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

　　　　　　https://blog.csdn.net/Peacock__/article/details/83030476