Dynamic Oracle cursor implementation

During the development process, Oracle cursors are often used for statistics (no temporary tables are required) and their statistical results are returned. However, the SQL statements behind the cursor are often dynamic, for example, select * from tablename where? Order ?. "? "Represents a condition. How can this problem be solved?

I practiced it with the help of my colleagues. Summary.

Assume that the tablename table has the following fields:

 
 

  
  
field1 varchar2(50)   
  
  
field2 Varchar2(50)   
  
  
field3 Varchar(50)   
  
  
field4 varchar2(50)   
  
  
field5 varchar2(20)   
  
  
field6 float,   
  
  
field7 float   
 
 

1. Define a cursor

 
 

  
  
create or replace package RefCursor is   
  
  
-- Author : Ricky   
  
  
-- Created : 2003-9-1 14:08:45   
  
  
-- Purpose :   
  
  
-- Public type declarations   
  
  
type t_RefCursor is ref cursor;   
  
  
end RefCursor; 
 
 

2. Creation type

The created type is the same as that of the table fields in tablename. Of course, it depends on whether you actually want to count all fields.

 
 

  
  
create or replace type TableType as object   
  
  
 (   
  
  
-- Author : Ricky   
  
  
-- Created : 2003-8-25 9:12:08   
  
  
-- Purpose :   
  
  
-- Attributes   
  
  
field1 varchar2(50),   
  
  
field2 Varchar2(50),   
  
  
field3 Varchar(50),   
  
  
field4 varchar2(50),   
  
  
field5 varchar2(20),   
  
  
field6 float,   
  
  
field7 float   
  
  
 );  
 
 

3. Create a table Type

 
 

  
  
create or replace type TableTypeList as table of TableType;  
 
 

4. It is used in stored procedures or functions and is used in functions below (a table structure cannot be returned in Stored Procedures and temporary tables must be used)

 
 

  
  
CREATE OR REPLACE FUNCTION "TEST" (   
  
  
return TableTypeList pipelined as   
  
  
begin   
  
  
v_Cur RefCursor.t_Refcursor;   
  
  
v_SQLStatement string(10000);   
  
  
v_Table tablename%rowtype;   
  
  
tmp1 tablename.field1%Type;   
  
  
tmp2 tablename.field2%Type;   
  
  
tmp3 tablename.field3%Type;   
  
  
tmp4 tablename.field4%Type;   
  
  
tmp5 tablename.field5%Type;   
  
  
tmp6 tablename.field6%Type;   
  
  
tmp7 tablename.field6%Type;   
  
  
v_SQLStatement := 'Select * From tablename where field1='1' order by field1';   
  
  
open v_Cur for v_SQLStatement;   
  
  
loop   
 
 

Here is the loop Process
 

 
 

  
  
fetch v_Cur into v_Comm;   
  
  
exit when v_CommCur%notfound;  
 
 

Here is the statistical process you want to process. I didn't make any statistics during the intermediate process. You should add them as needed in practice.
 

 
 

  
  
field1 = v_Cur.field1;   
  
  
field2 = v_Cur.field2;   
  
  
field3 = v_Cur.field3;   
  
  
field4 = v_Cur.field4;   
  
  
field5 = v_Cur.field5;   
  
  
field6 = v_Cur.field6;   
  
  
field7 = v_Cur.field7;   
  
  
v_Table = TableType(field1,   
  
  
field2,   
  
  
field3,   
  
  
field4,   
  
  
field5,   
  
  
field6,   
  
  
field7)   
  
  
pipe row(v_Table);   
  
  
end loop   
  
  
end;  
 
 

The above content is an introduction to the implementation of the dynamic form of Oracle cursor. I hope you will have some gains.