[数据库系统原理] 第 5 章 数据库编程
在实际应用中完成一个完整操作需要多条语句来处理多个表才能实现。
使用的技术:存储过程 和 存储函数。
第一节 存储过程
解决大量多次重复输入相同SQL代码的状况。
将一些固定的操作集合起来,由DB服务器统一执行。
一 存储过程的基本概念
存储过程是一组为了完成某项特定功能的SQL语句集,实质上就是一段存储在DB中的代码。
存储过程可由声明式SQL语句(如CREATE、UPDATE和SELECT等语句)和过程式SQL语句(如IF... THEN ... ELSE ...控制结构语句)组成。
这些语句经过编译后存储在数据库中;用户只需要通过存储过程名字并给定所需的参数(如果需要参数的话),即可随时调用并执行它,而不必重新编译,可提高数据库操作语句的执行效率。
一个存储过程时一个可编程的函数,使用存储过程的好处是:
①可增强SQL语言的功能和灵活性。
②良好的封装性。
③高性能,执行一次后驻留与高速缓存,提升再次执行效率。
④减少网络流量,操作在服务器执行。
⑤可作为一种安全机制。
但它也是由明显缺点的:(课本上没有讲)
①如果使用许多存储过程,则使用这些存储过程的每个连接的内存使用量将显着增加。此外,如果在存储过程中过度使用大量逻辑操作,CPU使用率将会增加,因为数据库服务器没有针对逻辑操作进行良好设计。
②存储过程的构造不是为开发复杂和灵活的业务逻辑而设计的。
③调试存储过程很困难。只有少数数据库管理系统允许您调试存储过程。
④开发和维护存储过程并不容易。开发和维护存储过程通常需要一种并非所有应用程序开发人员都具备的专业技能。这可能会导致应用程序开发和维护阶段出现问题。
二 创建存储过程
下面是一个简单的存储过程。GetAllProducts()
存储过程选择所有产品从products
表。 启动mysql
客户端工具并键入以下命令:
DELIMITER // #也可以用你习惯的任何符号代替默认的语句结束标识(;);例如:$$
CREATE PROCEDURE GetAllProducts()
BEGIN
SELECT productCode, productName FROM products;
END // #与第一行定义的符号一致
DELIMITER ; #将默认的语句结束标识修改为默认值分号
语法:
CREATE PROCEDURE sp_name([{IN | OUT | INOUT} para1 type[, {IN | OUT | INOUT} para2 type]...])
routine_body
①sp_name:存储过程名;参数列表放在括号中,即使没有参数括号也必须存在。
②[{IN | OUT | INOUT} para1 type:三个字段依次代表参数类别标识、参数名、参数的值的类型。
- IN:输入参数,代表其后的参数是用来给存储过程传入数据的。
- OUT:输出参数,代表其后的参数用来存放存储过程的返回值。
- INOUT:即可用作输入又可用作输出。
③routine_body:存储过程的主体部分,也称存储过程体。以"BEGIN"开始,以"END"结束;当routine_body中只有一条SQL语句时"BEGIN"和"END"可以省略。且"BEGIN ... END"是可以嵌套的。
例如:创建一个通过给定cust_id修改cust_sex的存储过程。
USE mysql_test; # 切换到数据库
DELIMITER $$
CREATE PROCEDURE sp_01(IN cid INT,IN csex CHAR(1))
BEGIN
UPDATE customers SET cust_sex = csex WHERE cust_id = cid;
END $$
DELIMITER ;
三 存储过程体
1 局部变量
使用DECLARE语句来声明。
DECLARE var_name[,...] type [DEFAULT value]
使用DEFARLT指定默认值,未指定则未NULL。
例如:
DECLARE cid INT(10)
注意:
①只能在BEGIN和END之间使用。
②必须在存储过程体开头处声明。
③作用范围仅限在它自己所在的BEGIN...END内。
④不同于用户变量,局部变量前没有@
,且只在它自己所在的BEGIN...END内可用;用户变量声明时在变量名前加@
,且存活在整个会话过程中。
2 SET语句
声明变量后,即可开始使用它。要为变量赋一个值,请使用SET
语句,例如:
DECLARE count INT DEFAULT 0;
SET count = 10;
3 SELECT ... INTO语句
或者也可以使用:
DECLARE count INT DEFAULT 0;
SELECT
COUNT(*) INTO count
FROM
products;
注意:存储过程体中SELECT...INTO语句返回结果集中只能有一行数据。
4 流程控制语句(书中简略,这里多写了一些)
MySQL IF语句
可分为IF ... THEN... END IF、IF ... THEN ... ELSE ... END IF和IF .... THEN ... ELSEIF... THEN ... ELSE ... END IF三类。
语法:
IF expression THEN
statements;
...
ELSEIF elseif-expression THEN
elseif-statements;
...
...
ELSE
else-statements;
...
END IF;
以下示例说明了如何使用IF-ESLEIF-ELSE
语句。GetCustomerLevel()
存储过程接受两个参数的客户数量和客户的水平。
- 首先,它从
customers
表中获得信用额度。 - 然后,根据信用额度,它决定了客户等级:
PLATINUM
,GOLD
,和SILVER
。
参数p_customerlevel
存储客户的级别,并由调用程序使用。
DELIMITER $$
CREATE PROCEDURE GetCustomerLevel(
in p_customerNumber int(11),
out p_customerLevel varchar(10))
BEGIN
DECLARE x double; #在存储过程体开头
SELECT creditlimit INTO x
FROM customers
WHERE customerNumber = p_customerNumber;
IF x > 50000 THEN
SET p_customerLevel = 'PLATINUM';
ELSEIF (x <= 50000 AND x >= 10000) THEN
SET p_customerLevel = 'GOLD';
ELSEIF x < 10000 THEN
SET p_customerLevel = 'SILVER';
END IF;
END $$
DELIMITER ;
执行结果:
mysql> call GetCustomerLevel(141,@p_customerLevel);
Query OK, 1 row affected (0.00 sec)
mysql> select @p_customerLevel; #注意这里,存储过程返回的结果放在out p_customerLevel参数里;对MySQL而言可以通过,用户变量@p_customerLevel查询到返回值。
+------------------+
| @p_customerLevel |
+------------------+
| PLATINUM |
+------------------+
1 row in set (0.00 sec)
MySQL CASE语句
除了IF
语句之外,MySQL还提供了一个名为CASE
语句的替代条件语句。CASE
语句使代码更具可读性和效率。 CASE
声明有两种形式:简单和搜索CASE
语句。
CASE
语句的语法:
CASE case_expression
WHEN when_expression_1 THEN commands
WHEN when_expression_2 THEN commands
...
ELSE commands
END CASE;
将case_expression
的值与每个WHEN
子句中的when_expression
进行比较,例如when_expression_1
,when_expression_2
等。如果case_expression
和when_expression_n
的值相等,则执行相应的WHEN
分支中的命令(commands
)。 如果WHEN
子句中的when_expression
与case_expression
的值都不匹配,则ELSE
子句中的命令将被执行。
ELSE
子句是可选的。如果省略ELSE
子句,并且找不到匹配项,MySQL将引发错误。
MySQL循环语句
MySQL的循环语句包括WHILE
,REPEAT
和LOOP
运行反复基于条件的代码块。
WHILE DO循环
WHILE
语句的语法如下:
WHILE expression DO
statements
END WHILE;
例如:
DELIMITER $$
DROP PROCEDURE IF EXISTS test_mysql_while_loop$$
CREATE PROCEDURE test_mysql_while_loop ( )
BEGIN
DECLARE x INT;
DECLARE str VARCHAR ( 255 );
SET x = 1; #如果不初始化,则x时NULL,则会导致后续循环编程死循环。
SET str = '';
WHILE x <= 5 DO
SET str = CONCAT( str, x, ',' );
SET x = x + 1;
END WHILE;
SELECT str;
END $$
DELIMITER ;
测试一下test_mysql_while_loop
存储过程:
CALL test_mysql_while_loop();
+------------+
| str |
+------------+
| 1,2,3,4,5, |
+------------+
1 row in set (0.01 sec)
REPEAT循环
REPEAT
循环语句的语法如下:
REPEAT
statements;
UNTIL expression #特别注意,这里没有分号
END REPEAT;
例如:
DELIMITER $$
DROP PROCEDURE IF EXISTS mysql_test_repeat_loop $$
CREATE PROCEDURE mysql_test_repeat_loop ( ) BEGIN
DECLARE x INT;
DECLARE str VARCHAR ( 255 );
SET x = 1;
SET str = '';
REPEAT
SET str = CONCAT( str, x, ',' );
SET x = x + 1;
UNTIL x > 5
END REPEAT;
SELECT str;
END $$
DELIMITER ;
注意UNTIL
表达式中没有分号(;
)。
CALL mysql_test_repeat_loop();
+------------+
| str |
+------------+
| 1,2,3,4,5, |
+------------+
1 row in set (0.00 sec)
LOOP,LEAVE和ITERATE语句
有两个语句允许您控制循环:
-
LEAVE
语句允许您立即退出循环而无需等待检查条件。LEAVE
语句的作用类似于PHP,C / C ++和Java等其他语言中的break
语句。 -
ITERATE
语句允许您跳过其下的整个代码并开始新的迭代。ITERATE
语句类似于PHP,C / C ++和Java中的continue
语句。
MySQL还为您提供了LOOP
一个重复执行代码块的语句,并具有使用循环标签的额外灵活性。 以下是使用LOOP
循环语句的示例:
DELIMITER $$
DROP PROCEDURE IF EXISTS test_mysql_loop $$
CREATE PROCEDURE test_mysql_loop()
BEGIN
DECLARE x INT;
DECLARE str VARCHAR ( 255 );
SET x = 1;
SET str = '';
loop_label :LOOP
IF x > 10 THEN
LEAVE loop_label; #跳出整个循环,直接来到END LOOP处
END IF;
SET x = x + 1;
IF ( x MOD 2 ) THEN
ITERATE loop_label; #跳出本轮循环,进入下一次循环,来到loop_label :LOOP处
ELSE
SET str = CONCAT( str, x, ',' );
END IF;
END LOOP;
SELECT str;
END $$
DELIMITER ;
mysql> call test_mysql_loop();
+-------------+
| str |
+-------------+
| 2,4,6,8,10, |
+-------------+
1 row in set (0.00 sec)
在这个例子中,
- 存储过程仅构造具有偶数的字符串,例如,2,4和6。
- 我们
loop_label
在LOOP
声明之前放置了一个循环标签。 - 如果值
x
大于10
,则由于LEAVE
语句而终止循环。 - 如果the的值
x
是奇数,则ITERATE
语句忽略其下的所有内容并开始新的迭代。 - 如果the的值
x
是偶数,则ELSE
语句中的块将构建具有偶数的字符串。
5 游标CURSOR
在MySQL中,使用SELECT语句进行数检索,若该语句成功被执行,则返回一组称作结果集的数据行,该结果集可能拥有0行或多行数据,这些数据无法直接被一行一行地进行处理。如果需要逐行地处理结果集中的数据就需要使用游标。
游标是一个被SELECT语句检索出来的结果集。
在存储(打开)了游标后,应用程序或用户就可以根据需要滚动处理其中的数据。在MySQL里游标只能在存储过程或者存储函数中使用(很多其他的DBMS没有这个限制)。
在MySQL中使用游标的步骤:
(1)声明游标
声明游标的时候只是给予游标SELECT的定义,而不会执行SELECT操作。
语法:
DECLARE cursor_name CURSOR FOR select_statement;
cursor_name:游标名
select_statement:一个SELECT语句,但是不能有INTO子句。
(2)打开游标
本质是将游标连接到由SELECT语句返回的结果集中。这个过程就会执行SELECT语句了。
语法:
OPEN cursor_name;
一个可以被多次打开,由于其他用户或应用程序可能随时更新数据表,因此每次打开游标的结果集可能会不同。
(3)读取数据
对于填有数据的游标哦,可根据需要取出数据。
语法:
FETCH cursor_name INTO var1_name[, var2_name]...
它与SELECT ... INTO语句具有相同的意义。
语句中变量的数目必须等于声明游标时SELECT子句中选择列的数目。游标相当于一个指针,它指向当前的一行数据。
(4)关闭游标
使用游标后必须关闭;关闭的语法:
CLOSE cursor_name;
若没有明确关闭游标,MySQL将会在到达END语句时自动关闭它。
例:
DELIMITER $$
CREATE PROCEDURE sp_001(OUT row_count INT)
BEGIN
DECLARE cid INT; #声明一个局部变量,给后续代码使用。
DECALRE ROWS INT;
DECLARE found BOOLEAN DEFAULT TRUE; #声明一个局部变量found,默认值是TRUE;作为有没有发现新数据的标识
DECLARE cur_cid CURSOR FOR
SELECT cust_id FROM customers; #声明一个游标,存储的值时来自customers表的所有cust_id字段的值
DECLARE CONTINUE HANDLER FOR NOT FOUND #声明一个继续处理的句柄(程序);当检查测到NOT FOUND出现时,将变量found的值设置为FALSE
SET found = FALSE;
SET ROWS = 0;
OPEN cur_cid; #打开游标
FETCH cur_cid INTO cid; #读取游标数据
WHILE found DO #当变量found的值为TURE时执行循环体
SET ROWS = ROWS + 1;
FETCH cur_cid INTO cid;
END WHILE;
CLOSE cur_cid; #关闭游标
END $$
DELIMITER ;
上面的例子中使用存储过程并定义游标,循环读取游标中的数据,统计游标中存储的数据的行数。
书中这个例子时有些错误的,例如ROWS变量没有定义就赋值,肯定时不对的。
另外,WHILE的实现逻辑也可以改进,大家自行研究下吧。
关于句柄的声明示例:declare continue handler for sqlstate '02000' set done=1;
代表当SQLSTATE '02000' 出现时,将变量done的值设置为1。
调用上面的存储过程:
mysql> CALL sp_001(@rows)
查看结果:
mysql> SELECT @rows
有几点需要说明:
①CONTINUE HANDLER:是一个继续执行句柄;它定义当条件出现时(FOR后面跟的特征值)需要执行的代码;用来控制循环语句。
②DECLARE语句定义的局部变量必须在游标和句柄定义之前;它们之间的定义顺序:
声明局部变量 >> 声明游标 >> 声明句柄
关于游标的使用:
①MySQL的游标只能在存储过程或者存储函数中使用。
②可以定义多个游标,但在同一个BEGIN...END块内,不能重名。
③游标不是一条SELECT语句,是被SELECT检索出来的结果集。
四 调用存储过程
语法:
CALL sp_name([para1[, para2]...]);
若参数表是空的则:
CALL sp_name[()]
①调用存储过程语句中的参数个数必须等于存储过程定义中的参数个数。
②调用无参数的存储过程时存储过程明后的空括号可以省略。
五 删除存储过程
语法:
DROP PROCEDURE [IF EXISTS] sp_name;
注意:这里无括号、无参数。
强烈建议删除前再三确认没有其他存储过程或对象依赖于这个存储过程。
第二节 存储函数
存储函数与存储过程的区别:
①存储函数不能使用输出参数,因为存储函数自身就是输出参数。
②可以直接对存储函数进行调用,且不需要使用CALL语句。
③存储函数必须包含一条RETURN语句。
一 创建存储函数
语法:
CREATE FUNCTION f_name([para1[, para2]])
RETURN type
routine_body
其中,para1的语法是:
para_name type
①para1只有参数名称和参数的数据类型;没有IN|OUT|INOUT的说法。
②RETURN 可用于声明存储函数返回值的数据类型。
③routine_body:存储函数的主体部分,又叫存储函数体;其中必须包含有RETURN value语句。
例子:
DELIMITER $$
CREATE FUNCTION f_001(cid INT)
RETURN char(2)
DETERMINISTIC #DETERMINISTIC – 表示函数或过程是纯函数或过程,即它的输出完全由输入参数确定。
BEGIN
DECLARE SEX char(2);
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 $$
DELIMITER ;
二 调用存储函数
语法:
SELECT f_name([f_para1[, fpara2]...]);
例如:
SELECT f_01(100);
三 删除存储函数
语法:
DROP FUNCTION [IF EXISTS] f_name;
删除前应再三确认无其他存储过程、存储函数或者对象依赖它。
上一篇: 数据库系统的核心是什么?
下一篇: 第一代数据库系统的含义