(转)用.NET调用Oracle存储过程返回记录集

oracle的存储过程返回记录集，关键之处是要用游标。关于数据库的游标(cursor)大家肯定都接触不少，我们可以通过OPEN,FETCH,CLOSE操作控制游标进行各种方便的操作，这方面的例子我就不在重复了。

　　我们现在要介绍的是游标变量(cursor variable)。类似游标，游标变量也是指向一个查询结果集的当前行。不同的是，游标变量能为任何类型相似(type-compatible)的查询打开，而并不是绑定到某一个特定的查询。通过游标变量，你可以在数据库的数据提取中获得更多的方便。

　　首先是建立表：

 

CREATE TABLE LIHUAN.BILL_POINTS
(
POINTS_ID NUMBER(10,0) NOT NULL,
CUSTOMER_ID NUMBER(10,0) NOT NULL,
BILL_POINT_NO NUMBER(2,0) DEFAULT 1 NOT NULL,
CONSTRAINT PK_BILL_POINTS PRIMARY KEY (POINTS_ID)
)
/

 

其次，建PACKAGE

 

CREATE OR REPLACE PACKAGE LIHUAN.YY_PKG_BILL_POINT_NO/**//*取得用户的所有计费电序号*/
IS
TYPE T_CURSOR IS REF CURSOR;
PROCEDURE BILL_POINT_NO(P_CUSTOMER_ID BILL_POINTS.CUSTOMER_ID%TYPE,
Re_CURSOR OUT T_CURSOR);
END;
/

 

再次，建PACKAGE BODY

 

CREATE OR REPLACE PACKAGE BODY LIHUAN.YY_PKG_BILL_POINT_NO/**//*取得用户的所有计费电序号*/
IS
PROCEDURE BILL_POINT_NO(P_CUSTOMER_ID BILL_POINTS.CUSTOMER_ID%TYPE,
Re_CURSOR OUT T_CURSOR)
IS
V_CURSOR T_CURSOR;
BEGIN
　OPEN V_CURSOR FOR
select Bill_Point_No from BILL_POINTS where CUSTOMER_ID =P_CUSTOMER_ID;
Re_CURSOR := V_CURSOR;
END;
END;
/

最后，在.NET中程序调用。

 public bool RunProcedure(string ReturnParameter,OracleType ParamType, ref DataSet Dataset, Hashtable HT,
string ProcedureName, string OracleConnection)
        {
            OracleConnection dsconnection = new System.Data.OracleClient.OracleConnection(OracleConnection);
            OracleCommand dacommand =new OracleCommand(ProcedureName, dsconnection);
            dsconnection.Open();
            dacommand.CommandType = CommandType.StoredProcedure;
            IDictionaryEnumerator Enumerator;
            Enumerator = HT.GetEnumerator();
            object Value = null;
            OracleParameter OracleParam;
            OracleParam = dacommand.Parameters.
            Add(new OracleParameter(ReturnParameter, ParamType));
            OracleParam.Direction = ParameterDirection.Output;
            while (Enumerator.MoveNext())
            {
                Value = Enumerator.Value;
                OracleParam = dacommand.Parameters.
                Add(new OracleParameter
                (Enumerator.Key.ToString(), Value));
            }
            OracleDataAdapter ODAdapter = new OracleDataAdapter(dacommand);
            try
            {
                ODAdapter.Fill(Dataset);
                return true;
            }
            catch (System.Exception e)
            {
                e.ToString();
                return false;
            }
            finally
            {
                HT.Clear();
                dacommand.Parameters.Clear();
                dsconnection.Close();
            }
        }

 

 public DataSet Bill_Point_No(string CUSTOMER_ID)//ok
        {
            DataSet dataset = new DataSet();
            Hashtable HT = new Hashtable();
            HT.Add("P_CUSTOMER_ID", CUSTOMER_ID);
            if (RunProcedure("Re_CURSOR", OracleType.Cursor, ref dataset,HT, "YY_PKG_BILL_POINT_NO.BILL_POINT_NO",bmsOracleConnectionString))
            { ;}
            else
            {
                dataset = null;
            }
            return dataset;
        }

 

string bmsOracleConnectionString = ConfigurationManager.ConnectionStrings["db"].ConnectionString;
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                OracleConnection conn = new OracleConnection(bmsOracleConnectionString);
                conn.Open();
                DataSet ds = new DataSet();
                ds=Bill_Point_No("1");
                GridView1.DataSource = ds.Tables[0].DefaultView;
                GridView1.DataBind();
                conn.Close();                
            }
        }