Oracle creates stored procedures in two ways

Oracle creates stored procedures in two ways

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 Two
/*
Let's see a
1: Create a temporary table
2: Insert result set into temporary table
3: Return the result set of the temporary table
Simple instance of Oracle stored procedure creation
*/

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

/*

1, each statement after the end of use;
2, the DDL needs to use execute immediate dynamic SQL execution
3, the stored procedure does not support direct Select, need to use a cursor
*/