dev-zuo 笔记

# 数据库编程

# 存储过程

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

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

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

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

特点：

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

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

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

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

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

DELIMITER !!

# mysql> select * from customers_view!!

# 回到分号结束
DELIMITER ;

# CREATE PROCEDURE 创建

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

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

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

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

# 存储过程体(简单应用)

# 使用DECLARE声明局部变量

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

DECLARE var_name[...] type [DEFAULT value]
# 1.只能在存储过程体的BEGIN ... END语句块声明变量
# 2.必须在存储过程的开头处声明
# 3.作用范围仅限于声明它的BEGIN...END语句块
# 4.不同于用户变量

# 例子：声明一个整型局部变量cid
DECLARE cid INT(10);

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

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

# 使用set为局部变量赋值

SET var_name=expr[,var_name=expr]...

# 给局部变量cid赋予一个整数值 910
SET cid = 910;

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

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

# 流程控制语句

  1. 条件判断语句 IF...THEN...ELSE语句 CASE语句
  2. 循环语句 WHILE、REPEAT、LOOP语句
IF 条件 THEN
  表达式
ELSE
  表达式

WHILE 条件
  表达式
END WHILE

REPEAT
  表达式
END REPEAT

LOOP
  表达式
END LOOP

ITERATE语句 用于表示退出当前循环

# 使用DECLARE CURSOR语句创建游标

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

# 使用OPEN打开游标

OPEN cusor_name # 指定要打开的游标

# 使用FETCH...INTO语句读取数据

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

# 使用CLOSE关闭游标

CLOSE cursor_name 
# cursor_name 用于要关闭的游标

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

使用CALL语句调用存储过程

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

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

# 之前创建的存储过程 sp_update_sec
USE mysql_test;
DELIMITER $$
CREATE PROCEDURE sp_update_sex(IN cid INT,IN csex CHAR(1)) 
BEGIN
UPDATE mysql_test.customers SET cust_sex=csex WHERE cust_id=cid;
END $$
DELIMITER ;

# 调用存储过程
CALL sp_update_sex(909, 'F')

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

DROP PROCEDURE

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创建存储函数

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

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

# deterministic [dɪˌtɜːmɪˈnɪstɪk] 确定性的
# DETERMINISTIC 
# USE mysql_test;
DELIMITER $$
CREATE FUNCTION get_sex_by_id(cid INT)
  RETURNS VARCHAR(10) 
  DETERMINISTIC
BEGIN
  DECLARE SEX VARCHAR(2);
  SELECT cust_sex FROM customers WHERE cust_id=cid INTO SEX;
  IF SEX IS NULL THEN
    RETURN '没有该客户';
  ELSE IF SEX='F' THEN
    RETURN '女';
  ELSE 
    RETURN '男';
  END IF;
  END IF;
END $$
DELIMITER ;

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

使用SELECT调用存储函数

SELECT sp_name([func_parameter[,...]])

SELECT get_sex_by_id(901);

# mysql> SELECT get_sex_by_id(909);
+--------------------+
| get_sex_by_id(909) |
+--------------------+
| 女                 |
+--------------------+
1 row in set, 1 warning (0.01 sec)

# mysql> SELECT get_sex_by_id(9100);
+---------------------+
| get_sex_by_id(9100) |
+---------------------+
| 没有该客户          |
+---------------------+
1 row in set (0.00 sec)

# mysql> SELECT get_sex_by_id(904) as gender;
+--------+
| gender |
+--------+
| 男     |
+--------+
1 row in set (0.00 sec)

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

使用DROP FUNCTION 删除存储函数

DROP FUNCTION [IF EXISTS] sp_name

DROP FUNCTION IF EXISTS get_sex_by_id;

# 练习题

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

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

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

# 语法
SELECT sp_name([param1[,param2,...]])
上次更新: 2020/10/29 22:59:19

← 数据库安全与保护 →