《【精选】Informix存储过程 详解》由会员分享，可在线阅读，更多相关《【精选】Informix存储过程 详解（26页珍藏版）》请在金锄头文库上搜索。
1、转载Informix 存储过程 详解一、存储过程概述存储过程是一个用户定义的函数,由存储过程语句(SPL) 和一组 SQL语句组成，以可以执行代码形式存储在数据库中，和表、视图、索引等一样，是数据库的一种对象。存储过程语言 SPL（Stored Procedure Language） ，只能用在存储过程中，可以分成下面几类：1、变量定义和赋值：define，let2、流程控制：分支控制：if thenelifelseend if;循环控制：FOR，FOREACH，WHILE,EXIT， CONTINUE3、函数调用与返回CALL， SYSTEM ，RETURN4、错误处理和调试TRACE， O
2、N 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_nam
3、e=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 er
4、ror 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);与存储
5、过程相关系统表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 rese
6、rvedSoftware Serial Number AAA#B000000create procedure xxxuser.pro1231()returning int;return 2007;end proceduredocument this is a test;$dbschema -d dbname -f procedurename proc.sql从系统表中查看select datafrom sysprocedures, sysprocbodywhere datakey =Tand sysprocedures.procname = pro1231and sysprocedures.p
7、rocid =sysprocbody.procid;结果显示datacreate procedure pro1231()returning int;return 2007;end proceduredocument this is a test二、创建存储过程语法：CREATE DBA PROCEDURE 过程名(参数, 参数 ,. )RETURNING 子句语句块END PROCEDURE document 子句 WITH LISTING IN 文件名过程名说明创建的存储过程的名字参数说明调用该存储过程所需的参数个数和类型一个存储过程可以不返回任何值，或返回一个或多个值，也可返回多组值。返回
8、多组值的存储过程称之为游标式存储过程，对该类存储过程，相应调用函数需作一些特殊处理。例子：假设建立一个脚本名为 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;-最后可以加分号(;)，也可以不加
9、存储过程中的语句块由 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
10、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
11、;.return num1,num2;end procedure;从 SQL中调用存储过程select * from userswhere number=proc_new(23);call语句从一个存储过程中调用另一个存储过程两种格式：CALL 过程名（参数 , .） RETURNING 变量，.;CALL 过程名（参数名参数, .） RETURNING 变量，.;说明：参数可以是 SPL表达式或是 SELECT语句，只要该语句返回单值，并且具有适当的类型和长度。如果参数个数多于被调用的存储过程的参数，则返回错误。如果参数个数少于被调用的存储过程的参数，则未说明的参数被初始化为其缺省值（该值在
12、创建存储过程时说明）若无缺省值， 则返回错误。RETURNING 子句说明的变量用于接收被调用存储过程的返回值，如无返回值， 则可省略。例如：define var_no1 int;三、存储过程语言变量局部变量仅在本存储过程中有效的变量。 局部变量不允许有缺省值全局变量在同一用户活动期间， 存储过程中的可被同一数据库的其它存储过程访问的的变量。全局变量必须在所有使用的存储过程中定义，并且必须给出缺省值，实际的缺省值是第一次被访问时定义的缺省值。变量（局部变量）的范围变量在语句块内有效， 如语句块嵌套，则同名的内层定义覆盖其外层定义， 内层语句执行完后，外层定义重新有效。存储过程表达式存储过程表达
13、式可以是除聚集函数表达式外的任何 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 VARCHA
14、R(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数据：INSERTUPDA
15、TERETURN（到前端应用程序）变量赋值 四种方式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 语句exe
16、cute 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的行，可以理解为将 s
