Shulou(Shulou.com)06/01 Report--

1.-- Oracle creates a package to declare custom cursor types

Create or replace package pk_Var is

Type csr_tp is ref cursor

End

2.-- Oracle creates a stored procedure to return a data set

CREATE OR REPLACE PROCEDURE pr_getdata (

V_empno VARCHAR2, RC1 out PK_Var.csr_tp)

AS

SSQL varchar2 (2000)

Begin

SSQL: = 'select * from emp where empno=v_empno'

Open RC1 for sSQL

Return

End pr_getdata

3. The c # code is as follows:

/ / create oracle parameters

OracleParameter [] sqlParme = new OracleParameter [2]

/ / Parameter 1

SqlParme [0] = new OracleParameter ("RC1", OracleType.Cursor)

SqlParme [0] .Direction = ParameterDirection.Output

SqlParme [1] = new OracleParameter ("TKALA", OracleType.VarChar)

SqlParme [1] .Direction = ParameterDirection.Input

SqlParme [1] .value = aHoleOldNum

/ / get dataTable data sources from stored procedures

Private DataTable DBExecStoredProcedure (string storeureName, OracleParameter [] sqlParme)

{

Try

{

/ / use Microsoft's ORACLE access interface

If (DBHelper.G_pDBConn.State = = ConnectionState.Closed) / / get a data connection

DBHelper.G_pDBConn.Open ()

OracleCommand oraCmd = new OracleCommand (storeureName, DBHelper.G_pDBConn)

OraCmd.CommandType = CommandType.StoredProcedure

OraCmd.Parameters.Clear (); / / empty first

Foreach (OracleParameter parme in sqlParme)

{

OraCmd.Parameters.Add (parme)

}

DataTable table = new DataTable ()

DateTime BegTime = System.DateTime.Now

OracleDataAdapter da1 = new OracleDataAdapter (oraCmd); / / fetch data

Da1.Fill (table)

Return table

}

Catch (Exception ex)

{

MessageBox.Show ("failed to execute stored procedure:" + ex.Message, "debug")

Return null

}

Finally {

DBHelper.G_pDBConn.Close ()

}

}

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.