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;
======================================================================