用ADO.NET访问Oracle9i存储过程(下)
添加时间: 2008-4-26 1:43:06 作者: Oracle指导 阅读次数:48 来源: http://www.d9soft.com
updateCommand.Parameters.Add("p_street_address", OracleType.VarChar, 40,
"STREET_ADDRESS");
updateCommand.Parameters.Add("p_postal_code", OracleType.VarChar, 12,
"POSTAL_CODE");
updateCommand.Parameters.Add("p_city", OracleType.VarChar, 30, "CITY");
updateCommand.Parameters.Add("p_state_province", OracleType.VarChar, 25,
"STATE_PROVINCE");
updateCommand.Parameters.Add("p_country_id", OracleType.Char, 2,
"COUNTRY_ID");
da.UpdateCommand = updateCommand;
// define the delete command for the data adapter
OracleCommand deleteCommand =
new OracleCommand("CRUD_LOCATIONS.DeleteLocations",
new OracleConnection(connString));
deleteCommand.CommandType = CommandType.StoredProcedure;
deleteCommand.Parameters.Add("p_location_id", OracleType.Number, 4,
"LOCATION_ID");
da.DeleteCommand = deleteCommand;
OracleCommand insertCommand =
new OracleCommand("CRUD_LOCATIONS.InsertLocations",
new OracleConnection(connString));
insertCommand.CommandType = CommandType.StoredProcedure;
insertCommand.Parameters.Add("p_location_id", OracleType.Number, 4,
"LOCATION_ID");
insertCommand.Parameters.Add("p_street_address", OracleType.VarChar, 40,
"STREET_ADDRESS");
insertCommand.Parameters.Add("p_postal_code", OracleType.VarChar, 12,
"POSTAL_CODE");
insertCommand.Parameters.Add("p_city", OracleType.VarChar, 30, "CITY");
insertCommand.Parameters.Add("p_state_province", OracleType.VarChar, 25,
"STATE_PROVINCE");
insertCommand.Parameters.Add("p_country_id", OracleType.Char, 2,
"COUNTRY_ID");
da.InsertCommand = insertCommand;
// define a DataTable and fill it using the data adapter
DataTable dt = new DataTable();
da.Fill(dt);
// ... do work that adds, edits, updates, or deletes records in the table
// call the Update() method of the data adapter to update the Oracle
// database with changes made to the data
da.Update(dt);
使用多个结果集
Oracle 不支持批量查询，因此无法从一个命令返回多个结果集。使用存储过程时，返回多个结果集类似于返回单个结果集；必须使用 REF CURSOR 输出参数。要返回多个结果集，请使用多个 REF CURSOR 输出参数。
以下是返回两个结果集（全部 EMPLOYEES 和 JOBS 记录）的包规范：
CREATE OR new PACKAGE SELECT_EMPLOYEES_JOBS AS
TYPE T_CURSOR IS REF CURSOR;
PROCEDURE GetEmployeesAndJobs (
cur_Employees OUT T_CURSOR,
cur_Jobs OUT T_CURSOR
);
END SELECT_EMPLOYEES_JOBS;
包正文如下所示：
CREATE OR new PACKAGE BODY SELECT_EMPLOYEES_JOBS AS
PROCEDURE GetEmployeesAndJobs
(
cur_Employees OUT T_CURSOR,
cur_Jobs OUT T_CURSOR
)
IS
BEGIN
-- return all EMPLOYEES records
OPEN cur_Employees FOR
SELECT * FROM Employees;
-- return all JOBS records
OPEN cur_Jobs FOR
SELECT * FROM Jobs;
END GetEmployeesAndJobs;
END SELECT_EMPLOYEES_JOBS;
以下代码显示了如何使用从上述包中返回的两个结果集来填充 DataSet 中的两个相关表：
// create the connection
OracleConnection conn = new OracleConnection("Data Source=oracledb;
User Id=UserID;Password=Password;");
// define the command for the stored procedure
OracleCommand cmd = new OracleCommand();
cmd.Connection = conn;
cmd.CommandText = "SELECT_EMPLOYEES_JOBS.GetEmployeesAndJobs";
// add the parameters including the two REF CURSOR types to retrieve
// the two result sets
cmd.Parameters.Add("cur_Employees", OracleType.Cursor).Direction =
ParameterDirection.Output;
cmd.Parameters.Add("cur_Jobs", OracleType.Cursor).Direction =
ParameterDirection.Output;
cmd.CommandType = CommandType.StoredProcedure;
// create the DataAdapter and map tables
OracleDataAdapter da = new OracleDataAdapter(cmd);
da.TableMappings.Add("Table", "EMPLOYEES");
da.TableMappings.Add("Table1", "JOBS");
// create and fill the DataSet
DataSet ds = new DataSet();
da.Fill(ds);
// create a relation
ds.Relations.Add("EMPLOYEES_JOBS_RELATION",
ds.Tables["JOBS"].Columns["JOB_ID"],
ds.Tables["EMPLOYEES"].Columns["JOB_ID"]);
// output the second employee (zero-based array) and job title
// based on the relation
Console.WriteLine("Employee ID: " +
ds.Tables["EMPLOYEES"].Rows[1]["EMPLOYEE_ID"] +
"; Job Title: " +
ds.Tables["EMPLOYEES"].Rows[1].GetParentRow(
"EMPLOYEES_JOBS_RELATION")["JOB_TITLE"]);
控制台输出显示了第二个员工的职务：
Employee ID: 101; Job Title: Administration Vice President
小结
通过 Oracle .NET 数据提供程序，可以方便地执行存储过程以及访问返回值（无论返回值是一个还是多个标量值或结果集）。
用ADO.NET访问Oracle9i存储过程(下)(3) 第 [1] [2] [3] [4] 下一页
上一篇文章: 用ADO.NET访问Oracle9i存储过程(上) 下一篇文章: Oracle冷备份的不完全恢复(上)
相关文章: