数据库之存储过程和存储函数(六)

数据库之存储过程和存储函数(六)

---

什么是存储过程

​ 存储过程是一组为了完成某项特定功能的SQL语句集，其实质就是一段存储在数据库中的代码。它可以由声明式的sql语句和过程式sql语句组成。

优点

 		1. 可以增强sql语言的功能和灵活性 
 		2. 良好的封装性
 		3. 高性能
 		4. 减少网络流量
 		5. 可作为一种安全机制来确保数据库的安全性和数据的完整性

创建存储过程

-- 自定义结束符$$
DELIMITER $$

-- 创建存储过程
CREATE PROCEDURE sp_name([proc_parameter[,…]]) -- sp_name存储过程的名称
routine_body	-- 存储过程的主体部分，也称为存储过程体
-- 例：
CREATE PROCEDURE sp_update_sex(IN cid INT,IN csex CHAR(1))
    BEGIN
       UPDATE customers SET cust_sex=csex WHERE cust_id=cid;
    END $$
-- 使用DECLARE语句声明局部变量
DECLARE var_name[,…] type [DEFAULT value]
例：DECLARE var_name[,…] type [DEFAULT value]
-- 使用DECLARE语句声明局部变量
1）只能在存储过程体的BEGIN…END语句块中声明；
2）必须在存储过程的开头处声明；
3）作用范围仅限于声明它的BEGIN…END语句块；
4）不同于用户变量

局部变量与用户变量的区别：

1）局部变量声明时，在其前面没有@符号，并且它只能

​ 被声明它的BEGIN…END语句块中的语句所使用；

2）用户变量在声明时，会在其名称前面使用@符号，同

​ 时已声明的用户变量存在于整个会话之中。

-- 使用SET语句为局部变量赋值
SET var_name=expr[,var_name=expr]…
例：SET cid=910;
-- 使用SELECT…INTO语句把选定列的值直接存储到局部变量中
SELECT col_name[,…] INTO var_name[,…] table_expr

流程控制语句

1、条件判断语句
	IF 条件 THEN 表达式1 ELSE 表达式2 end if
	CASE语句
2、循环语句
	WHILE语句
例： WHILE 条件 表达式 END WHILE
	REPEAT语句
例：repeat 表达式 END repeat
	LOOP语句
例：loop 表达式 END loop
3、结束语句
	ITERATE语句  -- 用于表示退出当前循环
4、使用DECLARE CURSOR语句创建游标
    DECLARE cursor_name CURSOR FOR select_statement
5、使用OPEN语句打开游标
    OPEN cursor_name
6、使用FETCH…INTO语句读取数据
    FETCH cursor_name INTO var_name[,var_name] …
7、使用CLOSE语句关闭游标
    CLOSE cursor_name 
8、使用CALL语句调用存储过程
    CALL sp_name([parameter[,…]])
    CALL sp_name[()]
例：> CALL sp_update_sex(909,’M’);
9、使用DROP PROCEDURE语句删除存储过程
    DROP PROCEDURE[IF EXISTS] sp_name

什么是存储函数

存储函数与存储过程一样，是由SQL语句和过程式语句组成的代码片段。

使用CREATE FUNCTION语句创建存储函数

 CREATE FUNCTION sp_name([func_parameter[,…]]) 
 RETURNS type -- 声明存储函数返回值的数据类型； type指定返回值的数据类型 
 routine_body -- 指定存储函数的主体部分，也称为存储函数体 
 例：
 Use mysql_test; 
 DELIMITER $$ 
 CREATE FUNCTION fn_search(cid INT)  RETURNS CHAR(20)  
 DETERMINISTIC 
 BEGIN  
 DECLARE SEX CHAR(20);  
 SELECT cust_sex  INTO SEX FROM  customers WHERE  cust_id=cid;  
 IF SEX IS NULL  THEN   RETURN(SELECT’没有该客户’);  
 ELSE IF SEX=‘F’ THEN   RETURN(SELECT’女’);            
 ELSE   RETURN(SELECT  ‘男’);
 END IF;                    
 END IF; END $$ 

使用关键字SELECT调用存储函数

 SELECT sp_name([func_parameter[,…]]) 

使用DROP FUNCTION语句删除存储函数

DROP FUNCTION [IF EXISTS] sp_name 

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

存储函数	存储过程
不能拥有输出参数	可以拥有输出参数
必须包含一条RETURN语句	不允许包含RETURN语句
可以直接调用存储函数，不需要CALL语句	需要CALL语句调用存储过程