|
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
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="PS" ) 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="PS" 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="PS" 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="PS" 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 |
