Informix存储过程

您所在的位置：网站首页 › dbvisualizer调用存储过程命令 › Informix存储过程

Informix存储过程

2024-07-09 11:08| 来源: 网络整理| 查看: 265

一、存储过程概述

　　存储过程是一个用户定义的函数,由存储过程语句(SPL) 和一组SQL语句组成，以可以执行代码形式存储在数据库中，和表、视图、索引等一样，是数据库的一种对象。

存储过程语言SPL（Stored Procedure Language），只能用在存储过程中，可以分成下面几类：

1、变量定义和赋值：define，let

2、流程控制：

分支控制： if then elif else end if;

循环控制： FOR，FOREACH，WHILE,EXIT， CONTINUE

3、函数调用与返回

CALL， SYSTEM ，RETURN

4、错误处理和调试

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; ----输入一次记数加1 update 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 procedure document ‘this is a error count procedure’ with 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 db@servername:count_add('administrator');

与存储过程相关系统表

SYSPROCEDURES:记录数据库的所有存储过程

SYSPROCBODY：记录存储过程文本

SYSPROCPLAN：记录存储过程执行的查询规划

SYSPROCAUTH：记录授予存储过程的权限

查看存储过程代码、导出存储过程

$dbschema -d dbname -f procedurename -ss

例如：

xxxdb% dbschema -d xxdb -f pro1231 DBSCHEMA Schema Utility

INFORMIX-SQL Version 9.40.FC7

create procedure "xxxuser".pro1231() returning int;

return 2007;

end procedure document "this is a test" ;

$dbschema -d dbname -f procedurename proc.sql

从系统表中查看

select data from sysprocedures, sysprocbody where datakey ="T" and sysprocedures.procname = "pro1231" and sysprocedures.procid = sysprocbody.procid;

结果显示

data create procedure pro1231() returning int;

return 2007;

end procedure document "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后面不能加分号(;)，否则会报语法错误 document 'this is a new procedure' --这里也不能加分号(;)，ducoment子句需加双引号或单引号。 with listing in '/export/home/user/pro.log'; --最后可以加分号(;)，也可以不加

存储过程中的语句块由SPL语句和SQL语句组成，但不包含下面的SQL语句

CREATE DATABASE DATEBASE CLOSE DATEBASE CHECK TABLE REPAIR INFO OUTPUT LOAD UNLOAD CREATE PROCEDURE CREATE PROCEDURE FROM

document子句对存储过程做一些说明，可省略。

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 users where 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数据类型只使用描述符：

LET DEFINE CALL SELECT

存取BLOB数据：

INSERT UPDATE RETURN（到前端应用程序）

变量赋值四种方式

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 age>30; --select语句返回大于1的行，可以理解为将select到的值放在id这个列表中一个一个执行。　　update others set uservalue=345 where userid=id; 　　end foreach; end procedure;

使用update游标

条件分支 if then elif then else end 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_tbl where num > 100 if p_time is not null then delete 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) then … end if if var_num > all(select usernum from users where usernum = 23) then … end if if var_name matches “A*” then … end 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 i

【本文地址】

Informix存储过程

Informix存储过程

今日新闻

推荐新闻