Informix-SPL(过程)语法详解 (到这个网站很久了，贡献一点吧)

蓝点123

Informix数据库用户一般都熟悉的使用SQL语句进行简单的查询和统计，而在Informix-Online的数据库服务器中提供了用结构化查询语言SQL语句和流程控制存储语言SPL创建存储例程，以减少Informix的处理。存储例程是SQL语句和SPL的集合。它们存放在数据库中，SQL语句会被分析.优化,在例程的执行中,高速缓存中会有一执行规划,使后续操作的执行速度很快。而单独的SQL语句只有在执行时才会被优化，并且存储例程可以被不同的开发工具调用（4GL 、ESQL/C、POWERBUILDER.DELPHI），在SELECT语句中也调用过程。例程调试简单，不必重新编译应有软件包。在例程创建时Informix查询处理器会分析它，并产生一执行规划，永久存放于SPROCEDURES.SYSPROBODY.和SYSPROPLAN中,其后例程按此规划执行,由于大部分查询处理已经完成,存储例程可以在瞬间执行完毕。由于存储例程所具有的优越性，它已成为进行Informix数据库核心开发的有力工具，掌握它对Informix的开发人员有积极意义，现将我在实际工作中使用的语法和例子详解于下,疏漏和错误请来信指教。

1.创建和执行例程

  ①.创建例程语法：

      CRAETE PROCEDURE [OWNER.]PROCEDURE_NAME (参数1  参数类型=[DEFAULT], 参数2  参数类型=[DEFAULT],参数n  参数类型=[DEFAULT])

                    RETURNING 值1类型,值2类型,值n类型；

      <......>;语句体；      END RPOCEDURE

例程大小不可超过64K,这包括所有的SQL.SPL.空格.跳格符,例程名最多18个字符,并在数据库中唯一存在,语句间用";"分隔,例程只能在当前数据库中创建,例程创建后是一标准

执行模板块，可在不同的应有中对其调用,这对开发不同版本的应用将更为便利。例：

create procedure "test".upwage()

define rev_rev_item_code varchar(2,0);

define rev_p_rev_date date;

define acc_rec_prem_no decimal(8,2);define rev_p_rev_amt  decimal(10,2);define rev_I_info_branch varchar(6,0);define rev_I_info_appl_no decimal(8,0);define rev_I_info_date date;define rev_o_rev_date date;

define rev_o_rev_amt decimal(10,2);

define acc_ac_rev_amnt decimal(10,2);

define acc_rec_prem_date date;

begin work;

foreach cur_rev for select I_info_appl_branch,I_info_appl_no,I_info_date,

o_rev_date,o_rev_amt into rev_I_info_branch,

rev_I_info_appl_no,rev_I_info_date,rev_o_rev_date,                             rev_o_rev_amt from rev_rec_tbl where

(rev_item_code="S" ) and p_rev_date is null and

p_rev_amt=0 and (I_info_appl_no is not null or

I_info_date is not null);

select max(rec_prem_acc_no) into acc_rec_prem_no from rec_prem_acc where                                   I_info_appl_branch=rev_I_info_branch

and I_info_appl_no=rev_I_info_appl_no

and I_info_date=rev_I_info_date

and o_rev_date=rev_o_rev_date

and rev_item_code="S" and

ac_rev_amnt=rev_o_rev_amt;

select date(rec_prem_date) into acc_rec_prem_date

from rec_prem_acc where I_info_appl_branch=rev_I_info_branch

and I_info_appl_no=rev_I_info_appl_no

and I_info_date=rev_I_info_date

and o_rev_date=rev_o_rev_date

and rev_item_code="S" and

ac_rev_amnt=rev_o_rev_amt and

rec_prem_acc_no=acc_rec_prem_no ;

select ac_rev_amnt into acc_ac_rev_amnt

  from rec_prem_acc

where I_info_appl_branch=rev_I_info_branch

  and I_info_appl_no=rev_I_info_appl_no

  and I_info_date=rev_I_info_date

  and o_rev_date=rev_o_rev_date

  and rev_item_code="S"

  and rec_prem_acc_no=acc_rec_prem_no

  and ac_rev_amnt=rev_o_rev_amt;

if acc_ac_rev_amnt is null or acc_rec_prem_date is null then

   continue foreach;

end if;

update rev_rec_tbl

   set p_rev_date=acc_rec_prem_date,

       p_rev_amt =acc_ac_rev_amnt

where I_info_appl_branch=rev_I_info_branch

   and I_info_date=rev_I_info_date

   and I_info_appl_no=rev_I_info_appl_no

   and o_rev_date=rev_o_rev_date;

end foreach;

commit work;

end procedure;

  ②.执行例程语法：

    A):在dbaccess中

       EXECUTE PROCEDURE DBNAME@SERVER_NAME:例程名(参数1,参数2,.....)用这种方法可对例程进行调试。

    B):在Informix-4GL中

       PREPARE PREP STATTEMENT FROM "EXECUTE PROCEDURE DBNAME@SERVER_NAME:例程名（?,?,?,...）

       DECLARE P_CURS SURSOR FOR PREP STMT

       OPEN P_CURS USING 参数1,参数2,....

       FETCH P_CURS INTO 返回值1，返回值2，...

       CLOSE P_CURS

       当应用程序不支持EXECUT PROCEDURE语法,则需使用PREPARE命令,如INFORMIX-4GL中,而使用INFORMIX-NEWEAR则无此限制,需注意的是PREPARE语句中变量用?号代替,其个数要与例程的参数个数和类型一致,返回值也是一样。

    C):在Informix-ESQL/C中

       EXEC SQL EXECUTE PROCEDURE 例程名(参数1,参数2,...) INTO （返回值1，返回值2,...）在EC5.0或更高版本可使用EXECUTE PROCEDURE 语法,在ESQL/C中宿主变量用于想存储例程传递值,同时也接收返回值.

    D):在POWER BUILDER中

       DECLARE 逻辑名 PROCEDURE FOR 例程名(:参数1,:参数2:...) INTO :返回值1,:返回值2,..USING 事物名

       EXEC 例程名(:参数1,:参数2,.....)

       PB要求为例程制定逻辑名,以后的SQL语句将以逻辑名为准指向后台数据库例程,当例程即便没有参数也必须有小扩号.

2.流程控制语言：

在过程中也提供了其他语言具备的流程控制语言,完成循环判断和分类处理的能力,主要有:①.IF ....ELIF.....ELSE.....END IF例：

CRAETE PROCEDURE STR_COM(STR1 CHAR(20),STR2 CHAR(20))

RETURNING INT;

DEFINE REL INT;

IF STR1>;STR2 THEN  --当STR1>;STR2 REL=1

LET REL=1;

ELIF STR2>;STR1 THEN --当STR2>;STR1 REL=-1

LET REL=-1;

ELSE

LET REL=0;   --当STR1=STR2 REL=0

END IF

RETUEN REL;

END PROCEDURE

当IF的条件为一个SQL语句如SELECT时需用扩号，并且返回值为单值。

②.FOR .....END FOR

例:FOR INDEX IN (20 TO 30 STEP 2,100 TO 200 STEP 10)

   --执行代码

   END FOR

FOR的条件可以是变量，常量或一个SQL语句的返回值

③.WHILE.......END WHILE

当WHILE的条件为TRUE时执行WHILE后的语句,为FALSE退出循环。

例:WHILE I<10

    INSERT INTO TBB_1 VALUES(I);

    LET I=I+1;

    END WHILE;

④.FOREACH........END FOREACH

该语句较为特别FOREACH循环能够声明并打开游标,读取记录行,并关闭游标.其完整语法:

FOREACH 游标名 [WITH HOLD] SELECT 字段名 INTO 变量 FROM

   TABLE WHERE 条件 ；

END FOREACH

FOREACH EXECUTE PROCEDURE 例程名（参数1，参数2，..） INTO

  变量

END FOREACH

该循环中语句的执行次数与SELECT 和 EXECUTE PROCEDURE语句返回的行数一样多。如果FOREACH语句中包含一条EXECUTE PROCEDURE，则循环停止的条件为：

.执行了不带任何参数的RETURN语句

.执行了END PROCEDURE

如果没有返回行数据,则不再执行循环中的语句。存储例程中不允许使用滚动游标。

当使用WITH HOLD时，更新游标将放置更新锁，使其他过程无法更新该行，直至事物完成，当在FOREACH循环的语句块中的UPDATE或DELETE有

WHERE CURRENT OF短语，则存储例程会自动使用更新游标

例1：BEGIN WORK；

     FOREACH CUR_1 FOR SELECT DATE INTO V_DATE FROM TABLE

     IF V_DATE IS NULL THEN

     DELETE FROM TABLE WHERE CURRENT OF CUR_1;

     END IF;

     END FOREACH;

     COMMIT WORK;

例2：FOREACH EXECUTE PROCEDURE BAR(10,20) INTO I

     INSERT INTO TABLE1 VALUES(I)

     END PROCEDURE

⑤.CONTINUE    适用语句( FOR WHILE FOREACH)   将执行传递给下一次循环⑥.EXIT

   适用语句( FOR WHILE FOREACH)

   从循环中退出

   例：FOR J=1 TO 20

       IF J>;10 THEN

          CONTINUE FOR;

       END IF

       LET I,S=J,0;

         WHILE I>;0

             LET I=I-1;

             IF I=5 THEN

               EXIT FOR;

             END IF

         END WHILE

       END FOR

3.变量的定义与赋值:

   存储例程中使用的变量必须在例程开始处用DEFINE语句定义，变量的数据类型为除SERIAL以外的任意类型，如果定义一个TEXT或BYTE类型的变量，则该变量为指向数据的指针。传递给程序的变量必须在CRAETE PROCEDURE语句中定义。DEFINE也可使用LIKE语句。变量类型缺省为局部变量，也可引用GLOBAL定义全局变量，全局变量在例程间保持它的值，直至会话结束。用户必须为每一个定义的全局变量赋缺省值，缺省值仅在例程第一次引用该全局变量时使用，以后的例程将会忽略其缺省值。

  例：CREATE PROCEDURE SP1（）

        RETURNING INT；

        DEFINE GLOBAL I INT DEFAULT 1；

        LET I=I+1；

        RETURN I；

      END PROCEDURE

      CRAETE PROCEDURE SP2 （）

        RETURNING INT；

        DEFINE GLOBAL I INT DEFAULT 4；

        LET I=I+1；

        RETURN I;

      END PROCEDURE

     当执行顺序为SP1，SP2 返回值3，当执行顺序为SP2，SP1返回值为6。

     例程也可被声名为变量

  例：DEFINE LEN PROCEDURE

      LET X=LEN（A，B，C）

    变量赋值必须用LET关键字，如果不给变量赋值，变量会有一个不确定值，任何对该变量的使用都会产生错误。

      BEGIN......END

利用BEGIN....END可以封装语句,它允许用户完成以下功能:

     .定义仅用于该语句块的变量

     .在语句块内以不同方式处理异常情况.

     .在某语句块中定义的变量,在该语句块以及它所包含的语句块都有效,除非又将变量定义一次。

      例 : CREATE PROCEDURE SP （）

           RETURNING INT；

           DEFINE V1 INT；

           LET V1=1；

           BEGIN

             DEFINE V1 INT；

             LET V1=2；

           END

          RETURN V1；

        END PROCEDURE

   返回值为1。

4.在例程中执行其他例程和系统命令:

   应用CALL命令可在例程中调用其他的例程。

   例：CREATE PROCEDURE SP（）     

       DEFINE I，J，K，L INT；

         CALL SP1（10，20） RETURNING I，J，K；

       END PROCEDURE

   用SYSTEM可在例程中调用系统命令。 通过SYSTEM命令，用户可以执行操作系统命令。系统命令放在括号内，用双管道号（||），还可以为SYSTEM命令串连起多个表达式。但在存储例程中不能使用该命令的返回值，如果系统调用失败（返回非零值），返回值将和SQL错误代码一起，放在ISAM代码中。

    例：SYSTEM "/usr/exec/exec.sh"

    用RETURN命令可以将例程运行的结果返回给调用它的应用。当需要多次调用同一例程可执行RETURN WITH RESUME命令，它可以保证下一次调用该例程时，所有变量保持原值，而且从RETURN WITH RESUME后的第一条语句执行。

    例：RETURN V_INT WITH RESUME;

5.调试跟踪语句:

   我们在做应用的时候，当程序完成后都需要进行数据测试，以便验证程序逻辑的严密性，在例程中，INFORMIX也提供了调试跟踪语句，其主要有（TRACE，ON EXCEPTION，RAISE EXCEPTION）。TRACE 语句可以跟踪语句块中每一个活动语句的结果和过程，并且可用SET DEBUG FILE TO FILENAME 语法写入指定文件中。

    ON EXECEPTION可在例程中设定断点，向一个DEBUGLOG或ERRLOG文件写入错误信息。完整语法为:

    ON EXCEPTION IN （error_number）

       set sql错误代码变量，isam错误代码变量，错误信息变量

       处理语句

    END EXCEPTION WITH RESUME

    注意SET后的3个变量必须在DEFINE中定义，其中sql错误代码．Isam错误代码变量类型为INT,错误信息变量为足够长的CHAR变量。Error_number是一个SQL错误代码或用RAISE EXCEPTION设置的陷阱代码。WITH RESUME为可选项,当用WITH RESUME语句时,INFORMIX会执行完ON EXCEPTION语句块的命令后会返回到出错命令行的下一行接着执行,或RAISE EXCEPTION的下一行执行。

    RAISE EXCEPTION人为设定SQL错误代码，ISAM错误代码，错误信息。

    RAISE EXCEPTION与ON EXCEPTION语法连用很容易跟踪例程的错误代码

例：CREATE PROCEDURE TMP_PROCEDURE()

       DEFINE SQLCODE INT;

       DEFINE ISAMCODE INT;

       DEFINE ERR_TXT CHAR(255);

      ON EXCEPTION SET SQLCODE,ISAMCODE,ERR_TXT

        IF SQLCODE= -284 THEN

           RASIE EXCEPTION SQLCODE,ISAMCODE,"在TMP_PROCEDURE中查询返回多条记录";

        END IF

        IF SQLCODE= -1218 THEN

           RASIE EXCEPTION SQLCODE,ISAMCODE,"在TMP_RPOCEDURE中类型转换错误“；

       END IF

     END EXCEPTION

      SELECT .......

END PROCEDURE

  

６.过程实例:

CREATE PROCEDURE YEARS(E_DATE DATE,B_DATE DATE)

RETURNING INT;返回E_DATE与B_DATE之间的整年数(E_DATE大于B_DATE)

    DEFINE V_E  INT;

    DEFINE V_B  INT;

    LET V_E = MONTH(E_DATE);

    LET V_B = MONTH(B_DATE);

    IF V_E < V_B THEN

       LET V_E = YEAR(E_DATE)-YEAR(B_DATE)-1;

       RETURN V_E;

    ELIF V_E >; V_B THEN

       LET V_E = YEAR(E_DATE)-YEAR(B_DATE);

       RETURN V_E;

    END IF;

    LET V_E = DAY(E_DATE);

    LET V_B = DAY(B_DATE);

    IF V_E < V_B THEN

       LET V_E = YEAR(E_DATE)-YEAR(B_DATE)-1;

       RETURN V_E;

    ELSE

       LET V_E = YEAR(E_DATE)-YEAR(B_DATE);

       RETURN V_E;

    END IF;

END PROCEDURE;

大梦

是原创，还是转贴？
是转贴请注明原作者！

大梦

你干脆把中国人寿的全贴出来！

蓝点123

对于想学过程的同志会有帮助的！

蓝点123

对于想学过程的同志会有帮助的！

蓝点123

对于想学过程的同志会有帮助的！

蓝点123

文中并没有署名，我只是觉得对大家会有用才发的。难道是你大梦的高做？

sgool

不错，up

u8

是不是?

大梦

UP就UP