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

喜欢ヅ旅行 2023-08-17 17:44 158阅读 0赞

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

--------------------

## 什么是存储过程 ##

存储过程是一组为了完成某项特定功能的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

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

<table> 
 <thead> 
  <tr> 
   <th style="text-align:center;">存储函数</th> 
   <th style="text-align:center;">存储过程</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td style="text-align:center;">不能拥有输出参数</td> 
   <td style="text-align:center;">可以拥有输出参数</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;">必须包含一条RETURN语句</td> 
   <td style="text-align:center;">不允许包含RETURN语句</td> 
  </tr> 
  <tr> 
   <td style="text-align:center;">可以直接调用存储函数，不需要CALL语句</td> 
   <td style="text-align:center;">需要CALL语句调用存储过程</td> 
  </tr> 
 </tbody> 
</table>

转载于:https://www.cnblogs.com/SunArmy/p/11417300.html