C#调用ORCLe 带参数select存储过程--10g及以下

  一、在oracle中建立表

二建立存储过程

create or replace package stupkg   ----stupkg是包的名称
    is
        type mycursor is ref cursor;     -------定义一个名为mycursor的类型
        procedure getstudentinfo(id1 in number, cur_mycursor out mycursor);  
    end;  ----包规范中包括定义的一个mycursor类型和存储过程getstudentinfo
 




create or replace package body stupkg
    is
      procedure getstudentinfo(id1 in number, cur_mycursor out mycursor)
      is
      begin
           open cur_mycursor for select * from AUTHOR WHERE AUTHOR.id = id1;
      end getstudentinfo;
    end stupkg;

三、在VS2010调用

 1、using System.Data.OracleClient; //如何引用 该过时包 可以将.NET框架改为4  见上篇文章

 2、建立连接字符串

   string CONN_STR = "Data Source=(DESCRIPTION="
                + "(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=117.131.244.186)(PORT=1521)))"
                + "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=AANJSC)));"
                + "User Id=XAAXHQ;Password=XAAHQ;";

附代码

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OracleClient;
//using Oracle.DataAccess.Client;

using System.Data;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            string CONN_STR = "Data Source=(DESCRIPTION="
                + "(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=147.239.244.186)(PORT=1521)))"
                + "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=NSSSJSC)));"
                + "User Id=XXFFHQ;Password=FFF;";
            if (string.IsNullOrEmpty(CONN_STR))//指示指定的字符串是 null 还是 Empty 字符串。
            {
                throw new Exception("数据库访问服务出现故障，请联系xx！");
            }
            else
            {
                OracleConnection conn = null;
                try
                {
                    conn = new OracleConnection(CONN_STR);
                    conn.Open();
                }
                catch (Exception e)
                {
                    Console.WriteLine("{0}second exception caught", e);
                    Console.ReadLine();
                }
                Console.WriteLine("连接成功");
                Console.ReadLine();


                OracleDataAdapter oda = new OracleDataAdapter("select * from author",conn);
                DataSet ds = new DataSet();
                oda.Fill(ds);
                 DataTable dt = ds.Tables[0];






                  //调用存储过程

                 OracleCommand orclcomm = new OracleCommand("stupkg.getstudentinfo", conn);
                 orclcomm.CommandType = CommandType.StoredProcedure;
                 OracleParameter paramentIn = new OracleParameter("id1", OracleType.Int32);//输入参数
                 paramentIn.Value = 1;
                 paramentIn.Direction = ParameterDirection.Input;

                 OracleParameter parament = new OracleParameter("cur_mycursor", OracleType.Cursor);//输出游标参数
                 parament.Direction = ParameterDirection.Output;

                 orclcomm.Parameters.Add(paramentIn);
                 orclcomm.Parameters.Add(parament);

                 OracleDataAdapter oda2 = new OracleDataAdapter(orclcomm);
                 DataSet ds2 = new DataSet();

                 oda2.Fill(ds2);
           




                if (null != conn)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
        }
    }
}