1、简介
        存储过程(Stored Procedure)是一组为了完成特定功能的SQL语句集,经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。存储过程是数据库中的一个重要对象,任何一个设计良好的数据库应用程序都应该用到存储过程。
        存储过程是由流控制和SQL语句书写的过程,这个过程经编译和优化后存储在数据库服务器中,应用程序使用时只要调用即可。
2、重要概念和疑问解答
        1)何谓informix中的SPL?
        SPL(Stored Procedure Language)是informix中提供的一种提供流程控制(分支和循环)的SQL。它包括SPL过程(没有返回值)和SPL函数(有返回值)。SPL将在创建的时候被解析和优化,它以可执行的方式保存在system catalog表中。
        2)使用SPL为什么会改善性能?
        i)因为它运行在informix引擎中,所以减少了I/O;
        ii)降低了应用的复杂性;
        iii)对频率较高的重复操作使用更好。
        3)SPL的灵活性表现在哪些部分?
        i)它可在其它SQL语法中被调用;
        ii)它也可以在触发器中使用;
        iii)可在SQL中添加流程控制;
        iv)很容易维护。
       4)SPL的参数
       i)可以传递很多个参数;
       ii)SPL的参数的最大限制是32K;
       iii)除了如下两种外可以是任何一种SQL数据类型:
             Serial/Serial8
             Text/Byte (能够使用REFERENCES关键字传递)。
       iv)能够使用很复杂或者用户自定义的数据类型;
       v)可以通过default为参数指定默认值。
       5)SPL函数的返回值
       i)可以使用RETURNING或RETURNS关键字定义返回值的返回类型;
       ii)除了如下两种数据类型外可以为任何数据类型:
            Serial/Serial8
            Text/Byte (可以使用REFERENCES关键字传递)。
       iii)在过程体内必须至少有一个return语句;
       iv)可以为返回值取一个名称。例如:
 4、SPL中的流程控制
     1)分支语句if...then...else...end if
     参考实例:
     2)分支语句case
     参考实例:

CREATE FUNCTION val_comp (val1 INTEGER, val2 INTEGER)
RETURNING 
INTEGER AS comp_res;

      6)SPL的重载
      可定义多个具有相同名字的SPL函数;例如如下SPL函数虽然名称相同,但是参数类型或各不相同:

CREATE FUNCTION val_comp (val1 INTEGER, val2 INTEGER)
CREATE FUNCTION val_comp (val1 CHAR(25), val2 CHAR(25))
CREATE FUNCTION val_comp (val1 DECIMAL, val2 DECIMAL)

     可使用SPECIFIC关键字指定SPL函数的简短的、独一无二的被重载的SPL的函数的名称。这个名字在数据库中是唯一的。例如:

CREATE FUNCTION val_comp (val1 INTEGER, val2 INTEGER) SPECIFIC int_comp
CREATE FUNCTION val_comp (val1 CHAR(25), val2 CHAR(25)) SPECIFIC string_comp
CREATE FUNCTION val_comp (val1 DECIMAL, val2 DECIMAL) SPECIFIC dec_comp

     7)何谓语句块?
     i)语句块是一组SPL或SQL语句;
     ii)隐式的语句块包含在CREATE PROCEDURE/FUNCTION和END PROCEDURE/FUNCITON之间;
     iii)可使用BEGIN和END来显式的知名内嵌在另一个语句块中的语句块,例如:

CREATE PROCEDURE myproc1() -- 隐式的语句块的开始
DEFINE x INT;
LET x 
= 15;
INSERT INTO table1 VALUES (x, 'amigo');
BEGIN -- 显式的语句块的开始
DEFINE y INT;
LET y 
= 16;
INSERT INTO table1 VALUES ( y, 'xingxing');
END -- 显式的语句块的结束
END PROCEDURE-- 隐式的语句块的结束

      8)如何定义变量?
      i)是语句块中使用DEFINE定义变量;
      ii)变量在内存中,而不是在数据库中保存;
      iii)有本地变量和全局变量两种;
      iv)变量可以是除了如下两种类型之外的任何一种SQL数据类型和扩展类型:
             Serial/Serial8
             Text/Byte (可以使用REFERENCES关键字来声明)。
      定义变量举例如下:

DEFINE x, y INT-- 内建的数据类型
DEFINE p person_type; -- 用户自定义的数据类型person_type
DEFINE mymusic REFERENCES BYTE; -- 使用REFERENCES关键字定义BYTE类型

      9)本地变量和全局变量比较
      本地变量:
      i)本地变量值在SPL中有效;
      ii)在SPL函数或过程结束后就被复位;
      iii)它不能有默认值;
      iv)它的作用域是在它定义的语句块中,或者任何内嵌语句块中;
      v)本地变量能够被重新定义在另一个语句块中。
     下面来看一个本地变量的实例:

CREATE PROCEDURE local_scope()
DEFINE x,y,z 
INT;
LET x 
= 5;
LET y 
= 10;
LET z 
= x + y; -- z等于15
BEGIN
  DEFINE x, q 
INT-- x被重新定义
  DEFINE z CHAR(5); -- z被重新定义
  LET x = 100;
  LET q 
= x + y; -- q=110
  LET z = 'amigo'-- 给z设置了一个新的值
END
LET y 
= x; -- y等于5
LET x = z; -- z的值是15,而不是amigo
   END PROCEDURE;

     全局变量:
     i)在相同数据库中使用相同的用户会话的地方都能得到全局变量的值;
     ii)必须有一个默认值;
     iii)必须在每一个要使用它的SPL函数或过程中定义;
     iv)不能是一个集合变量。
     例如如下定义了两个SPL函数,名称分别为func1和func2,func1参考语句如下:

CREATE FUNCTION func1() RETURNING INT;
DEFINE GLOBAL gvar 
INT DEFAULT 2;
LET gvar 
= gvar + 1;
RETURN gvar;
END FUNCTION;

     func2参考语句如下:

CREATE FUNCTION func2()RETURNING INT;
DEFINE GLOBAL gvar 
INT DEFAULT 5;
LET gvar 
= gvar + 1;
RETURN gvar;
END FUNCTION;

     如果执行两者的顺序如下:

EXECUTE FUNCTION func1();
EXECUTE FUNCTION func2();

     则执行完第一句后,gvar被设置了默认值2,且执行了加1的操作,所以第一句执行完毕后gvar的值为3.
     接着执行第二句,第这次不在设置gvar的默认值,因此在3的基础上再执行了加1操作,执行完毕后gvar的值为4.
     若执行两者的顺序如下:

EXECUTE FUNCTION func2();
EXECUTE FUNCTION func1();

     则执行完第一句后,gvar被设置了默认值5,且执行了加1的操作,所以第一句执行完毕后gvar的值为6.
     接着执行第二句,第这次不在设置gvar的默认值,因此在6的基础上再执行了加1操作,执行完毕后gvar的值为7.
     10)给变量赋值
     i)使用一个未定义的变量将会报错;
     ii)给已定义的变量赋值的方法:
           * 使用LET语句直接给变量赋初值,参考语句如下:

      LET <变量> = <有效的表达式或函数名>;

          *使用SELET INTO语句将查询到的结果给变量赋值,参考语句如下:

      SELECT … INTO <变量> FROM …;

          *使用CALL...RETURNING语句将返回结果赋给变量,参考语句如下:

       CALL … RETURNING <变量>;

          *使用EXECUTE FUNCTION INTO语句,将返回结果赋给变量,参考语句如下:

       EXECUTE FUNCTION … INTO <变量>;

3、语法
 
      1)创建SPL过程

CREATE PROCEDURE name (parameter list) SPECIFIC specific_name
… {语句块}
END PROCEDURE;

      例如,在下面的实例中创建了一个名为set_status的存储过程,可传入myid和mystatus两个参数,在这个存储过程中,对item_inventory表进行update操作,id字段满足myid的记录将status字段更新为mystatus。参考语句如下:

CREATE PROCEDURE set_status (myid INTEGER DEFAULT 0, mystatus CHAR(25))
UPDATE item_inventory SET status = mystatus WHERE id = myid;
END PROCEDURE;

       2)创建SPL函数

CREATE FUNCTION name (parameter list)
RETURNING list SPECIFIC specific_name
… {语句块}
END FUNCTION;

      例如,创建一个名为val_comp的SPL函数,可传入val1和val2两个参数,在该函数中,比较这两个变量,如果两者相等,返回0,否则返回1。参考语句如下:

CREATE FUNCTION val_comp (val1 INTEGER, val2 INTEGER)
RETURNING 
INTEGER;
DEFINE res 
INTEGER;
IF (val1 = val2) THEN
LET res 
= 0;
ELSE
LET res 
= 1;
END IF;
RETURN res;
END FUNCTION;

 

IF ( condition ) THEN
   statements
ELIF ( condition ) 
THEN
   statements
   …
ELSE
   statements
END IF;

 

CASE ( condition )
   
WHEN <value1> THEN
      statements
   
WHEN <value2> THEN
      statements
      …
    
ELSE
      statements
END CASE;

     3)循环语句for
     参考语法如下:
 

FOR 变量 IN ( 范围或值 )

     或者使用如下语法:
 

FOR 变量=范围

     举例如下:
 

FOR count = 2 TO 30
FOR count = 2 TO 30 STEP 2
FOR count IN ( 2579)
FOR count IN ( 2 to 8 STEP 212 to 1922)
FOR name IN ("AMY", "MAX",
(
SELECT name FROM customer WHERE customer_num = 100) )

     4)循环语句while
     参考语法如下所示:
 

WHILE (条件表达式)
   执行的语句
END WHILE;

      while中的条件表达式的举例如下:
 

WHILE (count < 20)
WHILE (status matches "A*")
WHILE (EXISTS (SELECT name FROM customer WHERE cus_num=100))
WHILE (status IN ("A", "I", "D"))

      5)循环语句foreach
      i)被用来取多行数据;
      ii)它打开了一个游标;
      iii)游标用来取得当前的行,以便进行行的更新或删除操作。
      参考实例如下:
 

FOREACH SELECT salary INTO ind_sal
FROM customer
WHERE location = “UK"
sum_sal 
+= ind_sal;
END FOREACH;
RETRUN sum_sal;

       6)continue或exit语句
       都可以被使用在for、foreach和while循环语句中。
       参考实例如下:
 

FOR i = 1 TO 5
   LET j 
= i;
   
WHILE (j > 0)
      LET id 
= foo(j);
      
IF (id = 5THEN
         LET j 
= j – 1;
         
CONTINUE WHILE-- 不执行后续的操作,继续回到while循环接着执行
      END IF;
      LET 
sum = sum + 5;
      LET j 
= j – 1;
      
IF (sum > 500THEN
         
EXIT FOR-- 退出for循环
      END IF;
   
END WHILE;
END FOR;
RETURN sum;

5、异常处理
      1)ON EXCEPTION语句
      ON EXCEPTION语句语句提供了异常的捕获和处理机制。在IN中指定要捕获的错误,并指定当异常发生时需要执行的动作。在一个语句块中允许有多个ON EXCEPTION语句。
      ON EXCEPTION语句必须在DEFINE语句之后,并在在任何可执行的语句块之前。并且它在内嵌的语句块中也是有效的。它使用SET语句来接收SQL、ISAM错误码和错误信息。
      参考实例如下所示:
 

CREATE PROCEDURE ex_test()
   DEFINE sql_err 
INTEGER;
   DEFINE isam_err 
INTEGER;
   DEFINE err_txt 
CHAR(200);
   
ON EXCEPTION IN (-206SET sql_err, isam_err, err_txt
      
CREATE TABLE tab1 ( col1 INT, col2 INT);
      
INSERT INTO tab1 VALUES (12);
      
INSERT INTO tab1 VALUES (23);
   
END EXCEPTION
   
INSERT INTO tab1 VALUES (12); --如果tab1不存在时,跳到异常处理
   INSERT INTO tab1 VALUES (23);
END PROCEDURE;

       2)WITH RESUME语句
       该语句在存储过程发生错误时恢复语句。
       如下实例展示了WITH RESUME的使用,当异常处理完毕后,继续后续的语句处理。参考语句如下:
 

CREATE PROCEDURE ex_test()
ON EXCEPTION IN (-206)
     
CREATE TABLE tab1 (col1 INT, col2 INT);
     
INSERT INTO tab1 VALUES (12);
END EXCEPTION WITH RESUME;
INSERT INTO tab1 VALUES (12); -- 如果tab1不存在,跳到异常处理
INSERT INTO tab1 VALUES (23); -- 异常处理完成后,继续该句进行处理
INSERT INTO tab1 VALUES (34);
END PROCEDURE;

       3)RAISE EXCEPTION语句
       i) 该语句用来创建错误;
       ii)它能够用来指定SQL错误、ISAM错误和错误信息;
       iii)用该语句创建的错误,能够被ON EXCEPTION捕获;
       iv)可以使用指定的错误码-746来表示自定义的错误消息。
       如下实例使用ON EXCEPTION语句指明了当发生错误时将错误码、错误信息插入到自定义的错误表my_error_table,当传入的参数小于1时,抛出自定义的-746异常,其余情况成功将数据插入到tab1表中,参考语句如下:
 

CREATE PROCEDURE ex_test5 (a INT)
   DEFINE sql_err 
INTEGER;
   DEFINE isam_err 
INTEGER;
   DEFINE err_txt 
CHAR(200);
   
ON EXCEPTION IN (-746SET sql_err, isam_err, err_txt
      
INSERT INTO my_error_table values (sql_err, isam_err, err_txt);
   
END EXCEPTION;
   
IF (a < 1THEN
      RAISE EXCEPTION 
-7460, "插入值必须大于0";
   
END IF;
   
INSERT INTO tab1 VALUES (1, a);
END PROCEDURE;

6、执行SPL函数或存储过程
        可使用EXECUTE PROCEDURE语句来执行SPL存储过程,使用EXECUTE FUNCTION来执行SPL函数;
        参考实例1:
 

EXECUTE PROCEDURE foo();

        参考实例2:
 

CREATE FUNCTION func1() RETURNING INT
    DEFINE myvalue 
INT;
    CALL foo() RETURNING myvalue;
    
INSERT INTO table1 VALUES (myvalue);
    
RETURN myvalue;
END FUNCTION;

        参考实例3:
 

SELECT * FROM table2 WHERE id = get_id("amigo");

        参考实例4:
 

UPDATE table2 SET col2=foo() WHERE id=1;

7、删除SPL函数或存储过程
        1)使用DROP PROCEDURE来删除SPL存储过程;
        2)使用DROP FUNCTION来删除SPL函数;
        3)使用DROP ROUTINE来删除SPL存储过程或SPL函数。
        几个参考实例如下:
 

DROP PROCEDURE foo;
DROP PROCEDURE foo (INTEGER);
DROP SPECIFIC PROCEDURE foo_int;
DROP FUNCTION foo_ret;
DROP FUNCTION foo_ret (INTEGER);
DROP SPECIFIC FUNCTION foo_ret_int;
DROP ROUTINE foo;
DROP ROUTINE foo_ret;