记informix转oracle的一些修改点

记informix转oracle的一些修改点

最近公司项目需要由informix数据库转向oracle数据库，故写下一些遇到的语法修改点。

1. 建立存储过程的语法
Informix
CREATE PROCEDURE procedure-name ( [expression[,…])
[define-stmt]
[exception-declaration]
[statement-list]
END PROCEDURE [DOCUMENT string[,…]][WITH LISTING IN string]
oracle
create [or replace] procedure procedue_name
[ (arg1 [ {in | out | in out }] type,
………
(argn [ {in | out | in out }] type,)]
{is | as} –代替DECLARE关键字
[ 变量定义区]
begin
……….
end procedure_name;
注意，若存过没有入参，oracle数据库后面不需要()。如果存储过程想返回一个参数，在informix中是通过返回值的形式实现的，而在oracle是通过输出参数或者输入输出参数实现的。

2. 对于oracle的function，不能返回多个值，这时要用数组返回。例：
首先要创建存储数据的object

 CREATE OR REPLACE TYPE aaa as object(areastr VARCHAR2(4000),V_IS_END VARCHAR(20));

然后创建该对象的table

CREATE OR REPLACE TYPE aaa_tabae table of aaa;

主体functional

CREATE OR REPLACE FUNCTION aaa(a int,b int)return aaa_tableasretValue aaa_table :=  aaa_table();areastr VARCHAR2(4000);V_IS_END VARCHAR(20);begin --main statmentV_REVALUE.EXTEND();V_REVALUE(V_REVALUE.COUNT):=get_t_nodecity_depart(areastr,V_IS_END);RETURN V_REVALUE; end;

而informix的function可以直接返回多个值，

return areastr,V_IS_END;

3. 赋值与判断语句

--赋值：--informixlet v_1 = 100;--oraclev_1 := 100;--判断：--informixif (v_1 =100) then    ……elif (v_1=200) then    ……Else   ……end if;--oracleif (v_1 =100) then    ……elsif (v_1=200) then    ……Else   ……end if;

4. 错误捕捉
Informix

on exception    ......end exception

Oracle

exception     when others then     ……

例如：

begin --main statmentexception     when others then     --do somethingend;

对于自定义exception，应该这样做：

asa EXCEPTION;b EXCEPTION;BEGIN--main statment      IF ...      RAISE a;      else      RAISE b;exception  when a then  --do something  when b then  --do somethingend;

5. 对游标的处理

informix:

foreach cur_name for       select .... into ....from .... where .....      ....end foreach;

oracle:

for cur_name in( select .... into ....from .... where .....) loop      ....end loop;

还有一种是打开游标操作的，具体可百度；

5. 在存储过程中调用另外一个存储过程

Informix :

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

Oracle :

pNoParam;pNormalParam(1, ‘a’, v_Result);

6. 日期操作
i）当前时间
Informix

define cur_dtime_var           datetime year to second;

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

Currtime date;Currtime := sysdate;

ii）当前日期的增减
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秒种

iii）日期转换成字符串
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');

iv）字符串转换成日期
假设字符串的形式是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’);

7.事务

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

8.打印调试信息

Informix

--设置跟踪模式set debug file to "trace_check";  -- with append;      --说明“with append”表示以追加模式打开跟踪结果文件

trace '开始执行存储过程'....trace 'v_date='||v_date;....trace ‘存储过程执行完毕’trace off;

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

Oracle

DBMS_OUTPUT.PUT_LINE(‘开始执行存储过程’);……DBMS_OUTPUT.PUT_LINE('v_date='||v_date);……DBMS_OUTPUT.PUT_LINE(‘存储过程执行完毕’);

先设置一下缓冲区的大小

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

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

暂时就这些。。。