oracle 存储过程返回结果集 table形式 (使用sys_refcursor 及程序包package 两种方式)

1.创建一个表Test用来测试.

1 CREATE TABLE  "TEST" 
2    (    "AAA" NUMBER(*,0), 
3     "BBB" VARCHAR2(10 BYTE)
4    )

2.向Test表中插入测试数据

1 insert into Test values(1,'a');
2 insert into Test values(2,'b');
3 insert into Test values(3,'c');
4 insert into Test values(1,'aaaa');
5 insert into Test values(2,'bbbbb');
6 insert into Test values(3,'cccccc');

3.进入主题，创建存储过程

create or replace procedure pro1(

param nvarchar2,

mycur OUT sys_refcursor --游标，用于存储过程中返回结果集

)

as

begin

open mycur for select * from test where AAA=param ;

end;

4.执行存储过程

1 var type_cur refcursor
2 var para nvarchar2(20)
3 execute :para :='1';
4 exec pro1(:para,:type_cur);
5 print type_cur;

5.使用程序包package创建存储过程返回table

1 create or replace package pkg_HelloWorld as
2   type myrctype is ref cursor; --定义游标类型
3   procedure getHelloWorld (param nvarchar2,rst out myrctype); --创建存储过程声明，注：必须与程序包体中的存储过程头相同
4 end pkg_HelloWorld;

6.创建package body包体

create or replace package body pkg_HelloWorld as
  procedure getHelloWorld(param nvarchar2,rst out myrctype) --注：必须与包声明中相同
as
begin
 open rst for
    select * from test where AAA=param;  
  end getHelloWorld;
end pkg_HelloWorld;

7.调用使用包，返回table

var type_cur refcursor
var para nvarchar2(20)
execute :para :='1';
exec PKG_HELLOWORLD.getHelloWorld(:para,:type_cur);
print type_cur;

=========================================================================

来自:http://blog.itpub.net/12639172/viewspace-564910/

Asp.Net 访问Oracle的简单例子

　1.为灵活设置，将连接字符写在web.config文件中

  <appSettings>
        <add key="oracleconn" value="User ID=terryfeng;Password=frt_2007;Data Source=test;"/>

<add key="oracleconn" value="User ID=terryfeng;Password=frt_2007;Data Source=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST=192.168.1.200)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=test.finemoon)))" />
    appSettings>

字串说明：

   需要定义Net服务：  <add key="参数名" value="User ID=用户名;Password=用户密码;Data Source=数据源;"/>  注意：这种连接方法可能会出现权限问题请参考 我的另一篇随笔解决“System.Data.OracleClient 需要 Oracle 客户端软件 8.1.7 或更高版本。”（图）

    无需定义Net服务：  <add key="参数名" value="User ID=用户名;Password=用户密码;Data Source=(DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = 协议)(HOST=主机)(PORT=端口)))(CONNECT_DATA=(SERVICE_NAME=服务名)))" /> 注意： 这种方法有一个问题是，无法想上面的服务那样一次定义服务，在服务下面再定义多个连接

　2.为Web 应用程序添加 System.Data.OracleClient 程序集引用　

　3.下面是测试的程序

　　aspx 文件代码如下：

 1 <%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="AspNetOra._Default" %>
 2 
 3 <DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 4 <html xmlns="http://www.w3.org/1999/xhtml">
 5 <head runat="server">
 6     <title>title>
 7 </head>
 8 <body>
 9     <form. id="form2" runat="server">
10     <div>
11         <p>
12             调用一个Oracle连接并执行一条语句测试是否成功<asp:Label ID="Label1" runat="server" ForeColor="Red" Text="">asp:Label>
13             <br />
14             <asp:Button ID="Button2" runat="server" OnClick="Button1_Click" Text="测试连接是否成功" />
15         </p>
16         <p>
17             调用给SQL语句执行绑定<br />
18             <asp:GridView ID="GridView2" runat="server">
19             asp:GridView>
20             <br />
21             <asp:Button ID="Button3" runat="server" OnClick="Button2_Click" Text="返回数据集合，绑定Gridview" />
22         </p>
23         <p>
24             输入输出参数的存储过程调用<br />
25             <asp:TextBox ID="TextBox1" runat="server">asp:TextBox>
26             <br />
27             <asp:Button ID="Button4" runat="server" Text="z输入输出参数的存储过程" OnClick="Button3_Click" />
28         </p>
29         <p>
30             执行返回数据集的Oracle存储过程<br />
31             <asp:GridView ID="GridView3" runat="server">
32             asp:GridView>
33             <asp:Button ID="Button5" runat="server" Text="执行存储过程返回数据集" OnClick="Button4_Click" />
34        </ p>
35    < /div>
36     <form>
37 <body>
38 <html>

aspx.cs 文件代码如下：

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Linq;
  4 using System.Web;
  5 using System.Web.UI;
  6 using System.Web.UI.WebControls;
  7 using System.Data.SqlClient;
  8 using System.Data.OracleClient;
  9 
 10 namespace AspNetOra
 11 {
 12     public partial class _Default : System.Web.UI.Page
 13     {
 14         System.Data.OracleClient.OracleConnection objConn;  //声明一个OracleConnection对象
 15 
 16         System.Data.OracleClient.OracleCommand objCmd;  //声明一个OracleCommand对象
 17 
 18         string strcon = System.Configuration.ConfigurationManager.AppSettings["oracleconn"]; //从Web.config 文件里调用数据库连接字符串
 19 
 20         protected void Page_Load(object sender, EventArgs e)
 21         {
 22 
 23         }
 24         /// 
 25 
 26         /// 测试连接
 27         /// 
 28 
 29         /// 
 30         /// 
 31         protected void Button1_Click(object sender, EventArgs e)
 32         {
 33             try
 34             {
 35                 bjConn = new OracleConnection(strcon); //用连接字符串，实例化连接对象
 36 
 37                 objConn.Open();  //打开数据库连接
 38 
 39                 string strSQL = "select * from sys.test1"; //查询sys建立的test1表的内容 因为我现在用的登录用户不是sys了
 40 
 41                 objCmd = new OracleCommand(strSQL, objConn); //用命令和连接对象建立命令实例
 42 
 43                 objCmd.ExecuteNonQuery();
 44 
 45                 objConn.Close();
 46 
 47                 this.Label1.Text = "成功";
 48 
 49             }
 50             catch
 51             {
 52                 this.Label1.Text = "失败";
 53             }
 54 
 55 
 56         }
 57 
 58         /// 
 59 
 60         /// 执行SQL 语句返回数据集合
 61         /// 
 62 
 63         /// 
 64         /// 
 65         protected void Button2_Click(object sender, EventArgs e)
 66         {
 67             bjConn = new OracleConnection(strcon); //用连接字符串，实例化连接对象
 68 
 69             objConn.Open();  //打开数据库连接
 70 
 71             System.Data.OracleClient.OracleDataAdapter adapter = new OracleDataAdapter("select * from sys.test1", objConn);
 72 
 73             System.Data.DataSet ds = new System.Data.DataSet();
 74 
 75             adapter.Fill(ds);
 76 
 77             GridView1.DataSource = ds.Tables[0].DefaultView;
 78 
 79             GridView1.DataBind();
 80 
 81         }
 82 
 83         /// 
 84 
 85         ///  存储过程输入、输出参数
 86         ///  
 87         ///  存储过程定义如下：
 88         /// 
 89         ///  create or replace procedure getName(name_out out varchar2 ,id_in in varchar2) is
 90         ///  begin
 91         ///    select NAME into name_out from test1 where id = id_in;
 92         ///  end getName;
 93         /// 
 94         /// 
 95 
 96         /// 
 97         /// 
 98         protected void Button3_Click(object sender, EventArgs e)
 99         {
100             bjConn = new OracleConnection(strcon); //用连接字符串，实例化连接对象
101 
102             objConn.Open();  //打开数据库连接
103 
104             OracleCommand cmd = new OracleCommand("getName", objConn);
105 
106             cmd.CommandType = System.Data.CommandType.StoredProcedure;
107 
108             cmd.Parameters.Add("name_out", OracleType.VarChar, 20); //输出参数，注意名字要与存储过程一致
109 
110             cmd.Parameters["name_out"].Direction = System.Data.ParameterDirection.Output;
111 
112             cmd.Parameters.Add("id_in", OracleType.VarChar, 20);  //输入参数，注意名字要与存储过程一致
113 
114             cmd.Parameters["id_in"].Direction = System.Data.ParameterDirection.Input;
115 
116             cmd.Parameters["id_in"].Value = "1";  //给输入参数赋值
117 
118             cmd.ExecuteNonQuery();
119 
120             this.TextBox1.Text = cmd.Parameters["name_out"].Value.ToString(); //获得输出参数
121 
122 
123         }
124 
125         /// 
126 
127         ///  Oracle存储过程并不直接返回记录集，记录集以游标的形式通过参数返回。一个包(Packages可以包含多个存储过程，
128         ///  访问存储过程时采用“包名.存储过程名”的方式。
129         ///  存储过程是我上一个随笔写的Test，但是上一个存储过程是用Sys建立的，在这里我又重新用当前用户建立的一个属于这个用户的包
130         ///  
131         ///  包定义如下：
132         ///  包头：
133         ///  create or replace package test is
134 
135         ///     TYPE MYCURSOR IS REF CURSOR;
136         ///     PROCEDURE GETLIST(cur_OUT OUT MYCURSOR);
137 
138         ///  end test;
139         /// 
140         /// 
141         /// 
142         ///   包体：
143         ///  create or replace package body test is
144 
145         ///    PROCEDURE GETLIST(cur_OUT OUT MYCURSOR) AS
146         ///    BEGIN
147         ///         OPEN cur_OUT FOR SELECT * FROM test1;
148         ///    END;
149 
150         ///  end test; 
151         /// 
152         /// 
153         /// 
154         /// 
155         /// 
156         /// 
157 
158         /// 
159         /// 
160         protected void Button4_Click(object sender, EventArgs e)
161         {
162             bjConn = new OracleConnection(strcon); //用连接字符串，实例化连接对象
163 
164             objConn.Open();  //打开数据库连接
165 
166             OracleCommand cmd = new OracleCommand("test.GETLIST", objConn);
167 
168             cmd.Parameters.Add("cur_OUT", OracleType.Cursor);     //注意这里的类型
169 
170             cmd.Parameters["cur_OUT"].Direction = System.Data.ParameterDirection.Output;
171 
172             cmd.CommandType = System.Data.CommandType.StoredProcedure;
173 
174             OracleDataAdapter da = new OracleDataAdapter(cmd);
175 
176             System.Data.DataSet ds = new System.Data.DataSet();
177 
178             da.Fill(ds);
179 
180             GridView2.DataSource = ds.Tables[0].DefaultView;
181 
182             GridView2.DataBind();
183 
184 
185         }
186     }
187 }

========================================================================

这句话很重要，是使用oralce存储过程绑定gridview的关键->
　　Oracle存储过程并不直接返回记录集，记录集以游标的形式通过参数返回。一个包(Packages可以包含多个存储过程,访问存储过程时采用“包名.存储过程名”的方式。
　　也可以使用我刚才上面介绍的那种方式，直接使用存储过程返回sys_cursor。
直接使用存储过程sys_cursor与使用Package返回sys_cursor的使用方法完全一样，唯一不同的是调用时的语法略有差异。下面用C#代码写出来，可以与上面的做一下对比。

 using (OracleConnection oraConn = new OracleConnection(DbFactory.GetConnStr(DataBase.Oracle, "192.168.0.1", "test", "1", "1")))
        {
            using (OracleCommand cmd = new OracleCommand("testProc2", oraConn))//只有这里有一点点区别
            {
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.Parameters.Add("cur_out", OracleDbType.RefCursor);
                cmd.Parameters["cur_out"].Direction = System.Data.ParameterDirection.Output;
                using (OracleDataAdapter oraDa = new OracleDataAdapter(cmd))
                {
                    DataSet ds = new DataSet();
                    oraDa.Fill(ds);
                    GridView1.DataSource = ds.Tables[0];
                    GridView1.DataBind();
                }
            }
        }