The cursor returned by the stored procedure in the Oracle package

I. Role of Oracle package

1. Definition and description
A. encapsulation of related objects (custom types, functions, stored procedures, etc.)
B.ProgramEach part of the package
-Package type description
Declaration subroutine
-Package subject
Define subroutine

2. advantages of using the package
-Modularization
-Easier Application Design
-Information Hiding
-New Features
-Better performance

3. Differences between public and private items
Public items: variables, processes, and functions defined in the description section of the package
Private items: variables, processes, and functions defined in the main part of the package

Public Private items
References outside the package cannot be referenced outside the package.
Is defined in the package specification description in the package body.
For global purposes for local purposes

 

Ii. define a package

Used to define the user-defined type, function, and stored procedure used in a package...

Example:

Create or replace package pkg_sys_search is
-- Define the returned cursor
Type vld_result is ref cursor;
-- Stored procedure with a return type of cursor variable
Procedure get_search_result (search_condition in varchar2, return_val out vld_result );
End pkg_sys_search;

 

3. Complete Package body

The specific implementation of custom types, functions, stored procedures, and other objects in the package.

Example:

Create or replace package body pkg_sys_search
As
Procedure get_search_result (search_condition in varchar2, return_val out vld_result)
As
Begin
-- Set the return value
Open return_val
Select * From sys_param where type = search_condition;
End;
End pkg_sys_search;

 

Note: The parameter name in get_search_result defined in the package body must be consistent with get_search_result in the package.

 

Iv. Call this process using ado.net

Using system. Data. oracleclient

 

Oracleconnection conn = new oracleconnection ("Data Source = oracledb; user id = userid; Password = password ;");
// Create the command for the stored procedure
Oraclecommand cmd = new oraclecommand ();
Cmd. Connection = conn;
Cmd. commandtext = "pkg_sys_search.get_search_result ";
Cmd. commandtype = commandtype. storedprocedure;
// Add the parameters for the stored procedure including the ref cursor
// To retrieve the result set
Cmd. Parameters. Add ("search_condition", oracletype. varchar). value = "aduIt ";
Cmd. Parameters. Add ("return_val", oracletype. cursor). Direction = parameterdirection. output;
// Open the connection and create the datareader
Conn. open ();
Oracledatareader DR = cmd. executereader ();
// Return with datareader

While (dr. Read ())
{
For (INT I = 0; I <dr. fieldcount; I ++)
Console. Write (Dr [I]. tostring () + ";");
Console. writeline ();
}

 

// Return with Dataset
System. Data. oracleclient. oracledataadapter da = new system. Data. oracleclient. oracledataadapter (CMD );
Dataset DS = new dataset ();
Da. Fill (DS, "test ");

Conn. Close ();