避免编写重复的语句
安全性可控
执行效率高
14.1、创建存储过程和函数
14.1.1、创建存储过程
CREATE PROCEDUREsp_name ([proc_parameter[,...]])
[characteristic...] routine_body
procedure 发音 [prə'si:dʒə]
proc_parameter IN|OUT|INOUT param_name type
characteristic n. 特征；特性；特色
LANGUAGESQL 默认，routine_boyd由SQL组成
[NOT]DETERMINISTIC 指明存储过程的执行结果是否是确定的，默认不确定
CONSTAINSSQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA指定程序使用SQL语句的限制
CONSTAINS SQL 子程序包含SQL，但不包含读写数据的语句，默认
NO SQL 子程序中不包含SQL语句
READS SQL DATA 子程序中包含读数据的语句
MODIFIES SQL DATA 子程序中包含了写数据的语句
SQLSECURITY {DEFINER|INVOKER}，指明谁有权限执行。
DEFINER，只有定义者自己才能够执行，默认
INVOKER 表示调用者可以执行
COMMENT‘string’ 注释信息
CREATE PROCEDURE num_from_employee (IN emp_id, INT, OUT count_num INT)
READS SQL DATA
BEGIN
SELECT COUNT(*) INTO count_num
FROM employee
WHERE d_id=emp_id;
END
14.1.2、创建存储函数
CREATE FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic...] routine_body
CREATEFUNCTION name_from_employee(emp_id INT)
RETURNSVARCHAR(20)
BEGIN
RETURN (SELECT name FROM employee WHEREnum=emp_id);
END
14.1.3、变量的使用
1．定义变量
DECLARE var_name[,…]type [DEFAULT value]
DECLAREmy_sql INT DEFAULT 10;
2．为变量赋值
SET var_name=expr[,var_name=expr]…
SELECT col_name[,…]INTO var_name[,…] FROM table_name WHERE condition
14.1.4、定义条件和处理程序
1．定义条件
DECLARE condition_nameCONDITION FOR condition_value
condition value:
SQLSTATE[VALUE] sqlstate_value | mysql_error_code
对于ERROR 1146(42S02)
sqlstate_value: 42S02
mysql_error_code:1146
//方法一
DECLARE can_not_find CONDITION FOR SQLSTATE ‘42S02’
//方法二
DECLARE can_not_find CONDITION FOR 1146
2．定义处理程序
DECLARE hander_type HANDLER FOR condition_value[,…] sp_statement
handler_type:
CONTINUE|EXIT|UNDO
condition_value:
SQLSTATE[VALUE] sqlstate_value | condition_name |SQLWARNING|NOTFOUND|SQLEXCEPTION|mysql_error_code
UNDO目前MySQL不支持
1、捕获sqlstate_value
DECLARE CONTINUE HANDLER FOR SQLSTATE ‘42S02’ SET @info=’CANNOT FIND’;
2、捕获mysql_error_code
DECLARE CONTINUE HANDLER FOR 1146 SET @info=’CAN NOT FIND’;
3、先定义条件，然后调用
DECLARE can_not_find CONDITION FOR 1146;
DECLARE CONTINUE HANDLER FOR can_not_find SET @info=’CANNOT FIND’;
4、使用SQLWARNING
DECLARE EXITHANDLER FOR SQLWARNING SET @info=’CANNOT FIND’;
5、使用NOT FOUND
DECLARE EXIT HANDLER FOR NOT FOUND SET @info=’CANNOT FIND’;
6、使用SQLEXCEPTION
DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info=’CANNOT FIND’;
14.1.5、光标的使用
存储过程中对多条记录处理，使用光标
1．声明光标
DECLARE cousor_name COURSOR FOR select statement;
DECLARE cur_employee CURSOR FOR SELECT name, age FROM employee;
2．打开光标
OPEN cursor_name;
OPEN cur_employee;
3．使用光标
FETCH cur_employee INTO var_name[,var_name…];
FETCH cur_employeeINTO emp_name, emp_age;
4．关闭光标
CLOSE cursor_name
CLOSE cur_employee
14.1.6、流程控制的使用
1．IF语句
IF search_condition THEN statement_list
[ELSEIF search_condition THENstatement_list]…
[ELSE statement_list]
END IF
IF age>20THEN SET @count1=@count1+1;
ELSEIF age=20 THEN @count2=@count2+1;
ELSE @count3=@count3+1;
END
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 THENstatement_list
[WHEN search_condition THENstatement_list]…
[ELSE statement_list]
END CASE
CASE age
WHEN 20 THEN SET @count1=@count1+1;
ELSE SET @count2=@count2+1;
END CASE;
CASE
WHERE age=20 THEN SET@count1=@count1+1;
ELSE SET @count2=@count2+1;
END CASE;
3．LOOP语句
[begin_label:]LOOP
statement_list
ENDLOOP[end_label]
add_num:LOOP
SET @count=@count+1;
END LOOPadd_num;
4．LEAVE语句
跳出循环控制
LEAVE label
add_num:LOOP
SET @count=@count+1;
LEAVE add_num;
END LOOP add_num;
5．ITERATE语句
跳出本次循环，执行下一次循环
ITERATE label
add_num:LOOP
SET @count=@count+1;
IF @count=100 THEN LEAVE add_num;
ELSEIF MOD(@count,3)=0 THEN ITERATEadd_num;
SELECT * FROM employee;
END LOOP add_num;
6．REPEAT语句
有条件循环，满足条件退出循环
[begin_label:]REPEAT
statement_list
UNTIL search_condition
ENDREPEAT[end_label]
REPEAT
SET @count=@count+1;
UNTIL @count=100;
ENDREPEAT;
7．WHILE语句
[begin_label:]WHILE search_condition DO
statement_list
ENDREPEAT[end_label]
WHILE@count<100 DO
SET @count=@count+1;
ENDWHILE;
14.2、调用存储过程和函数
存储过程是通过CALL语句来调用的。而存储函数的使用方法与MySQL内部函数的使用方法是一样的。执行存储过程和存储函数需要拥有EXECUTE权限。EXECUTE权限的信息存储在information_schema数据库下面的USER_PRIVILEGES表中
14.2.1、调用存储过程
CALL sp_name([parameter[,…]]) ;
14.2.2、调用存储函数
存储函数的使用方法与MySQL内部函数的使用方法是一样的
14.3、查看存储过程和函数
SHOW { PROCEDURE| FUNCTION } STATUS [ LIKE ' pattern ' ];
SHOW CREATE {PROCEDURE | FUNCTION } sp_name ;
SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME=' sp_name ' ;
14.4、修改存储过程和函数
ALTER {PROCEDURE| FUNCTION} sp_name [characteristic ...]
characteristic:
{ CONTAINS SQL |NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY {DEFINER | INVOKER }
| COMMENT'string'
14.5、删除存储过程和函数
DROP {PROCEDURE| FUNCTION } sp_name;