《存储过程和存储函数》

蔚落 2023-01-02 11:27 280阅读 0赞

# 存储过程和存储函数 #

MySQL中提供存储过程与存储函数机制，我们先将其统称为**存储程序**，一般的SQL语句需要先编译然后执行，存储程序是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，当用户通过指定存储程序的名字并给定参数（如果该存储程序带有参数）来调用才会执行。

## 1.1 存储程序优缺点 ##

*  优点

通常存储过程有助于提高应用程序的性能。当创建，存储过程被编译之后，就存储在数据库中。 但是，MySQL实现的存储过程略有不同。 MySQL存储过程需要编译。 在编译存储过程之后，MySQL将其放入缓存中。 MySQL为每个连接维护自己的存储过程高速缓存。 如果应用程序在单个连接中多次使用存储过程，则使用编译版本

1）**性能：** 存储过程有助于减少应用程序和数据库服务器之间的流量，因为应用程序不必发送多个冗长的SQL语句，而只能发送存储过程的名称和参数。

2）**复用：** 存储的程序对任何应用程序都是可重用的和透明的。 存储过程将数据库接口暴露给所有应用程序，以便开发人员不必开发存储过程中已支持的功能。

3）**安全：** 存储的程序是安全的。 数据库管理员可以向访问数据库中存储过程的应用程序授予适当的权限，而不向基础数据库表提供任何权限。

*  缺点

1）如果使用大量存储过程，那么使用这些存储过程的每个连接的内存使用量将会大大增加。 此外，如果在存储过程中过度使用大量逻辑操作，则CPU使用率也会增加。

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

3）我们在开发中，性能的瓶颈往往就是数据库层，我们应该想尽办法来优化数据库的性能，而不是徒增数据库的压力。

## 1.2 存储过程的使用 ##

*  语法

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

*  示例

create procedure test1()
    begin
    	select 'Hello';
    end;

*  调用存储过程

call test1();

*  查看存储过程

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

*  删除存储过程

drop procedure test1;

## 1.2 存储过程的语法 ##

### 1.2.1 变量 ###

*  declare：声明变量

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

*  set：赋值操作

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

*  into：赋值

CREATE PROCEDURE test4 ()
    begin
    	
    	declare 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 ()
    begin
    	
    	declare id int default 1;			
    	declare class_name varchar(30);
    	
    	if id=1 then
    		set class_name='哇塞，Java大佬！';
    	elseif id=2 then
    		set class_name='原来是UI的啊';
    	else
    		set class_name='不用想了，肯定是产品小样';
    	end if;
    	
    	select class_name;
    	
    end ;
    
    call test5();

### 1.2.3 传递参数 ###

*  语法：

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

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

#### in-输入参数 ####

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

#### out-输出参数 ####

-- 定义一个输入参数和一个输出参数
    CREATE PROCEDURE test7 (in id int,out class_name varchar(100))
    begin
    	if id=1 then
    		set class_name='哇塞，Java大佬！';
    	elseif id=2 then
    		set class_name='原来是UI的啊';
    	else
    		set 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))
    begin
    	
    	case 
    		when month >=1 and month<=3 then
    			set season='spring';
    		when month >=4 and month<=6 then
    			set season='summer';
    		when month >=7 and month<=9 then
    			set season='autumn';
    		when month >=10 and month<=12 then
    			set season='winter';
    	end case;
    end ;
    
    call test8(9,@season);			-- 定义会话变量来接收test8存储过程返回的值
    
    select @season;

### 1.3.5 while循环 ###

*  需求

计算任意数的累加和

CREATE PROCEDURE test10 (in count int)
    begin
    	declare total int default 0;
    	declare i int default 1;
    	
    	while i<=count do
    		set 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)参数
    begin
    	declare 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)参数
    begin
    	declare total int default 0;	
    	sum:loop							-- 定义循环标识
    		set total=total+count;
    		set count=count-1;
    		
    		if count < 1 then
    			leave sum;					-- 跳出循环
    		end if;
    	end loop sum;						-- 标识循环结束
    	select total;
    	
    end ;
    
    call test12(10);

### 1.3.8 游标 ###

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

*  声明游标

declare cursor_name cursor for statement;

*  打开游标

open cursor_name;

*  读取游标

fetch cursor_name;

*  关闭游标

close cursor_name;

*  案例：

CREATE PROCEDURE test13 ()		-- 默认是输入(in)参数
    begin
    	
    	declare 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');

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

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

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

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

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

运行没有任何问题

*  测试存储函数创建临时表

create function fun2()
    returns int
    begin
    
    	declare id int ;
    	create table temp_table(				
    		id int
    	);
    	
    	insert into temp_table values(1);
    	
    	select id from into id temp_table;	
    	return id;
    end

发现报错。

*  创建一个普通的存储函数：

create function test1()
    returns int
    begin
    
    	declare num int default 0;
    	
    	set num=10;
    	
    	return num;
    end;

*  执行存储函数：

select test1()