Informix存储过程详解.docVIP

转载Informix存储过程 详解一、存储过程概述存储过程是一个用户定义的函数,由存储过程语句(SPL) 和一组SQL语句组成，以可以执行代码形式存储在数据库中，和表、视图、索引等一样，是数据库的一种对象。存储过程语言SPL（Stored Procedure Language），只能用在存储过程中，可以分成下面几类：1、变量定义和赋值：define，let2、流程控制：分支控制：if thenelifelseend if;循环控制：FOR，FOREACH，WHILE,EXIT， CONTINUE3、函数调用与返回CALL， SYSTEM ，RETURN4、错误处理和调试TRACE， ON EXCEPTION， RAISE EXCEPTION例子：drop procedure count_add;-删除存储过程create procedure count_add(user_name_var varchar(50) default administrator)-user_name_var传递参数变量在此定义returning varchar(50);-返回一个字符型的值define error_count_var integer;-定义输入的次数变量select error_count into error_count_var from users where user_name=user_name_var;-error_count默认是0，从0开始记数let error_count_var=error_count_var 1;-输入一次记数加1update users set error_count= error_count_var where user_name =user_name_var return user_name_var;-返回变量值，与returning对应。return user_name_var WITH RESUME;-将保证存储过程继续执行，所有的变量均保持原有的值end proceduredocument this is a error count procedurewith listing in /export/home/xie/errorcount.txt;将该脚本保存为count_add.sql，在unix系统下，执行如下命令建立存储过程：$dbaccess db1 count_add.sql存储过程建立在数据库db1中，执行存储过程可以通过dbaccess工具：$dbaccess db1然后进入执行状态execute procedure count_add(administrator);execute procedure dbservername:count_add(administrator);与存储过程相关系统表SYSPROCEDURES:记录数据库的所有存储过程SYSPROCBODY：记录存储过程文本SYSPROCPLAN：记录存储过程执行的查询规划SYSPROCAUTH：记录授予存储过程的权限 查看存储过程代码、导出存储过程$dbschema -d dbname -f procedurename -ss例如：xxxdb% dbschema -d xxdb -f pro1231DBSCHEMA Schema Utility INFORMIX-SQL Version 9.40.FC7 Copyright IBM Corporation 1996, 2004 All rights reservedSoftware Serial Number AAA#B000000create procedure 1231()returning int;return 2007;end proceduredocument this is a test;$dbschema -d dbname -f procedurename proc.sql从系统表中查看select datafrom sysprocedures, sysprocbodywhere datakey =Tand cname = pro1231and cid =cid;结果显示datacreate procedure pro1231()returning int;return 2007;end proceduredocument this is a test二、创建存储过程语法：CREATE DBA PROCEDURE 过程名(参数, 参数 ,. ) RETURNING 子句 语句块 END PROCEDURE document 子句 WITH LISTING IN 文件名过程名说明创建的存储过程的名字参数说明调用该存储过程所需的参数个数和类型一个存储过程可以不返回任何值，或返回一个或多个值，也可返回多组值。返回多组值的存储过程称之为游标式存储过程，对该类存储过程，相应调用函数需作一些特殊处理。例子：假设建立一个脚本名为proc1231.sql的文件，内容如下：create procedure proc1231()returning int;return 2007;end procedure-end procedure后面不能加分号(;)，否则会报语法错误documentthis is a new procedure-这里也不能加分号(;)，ducoment子句需加双引号或单引号。with listing in /export/home/user/pro.log;-最后可以加分号(;)，也可以不加存储过程中的语句块由SPL语句和SQL语句组成，但不包含下面的SQL语句CREATE DATABASEDATEBASECLOSE DATEBASECHECK TABLEREPAIR INFO OUTPUT LOAD UNLOADCREATE PROCEDURECREATE PROCEDURE FROMdocument子句对存储过程做一些说明，可省略。WITH LISTING IN 选项说明接受编译器输出信息的文件名，如省略， 则编译器不产生输出。向存储过程中传递变量create procedure procname(var_num integer default null)delete from proctable where colno=var_num;end procedure;execute procedure procname(11);return语句从存储过程中返回0个或多个值RETURN 语句说明的返回值的个数和类型必须与创建存储过程时说明的返回值的个数和类型一致，或者不返回任何值，在后一情形下，所有的返回值为空值。WITH RESUME子句将保证存储过程继续执行，所有的变量均保持原有的值。该子句用于返回多组值，比如循环语句中。create procedure proc_new()returning int,int;define num1;define num2;.return num1,num2;end procedure;从SQL中调用存储过程select * from userswhere number=proc_new(23);call语句从一个存储过程中调用另一个存储过程两种格式：CALL 过程名（参数, .） RETURNING 变量，.;CALL 过程名（参数名参数, .） RETURNING 变量，.;说明：参数可以是SPL表达式或是SELECT语句，只要该语句返回单值，并且具有适当的类型和长度。如果参数个数多于被调用的存储过程的参数，则返回错误。如果参数个数少于被调用的存储过程的参数，则未说明的参数被初始化为其缺省值（该值在创建存储过程时说明）若无缺省值， 则返回错误。RETURNING 子句说明的变量用于接收被调用存储过程的返回值，如无返回值， 则可省略。例如：define var_no1 int;三、存储过程语言变量局部变量仅在本存储过程中有效的变量。 局部变量不允许有缺省值全局变量在同一用户活动期间， 存储过程中的可被同一数据库的其它存储过程访问的的变量。全局变量必须在所有使用的存储过程中定义，并且必须给出缺省值，实际的缺省值是第一次被访问时定义的缺省值。变量（局部变量）的范围变量在语句块内有效， 如语句块嵌套，则同名的内层定义覆盖其外层定义， 内层语句执行完后，外层定义重新有效。存储过程表达式存储过程表达式可以是除聚集函数表达式外的任何SQL算术表达式例子： var_value1; var_value1 var_value2;变量定义用DEFINE 语句定义变量，其类型可以是除SERIAL数据类型外 的所有SQL 数据类型定义TEXT 或BYTE 类型变量时， 需在其变量前加关键字REFERENCES， 以表明该变量并不含有真正的数据， 而只是指向数据的指针。可以使用LIKE定义与字段类型一致的数据类型传入参数变量的定义在create procedure procname(var_value int)定义例子：DEFINE i,j INT;DEFINE name VARCHAR(12);DEFINE time_stamp DATETIME YEAR TO SECOND DEFAULT CURRENT YEAR TO SCECOND;DEFINE date_value DATE;DEFINE txt REFERENCES TEXT;DEFINE by REFERENCES BYTE;DEFINE p_customer like users.customer_num;定义全局变量define global global_var int default 0;局部BLOB数据类型只使用描述符：LETDEFINECALLSELECT存取BLOB数据：INSERTUPDATERETURN（到前端应用程序）变量赋值 四种方式1、利用LET 语句LET i, j=1, 0;LET var_name, var_num=(SELECT username, usernum FROM users WHERE usernum=100);2、利用SELECT 或SELECT . INTO 语句SELECT username INTO var_name FROM users WHERE usernum=100;3、利用CALL 语句call proc_new(22) returning var_no1;4、利用EXECUTE PROCEDURE .INTO 语句execute procedure proc_name(username,address into p_name,p_addr);语句块create procedure pro_test()returning int; begin define i int; let i=1; end-begin,end 语句块（显示）retrun i;-returning,return语句块（隐含）end procedure;foreach循环create procedure pro_foreach()define id int;select userid into id from users where age30;-select语句返回大于1的行，可以理解为将select到的值放在id这个列表中一个一个执行。update others set uservalue=345 where userid=id;end foreach;end procedure;使用update游标条件分支if thenelif thenelseend if-drop procedure pro_dele;create procedure pro_dele()define p_time date;begin work;foreach curl for-update游标必须命名select time_stamp into p_time from pro_dele_tblwhere num 100if p_time is not null thendelete from pro_del_tbl where current of curl;-删除当前记录end if;end foreach;commit work;-所有修改记录的锁被释放。end procedure;-if语句中的表达式If exists(select username from users where usernum = 13) thenend ifif var_num all(select usernum from users where usernum = 23) thenend ifif var_name matches “A*” thenend if循环语句while格式： WHILE 条件语句 语句块 END WHILE执行过程先测试条件，若为真，则执行语句块，否则退出循环。重复上述步骤，开始下一次循环，直到条件为假而退出循环WHERE 与FOREACH 的区别WHILE 循环的条件是不定的，循环次数不定，因而可能出现无穷循环FOR和FOREACH 循环的条件是确定的，循环次数也是明确的，不可能出现无穷循环例子create procedure prowhile()define i int;define sum int;let i=1;let sum=0;while i100let sum=sum i;let i=i 1;end while;end procedure;循环语句for三种格式FOR 变量 IN (expr1 to expr2 STEP expr3) 语句块 END FOR-expr1,expr2表示范围，expr3表示步长，默认为1for i=1 to 10 step 2.end for;FOR 变量 (expr1 to expr2 STEP expr3) 语句块 END FOR-expr1,expr2表示范围，expr3表示步长，默认为1FOR 变量 IN (expr1, expr2,.) 语句块 END FORfor i in (1,2,3,4,5,7,8).end for;循环的转移for i = 1 to 10if i = 5 thencontinue for;-contiune将执行下一次循环elif i = 8 thenexit for;-exit for将退出循环，执行for循环的下一条语句end if;end for;循环语句foreach用 FOREACH 语句可以查询或操作一组记录FOREACH 隐式定义并打开一个游标三种格式FOREACH WITH HOLD SELECT . INTO 语句 语句块 END FOREACH；FOREACH 游标名WITH HOLD FOR SELECT . INTO 语句 语句块 END FOREACH；FOREACH EXECUTE PROCEDURE 存储过程名(参数.参数) INTO 变量, 变量, . 语句块 END FOREACH；FOREACH 定义的游标在以下情况下关闭：无行返回事务提交或回滚导致非保护游标关闭循环非正常退出（使用EXIT 或 ON EXCEPTION）操作系统命令system echo Delete Operation Completed |mail judy;数据库系统一直等到上述命令执行完毕不能使用返回值判断执行是否成功如执行不成功，将设置适当的ISAM错误代码和SQL错误代码检查NOTFOUND条件create procedure protest()define num integer;returning integer;foreach select usernum into num from userswhere usernum=20-select执行失败将不执行foreach中的语句。return num;end foreach;return;-return没有返回值将在前端应用中引发NOTFOUNDend procedure;递归调用create procedure digui(i int defualt 1)returning int;if i 6 thenreturn 1;end if;return n * digui(n-2);-调用自己end procedure;获取serial值create procedure seri_inst()define seri int;insert into users(user_num,user_date)values (1,2006-01-03);let seri = dbinfo(sqlca.sqlerrd1);-获取上一条insert语句中的serial值insert into other(num,name)values(seri,new);end procedure;获取处理的记录数create procedure num_rows()returning int;define num_rows int;delete from orders where customer_num = 104;let num_rows = dbinfo(sqlca.sqlerrd2);-select、update或update的记录数return num_rows;end procedure;跟踪调试语句trace存储过程被正确创建后，说明无语法错误，但有可能有逻辑错误TRACE语句用于调试存储过程， 它可以跟踪以下存储过程实体的值：变量（Variables)过程参数(Procedure arguments)返回值(Return values)SQL 错误代码（SQL error codes)ISAM 错误代码(ISAM error codes)TRACE 语句把跟踪结果写到一个文件中， 该文件由SQL语句SET DEBUG FILE指定TRACE 语句的三种形式：TRACE ON ：打开跟踪调试， 跟踪所有语句TRACE OFF ：关闭跟踪调试TRACE PROCEDURE： 对于过程调用， 不跟踪过程体，仅跟踪过程的输入和返回值。create procedure tracepro(var_user_num int)define var_user_date date;set debug file to /export/home/user/trace;-设置输出文件trace on;-跟踪所有执行的语句select user_date into var_user_datefrom userswhere user_num = var_user_num;if var_user_date is null thentrace user date is null;-执行到这里输出user date is nullexecute procedure other(var_user_num );end if;trace off;-关闭跟踪end procedure;四 存储过程中的异常处理没有异常处理create procedure yichang ()returning int;define var_num integer;let var_num = jack;-存储过程到这将会出错 return var_num;end procedure;异常处理create procedure err_deal()define sql_err int;define isam_err int;define error_info char(100);on exception set sql_err, isam_err, error_infocall error_rout(sql_err,isam_err,error_info);end exception;end procedure;异常捕获：ON EXCEPTION用ON EXCEPTION语句捕获一个或一组特定的异常(即错误),用错误号标识。ON EXCEPTION 语句与RAISE EXCEPTION 语句一起提供存储过程语言（SPL）的错误 捕获和恢复机制。在一个语句块内可以定义多个 ON EXCEPTION 语句。被捕获的异常可以是系统异常或用户定义的异常。一旦异常被捕获，错误状态即被清除。ON EXCEPTION 语句的位置：ON EXCEPTION 语句是一声明性而非执行性语句， 故应位于任何执行语句之前， 而位于DEFINE 语句之后。格式 ON EXCEPTION IN (错误号，.) SET SQL 错误变量 ISAM 错误文本变量 语句块 END EXCEPTION WITH RESUMEIN 子句说明欲捕获的错误号， 缺省时捕获所有的错误号。SET 子句接收错误号和错误文本的变量，该语句可省略。SQL 错误变量： 说明接收SQL 错误号的变量ISAM错误变量： 说明接收ISAM错误号的变量错误文本变量： 说明接收与SQL错误号对应的错误文本的变量WITH RESUME 关键字用于把控制转向到捕获的错误被处理后的紧接发生异常语句后的语句，其效果相当于异常被处理后程序继续执行下去。 WITH RESUME 可以省略。捕捉特定的错误create procedure err_deal()define sql_err int;define isam_err int;defi