informix存储过程for

A. 如何编写informix的存储过程

1. 新建存储过程
create procere testpro(
col1 varchar(10)
, col2 decimal(20)
) returning decimal(10);
define v_count varchar(10);

select count(*) into v_count from systables;
return v_count;
end procere;
上例为一个简单的存储过程。
在informix 中没有出参的写法，11.7版本之后才有了出参 ，调用的方法是需要新建一个存储过程传参。因为我的版本比较低，不能执行就不做具体解释了。
2. 存储过程中的循环
for i in (1 to 10 step 1)
end for;
foreach

B. 如何运行 informix 存储过程

标准sql语言，通过数据库访问语言直接运行：
execute procere proc_name(in_para_list);
在存储过程中调用；
call proc_name(in_para_list);

C. Informix和Oracle存储过程的异同

1、建立存储过程的语法
1）Informix
create procere proc_name( [....in_parameter_list])
returning out_para_list / out_result_set;
2）oracle
create [or replace] procere procee_name
[ (arg1 [ {in | out | in out }] type
(argn [ {in | out | in out }] type,)]
{is | as} --代替DECLARE关键字
[ 变量定义区]
begin
end procere_name;

2、错误捕捉
1）Informix
使用
on exception
end exception
2）Oracle
使用
exception
when others then

3、对游标的处理
1）Informix
create procere pHasCursor()
define v_f1 integer；
begin
on exception
rollback work;
return;
end exception
begin work;
foreach curt1 with hold for
select f1 into v_f1 from t1 -- 注意这里没有分号
if (v_f1 = 1) then
update t1 set f2 = 'one' where current of curt1;
elif (v_f1 = 2) then
update t1 set f2 = 'two' where current of curt1;
else
update t1 set f2 = 'others' where current of curt1;
end if;
end foreach;
commit work;
end；
end procere;
2）Oracle
create or replace procere pHasCursor
as
v_f1 number(10,0);
cursor curt1 is
select f1 from t1 for update;
begin
open curt1;
loop
fetch curt1 into v_f1;
exit when curt1%notfound;
if (v_f1 = 1) then
update t1 set f2 = 'one' where current of curt1;
elsif (v_f1 = 2) then
update t1 set f2 = 'two' where current of curt1;
else
update t1 set f2 = 'others' where current of curt1;
end if;
end loop;
commit;
return;
exception
when others then
begin
rollback;
end;
end pHasCursor;

3、在存储过程中调用另外一个存储过程
1）Informix
Call pNoParam();
Call pNormalParam(1, ‘a’) returning v_Result;
2）Oracle
pNoParam;
pNormalParam(1, ‘a’, v_Result);

4、日期操作
1）Informix
define cur_dtime_var datetime year to second;
当前日期时间： let cur_dtime_var = current; -- datetime
2） Oracle
Currtime date;
Currtime := sysdate;

5、关于参数的说明
如果存储过程想返回一个参数，在informix中是通过返回值的形式实现的，而在oracle是通过输出参数或者输入输出参数实现的。
举例：
1）Informix:
create procere p1() returning integer;
return 0;
end procere;
2）oracle:
create or replace procere p1(x out number)
as
begin
x := 0;
end p1;

6、赋值
1）informix
let v_1 = 100;
2）oracle
v_1 := 100;

7、if语句
1）informix
if (v_1 =100) then
elif (v_1=200) then
Else
end if;
2）oracle
if (v_1 =100) then
elsif (v_1=200) then
Else
end if;

D. INFORMIX和ORACLE的存储过程的异同

INFORMIX数据库又要支持ORACLE数据库，如果应用程序涉及到存储过程的话，就需要既熟悉INFORMIX存储过程的写法，又需要熟悉ORACLE存储过程的写法。笔者将在工作中总结的一些经验写出来与大家共享。
建立存储过程的语法:

一、Informix

create PRocere proc_name( [....in_parameter_list])

returning out_para_list / out_result_set;

二、Oracle

create [or replace] procere procee_name

[ (arg1 [ {in | out | in out }] type

(argn [ {in | out | in out }] type,)]

{is | as} --代替DECLARE关键字

[ 变量定义区]

begin

end procere_name;

三、几个简单的例子

1、没有参数也没有返回值

1）Informix

create procere pNoParam()

begin

on exception

rollback work;

return;

end exception

begin work;

delete from t1;

delete from t2;

commit work;

end;

end procere;

2）Oracle

create or replace procere pNoParam

as

begin

delete from t1;

delete from t2;

commit;

exception

when others then

begin

rollback;

end;

end pNoParam;

2、有输入输出

往t1表中插入一条记录，返回值表示插入是否成功。

1）Informix

create procere pNormalParam(f1 integer, f2 varchar(10))

returning integer;

begin

on exception

rollback work;

return -1;

end exception

begin work;

insert into t1 values(f1, f2);

commit work;

return 0;

2）Oracle

create or replace procere pNormalParam(f1 number,

f2 varchar2, v_Result out number)

as

begin

insert into t1 values(f1,f2);

commit;

v_Result = 0;

return;

exception

when others then

begin

rollback;

v_Result := -1;

end;

end pNormalParam;

需要注意的是，在oracle存储过程中，参数是不能加上size的，比如f1，在t1表中该字段是number(10,0)，而这里只能写number，而不能写number(10,0)。

3、返回记录集

1）Informix

create procere pReturnSet() returning integer, varchar(10);

define i integer;

define j varchar(10);

foreach

select f1, f2 into i, j from t1

return i, j with resume;

end foreach;

end procere;

2）Oracle

create or replace package TestRefCursorPkg as type TestRefCursorTyp is ref cursor; procere pReturnSet(RefCursor out TestRefCursorTyp); end TestRefCursorPkg;

create or replace package body TestRefCursorPkg as

procere pReturnSet (RefCursor out TestRefCursorTyp)

as

localCursor TestRefCursorTyp;

begin

open localCursor for select f1, f2 from t1;

RefCursor := localCursor;

end pReturnSet;

end TestRefCursorPkg;

/

四、其他差异说明

1、错误捕捉

1）Informix

使用

on exception

end exception

2）Oracle

使用

exception

when others then

2、对游标的处理

1）Informix

create procere pHasCursor()

define v_f1 integer；

begin

on exception

rollback work;

return;

end exception

begin work;

foreach curt1 with hold for

select f1 into v_f1 from t1 -- 注意这里没有分号

if (v_f1 = 1) then

update t1 set f2 = 'one' where current of curt1;

elif (v_f1 = 2) then

update t1 set f2 = 'two' where current of curt1;

else

update t1 set f2 = 'others' where current of curt1;

end if;

end foreach;

commit work;

end；

end procere;

2）Oracle

create or replace procere pHasCursor

as

v_f1 number(10,0);

cursor curt1 is

select f1 from t1 for update;

begin

open curt1;

loop

fetch curt1 into v_f1;

exit when curt1%notfound;

if (v_f1 = 1) then

update t1 set f2 = 'one' where current of curt1;

elsif (v_f1 = 2) then

update t1 set f2 = 'two' where current of curt1;

else

update t1 set f2 = 'others' where current of curt1;

end if;

end loop;

commit;

return;

exception

when others then

begin

rollback;

end;

end pHasCursor;

3、在存储过程中调用另外一个存储过程

1）Informix

Call pNoParam();

Call pNormalParam(1, ‘a’) returning v_Result;

2）Oracle

pNoParam;

pNormalParam(1, ‘a’, v_Result);

4、日期操作

1）当前时间

① Informix

define cur_dtime_var datetime year to second;

当前日期时间： let cur_dtime_var = current; -- datetime

② Oracle

Currtime date;

Currtime := sysdate;

2）当前日期的增减

① Informix

let tmp_date = today + 3 UNITS day; -- 当前时间加三天

let tmp_datetime = current + 1 UNITS second; -- 当前时间加1秒种

② Oracle

Tmp_date := sysdate + 3; -- 当前时间加三天

Tmp_date := sysdate + 1/24/3600; --当前时间加1秒种

3）日期转换成字符串

① Informix

let v_PeriodEndTime = year(v_date)||extend(v_date,month to month)

||extend(v_date,day to day) ||extend(v_date,hour to hour)

||extend(v_date,minute to minute)|| extend(v_date,second to second);

② Oracle

v_PeriodEndTime := to_char(v_date, 'yyyymmddhh24miss');

4）字符串转换成日期

假设字符串的形式是yyyymmddhhmiss形式的

① Informix

-- 直接转换成日期

let v_BeginDate = substr(v_BeginTime,1,4)||'-'||substr(v_BeginTime,5,2)

||'-'||substr(v_BeginTime,7,2)||' '||substr(v_BeginTime,9,2)

||':'||substr(v_BeginTime,11,2)||':'||substr(v_BeginTime,13,2);

-- 这个月的第一天

let v_date = substr(v_BeginTime,1,4)||'-'

||substr(v_BeginTime,5,2)||'-1 00:00:00';

-- 这个星期的第一天

let v_date = substr(v_BeginTime,1,4)||'-'||substr(v_BeginTime,5,2)

||'-'||substr(v_BeginTime,7,2)||' 00:00:00';

let v_week = weekday(v_date);

let v_date = v_date - v_week UNITS day;

② Oracle

-- 直接转换成日期

v_BeginDate := to_date(v_BeginTime, 'yyyymmddhh24miss');

-- 这个月的第一天

v_BeginDate := trunc(to_date(v_BeginTime, 'yyyymmddhh24miss'), ‘mm’);

-- 这个星期的第一天

v_BeginDate := trunc(to_date(v_BeginTime, 'yyyymmddhh24miss'), ‘day’);

5）事务

在oracle中缺省情况下，一个事务的结束就是下一个事务的开始，所以对于一个事务来说，我们只要写commit；即可，不需要明确标出什么时候开始一个事务，而informix需要。

6）打印调试信息

7）Informix

--设置跟踪模式

set debug file to "trace_check"; -- with append;

--说明“with append”表示以追加模式打开跟踪结果文件

trace '开始执行存储过程'

trace 'v_date='||v_date;

trace ‘存储过程执行完毕’

trace off;

执行完以后打开当前目录下的trace_check即可看到打印出来的信息。

8）Oracle

DBMS_OUTPUT.PUT_LINE(‘开始执行存储过程’);

DBMS_OUTPUT.PUT_LINE('v_date='||v_date);

DBMS_OUTPUT.PUT_LINE(‘存储过程执行完毕’);

先设置一下缓冲区的大小

set serveroutput on size 100000; -- 如果不执行该语句，会看不到调试信息

执行完毕以后，打印出来的信息就会直接显示在界面上。

E. informix数据库如何创建存储过程

1、有输入输出
create procere pNormalParam(f1 integer, f2 varchar(10))
returning integer;
begin
on exception
rollback work;
return -1;
end exception
begin work;
insert into t1 values(f1, f2);
commit work;
return 0;
end;
end procere;
2、返回记录集
create procere pReturnSet() returning integer, varchar(10);
define i integer;
define j varchar(10);
foreach
select f1, f2 into i, j from t1
return i, j with resume;
end foreach;
end procere;
3、你可能语法不对，也可能你查询的值不唯一，或者是你没有正确的返回。
希望对你 有帮助。

F. 关于informix存储过程问题。写一个存储过程的时候出现以下图片的内容，求解

异常捕获：ON EXCEPTION 用ON EXCEPTION语句捕获一个或一组特定的异常(即错误),用错误号标识。
ON EXCEPTION IN (错误号，...)
SET SQL 错误变量 ISAM 错误文本变量 语句块
END EXCEPTION [WITH RESUME]
IN 子句说明欲捕获的错误号， 缺省时捕获所有的错误号。
SET 子句接收错误号和错误文本的变量，该语句可省略。
SQL 错误变量： 说明接收SQL 错误号的变量
ISAM错误变量： 说明接收ISAM错误号的变量
错误文本变量： 说明接收与SQL错误号对应的错误文本的变量
WITH RESUME 关键字用于把控制转向到捕获的错误被处理后的紧接发生异常语句后的语句，其效果相当于异常被处理后程序继续执行下去。 WITH RESUME 可以省略。

问题1：只要有错误，都会捕获
问题2：set v_errcode——将SQL 错误号赋给变量v_errcode
set pdqpriority 50——PDQ(Parallel database query）并行数据库查询，该语句设置当前session使用50%的资源
问题3：trace这句输出存储过程开始的时间和“Run Begin！”

G. 请问如何在informix的存储过程中加入循环语句

create procere inserttest(numrows int)
define i int;
let i=0;
while i< numrows
insert into test values(i,i+1,i*3);
let i=i+1;
delete test where a=i;
end while
end procere;
适当的地方你改一下吧

H. informix存储过程迁移oracle问题

for c1 in (select first 5 ini_id, ne_label, region_id,object_class, sum(count) sum_count
from MSC_ALARM_MONTH
where first_result = v_first_result
and vendor_id = 4 -- 诺基亚
group by ini_id, ne_label, region_id ,object_class
order by sum_count desc) loop

end loop;

I. Informix存储过程题(谁能解决，小弟万分感谢）

其实写逻辑的话并不难，主要是对新的语言和开发环境的陌生
不如看看下边的informix存储过程的语法，学
写逻辑的话就是程序员的基本功了，掌握了语法，只要思路清晰，无论是什么语言什么环境都不会太难

======================================================================
存储过程基本语法：
1）创建存储过程
create procere proc_name( [....in_parameter_list])
returning out_para_list / out_result_set;
2）删除存储过程
drop procere proc_name;

3）存储过程存放
存储过程信息放在sysproceres系统目录表中，权限存放再sysprocauth表中。sysprocbody表存放每个存储过程的文本、编译伪代码、等信息。sysprocplan表存放查询执行计划和存储过程中引用的表格之间的相关性清单。

4）执行存储过程
。是标准SQL语言，通过数据库访问语言直接运行：
execute procere proc_name(in_para_list)
returning out_para_list / out_result_set;
。在存储过程中调用；
call proc_name(in_para_list) returning out_para_list / out_result_set;
或 let local_variable = my_spl("argumeny_1);
调用不同数据库的存储过程: execute procere database:my_sp1();
调用远程数据库不同网络节点：execute procere database@sitename:my_sp1();

5、 其他常用语句
1）分支
if ... then
...
elif ... then
...
else
...
end if

2）循环(三种)
。for
for i=1 to 10
...
end for (没有分号)

。while
while ...
...
end while(没有分号)

。foreach
foreach select col1,.. into val1,...
from tabname where...
....
end foreach(没有分号)

3）循环控制语句：
。continue：条过余下的循环体，启动下一轮循环；
。EXIT：终止循环；
。RETURN：完成退出存储过程；
。RAISE EXCEPTION：退出，在循环外捕获错误；
例子：
foreach select col1,.. into val1,...
from tabname where...
....
if ... then
continue foreach;
end if;
...

for i=1 to 10
...
if ... then
exit for;
end if;
end for;

while ...
if ... then
return ...;
end if;
end while;
end foreach(没有分号)

4）分块：BEGIN和END必须成对出现
...
BEGIN
on excewption
....
end exception

begin
....
end;
END；

四、SELECT语句
1、WHERE子句的关键字：
1）BETWEEN：指定数值范围；
2）IN：指定数值表；
3）LIKE：指定通配符文本查询；
4）MATCHES：指定通配符文本查询；
5）IS NULL：查询NULL值；
6）NOT：否定查询结果；
2、WHERE子句的操作符
1）= ； 2）！= 不等于 3）<> 不等于 4）> 大于 5）>= 大于或等于；
6）<：小于； 7）<=：小于或等于；
eg1 : select * from tablename
where col1 [not] between val1 and val2;
eg2 : select * from tablename
where col1 [not] in [val1,val2,...valn];
3、字符值搜索
1）子串：字符列名后用[]，指定自串的开始和结束位置；如COL1[1，5]；
注：选择不是从第一个字符开始的子串时，不能使用该列的索引；并且消耗资源多；
第一个数字代表开始位置；第二个数字代表终止位置，该数字不能大于列长度；
2）LIKE：
。%：代表0个或多个字符；
。下画线(_)代表一个字符；可以连续使用多个下画线代表多个字符；
。搜索原字符（%，_)，需使用转意符（\）；例如：
select * from tablename where col1 like "%\%%":查询带%的内容；
注：。LIKE关键字是ANSI标准，方便移植；MATCHES功能有扩展；
。大小写有区别；

3）MATCHES：根据通配符搜索字符列。通配符与LIKE不同.( 注：该命令大小写有区别)
。*：代表0个或多个字符；
。？：代表一个字符；
。能对一个字符位指定字符范围和字符表，利用[]实现。（该功能LIKE没有）；若要
指定字符范围，可以使用连字符（-）分开两个字符；例如：查询从A到L字母开始
的内容：
select * from tablename where col1 matches "[A-L]*";
。可以在前面加上插入符（^）否定一个范围的结果，例如：查询排除A到L字母开头
的内容：
select * from tablename where col1 matches "[^A-L]*";
。可以省略连字符，用[]指定字符表，将每个字符放在[]中，例如：查询以A、B、
C开头的信息；
select * from tablename where col1 matches "[ABC]*";
。前面加上插入符（^)可以否定查询结果，例如：上例取反：
select * from tablename where col1 matches "[^ABC]*";
。根据字母组合选择值：例如查询COL1中任何地方带‘ABC或abc’的信息：
select * from tabname where col1 matches "*[aA][bB][cC]*"
说明：由于区分大小写，所以要对每一位指定大小写；
select * from tabname where col1 matches "[A-Ls]*"
查询COL1中以A-L和s开头的信息；
。若要使用元字符（*和？），则需要使用转意符(\)，例如：查询COL1中有*的信
息：
select * from tabname where col1 matches "*\**"
4、NULL 处理
1）列的值未知时，使用NULL值标识。
2）NULL值不同于0或空格，使用IS [NOT] NULL 查询未知值 或 NULL 值；

5、歧义列名
1）指定多个表中都存在的列名，需要加上表名限制，例如：
select col1,col2,col3 from t1,t2 where t1.col1=t2.col1 --错误，因COL1没限制
select a.col1,a.col2,b.col3 from t1 a,t2 b where a.col1= b.col1;

5、外连接
1）返回主表的全部满足条件的记录（不管从表是否有匹配）和从表的匹配记录（相交记
录）；
2）考虑点：
。一个表是主表，一个是从表；
。取得主表的所有记录，不管从表是否有匹配；
。从表名放在OUTER关键字后面；
。若从表没有符合连接条件的记录，则从表中的列未NULL值；
。若指定外连接，则连接条件放在WHERE子句中；
例子： select a.col1,a.col2 ,b.col3 from t1 a,outer t2 b
where a.col1 = b.col1;

6、用括号嵌套简单连接（简单连接）
例子：select a.col1,col2,b.col3,col4,col5 from t1 a,OUTER(t2 b,t3 c)
where a.col1=b.col1
and b.col2=c.col2
and col5='abc'; （COL5在T3表中）
1）例子中首先将T3连接到T2，查询COL5=‘ABC’的内容；
2）然后将结果于表T1连接，取得最后结果；（是外连接）
3）OUTER关键字用于外连接T2和T3表的连接结果；

7、用括号嵌套外连接
例子：select a.col1,col2,b.col3,col4,col5
from t1 a,OUTER(t2 b,OUTER t3 c)
where a.col1=b.col1
and b.col2=c.col2
and col5='abc'; （COL5在T3表中）
注：T2与T3是外连接，T1与（T2和T3的外连接）结果是外连接；

8、GROUP BY 子句和HAVING 子句
1）例：select tabname[1,3],count(*) as cnt from systables group by tabname[1,3]
2）HAVING子句对一组采用过滤条件，通常与GROUP BY 子句一起使用，指定每个组的
过滤条件；
select tabname[1,3],count(*) as cnt
from systables group by tabname[1,3]
having count(*)>1

9、ORDER BY 子句
1）指定返回结果顺序；
2）该子句中列出一个或多个列；优先级依次降低；
3）ASC指定升序，DESC执行降序；

10、INTO TEMP 子句
1）生成临时表，在对话期间存在；
2）可以用DROP TABLE显示删除；
3）下列条件来临时表自动删除：1、退出程序时2、关闭当前数据库时3、切断当前数据库连接时。

五、日期时间操作
1、数据类型：date , datetime;
2、当前日期时间：current;
3、当前日期：today;
4、相关函数：参见附件“常用函数”
5、例子：
1）当前日期时间： let cur_dtime_var = current; -- datetime
2）当前日期：let cur_date_var = today; --date
3）日期加减：
。 let tmp_date = today + 3 UNITS day -- 当前时间加三天
。let tmp_date = today + interval(7) day to day --当前时间加上7天
4）日期/时间转换成字符串：
define s char(20);
define y datetime year to second;
let y = current;
let s = year(y)||month(y)||day(y)
||hour(y)||extend(y,minute to minute)
|| extend(y,second to second);

5）字符串转换成日期/时间：
define dt datetime year to second;
let dt = EXTEND(DATETIME(2002-07-09 01:02:03) YEAR TO second, YEAR TO second);

六、游标操作
1、定义
foreach cur_name for
select .... into ....from .... where .....
....
end foreach;

2、例子
define v1 integer;
define v2 datetime year to second;
define v3 varchar(10);
define v4 varchar(20);
FOREACH
select col1,col2,col3,col4 into v1,v2,v3,v4
from t1
RETURN v1,v2,v3,v4 WITH RESUME;
END FOREACH;

七、事务
1、语法：
begin work;
.....
commit work / rollback work;

2、说明：
1）必须在日志数据库中，才能使用事务，否则提示错误；
2）事务中的操作不能过多，否则可能造成长事务被取消或造成死锁；

八、异常处理方法
方法1：在每次操作后进行判断。
缺点：该方法操作烦琐，效率低下；
优点：能准确定位问题出处；

方法2：使用数据库提供的异常保护功能：
on exception
....;
end exception;
缺点：不能准确定位问题的出处；
优点：操作简单、高效；
使用建议：根据实际情况选择，往往两者灵活结合使用效果最好；

九、存储过程跟踪方法
方法1、将跟踪内容写入表中，过程执行完成后查询；
方法2、在过程中直接返回跟踪对象的内容；
方法3、使用trace功能，详细使用参见后面的例子；

十、存储过程加载
方法一：使用dbaccess工具。使用NEW菜单项中直接输入或拷贝存储过程语句，再使用
RUN功能运行；
缺点：由于BUFFER大小有限，存储过程语句不能过多，超过部份自动截断；
方法二：使用dbaccess工具，使用choose功能直接选择编辑好的SQL文件，再使用RUN功能
运行。
方法三：使用工具dbaccess，直接加载SQL脚本：
dbaccess db_name file_name.sql

十一、例子
1、用存储过程中返回一个或多个值
create procere proc_test() returning int,datetime year to second,varchar(10);
define v1 integer;
define v2 datetime year to second;
define v3 varchar(10);
let v1 = 1;
let v2 = current;
let v3 = "test";
return v1,v2,v3;
end procere;

2、用存储过程中返回一条记录
预设条件：表T1，字段col1 int, col2 datetime year to day,col3 char(10);
create procere proc_test() returning int,datetime year to second,varchar(10);
define v1 integer;
define v2 datetime year to second;
define v3 varchar(10);
select col1,col2,col3 into v1,v2,v3
from t1
where col1=1; --col1为关键字，满足该条件的记录只有一条；
return v1,v2,v3;
end procere;

3、用存储过程中返回多条记录（游标 -- cursor）
预设条件：表T1，字段col1 int, col2 datetime year to day,col3 char(10);
create procere proc_test() returning int,datetime year to second,varchar(10);
define v1 integer;
define v2 datetime year to second;
define v3 varchar(10);
foreach
select col1,col2,col3 into v1,v2,v3
from t1;
return v1,v2,v3 with resume;
end foreach;
end procere;
======================================================================