存储过程（详细-附样例）-EW帮帮网

微信交流群：https://732903.hlcode.cn/?id=NK1fWUR

所有样例都是在mysql5.7版本下执行的，如果用其他数据库，请根据对应的数据库存储过程定义更改语句。

主体目录

一、存储过程相关命令

请以左边系统目录为准

一、存储过程相关命令

序号	命令	注释
1	SHOW PROCEDURE STATUS;	查看所有存储过程信息
2	SHOW CREATE PROCEDURE 存储过程名;	查看存储过程源码
3	DROP PROCEDURE 存储过程名;	删除存储过程
4	CALL 存储过程名(‘输入参数’，@输出参数名);	调用存储过程，如无参数，括号里留空

系统表information_schema.ROUTINES里是存储过程的详细信息：information_schema.ROUTINES 是MySQL中的一个系统表，存储了所有存储过程、函数、触发器的详细信息，包括名称、返回值类型、参数、创建时间、修改时间等。

二、存储过程

1、基本的存储过程

1.基本样例

#改变语句结束符为//。
DELIMITER //
#创建存储过程名称([ 参数列表 ])。参数列表可以为空
create procedure name_count()

#存储过程执行代码的开头
begin

    #查询某个数据库里有多少张表
    select table_schema,count(*) from information_schema.tables where TABLE_SCHEMA = 'ling';

#存储过程执行代码的结尾
end //
#改变语句结束符为;。是将语句结束符改回;。
DELIMITER ;

#调用存储过程
CALL  name_count();

2. 基本样例语句命令详细说明

1）delimiter

DELIMITER的主要作用是改变语句结束符。MySQL默认是使用分号 ; 作为语句结束符。但是在编写存储过程、函数等复杂SQL时，需要在代码块中使用分号来结束单个SQL语句，包含多个SQL语句。这时候就需要DELIMITER命令来临时改变默认的语句结束符。告诉mysql，遇到指定符号再执行

注：不同数据库的存储定义可能不一样，delimiter有时可省略。

样例

#改变语句结束符为//。
DELIMITER //

#中间放存储过程语句

#改变语句结束符为;。是将语句结束符改回;。一般可以省略
DELIMITER ;

2)create procedure 存储过程名称( [参数列表] )

create procedure：创建存储过程。

注：参数列表里的注释放在下面的带参数样例语句命令详细说明中

样例

#格式
CREATE PROCEDURE `库名`.`存储过程名称`(IN/OUT/INOUT 参数名 参数类型)

#样例
CREATE PROCEDURE name_s()

CREATE PROCEDURE `ling`.`name_s`()

CREATE PROCEDURE `name_ku`.`name_s`(IN sx varchar(255),OUT sx_count INT)

3）begin、end

存储过程的架构，BEGIN是开始，END是结束。

存储过程语句必须在BEGIN和END之间。

2、参数

1. 语法格式：

#格式
CREATE PROCEDURE `库名`.`存储过程名称`(IN/OUT/INOUT 参数名 参数类型)

#样例
CREATE PROCEDURE name_s()

CREATE PROCEDURE `ling`.`name_s`()

CREATE PROCEDURE `name_ku`.`name_s`(IN sx varchar(255),OUT sx_count INT)

参数列表

存储过程中的参数列表分别有 in，out，inout三种类型；

1、in：输入参数（默认为in参数），该参数的值必须由存储过程制定。
2、ou：输出参数，该参数的值经存储过程计算后，将out参数的计算结果返回给调用的存储过程。
3、inout：即是输入参数，又是输出参数，该参数的值即可有储过程制定，又可以将inout参数的计算结果返回给调用的存储过程。

注：into 是赋值函数，将某个值，赋值给另一个。

2.参数样例

#改变语句结束符为//。
DELIMITER //

#创建存储过程名称([ 参数列表 ])。参数列表可以为空
#定义了输入参数naname，输出参数naname_count
create procedure name_count1(IN naname VARCHAR(100),OUT naname_count VARCHAR(100))

#存储过程执行代码的开头
begin

	#查询某个数据库里有多少张表
	select CONCAT_WS('',table_schema,'库表总数:',count(*))  into naname_count  from information_schema.tables where TABLE_SCHEMA = naname;
    #调用后直接查询出参数结果
    #select naname_count;
#存储过程执行代码的结尾
end //
#改变语句结束符为;。是将语句结束符改回;。
DELIMITER ;

#调用存储过程，第一个是输入的参数，第二个是输出
CALL name_count1('ling',@naname_count);

select @naname_count;

注：想要直接call调用后直接出结果，而不是调用后再select查询，则要将select语句放到存储过程中，在存储过程执行代码的结尾end上面一行添加select语句即可。

语句样例中有，把注释去掉即可。

3、变量

1. 语法格式：

#声明变量name_s ，数据类型是VARCHAR，默认值是空
DECLARE name_s VARCHAR(10) DEFAULT '';

#声明变量sceor ，数据类型是int ，默认值是5000
DECLARE sceor int DEFAULT 5000;

#把1赋值给name_s变量
set name_s = "1" ;
set name_s := "1" ;

在存储过程中使用的是局部变量，只在存储过程中生效

1、declare：声明变量

2、default：变量默认赋值

注：set 是赋值操作，跟into的作用一样，将某个值，赋值给另一个。

2.变量样例

#改变语句结束符为//。
DELIMITER //

#创建存储过程名称([ 参数列表 ])。参数列表可以为空。不需要参数的可把参数去了
create procedure name_count2(IN naname VARCHAR(100),OUT naname_count VARCHAR(100))

#存储过程执行代码的开头
begin

	#声明，用来声明变量的,DEFAULT后面也可留空''。
	DECLARE name_s VARCHAR(10) DEFAULT '表总数';

	#set可用来改变值， 变量=值
	set name_s ="库表总数:";

	#查询某个数据库里有多少张表
		select CONCAT_WS('',table_schema,name_s,count(*))  into naname_count  from information_schema.tables where TABLE_SCHEMA = naname;

#存储过程执行代码的结尾
end //
#改变语句结束符为;。是将语句结束符改回;。
DELIMITER ;

#调用存储过程，第一个是输入的参数，第二个是输出
CALL name_count2('ling',@naname_count);

select @naname_count;

4、if 流程控制

1.语法格式：

if 条件1 then
......(此处是满足条件后进行的操作)
elseif 条件2 then
......(此处是满足条件后进行的操作)
elseif 条件3 then
......(此处是满足条件后进行的操作)
else
......(此处是都不满足前面的条件后进行的操作)
end if;

2.if 语句样例

1）样例1

判断输入的成绩是否合格

score >= 90分，等级为优秀。

score >= 75分且 score < 90分，为优良。

score >= 60分且 score < 75分，为及格。

score < 60分，为不及格。

#改变语句结束符为//。
DELIMITER //
#创建存储过程名称([ 参数列表 ])。参数列表可以为空。不需要参数的可把参数去了
create procedure name_if_score(IN score int)

#存储过程执行代码的开头
begin 
	#定义变量
    declare rating varchar(100);
    #if判断
    if score >= 90 and score <= 100 then
        set rating := '优秀';
    elseif score >= 75 and score < 90 then
        set rating := '优良';
    elseif score >= 60 and score < 75 then
        set rating := '及格';
    elseif score >= 0 and score < 60 then
        set rating := '不及格';
    else
        set rating := '参数错误，请重新输入成绩';
    end if;
    select rating;
		
#存储过程执行代码的结尾
end //
#改变语句结束符为;。是将语句结束符改回;。
DELIMITER ;

call name_if_score('76');

2）样例2

判断输入的收入等级

超过10000的属于“高收入”。

6000到10000的属于“中收入”。

少于6000的属于“低收入”。

#改变语句结束符为//。
DELIMITER //
#创建存储过程名称([ 参数列表 ])。参数列表可以为空。不需要参数的可把参数去了
create procedure name_if_salary(IN salary int)

#存储过程执行代码的开头
begin 
	#if判断
	if salary >= 10000 and salary <= 9999999 then
		select '高收入';
	elseif salary >= 6000 and salary <10000 then
		select '中收入';
	elseif salary < 6000  and salary >=0  then
		select '低收入';
    else
        select '参数错误，请重新输入收入';
    end if;
		
#存储过程执行代码的结尾
end //
#改变语句结束符为;。是将语句结束符改回;。
DELIMITER ;

call name_if_salary('6300');

5、case 条件控制

1.语法格式：

1）格式1

case
	when 条件1 then
	......（此处是满足条件后进行的操作）
	when 条件2 then
	......（此处是满足条件后进行的操作）
	when 条件3 then
	......（此处是满足条件后进行的操作）
	else
	......(此处是都不满足前面的条件后进行的操作)
end case;

2）格式2

case 值
	when 条件1 then
	......（此处是满足条件后进行的操作）
	when 条件2 then
	......（此处是满足条件后进行的操作）
	when 条件3 then
	......（此处是满足条件后进行的操作）
	else
	......（此处是都不满足前面的条件后进行的操作）
end case;

2.case 语句样例

1）样例1

判断输入的月份是那一季节

1-3月份，为春季

4-6月份，为夏季

7-9月份，为秋季

10-12月份，为冬季

#改变语句结束符为//。
DELIMITER //
#创建存储过程名称([ 参数列表 ])。参数列表可以为空。不需要参数的可把参数去了
create procedure name_case_month(IN month int)

#存储过程执行代码的开头
begin 
	#定义变量
    declare season varchar(15);
    #case判断
    case
        when month >= 1 and month <= 3 then
            set season = '春季';
        when month >= 4 and month <= 6 then
            set season = '夏季';
        when month >= 7 and month <= 9 then
            set season = '秋季';
        when month >= 10 and month <= 12 then
            set season = '冬季';
        else
            set season = '参数错误，请重新输入月份';
        end case ;
    select concat('输入的月份是: ',month, ', 所属的季节是: ',season);
		
#存储过程执行代码的结尾
end //
#改变语句结束符为;。是将语句结束符改回;。
DELIMITER ;

call name_case_month(11);

2）样例2

判断输入的数字是星期几

1到7分别对应星期一到星期日

#改变语句结束符为//。
DELIMITER //
#创建存储过程名称([ 参数列表 ])。参数列表可以为空。不需要参数的可把参数去了
create procedure name_case_week(IN week int)

#存储过程执行代码的开头
begin 

    #case判断
    case week
        when   1  then
            select '星期一';
				when   2  then
            select '星期二';
				when   3  then
            select '星期三';
				when   4  then
            select '星期四';
				when   5  then
            select '星期五';
				when   6  then
            select '星期六';
				when   7  then
            select '星期日';
        else
            select '参数错误，请重新输入';
        end case ;
		
#存储过程执行代码的结尾
end //
#改变语句结束符为;。是将语句结束符改回;。
DELIMITER ;

call name_case_week(5);

6、while 循环语句

1.语法格式：

#判定是否符合条件，符合则执行循环，执行到不符合条件后停止循环；否则，不执行逻辑
while 条件 do
	循环内容;
end while;

2.while 语句样例

1）样例1

执行传入的数字（bigit）对应的循环次数，每循环一次，数字加1

循环条件：每循环一次, 传入的数字进行减1 , 等传入的数字（bigit）减到0, 就退出循环。

循环结果：定义一个变量，变量是0，每循环一次加1，直到循环结束，输出最后的变量值。

#改变语句结束符为//。
DELIMITER //
#创建存储过程名称([ 参数列表 ])。参数列表可以为空。不需要参数的可把参数去了
create procedure name_while_bigit(IN bigit int)

#存储过程执行代码的开头
begin 
	#定义变量
	declare sum_n  int default 0;
	#while循环，符合条件就进行循环，执行到不符合条件后停止循环
	while bigit > 0 do 
		#循环内容
		set sum_n = sum_n + 1;
		#每次减1后重新赋值给bigit
		set bigit = bigit - 1;
  end while;
	
  select sum_n;

#存储过程执行代码的结尾
end //
#改变语句结束符为;。是将语句结束符改回;。
DELIMITER ;

call name_while_bigit(5);

2）样例2

传入一个数字（bigit），计算1~n中所有奇数的和。

循环条件：每循环一次, 传入的数字进行减1 , 等传入的数字（bigit）减到0, 就退出循环。

循环结果：定义一个变量，变量是0，每循环一次if判断是否为奇数，是的加该数，直到循环结束，输出最后的变量值。

#改变语句结束符为//。
DELIMITER //
#创建存储过程名称([ 参数列表 ])。参数列表可以为空。不需要参数的可把参数去了
create procedure name_while_bigit1(IN bigit int)

#存储过程执行代码的开头
begin 
	#定义变量
	declare sum_n  int default 0;
	#while循环，符合条件就进行循环，执行到不符合条件后停止循环
	while bigit > 0 do 
		#循环内容，判断当前数是否是奇数
		if mod(bigit,2)  = 1  then 
              #是奇数相加
		      set sum_n = sum_n + bigit;
		end if;
		#每次减1后重新赋值给bigit
		set bigit = bigit - 1;
  end while;
	
  select sum_n;

#存储过程执行代码的结尾
end //
#改变语句结束符为;。是将语句结束符改回;。
DELIMITER ;

call name_while_bigit1(3);

7、repeat 循环语句

1.语法格式：

#先执行一次循环体，然后判断 until 条件是否满足，如果满足，就退出循环。如果不满足，就继续循环，直到条件是否满足退出循环
repeat
	循环体;
	until 条件
end repeat;

2.repeat 语句样例

1）样例1

执行传入的数字（bigit）对应的循环次数，每循环一次，数字加1

循环条件：每循环一次, 传入的数字进行减1 , 等传入的数字（bigit）减到0, 就退出循环。

循环结果：定义一个变量，变量是0，每循环一次加1，直到循环结束，输出最后的变量值。

#改变语句结束符为//。
DELIMITER //
#创建存储过程名称([ 参数列表 ])。参数列表可以为空。不需要参数的可把参数去了
create procedure name_repeat_bigit(IN bigit int)

#存储过程执行代码的开头
begin 
	#定义变量
	declare sum_n  int default 0;
	#repeat循环，先执行一次循环体，然后判断 until 条件是否满足，如果满足，就退出循环。如果不满足，就继续循环，直到条件是否满足退出循环
	repeat  
		#循环内容
		set sum_n = sum_n + 1;
		#每次减1后重新赋值给bigit
		set bigit = bigit - 1;
		#判断循环条件
		until bigit <= 0
  end repeat;
	
  select sum_n;

#存储过程执行代码的结尾
end //
#改变语句结束符为;。是将语句结束符改回;。
DELIMITER ;

call name_repeat_bigit(5);

2）样例2

传入一个数字（bigit），计算1~n中所有奇数的和。

循环条件：每循环一次, 传入的数字进行减1 , 等传入的数字（bigit）减到0, 就退出循环。

循环结果：定义一个变量，变量是0，每循环一次if判断是否为奇数，是的加该数，直到循环结束，输出最后的变量值。

#改变语句结束符为//。
DELIMITER //
#创建存储过程名称([ 参数列表 ])。参数列表可以为空。不需要参数的可把参数去了
create procedure name_repeat_bigit1(IN bigit int)

#存储过程执行代码的开头
begin 
	#定义变量
	declare sum_n  int default 0;
	#repeat循环，先执行一次循环体，然后判断 until 条件是否满足，如果满足，就退出循环。如果不满足，就继续循环，直到条件是否满足退出循环
	repeat  
		#循环内容，判断当前数是否是奇数
		if mod(bigit,2)  = 1  then 
              #是奇数相加
		      set sum_n = sum_n + bigit;
		end if;
		#每次减1后重新赋值给bigit
		set bigit = bigit - 1;
		#判断循环条件
		until bigit <= 0
  end repeat;
	
  select sum_n;

#存储过程执行代码的结尾
end //
#改变语句结束符为;。是将语句结束符改回;。
DELIMITER ;

call name_repeat_bigit1(5);

8、loop 循环语句

1.语法格式：

#LOOP简单的循环，如果不在SQL中添加退出循环的条件，则会进入的死循环

LOOP循环名称:LOOP
    SQL...
END LOOP LOOP循环名称;

#在SQL使用的函数，用来退出和跳过loop循环。
leave：配合SQL循环使用，退出循环。
iterate：必须用在循环中，用来跳过此次循环后面的SQL，直接进入下一次循环。

2.loop 语句样例

1）样例1

执行传入的数字（bigit）对应的循环次数，每循环一次，数字加1，传入数字（bigit）是偶数时，这次循环跳过。

循环条件：每循环一次, 传入的数字进行减1 , 等传入的数字（bigit）减到0, 就退出循环。

循环结果：定义一个变量，变量是0，每循环一次加1，传入数字（bigit）是偶数时，这次循环跳过。直到循环结束，输出最后的变量值。

注：判断循环退出（ileave）条件，要在跳过循环（iterate）条件之前，并且在跳过循环（iterate）条件之前要给判断条件重新赋值。

#改变语句结束符为//。
DELIMITER //
#创建存储过程名称([ 参数列表 ])。参数列表可以为空。不需要参数的可把参数去了
create procedure name_loop_bigit(IN bigit int)

#存储过程执行代码的开头
begin 
	#定义变量
	declare sum_n  int default 0;
	#loop循环
	name_bigit:loop  
		#循环内容
		#判断循环退出条件
		if bigit <= 0 then
				#符合条件退出循环
				leave name_bigit; 
				
		#判断循环跳过条件
		elseif  bigit%2 = 0 THEN
				#跳过此次循环前先减1后重新赋值给bigit
				set bigit = bigit - 1;
				#符合条件跳过此次循环，进入下一次循环。
				iterate name_bigit;

		end if;

				set sum_n = sum_n + 1;
		#未跳过此次循环减1后重新赋值给bigit
				set bigit = bigit - 1;
  end loop name_bigit;
	
  select sum_n;

#存储过程执行代码的结尾
end //
#改变语句结束符为;。是将语句结束符改回;。
DELIMITER ;

call name_loop_bigit(5);

2）样例2

传入一个数字（bigit），计算1~n中所有奇数的和。不做跳过循环。

循环条件：每循环一次, 传入的数字进行减1 , 等传入的数字（bigit）减到0, 就退出循环。

循环结果：定义一个变量，变量是0，每循环一次if判断是否为奇数，是的加上该数，直到循环结束，输出最后的变量值。

#改变语句结束符为//。
DELIMITER //
#创建存储过程名称([ 参数列表 ])。参数列表可以为空。不需要参数的可把参数去了
create procedure name_loop_bigit1(IN bigit int)

#存储过程执行代码的开头
begin 
	#定义变量
	declare sum_n  int default 0;
	#loop循环
	name_bigit:loop  
		#循环内容
		#判断循环退出条件
		if bigit <= 0 then
				#符合条件退出循环
				leave name_bigit; 
		end if;

		#循环内容，判断当前数是否是奇数
		if mod(bigit,2)  = 1  then 
              #是奇数相加
		      set sum_n = sum_n + bigit;
		end if;
		
		#此次循环减1后重新赋值给bigit
				set bigit = bigit - 1;
  end loop name_bigit;
	
  select sum_n;

#存储过程执行代码的结尾
end //
#改变语句结束符为;。是将语句结束符改回;。
DELIMITER ;

call name_loop_bigit1(5);

9、游标

1.语法格式：

#声明游标
declare 游标名称 cursor for 查询语句;

#打开游标
open 游标名称;

#游标获取数据
fetch 游标名称 into 变量,变量,变量......

#关闭游标
close 游标名称;

2.游标语句样例

1）样例1

根据传入的数据库名，查询该数据库所有表的信息，并插入对应的结果存放表中。

定义变量：先定义需要字段的变量，用于后面游标获取数据后进行赋值。

声明游标：游标用来查询传入的数据库名的数据库所有表的信息。

建表清表：建一个结果存放表。并且清空下结果存放表，用于每次执行都是新的数据。

打开游标：使用游标前，要先打开游标。

循环处理：使用游标获取数据，并进行处理。

关闭游标：循环处理结束后，进行关闭游标

注：局部变量的声明需要在游标声明之前完成

#改变语句结束符为//。
DELIMITER //
#创建存储过程名称([ 参数列表 ])。参数列表可以为空。不需要参数的可把参数去了
create procedure name_sql_date(IN nname varchar(25))

#存储过程执行代码的开头
begin 
	#定义变量
	DECLARE table_schema_1 varchar(255);
	DECLARE table_name_1 varchar(255);
	DECLARE table_comment_1 varchar(255);
	DECLARE table_rows_1 varchar(255);
	DECLARE data_size_1 varchar(255);
	DECLARE create_time_1 varchar(255);
	DECLARE update_time_1 varchar(255);
	
	#声明游标
	declare sql_schema_table cursor for select table_schema,table_name,table_comment,table_rows,( data_length / 1024 ) as data_size,create_time,update_time from information_schema.`tables` where  table_schema = nname;
	
	#创建结果存放表，字段顺序与上面查询语句的字段顺序一样
	    create table if not exists `schema_table` (
        `table_schema` VARCHAR ( 255 ) DEFAULT NULL COMMENT '数据库名称',
        `table_name` VARCHAR ( 257 ) DEFAULT NULL COMMENT '表英文名称',
        `table_comment` VARCHAR ( 258 ) DEFAULT NULL COMMENT '表中文名称',
        `table_rows` VARCHAR ( 259 ) DEFAULT NULL COMMENT '数据行数',
        `data_size` VARCHAR ( 260 ) DEFAULT NULL COMMENT '表大小',
        `create_time` VARCHAR ( 261 ) DEFAULT NULL COMMENT '创建时间',
        `update_time` VARCHAR ( 262 ) DEFAULT NULL COMMENT '更新时间'
    ) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT = '数据库-数据表相关信息';
	
	#清空结果存放表
	truncate table schema_table;
	
	#打开游标
	open sql_schema_table;
	
	#循环，true表示循环所有
  while true do
		#游标获取数据，并进行赋值。
        fetch sql_schema_table into table_schema_1,table_name_1,table_comment_1,table_rows_1,data_size_1,create_time_1,update_time_1;
		#将得到的数据进行插入结果存放表
        insert into schema_table(table_schema,table_name,table_comment,table_rows,data_size,create_time,update_time) values (table_schema_1,table_name_1,table_comment_1,table_rows_1,data_size_1,create_time_1,update_time_1);
  end while;

	#关闭游标
	close sql_schema_table;
	
#存储过程执行代码的结尾
end //
#改变语句结束符为;。是将语句结束符改回;。
DELIMITER ;

call name_sql_date('数据库名');

运行存储过程后，发现报错1329 - No data - zero rows fetched, selected, or processed。

但去查看结果存放表（schema_table）时发现，表里已经有数据了。这是因为while true循环导致的。这个问题用条件处理（捕捉异常并处理）解决。详见目录第10点（下一章节）。

2）样例2

根据传入的数据库名，查询该数据库所有表的信息，并插入对应的结果存放表中。然后再统计准确的数据量更新到结果存放表中。

定义变量：先定义需要字段的变量，用于后面游标获取数据后进行赋值。

声明游标：游标用来查询传入的数据库名的数据库表的信息。并对游标循环结束后进行变量赋值。

建表入表：建一个结果存放表。并且将要查询的库表原始信息放入结果存放表，用于每次执行都是新的数据。

打开游标：使用游标前，要先打开游标。等

循环处理：使用游标获取数据，将查询到的数据更新到结果存放表。

关闭游标：循环处理结束后，进行关闭游标

#改变语句结束符为//。
DELIMITER //
#创建存储过程名称([ 参数列表 ])。参数列表可以为空。不需要参数的可把参数去了
create procedure name_sql_date_rows(IN nname varchar(25))

#存储过程执行代码的开头
begin 
	#定义变量
   DECLARE s int DEFAULT 0;
   DECLARE table_schema_1 varchar(256);
   DECLARE table_name_1 varchar(256);
	
	#声明游标
	declare sql_schema_table cursor for select table_schema,table_name from schema_table_rows;
	
	#声明当游标循环完后，将标志变量置成某个值
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1;
	
	#创建结果存放表。
create table if not exists `schema_table_rows`  (
  `id` int(25) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `table_schema` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '数据库名称',
  `table_name` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '表英文名称',
  `table_comment` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '表中文名称',
  `table_rows` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '数据行数',
  `data_size` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '表大小',
  `create_time` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '表创建时间',
  `update_time` varchar(256) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '表更新时间',
  `TONG_TIME` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '入库时间',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 19246 CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT = '数据库-数据表相关信息' ROW_FORMAT = Dynamic;
	
	
	#插入结果存放表的原始数据
	insert into  schema_table_rows(table_schema
	,table_name
	,table_comment
	,table_rows
	,data_size
	,create_time
	,update_time
	)
	SELECT 
	a.table_schema,
	a.table_name,
	a.table_comment,
	a.table_rows,
	( a.data_length / 1024 ) AS data_size,
	a.create_time,
	a.update_time 
	FROM
	information_schema.`tables` a
	left join schema_table_rows b  on  a.table_schema = b.table_schema and a.table_name = b.table_name
	where b.table_name is null  and a.table_name <> 'schema_table_rows' and a.table_schema = nname;   
	
	#打开游标
	open sql_schema_table;
	
	#循环，true表示循环所有
  while s <> 1 do
		#游标获取数据，并进行赋值。
        fetch sql_schema_table into table_schema_1,table_name_1;
		#将查询到的数据更新到结果存放表
		#定义变量
   set @rows_sql =CONCAT("update schema_table_rows  set table_rows = (select count(1) from ",table_schema_1,'.',table_name_1,") where table_schema='",table_schema_1,"' and table_name='",table_name_1,"' ");
	 
	  #使用PREPARE语句，将@rows_sql的内容，准备为一个可执行的语句。
		PREPARE update_rows FROM @rows_sql;
	  
		#使用EXECUTE语句执行准备好的语句。
		EXECUTE update_rows ;
		
		#循环完成后，s会被赋值为1，然后退出循环。这个上面头有做赋值声明。
  end while;

	#关闭游标
	close sql_schema_table;
	
	#释放准备好的语句连接。
	DEALLOCATE PREPARE update_rows; 
	
	
#存储过程执行代码的结尾
end //
#改变语句结束符为;。是将语句结束符改回;。
DELIMITER ;

call name_sql_date_rows('ling');

10、条件处理（捕捉异常并处理）

1.语法格式：

DECLARE handler_name HANDLER FOR condition_value  action_statement

#中文注释
DECLARE 异常处理选项  HANDLER FOR    捕获的异常      异常发生时执行的语句

#样例
#        异常处理选项                  捕获的异常           异常发生时执行的语句
declare    exit        handler for   not found          close sql_schema_table;
declare    exit        handler for   SQLSTATE '02000'   close sql_schema_table;

1、handler_name 表示异常处理程序的名称，重要取值包括：
CONTINUE：发生异常后，继续执行存储过程。(捕捉)
EXIT：发生异常后，终止存储过程的执行。（上抛）

2、condition_value 是指捕获的异常：
        SQLSTATE sqlstate_value，sqlstate_value是指状态码，样例：SQLSTATE ‘02000’。

也可以用下面的代码简写：
        SQLWARNING，代表所有01开头的SQLSTATE，01是指警告的报错信息。
        NOT FOUND，代表所有02开头的SQLSTATE，02是指无数据的报错信息。
        SQLEXCEPTION，除了01和02开头的所有SQLSTATE。

3、action_statement 是指异常发生时执行的语句，例如：CLOSE sql_schema_table

2.语句样例

1）样例1

根据传入的数据库名，查询该数据库所有表的信息，并插入对应的结果存放表中。

定义变量：先定义需要字段的变量，用于后面游标获取数据后进行赋值。

声明游标：游标用来查询传入的数据库名的数据库所有表的信息。

条件处理：根据前面执行的报错信息，添加对应的条件处理。

建表清表：建一个结果存放表。并且清空下结果存放表，用于每次执行都是新的数据。

打开游标：使用游标前，要先打开游标。

循环处理：使用游标获取数据，并进行处理。

关闭游标：循环处理结束后，进行关闭游标。

#改变语句结束符为//。
DELIMITER //
#创建存储过程名称([ 参数列表 ])。参数列表可以为空。不需要参数的可把参数去了
create procedure name_sql_date(IN nname varchar(25))

#存储过程执行代码的开头
begin 
	#定义变量
	DECLARE table_schema_1 varchar(255);
	DECLARE table_name_1 varchar(255);
	DECLARE table_comment_1 varchar(255);
	DECLARE table_rows_1 varchar(255);
	DECLARE data_size_1 varchar(255);
	DECLARE create_time_1 varchar(255);
	DECLARE update_time_1 varchar(255);
	
	#声明游标
	declare sql_schema_table cursor for select table_schema,table_name,table_comment,table_rows,( data_length / 1024 ) as data_size,create_time,update_time from information_schema.`tables` where  table_schema = nname;
	
    #条件处理
	declare exit handler for not found close sql_schema_table;

	#创建结果存放表，字段顺序与上面查询语句的字段顺序一样
	    create table if not exists `schema_table` (
        `table_schema` VARCHAR ( 255 ) DEFAULT NULL COMMENT '数据库名称',
        `table_name` VARCHAR ( 257 ) DEFAULT NULL COMMENT '表英文名称',
        `table_comment` VARCHAR ( 258 ) DEFAULT NULL COMMENT '表中文名称',
        `table_rows` VARCHAR ( 259 ) DEFAULT NULL COMMENT '数据行数',
        `data_size` VARCHAR ( 260 ) DEFAULT NULL COMMENT '表大小',
        `create_time` VARCHAR ( 261 ) DEFAULT NULL COMMENT '创建时间',
        `update_time` VARCHAR ( 262 ) DEFAULT NULL COMMENT '更新时间'
    ) ENGINE = INNODB DEFAULT CHARSET = utf8 COMMENT = '数据库-数据表相关信息';
	
	#清空结果存放表
	truncate table schema_table;
	
	#打开游标
	open sql_schema_table;
	
	#循环，true表示循环所有
  while true do
		#游标获取数据，并进行赋值。
        fetch sql_schema_table into table_schema_1,table_name_1,table_comment_1,table_rows_1,data_size_1,create_time_1,update_time_1;
		#将得到的数据进行插入结果存放表
        insert into schema_table(table_schema,table_name,table_comment,table_rows,data_size,create_time,update_time) values (table_schema_1,table_name_1,table_comment_1,table_rows_1,data_size_1,create_time_1,update_time_1);
  end while;

	#关闭游标
	close sql_schema_table;
	
#存储过程执行代码的结尾
end //
#改变语句结束符为;。是将语句结束符改回;。
DELIMITER ;

call name_sql_date('数据库名');

如果没加条件处理，运行存储过程后，会发现报错1329 - No data - zero rows fetched, selected, or processed。加条件处理后，能顺利执行无报错。

存储过程（详细-附样例）

一、存储过程相关命令

二、存储过程

1、基本的存储过程

1.基本样例

2. 基本样例语句命令详细说明

1）delimiter

2)create procedure 存储过程名称( [参数列表] )

3）begin、end

2、参数

1. 语法格式：

2.参数样例

3、变量

1. 语法格式：

2.变量样例

4、if 流程控制

1.语法格式：

2.if 语句样例

1）样例1

2）样例2

5、case 条件控制

1.语法格式：

1）格式1

2）格式2

2.case 语句样例

1）样例1

2）样例2

6、while 循环语句

1.语法格式：

2.while 语句样例

1）样例1

2）样例2

7、repeat 循环语句

1.语法格式：

2.repeat 语句样例

1）样例1

2）样例2

8、loop 循环语句

1.语法格式：

2.loop 语句样例

1）样例1

2）样例2

9、游标

1.语法格式：

2.游标 语句样例

1）样例1

2）样例2

10、条件处理（捕捉异常并处理）

1.语法格式：

2.语句样例

1）样例1

网站公告

今日签到

热门文章

最新发布

2.游标语句样例