INFORMIX的存储过程

1、INFORMIX的存储过程存贮过程是已命名的、预编译的、典型的SQL集。数据库存贮过程使程序员能够在数据库中定义所有应用程序都可以使用的过程化SQL语句。用存贮过程来完成数据库的修改使程序员能够增强数据库的安全性、完整性并提高性能。由于存贮过程提供了条件执行，因此也可以用来执行附加商务规则。存贮过程是SQL语句和SPL的集合。它们存放在数据库中，而SQL则被分析、优化，并产生一执行规划，永久地存放在当前库（非SYSMASTER库）的SYSPROCEDURES（过程名）、SYSPROCBODY（代码）、SYSPROCPLAN(编译后的规划)系统表中。当存贮过程被执行时，直接从库中调用优化后的代码

2、进行运行，因此大大提高了SQL的性能、效率，极大增强了SQL语句和批处理的性能。1、编辑存贮过程：可用通用的文本编辑器对存贮过程进行编辑，然后将文件放到主机上。注意，如果采用在DBACCESS中以批处理方式编译存贮过程，则存贮过程的源文件名的后缀必须以sql结尾，文件存放的目录必须在启动DBACCESS的目录下。2、创建（编译）存贮过程：产生一执行规划（编译后的代码），永久存放在数据库系统表中。存贮过程由SQL语句CREATE PROCEDURE “过程名”创建。当该语句被执行时（如在DBACCESS中按批处理运行），包含在CREATE PROCEDURE和END PROCEDURE之间的所有

3、代码首先进行语法检查，然后被解析、优化，以过程名为标识符，存放在数据库中，等待执行。可以用WITH LISTING IN PATHNAME关键字把编译的警告信息存放到指定的文件中。3、执行存贮过程：调用执行规划。用SQL语句EXECUTE PROCEDURE或SPL语句CALL/LET按存贮过程名进行调用，则库中编译好的执行规划将被调出直接运行，这样可得到较高的运行效率。 可把存贮过程当作一个函数，直接在 SELECT 语句中调用，如：select char_ascii(a) as value from dw;SELECT customer_num, lname, get_orders(cus

4、tomer_num) n_orders FROM customer;类似于表、视图、用户等，存贮过程也是一个数据库对象，其名称必须符合标识符命名规则(18字符长，以字符或_开始)，也只能在当前数据库中创建，并赋予一定的权限。SYSPRC表的内容：prc_hashno 24prc_chainno 0prc_id 1505prc_refcnt 0prc_delete 0prc_heapptr 388571168prc_heapsz 1prc_servername oncq97dataprc_dbname dev_dataprc_ownername developprc_name wd_proc1当

5、修改wd_proc1，再次创建后，SYSPRC表中查不到此存贮过程的记录；当执行存贮过程后，SYSPRC表中又出现该记录；？二、存贮过程的总体结构存贮过程的输入、输出参数都必须显式给出；- * 创建 TS 表 INSERT 触发器的存贮过程 */- 功能：根据TS表中处理单位的信息，在CL表中向相关单位- 生成一条或多条处理记录（每个单位一条记录，最多三个单位）；- 传入参数：dw, id, status, signDROP PROCEDURE ti_ts_proc;CREATE PROCEDURE ti_ts_proc ( id char(12), dw char(9), status ch

6、ar(1),sign char(1) ) DEFINE dd date ; DEFINE first_dw char(3); DEFINE second_dw char(3); DEFINE sql_err,isam_err INT; DEFINE error char(80); - 出错处理 - ON EXCEPTION SET sql_err,isam_err,error TRACE SQL err no: |sql_err; TRACE ISAM err no: |isam_err; TRACE error message: |error; END EXCEPTION SET DEBUG

7、 FILE TO /export/home/app180/debug_wd; TRACE begin trace:|current; - LET dd = TODAY ; TRACE DD=|dd; IF status 2 or sign=1 THEN RETURN ; END IF ; TRACE length of dw is |length(dw); TRACE Length(trim(dw)=|length(trim(dw); TRACE ON; LET dw=TRIM(dw); IF length(dw) 1 THEN BEGIN IF length(trim(dw) = 3 THE

8、N insert into cl(id,dw,clsign) values(id,dw,1); ELIF length(trim(dw) = 6 THEN BEGIN LET first_dw = substring(dw FROM 1 FOR 3) ; LET second_dw = substring(dw FROM 4 FOR 3); - 变量，起始位，位长; insert into cl(id,dw,clsign) values(id,first_dw,1); insert into cl(id,dw,clsign) values(id,second_dw,1); end ELIF l

9、ength(trim(dw) = 9 THEN begin LET first_dw = dw1,3; - substring(dw FROM 1 FOR 3) ; LET second_dw = substring(dw from 4 for 3); insert into cl(id,dw,clsign) values(id,first_dw,1); insert into cl(id,dw,clsign) values(id,second_dw,1); LET first_dw = substring(dw from 7 for 3); insert into cl(id,dw,clsi

10、gn) values(id,second_dw,1); end END IF end END IF TRACE OFF;END PROCEDURE WITH LISTING IN /export/home/app180/wd_listing;- 创建TS表 INFORMIX TI-TS TRIGGER- 功能：把当前记录的值传递给存贮过程处理 DROP TRIGGER ti_ts; CREATE TRIGGER ti_ts INSERT ON ts REFERENCING NEW AS post FOR EACH ROW (EXECUTE PROCEDURE ti_ts_proc(post.i

11、d,post.dw,post.status,post.sign) );三、存贮过程的语法完整的语句：存贮过程中（SQL或SPL语句）没有专门的续行符，一条完整的语句可放在连续（甚至不连续）的多行中，最后以分号结束。对于SPL语句要特别注意语句完整性的概念。如ON EXCEPTION .END EXCEPTION；这些成对出现的语句，从表面上看它们是分开的，但实际上是一条完整的语句，因此只能有一个分号。类似情形还有IF.ELIF.ELSE ENDIF；、WHILE.END WHILE；等。存贮过程的流程控制语句条件转移：IF 条件 THENELIF 条件 THENELSEENDIF；语句块：BE

12、NIN与END封装了一系列语句，并指明在流程控制结构如IFELSE中作为整体来处理。可以定义仅用于该语句块的变量； BEGIN 语句块 END 变量：通过DEFINE关键字声明、命名，并由SELECT命令或LET语句赋值。变量的初值为NULL。变量名可与数据字段名重复，INFORMIX存贮过程中无显式游标操作：DECLARE CURSOR 游标名FOR 条件：定义游标；游标的类型有：SELECT、INSERT、UPDATE、DELETE；？OPEN 游标名：按定义的条件打开游标工作区；FETCH游标名INTO 变量：记录指针移向下一行，并将新行的数据赋给变量；适用于SELECT类游标；PUT

13、游标名 WITH ：工作区中加入新记录，适用于INSERT类游标；CLOSE游标名：关闭工作区，提交数据； 隐式游标操作：FOREACH cursor_name For SELECT . INTO . FROM . WHERE . 语句块END FOREACH；当要对记录集中的数据进行更改处理时，FOREACH后必须要加上游标名，然后使用UPDATE WHERE CURRENT OF cursor_name，则更改游标当前记录的值。 事务处理：BEGIN WORK；COMMIT WORK；ROLLBACK WORK；循环语句：WHILE 条件END WHILE；FOR 变量=初值 TO 终值

14、STEP 步长 | 变量IN （ ， ， ）END FOR；EXIT FOR/WHILE/FOREACH：退出最近的一层循环，并使程序从循环外的下一语句继续运行；CONTINUE FOR/WHILE/FOREACH：跳出当前循环的的剩余部分，从下一循环的开始处继续执行；定义时间类型，必须给出范围，如：DEFINE beg_time DATETIME year to second;调用远程表（在另外一个库中）：“库名：表名” if not exists(select * from sysmaster:systabnames where tabname=getpwd) then trace 视图g

15、etpwd不存在，应用程序不能正常运行！; return; end if;四、存贮过程的的异常处理和跟踪调试存贮过程在运行中可能产生两类出错信息，一是由数据库服务器返回的语句异常，如执行删除操作不成功时要报错；另外一种是用户在存贮过程中根据业务需求自定义的出错信息。INFORMIX通过使用ON EXCEPTION语句来捕获存贮过程在运行中所产生的各种异常，并根据用户所编写的错误处理代码执行相应的操作。具体来说，当出现错误时在SQL通讯区（SQLCA）将产生一个对应的错误代号，SPL解释程序从当前出错行由后向前查找ON EXCEPTION说明（如有多个ON EXCEPTION的嵌套情况），直至捕

16、获到与此对应的错误号。捕获错误后，首先复位错误号(经实际测试：如果未捕获到对应的错误号，则中断本过程运行，将错误号带到上级程序进行处理)，然后执行该错误号下的错误处理代码。当代码执行完毕并且END EXCEPTION 使用WITH RESUME关键字，自动重新执行出错语句之后的语句；如果没有WITH RESUME，则退出当前的存贮过程；EXCEPTION后的程序执行点：l 如果ON EXCEPTION语句在一个BEGINEND块中，则错误处理完后，程序跳到END后的下一语句开始执行；l 如果ON EXCEPTION语句在一个循环中，如 FOR, WHILE, FOREACH，则错误处理完后，程

17、序直接从下一个循环开始执行；l If no statement or block, but only the procedure, contains the ON EXCEPTION statement, the procedure executes a RETURN statement with no arguments to terminate. That is, the procedure returns a successful status and no values.- 测试EXCEPTION的嵌套 -create procedure wd_test_multi_exce() re

18、turning varchar(100);define tmp varchar(100);begin on exception return tmp| return outside.; end exception; begin on exception let tmp = raise inside. ; end exception; raise exception -9997; end; if tmp is null then let tmp = raise outside.; end if; raise exception -9997;end;end procedure;运行结果：(expr

19、ession) raise inside. return outside.Continuing Execution After an Exception OccursIf you do not include the WITH RESUME keyword in your ON EXCEPTIONstatement, the next statement that executes after an exception occurs dependson the placement of the ON EXCEPTION statement, as the following scenarios

20、describe:n If the ON EXCEPTION statement is inside a statement block with aBEGIN and an END keyword, execution resumes with the firststatement (if any) after that BEGIN.END block. That is, it resumesafter the scope of the ON EXCEPTION statement.n If the ON EXCEPTION statement is inside a loop (FOR,

21、WHILE,FOREACH), the rest of the loop is skipped, and execution resumeswith the next iteration of the loop.n If no statement or block, but only the procedure, contains the ONEXCEPTION statement, the procedure executes a RETURN statementwith no arguments to terminate. That is, the procedure returns as

22、uccessful status and no values.Errors Within the ON EXCEPTION Statement BlockSQLCA的数据结构：不管SQL语句执行情况如何，它总能返回一个结果代码，它反映数据库操作的执行情况以及对数据库的影响。返回的结果在结构SQLCA（SQL Communications Area）中，SQLCODE，SQLERRD，SQLWARN，SQLERRM；使用DBINFO函数，判断查询是否成功，更改的记录数；dbinfo(sqlerrd1), dbinfo(sqlerrd2)出错处理的代码包含在ON EXCEPTION.END EXC

23、EPTION之中；ON EXCEPTION IN （-206，-300）SET SQL-ERR，ISAM-ERR，ERROR-DATASQL-ERR：每条SQL语句执行后数据库均要返回一个系统号来说明执行的情况，=pre_upd.quantity*1.3) (EXECUTE PROCEDURE calc_totpr (pre_upd.quantity, Post_upd.quantity, pre_upd.total ) INTO total_price );六、错误调试1、字符变量必须赋给非空的初值，否则参与所有运算后其值都是NULL； let index,str1=1,null; -应将S

24、TR1的值改为； while index=length(new_pass) let v_pass=(char_ascii(substr(new_pass,index,1)-ms_sum); let str1=str1|v_pass; trace str1=|str1; -其值始终为NULL； let index=index+1; end while;-drop procedure sp_wd1create procedure sp_wd1()returning varchar(100);define tmp_return varchar(100) ; let tmp_return = null

25、; IF nvl (SUBSTR(tmp_return, 1, 1), 0) = 0 THEN RETURN nvl(SUBSTR(tmp_return,1,1),0) = 0: true; else RETURN nvl(SUBSTR(tmp_return,1,1),0) = 0: false; END IF; end procedure;运行结果：(expression) nvl(SUBSTR(tmp_return,1,1),0) = 0: true当 IF 判断条件中有NULL值时，因运算结果为NULL，故判断出的结果始终不为1，即是FALSE。如： if tmp_return0 the

26、n . else . （不论判断条件是等于或是不等于0， 程序都进入FALSE分支。） end if ;2、对视图的增、删、改操作（with check option）对视图增、删、改的操作结果与视图的定义有一定的关系；3、查看一张表的权限情况select * from sysmaster:systabauth where tabid= (select tabid from systables where tabname=ts );查询结果：grantor grantee tabid tabauthinformix public 118 s-只有SELECT权限；4、并发操作时的锁机制shar

27、ed lock(共享锁)：在共享锁有效期间不允许被修改；一个数据对象上（表、字段等）可加载多个共享锁。exclusive lock(排它锁)：promotable lock()：如何判断操作是否成功？5、事务处理存贮过程中每COMMIT 或 ROLLBACK一次，则所有打开的游标都被关闭。6、没有ON EXCEPTION语句，出错怎么办？如果存贮过程中无ON EXCEPTION语句，当出现异常时，直接退出存贮过程。在父过程中有ON EXCEPTION语句而子过程中没有，当子过程中有异常出现时，子过程中断运行，错误号被父过程的ON EXCEPTION语句捕获，由有父过程错误处理语句进行处理。-

28、子过程 -drop procedure wd_test_multi_line;create procedure wd_test_multi_line() returning varchar(100); define name like customer.fname; select fname into name from customer; - 无WHERE子句，有多个返回值，出错284号，存贮过程由此返回，剩下的语句不执行。 trace name|name; let name=wang dong; return name;end procedure;- 父过程 -drop procedure

29、 wd_test_no_excep;create procedure wd_test_no_excep() returning varchar(100); define ch1 varchar(100); define i,j int; on exception set i,j,ch1 trace i=|i| j=|j| ch1=|ch1; return running failed!; end exception; set debug file to debug_wd; trace on; let ch1=today; let ch1=wd_test_multi_line(); -调用无ON

30、 EXCEPTION的子存贮过程。 trace off; return ch1;end procedure;-调试文档：trace onlet ch1 = 01/07/1997 execute procedure cq97.wd_test_multi_line ()exception : looking for handlerSQL error = -284 ISAM error = 0 error string = = ； - 在子过程中的出错信息exception : no appropriate handlerexception : looking for handlerSQL erro

31、r = -284 ISAM error = 0 error string = = exception : handler FOUNDexpression:(| (| (| (| (| i=, i), j=), j), ch1=), ch1)evaluates to i=-284 j=0 ch1= trace expression :i=-284 j=0 ch1= ；- 在父过程中的错误处理procedure wd_test_no_excep returns running failed! iteration of cursory procedure wd_test_no_excep7、5月11

32、日，同广州电信讨论INFORMIX存贮过程的编写，需确认以下问题：1） 把存贮过程作为参数进行传递；（可以）2） 函数作为参数进行传递；（可以）3） 参数的数据类型用字段类型；（可以）4） 定义常量、宏；？5） 提取序列号，SWQUENCE转SERIAL；（可以）在执行了INSERT语句后，函数DBINFO（SQLCA.SQLERRD1）返回新增行的序列号字段值。6） 数据类型的转换；？7） 规范文档；？8） 在子过程中可直接使用父过程所定义的DEBUG FILE文件；（可以）9） 修改子过程后，不必重新编译父过程；？（不可以）由于重新编译存贮过程前必须先将其DROP掉，因而重新编译后即使存贮

33、过程的名称不变，但在数据库中的ID号已变化；因此父过程在运行时将找不到重新编译后的子过程，INFORMIX报721号错误。解决方法是由下向上重新编译所有调用了该过程的上级存贮过程。奇怪现象：不重新编译，第一次执行父过程，运行出错；再执行一次，可正常运行。10） RETURNING参数不可省略；？11） 未查询到记录时，INFORMIX不报错。结论：参数可用表达式来代替，表达式中可包含存贮过程（必须只有一个返回值）、函数等， 测试主程序 drop procedure wd_test_main;create procedure wd_test_main() returning int; -使用了R

34、ETURNING后，要加分号。define i int;define num int;define flag int; - boolean; - 不能使用布尔类型。define mess char(100);define sql_err int;define isam_err int;define error varchar(200); - 设置通用出错陷阱； ON EXCEPTION SET sql_err, isam_err, error TRACE SQL err no |sql_err; TRACE ISAM err no |isam_err; TRACE error message

35、is |error |; TRACE end running with error.; END EXCEPTION ; SET DEBUG FILE TO /export/cq97/debug_wd; - 以后所有的TRACE语句，包括子过程，均写入该文件。 trace begin to test at |current; - 新增一用户，返回序列号； - begin work; -256错误号，本库无日志，不能执行事务处理。 let i=1; call wd_test_add_cust(wd,wd_test_add_3(i)+1) - 表达式作为参数 returning flag,mess,

36、num; - 返回时，必须把所有参数写齐。 if flag=0 then trace call failed.message is - |mess; - rollback; return; else trace customer_num is |num; - commit work; end if; - 查不到数据是否报错？- 不报错 select fname into error from customer where fname = wdwdwd; if dbinfo(sqlca.sqlerrd2)200; -写一个存贮过程，进行追踪 trace off; return update ok

37、;end procedure;- 测试UPDATE TRIGGER -drop trigger wd_test_update_t;create trigger wd_test_update_t update of fname on customer referencing new as n old as o for each row ( execute procedure wd_test_update(o.fname) into fname -直接修改触发字段，实验成功。 );- 配合TRIGGER wd_test_update_t，测试修改触发字段。-drop procedure wd_te

38、st_update;create procedure wd_test_update(i_fname like customer.fname) returning varchar(50); define char1 varchar(50); let char1 = trim(i_fname)|_update; - i_fname定义为字段类型，输入变量的的值包含空格，直至字段的长度。实验CHAR、VARCHAR类型，也是同样的情况？ return char1;end procedure; 9、游标FOREACH中，记录数的确定drop procedure wd_dbinfo_test;create procedure wd_dbinfo_test()define sql_err int;define isam_err int;define error varchar(200);define i,j,k int;define c_fname char(20); ON EXCEPTION SET sql_err, isam_err, error TRACE SQL