MySQL存储过程和存储函数_mysql 存储过 call proc_stat_data(3,null)

2）很难调试存储过程。只有少数数据库管理系统允许调试存储过程。不幸的是，MySQL不提供调试存储过程的功能。

1.2 数据准备

• 创建数据库：

DEFAULT CHARACTER SET utf8;
use test;

这里记得设置编码！

• 创建测试表：

DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (`id` int(11) NOT NULL AUTO\_INCREMENT,`name` varchar(30) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO\_INCREMENT=4 DEFAULT CHARSET=utf8;insert  into `class`(`id`,`name`) values 
(1,'Java'),
(2,'UI'),
(3,'产品');DROP TABLE IF EXISTS `student`;CREATE TABLE `student` (`id` int(11) NOT NULL AUTO\_INCREMENT,`name` varchar(20) DEFAULT NULL,`class\_id` int(11) DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO\_INCREMENT=6 DEFAULT CHARSET=utf8;/\*Data for the table `student` \*/insert  into `student`(`id`,`name`,`class\_id`) values 
(1,'张三',1),
(2,'李四',1),
(3,'王五',2),
(4,'赵刘',1),
(5,'钱七',3);

• 查询数据：

select \* from class;select \* from student;

1.3 存储过程的使用

• 语法

CREATE PROCEDURE procedure_name ([parameters[,...]])
begin
-- SQL语句
end ;

• 示例

create procedure test1()
beginselect 'Hello';
end;

• 调用存储过程

call test1();

• 查看存储过程

-- 查看db01数据库中的所有存储过程
select name from mysql.proc where db='test';-- 查看存储过程的状态信息
show procedure status;-- 查看存储过程的创建语句
show create procedure test1;

• 删除存储过程

drop procedure test1;

1.2 存储过程的语法

1.2.1 变量

• declare：声明变量

CREATE PROCEDURE test2 ()
begindeclare num int default 0;		-- 声明变量,赋默认值为0select num+10;end ;call test2();			-- 调用存储过程

• set：赋值操作

CREATE PROCEDURE test3 ()
begindeclare num int default 0;set num =20;			-- 给num变量赋值select num;end ;call test3();

• into：赋值

CREATE PROCEDURE test4 ()
begindeclare num int default 0;			select count(1) into num from student;select num;
end ;call test4();

1.2.2 if语句

• 需求：根据class_id判断是Java还是UI还是产品

CREATE PROCEDURE test5 ()
begindeclare id int default 1;			declare class_name varchar(30);if id=1 thenset class_name='哇塞，Java大佬！';elseif id=2 thenset class_name='原来是UI的啊';elseset class_name='不用想了，肯定是产品小样';end if;select class_name;
end ;call test5();

1.2.3 传递参数

• 语法：

create procedure procedure_name([in/out/inout] 参数名  参数类型)

• in：该参数可以作为输入，也就是需要调用方传入值 , 默认
• out：该参数作为输出，也就是该参数可以作为返回值
• inout：既可以作为输入参数，也可以作为输出参数

1.2.3.1 in-输入参数

-- 定义一个输入参数
CREATE PROCEDURE test6 (in id int)
begindeclare class_name varchar(30);if id=1 thenset class_name='哇塞，Java大佬！';elseif id=2 thenset class_name='原来是UI的啊';elseset class_name='不用想了，肯定是产品小样';end if;select class_name;
end ;call test6(3);

1.2.3.2 out-输出参数

-- 定义一个输入参数和一个输出参数
CREATE PROCEDURE test7 (in id int,out class_name varchar(100))
beginif id=1 thenset class_name='哇塞，Java大佬！';elseif id=2 thenset class_name='原来是UI的啊';elseset class_name='不用想了，肯定是产品小样';end if;end ;call test7(1,@class\_name);	-- 创建会话变量 select @class\_name;		-- 引用会话变量

@xxx：代表定义一个会话变量，整个会话都可以使用，当会话关闭（连接断开）时销毁
@@xxx：代表定义一个系统变量，永久生效。

1.2.4 case语句

• 需求：传递一个月份值，返回所在的季节。

CREATE PROCEDURE test8 (in month int,out season varchar(10))
begincase when month >=1 and month<=3 thenset season='spring';when month >=4 and month<=6 thenset season='summer';when month >=7 and month<=9 thenset season='autumn';when month >=10 and month<=12 thenset season='winter';end case;
end ;call test8(9,@season);			-- 定义会话变量来接收test8存储过程返回的值select @season;

1.3.5 while循环

• 需求：计算任意数的累加和

CREATE PROCEDURE test10 (in count int)
begindeclare total int default 0;declare i int default 1;while i<=count doset total=total+i;set i=i+1;end while;select total;
end ;call test10(10);

1.3.6 repeat循环

• 需求：计算任意数的累加和

CREATE PROCEDURE test11 (count int)		-- 默认是输入(in)参数
begindeclare total int default 0;repeat set total=total+count;set count=count-1;until count=0				-- 结束条件,注意不要打分号end repeat;select total;
end ;call test11(10);

1.3.7 loop循环

• 需求：计算任意数的累加和

CREATE PROCEDURE test12 (count int)		-- 默认是输入(in)参数
begindeclare total int default 0;	sum:loop							-- 定义循环标识set total=total+count;set count=count-1;if count < 1 thenleave sum;					-- 跳出循环end if;end loop sum;						-- 标识循环结束select total;end ;call test12(10);

1.3.8 游标

游标是用来存储查询结果集的数据类型，可以帮我们保存多条行记录结果，我们要做的操作就是读取游标中的数据获取每一行的数据。

• 声明游标

declare cursor_name cursor for statement;

• 打开游标

open cursor_name;

• 关闭游标

close cursor_name;

• 案例：

CREATE PROCEDURE test13 ()		-- 默认是输入(in)参数
begindeclare id int(11);declare `name` varchar(20);declare class_id int(11);-- 定义游标结束标识符declare has_data int default 1;declare stu_result cursor for select \* from student;-- 监测游标结束declare exit handler for not FOUND set has_data=0;-- 打开游标open stu_result;repeat fetch stu_result into id,`name`,class_id;select concat('id: ',id,';name: ',`name`,';class\_id',class_id);until has_data=0		-- 退出条件,注意不要打分号end repeat;-- 关闭游标close stu_result;end ;call test13();

1.3 存储过程和存储函数的区别

• 存储函数的限制比较多，例如不能用临时表,只能用表变量，而存储过程的限制较少，存储过程的实现功能要复杂些,而函数的实现功能针对性比较强。
• 返回值不同。存储函数必须有返回值,且仅返回一个结果值；存储过程可以没有返回值,但是能返回结果集(out，inout)。
• 调用时的不同。存储函数嵌入在SQL中使用，可以在select 存储函数名(变量值)；存储过程通过call语句调用 call 存储过程名。
• 参数的不同。存储函数的参数类型类似于IN参数，没有类似于OUT和INOUT的参数。存储过程的参数类型有三种，in、out和inout：
  • in：数据只是从外部传入内部使用(值传递),可以是数值也可以是变量
  • out：只允许过程内部使用(不用外部数据),给外部使用的(引用传递:外部的数据会被先清空才会进入到内部),只能是变量
  • inout：外部可以在内部使用,内部修改的也可以给外部使用,典型的引用 传递,只能传递变量。

1.3.1 临时表

临时表顾名思义就是临时要用创建的表，临时表的作用仅限于本次会话，等连接关闭后重新打开连接临时表将不存在

• 创建一张临时表：

create temporary table temp_table(id int,name varchar(10)
);
insert into temp_table values (1,'1');select \* from temp_table ;

temporary：代表创建的表是一张临时表；

• 注意：临时表示查询不到的

show tables;   -- 不会显示临时表的存在

• 测试存储过程创建临时表：

create procedure pro1()
begincreate temporary table temp_table(id int);insert into temp_table values(1);select \* from temp_table;
end;