使用ADO.NET訪問Oracle 9i存儲過程(上)

2024-08-29 13:32:20

字體：大中小

來源：轉載

供稿：網友

　　本文討論了如何使用 ado.net 訪問 oracle 存儲過程（稱為 sql 編程塊）和函數（返回單個值的編程塊）。

　　您可以使用以下托管數據提供程序連接到 oracle 數據庫：microsoft .net oracle 提供程序、ole db .net 提供程序、odbc .net 數據提供程序以及 oracle 的 odp.net 提供程序。本文使用用于 oracle 的 microsoft?.net 框架數據提供程序。使用 oracle odp.net 數據提供程序或用于 ole db 的 microsoft .net 框架數據提供程序時可使用不同的功能。

　　oracle .net 數據提供程序隨 .net 框架 1.1 一起提供。如果您使用的是 .net 框架 1.0，您將需要下載 .net managed provider for oracle。無論是哪個版本，數據提供程序類都位于 system.data.oracleclient 命名空間中。

　　概述

　　pl/sql 是 sql 的 oracle 實現。它與 microsoft?sql server? 所使用的 t-sql 類似，但也有一些不同之處，本文稍后對此進行了詳細討論。與 t-sql 一樣，pl/sql 擴展了標準 sql。pl/sql 用于定義命名編程塊，如存儲過程、函數和觸發器。

　　類

　　可使用 system.data.oracleclient 命名空間中類的子集來執行 oracle 存儲過程和函數。下表對這些類進行了說明：

　　類說明

　　oraclecommand

　　針對 oracle 數據庫執行的存儲過程的 sql 語句。

　　oracleconnection

　　打開的數據庫連接。

　　oracleparameter

　　oraclecommand 的參數，也可能是它到 datacolumn 的映射。

　　oracleparametercollection

　　oracleparameter 對象的集合。

　　oracletype

　　oracle 數據類型和結構的枚舉。

　　執行存儲過程

　　執行 oracle 存儲過程與執行 sql server 存儲過程類似。下面的步驟說明了如何執行 oracle 存儲過程和檢索它返回的結果。

|||

本文來源于網頁設計愛好者web開發社區http://www.html.org.cn收集整理，歡迎訪問。

　　在 hr 架構中創建一個名為 count_job_history 的存儲過程，以計算 job_history 表中的記錄數。

create or new procedure count_job_history
(
　　 reccount out number
)
is
begin
　　 select count(*) into reccount
　　 from job_history;
end count_job_history;

　　hr 架構是默認 oracle 安裝中包含的一個示例。

　　將 system.data.oracleclient.dll（用于 oracle 的 microsoft .net 框架數據提供程序）的引用添加到項目中。

　　使用 using 指令導入 oracleclient 類中的類型。

　　using system.data.oracleclient;

　　創建一個 oracleconnection 對象。

　　oracleconnection conn = new oracleconnection("data source=oracledb;

　　user id=userid;password=password;");

　　用您的值替換 oracle 數據庫的名稱、用戶名和密碼。

　　創建一個 oraclecommand 對象。將其 connection 屬性設置為第 4 步中創建的連接。將其 commandtext 設置為存儲過程的名稱，并將其 commandtext 屬性設置為 commandtype.storedprocedure。當您調用第 8 步中介紹的一個 execute() 方法時，該命令對象將執行指定的存儲過程。

oraclecommand cmd = new oraclecommand();
cmd.connection = conn;
cmd.commandtext = "count_job_history";
cmd.commandtype = commandtype.storedprocedure;

　　如果您的存儲過程名稱含有特殊字符，您就必須使用轉義序列。您可以通過重置 commandtext 屬性來重用現有的 oraclecommand 對象。

|||

　　創建輸入、輸出和返回值的 oracleparameter 對象，并將其添加到 oraclecommand 對象的參數集合中。

　　cmd.parameters.add("reccount", oracletype.number).direction =
　　 parameterdirection.output;

　　該行代碼是以下兩行代碼的簡寫形式：

cmd.parameters.add("reccount", oracletype.number);
cmd.parameters["reccount"].direction = parameterdirection.output;

　　如果您要檢索結果集，請創建 dataset、datatable 或 datareader。在本示例中，我們只是獲取第 6 步中創建的輸出參數中的計數。

　　使用 oraclecommand 對象的一個 execute 方法打開連接并執行存儲過程，如下所示：

　　方法說明

　　executereader

　　通過執行能夠返回結果集的存儲過程生成 oracledatareader。

　　executenonquery

　　執行不返回結果集的查詢或過程，返回受影響的行數。

　　executeoraclenonquery

　　執行查詢，返回受影響的行數。

　　該方法還使用 oraclestring 參數來返回 update、insert 或 delete 查詢所修改的最后一行的行 id。

　　executescalar

　　執行一個查詢或過程，并且返回查詢或過程的返回值，或者將結果集第一行第一列的值作為 .net 框架數據類型返回。

　　executeoraclescalar

　　執行一個查詢或過程，并且返回查詢或過程的返回值，或者將結果集第一行第一列的值作為 oracletype 數據類型返回。

　　使用完連接后，不要忘記將其關閉。

conn.open();
cmd.executenonquery();
conn.close();

　　如果您要使用 dataadapter 來填充 datatable 或 dataset，可以依靠 dataadapter 來打開和關閉連接。

|||

　　處理結果。在我們的示例中，可在顯示到控制臺的輸出參數中得到記錄數：

　　console.writeline(cmd.parameters["reccount"].value);

　　下面是在本示例中開發的用于執行存儲過程和檢索結果的代碼：

oracleconnection conn = new oracleconnection("data source=oracledb;
　　 user id=userid;password=password;");
　　oraclecommand cmd = new oraclecommand();
cmd.connection = conn;
cmd.commandtext = "count_job_history";
cmd.commandtype = commandtype.storedprocedure;
cmd.parameters.add("reccount", oracletype.number).direction =
　　 parameterdirection.output;
　　conn.open();
cmd.executenonquery();
conn.close();
　　console.writeline(cmd.parameters["reccount"].value);

　　不返回數據的存儲過程

　　oraclecommand 類的 executeoraclenonquery() 方法用于執行不返回任何行的 sql 語句或存儲過程。該方法返回一個 int 值，表示受 update、insert 和 delete 命令影響的行數；如果沒有任何行受到影響，則返回 -1。如果您所執行的 insert、delete 或 update 語句恰好影響一行，則該方法具有單個參數 oraclestring out rowid，該參數唯一標識 oracle 數據庫中受影響的行。可以使用該值來優化后續相關查詢。

　　還可以使用 oraclecommand 類的 executenonquery() 方法來執行不返回數據的存儲過程，但您將無法獲得上面介紹的唯一行標識符。

　　盡管上述命令都不會返回任何數據，但映射到參數的輸出參數和返回值仍然使用數據進行填充。這使您可以使用上述任一命令從存儲過程返回一個或多個標量值。

|||

　　以下 oracle 存儲過程刪除了由單個輸入參數指定的員工的所有工作經歷，并且不返回任何數據。

create or new procedure delete_job_history
(
　　 p_employee_id number
)
is
begin
　　 delete from job_history
　　 where employee_id = p_employee_id;
end delete_job_history;

　　以下代碼運行了該存儲過程。

// create the connection
oracleconnection conn = new oracleconnection("data source=oracledb;
　　 user id=userid;password=password;");
　　// create the command for the stored procedure
oraclecommand cmd = new oraclecommand();
cmd.connection = conn;
cmd.commandtext = "count_job_history";
cmd.commandtype = commandtype.storedprocedure;
　　// add the parameter specifying the employee for whom to delete records
cmd.parameters.add("p_employee_id", oracletype.number).value = 102;
　　oraclestring rowid;
// execute the stored procedure
conn.open();
int rowsaffected = cmd.executenonquery();
conn.close();
　　console.writeline("rows affected: " + rowsaffected);

　　如果您尚未修改默認的 hr 安裝，則 job_history 表中員工 102 的記錄被刪除，并且向控制臺輸出以下內容：

　　rows affected: 1

　　訪問返回值

　　return 語句立即將控制從存儲過程返回到調用程序。oracle 存儲過程中的 return 語句無法像在 t-sql 中那樣返回值。

|||

網站運營seo文章大全

提供全面的站長運營經驗及seo技術!

　　oracle 函數是計算并返回單個值的子程序。它們的結構類似于存儲過程，不同之處在于它們總是具有必須返回值的 return 子句。

　　下面是一個返回指定員工的電子郵件的函數：

create or new function get_employee_email (
　　 p_employee_id number
)
return varchar2
is p_email varchar2(25);
begin
　　 select email into p_email from employees
　　 where employee_id = p_employee_id;
　　
　　 return p_email;
end get_employee_email;

　　執行函數的方式與執行存儲過程的方式相同。可使用 parameterdirection.returnvalue 參數獲得由函數返回的結果。以下代碼顯示了使用方法：

// create the connection
oracleconnection conn = new oracleconnection("data source=oracledb;
　　 user id=userid;password=password;");
　　// create the command for the function
oraclecommand cmd = new oraclecommand();
cmd.connection = conn;
cmd.commandtext = "get_employee_email";
cmd.commandtype = commandtype.storedprocedure;
　　// add the parameters, including the return parameter to retrieve
// the return value
cmd.parameters.add("p_employee_id", oracletype.number).value = 101;
cmd.parameters.add("p_email", oracletype.varchar, 25).direction =
　　 parameterdirection.returnvalue;
　　// execute the function
conn.open();
cmd.executenonquery();
conn.close();
　　// output the result
console.writeline("email address is: " + cmd.parameters["p_email"].value);

　　控制臺輸出顯示了員工 101 的電子郵件地址。

|||

本文來源于網頁設計愛好者web開發社區http://www.html.org.cn收集整理，歡迎訪問。

create or new package body crud_locations as
procedure getlocations (cur_locations out t_cursor)
is
begin
　　 open cur_locations for
　　 select * from locations;
end getlocations;
　　-- implementation of other procedures ommitted.
　　end crud_locations;

　　使用 datareader

　　可以通過調用 oraclecommand 對象的 executereader() 方法來創建 oracledatareader。本節說明如何使用 datareader 來訪問由存儲過程 select_job_history 返回的結果集。以下為包規范：

create or new 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;

　　包正文定義了一個過程，該過程檢索指定員工的工作經歷的結果集，并將其作為 ref cursor 輸出參數返回：

　create or new 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;

　　以下代碼執行該過程，根據結果集創建 datareader，并將 datareader 的內容輸出到控制臺。

　　// create connection
oracleconnection conn = new oracleconnection("data source=oracledb;
　　 user id=userid;password=password;");
　　// create the command for the stored procedure
oraclecommand cmd = new oraclecommand();
cmd.connection = conn;
cmd.commandtext = "select_job_history.getjobhistorybyemployeeid";
cmd.commandtype = commandtype.storedprocedure;
　　// 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;
　　// open the connection and create the datareader
conn.open();
oracledatareader dr = cmd.executereader();
　　// output the results and close the connection.
while(dr.read())
{
　　 for(int i = 0; i < dr.fieldcount; i++)
　　　　 console.write(dr[i].tostring() + ";");
　　 console.writeline();
}
conn.close();