Two methods for creating a stored procedure in oracle

The SQL Server syntax is used to write the oracle process during oracle storage. Therefore, errors may occur. For other differences, see the following example.

When using the SQL server syntax to write oracle stored procedures, errors may occur. The other differences are as follows.

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.
*/