赵工的个人空间

存储过程是由流控制和SQL语句书写的过程，这个过程经编译和优化后存储在数据库服务器中，应用程序使用时只要调用即可。
一般的SQL语句，每执行一次就编译一次，而存储过程在创建时进行编译，以后每次执行都不需要重新编译。当对数据库进行复杂操作时，如对多个表进行结合操作，可将此操作封装起来，并可以重复使用，减少开发人员的工作量，还可以设定只有某些用户才具有使用权来提高安全性，并降低网络的通信量。

1. 创建存储过程：

存储子程序内不允许使用USE语句，数据库移除时与它关联的所有存储子程序也都被移除。MySQL中存储过程的建立以关键字CREATE PROCEDURE开始，后面紧跟存储过程的名称和参数。MySQL存储过程的名称不区分大小写，不过其名称不能与MySQL数据库的内建函数重名。创建存储过程的语法为：
CREATE PROCEDURE sp_name ([proc_parameter[,…]])
[characteristic…] routine_body
其中，sp_name参数是存储过程的名称；proc_parameter表示存储过程的参数列表；characteristic参数指定存储过程的特性；routine_body参数是SQL代码的内容，可以用begin... end 来标识SQL代码的开始和结束。
proc_parameter:
[IN|OUT|INOUT] param_name type
type:
Any valid MySQL data type
characteristic:
LANGUAGE SQL
|[NOT] DETERMINISTIC
|{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}
|SQL SECURITY {DEFINER|INVOKER}
|COMMENT ‘string’
routine_body:
Valid SQL procedure statement or statements
proc_parameter的参数有IN、OUT和INOUT三种，IN表示输入参数，OUT表示输出参数，INOUT表示既可以输入也可以输出。param_name参数是存储过程参数名称；type参数指定存储过程的参数类型，可以为MySQL数据库的任意数据类型。示例：
delimiter //
create procedure proc_name (in parameter integer)
begin
declare variable varchar(20);
if parameter=1 then
set variable='MySQL';
else
set variable='PHP';
end if;
insert into tb (name) values (variable);
end
//
delimiter ;
存储过程的参数一般由3个部分组成：
·第一部分：可以是in、out或inout。In表示向存储过程传入数据；out表示向外传出参数；inout表示定义的参数可传入存储过程，并可以被存储过程修改后传出存储过程。省略时默认为传入参数。
·第二部分：参数名
·第三部分：参数的类型，为MySQL所有可用的字段类型。如果有多个参数，参数之间用逗号分隔。
MySQL存储过程的语句块以begin开始，以end结束。语句体中可以包含变量的声明、控制语句、SQL查询语句等。
由于MySQL默认以“;”为分隔符，过程中的每一句都被MySQL以存储过程编译，这样编译过程会报错，所以事前要用DELIMITER关键字把当前段的分隔符换成另外一个字符，创建了存储过程之后就把分隔符还原。示例：delimiter //

2.创建存储函数：

创建存储函数与创建存储过程大体相同：
CREATE FUNCTION sp_name ([proc_parameter[,…]])
    RETURNS type
[characteristic…] routine_body
其中，RETURNS type指定返回值的类型。示例：
delimiter //
create function student_name (std_id INT)
returns varchar(50)
begin
return(select name from studentinfo where sid=std_id);
end
//
delimiter ;

3.变量：

MySQL存储过程中的参数主要有局部参数和回话参数两种，又可以被称为局部变量和会话变量。局部变量只在定义该局部变量的begin...end范围内有效，会话变量在整个存储过程范围内均有效。

1）局部变量：

局部变量以declare关键字声明，后跟变量名和变量类型。比如：
declare a int
也可以用default关键字为变量指定默认值。比如：
declare a int default 10
不声明默认值的变量，默认值为NULL。

2）全局变量：

MySQL中的会话变量不必声明即可使用。会话变量在整个过程中有效，会话变量名以字符@作为起始字符。示例：
delimiter //
create procedure p2 ()
begin
set @t=1;
begin
set @t=2;
select @t;
end;
select @t;
end;
//
delimiter ;

3）为变量赋值：

MySQL中，使用SET关键字为变量赋值。语法为：
SET var_name=expr[,var_name=expr]...
一个SET语句可以同时为多个变量赋值，各个变量的赋值语句之间用逗号分隔。示例：
SET mr_soft=10;
mySQL还有另一种为变量赋值的方法。语法为：
SELECT col_name[,...] INTO var_name[,...] FROM table_name where condition
其中，col_name参数表示查询的字段名称；var_name参数是变量的名称；table_name指定数据表的名称；condition指定查询条件。示例：
select tel into customer_tel from studentinfo where name='LeonSK';
上述赋值语句必须存在于创建的存储过程中，且需要将赋值语句放置在BEGIN...END之间。
4. 光标的运用：
通过MySQL查询数据库，其结果可能为多条记录。在存储过程和存储函数中使用光标可以实现逐条读取结果集中的记录。光标使用包括声明光标DECLARE CURSOR、打开光标OPEN CURSOR、使用光标FETCH CURSOR和关闭光标CLOSE CURSOR。光标必须声明在处理程序之前，且声明在变量和条件之后。

4.声明光标：

声明光标使用DECLARE关键字。语法为：
DECLARE cursor_name CURSOR FOR select_statement
其中，cursor_name是光标的名称；select_statement是一个SELECT语句，返回一行或多行数据。这个语句也可以在存储过程中定义多个光标，但是必须保证每个光标名称的唯一性。示例：
declare info_std cursor for select sid,name,age,sex from studentinfo where sid=1;
这里的SELECT子句中不能包含INTO子句，并且光标只能在存储过程或存储函数中使用，上述代码不能单独执行。

1）打开光标：

在声明光标后，要从光标中提取数据，必须首先打开光标。MySQL中，使用OPEN关键字来打开光标，语法为：
OPEN cursor_name
在程序中，一个光标可以打开多次。由于可能在用户打开光标后，其他用户或程序正在更新数据表，所以可能会导致用户在每次打开光标后显示的结果都不同。

2）使用光标：

光标在顺利打开后，可以使用FETCH...INTO语句来读取数据。语法为：
FETCH cursor_name INTO var_name[,var_name]...
其中，cursor_name为以及打开的光标名；var_name参数表示将光标中的SELECT语句查询出来的信息存入该参数中。var_name是存放变量的变量名，必须在声明光标前定义好。示例：
fetch info_std into tmp_name,tmp_tel;

3）关闭光标：

光标使用完毕后要及时关闭。MySQL采用CLOSE关键字关闭光标。语法为：
CLOSE cursor_name

5.流程控制语句：

在MySQL存储过程的语句块中可以使用流程控制语句，包括语句有：
·IF语句
·CASE语句
·LOOP语句
·LEAVE语句
·ITERATE语句
·REPEAT语句
·WHILE语句

1）IF语句：

IF语句用来进行条件判断，根据不同的条件执行不同的操作。IF语句的语法为：
IF condition THEN statement_list
[ELSEIF condition THEN statement_list]…
[ELSE statement_list]
END IF
IF语句实现了一个基本的条件构造。如果condition求值为真，相应的SQL语句列表被执行。如果没有condition匹配，在ELSE子句里的语句列表被执行。statement_list可以包括一个或多个语句。示例：
delimiter //
create procedure if_exam (out sum int)
begin
if x=1 then
select 1;
elseif x=2 then
select 2;
else
select 3;
end if;
end
//
delimiter ;

2）CASE语句：

CASE语句为多分枝结构。语法为：
CASE case_value
    WHEN when_value THEN statement_list
    [WHEN when_value THEN statement_list]…
[ELSE statement_list]
END CASE
或者
CASE
    WHEN condition THEN statement_list
    [WHEN condition THEN statement_list]…
[ELSE statement_list]
END CASE
存储过程的CASE语句实现一个复杂的条件构造。如果condition求值为真，相应的SQL被执行。如果没有搜索到匹配条件，在ELSE子句里的语句被执行。示例：
delimiter //
create procedure case_exam (out sum int)
begin
case x
when 1 then select 1;
when 2 then select 2;
else select 3;
end case;
end
//
delimiter ;

3）WHILE语句：

WHILE语句执行时首先判断condition条件是否为真，如果是则执行循环体，否则退出循环。 语法为：
[begin_label:]WHILE condition DO
statement_list
END WHILE [end_label]
WHILE语句内的语句或语句群被重复执行，直至condition为假。示例：
delimiter //
create procedure while_exam (out sum int)
begin
declare i int default 1;
declare s int default 0;
while i<=100 do
set s=s+i;
set i=i+1;
end while;
set sum=s;
end
//
delimiter ;
调用该存储过程：
call while_exam(@s)
mysql>select @s;

4）LOOP语句：

LOOP允许某些特定语句重复执行。该循环没有内置的循环条件，但可以通过LEAVE语句退出循环。语法为：
[begin_label:]LOOP
statement_list
END LOOP [end_label]
LOOP循环的语句一直重复执行直到循环被退出，退出一般使用LEAVE语句。
LOOP语句可以被标注。除非begin_label存在，否则end_label不能被给出，并且如果两者都出现，它们必须是同样的。示例：
delimiter //
create procedure loop_exam (out sum int)
begin
declare i int default 1;
declare s int default 0;
loop_label:loop
set s=s+i;
set i=i+1;
if i>100 then
leave loop_label;
end if;
end loop;
set sum=s;
end
//
delimiter ;
调用该存储过程：
call loop_exam(@s)
mysql>select @s;

5）REPEAT循环语句：

该语句先执行一次循环，之后判断condition条件是否为真，若为真则退出循环，否则继续执行循环。语法为：
[begin_label:]REPEAT
statement_list
UNTIL condition
END REPEAT [end_label]
REPEAT语句内的语句或语句群被重复执行，直至condition为真。
REPEAT语句可以被标注。除非begin_label存在，end_label才能被使用，如果两者都存在，它们必须是同样的。示例：
delimiter //
create procedure repeat_exam (out sum int)
begin
declare i int default 1;
declare s int default 0;
repeat
set s=s+i;
set i=i+1;
until i>100
end repeat;
set sum=s;
end
//

6）LEAVE语句：

LEAVE label
这个语句被用来退出任何被标注的流程控制构造，它和BEGIN…END或循环一起被使用。

7）ITERATE语句：

ITERATE label
ITERATE语句只可以出现在LOOP、REPEAT和WHILE语句内，意思是再次循环。示例：
CRAETE PROCEDURE doiterate(p1 INT)
BEGIN
  label1:LOOP
SET pi=p1+1;
IF p1<10 THEN ITERATE label1;END IF;
LEAVE label1;
 END LOOP label1;
 SET @x=p1;
END

6.使用存储过程和存储函数：

存储过程和存储函数，都是存储在服务器的SQL语句集合。要使用这些已经定义好的存储过程和存储函数就必须要通过调用的方式来实现。

1）调用存储过程：

可以使用CALL语句来调用存储过程。语法格式为：
CALL procedure_name([proc_parameter[,…]]);
示例：
mysql>USE text;
mysql>DELIMITER//
mysql>CREATE PROCEDURE dowhile();
mysql>USE text
->BEGIN
->DECLARE i INT DEFAULT 5;
->WHILE i>0 DO
    ->SELECT * FROM students;
    ->SET i=i-1;
    ->END WHILE;
->END //
创建了存储过程之后，需要将终止符修改为“;”，这样比较符合习惯：
mysql>DELIMITER;
mysql>CALL dowhile();

2）调用存储函数：

存储函数的使用方法与存储过程基本相同。语法为：
SELECT function_name([proc_parameter[,…]]);

3）SHOW STATUS查看存储过程：

存储过程创建后，存储在MySQL数据库的proc表中，可以查看创建的所有存储过程。语法为：
SHOW {PROCEDURE|FUNCTION} STATUS [LIKE 'pattern']
首先选择某个数据库：
mysql>use text;
然后查看这个数据库下的存储过程：
mysql>show procedure status;
SHOW STATUS语句只能查看存储过程和存储函数所操作的数据库对象，如名称、类型、定义者、修改时间等信息。

4）SHOW CREATE查看存储过程：

要查看存储过程和存储函数的详细定义，要使用SHOW CREATE。语法为：
SHOW CREATE {PROCEDURE|FUNCTION} sp_name
示例：
show create procedure count_of_student;

5）修改存储过程和存储函数：

MySQL使用ALTER PROCEDURE语句来修改存储过程，使用ALTER FUNCTION语句来修改存储函数。语法为：
ALTER {PROCEDURE|FUNCTION} sp_name [characteristic...]
characteristic:
{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}
 | SQL SECURITY {DEFINER|INVOKER}
 | COMMENT 'string'
其中，characteristic指定存储函数的特性；CONTAINS SQL表示子程序包含SQL语句，但不包括读写数据的语句；NO SQL表示子程序不包含SQL语句；READS SQL DATA表示子程序中包含读数据的语句；MODIFIES SQL DATA表示子程序中包含写数据的语句；SQL SECURITY {DEFINER|INVOKER}指明权限执行，DEFINER表示只有定义者自己才能执行，INVOKER表示调用者可以执行；COMMENT 'string'是注释信息。示例：
alter procedure count_std modifies sql data sql security invoker;
如果要查看修改后的结果，可以使用select from studentinfo.Ruotines where routine_name= 'sp_name'来查看表的信息。

6）删除存储过程：

MySQL使用DROP PROCEDURE语句来删除存储过程，使用DROP FUNCTION语句来删除存储函数。语法格式为：
DROP {PROCEDURE|FUNCTION} [IF EXISTS] sp_name
其中，IF EXISTS是MySQL扩展，判断存储过程和存储函数是否存在，以免发生错误。在删除之前，必须确认该存储过程或存储函数没有依赖关系，否则可能导致其他与其关联的存储过程无法运行。

7.捕获存储过程中的错误：

为了避免程序运行中产生的问题导致异常而终止运行，往往预先定义条件和处理程序来提示或提出解决办法。MySQL通过DECLARE关键字来定义条件和处理程序。

1）定义条件：

应用DECLARE语句定义条件。语法为：
DECLARE condition_name CONDITION FOR condition_value
condition_value:
   SQLSTATE [VALUE] sqlstate_value|mysql_error_code
其中，condition_name表示条件名称；condition_value表示类型；sqlstate_value表示错误；mysql_error_code表示错误代码。示例：
declare can_not_find condition for sqlstate '42S02';
declare can_not_find condition for 1146;
上述语句都为can_not_find错误进行了定义，第1种设置sqlstate_value值为42S02，第2种设置mysql_error_code的值为1146。

2）定义处理程序：

MySQL也可以使用DECLARE关键字来定义处理程序。语法为：
DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement
handler_type:
   CONTINUE|EXIT|UNDO
condition_value:
   SQLSTATE [VALUE] sqlstate_value|condition_name |SQLWARNING|NOT FOUND
   | SQLEXCEPTION|mysql_error_code
这个语句指定每个可以处理一个或多个条件的处理程序。如果产生一个或多个条件，指定的语句被执行。
参数说明：
handler_type指明错误的处理方式，有3个取值：
·CONTINUE：当前子程序的执行在执行处理程序语句之后继续。
·EXIT：当前BEGIN...END复合语句的执行被终止。
·UNDO：表示遇到错误后撤回之前的操作，MYSQL不支持该处理方式。
condition_value指定错误类型，有6个取值：
·sqlstate_value：表示MySQL的错误。
·condition_name：DECLARE定义的条件名称。
·SQLWARNING：表示所有以01开头的sqlstate_value值。
·NOT FOUND：表示所有以02开头的sqlstate_value值。
·SQLEXCEPTION：表示所有没有被SQLWARNING和NOT FOUND捕获的sqlstate_value值。
·mysql_error_code：表示错误代码。