存储过程和函数

迈不过友情╰ 2022-09-29 10:56 235阅读 0赞

1.存储程序的语法

存储程序和函数是用CREATE PROCEDURE和CREATE FUNCTION语句创建的子程序。一个子程序要么是一个程序要么是一个函数。使用CALL语句来调用程序，程序只能用输出变量传回值。就像别其它函数调用一样，函数可以被从语句外调用（即通过引用函数名），函数能返回标量值。存储子程序也可以调用其它存储子程序。

在MySQL 5.1中，一个存储子程序或函数与特定的数据库相联系。这里有几个意思：

·      当一个子程序被调用时，一个隐含的USE *db\_name* 被执行（当子程序终止时停止执行）。存储子程序内的USE语句时不允许的。

·      你可以使用数据库名限定子程序名。这可以被用来引用一个不在当前数据库中的子程序。比如，要引用一个与test数据库关联的存储程序p或函数f，你可以说CALL test.p()或test.f()。

·     数据库移除的时候，与它关联的所有存储子程序也都被移除。

1.1CREATE 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

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

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

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

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'

3 DROP PROCEDURE和DROP FUNCTION

DROP {PROCEDURE | FUNCTION} [IF EXISTS] sp_name

4.SHOW CREATE PROCEDURE和SHOW CREATE FUNCTION

###  ###

SHOW CREATE {PROCEDURE | FUNCTION} sp_name

5.SHOW PROCEDURE STATUS和SHOW FUNCTION STATUS

SHOW {PROCEDURE | FUNCTION} STATUS [LIKE 'pattern']

6. CALL语句

CALL sp_name([parameter[,...]])

7BEGIN ... END复合语句

[begin_label:] BEGIN

[statement_list]

END [end_label]

存储子程序可以使用BEGIN ... END复合语句来包含多个语句。*statement\_list* 代表一个或多个语句的列表。*statement\_list*之内每个语句都必须用分号（；）来结尾。

复合语句可以被标记。除非*begin\_label*存在,否则*end\_label*不能被给出,并且如果二者都存在,他们必须是同样的。

8.declare

DECLARE语句被用来把不同项目局域到一个子程序：局部变量，条件和处理程序，及光标。

DECLARE仅被用在BEGIN ... END复合语句里，并且必须在复合语句的开头，在任何其它语句之前。

光标必须在声明处理程序之前被声明，并且变量和条件必须在声明光标或处理程序之前被声明。

9.存储程序中的变量

9.1DECLARE局部变量

####  ####

DECLARE var_name[,...] type [DEFAULT value]

这个语句被用来声明局部变量。要给变量提供一个默认值，请包含一个DEFAULT子句。值可以被指定为一个表达式，不需要为一个常数。如果没有DEFAULT子句，初始值为NULL。

9.2 变量SET语句

SET var_name = expr [, var_name = expr] ...

在存储程序中的SET语句是一般SET语句的扩展版本。被参考变量可能是子程序内声明的变量，或者是全局服务器变量。

在存储程序中的SET语句作为预先存在的SET语法的一部分来实现。这允许SET a=x, b=y, ...这样的扩展语法。其中不同的变量类型（局域声明变量及全局和集体变量）可以被混合起来。这也允许把局部变量和一些只对系统变量有意义的选项合并起来。在那种情况下，此选项被识别，但是被忽略了。

9.3 SELECT ... INTO语句

SELECT col_name[,...] INTO var_name[,...] table_expr

这个SELECT语法把选定的列直接存储到变量。因此，只有单一的行可以被取回。

SELECT id,data INTO x,y FROM test.t1 LIMIT 1;

CREATE PROCEDURE sp1 (x VARCHAR(5))
      BEGIN
        DECLARE xname VARCHAR(5) DEFAULT 'bob';
        DECLARE newname VARCHAR(5);
        DECLARE xid INT;
        
        SELECT xname,id INTO newname,xid 
          FROM table1 WHERE xname = xname;
        SELECT newname;
      END;

当这个程序被调用的时候，无论table.xname列的值是什么，变量newname将返回值‘bob’。

10. 条件和处理程序   
  
10.1  DECLARE条件

DECLARE condition_name CONDITION FOR condition_value

condition_value:

SQLSTATE [VALUE] sqlstate_value

| mysql_error_code

这个语句指定需要特殊处理的条件。它将一个名字和指定的错误条件关联起来。这个名字可以随后被用在DECLARE HANDLER语句中。

10.2 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

这个语句指定每个可以处理一个或多个条件的处理程序。如果产生一个或多个条件，指定的语句被执行。

对一个CONTINUE处理程序，当前子程序的执行在执行处理程序语句之后继续。对于EXIT处理程序，当前BEGIN...END复合语句的执行被终止。UNDO 处理程序类型语句还不被支持。

CREATE PROCEDURE handlerdemo ()
     BEGIN
       DECLARE CONTINUE（还可以为exit） HANDLER FOR SQLSTATE '23000' SET @x2 = 1;//表示遇到23000，继续执行
       SET @x = 1;
       INSERT INTO test.t VALUES (1);
       SET @x = 2;
       INSERT INTO test.t VALUES (1);
       SET @x = 3;
     END;

11.光标

简单光标在存储程序和函数内被支持。语法如同在嵌入的SQL中。光标当前是不敏感的，只读的及不滚动的。不敏感意为服务器可以活不可以复制它的结果表。

光标必须在声明处理程序之前被声明，并且变量和条件必须在声明光标或处理程序之前被声明。

CREATE PROCEDURE curdemo()
    BEGIN
      DECLARE done INT DEFAULT 0;
      DECLARE a CHAR(16);
      DECLARE b,c INT;
      DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
      DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
      DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
     
      OPEN cur1;
      OPEN cur2;
     
      REPEAT
        FETCH cur1 INTO a, b;
        FETCH cur2 INTO c;
        IF NOT done THEN
           IF b < c THEN
              INSERT INTO test.t3 VALUES (a,b);
           ELSE
              INSERT INTO test.t3 VALUES (a,c);
           END IF;
        END IF;
      UNTIL done END REPEAT;
     
      CLOSE cur1;
      CLOSE cur2;
    END

11.1声明光标

DECLARE cursor_name CURSOR FOR select_statement

这个语句声明一个光标。也可以在子程序中定义多个光标，但是一个块中的每一个光标必须有唯一的名字。

SELECT语句不能有INTO子句。

11.2 光标OPEN语句

OPEN cursor_name

这个语句打开先前声明的光标。

11.3 光标FETCH语句

FETCH cursor_name INTO var_name [, var_name] ...

这个语句用指定的打开光标读取下一行（如果有下一行的话），并且前进光标指针。

11.4 close

CLOSE cursor_name