C#调用oracle存储过程

 

一、Oracle方面

１．创建Oracle过程存储

1)   创建表

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

2)   创建包

CREATEOR REPLACE PACKAGE MultiRefCursorsAS

  TYPE test_cursorISREF CURSOR ;

  Procedure getRecord (p_cursorINOUT test_cursor);

END MultiRefCursors;

/

 

3)  创建存储过程

createor replace package body MultiRefCursorsas

procedure getRecord( p_cursorINOUT test_cursor)is

v_sqlvarchar2(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();
        }

    }

}