Oracle中如何返回数据集
(作者:Fred Zhang 2005-9-20 MSN: [email protected])
在实际开发中,我们常常需要从数据库中返回数据集,在MS SQL Server中返回数据集是非常容易的事情,只要把Select 语句放在存储过程中就可以了,但在Oracle却不太容易,常常让初学者望而却步。下文就是介绍在Oracle中如何返回数据集。1. 测试环境:
Oracle 9.2 +ASP.Net2003
Oracle 中HR方案 Password:Hr
2. 第一步:建Oracle包:
CREATE OR REPLACE PACKAGE SELECT_JOB_HISTORY AS
TYPE T_CURSOR IS REF CURSOR;
PROCEDURE GetJobHistoryByEmployeeId
(
p_employee_id IN NUMBER,
cur_JobHistory OUT T_CURSOR
);
END SELECT_JOB_HISTORY;
3.第二步:建Oracle包体
CREATE OR REPLACE PACKAGE BODY SELECT_JOB_HISTORY AS
PROCEDURE GetJobHistoryByEmployeeId
(
p_employee_id IN NUMBER,
cur_JobHistory OUT T_CURSOR
)
IS
BEGIN
OPEN cur_JobHistory FOR
SELECT * FROM JOB_HISTORY
WHERE employee_id = p_employee_id;
END GetJobHistoryByEmployeeId;
END SELECT_JOB_HISTORY;
4. 第三步 在ASP.Net中调用,输入员工的ID号101.using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.OracleClient;namespace test
{
/// <summary>
/// WebForm8
/// </summary>
public class WebForm8 : System.Web.UI.Page
{
protected System.Web.UI.WebControls.DataGrid DataGrid1;
private void Page_Load(object sender, System.EventArgs e)
{
if(!Page.IsPostBack)
{
DataGrid1.DataSource=getDataSet();
DataGrid1.DataBind();
}
}
public DataView getDataSet()
{
//OracleConnection conn = new OracleConnection("Data Source=ora9_192.168.100.161;User Id=cams;Password=cams;");
OracleConnection conn = new OracleConnection("Data Source=ora9_192.168.100.161;User Id=hr;Password=hr;");
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "select_job_history.GetJobHistoryByEmployeeId";
cmd.CommandType = CommandType.StoredProcedure;
DataSet objDataSet=new DataSet(); // add the parameters for the stored procedure including the REF CURSOR
// to retrieve the result set
cmd.Parameters.Add("p_employee_id", OracleType.Number).Value =101;
cmd.Parameters.Add("cur_JobHistory", OracleType.Cursor).Direction =ParameterDirection.Output;
conn.Open();
OracleDataAdapter objDA=new OracleDataAdapter(cmd);
objDA.Fill(objDataSet,"gp");
DataView objdv=new DataView(objDataSet.Tables["gp"]); conn.Close();
return objdv; } #region
override protected void OnInit(EventArgs e)
{
InitializeComponent();
base.OnInit(e);
}
private void InitializeComponent()
{
this.Load += new System.EventHandler(this.Page_Load); }
#endregion
}
}
(作者:Fred Zhang 2005-9-20 MSN: [email protected])
在实际开发中,我们常常需要从数据库中返回数据集,在MS SQL Server中返回数据集是非常容易的事情,只要把Select 语句放在存储过程中就可以了,但在Oracle却不太容易,常常让初学者望而却步。下文就是介绍在Oracle中如何返回数据集。1. 测试环境:
Oracle 9.2 +ASP.Net2003
Oracle 中HR方案 Password:Hr
2. 第一步:建Oracle包:
CREATE OR REPLACE PACKAGE SELECT_JOB_HISTORY AS
TYPE T_CURSOR IS REF CURSOR;
PROCEDURE GetJobHistoryByEmployeeId
(
p_employee_id IN NUMBER,
cur_JobHistory OUT T_CURSOR
);
END SELECT_JOB_HISTORY;
3.第二步:建Oracle包体
CREATE OR REPLACE PACKAGE BODY SELECT_JOB_HISTORY AS
PROCEDURE GetJobHistoryByEmployeeId
(
p_employee_id IN NUMBER,
cur_JobHistory OUT T_CURSOR
)
IS
BEGIN
OPEN cur_JobHistory FOR
SELECT * FROM JOB_HISTORY
WHERE employee_id = p_employee_id;
END GetJobHistoryByEmployeeId;
END SELECT_JOB_HISTORY;
4. 第三步 在ASP.Net中调用,输入员工的ID号101.using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.OracleClient;namespace test
{
/// <summary>
/// WebForm8
/// </summary>
public class WebForm8 : System.Web.UI.Page
{
protected System.Web.UI.WebControls.DataGrid DataGrid1;
private void Page_Load(object sender, System.EventArgs e)
{
if(!Page.IsPostBack)
{
DataGrid1.DataSource=getDataSet();
DataGrid1.DataBind();
}
}
public DataView getDataSet()
{
//OracleConnection conn = new OracleConnection("Data Source=ora9_192.168.100.161;User Id=cams;Password=cams;");
OracleConnection conn = new OracleConnection("Data Source=ora9_192.168.100.161;User Id=hr;Password=hr;");
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "select_job_history.GetJobHistoryByEmployeeId";
cmd.CommandType = CommandType.StoredProcedure;
DataSet objDataSet=new DataSet(); // add the parameters for the stored procedure including the REF CURSOR
// to retrieve the result set
cmd.Parameters.Add("p_employee_id", OracleType.Number).Value =101;
cmd.Parameters.Add("cur_JobHistory", OracleType.Cursor).Direction =ParameterDirection.Output;
conn.Open();
OracleDataAdapter objDA=new OracleDataAdapter(cmd);
objDA.Fill(objDataSet,"gp");
DataView objdv=new DataView(objDataSet.Tables["gp"]); conn.Close();
return objdv; } #region
override protected void OnInit(EventArgs e)
{
InitializeComponent();
base.OnInit(e);
}
private void InitializeComponent()
{
this.Load += new System.EventHandler(this.Page_Load); }
#endregion
}
}
我看好多网友都是问比如说在java中,在.net环境pb环境中如何调用返回数据集的存储过程.
(
p_employee_id IN NUMBER,
cur_JobHistory OUT T_CURSOR,
pageCount out number,
counts out number -----多一两个输入参数
) 请问怎么获取。??
OracleDataAdapter objDA=new OracleDataAdapter(cmd);
这样只能获取游标的值,获不到单个参数的值