Use dynamic SQL for Oracle stored procedures

I believe everyone knows about the Oracle stored procedure. The following describes how to use dynamic SQL in the Oracle stored procedure. I hope this will help you.

There are two ways to use dynamic SQL for Oracle stored procedures: DBMS_ SQL or execute immediate. We recommend that you use the latter. The test procedure is as follows:

1. DDL and DML

 
 

  
  
/*** DDL ***/
  
  
Begin
  
  
Execute immediate 'drop table temp_1 ';
  
  
Execute immediate 'create table temp_1 (name varchar2 (8 ))';
  
  
End;
  
  
 
  
  
/*** DML ***/
  
  
Declare
  
  
V_1 varchar2 (8 );
  
  
V_2 varchar2 (10 );
  
  
Str varchar2 (50 );
  
  
Begin
  
  
V_1: = 'tester ';
  
  
V_2: = 'beijing ';
  
  
Str: = 'insert INTO test (name, address) VALUES (: 1,: 2 )';
  
  
Execute immediate str USING v_1, v_2;
  
  
Commit;
  
  
End;
 
 

2. Return a single result

 
 

  
  
Declare
  
  
Str varchar2 (500 );
  
  
C_1 varchar2 (10 );
  
  
R_1 test % rowtype;
  
  
Begin
  
  
C_1: = 'tester ';
  
  
Str: = 'select * from test where name =: c where rownum = 1 ';
  
  
Execute immediate str into r_1 using c_1;
  
  
DBMS_OUTPUT.PUT_LINE (R_1.NAME | R_1.ADDRESS );
  
  
End;
 
 

3. Returned result set

 
 

  
  
Create or replace package pkg_test
  
  
/* Define the ref cursor type
  
  
The return type is not added. The return type is weak. dynamic SQL queries are allowed,
  
  
Otherwise, it is strongly typed and cannot be queried using dynamic SQL;
  
  
*/
  
  
Type myrctype is ref cursor;
  
  
 
  
  
-- Function declaration
  
  
Function get (intID number) return myrctype;
  
  
End pkg_test;
  
  
/
  
  
 
  
  
Create or replace package body pkg_test
  
  
-- Function body
  
  
Function get (intID number) return myrctype is
  
  
Rc myrctype; -- defines the ref cursor variable
  
  
Sqlstr varchar2 (500 );
  
  
Begin
  
  
If intID = 0 then
  
  
-- The static test directly returns the result using the select statement.
  
  
Open rc for select id, name, sex, address, postcode, birthday from
  
  
Student;
  
  
Else
  
  
-- Assign a value to a dynamic SQL statement. Use w_id to declare that the variable is obtained from the outside.
  
  
Sqlstr: = 'select id, name, sex, address, postcode, birthday from student
  
  
Where id =: w_id ';
  
  
-- Dynamic Test: return results using sqlstr strings and PASS Parameters using keywords
  
  
Open rc for sqlstr using intid;
  
  
End if;
  
  
 
  
  
Return rc;
  
  
End get;
  
  
 
  
  
End pkg_test;
  
  
/
 
 

Implementation of Oracle fuzzy query

Oracle Stored Procedure debugging method

Learn about the physical structure of Oracle

How to delete ORACLE archive logs

Oracle mobile redo log file