MySQL入门很简单－学习笔记 - 第14章存储过程和函数

本文链接：https://blog.csdn.net/kimsoft/article/details/6763981

避免编写重复的语句

安全性可控

执行效率高

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’ 注释信息

CREATEPROCEDURE num_from_employee (IN emp_id, INT, OUT count_num INT)

READS SQL DATA

BEGIN

SELECTCOUNT(*) INTOcount_num

FROMemployee

WHEREd_id=emp_id;

END

14.1.2、创建存储函数

CREATE FUNCTIONsp_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．为变量赋值

SETvar_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．定义处理程序

DECLAREhander_type HANDLER FOR condition_value[,…] sp_statement

handler_type:

CONTINUE|EXIT|UNDO

condition_value:

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．声明光标

DECLAREcousor_name COURSOR FOR select statement;

DECLAREcur_employee CURSOR FOR SELECT name, age FROM employee;

2．打开光标

OPENcursor_name;

OPENcur_employee;

3．使用光标

FETCHcur_employee INTO var_name[,var_name…];

FETCH cur_employeeINTO emp_name, emp_age;

4．关闭光标

CLOSEcursor_name

CLOSE cur_employee

14.1.6、流程控制的使用

1．IF语句

IFsearch_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 LOOPadd_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 LOOPadd_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:]WHILEsearch_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 * FROMinformation_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;

Google Project 完整PDF下载

MySQL入门很简单－学习笔记 - 索引页

MySQL入门很简单－学习笔记 - 第1 章数据库概述

MySQL入门很简单－学习笔记 - 第 2 章 Windows平台下安装与配置MySQL

MySQL入门很简单－学习笔记 - 第 4 章 MySQL数据类型

MySQL入门很简单－学习笔记 - 第 5 章操作数据库

MySQL入门很简单－学习笔记 - 第6章创建、修改和删除表

MySQL入门很简单－学习笔记 - 第7章索引

MySQL入门很简单－学习笔记 - 第 8 章视图

MySQL入门很简单－学习笔记 - 第 9 章触发器

MySQL入门很简单－学习笔记 - 第10章查询数据

MySQL入门很简单－学习笔记 - 第11章插入、更新与删除数据

MySQL入门很简单－学习笔记 - 第13章 MySQL函数

MySQL入门很简单－学习笔记 - 第14章存储过程和函数

MySQL入门很简单－学习笔记 - 第15章 MySQL用户管理

MySQL入门很简单－学习笔记 - 第16章数据备份与还原

MySQL入门很简单－学习笔记 - 第17章 MySQL日志

MySQL入门很简单－学习笔记 - 第18章性能优化

Google Project 完整PDF下载

MySQL入门很简单－学习笔记 - 第14章 存储过程和函数

避免编写重复的语句

14.1、创建存储过程和函数

14.1.1、创建存储过程

14.1.2、创建存储函数

14.1.3、变量的使用

1．定义变量

2．为变量赋值

14.1.4、定义条件和处理程序

1．定义条件

2．定义处理程序

14.1.5、光标的使用

1．声明光标

2．打开光标

3．使用光标

4．关闭光标

14.1.6、流程控制的使用

1．IF语句

2．CASE语句

3．LOOP语句

4．LEAVE语句

5．ITERATE语句

6．REPEAT语句

7．WHILE语句

14.2、调用存储过程和函数

14.2.1、调用存储过程

14.2.2、调用存储函数

14.3、查看存储过程和函数

14.4、修改存储过程和函数

14.5、删除存储过程和函数

MySQL入门很简单－学习笔记 - 第14章存储过程和函数