Oracle stored procedure returns result set

Haven't come up for a long time, do not have a little idle work today, studied the Oracle stored procedure returns the result set.

With Oracle staging tables, the use of stored procedures to return data read from a result set can solve the problem of connection between a massive data table and other tables. In the stored procedure, according to the filter criteria from the mass data table to select the matching records and put into the temporary, you can use a view to connect the temporary table with other related tables, so as to avoid the large data caused by the connection efficiency problem.

This article only discusses using stored procedures to return result sets.

The specific implementation is as follows:

--Enable server output
---------------------
Set Serveroutput on

--Create a test table
---------------------
CREATE TABLE Test_pkg_test
(
ID number (TEN) constraint Pk_test_pkg_test primary key,
Name VARCHAR2 (30)
);

--Write test data
---------------------
begin
INSERT INTO test_pkg_test (ID) values (1);
INSERT into test_pkg_test (ID) values (2);
INSERT into test_pkg_test (ID) values (3);
INSERT into test_pkg_test (ID) values (4);
INSERT into test_pkg_test (ID) values (5);
INSERT into test_pkg_test (ID) values (6);
INSERT into test_pkg_test (ID) values (7);
INSERT into test_pkg_test (ID) values (8);
INSERT into test_pkg_test (ID) values (9);
INSERT into test_pkg_test (ID) values (10);
INSERT into test_pkg_test (ID) values (11);
INSERT into test_pkg_test (ID) values (12);
INSERT into test_pkg_test (ID) values (13);
INSERT into test_pkg_test (ID) values (14);
INSERT into test_pkg_test (ID) values (15);
INSERT into test_pkg_test (ID) values (16);
INSERT into test_pkg_test (ID) values (17);
INSERT into test_pkg_test (ID) values (18);
End;
/
Update test_pkg_test set name= ' name of ' | | to_char (ID);
commit;

--declaration Package
---------------------
Create or Replace package Pkg_test
As
Type type_cursor is REF CURSOR;
Procedure Read_rows (header varchar2, result out type_cursor);
End Pkg_test;
/

--Implementing the package
---------------------
Create or replace package body Pkg_test
As
Procedure Read_rows (header varchar2, result out type_cursor)
Is
SQLText VARCHAR2 (500);
Begin
If header is a null or length (header) =0 Then
SQLText: = ' select * from Test_pkg_test ';
Else
SQLText: = ' select * from Test_pkg_test where substr (name,1, ' | | to_char (length header) | | ') = ' ' | | header | | ‘‘‘‘;
End If;
--dbms_output.put_line (SQLTEXT);
Open result for SQLText;
End Read_rows;
End Pkg_test;
/

--Test in Sqlplus
---------------------
var result refcursor
EXEC pkg_test.read_rows (null,:result);
Print result
exec pkg_test.read_rows (' Name of 1 ',: result);
print result;

--Test in the program (c#.net)
-- ***************************************
Static Class Pkg_test
{
public static void Test ()
{
using (OracleConnection conn = new OracleConnection ())
{
Conn. ConnectionString = "Data source=mydb; User Id=myuser; Password=mypassword ";
Conn. Open ();

using (oraclecommand cmd = new OracleCommand ("Pkg_test.read_rows", conn))
{
Cmd.commandtype = System.Data.CommandType.StoredProcedure;
OracleParameter p = new OracleParameter ("header", Oracletype.varchar);
P.value = "Name of 1";
P.value = DBNull.Value;
Cmd. Parameters.Add (P);

p = new OracleParameter ("result", oracletype.cursor);
P.direction = System.Data.ParameterDirection.Output;
Cmd. Parameters.Add (P);

                     OracleDataReader reader = cmd. ExecuteReader ();
                     while (reader. Read ())
                     {
                         Console.WriteLine ("{0}\t{1}", Reader. GetValue (0), reader. GetValue (1));
                    }
               }
           }
       }

-- ***************************************

--deleting packages and test tables
---------------------
Drop Package pkg_test;
drop table test_pkg_test;

Oracle stored procedure returns result set