存储过程

存储过程的基本概念(领会)

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

声明式的SQL语句：CREATE、UPDATE、SELECT等

过程式的SQL语句：CASE IF ELSE 等

特点：

  • 可增强SQL语言的功能和灵活性
  • 良好的封装性
  • 高性能
  • 可减少网络流量
  • 可作为一种安全机制来确保数据库的安全性和数据的完整性

创建存储过程(综合应用)

DELIMITER命令，delimiter [dɪ’lɪmɪtə] 定界符，一般语句遇到 ; 就结束执行SQL语句

  1. DELIMITER $$ # $$用户定义的结束符号

示例：将MySQL结束符修改为两个感叹号 “!!”

  1. DELIMITER !!
  3. # mysql> select * from customers_view!!
  5. # 回到分号结束
  6. DELIMITER ;

CREATE PROCEDURE 创建

procedure [prəˈsiːdʒə(r)] 程序，步骤

  1. # 语法
  2. CREATE PROCEDURE sp_name([proc_parameter[,...]]) routine_body
  3. # 默认创建存储过程在当前的数据库
  4. # sp_name 指定存储过程的名称，默认在当前数据库中创建
  5. # proc_parameter 指定存储过程的参数列表 [IN | OUT | INOUT] param_name type 参数名 参数类型
  6. # routine [ruːˈtiːn] 惯例，例行程序，存储过程的主题部分，也称为存储过程体。

示例：在mysql_test中创建一个存储过程，用于实现给定表customers中一个客户id号即可修改表customers中该客户的性别为一个指定的性别

  1. USE mysql_test;
  2. DELIMITER $$
  3. CREATE PROCEDURE sp_update_sex(IN cid INT,IN csex CHAR(1)) 
  4. BEGIN
  5. UPDATE mysql_test.customers SET cust_sex=csex WHERE cust_id=cid;
  6. END $$

存储过程体(简单应用)

使用DECLARE声明局部变量

desclare [dɪˈkleə(r)] 声明

  1. DECLARE var_name[...] type [DEFAULT value]
  2. # 1.只能在存储过程体的BEGIN ... END语句块声明变量
  3. # 2.必须在存储过程的开头处声明
  4. # 3.作用范围仅限于声明它的BEGIN...END语句块
  5. # 4.不同于用户变量
  7. # 例子：声明一个整型局部变量cid
  8. DECLARE cid INT(10);

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

  1. 用户变量前面有@，局部变量前面没@
  2. 可用范围：用户变量在整个SQL里都可以使用，局部变量只能在BEGIN…END语句块中使用

使用set为局部变量赋值

  1. SET var_name=expr[,var_name=expr]...
  3. # 给局部变量cid赋予一个整数值 910
  4. SET cid = 910;

使用SELECT…INTO把选定列的值直接存储到局部变量中

  1. SELECT col_name[,...] INTO var_name[,...] table_expr
  2. # col_name 指定列名
  3. # var_name 指定要赋值的变量名
  4. # table_expr 表示SELECT语句中的FROM子句及后面的语法部分

流程控制语句

  1. 条件判断语句 IF…THEN…ELSE语句 CASE语句
  2. 循环语句 WHILE、REPEAT、LOOP语句
  1. IF 条件 THEN
  2.   表达式
  3. ELSE
  4.   表达式
  6. WHILE 条件
  7.   表达式
  8. END WHILE
  10. REPEAT
  11.   表达式
  12. END REPEAT
  14. LOOP
  15.   表达式
  16. END LOOP
  18. ITERATE语句 用于表示退出当前循环

使用DECLARE CURSOR语句创建游标

  1. DECLEAR cusor_name CURSOR FOR select_statement
  2. # cursor_name 指定要创建的游标的名称

使用OPEN打开游标

  1. OPEN cusor_name # 指定要打开的游标

使用FETCH…INTO语句读取数据

  1. FETCH cursor_name INTO var_name[,val_name]...
  2. # cursor_name 指定已打开的游标
  3. # var_name 指定存放数据的变量名

使用CLOSE关闭游标

  1. CLOSE cursor_name 
  2. # cursor_name 用于要关闭的游标

调用存储过程(综合应用)

使用CALL语句调用存储过程

  1. CALL sp_name([parameter[,...]])
  2. CALL sp_name[()]
  3. # sp_name 被调用存储过程的名称

示例：调用数据库mysql_test中的存储过程sp_update_sex，将客户id号为909的客户性别修改为女性 ‘F’

  1. # 之前创建的存储过程 sp_update_sec
  2. USE mysql_test;
  3. DELIMITER $$
  4. CREATE PROCEDURE sp_update_sex(IN cid INT,IN csex CHAR(1)) 
  5. BEGIN
  6. UPDATE mysql_test.customers SET cust_sex=csex WHERE cust_id=cid;
  7. END $$
  8. DELIMITER ;
  10. # 调用存储过程
  11. CALL sp_update_sex(909, 'F')

删除存储过程(简单应用)

DROP PROCEDURE

  1. DROP PROCEDURE [IF EXIST] sp_name

练习题

1.存储过程是一组为了完成某项特定功能的SQL语句集，其实质就是一段存储在数据库中的（ ）。答案：代码

2.创建存储过程使用的语句是（ ）。单选题，A

  • A CREATE PROCEDURE
  • B DROP PROCEDURE
  • C CALL PROCEDURE
  • D DECLARE PROCEDURE

3.下面哪个不是循环语句（ ）。单选题，B

  • A WHILE
  • B CASE
  • C REPEAT
  • D LOOP

4.DECLARE CURSOR语句的作用是（ ）。单选题 B

  • A 打开游标
  • B 声明游标
  • C 读取数据
  • D 关闭游标

存储函数

什么是存储函数(领会)

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

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

创建存储函数(综合应用)

使用CREATE FUNCTION创建存储函数

  1. CREATE FUNCTION sp_name([func_parameter[,...]])
  2. RETURNS type routine_body
  3. # sp_name 指定存储函数的名称
  4. # RETURNS type 声明存储函数返回值的数据类型；type指定返回值的数据类型
  5. # routine_body 指定存储函数的主题部分，也称为存储函数体

示例：在数据库mysql_test中创建一个存储函数，要求该函数能根据给定的客户id号返回客户的性别，如果数据库中没有给定的客户id号，则返回 “没有该客户”

  1. # deterministic [dɪˌtɜːmɪˈnɪstɪk] 确定性的
  2. # DETERMINISTIC 
  3. # USE mysql_test;
  4. DELIMITER $$
  5. CREATE FUNCTION get_sex_by_id(cid INT)
  6.   RETURNS VARCHAR(10) 
  7.   DETERMINISTIC
  8. BEGIN
  9.   DECLARE SEX VARCHAR(2);
  10.   SELECT cust_sex FROM customers WHERE cust_id=cid INTO SEX;
  11.   IF SEX IS NULL THEN
  12.     RETURN '没有该客户';
  13.   ELSE IF SEX='F' THEN
  14.     RETURN '女';
  15.   ELSE 
  16.     RETURN '男';
  17.   END IF;
  18.   END IF;
  19. END $$
  20. DELIMITER ;

调用存储函数(综合应用)

使用SELECT调用存储函数

  1. SELECT sp_name([func_parameter[,...]])
  3. SELECT get_sex_by_id(901);
  5. # mysql> SELECT get_sex_by_id(909);
  6. +--------------------+
  7. | get_sex_by_id(909) |
  8. +--------------------+
  9. | 女                 |
  10. +--------------------+
  11. 1 row in set, 1 warning (0.01 sec)
  13. # mysql> SELECT get_sex_by_id(9100);
  14. +---------------------+
  15. | get_sex_by_id(9100) |
  16. +---------------------+
  17. | 没有该客户          |
  18. +---------------------+
  19. 1 row in set (0.00 sec)
  21. # mysql> SELECT get_sex_by_id(904) as gender;
  22. +--------+
  23. | gender |
  24. +--------+
  25. | 男     |
  26. +--------+
  27. 1 row in set (0.00 sec)

删除存储函数(简单应用)

使用DROP FUNCTION 删除存储函数

  1. DROP FUNCTION [IF EXISTS] sp_name
  3. DROP FUNCTION IF EXISTS get_sex_by_id;

练习题

1.创建存储函数会用的语句是（ ）。单选题，答案：C

  • A CREATE PROCEDURE
  • B DROP PROCUDURE
  • C CREATE FUNCTION
  • D DROP FUNCTION

2.写出调用存储函数的语法格式。简答题

  1. SELECT sp_name([param1[,param2,...]])