When using the SQL server syntax to write oracle stored procedures, errors may occur. The other differences are as follows.
Two methods for creating a stored procedure in oracle
Create or replace procedure getdefault is
Begin
Execute immediate 'Create global temporary table deftemp (pid varchar2 (5) on commit delete rows ';
Select * from deftemp;
End
// Method 2
/*
Next let's take a look
1: create a temporary table
2: Insert a result set to the temporary table
3: return the result set of the temporary table
Simple instances created in oracle stored procedures
*/
Declare
Create_str varchar2 (100 );
Dept_row dept % rowtype;
Begin
Create_str: = 'Create global temporary table temp on commit preserve rows as select * from dept ';
Execute immediate create_str;
Execute immediate 'select * from temp where rownum = 1' into dept_row;
Dbms_output.put_line (dept_row.deptno | '+' | dept_row.dname | '+' | dept_row.loc );
End;
/*
When using the SQL server syntax to write oracle stored procedures, errors may occur. The other differences are as follows.
1. Each statement is used after the end;
2. DDL needs to be executed using execute immediate dynamic SQL
3. The stored procedure does not support direct select. A cursor is required.
*/