存储过程简介
我们常用的操作数据库语言SQL语句在执行的时候需要要先编译,然后执行,而存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,用户通过指定存储过程的名字并给定参数(如果该存储过程带有参数)来调用执行它。
一个存储过程是一个可编程的函数,它在数据库中创建并保存。它可以有SQL语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的函数,或者封装特定功能时,存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟。它允许控制数据的访问方式。
存储过程通常有以下优点:
-
存储过程增强了SQL语言的功能和灵活性。存储过程可以用流控制语句编写,有很强的灵活性,可以完成复杂的判断和较复杂的运算。
-
存储过程允许标准组件是编程。存储过程被创建后,可以在程序中被多次调用,而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改,对应用程序源代码毫无影响。
-
存储过程能实现较快的执行速度。如果某一操作包含大量的Transaction-SQL代码或分别被多次执行,那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询,优化器对其进行分析优化,并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化,速度相对要慢一些。
-
存储过程能过减少网络流量。针对同一个数据库对象的操作(如查询、修改),如果这一操作所涉及的Transaction-SQL语句被组织程存储过程,那么当在客户计算机上调用该存储过程时,网络中传送的只是该调用语句,从而大大增加了网络流量并降低了网络负载。
-
存储过程可被作为一种安全机制来充分利用。系统管理员通过执行某一存储过程的权限进行限制,能够实现对相应的数据的访问权限的限制,避免了非授权用户对数据的访问,保证了数据的安全。
MySQL存储过程的创建
格式:
MySQL存储过程创建的格式:CREATE PROCEDURE 过程名 ([过程参数[,...]]),例:
delimiter //
create procedure pro_5()
begin
select * from tbl_user;
end//
注:
这里需要注意的是DELIMITER //,DELIMITER是分割符的意思,因为MySQL默认以";"为分隔符,如果我们没有声明分割符,那么编译器会把存储过程当成SQL语句进行处理,则存储过程的编译过程会报错,所以要事先用DELIMITER关键字申明当前段分隔符,这样MySQL才会将";"当做存储过程中的代码,不会执行这些代码,用完了之后要把分隔符还原。
存储过程根据需要可能会有输入、输出、输入输出参数,如果有多个参数用","分割开。
过程体的开始与结束使用BEGIN与END进行标识。
直接使用sql创建存储过程时,需要加DELIMITER,当使用工具去创建存储过程时,则不需要使用DELIMITER。
参数:
MySQL存储过程的参数用在存储过程的定义,共有三种参数类型,IN,OUT,INOUT,形式如:
CREATE PROCEDURE([[IN |OUT |INOUT ] 参数名 数据类形...])
IN 输入参数:表示该参数的值必须在调用存储过程时指定,在存储过程中修改该参数的值不能被返回,为默认值,例:
CREATE PROCEDURE PRO_1(IN v1 int)
BEGIN
SELECT V1;
SET V1 = 9;
SELECT V1;
END
SET @V1 = 12;
CALL PRO_1(@V1);
SELECT @V1;
输出结果为12,9,12,在存储过程中变量@V1被修改,在存储过程中能够输出修改后的值,但是这个值并不能够返回,在存储过程外,@V1仍然是12.
OUT 输出参数:该值可在存储过程内部被改变,并可返回,例:
CREATE PROCEDURE PRO_2(OUT v2 int)
BEGIN
SELECT V2;
SET V2 = 9;
SELECT V2;
END
SET @V1 = 12;
CALL PRO_2(@V1);
SELECT @V1;
输出结果为null,9, 9。out类型的输入实参是无效的,会默认为null,该值可在存储过程内部被改变,并可返回,所以在存储过程外@V1的值被改变。
INOUT 输入输出参数:调用时指定,并且可被改变和返回,例:
CREATE PROCEDURE PRO_3(INOUT V3 int)
BEGIN
SELECT V3;
SET V3 = 9;
SELECT V3;
END
SET @V1 = 12;
CALL PRO_3(@V1);
SELECT @V1;
输出结果为12, 9, 9。inout类型的输入参数有效,且在存储过程内被改变可以将值返回。
变量:
变量声明与赋值:
DECLARE variable_name datatype [DEFAULT value];其中,datatype为MySQL的数据类型,如:int, float, date, varchar(length),例如:
CREATE PROCEDURE PRO_4()
BEGIN
DECLARE V1 INT DEFAULT 9;
DECLARE V2 VARCHAR(5);
DECLARE V3 DOUBLE;
SET V3 = 9.12;
SELECT V1;
SELECT V2;
SELECT V3;
END
CALL PRO_4();
输出结果为9, null, 9.12。default可以设置默认值,也可以使用set赋值,如果没有赋值,则为null。
用户变量:
在MySql客户端使用用户变量:
SELECT "Hello World" INTO @S;
SELECT @S;
在存储过程中使用用户变量:
CREATE PROCEDURE PRO_5()
BEGIN
SELECT CONCAT(@STR1,' Word!');
END
SET @STR1 = 'Hello';
CALL PRO_5();
输出结果为Hello Word!
注释:
MySQL存储过程可使用两种风格的注释:
双模杠:--
该风格一般用于单行注释
c风格: /*一般用于多行注释*/
存储过程的调用
用call和存储过程名以及一个括号,括号里面根据需要,加入参数,参数包括输入参数、输出参数、输入输出参数。
注意:out 与 inout类型的变量不能够在调用存储过程时直接传值,应该传入变量。
存储过程的查询
想知道一个数据库下面有那些表,我们一般采用show tables;进行查看。那么我们要查看某个数据库下面的存储过程也可以通过命令查看,但是有所不同:
show procedure status where db='数据库名称';
查看单个存储过程的细节:
show create procedure 数据库.存储过程;
存储过程的修改
目前,MySQL还不提供对已存在的存储过程的代码修改 如果,一定要修改存储过程的代码,必须,先将存储过程删除之后,在重新编写代码,或者创建一个新的存储过程,或者使用像Navicat这样的工具进行修改。
存储过程的删除
删除一个存储过程比较简单,和删除表一样:
DROP PROCEDURE [IF EXISTS] 存储过程名称,例:
drop procedure if exists PRO_1;
存储过程控制语句
变量作用域:
内部的变量在其作用域范围内享有更高的优先权,当执行到end时,内部变量消失,此时已经在其作用域外,变量不再可见了,因为在存储过程外再也不能找到这个声明的变量,但是我们可以通过out参数或者将其值指派给会话变量来保存其值。
CREATE PROCEDURE PRO_6()
BEGIN
DECLARE V1 VARCHAR(10) DEFAULT '外部变量';
BEGIN
DECLARE V1 VARCHAR(10) DEFAULT '内部变量';
SELECT V1;
END;
SELECT V1;
END
CALL PRO_6();
输出结果为:
内部变量,外部变量。
条件语句:
if-then-else语句
CREATE PROCEDURE PRO_7(IN V int)
BEGIN
DECLARE K INT;
SET K = V + 1;
IF K = 1 THEN
SELECT K;
ELSE
SELECT V;
END IF;
END
CALL PRO_7(0);
输出为1;
case语句
CREATE DEFINER=`root`@`localhost` PROCEDURE `PRO_8`(IN `V` int)
BEGIN
DECLARE K INT;
SET K = V + 1;
CASE K
WHEN K = 1 THEN
SELECT * FROM TBL_USER;
WHEN K = 2 THEN
SELECT * FROM TBL_STUDENT;
ELSE
SELECT V;
END CASE;
END
while语句
CREATE PROCEDURE PRO_9()
BEGIN
DECLARE V INT DEFAULT 0;
WHILE V < 5 DO
INSERT INTO TBL_USER VALUES (V, SUBSTRING((RAND()), 1, 5), V);
SET V = V + 1;
END WHILE;
END
repeat语句
CREATE PROCEDURE PRO_10()
BEGIN
DECLARE V INT DEFAULT 0;
REPEAT
INSERT INTO TBL_USER VALUES (V, SUBSTRING(MD5(RAND()), 1, 5), V);
SET V = V + 1;
UNTIL V > 5 END REPEAT;
END
repeat语句是在执行后判断,而while实在执行前判断。
loop语句
CREATE PROCEDURE PRO_11()
BEGIN
DECLARE V INT DEFAULT 20;
LOOP1: LOOP
INSERT INTO TBL_USER VALUES (V, SUBSTRING(MD5(RAND()), 1, 5), V);
SET V = V + 1;
IF V = 30 THEN
LEAVE LOOP1;
END IF;
END LOOP LOOP1;
END