Similarities and differences between Informix and Oracle stored procedures

To establish the syntax for a stored procedure:

First, Informix

CREATE PROCEDURE Proc_name ([... in_parameter_list])

Returning Out_para_list/out_result_set;

Second,Oracle

create [or replace] procedure Procedue_name

[(Arg1 [{in | out | out}] Type

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

{is | as}--Replaces DECLARE keyword

[Variable definition area]

Begin

End procedure_name;

Three, a few simple examples

1, no parameters and no return value

1) Informix

CREATE PROCEDURE Pnoparam ()

Begin

On exception

Rollback work;

Return

End exception

Begin work;

Delete from T1;

Delete from T2;

Commit work;

End

End procedure;

2)Oracle

Create or replace procedure Pnoparam

As

Begin

Delete from T1;

Delete from T2;

Commit

exception

When others then

Begin

Rollback

End

End Pnoparam;

2, have input and output

Inserts a record into the T1 table, indicating whether the insert succeeded.

1) Informix

CREATE PROCEDURE 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 procedure 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;

Note that in Oracle stored procedures, parameters cannot be added to size, such as F1, where the field is number (10,0) in the T1 table, and only number can be written, not number (10,0).

　　

　　

3. Return recordset

1) Informix

CREATE PROCEDURE 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 procedure;

2) Oracle

Create or Replace package testrefcursorpkg as type Testrefcursortyp is REF CURSOR; Procedure Preturnset (refcursor out Testrefcursortyp); End testrefcursorpkg;

Create or replace package body testrefcursorpkg as

Procedure Preturnset (Refcursor out Testrefcursortyp)

As

Localcursor Testrefcursortyp;

Begin

Open Localcursor for select F1, f2 from T1;

Refcursor: = Localcursor;

End Preturnset;

End testrefcursorpkg;

/

Iv. Other Difference Notes

1, Error capture

1) Informix Use

On exception

End exception

2) Oracle

Use

exception

When others then

2, the processing of the cursor

1) Informix

CREATE PROCEDURE 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--note there is no semicolon

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

2) Oracle

Create or replace procedure 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. Call another stored procedure in the stored procedure

1) Informix

Call Pnoparam ();

Call Pnormalparam (1, ' a ') returning v_result;

2) Oracle

Pnoparam;

Pnormalparam (1, ' a ', v_result);

4. Date operation

1) Current time

①informix

Define Cur_dtime_var datetime to second;

Current date Time: let Cur_dtime_var = present; --DateTime

②oracle

Currtime date;

Currtime: = sysdate;

2 increase or decrease of current date

①informix

Let Tmp_date = today + 3 UNITS day; --Current time plus three days

Let Tmp_datetime = current + 1 UNITS second; --Current time plus 1 seconds

②oracle

Tmp_date: = sysdate + 3; --Current time plus three days

Tmp_date: = Sysdate + 1/24/3600; --Current time plus 1 seconds

3) Date converted to string

①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) string converted to date

Assuming the form of a string is Yyyymmddhhmiss form

①informix

--Convert directly to date

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

--the first day of the month

Let v_date = substr (v_begintime,1,4) | | -'

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

--the first day of the week

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

--Convert directly to date

V_begindate: = To_date (V_begintime, ' Yyyymmddhh24miss ');

--the first day of the month

V_begindate: = Trunc (To_date (v_begintime, ' Yyyymmddhh24miss '), ' mm ');

--the first day of the week

V_begindate: = Trunc (To_date (v_begintime, ' Yyyymmddhh24miss '), ' Day ');

5) Business

By default in Oracle, the end of a transaction is the beginning of the next transaction, so for a transaction we just write a commit, and you don't have to explicitly mark when to start a transaction, and Informix needs it.

6) Print debugging information

7) Informix

--Set Trace mode

Set debug file to "Trace_check"; --with Append;

--description "with append" means open trace result file in Append mode

Trace ' starts executing stored procedures '

Trace ' v_date= ' | | V_date;

Trace ' Stored procedure completed '

Trace off;

When you are done, open the Trace_check in the current directory to see the printed information.

8) Oracle

Dbms_output. Put_Line (' Start execution of stored procedures ');

Dbms_output. Put_Line (' v_date= ' | | V_date);

Dbms_output. Put_Line (' Stored procedure execution completed ');

Set the size of the buffer first

Set serveroutput on size 100000; --If you do not execute the statement, you will not see the debug information

After the execution, the printed information will be displayed directly on the interface.

5, about the parameters of the description

If the stored procedure wants to return a parameter, it is implemented in Informix by the form of a return value, which is implemented by an output parameter or an input or output parameter in Oracle.

Example:

1) Informix:

CREATE PROCEDURE P1 () returning integer;

return 0;

End procedure;

2) Oracle:

Create or Replace procedure P1 (x out number)

As

Begin

x: = 0;

End P1;

6. Assigning value

1) Informix

Let v_1 = 100;

2) Oracle

V_1: = 100;

7, if statement

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;