为什么要使用存储过程?
答:1.可以增强SQL语言的功能和灵活性,存储过程可以用流程语句来编写,可以完成复杂的判断和运算
2.良好的封装性。存储过程被创建后,可以在程序中反复的被调用,而不必重新编写该存储过程的SQL语句。
3.高性能。存储过程执行一次后,其执行规划就驻留在高速缓冲存储器中,在以后的操作中只用从中调用已编译好的二进制代码执行即可。
4.可以减少网络流量。由于存储过程是在服务器端运行,且执行速度快,客户调用该存储过程时,网路传送的只是改调用语句,降低了网络负载。
5.存储过程可作为一种安全机制来确保数据库的安全性与完整性。
MySQL中,创建存储过程的基本形式如下:
CREATE PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body </span>
sp_name参数是存储过程的名称;
proc_parameter表示存储过程的参数列表;
characteristic参数指定存储过程的特性;
routine_body参数是SQL代码的内容,可以用BEGIN…END来标志SQL代码的开始和结束。
proc_parameter中的每个参数由3部分组成。这3部分分别是输入输出类型、参数名称和参数类型。其形式如下:
[ IN | OUT | INOUT ] param_name type
IN表示输入参数;OUT表示输出参数; INOUT表示既可以是输入,也可以是输出;
param_name参数是存储过程的参数名称;
type参数指定存储过程的参数类型,该类型可以是MySQL数据库的任意数据类型。
characteristic参数有多个取值。其取值说明如下:
LANGUAGE SQL:说明routine_body部分是由SQL语言的语句组成,这也是数据库系统默认的语言。
[NOT] DETERMINISTIC:指明存储过程的执行结果是否是确定的。
DETERMINISTIC表示结果是确定的。每次执行存储过程时,相同的输入会得到相同的输出。
NOT DETERMINISTIC表示结果是非确定的,相同的输入可能得到不同的输出。默认情况下,结果是非确定的。
{ CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }:指明子程序使用SQL语句的限制。
CONTAINS SQL表示子程序包含SQL语句,但不包含读或写数据的语句;
NO SQL表示子程序中不包含SQL语句;
READS SQL DATA表示子程序中包含读数据的语句;
MODIFIES SQL DATA表示子程序中包含写数据的语句。
默认情况下,系统会指定为CONTAINS SQL。
SQL SECURITY { DEFINER | INVOKER }:指明谁有权限来执行。
DEFINER表示只有定义者自己才能够执行;
INVOKER表示调用者可以执行。默认情况下,系统指定的权限是DEFINER。
COMMENT 'string':注释信息。
技巧:创建存储过程时,系统默认指定CONTAINS SQL,表示存储过程中使用了SQL语句。但是,如果存储过程中没有使用SQL语句,最好设置为NO SQL。而且,存储过程中最好在COMMENT部分对存储过程进行简单的注释,以便以后在阅读存储过程的代码时更加方便。
例子:创建一个removeSexAndReturnInfos存储过程,在对相对应的sex删除时,同时会输出执行的行数,以及所剩的数目。(创建多个OUT类型参数的存储过程,同时创建单个OUT,或没有OUT,获没有参数均是可以使用存储过程的)
mysql> DELIMITER //
mysql> CREATE PROCEDURE removeSexAndReturnInfos (IN csex CHAR(1),OUT deleteUser SMALLINT UNSIGNED,
OUT userCounts SMALLINT UNSIGNED)
-> BEGIN
-> DELETE FROM custs WHERE cust_sex=csex;
-> SELECT ROW_COUNT() INTO deleteUser;
-> SELECT COUNT(cust_id) FROM custs INTO userCounts;
-> END
-> //
</span>
创建存储过程后,调用存储过程
mysql>Call removeSexAndReturnInfos('G',@a,@b)
说明:@a @b 为参数,其他形式均可
mysql>SELECT @a,@b;
输出结果为两个参数所代表的值,就是行数
说明:MySQL中默认的语句结束符为分号(;)。存储过程中的SQL语句需要分号来 结束。为了避免冲突,首先用"DELIMITER &&"将MySQL的结束符设置为&&。最后再用"DELIMITER ;"来将结束符恢复成分号。这与创建触发器时是一样的。