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

最新推荐文章于 2021-04-15 20:56:49 发布

原创最新推荐文章于 2021-04-15 20:56:49 发布 · 1.3k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#ORACLE #存储过程 #c#

Oracle 专栏收录该内容

3 篇文章

订阅专栏

本文介绍如何在Oracle中创建存储过程，并通过VS2010使用System.Data.OracleClient包调用这些存储过程。具体步骤包括定义存储过程、建立数据库连接、调用存储过程并获取结果。

一、在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();
                }
            }
        }
    }
}