oracle 存储过程返回 结果集 table形式 (使用sys_refcursor 及程序包package 两种方式)
2015-11-27 16:20 silence_blog 阅读(8206) 评论(0) 编辑 收藏 举报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(); } } }
