MySQL 存储过程和函数

MySQL 存储过程和函数

一、实验介绍

1.1 实验内容

本实验中将学习并实践 MySQL 中的存储过程和函数的创建及使用方法。

1.2 实验知识点

• 存储过程
• 函数

1.3 实验环境

课程使用的实验环境为 Ubuntu Linux 14.04 64 位版本。实验中会用到程序：

• Mysql 5.5.50
• Xfce终端

二、实验步骤

2.1 创建存储过程和函数

CREATE PROCEDURE和CREATE FUNCTION的语法：

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

CREATE FUNCTION sp_name ([func_parameter[,...]])
    RETURNS type
    [characteristic ...] routine_body

    proc_parameter:
    [ IN | OUT | INOUT ] param_name type

    func_parameter:
    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

这些语句用于创建并存储子程序。要在MySQL 5.1中创建子程序，必须具有CREATE ROUTINE权限，并且ALTER ROUTINE和EXECUTE权限被自动授予它的创建者。

默认情况下，子程序与当前数据库关联。要明确地把子程序与一个给定数据库关联起来，可以在创建子程序的时候指定其名字为db_name.sp_name。

如果子程序名和SQL内部函数名一样，在定义子程序时，你需要在这个名字和随后括号中间插入一个空格，否则发生语法错误。在你之后调用子程序的时候也要插入。为此，即使有可能出现这种情况，我们还是建议最好避免给你的存储子程序取与已经存在的SQL函数一样的名字。

由括号包围的参数列必须总是存在。如果没有参数，也该使用一个空参数列()。每个参数默认都是一个IN（内部）参数。要指定为其它参数，可在参数名之前使用关键词 OUT或INOUT。

注意： 指定参数为IN， OUT， 或INOUT 只对PROCEDURE是合法的。（FUNCTION参数总是被认为是IN）

下面是一个使用OUT参数的简单的存储过程的例子。例子为：在程序被定义的时候，用mysql客户端delimiter命令来把语句定界符从;变为//。这就允许用在程序体中的;定界符被传递到服务器而不是被mysql自己来解释。

mysql> delimiter //
mysql> CREATE PROCEDURE simpleproc (OUT param1 INT)
    -> BEGIN
    ->   SELECT COUNT(*) INTO param1 FROM t;
    -> END
    -> //
mysql> delimiter ;
mysql> CALL simpleproc(@a);
mysql> SELECT @a;

当使用delimiter命令时，你应该避免使用反斜杠(‘\’)字符，因为那是MySQL的转义字符。

下列是一个例子，一个采用参数的函数使用一个SQL函数执行一个操作，并返回结果：

mysql> delimiter //
mysql> CREATE FUNCTION hello (s CHAR(20)) RETURNS CHAR(50)
    -> RETURN CONCAT('Hello, ',s,'!');
    -> //
mysql> delimiter ;
mysql> SELECT hello('world');

如果在存储函数中的RETURN语句中返回一个类型不同于在函数的RETURNS子句中指定类型的值，返回值被强制转换为相对恰当的类型。比如，如果一个函数返回一个ENUM或SET值，但是RETURN语句返回一个整数，对于SET成员集合的相应的ENUM成员，从函数返回的值是字符串类型数据。

2.2 修改存储过程和函数

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'

这个语句可以被用来改变存储过程或函数的特