1 存储过程的定义
存储过程是存储在数据库目录中的一段声明性SQL语句。应用程序(触发器,JAVA,PHP)可以调用存储过程。
使用存储过程带来的好处:
(1)mysql的存储过程编译之后放到缓存中,以后每次执行存储过程都不需要重新编译,而一般SQL语句每执行一次就需编译一次,所以使用存储过程可提高数据库的执行速度。
(2)存储过程可以重复使用,提高了可重用性,减少数据库开发人员的工作量。
(3)存储过程有助于减少应用程序和数据库服务器之间的流量,因为应用程序不必发送多个冗长的SQL语句,而只能发送存储过程的名称和参数。
(4)安全性高,可设定只有某用户才能对指定存储过程的使用权
带来了好处,那肯定也带来了一些坏处:
(1)如果使用大量存储过程,那么使用这些存储过程的每个连接的内存使用量将会大大增加。
(2)很难调试存储过程。只有少数数据库管理系统允许您调试存储过程。
(3)开发和维护存储过程并不容易。开发和维护存储过程通常需要一个不是所有应用程序开发人员拥有的专业技能。
2 存储过程的创建
接下来的实验中使用到了数据库,数据库的sql文件下载地址为:http://www.yiibai.com/downloads/yiibaidb.zip
我们就通过最常使用的navicat进行存储过程的创建
(1)首先,点击函数 ----新建函数---过程
(2)输入参数,参数具有IN、OUT、INOUT类型,关于参数我们下面会介绍
(3)编写存储过程
我们就编写一个查询:查询product表的商品列表
(4)点击运行,产生实验结果
一个简单的过程创建完成了。
3 存储过程的变量
存储过程中的变量和函数中的变量非常的像
3.1 变量的声明
DECLARE variable_name datatype(size) DEFAULT default_value;
-
首先,在
DECLARE
关键字后面要指定变量名。 - 其次,指定变量的数据类型及其大小。
-
最后,当声明一个变量时,它的初始值为
NULL
。但是可以使用DEFAULT
关键字为变量分配默认值。
变量声明举例
DECLARE total_sale INT DEFAULT 0;
3.2 变量的赋值
(1)SET
DECLARE total_count INT DEFAULT 0;
SET total_count = 10;
(2)SELECT INTO
DECLARE total_products INT DEFAULT 0
SELECT COUNT(*) INTO total_products
FROM products
3.3 变量的作用域
如果在存储过程中声明一个变量,那么当达到存储过程的END
语句时,它将超出范围,因此在其它代码块中无法访问。
以@
符号开头的变量是会话变量。直到会话结束前它可用和可访问。
4 存储过程参数
4.1 参数类型
IN:存储过程接收的参数
OUT:存储过程返回的参数
INOUT:INOUT
参数是IN
和OUT
参数的组合。既可以作为接收的参数,又可以作为返回参数并将新值传递回调用程序
4.2 IN参数示例
以下示例说明如何使用GetOfficeByCountry
存储过程中的IN
参数来查询选择位于特定国家/地区的办公室。
我们通过navicat,定义存储过程使用的参数
在这里我们没有办法设置varchar的长度,我们可以到存储过程编辑 界面设置。
点击运行,输入参数
结果:
4.3 OUT参数示例
以下存储过程通过订单状态返回订单数量。它有两个参数:
-
orderStatus
:IN
参数,它是要对订单计数的订单状态。 -
total
:存储指定订单状态的订单数量的OUT
参数。
我们使用total接受参数,这里的total必须使用@修饰
4.4 INOUT参数示例
-
set_counter
存储过程接受一个INOUT
参数(count
)和一个IN
参数(inc
)。 - 在存储过程中,通过
inc
参数的值增加计数器(count
)。
由于要多次的调用存储过程,因此,我们使用查询语句来调用存储过程,调用的方法是call +存储过程。
5 IF和CASE语句
5.1 IF语法规则
IF expression THEN
statements;
END IF;
IF expression THEN
statements;
ELSE
else-statements;
END IF;
IF expression THEN
statements;
ELSEIF elseif-expression THEN
elseif-statements;
...
ELSE
else-statements;
END IF;
5.2 CASE语法规则
CASE case_expression
WHEN when_expression_1 THEN commands
WHEN when_expression_2 THEN commands
...
ELSE commands
END CASE;
5.3 IF 示例
以下示例说明如何使用IF ESLEIF ELSE
语句,GetCustomerLevel()
存储过程接受客户编号和客户级别的两个参数。
首先,它从customers
表中获得信用额度。然后,根据信用额度,它决定客户级别:PLATINUM
, GOLD
和 SILVER
。
5.4 CASE 示例
-
GetCustomerShipping
存储过程接受客户编号作为IN
参数,并根据客户所在国家返回运送时间。 - 在存储过程中,首先,我们根据输入的客户编号得到客户的国家。然后使用简单
CASE
语句来比较客户的国家来确定运送期。如果客户位于美国(USA
),则运送期为2
天。 如果客户在加拿大,运送期为3
天。 来自其他国家的客户则需要5
天的运输时间。
5.5 IF和CASE的区别
- 当将单个表达式与唯一值的范围进行比较时,简单CASE语句比IF语句更易读。另外,简单
CASE
语句比IF
语句更有效率。 - 当您根据多个值检查复杂表达式时,
IF
语句更容易理解。 - 如果您选择使用
CASE
语句,则必须确保至少有一个CASE
条件匹配。否则,需要定义一个错误处理程序来捕获错误。IF
语句则不需要处理错误。
6 存储过程循环
6.1 语法规则
WHILE expression DO
statements
END WHILE
REPEAT
statements;
UNTIL expression
END REPEAT
LOOP,LEAVE和ITERATE语句
有两个语句允许您用于控制循环:
-
LEAVE
语句用于立即退出循环,而无需等待检查条件。LEAVE
语句的工作原理就类似PHP,C/C++
,Java等其他语言的break
语句一样。 -
ITERATE
语句允许您跳过剩下的整个代码并开始新的迭代。ITERATE
语句类似于PHP
,C/C++
,Java
等中的continue
语句。
MySQL还有一个LOOP
语句,它可以反复执行一个代码块,另外还有一个使用循环标签的灵活性。
6.1 while do
BEGIN
#Routine body goes here...
DECLARE x INT;
DECLARE str VARCHAR(255);
SET x = 1;
SET str = '';
WHILE x <= 5 DO
SET str = CONCAT(str,x,',');
SET x = x + 1;
END WHILE;
SELECT str;
END
6.2 REPEAT
repeat和while do类似
6.3 LOOP,LEAVE,ITERATE
- 以上存储过程仅构造具有偶数字符串的字符串,例如
2
,4
,6
等。 - 在
LOOP
语句之前放置一个loop_label
循环标签。 - 如果
x
的值大于10
,则由于LEAVE
语句,循环被终止。 - 如果
x
的值是一个奇数,ITERATE
语句忽略它下面的所有内容,并开始一个新的迭代。 - 如果
x
的值是偶数,则ELSE
语句中的块将使用偶数构建字符串。
BEGIN
#Routine body goes here...
DECLARE x INT;
DECLARE str VARCHAR(255);
SET x = 1;
SET str = '';
loop_label: LOOP
IF x > 10 THEN
LEAVE loop_label;
END IF;
SET x = x + 1;
IF (x mod 2) THEN
ITERATE loop_label;
ELSE
SET str = CONCAT(str,x,',');
END IF;
END LOOP;
SELECT str;
END
7 游标
游标允许您迭代查询返回的一组行,并相应地处理每行。
由于游标的使用用到了声明处理程序。因此,我们先介绍声明处理程序。
7.1 声明处理程序
DECLARE action HANDLER FOR condition_value statement;
如果条件的值与condition_value
匹配,则MySQL将执行statement
,并根据该操作继续或退出当前的代码块。
各个字段的解释
7.2 使用MySQL游标
(1)声明一个游标
DECLARE cursor_name CURSOR FOR SELECT_statement;
(2)打开游标
OPEN cursor_name;
(3)使用FETCH
语句来检索光标指向的下一行,并将光标移动到结果集中的下一行。之后,可以检查是否有任何行记录可用,然后再提取它。
FETCH cursor_name INTO variables list;
(4)关闭游标
CLOSE cursor_name;
(5) 还必须声明一个NOT FOUND
处理程序来处理当游标找不到任何行时的情况
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
7.3 示例
使用游标遍历查询出的employees
表中所有员工的电子邮件列表。
BEGIN
#Routine body goes here...
DECLARE v_finished INTEGER DEFAULT 0;
DECLARE v_email varchar(100) DEFAULT "";
-- declare cursor for employee email
DEClARE email_cursor CURSOR FOR
SELECT email FROM employees;
-- declare NOT FOUND handler
DECLARE CONTINUE HANDLER
FOR NOT FOUND SET v_finished = 1;
OPEN email_cursor;
get_email: LOOP
FETCH email_cursor INTO v_email;
IF v_finished = 1 THEN
LEAVE get_email;
END IF;
-- build email list
SET email_list = CONCAT(v_email,";",email_list);
END LOOP get_email;
CLOSE email_cursor;
END
8 存储过程错误处理
8.1 声明一个错误处理程序
如果发生重复的键错误,则会发出MySQL错误1062
我们对应一个1062的声明处理程序
DECLARE CONTINUE HANDLER FOR 1062
SELECT 'Error, duplicate key occurred';
往这个表中插入重复的主键值,就会报错。
BEGIN
#Routine body goes here...
DECLARE CONTINUE HANDLER FOR 1062
SELECT CONCAT('duplicate keys (',article_id,',',tag_id,') found') AS msg;
-- insert a new record into article_tags
INSERT INTO article_tags(article_id,tag_id)
VALUES(article_id,tag_id);
-- return tag count for the article
SELECT COUNT(*) FROM article_tags;
END
CALL insert_article_tags(1,1);
CALL insert_article_tags(1,2);
CALL insert_article_tags(1,3);
CALL insert_article_tags(1,3);#插入重复的键,会调用声明处理程序
8.2 使用命名错误条件
上面的例子中使用了1062作为触发声明处理程序的条件,想象一下,你有一个大的存储过程代码使用了好多类似这样的数字; 这将成为维护代码的噩梦。
解决方法:重命名
DECLARE condition_name CONDITION FOR condition_value;
DECLARE table_not_found CONDITION for 1051;
DECLARE EXIT HANDLER FOR table_not_found SELECT 'Please create table abc first';
SELECT * FROM abc;
9 存储函数
储存函数和储存过程比较相似,但是,两者的使用场景不同。储存过程是暴露给外部应用的(JAVA,PHP),而存储函数使用在您SQL语句中或者表达式中。
9.1 语法规则
CREATE FUNCTION function_name(param1,param2,…)
RETURNS datatype
[NOT] DETERMINISTIC
statements
存储函数和存储过程的创建是类似,但是存储函数在主体部分中,必须至少指定一个RETURN
语句。RETURN
语句用于返回一个值给调用者。 每当到达RETURN
语句时,存储的函数的执行将立即终止。
9.2 示例
我们改写5.3 中IF 示例
(1)输入函数的参数
(2)输入返回值
BEGIN
#Routine body goes here...
DECLARE lvl varchar(10);
IF p_creditLimit > 50000 THEN
SET lvl = 'PLATINUM';
ELSEIF (p_creditLimit <= 50000 AND p_creditLimit >= 10000) THEN
SET lvl = 'GOLD';
ELSEIF p_creditLimit < 10000 THEN
SET lvl = 'SILVER';
END IF;
RETURN (lvl);
END;
现在,我们在SELECT语句中调用CustomerLevel()
存储函数
请注意,存储函数仅返回单个值。 如果没有包含INTO
子句的SELECT
语句,则将会收到错误