oracle ref cursor

 ----建立测试数据

   CREATE TABLE DEPT
   (DEPTNO NUMBER(2,0) NOT NULL,
   DNAME VARCHAR2(14) NULL,
   LOC VARCHAR2(13) NULL,
   PRIMARY KEY (DEPTNO)
   );

   INSERT INTO Dept VALUES(11,'Sales','Texas');
   INSERT INTO Dept VALUES(22,'Accounting','Washington');
   INSERT INTO Dept VALUES(33,'Finance','Maine');

   CREATE TABLE EMP
   (EMPNO NUMBER(4,0) NOT NULL,
   ENAME VARCHAR2(10) NULL,
   JOB VARCHAR2(9) NULL,
   MGR NUMBER(4,0) NULL,
   SAL NUMBER(7,2) NULL,
   COMM NUMBER(7,2) NULL,
   DEPTNO NUMBER(2,0) NULL,
   FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO), 
   PRIMARY KEY (EMPNO)
   );

   INSERT INTO Emp VALUES(123,'Bob','Sales',555,35000,12,11);
   INSERT INTO Emp VALUES(321,'Sue','Finance',555,42000,12,33);
   INSERT INTO Emp VALUES(234,'Mary','Account',555,33000,12,22);

----建立包已经存储过程

    CREATE OR REPLACE PACKAGE curspkg_join AS
    TYPE t_cursor IS REF CURSOR ;
    Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor OUT t_cursor);
   END curspkg_join;
CREATE OR REPLACE PACKAGE BODY curspkg_join AS
   Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor OUT t_cursor)
   IS
    v_cursor t_cursor;
   BEGIN
    IF n_EMPNO <> 0
    THEN
     OPEN v_cursor FOR
     SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME
      FROM EMP, DEPT
      WHERE EMP.DEPTNO = DEPT.DEPTNO
      AND EMP.EMPNO = n_EMPNO;

    ELSE
     OPEN v_cursor FOR
     SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME
      FROM EMP, DEPT
      WHERE EMP.DEPTNO = DEPT.DEPTNO;

    END IF;
    io_cursor := v_cursor;
   END open_join_cursor1;
   END curspkg_join;

 

 

 

----程序调用

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;
using System.Data.OracleClient;

public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        getConn();
    }
    protected void getConn()
    {
         //OracleConnection conn=new OracleConnection("DataSource=primary;User Id=gkl;Password=gkl;");
        OracleConnection conn = new OracleConnection("Data Source=PRIMARY;User Id=gkl;Password=gkl;");
        conn.Open();
         OracleCommand cmd = new OracleCommand();
         cmd.Connection = conn;
         cmd.CommandText = "curspkg_join.open_join_cursor1";

         cmd.Parameters.Add("n_EMPNO", OracleType.Number,4).Direction =
                 ParameterDirection.Input;
         cmd.Parameters[0].Value = 0;
         cmd.Parameters.Add("io_cursor", OracleType.Cursor).Direction =
           ParameterDirection.Output;
         cmd.CommandType = CommandType.StoredProcedure;

         //   create   the   DataAdapter   and   map   tables  
         OracleDataAdapter da = new OracleDataAdapter(cmd);

         //   create   and   fill   the   DataSet  
         DataSet ds = new DataSet();
         da.Fill(ds);

         //   create   a   relation  
         GridView1.DataSource = ds;
         GridView1.DataBind();
    }
}