Oracle在PL/SQL中使用存儲過程
更新時間：2022年05月06日 10:36:09 作者：springsnow
這篇文章介紹了Oracle在PL/SQL中使用存儲過程的方法，文中通過示例代碼介紹的非常詳細(xì)。對大家的學(xué)習(xí)或工作具有一定的參考借鑒價值，需要的朋友可以參考下
一、概述
過程和函數(shù)統(tǒng)稱為PL/SQL子程序，他們是被命名的PL/SQL塊，均存儲于數(shù)據(jù)庫中。
并通過輸入、輸出和輸入輸出參數(shù)與其調(diào)用者交換信息。唯一區(qū)別是函數(shù)總向調(diào)用者返回數(shù)據(jù)。
二、存儲過程詳解
1、創(chuàng)建過程語法：
CREATE [ OR REPLACE ] PROCEDURE [ schema. ] procedure_name
[ ( parameter_declaration [, parameter_declaration ]... ) ]
[ invoker_rights_clause ]
{ IS | AS }
{ [ declare_section ] body | call_spec | EXTERNAL} ;說明：
- procedure_name：過程名稱。
- parameter_declaration：參數(shù)聲明，格式如下：
parameter_name [ [ IN ] datatype [ { := | DEFAULT } expression ]
| { OUT | IN OUT } [ NOCOPY ] datatype- IN：輸入?yún)?shù)。
- OUT：輸出參數(shù)。
- IN OUT：輸入輸出參數(shù)。
- invoker_rights_clause：這個過程使用誰的權(quán)限運行，格式：
AUTHID { CURRENT_USER | DEFINER }- declare_section：聲明部分。
- body：過程塊主體，執(zhí)行部分
2、創(chuàng)建存儲過程
帶有輸入、輸出參數(shù)的過程
CREATE OR REPLACE PROCEDURE proc_demo
(
dept_no NUMBER DEFAULT 10,
sal_sum OUT NUMBER,
emp_count OUT NUMBER
)
IS
BEGIN
SELECT SUM(salary), COUNT(*) INTO sal_sum, emp_count
FROM employees WHERE department_id = dept_no;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('溫馨提示:你需要的數(shù)據(jù)不存在!');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);
END proc_demo;3、調(diào)用存儲過程
調(diào)用方式：
1)、當(dāng)在SQL*PLUS中調(diào)用過程時，需要使用CALL或者EXECUTE命令，而在PL/SQL塊中過程可以直接引用。
-- 調(diào)用刪除員工的過程 EXEC remove_emp(1); -- 調(diào)用插入員工的過程 EXECUTE insert_emp(1, 'tommy', 'lin', 2);
2)、在PL/SQL語句塊中直接調(diào)用。
DECLARE
V_num NUMBER;
V_sum NUMBER(8, 2);
BEGIN
Proc_demo(30, v_sum, v_num);
DBMS_OUTPUT.PUT_LINE('溫馨提示:30號部門工資總和：'||v_sum||',人數(shù)：'||v_num);
Proc_demo(sal_sum => v_sum, emp_count => v_num);
DBMS_OUTPUT.PUT_LINE('溫馨提示:10號部門工資總和：'||v_sum||',人數(shù)：'||v_num);
END;4、C# 調(diào)用：
OracleCommand cmd = new OracleCommand("prroc_demo", myOracleConnection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("v_companycode", OracleType.Char);
cmd.Parameters["v_companycode"].Value = "aa";
cmd.Parameters.Add("v_returnvalue", OracleType.Float).Direction = ParameterDirection.Output;
cmd.ExecuteNoQuery();
string eval = cmd.Parameters["v_returnvalue"].Value.ToString();三、存儲過程返回記錄集SYS_REFCURSOR
cursor與REF cursor大致有以下幾點區(qū)別：
- 靜態(tài)游標(biāo)不能返回到客戶端，只有PL/SQL才能利用它。ref游標(biāo)則可以，是從Oracle的存儲過站返回結(jié)果集的方式。
- PL/SQL靜態(tài)游標(biāo)可以是全局的，而ref游標(biāo)只能在定義它的過程中使用，但ref游標(biāo)可以從子例程傳遞到子例程，而普通游標(biāo)則不能。
- 靜態(tài)光標(biāo)比ref游標(biāo)效率要高。
- sys_refcursor在oracle9i以后系統(tǒng)定義的一個refcursor，主要用于在過程中返回結(jié)果集。
1、返回單行語法
create or replace procedure proc_query_rent ( param_region varchar2, --定義區(qū) param_room number, --定義室 param_hall number, --定義廳 param_rentMin number, --定義租金上限 param_rentMax number, --定義租金下限 param_resultSet OUT SYS_REFCURSOR --定義out參數(shù)返回結(jié)果集 ) as begin open param_resultSet for select * from tb_rent where region like case when param_region IS null then '%' else param_region end AND room like case when param_room IS null then '%' else to_char(param_room) end AND hall like case when param_hall IS null then '%' else to_char(param_hall) end AND rent between case when param_rentMin IS null then 0 else param_rentMin end AND case when param_rentMax IS null then 99999999 else param_rentMax end; end;
調(diào)用：
declare
v_rent_rows SYS_REFCURSOR;
v_rent_row tb_rent % rowType;
begin
proc_query_rent('山區(qū)', null, null, 1200, null, v_rent_rows);
Dbms_output.put_line('所在區(qū) 室 廳 租金');
loop
fetch v_rent_rows into v_rent_row;//單行
exit when v_rent_rows % NOTFOUND;
Dbms_output.put_line(v_rent_row.region || ' ' || v_rent_row.room || ' ' || v_rent_row.hall || ' ' || v_rent_row.rent);
end loop;
close v_rent_rows;
end;2、返回多行語法
存儲過程：
create or replace procedure getEmpByDept(in_deptNo in emp.deptno%type, out_curEmp out SYS_REFCURSOR) as begin open out_curEmp for SELECT * FROM emp WHERE deptno = in_deptNo ; EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20101, 'Error in getEmpByDept' || SQLCODE ); end getEmpByDept;
調(diào)用（執(zhí)行存儲過程）：
declare
cur_emp sys_refcursor;
type emp emp_type is table of yemp%rowtype;
vemps emp_type;
begin
sp_getEmp(line=>'A5',curemp=>cur_emp);
fetch cur_emp bulk collect into vemps;
for i in v_emps.first..v_emps.last loop
dbms_output.putline(v_emps(i).empid);
end loop;
close cur_emp;
end;C# 調(diào)用：
OracleCommand cmd = new OracleCommand("prroc_demo", myOracleConnection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("v_companycode", OracleType.Char).Value = "aa";
cmd.Parameters.Add("curEmp", OracleType.Cursor).Direction = ParameterDirection.Output;
OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);
this.dataGridView1.DataSource = ds.Tables[0];四、維護(hù)存儲過程
1、刪除過程
可以使用DROP PROCEDURE命令對不需要的過程進(jìn)行刪除
DROP PROCEDURE logexecution;
2、顯示過程代碼
select text from user_source where name='存儲過程名(大寫)' and type='PROCEDURE';
3、查看過程狀態(tài)
select object_type ,object_name ,status from user_objects where object_name = 'procedure';
4、重新編譯過程
alter procedure pro_backup compile;
五. 過程與函數(shù)比較
1、相同點：
- 都使用IN模式的參數(shù)傳入數(shù)據(jù)、OUT模式的參數(shù)返回數(shù)據(jù)。
- 輸入?yún)?shù)都可以接受默認(rèn)值，都可以傳值或傳引導(dǎo)。
- 調(diào)用時的實際參數(shù)都可以使用位置表示法、名稱表示法或組合方法。
- 都有聲明部分、執(zhí)行部分和異常處理部分。
- 其管理過程都有創(chuàng)建、編譯、授權(quán)、刪除、顯示依賴關(guān)系等。
2、不同點：
- 過程：作為PL/SQL語句執(zhí)行；函數(shù)：作為表達(dá)式的一部分執(zhí)行
- 過程：在規(guī)范中不包含RETURN子句；函數(shù)：必須在規(guī)范中包含RETURN子句
- 過程：不返回任何值；函數(shù)：必須返回單個值
- 過程：可以RETURN語句，但是與函數(shù)不同，它不能用于返回值；函數(shù)：必須包含至少一條RETURN語句
六、 與過程相關(guān)數(shù)據(jù)字典
USER_SOURCE, ALL_SOURCE, DBA_SOURCE, USER_ERRORS,
ALL_PROCEDURES,USER_OBJECTS,ALL_OBJECTS,DBA_OBJECTS
相關(guān)的權(quán)限:
CREATE ANY PROCEDURE DROP ANY PROCEDURE
SQL*PLUS 中，可以用DESCRIBE 命令查看過程的名字及其參數(shù)表。
DESC[RIBE] Procedure_name;
到此這篇關(guān)于Oracle在PL/SQL中使用存儲過程的文章就介紹到這了。希望對大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。
相關(guān)文章
oracle中左填充（lpad)和右填充（rpad）的介紹與用法
這篇文章主要跟大家介紹了關(guān)于oracle中左填充（lpad)和右填充（rpad）的相關(guān)資料，通過填充我們可以固定字段的長度，文中通過示例代碼介紹的非常詳細(xì)，對大家具有一定的參考學(xué)習(xí)價值，需要的朋友們下面來一起看看吧。2017-08-08
Oracle中dbms_output.put_line的用法實例
最近寫了oracle過程,有個ORACLE中dbms_output.put_line的相關(guān)問題,所以下面這篇文章主要給大家介紹了關(guān)于Oracle中dbms_output.put_line的用法實例,需要的朋友可以參考下2022-06-06
Oracle使用觸發(fā)器和mysql中使用觸發(fā)器的案例比較
這篇文章主要介紹了Oracle使用觸發(fā)器和mysql中使用觸發(fā)器的案例比較,本文通過示例講解，給大家介紹的非常詳細(xì)，需要的朋友參考下2016-12-12
Oracle數(shù)據(jù)庫中常要處理一些父子關(guān)系的記錄,在OLTP中要用得多一些,oracle提供了遞歸查詢可以很容易的滿足這個需求,下面這篇文章主要給大家介紹了關(guān)于Oracle遞歸查詢樹形數(shù)據(jù)的相關(guān)資料,需要的朋友可以參考下2022-11-11
oracle關(guān)聯(lián)查詢報invalid number錯誤的解決方法
這篇文章主要介紹了oracle關(guān)聯(lián)查詢報invalid number錯誤的解決方法,文中通過代碼示例和圖文結(jié)合的方式講解的非常詳細(xì),對大家的學(xué)習(xí)或工作有一定的幫助,需要的朋友可以參考下2024-09-09
這篇文章主要介紹了Oracle中檢查外鍵是否有索引的SQL腳本分享,本文給出了兩個版本的腳本源碼,一個查詢所有用戶,一個查詢單用戶,需要的朋友可以參考下2014-10-10
這篇文章主要給大家介紹了關(guān)于Oracle使用強(qiáng)制索引的方法與注意事項，文中通過示例代碼介紹的非常詳細(xì)，對大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價值，需要的朋友們下面隨著小編來一起學(xué)習(xí)學(xué)習(xí)吧。2017-11-11
本文給大家分享一個oracle查看表空間已分配和未分配空間的語句，大家參考使用吧2014-01-01
Oracle數(shù)據(jù)庫創(chuàng)建用戶與數(shù)據(jù)庫備份小結(jié)(必看篇)
下面小編就為大家推薦一篇Oracle數(shù)據(jù)庫創(chuàng)建用戶與數(shù)據(jù)庫備份小結(jié)。具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2018-02-02
在Oracle數(shù)據(jù)庫中，to_char函數(shù)是我們最常用的函數(shù)之一，下文對to_char函數(shù)的應(yīng)用作了詳細(xì)的介紹，如果您感興趣的話，不妨一看2014-08-08