么是存储过程和函数
存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程和函数可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据处理的效率是有好处的。
存储过程和函数的区别在于函数必须有返回值，而存储过程没有，存储过程的参数可以使用IN、OUT、INOUT类型，而函数的参数只能是IN类型的。如果有函数从其他类型的数据库迁移到MySQL，那么就可能因此需要将函数改造成存储过程。
为什么使用存储过程和函数
在完成一个逻辑操作时，有时会执行多条SQL语句，此外这些SQL语句的执行顺序也不是固定的，它会根据条件的变化而变化。在执行过程中，这些需要根据前面SQL语句的执行结果有选择的执行后面的SQL语句。为了解决该问题，MySQL软件提供了数据库对象存储过程和函数。
存储过程和函数的优点
<1> 存储过程和函数允许标准组件式编程提高了SQL语句的重用性、共享性和可移植性。
<2> 存储过程和函数能够实现较快的执行速度，减少网络流量。
<3> 存储过程和函数可以被作为一种安全机制来利用
存储过程和函数的缺点
<1> 存储过程和函数的编写比单句SQL语句复杂，需要用户具有更高的技能和更丰富的经验。
<2> 在编写存储过程和函数时，需要创建这些数据库对象的权限。
存储过程和函数的相关操作
创建、修改存储过程或者函数
创建、修改存储过程或者函数的语法如下：
CREATE
[DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
CREATE
[DEFINER = { user | CURRENT_USER }]
FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body
proc_parameter：[IN|OUT|INOUT]param_name type
func_parameter:param_name type
type:Any valid MySQL data type(支持MySQL中的所有数据类型)
调用语法：
CALL sp_name([parameter[,…]])
MySQL的存储过程或者函数中允许包含DDL语句，也允许在存储过程中执行提交（Commit，即确认之前的修改）或者回滚（Rollback，即放弃之前的修改），但是存储过程或者函数中不允许执行LOAD DATA INFILE语句。此外，存储过程和函数中可以调用其他的过程或者函数。
示例1：在员工姓名模糊查询时，可能需要匹配不同的查询条件，有时需要查询名字中带有“李”的，有时需要查询以“梅”结尾的，此时可以创建一个存储过程根据不同的传入参数，返回不同的结果：
mysql>DELIMITER $$
->CREATE PROCEDURE emp_in_hire(IN emp_name_reg VARCHAR(10),OUT emp_count INT)
->READS SQL DATA
->BEGIN
->SELECT id FROM emp WHERE ename REGEXP emp_name_reg;
->SELECT FOUND_ROWS() INTO emp_count;
->END $$
mysql>DELIMITER ;
mysql>CALL emp_in_hire ('^李',@emp_count);
+----+
| id |
+----+
| 2 |
+----+
1 row in set (0.00 sec)
Query OK, 1 row affected (0.00 sec)
mysql>SELECT @emp_count;
+------------+
| @emp_count |
+------------+
| 1 |
+------------+
1 row in set (0.00 sec)
示例中可以根据传入的第一个参数指定员工姓名匹配的正则表达式，第二个参数用来接收存储过程的输出参数。达到了一段SQL多次使用的目的，不用在业务逻辑中多次编写SQL语句。
但是在进行上面的例子中可以这样尝试一下，将IN类型的参数emp_name_reg 的类型定义为VARCHAR，也就是不指定长度，这样的情况在MySQL5.6版本中会创建失败。但是INT类型的参数就不需要指定长度。使用VARCHAR指定传入参数的类型时，没有指定长度，创建时总是提示检查语法。
还有需要注意的是，在参数命名时，存储过程的参数名不要跟字段名一样。将会导致参数的值将不会是传入的值，而是变成每条记录中对应字段的值。这样的后果，是非常严重的。比如执行单个删除操作：
DELIMITER $$
CREATE PROCEDURE delete_one_emp(IN ename VARCHAR(10))
BEGIN
DELETE FROM EMP WHERE ename=ename;
END $$
调用上面的存储过程时，传入’李四’，意图是删除姓名为李四的员工信息，但是执行后发现，整张表的数据被清空了，即便是字段名是大写，参数名是小写也会如此，因为MySQL对大小写不敏感。
示例2：创建函数将指定员工的工资增加指定的值，然后返回工资的最大值。
DELIMITER $$
CREATE FUNCTION add_salary(raise_count int(5),e_name_reg varchar(10))
RETURNS int(10)
COMMENT ’涨工资’
BEGIN
UPDATE EMP SET SALARY = SALARY+raise_count WHERE ENAME RLIKE e_name_reg;
RETURN (SELECT max(SALARY) FROM EMP);
END $$
DELIMITER;
SELECT add_salary(5000,'^李');
存储过程和函数的CREATE语法不支持使用CREATEOR REPLACE对存储过程和函数进行修改，如果需要对已有的存储过程或者函数进行修改需要执行ALTER语法。
在存储过程和函数的创建语法中有characteristic特性，下面对该特性值的部分进行说明。
<1> LANGUAGE SQL：说明下面过程的BODY是使用SQL语言编写，这条是系统默认的，为以后除SQL外的其他语言支持的存储过程而准备。
<2> [NOT]DETERMINISTIC：DETERMINISTIC确定的，即每次输入一样输出也一样的程序，NOT DETERMINISTIC非确定的，默认的是非确定的。当前，这个特征值还没有被优化程序使用。
<3> {CONTAINS SQL|NOT SQL|READS SQL DATA|MODIFIES SQL DATA}：这些特征值提供子程序使用数据的内在信息，这些特征值目前只是提供给服务器，并没有根据这些特征值来约束过程实际使用数据的情况。CATAINS SQL表示子程序不包含读或写的语句。NOT SQL表示子程序不包含SQL语句。READS SQL DATA表示子程序包含读数据的语句，但是不包含写数据的语句。MODIFIES SQL DATA表示子程序包含写数据的语句。如果这些特征值没有明确给定，默认使用的值CONTAINS SQL。
<4> SQL SECURITY{DEFINER|INVOKER}：可以用来指定子程序该用创建子程序者的许可来执行，还是使用调用者的许可来执行。默认是DEFINER。
<5> COMMENT ：存储过程或者函数的注释信息。
删除存储过程或者函数
一次只能删除一个存储过程或者函数，删除存储过程或者函数需要有该过程或者函数的ALTER ROUTINE权限。语法如下：
DROP{PROCEDURE|FUNCTION} [IF EXISTS] sp_name
查看存储过程或者函数
查看存储过程或者函数的状态
SHOW {PROCEDURE|FUNCTION} STATUS [LIKE ‘pattern’]
查看存储过程或者函数的定义
SHOW CREATE {PROCEDURE|FUNCTION} sp_name
通过查看information_schema.Routines了解存储过程和函数的信息
除了以上两种方法，还可以查看系统表来了解存储过程和函数的相关信息，通过查看information_schema.Routines就可以获得存储过程和函数的名称、类型、语法、创建人等信息。
变量的使用
存储过程和函数中可以使用变量。
变量的定义
通过DECLARE可以定义一个局部变量，该变量的作用范围只能在BEGIN…END块中，可以用在嵌套的块中。变量的定义必须写在复合语句的开头，并且在任何其他语句的前面。可以一次声明多个相同类型的变量。如果需要，可以使用DEFAULT赋默认值。
定义一个变量的语法如下：
DECLAREvar_name[,…] type [DEFAULT value]
例如，定义一个DATE类型的变量，名称是last_month_start:
DECLARElast_month_start DATE;
变量的赋值
变量可以直接赋值，或者通过查询赋值。直接赋值使用SET，可以赋常量或者赋表达式，具体语法如下：
SET var_name =expr[,var_name=expr]…
给刚才定义的变量last_month_start赋值，具体语法如下：
SETlast_month_start = DATE_SUB(CURRENT_DATE(),INTERVAL 1 MONTH);
也可以通过查询结果将结果赋给变量，这要求查询返回的结果必须只有一行，具体语法如下：
SELECTcol_name[,…] INTO var_name[,…] table_expr
示例3：通过查询将结果赋值给变量max_salary。
DELIMITER $$
CREATE FUNCTIONget_max_salary(param_deptno INT)
RETURNS INT(7)
DETERMINISTIC
READS SQL DATA
BEGIN
DECLAREv_max_salary INT(7);
SELECT IFNULL(MAX(SALARY),0)INTO v_max_salary
FROM EMP
WHEREDEPTNO=param_deptno;
RETURNv_max_salary;
END$$
DELIMITER ;
存储过程和函数中的异常及处理
在看到的资料书中都将本小节命名
INSERT INTO emp(id,ename,deptno) VALUES(1,'张三',1);
SET @X=2;
INSERT INTO emp(id,ename,deptno) VALUES(1,'
DELIMI
ERROR 1062 (23000): Duplicate entry '1' forkey 'PRIMARY'
SELECT @X;
->2
从最后的执行结果可以看出，执行到插入emp.ename=李四的记录时，会主键重复并退出，并没有执行到下面的语句。
<1> 向emp(id,ename,deptno)表中连续插入两条记录(1,’张三’,1),(1,’李四’，1)使用处理程序时，id重复的执行结果如下，我们在处理程序加上输出：
DELIMITER $$
CREATE PROCEDURE insert_emp()
BEGIN
DECLAREduplicate_entry CONDITION FOR SQLSTATE '23000';
DECLARECONTINUE HANDLER FOR duplicate_entry
BEGIN
SELECT'遇到异常继续执行';
END;
SET@x=1;
INSERTINTO emp(id,ename,deptno) VALUES(1,'张三',1);
SET@X=2;
INSERTINTO emp(id,ename,deptno) VALUES(1,'李四',1);
SET@X=3;
END $$
DELIMITER ;
CALL insert_emp();
->遇到异常继续执行
SELECT @x;
->3
调用条件处理的过程，再遇到主键重复的错误时，会按照定义的处理方式进行处理，由于例子中定义的CONTINUE，所以会继续执行下面的语句。
handler_action在MySQL5.6中还只支持CONTINUE和EXIT两种，CONTINUE表示继续执行下面的语句，EXIT表示执行终止，UNDO还不支持。
在上面的示例中嵌套了BEGIN…END内部的BEGIN…END末尾要使用定义的DELIMITER($$)，这点需要注意，如果使用后面的;结尾则会视为结束，造成创建失败。
光标的使用
在存储过程和函数中，可以使用光标对结果集进行循环的处理，光标的使用包括光标的声明，OPEN、FETCH和CLOSE，其语法分别如下：
<1> 声明光标
DECLARE cursor_name CURSOR FORselect_statement
<2> 打开光标
OPEN cursor_name
<3> 使用光标
FETCH cursor_name INTO var_name[,var_name]…
<4> 关闭光标
CLOSE cursor_name
示例5：对emp表按照行进行循环的处理，按照deptno的不同累加salary的值，判断循环结束的条件是NOT FOUND的状态，当FETCH光标找不到下一条记录的时候，就会关闭光标然后退出程序。
DELIMITER $$
CREATE PROCEDURE dept_salary_sum()
BEGIN
DECLAREi_dept_no int;
DECLAREi_salary int(5);
DECLAREcur_emp CURSOR FOR SELECT deptno,salary FROM emp;
DECLAREEXIT HANDLER FOR NOT FOUND CLOSE cur_emp;
SET@salary_sum_dept1=0;
SET@salary_sum_dept2=0;
OPENcur_emp;
REPEAT
FETCHcur_emp INTO i_dept_no,i_salary;
IFi_dept_no=1 THEN
SET@salary_sum_dept1=@salary_sum_dept1+i_salary;
ELSE
SET@salary_sum_dept2=@salary_sum_dept2+i_salary;
ENDIF;
UNTIL0 END REPEAT;
CLOSEcur_emp;
END $$
DELIMITER ;
CALL dept_salary_sum();
SELECT @salary_sum_dept1,@salary_sum_dept2;
->50000 27000
注意：变量，状态，处理程序，光标都是通过DECLARE定义的，他们之间是有先后顺序要求的。变量和状态必须在最前面声明，然后才能是光标的声明，最后才可以是处理程序的声明。
流程控制
可以使用IF、CASE、LOOP、LEAVE、ITERATE、REPEAT及WHILE语句进行流程的控制。详细说明及示例参考对应版本的官网。5.6版本：
http://dev.mysql.com/doc/refman/5.6/en/flow-control-statements.html
IF语句
IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] ...
[ELSE statement_list]
END IF
CASE语句
CASE case_value
WHEN when_value THEN statement_list
[WHEN when_value THEN statement_list] ...
[ELSE statement_list]
END CASE
或者
CASE
WHEN search_condition THEN statement_list
[WHEN search_condition THEN statement_list] ...
[ELSE statement_list]
END CASE
上面光标的例子中，IF语句也可以使用CASE语句来完成：
CASE
WHEN i_dept_no =1 THEN
SET@salary_sum_dept1=@salary_sum_dept1+i_salary;
ELSE
SET@salary_sum_dept2=@salary_sum_dept2+i_salary;
END CASE;
或者：
CASE i_dept_no
WHEN 1 THEN
SET@salary_sum_dept1=@salary_sum_dept1+i_salary;
ELSE
SET @salary_sum_dept2=@salary_sum_dept2+i_salary;
END CASE;
LOOP语句
[begin_label:] LOOP
statement_list
END LOOP [end_label]
LOOP实现简单的循环，退出循环的条件需要使用其它的语句定义，通常可以使用LEAVE语句实现。如果不在statement_list中增加退出循环的语句，那么LOOP语句可以用来实现简单的死循环。
示例6：简单LOOP循环
CREATE PROCEDURE doiterate(p1 INT)
BEGIN
label1: LOOP
SETp1 = p1 + 1;
IFp1 < 10 THEN
ITERATE label1;
ENDIF;
LEAVE label1;
ENDLOOP label1;
SET@x = p1;
END;
LEAVE语句
LEAVE label
用来从标注的流程构造中退出，通常和BEGIN…END或者循环一起使用。如上面的例子。
ITERATE语句
ITERATE label
ITERATE语句必须用在循环中，作用是跳过当前循环的剩下的语句，直接进入下一轮循环。如上面的例子。
REPEAT语句
[begin_label:] REPEAT
statement_list
UNTIL search_condition
END REPEAT [end_label]
有条件的循环控制语句，当满足条件的时候退出循环。在示例5中使用REPEAT语句来实现光标的循环获得。
示例7：REPEAT的使用
mysql> delimiter //
mysql> CREATE PROCEDURE dorepeat(p1 INT)
-> BEGIN
-> SET @x = 0;
-> REPEAT
-> SET @x = @x + 1;
-> UNTIL @x > p1 END REPEAT;
-> END
-> //
Query OK, 0 rows affected (0.00 sec)
mysql> CALL dorepeat(1000)//
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT @x//
+------+
| @x |
+------+
| 1001 |
+------+
1 row in set (0.00 sec)
WHILE语句
[begin_label:] WHILE search_condition DO
statement_list
END WHILE [end_label]
有条件的循环，与REPEAT的区别在于：WHILE是满足条件才执行循环，REPEAT是满足条件退出循环；WHILE在首次循环执行之前就潘丹条件，所以最少执行0次，而REPEAT是在首次执行循环之后才判断条件，所以循环最少执行1次。
示例8：WHILE的使用
REATE PROCEDURE dowhile()
BEGIN
DECLARE v1 INT DEFAULT 5;
WHILEv1 > 0 DO
...
SETv1 = v1 - 1;
ENDWHILE;
END;
事件调度器
事件调度器是MySQL5.1后新增的功能，可以将数据库按自定义的时间周期触发某种操作，可以理解为定时任务，类似Linux系统下的定时任务crontab
如：
CREATE EVENT myevent
ONSCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 HOUR
DO
UPDATE myschema.mytable SET mycol = mycol + 1;
其中：
<1> 事件名称在CREATE EVENT关键字之后指定；
<2> 通过ON SCHEDULE子句指定事件在何时执行及执行频次；
<3> 通过DO子句指定要执行的具体操作或事件。
上面的示例中创建一个创建后立即执行的调度事件，执行频次为1小时，执行内容为更新表数据。
事件调度器默认是关闭的，需要打开：
SET GLOBAL event_scheduler = 1；
如果事件调度器不再使用，可以禁用（disable）或删除（drop）：
ALTER EVENT myevent disable;
DROP EVENT myevent;
当前位置：
论坛首页 »
自由讨论区 »
MySQL, Oracle及数据库讨论区
»
发起投票
技术讨论
加好友
发消息
2018-8-28 16:37:51 |
赞(
操作
