Oracle proceduce return dataset Summary

To obtain a data set from Oracle procedure, the ref cursor method is usually used. To obtain this cursor, there are several ways:

1. dynamic SQL return:

In this case, procedure operations are usually relatively simple. For example, you can use a combination of SQL statements or join operations on multiple tables, but you can use one SQL statement to complete the query.

Create   Or   Replace   Procedure Sp_getcurrentstockpallet (
Startdate date,
Enddate date,
Status Char ,
Material_no Varchar2 ,
Pallet_id Varchar2 ,
Box_id Varchar2 ,
Plant Varchar2 ,
Stloc Varchar2 ,
Customer Varchar2 ,
Creator Varchar2 ,
Mat_doc Varchar2 ,
Box_count Number ,
Result out sys_refcursor
)
Is
V_ SQL Varchar2 ( 1000 );
Begin
V_ SQL: =
' Select B. Status, B. pallet_id, B. wm_pallet_id, count (B. box_id) box_count, sum (B. glass_qty) total_qty, B. Unit, B. material_no,
B. Grade, A. Plant, A. stloc, A. Area, A. Bin, B. Customer, B. product_type, B. create_time, B. remark
From sd_current_pallet A, sd_current_box B
Where a. pallet_id = B. pallet_id ' ;
If Material_no Is   Not   Null
Then
V_ SQL: = V_ SQL |   ' And B. material_no = '   |   ''''   | Material_no |   '''' ;
End   If ;
V_ SQL: =
V_ SQL
|   ' Group by B. Status, B. pallet_id, B. wm_pallet_id, B. Unit, B. material_no,
B. Grade, A. Plant, A. stloc, A. Area, A. Bin, B. Customer, B. product_type, B. create_time, B. remark ' ;
Open Result For V_ SQL;
Exception
When No_data_found
Then
Null ;
When Others
Then
Raise;
End Sp_getcurrentstockpallet;

2. If the procedure logic operation is complicated, it cannot be completed in one SQL statement. In general, intermediate calculation data needs to be temporarily stored in the operation process.
In this case, you can use the following methods:
1) Use a nested table to dynamically generate a dataset and use the table () function to return the dataset. In this way, you need to create an object in the database and create a nested table. The type defined in the local scope cannot be recognized.

Create an object.

Create   Or   Replace Type stockpallet_type As Object
(
Status Char ( 1 ),
Material_no Varchar2 ( 20 ),
Pallet_id Varchar2 ( 30 ),
Box_id Varchar2 ( 30 ),
Plant Varchar2 ( 4 ),
Stloc Varchar2 ( 4 ),
Customer Varchar2 ( 10 ),
Creator Varchar2 ( 10 ),
Mat_doc Varchar2 ( 20 ),
Box_count Number ( 10 )
)

Create a nested table with the object stockpallet_type created above

Create   Or   Replace Type t_stockpallet_nest As   Table   Of Stockpallet_type;

Create procedure. The output type is sys_refcursor.

Create   Or   Replace   Procedure Sp_getpalletbynesttable_v2 (
Result out sys_refcursor
)
As
Cursor C_box
Is
Select Material_no, pallet_id, box_id
From Sd_current_box
Where Rownum <   10 ;
-- Initialize nested tables
Box_array t_stockpallet_nest: = T_stockpallet_nest ();
I Number : =   0 ;
-- V_ SQL _return varchar2 (200 );
Begin
For Curbox In C_box
Loop
I: = I +   1 ;
Box_array.extend; -- Use the extend method to declare that an element is added at the end of the set.
Box_array (I ): =
Stockpallet_type ( ' 0 ' ,
Curbox. material_no,
Curbox. pallet_id,
Curbox. box_id,
' T001 ' ,
' 0210 ' ,
' HP ' ,
' Ivav ' ,
' 5000 ' ,
10
); -- Instantiate an object and assign it to a nested table
End Loop;
-- V_ SQL _return: = 'select * from table (box_array) '; -- note that this does not work.
Open Result For   Select   * From   Table (Box_array ); -- Table () function returns
Exception
When Others
Then
Null ;
End ;

2) Another common method is to use temporary tables.

You can use the dynamic creation method (Note: I checked some materials and tried to avoid dynamic creation and deletion of temporary tables in procedure, but there is one situation, the number of columns in a temporary table is variable and must be dynamically generated based on the logic. Which of the following sets is more reliable in this case ?), You can also create temporary tables for backup before creating procedure (note the difference from creating a common table? Data in the temporary table is stored in the current session <session-level temporary table>/transaction <transaction-level temporary table> ). Note that the DDL statement must be executed using the execute immediate statement.
If the "permission is insufficient" error occurs, consider the following two methods:
1. Grant create any table to Username
2. in the Oracle stored procedure, if you want to operate objects in different schemas, you can write the same procedure in different schemas, however, maintenance and synchronization are troublesome. You can add authid CURRENT_USER to procedure, the operation object in procedure is the object of the currently connected user, but not the object under the user to which procedure belongs.

1. Create the structure of the global temporary table once, outside of PL/SQL.
2. utilize that table in your procedures. The contents of the table will be local to your session and will automatically disappear when you either log out or commit, depending on how the table is configured.

Multiple sessions can use the same GTT at the same time, but they will not be able to see or interact with each others data. They will also not block each other for any action against that table

Create   Or   Replace   Procedure Sp_getpalletbytemptable_v1 (
Result out sys_refcursor
)
Authid CURRENT_USER  
Is
Tb_count Int ;
V_ SQL Varchar2 ( 300 );
Begin
V_ SQL: =   ' Drop table box_tmp_table ' ;
Execute Immediate v_ SQL;
V_ SQL: =
' Create global temporary table box_tmp_table
(
Box_id varchar2 (30 ),
Pallet_id varchar2 (30 ),
Material_no varchar2 (30)
) On commit preserve rows ' ;
Execute Immediate v_ SQL;
V_ SQL: =
' Insert into box_tmp_table
(Select box_id, pallet_id, material_no from sd_current_box where rownum <10) ' ;
Execute Immediate v_ SQL;
Commit ;
V_ SQL: =   ' Select * From box_tmp_table ' ;
Open Result For V_ SQL;
Exception
When No_data_found
Then
Null ;
When Others
Then
-- Consider logging the error and then re-raise
Raise;
End Sp_getpalletbytemptable_v1;