数据库（存储过程、存储函数、游标）

[袁宁茹]

存储过程：

创建存储过程

存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存放在数据库中，用户通过指定存储过程名字并给出参数（如果该存储过程带有参数）来执行它。

存储过程是数据库中的一个重要对象，包含各种SQL语句及流程控制语句，可以接受参数、输出参数、返回单个或多个结果。

创建存储过程语句

CREATE PROCEDURE sp_name ([proc_parameter[,...]])

[characteristic ...] routine_body

• sp_name：存储过程中的名称。
• proc_parameter：存储过程中的参数列表，
• in表示输入参数，out表示输出参数，
• inout即表示输入又表示输出参数。
• routine_body是SQL代码的内容，可以用BEGIN...END来表示SQL代码的开始和结束。
• DELIMITER命令可以将MySQL语句的结束标志临时修改为其他符号，从而使得MySQL服务器可以完整地处理存储过程体中所有的SQL语句。
• DELIMITER命令的语法格式是：

             DELIMITER $$

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

 

案例1:

创建存储过程P_GetAllEmployees

返回所有雇员的姓名、工号和上级的工号。

create procedure P_GetAllEmployees()

BEGIN

select Name,EmployeeID,ReportsTo

from employees;

end;

 

 

案例2:

创建存储过程P_Getlist

返回其价格小于指定金额（50）的产品的平均价格

方法一：

create procedure P_Getlist2(in price decimal(10,2),

out avg_price decimal(10,2))

BEGIN

DECLARE price1 DECIMAL(10,2);

set price1 = (select avg(UnitPrice)

 from products

 where UnitPrice < price);

end;

 

方法二：

create procedure P_Getlist(in price decimal(10,2),

out avg_price decimal(10,2))

BEGIN

DECLARE price1 DECIMAL(10,2);

select avg(UnitPrice)

into price1

from products

where UnitPrice < price;

 

set avg_price = price1;

end;

调用存储过程

MySQL中使用CALL语句来调用存储过程。调用存储过程后，数据库系统将执行存储过程中的语句。然后将结果返回给输出值。

CALL语句的基本语法形式如下：

    CALL sp_name([parameter[,…]]);

 其中sp_name是存储过程的名称；parameter是存储过程的参数。

 

案例1：call P_GetAllEmployees();

 

案例2：call P_Getlist(50,@avg);

             select @avg;

 

管理存储过程：

查看存储过程

用户可以通过SHOW STATUS语句查看存储过程状态，也可以通过SHOW CREATE语句来查看存储过程的定义，还可以通过查询information_schema.routines表来获取存储过程的信息。

SHOW STATUS语法结构如下：

     SHOW PROCEDURE STATUS；

SHOW CREATE语法结果如下：

     SHOW CREATE PROCEDURE sp_name;

修改存储过程

    修改存储过程是指修改已经定义好的存储过程和函数。MySQL中通过ALTER PROCEDURE语句来修改存储过程。基本语法格式如下：

    ALTER PROCEDURE sp_name [characteristic ...]

删除存储过程

    删除存储过程指删除数据库中已经存在的存储过程。

    MySQL中使用DROP PROCEDURE语句来删除存储过程。

    在删除之前，必须确认该存储过程没有任何依赖关系，否则可能会导致其他与其关联的存储过程无法运行。

    删除存储过程的语法如下：

       DROP PROCEDURE [IF EXISTS] sp_name;

 

调用存储过程

    MySQL中使用CALL语句来调用存储过程。调用存储过程后，数据库系统将执行存储过程中的语句。然后将结果返回给输出值。

    CALL语句的基本语法形式如下：

         CALL sp_name([parameter[,…]]);

     其中sp_name是存储过程的名称；parameter是存储过程的参数。

 

 

存储函数

创建存储函数

    创建存储函数的语句是CREATE FUNCTION语句，与创建存储过程大体相同。基本语法格式如下：

        CREATE FUNCTION sp_name ([func_parameter[,...]])

        RETURNS type

        [characteristic ...] routine_body

    参数不能指定in、out和inout; RETURNS type指定参数返回类型。

     存储函数体中必须包含一个return value语句。

案例3：

create FUNCTION f1(p_ProductID int)

RETURNS smallint   #如果没有权限在此处加 reads SQL DATA

BEGIN

DECLARE num smallint;

select UnitsInStock

into num

from products

where ProductID = p_ProductID;

return num;

end;

 

调用存储函数

    在MySQL中，存储函数的使用方法与MySQL内部函数的使用方法基本相同。

    用户自定义的存储函数与MySQL内部函数性质相同。区别在于，存储函数是用户自定义的，而内部函数由MySQL自带。

    其语法结构如下：

          SELECT function_name([parameter[,…]]);

案例3: select f1(6);

管理存储函数

• 查看存储函数

    用户可以通过SHOW STATUS语句查看存储函数状态，也可以通过SHOW CREATE语句来查看存储函数的定义，还可以通过查询information_schema.routines表来获取存储函数的信息。

SHOW STATUS语法结构如下：

      SHOW FUNCTION STATUS[LIKE 'pattern’]

SHOW CREATE语法结果如下：

      SHOW CREATE FUNCTION sp_name;

 

• 修改存储函数

      MySQL中通过ALTER FUNCTION语句来修改存储函数。

• 删除存储函数

DROP FUNCTION [IF EXISTS] sp_name;

 

 

使用游标

通过MySQL查询数据库，其结果可能为多条记录。在存储过程和函数中使用游标可以实现逐条读取结果集中的记录。

游标使用：

     声明游标（DECLARE CURSOR）

     打开游标(OPEN CURSOR)

     使用游标(FETCH CURSOR)

     关闭游标(CLOSE CURSIR)。

游标必须声明在处理程序之前，且声明在变量和条件之后。

 

 声明游标

     DECLARE cursor_name CURSOR FOR select_statement;

说明：cursor_name是游标的名称，游标名称使用与表名同样的规则。select_statement是一个SELECT语句，返回的是一行或多行数据。

 

 打开游标

 声明游标后，要使用游标从中提前数据，就必须先打开游标。

OPEN cursor_name;

 

读取数据

    游标打开后，就可以使用FETCH…INTO语句从中读取数据。

    语法格式如下：

    FETCH cursor_name INTO  var_name [, var_ name]...

    说明：

     var_name是存放数据的变量名。

     FETCH…INTO语句与SELECT…INTO语句具有相同的意义，FETCH语句是将游标指向的一行数据赋给一些变量，子句中变量的数目必须等于声明游标时SELECT子句中列的数目。

 

关闭游标

关闭游标使用CLOSE语句，语法格式如下：

   CLOSE cursor_name;

 

案例4：创建存储过程p_orderdetails，

用游标输出给定订单号的产品号、单价、数量和有无折扣，并用来查看订单号为10250的订单数据；

 

create PROCEDURE p_orderdetails(in p_OrderID int)

BEGIN

declare v_ProductID int;

declare v_UnitPrice decimal(10,2);

declare v_Quantity smallint;

declare v_Discount float;

declare done int default 1;

 

declare c_cur cursor FOR

select ProductID,UnitPrice,Quantity,Discount

from orderdetails

where OrderID=p_OrderID;

declare continue handler for not found set done = 0;

open c_cur;

while done != 0  do

    fetch c_cur into v_ProductID,v_UnitPrice,

 v_Quantity,v_Discount;

select v_ProductID,v_UnitPrice,

 v_Quantity,v_Discount;#输出

end while;

close c_cur;

end;

 

call p_orderdetails(10250);