MySQL存储过程

1.存储过程的概念
对于SQL编程而言,存储过程是数据中的一个重要的对象,它是在大型数据库系统中一组为了完成特定功能的SQL语句集,在第一次使用经过编译后,再次调用就不需要重复编译,因此执行效率比较高。
存储过程与函数的相同点在于,它们的目的都是为了可重复地执行数据库SQL语句的集合,并且都是经过一次编译后,后面再次需要时直接执行即可.
存储过程与函数的不相同点有四个,具体如下.

• 语法中实现的标识符不同,存储过程使用 PROCEDURE,函数为FUNCTION 。
• 存储过程在创建时没有返回值,而函数在定义时必须设置返回值.
• 存储过程没有返回值类型,且不能将结果直接赋值给变量;而函数定义时需要设置返回值类型,且在调用时必须将返回值赋给变量
• 存储过程必须通过CALL进行调用,不能使用 SELECT调用;而函数则可在SELECT语句中直接使用

2.存储过程的创建与执行
DELIMITER新结束符号
CREATE PROCEDURE过程名字([[IN|OUT|INOUT]参数名称 参数类型])
BEGIN
过程体
END
新结束符号
DELIMITER;
存储过程设置参数时,在参数名前还可以指定参数的来源及用途,区别如下.
IN:表示输入参数,即参数是在调用存储过程时传入到存储过程里面使用,传入的数据可以是直接数据(如5),也可以是保存数据的变量.
OUT:表示输出参数,初始值为NULL,它是将存储过程中的值保存到OUT指定的参数中,返回给调用者
INOUT:表示输入输出参数,即参数在调用时传入到存储过程,同时在存储过程中操作之后,又可将数据返回为调用者

Delimiter $$ 
Create Procedure proc(In sid Int) 
Begin 
 Select id,name From sh_goods_category Where id>sid; 
End 
$$

①查看存储过程的创建语句
SHOW CREATE PROCEDURE 过程名;
②查看所有符合要求的存储过程
SHOW PROCEDURE STATUS[LIKE 匹配模式];

要想存储过程发挥作用,必须使用 MySQL提供的CALL语句调用
由于存储过程和数据库相关,如果要执行其他数据库中的存储过程,则调用时需要指定数据库名称
CAL L数据库名.存储过程名称([实参列表]);
实参列表传递的参数需要与创建存储过程的形参相对应;
当形参被指定为IN时,则实参值可以为变量或是直接数据;
当形参被指定为OUT或 INOUT时,调用存储过程传递的参数必须是一个变量,用于接收返回给调用者的数据.

Call proc(14);

3.存储过程的修改与删除
在 MySQL中可以使用 ALTER语句修改存储过程的特性。
ALTER PROCEDURE 存储过程名称[特征]；
特征指的是存储过程需要修改哪个部分.
ALTER PROCEDURE不能更改存储过程的参数或主体.

将存储过程的执行者改为调用者，并设置注释信息：

Alter Procedure Proc SQL Security Invoker 
Comment "从商品分类表中获取大于指定id值的数据";

当数据库中存在废弃的存储过程时,用 MYSQL提供的DROP语句删除存储过程。
DROP PROCEDURE [IF EXISTS]存储过程名称

4.存储过程的错误处理
在存储过程执行期间,可以对某些特定的错误代码丶警告或异常进行定义,然后再针对这些错误添加处理程序进行处理.
自定义错误处理:在编写存储过程时,可以使用 DECLARE语句为指定的错误声明一个名称。
DECLARE 错误名称 CONDITION FOR[错误类型]

#自定义错误处理
Delimiter $$
Create Procedure proc()
Begin
 Declare command_not_allowed Condition From Sqlstate "42000";
End
$$

错误的处理程序:为错误代码命名后,需要用 DECLARE… HANDLER语句为其设置处理程序.
DECLARE错误处理方式 HANDLER FOR错误类型[,错误类型]…程序语句段
MySQL支持的错误处理方式有两种,一个为 CONTINUE(遇到错误不处理,继续执行),另一个为EXIT(遇到错误时马上退出).
FOR后的错误类型可选值有6种,其中两种与 DECLARE… CONDITION FOR语句的错误类型相同,另外4种类型如下
使用 DECLARE. CONDITION FOR语句声明的错误代码名称.
SQLWARNING:表示所有以01开头的 SQLSTATE错误代码.
NOT FOUND:表示所有以02开头的 SQLSTATE错误代码.
SQLEXCEPTION:表示除以01或02开头外的所有 SQLSTATE错误代码.

存储过程设置错误处理，在不能插入数据时跳过，并继续执行:

Delimiter $$
Create Procedure proc_demo()
Begin
 Declare Continue Handler For Sqlstate "23000"
 Set @num=1;
 Insert Into sh_goods_category(id,name)Values(20,"运动");
 Set @num=2;
 Insert Into sh_goods_category(id,name)Values(20,'运动');
 set @num=3;
End
$$

调用存储过程，查看变量值的变化:

Call Proc_demo();
Select @num;