INFORMIX-4GL实用手册

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=\

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=\

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=\

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 \例程名（?,?,?,...）

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