1、簡介
        存儲過程（Stored Procedure）是一組為了完成特定功能的SQL語句集，經編譯后存儲在數(shù)據(jù)庫中。用戶通過指定存儲過程的名字并給出參數(shù)（如果該存儲過程帶有參數(shù)）來執(zhí)行它。存儲過程是數(shù)據(jù)庫中的一個重要對象，任何一個設計良好的數(shù)據(jù)庫應用程序都應該用到存儲過程。
        存儲過程是由流控制和SQL語句書寫的過程，這個過程經編譯和優(yōu)化后存儲在數(shù)據(jù)庫服務器中，應用程序使用時只要調用即可。
2、重要概念和疑問解答
        1）何謂informix中的SPL？
        SPL（Stored Procedure Language）是informix中提供的一種提供流程控制（分支和循環(huán)）的SQL。它包括SPL過程（沒有返回值）和SPL函數(shù)（有返回值）。SPL將在創(chuàng)建的時候被解析和優(yōu)化，它以可執(zhí)行的方式保存在system catalog表中。
        2）使用SPL為什么會改善性能？
        i）因為它運行在informix引擎中，所以減少了I/O；
        ii）降低了應用的復雜性；
        iii）對頻率較高的重復操作使用更好。
        3）SPL的靈活性表現(xiàn)在哪些部分？
        i）它可在其它SQL語法中被調用；
        ii）它也可以在觸發(fā)器中使用；
        iii）可在SQL中添加流程控制；
        iv）很容易維護。
       4）SPL的參數(shù)
       i）可以傳遞很多個參數(shù)；
       ii）SPL的參數(shù)的最大限制是32K；
       iii）除了如下兩種外可以是任何一種SQL數(shù)據(jù)類型：
             Serial/Serial8
             Text/Byte (能夠使用REFERENCES關鍵字傳遞)。
       iv）能夠使用很復雜或者用戶自定義的數(shù)據(jù)類型；
       v）可以通過default為參數(shù)指定默認值。
       5）SPL函數(shù)的返回值
       i）可以使用RETURNING或RETURNS關鍵字定義返回值的返回類型；
       ii）除了如下兩種數(shù)據(jù)類型外可以為任何數(shù)據(jù)類型：
            Serial/Serial8
            Text/Byte (可以使用REFERENCES關鍵字傳遞)。
       iii）在過程體內必須至少有一個return語句；
       iv）可以為返回值取一個名稱。例如：

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

      6）SPL的重載
      可定義多個具有相同名字的SPL函數(shù)；例如如下SPL函數(shù)雖然名稱相同，但是參數(shù)類型或各不相同：

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函數(shù)的簡短的、獨一無二的被重載的SPL的函數(shù)的名稱。這個名字在數(shù)據(jù)庫中是唯一的。例如：

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）變量在內存中，而不是在數(shù)據(jù)庫中保存；
      iii）有本地變量和全局變量兩種；
      iv）變量可以是除了如下兩種類型之外的任何一種SQL數(shù)據(jù)類型和擴展類型：
             Serial/Serial8
             Text/Byte (可以使用REFERENCES關鍵字來聲明)。
      定義變量舉例如下：

DEFINE x, y INT; -- 內建的數(shù)據(jù)類型
DEFINE p person_type; -- 用戶自定義的數(shù)據(jù)類型person_type
DEFINE mymusic REFERENCES BYTE; -- 使用REFERENCES關鍵字定義BYTE類型

      9）本地變量和全局變量比較
      本地變量：
      i）本地變量值在SPL中有效；
      ii）在SPL函數(shù)或過程結束后就被復位；
      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）在相同數(shù)據(jù)庫中使用相同的用戶會話的地方都能得到全局變量的值；
     ii）必須有一個默認值；
     iii）必須在每一個要使用它的SPL函數(shù)或過程中定義；
     iv）不能是一個集合變量。
     例如如下定義了兩個SPL函數(shù)，名稱分別為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;

     如果執(zhí)行兩者的順序如下：

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

     則執(zhí)行完第一句后，gvar被設置了默認值2，且執(zhí)行了加1的操作，所以第一句執(zhí)行完畢后gvar的值為3.
     接著執(zhí)行第二句，第這次不在設置gvar的默認值，因此在3的基礎上再執(zhí)行了加1操作，執(zhí)行完畢后gvar的值為4.
     若執(zhí)行兩者的順序如下：

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

     則執(zhí)行完第一句后，gvar被設置了默認值5，且執(zhí)行了加1的操作，所以第一句執(zhí)行完畢后gvar的值為6.
     接著執(zhí)行第二句，第這次不在設置gvar的默認值，因此在6的基礎上再執(zhí)行了加1操作，執(zhí)行完畢后gvar的值為7.
     10）給變量賦值
     i）使用一個未定義的變量將會報錯；
     ii）給已定義的變量賦值的方法：
           * 使用LET語句直接給變量賦初值，參考語句如下：

      LET <變量> = <有效的表達式或函數(shù)名>;

          *使用SELET INTO語句將查詢到的結果給變量賦值，參考語句如下：

      SELECT … INTO <變量> FROM …;

          *使用CALL...RETURNING語句將返回結果賦給變量，參考語句如下：

       CALL … RETURNING <變量>;

          *使用EXECUTE FUNCTION INTO語句，將返回結果賦給變量，參考語句如下：

       EXECUTE FUNCTION … INTO <變量>;

3、語法
       1）創(chuàng)建SPL過程

CREATE PROCEDURE name (parameter list) SPECIFIC specific_name
… {語句塊}
END PROCEDURE;

      例如，在下面的實例中創(chuàng)建了一個名為set_status的存儲過程，可傳入myid和mystatus兩個參數(shù)，在這個存儲過程中，對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）創(chuàng)建SPL函數(shù)

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

      例如，創(chuàng)建一個名為val_comp的SPL函數(shù)，可傳入val1和val2兩個參數(shù)，在該函數(shù)中，比較這兩個變量，如果兩者相等，返回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;

 4、SPL中的流程控制
     1）分支語句if...then...else...end if
     參考實例：

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

     2）分支語句case
     參考實例：

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

     3）循環(huán)語句for
     參考語法如下：

FOR 變量 IN ( 范圍或值 )

     或者使用如下語法：

FOR 變量=范圍

     舉例如下：

FOR count = 2 TO 30
FOR count = 2 TO 30 STEP 2
FOR count IN ( 2, 5, 7, 9)
FOR count IN ( 2 to 8 STEP 2, 12 to 19, 22)
FOR name IN ("AMY", "MAX",
(SELECT name FROM customer WHERE customer_num = 100) )

     4）循環(huán)語句while
     參考語法如下所示：

WHILE (條件表達式)
   執(zhí)行的語句
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）循環(huán)語句foreach
      i）被用來取多行數(shù)據(jù)；
      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循環(huán)語句中。
       參考實例如下：

FOR i = 1 TO 5
   LET j = i;
   WHILE (j > 0)
      LET id = foo(j);
      IF (id = 5) THEN
         LET j = j – 1;
         CONTINUE WHILE; -- 不執(zhí)行后續(xù)的操作，繼續(xù)回到while循環(huán)接著執(zhí)行
      END IF;
      LET sum = sum + 5;
      LET j = j – 1;
      IF (sum > 500) THEN
         EXIT FOR; -- 退出for循環(huán)
      END IF;
   END WHILE;
END FOR;
RETURN sum;

5、異常處理
      1）ON EXCEPTION語句
      ON EXCEPTION語句語句提供了異常的捕獲和處理機制。在IN中指定要捕獲的錯誤，并指定當異常發(fā)生時需要執(zhí)行的動作。在一個語句塊中允許有多個ON EXCEPTION語句。
      ON EXCEPTION語句必須在DEFINE語句之后，并在在任何可執(zhí)行的語句塊之前。并且它在內嵌的語句塊中也是有效的。它使用SET語句來接收SQL、ISAM錯誤碼和錯誤信息。
      參考實例如下所示：

CREATE PROCEDURE ex_test()
   DEFINE sql_err INTEGER;
   DEFINE isam_err INTEGER;
   DEFINE err_txt CHAR(200);
   ON EXCEPTION IN (-206) SET sql_err, isam_err, err_txt
      CREATE TABLE tab1 ( col1 INT, col2 INT);
      INSERT INTO tab1 VALUES (1, 2);
      INSERT INTO tab1 VALUES (2, 3);
   END EXCEPTION
   INSERT INTO tab1 VALUES (1, 2); --如果tab1不存在時，跳到異常處理
   INSERT INTO tab1 VALUES (2, 3);
END PROCEDURE;

       2）WITH RESUME語句
       該語句在存儲過程發(fā)生錯誤時恢復語句。
       如下實例展示了WITH RESUME的使用，當異常處理完畢后，繼續(xù)后續(xù)的語句處理。參考語句如下：

CREATE PROCEDURE ex_test()
ON EXCEPTION IN (-206)
      CREATE TABLE tab1 (col1 INT, col2 INT);
      INSERT INTO tab1 VALUES (1, 2);
END EXCEPTION WITH RESUME;
INSERT INTO tab1 VALUES (1, 2); -- 如果tab1不存在，跳到異常處理
INSERT INTO tab1 VALUES (2, 3); -- 異常處理完成后，繼續(xù)該句進行處理
INSERT INTO tab1 VALUES (3, 4);
END PROCEDURE;

       3）RAISE EXCEPTION語句
       i） 該語句用來創(chuàng)建錯誤；
       ii）它能夠用來指定SQL錯誤、ISAM錯誤和錯誤信息；
       iii）用該語句創(chuàng)建的錯誤，能夠被ON EXCEPTION捕獲；
       iv）可以使用指定的錯誤碼-746來表示自定義的錯誤消息。
       如下實例使用ON EXCEPTION語句指明了當發(fā)生錯誤時將錯誤碼、錯誤信息插入到自定義的錯誤表my_error_table，當傳入的參數(shù)小于1時，拋出自定義的-746異常，其余情況成功將數(shù)據(jù)插入到tab1表中，參考語句如下：

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

6、執(zhí)行SPL函數(shù)或存儲過程
        可使用EXECUTE PROCEDURE語句來執(zhí)行SPL存儲過程，使用EXECUTE FUNCTION來執(zhí)行SPL函數(shù)；
        參考實例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函數(shù)或存儲過程
        1）使用DROP PROCEDURE來刪除SPL存儲過程；
        2）使用DROP FUNCTION來刪除SPL函數(shù)；
        3）使用DROP ROUTINE來刪除SPL存儲過程或SPL函數(shù)。
        幾個參考實例如下：

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;