12 MySQL存储过程与函数
存储过程和函数
存储过程和函数是事先经过编译并存储在数据库中的一段SQL语句的集合.
调用存储过程和函数可以简化应用开发人员的工作,减少数据在数据库和应用服务器之间的传输,提高数据处理的效率.
存储过程和函数的区别在于:
函数必须有返回值,而存储过程没有.
存储过程的参数可以使用 IN,OUT,INOUT 类型;而函数的参数只能是IN类型.
如果有函数从其他类型数据库迁移到MySQL就可能需要把函数改造成存储过程.
存储过程和函数的相关操作
首先确认是否有相应的权限.
创建存储过程/函数 需要CREATE ROUTINE权限.
修改/删除 存储过程/函数 需要ALTER ROUTINE权限.
执行存储过程/函数 需要EXECUTE权限.
1).创建/修改 存储过程或函数
CREATE PROCEDURE sp_name ([proc_param[,...]])
[characteristic …]
routine_body
CREATE FUNCTION sp_name ([func_param[,...])
RETURNS type
[characteristic …]
routine_body
其中,
proc_param :
[IN|OUT|INOUT] param_name type
func_param :
param_name type
type : any valid MySQL data type .
characteristic :
LANGUAGE SQL
| [NOT] DETERMINISTIC
| {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}
| SQL SECURITY {DEFINER | INVOKER}
| COMMENT 'string'
routine_body :
valid SQL procedure statement or statements .
ALTER {PROCEDURE | FUNCTION} sp_name [characteristic ...]
characteristic :
{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}
|SQL SECURITY {DEFINER|INVOKER}
|COMMENT 'string'
调用存储过程/函数,使用关键字CALL :
CALL sp_name ([[parameter [,...]])
注意: 存储过程/函数 中允许包含DDL ,也可以在存储过程中执行COMMIT/ROLLBACK 操作,还可以调用其他的过程/函数;
但是存储过程/函数 中不允许使用 LOAD DATA INFILE 语句.
Demo :
DELIMITER $$
CREATE PROCEDURE film_in_stock (IN p_film_id INT , IN p_store_id INT , OUT p_film_count INT)
READS SQL DATA
BEGIN
SELECT inventory_id
FROM inventory
WHERE film_id = p_film_id
AND store_id = p_store_id
AND inventory_in_stock(inventory_id);
SELECT FOUND_ROWS() INTO p_film_count;
END $$
DELIMITER ;
CALL film_in_stock(2,2,@a);
SELECT @a ;
注意 : 与视图的创建语法不同,存储过程/函数 的CREATE语法不支持使用 CREATE OR REPLACE对存储过程/函数进行修改.如需修改,可以执行ALTER语法.
characteristic特征值的简单说明:
1).LANGUAGE SQL
说明下面过程的BODY是使用SQL语句编写(系统默认的),以后MySQL可能支持其它语言.
2).[NOT] DETERMINISTIC:
目前还未被优化程序使用
DETERMINISTIC 每次输入一样输出也一样的程序
NOT DETERMINISTIC(系统默认).
3).{CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}
目前这些特征值只是提供给服务器,并未用来约束存储过程实际使用数据的情况.
CONTAINS SQL 表示子程序不包含读或者写数据的语句
NO SQL 表示子程序不包含SQL语句
READS SQL DATA 表示子程序包含读数据的语句
MODIFIES SQL DATA 表示子程序包含写数据的语句
默认值是 CONTAINS SQL
4).SQL SECURITY {DEFINER | INVOKER}
指定子程序 该用创建子程序者的权限来执行,还是使用调用者的权限来执行.默认值是DEFINER , 即使用创建者的权限执行.
5).COMMENT 'string'
存储过程/函数 的注释信息.
2.删除存储过程/函数
一次性只能删除一个存储过程/函数.需要有ALTER ROUTINE 权限.
DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name
3.查看存储过程/函数
1).查看存储过程/函数 的状态
SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']
2).查看存储过程/函数 的定义
SHOW CREATE {PROCEDURE|FUNCTION} sp_name
3).通过查看information_schema.Routines 了解存储过程/函数 的信息
包含名称,类型,语法,创建人等信息.
demo:SELECT * FROM routines WHERE ROUTINE_NAME = 'film_in_stock' \G
4.变量的使用
1).变量的定义
DECLARE var_name [,...] type [DEFAULT value]
2).变量的赋值
SET var_name = expr [,var_name = expr] ...
其中,expr 可以是字面量,函数返回值,SELECT 语句(要求结果有且只有一行)等.
或者使用SELECT .. INTO .. 语句
SELECT col_name[,..] INTO var_name[,..] table_expr
Demo :
DECLARE $$
CREATE FUNCTION get_customer_balance( p_customer_id INT , p_effective_date DATETIME )
RETURNS DECIMAL(5,2)
DETERMINISTIC
READS SQL DATA
BEGIN
...
DECLARE v_payments DECIMAL(5,2) ;
...
SELECT IFNULL (SUM(payment,amount),0) INTO v_payments
FROM payment
WHERE payment.payment_date <= p_effective_date
AND payment.customer_id = p_customer_id ;
...
RETURN v_rentfees + v_overfees - v_payments ;
END $$
DECLARE ;
5.定义条件和处理
1).条件的定义
DECLARE condition_name CONDITION FOR condition_value
其中,condition_value :
SQLSTATE [VALUE] sqlstate_value | mysql_error_code
2).条件的处理
DECLARE handler_type HANDLER FOR condition_value[,..] sp_statement
handler_type :
CONTINUE | EXIT | UNDO(暂不支持)
condition_value:
SQLSTATE [VALUE] sqlstate_value
|condition_name
|SQLWARNING
|NOT FOUND
|SQLEXCEPTION
|mysql_error_code
demo:todo
6.光标的使用 --就是游标
可以使用游标对结果集进行循环处理
1).声明光标
DECLARE cursor_name CURSOR FOR select_statement
select_statement 中的SELECT语句 不能包含 INTO , 即不可以是SELECT .. INTO ..语句
SELECT 语句查询出来的列数 , 必须与FETCH游标中的接收数据的变量数 一致.
2).OPEN光标
OPEN cursor_name
3).FETCH光标
FETCH cursor_name INTO var_name[,var_name,..]
4).CLOSE光标
CLOSE cursor_name
Demo : —— 需要注意 DECLARE 声明的顺序 : 变量 -> 游标 -> 条件处理 .
DELIMITER $$
CREATE PROCEDURE payment_stat()
BEGIN
DECLARE i_staff_id INT ;
DECLARE d_amount DECIMAL(5,2);
DECLARE cur_payment CURSOR FOR SELECT staff_id,amount FROM payment ;
DECLARE EXIT HANDLER FOR NOT FOUND CLOSE cur_payment ;
SET @x1 = 0 ;
SET @x2 = 0 ;
OPEN cur_payment ;
REPEAT
FETCH cur_payment INTO i_staff_id,d_amount ;
IF i_staff_id = 2
THEN
SET @x1 = @x1 + d_amount ;
ELSE
SET @x2 = @x2 + d_amount ;
END IF ;
UNTIL 0 END REPEAT ;
CLOSE cur_payment ;
END ;
$$
DELIMITER ;
CALL payment_stat();
注意:变量,条件,处理程序,游标都是通过DECLARE定义的,但是顺序是由先后要求的.
变量和条件 必须声明在最前面,然后是游标的声明,最后才是处理程序的声明.
7.流程控制
1).IF语句
IF search_condition
THEN statement_list
[
ELSEIF search_condition
THEN statement_list
]
[
ELSE statement_list
]
END IF
2).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
Demo : 改写游标demo中IF语句
CASE
WHEN i_staff_id = 2
THEN
SET @x1=@x1+d_amount ;
ELSE
SET @x2= @x2+d_amount ;
END CASE ;
或
CASE i_staff_id
WHEN 2
THEN
SET @x1 = @x1 + d_amount ;
ELSE
SET @x2 = @x2 + d_amount ;
END CASE ;
3).LOOP 语句
简单的循环,需要配合其他的语句定义来实现退出循环,通常使用LEAVE语句实现.
[begin_label:] LOOP
statement_list
END LOOP [end_label]
如果没有退出循环语句,就是个死循环.
4).LEAVE 语句
从标注的流程中退出,通常和BEGIN..END / 循环 一起使用.
Demo :
DELIMITER $$
CREATE PROCEDURE actor_insert()
BEGIN
SET @x = 0 ;
ins : LOOP
SET @x = @x+1;
IF @x=100
THEN
LEAVE ins ;
END IF ;
INSERT INTO actor(first_name,last_name)
VALUES('Test','201');
END LOOP ins ;
END ;
$$
DELIMITER ;
CALL actor_insert();
5).ITERATE 语句
必须用在循环中,表示跳过当前循环的剩下语句,进入下次循环.作用相当于Java中的continue.
Demo:
DELIMITER $$
CREATE PROCEDURE actor_insert()
BEGIN
SET @x = 0 ;
ins : LOOP
SET @x = @x + 1;
IF @x = 10
THEN
LEAVE ins ;
ELSEIF mod(@x,2) = 0
THEN
ITERATE ins ;
END IF ;
INSERT INTO actor (actor_id , first_name,last_name)
VALUES(@x*10,'Test',@x);
END LOOP ins ;
END ;
$$
DELIMITER ;
CALL actor_insert() ;
6).REPEAT 语句
有条件的循环语句,相当于Java中的do_while
不同的是REPEAT是满足条件时就退出循环,while是满足就执行.Repeat 和 do..while 一样,至少执行一次.
Demo参考游标的demo
7).WHILE 语句
[begin_label:] WHILE search_condition
DO statement_list
END WHILE [end_label]
总结:存储过程/函数的优势是可以将数据的处理放在数据库服务器上进行,避免将大量的结果集传输给客户端,减少数据传输,
但是在数据库服务器上进行大量的复杂运算会占用服务器的cpu,造成数据库服务器的压力,
所以存储过程/函数中不要进行大量的复杂运算,应该将这些运算操作分摊到应用服务器上执行.
