Posted on 2006-12-14 14:27
Fisher 阅读(2896)
评论(0) 编辑 收藏 所属分类:
DataBase
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="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
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;