C#調用oracle預存程序

一、Oracle方面

１．建立Oracle過程儲存

1)   建立表

create table users(usesNo number,userName varchar(50));

2)   建立包

CREATE OR REPLACE PACKAGE MultiRefCursors AS

   TYPE test_cursor IS REF CURSOR ;

   Procedure getRecord (p_cursor IN OUT test_cursor);

END MultiRefCursors;

/

 

3)   建立預存程序

create or replace package body MultiRefCursors as

procedure getRecord( p_cursor IN OUT test_cursor) is

v_sql varchar2(1000);

begin

 v_sql:='select * from users';

 open p_cursor for v_sql;

end;

end;

/

4)   添加資料

insert into users values(1,'ljp');

insert into users values(2,'dfa');

insert into users values(3,'dff');

insert into users values(1,'ljp');

insert into users values(2,'dfa');

insert into users values(3,'dff');

 

 二、  C#方面

 

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data;

using System.Data.OracleClient;

 

namespace ConsoleApplication1

{

    class Program

    {

        static void Main(string[] args)

        {

OracleConnection conn = new OracleConnection(@"Data Source=orcl;User ID=scott;Password=tiger;Unicode=True");

            conn.Open();

          

            OracleCommand cmd = new OracleCommand();

            cmd.Connection=conn;

            cmd.CommandType = CommandType.StoredProcedure;

 

            //調用預存程序查詢資料

            cmd.CommandText = "MultiRefCursors.getRecord";

            OracleParameter[] parameters = new OracleParameter[2];

//注意這裡的userNo,p_cursor參數名和類型號與預存程序裡面的一樣

            parameters[0] = new OracleParameter("userNo", OracleType.Int32);

            parameters[1] = new OracleParameter("p_cursor", OracleType.Cursor);

            parameters[0].Value = 3;

            parameters[0].Direction = ParameterDirection.Input;

            parameters[1].Direction = ParameterDirection.Output;

 

            foreach (OracleParameter parameter in parameters)

            {

                cmd.Parameters.Add(parameter);

            }

 

            OracleDataAdapter oda = new OracleDataAdapter(cmd);

            DataSet ds = new DataSet();

 

            try

            {

                oda.Fill(ds);

                if (ds.Tables[0] != null)

                {

                    foreach (DataRow dr in ds.Tables[0].Rows)

                    {

                        System.Console.WriteLine("userno:" + dr[0] + " username:" + dr[1]);

                    }

                }

            }

            catch (Exception ex)

            {

                System.Console.WriteLine(ex.Message);

            }

            finally

            {

                conn.Close();

            }

            System.Console.ReadKey();
        }

    }

}