在线欧美a,久久精品在线观看,无码欧精品亚洲日韩一区

原文: ORACLE PL/SQL編程之六：把過程與函數說透(窮追猛打，把根兒都拔起!)

ORACLE PL/SQL 編程之六：

把過程與函數說透 ( 窮追猛打，把根兒都拔起 !)

繼上篇： ORACLE PL/SQL 編程之八：把觸發器說透 得到了大家的強力支持，感謝。接下來再下猛藥，介紹下一篇，大家一定要支持與推薦呀 ~ ！我也才有動力寫后面的。

?

本篇主要內容如下：

6.1? 引言

6.2? 創建函數

6.3? 存儲過程

6.3.1 ? 創建過程

6.3.2 ? 調用存儲過程

6.3.3?A UTHID

6.3.4?P RAGMA AUTONOMOUS_TRANSACTION

6.3.5 ? 開發存儲過程步驟

6.3.6 ? 刪除過程和函數

6.3.7 ? 過程與函數的比較

?

6.1? 引言

過程與函數（另外還有包與觸發器）是命名的 PL/SQL 塊（也是用戶的方案對象），被編譯后存儲在數據庫中，以備執行。因此，其它 PL/SQL 塊可以按名稱來使用他們。所以，可以將商業邏輯、企業規則寫成函數或過程保存到數據庫中，以便共享。

過程和函數統稱為 PL/SQL 子程序，他們是被命名的 PL/SQL 塊，均存儲在數據庫中，并通過輸入、輸出參數或輸入 / 輸出參數與其調用者交換信息。過程和函數的唯一區別是函數總向調用者返回數據，而過程則不返回數據。在本節中，主要介紹：

1． ?? 創建存儲過程和函數。

2． ?? 正確使用系統級的異常處理和用戶定義的異常處理。

3． ?? 建立和管理存儲過程和函數。

6.2? 創建函數

1. 創建函數

?

語法如下：

?

CREATE ? [ OR?REPLACE ] ? FUNCTION ?function_name
?(arg1? [ ?{?IN?|?OUT?|?IN?OUT?} ] ?type1? [ DEFAULT?value1 ] ,
? [ arg2?[?{?IN?|?OUT?|?IN?OUT?} ] ?type2? [ DEFAULT?value1 ] ],
?......
? [ argn?[?{?IN?|?OUT?|?IN?OUT?} ] ?typen? [ DEFAULT?valuen ] ])
? [ ?AUTHID?DEFINER?|?CURRENT_USER? ]
RETURN ?return_type?
? IS ? | ? AS
???? < 類型.變量的聲明部分 > ?
BEGIN
????執行部分
???? RETURN ?expression
EXCEPTION
????異常處理部分
END ?function_name;

?

l ???????? IN,OUT,IN OUT 是形參的模式。若省略，則為 IN 模式。 IN 模式的形參只能將實參傳遞給形參，進入函數內部，但只能讀不能寫，函數返回時實參的值不變。 OUT 模式的形參會忽略調用時的實參值（或說該形參的初始值總是 NULL ），但在函數內部可以被讀或寫，函數返回時形參的值會賦予給實參。 IN OUT 具有前兩種模式的特性，即調用時，實參的值總是傳遞給形參，結束時，形參的值傳遞給實參。調用時，對于 IN 模式的實參可以是常量或變量，但對于 OUT 和 IN OUT 模式的實參必須是變量。
?

l ???????? 一般，只有在確認 function_name 函數是新函數或是要更新的函數時，才使用 OR REPALCE 關鍵字，否則容易刪除有用的函數。

?

例1. ?????????? 獲取某部門的工資總和：

?

-- 獲取某部門的工資總和
CREATE ? OR ? REPLACE
FUNCTION ?get_salary(
??Dept_no? NUMBER ,
??Emp_count?OUT? NUMBER )
?? RETURN ? NUMBER ?
IS
??V_sum? NUMBER ;
BEGIN
?? SELECT ? SUM (SALARY),? count ( * )? INTO ?V_sum,?emp_count
???? FROM ?EMPLOYEES? WHERE ?DEPARTMENT_ID = dept_no;
?? RETURN ?v_sum;
EXCEPTION
??? WHEN ?NO_DATA_FOUND? THEN ?
??????DBMS_OUTPUT.PUT_LINE( ' 你需要的數據不存在! ' );
??? WHEN ?OTHERS? THEN ?
??????DBMS_OUTPUT.PUT_LINE(SQLCODE || ' --- ' || SQLERRM);
END ?get_salary;

?

2. 函數的調用

函數聲明時所定義的參數稱為形式參數，應用程序調用時為函數傳遞的參數稱為實際參數。應用程序在調用函數時，可以使用以下三種方法向函數傳遞參數：

?

第一種參數傳遞格式：位置表示法。

即在調用時按形參的排列順序，依次寫出實參的名稱，而將形參與實參關聯起來進行傳遞。用這種方法進行調用，形參與實參的名稱是相互獨立，沒有關系，強調次序才是重要的。

格式為：

?????? argument_value1[,argument_value2 …]

?

例 2 ：計算某部門的工資總和：

?

DECLARE
??V_num? NUMBER ;
??V_sum? NUMBER ;
BEGIN
??V_sum?: = get_salary( 10 ,?v_num);
??DBMS_OUTPUT.PUT_LINE( ' 部門號為:10的工資總和： ' || v_sum || ' ，人數為： ' || v_num);
END ;

第二種參數傳遞格式：名稱表示法。

即在調用時按形參的名稱與實參的名稱，寫出實參對應的形參，而將形參與實參關聯起來進行傳遞。這種方法，形參與實參的名稱是相互獨立的，沒有關系，名稱的對應關系才是最重要的，次序并不重要。

格式為：

?????? argument => parameter [,…]

其中： argument 為形式參數，它必須與函數定義時所聲明的形式參數名稱相同 parameter 為實際參數。

在這種格式中，形勢參數與實際參數成對出現，相互間關系唯一確定，所以參數的順序可以任意排列。

例 3 ：計算某部門的工資總和：

?

DECLARE
??V_num? NUMBER ;
????V_sum? NUMBER ;
BEGIN
????V_sum?: = get_salary(emp_count? => ?v_num,?dept_no? => ? 10 );
????DBMS_OUTPUT.PUT_LINE( ' 部門號為:10的工資總和： ' || v_sum || ' ，人數為： ' || v_num);
END ;
?

第三種參數傳遞格式：組合傳遞。

即在調用一個函數時，同時使用位置表示法和名稱表示法為函數傳遞參數。采用這種參數傳遞方法時，使用位置表示法所傳遞的參數必須放在名稱表示法所傳遞的參數前面。也就是說，無論函數具有多少個參數，只要其中有一個參數使用名稱表示法，其后所有的參數都必須使用名稱表示法。

?

例 4 ：

CREATE ? OR ? REPLACE ? FUNCTION ?demo_fun(
??Name? VARCHAR2 , -- 注意VARCHAR2不能給精度，如：VARCHAR2(10)，其它類似
??Age? INTEGER ,
??Sex? VARCHAR2 )
?? RETURN ? VARCHAR2 ?
AS
??V_var? VARCHAR2 ( 32 );
BEGIN
??V_var?: = ?name || ' ： ' || TO_CHAR(age) || ' 歲. ' || sex;
?? RETURN ?v_var;
END ;

DECLARE ?
?? Var ? VARCHAR ( 32 );
BEGIN
?? Var ?: = ?demo_fun( ' user1 ' ,? 30 ,?sex? => ? ' 男 ' );
??DBMS_OUTPUT.PUT_LINE( var );

?? Var ?: = ?demo_fun( ' user2 ' ,?age? => ? 40 ,?sex? => ? ' 男 ' );
??DBMS_OUTPUT.PUT_LINE( var );

?? Var ?: = ?demo_fun( ' user3 ' ,?sex? => ? ' 女 ' ,?age? => ? 20 );
??DBMS_OUTPUT.PUT_LINE( var );
END ;

?

無論采用哪一種參數傳遞方法，實際參數和形式參數之間的數據傳遞只有兩種方法：傳址法和傳值法。所謂傳址法是指在調用函數時，將實際參數的地址指針傳遞給形式參數，使形式參數和實際參數指向內存中的同一區域，從而實現參數數據的傳遞。這種方法又稱作參照法，即形式參數參照實際參數數據。輸入參數均采用傳址法傳遞數據。

?????? 傳值法是指將實際參數的數據拷貝到形式參數，而不是傳遞實際參數的地址。默認時，輸出參數和輸入 / 輸出參數均采用傳值法。在函數調用時， ORACLE 將實際參數數據拷貝到輸入 / 輸出參數，而當函數正常運行退出時，又將輸出形式參數和輸入 / 輸出形式參數數據拷貝到實際參數變量中。

?

3. 參數默認值

在 CREATE OR REPLACE FUNCTION 語句中聲明函數參數時可以使用 DEFAULT 關鍵字為輸入參數指定默認值。

?

例 5 ：

CREATE ? OR ? REPLACE ? FUNCTION ?demo_fun(
??Name? VARCHAR2 ,
??Age? INTEGER ,
??Sex? VARCHAR2 ? DEFAULT ? ' 男 ' )
?? RETURN ? VARCHAR2 ?
AS
??V_var? VARCHAR2 ( 32 );
BEGIN
??V_var?: = ?name || ' ： ' || TO_CHAR(age) || ' 歲. ' || sex;
?? RETURN ?v_var;
END ;

?

具有默認值的函數創建后，在函數調用時，如果沒有為具有默認值的參數提供實際參數值，函數將使用該參數的默認值。但當調用者為默認參數提供實際參數時，函數將使用實際參數值。在創建函數時，只能為輸入參數設置默認值，而不能為輸入 / 輸出參數設置默認值。

DECLARE

? var VARCHAR ( 32 );

BEGIN

? Var := demo_fun( 'user1' , 30 );

?DBMS_OUTPUT.PUT_LINE( var );

? Var := demo_fun( 'user2' , age => 40 );

?DBMS_OUTPUT.PUT_LINE( var );

? Var := demo_fun( 'user3' , sex => ' 女 ' , age => 20 );

?DBMS_OUTPUT.PUT_LINE( var);

END ;

6.3? 存儲過程

6.3.1 ? 創建過程

?

建立存儲過程

在 ORACLE SERVER 上建立存儲過程 , 可以被多個應用程序調用 , 可以向存儲過程傳遞參數 , 也可以向存儲過程傳回參數 .

?

創建過程語法 :

?

CREATE ? [ OR?REPLACE ] ? PROCEDURE ?procedure_name
( [ arg1?[?IN?|?OUT?|?IN?OUT? ] ]?type1? [ DEFAULT?value1 ] ,
? [ arg2?[?IN?|?OUT?|?IN?OUT? ] ]?type2? [ DEFAULT?value1 ] ],
?......
? [ argn?[?IN?|?OUT?|?IN?OUT? ] ]?typen? [ DEFAULT?valuen ] )
???? [ ?AUTHID?DEFINER?|?CURRENT_USER? ]
{? IS ? | ? AS ?}
?? < 聲明部分 > ?
BEGIN
?? < 執行部分 >
EXCEPTION
?? < 可選的異常錯誤處理程序 >
END ?procedure_name;

?

說明： 相關參數說明參見函數的語法說明。

?

例 6 ．用戶連接登記記錄；

?

CREATE ? TABLE ?logtable?(userid? VARCHAR2 ( 10 ),?logdate?date);

CREATE ? OR ? REPLACE ? PROCEDURE ?logexecution?
IS
BEGIN
INSERT ? INTO ?logtable?(userid,?logdate)? VALUES ?( USER ,?SYSDATE);
END ;

?

例 7 ．刪除指定員工記錄；

?

CREATE ? OR ? REPLACE
PROCEDURE ?DelEmp
(v_empno? IN ?employees.employee_id % TYPE)?
AS
No_result?EXCEPTION;
BEGIN
??? DELETE ? FROM ?employees? WHERE ?employee_id? = ?v_empno;
??? IF ?SQL % NOTFOUND? THEN
??????RAISE?no_result;
??? END ? IF ;
???DBMS_OUTPUT.PUT_LINE( ' 編碼為 ' || v_empno || ' 的員工已被刪除! ' );
EXCEPTION
??? WHEN ?no_result? THEN ?
??????DBMS_OUTPUT.PUT_LINE( ' 溫馨提示:你需要的數據不存在! ' );
??? WHEN ?OTHERS? THEN
??????DBMS_OUTPUT.PUT_LINE(SQLCODE || ' --- ' || SQLERRM);
END ?DelEmp;

?

例 8 ．插入員工記錄 :

?

CREATE ? OR ? REPLACE
PROCEDURE ?InsertEmp(
???v_empno????? in ?employees.employee_id % TYPE,
???v_firstname? in ?employees.first_name % TYPE,
???v_lastname?? in ?employees.last_name % TYPE,
???v_deptno???? in ?employees.department_id % TYPE
???)?
AS
???empno_remaining?EXCEPTION;
???PRAGMA?EXCEPTION_INIT(empno_remaining,? - 1 );
??? /* ?-1?是違反唯一約束條件的錯誤代碼? */
BEGIN
??? INSERT ? INTO ?EMPLOYEES(EMPLOYEE_ID,?FIRST_NAME,?LAST_NAME,?HIRE_DATE,DEPARTMENT_ID)
??? VALUES (v_empno,?v_firstname,v_lastname,?sysdate,?v_deptno);
???DBMS_OUTPUT.PUT_LINE( ' 溫馨提示:插入數據記錄成功! ' );
EXCEPTION
??? WHEN ?empno_remaining? THEN ?
??????DBMS_OUTPUT.PUT_LINE( ' 溫馨提示:違反數據完整性約束! ' );
??? WHEN ?OTHERS? THEN
??????DBMS_OUTPUT.PUT_LINE(SQLCODE || ' --- ' || SQLERRM);
END ?InsertEmp;

例 9 ．使用存儲過程向 departments 表中插入數據。

?

CREATE ? OR ? REPLACE
PROCEDURE ?insert_dept
??(v_dept_id? IN ?departments.department_id % TYPE,
???v_dept_name? IN ?departments.department_name % TYPE,
???v_mgr_id? IN ?departments.manager_id % TYPE,
???v_loc_id? IN ?departments.location_id % TYPE)
IS
???ept_null_error?EXCEPTION;
???PRAGMA?EXCEPTION_INIT(ept_null_error,? - 1400 );
???ept_no_loc_id?EXCEPTION;
???PRAGMA?EXCEPTION_INIT(ept_no_loc_id,? - 2291 );
BEGIN
??? INSERT ? INTO ?departments
???(department_id,?department_name,?manager_id,?location_id)
??? VALUES
???(v_dept_id,?v_dept_name,?v_mgr_id,?v_loc_id);
???DBMS_OUTPUT.PUT_LINE( ' 插入部門 ' || v_dept_id || ' 成功 ' );
EXCEPTION
??? WHEN ?DUP_VAL_ON_INDEX? THEN
??????RAISE_APPLICATION_ERROR( - 20000 ,? ' 部門編碼不能重復 ' );
??? WHEN ?ept_null_error? THEN
??????RAISE_APPLICATION_ERROR( - 20001 ,? ' 部門編碼、部門名稱不能為空 ' );
??? WHEN ?ept_no_loc_id? THEN
??????RAISE_APPLICATION_ERROR( - 20002 ,? ' 沒有該地點 ' );
END ?insert_dept;

/* 調用實例一:
DECLARE
???ept_20000?EXCEPTION;
???PRAGMA?EXCEPTION_INIT(ept_20000,?-20000);
???ept_20001?EXCEPTION;
???PRAGMA?EXCEPTION_INIT(ept_20001,?-20001);
???ept_20002?EXCEPTION;
???PRAGMA?EXCEPTION_INIT(ept_20002,?-20002);
BEGIN
???insert_dept(300,?'部門300',?100,?2400);
???insert_dept(310,?NULL,?100,?2400);
???insert_dept(310,?'部門310',?100,?900);
EXCEPTION
???WHEN?ept_20000?THEN
??????DBMS_OUTPUT.PUT_LINE('ept_20000部門編碼不能重復');
???WHEN?ept_20001?THEN
??????DBMS_OUTPUT.PUT_LINE('ept_20001部門編碼、部門名稱不能為空');
???WHEN?ept_20002?THEN
??????DBMS_OUTPUT.PUT_LINE('ept_20002沒有該地點');
???WHEN?OTHERS?THEN
??????DBMS_OUTPUT.PUT_LINE('others出現了其他異常錯誤');
END;

調用實例二：
DECLARE
???ept_20000?EXCEPTION;
???PRAGMA?EXCEPTION_INIT(ept_20000,?-20000);
???ept_20001?EXCEPTION;
???PRAGMA?EXCEPTION_INIT(ept_20001,?-20001);
???ept_20002?EXCEPTION;
???PRAGMA?EXCEPTION_INIT(ept_20002,?-20002);
BEGIN
???insert_dept(v_dept_name?=>?'部門310',?v_dept_id?=>?310,?
???????????????v_mgr_id?=>?100,?v_loc_id?=>?2400);
???insert_dept(320,?'部門320',?v_mgr_id?=>?100,?v_loc_id?=>?900);
EXCEPTION
???WHEN?ept_20000?THEN
??????DBMS_OUTPUT.PUT_LINE('ept_20000部門編碼不能重復');
???WHEN?ept_20001?THEN
??????DBMS_OUTPUT.PUT_LINE('ept_20001部門編碼、部門名稱不能為空');
???WHEN?ept_20002?THEN
??????DBMS_OUTPUT.PUT_LINE('ept_20002沒有該地點');
???WHEN?OTHERS?THEN
??????DBMS_OUTPUT.PUT_LINE('others出現了其他異常錯誤');
END;
*/

?

6.3.2 ? 調用存儲過程

?

?? ? 存儲過程建立完成后，只要通過授權，用戶就可以在 SQLPLUS 、 ORACLE 開發工具或第三方開發工具中來調用運行。對于參數的傳遞也有三種：按位置傳遞、按名稱傳遞和組合傳遞，傳遞方法與函數的一樣。 ORACLE 使用 EXECUTE 語句來實現對存儲過程的調用：

?

EXEC [ UTE ] ?procedure_name(?parameter1,?parameter2…);

?

例 10 ：

?

EXECUTE ?logexecution;

?

例 11 ：查詢指定員工記錄；

?

CREATE ? OR ? REPLACE
PROCEDURE ?QueryEmp
(v_empno? IN ??employees.employee_id % TYPE,
?v_ename?OUT?employees.first_name % TYPE,
?v_sal???OUT?employees.salary % TYPE)?
AS
BEGIN
??????? SELECT ?last_name? || ?last_name,?salary? INTO ?v_ename,?v_sal?
???? FROM ?employees?
???? WHERE ?employee_id? = ?v_empno;?
???????DBMS_OUTPUT.PUT_LINE( ' 溫馨提示:編碼為 ' || v_empno || ' 的員工已經查到! ' );
EXCEPTION
??????? WHEN ?NO_DATA_FOUND? THEN ?
??????DBMS_OUTPUT.PUT_LINE( ' 溫馨提示:你需要的數據不存在! ' );
?????? WHEN ?OTHERS? THEN ?
??????DBMS_OUTPUT.PUT_LINE(SQLCODE || ' --- ' || SQLERRM);
END ?QueryEmp;
-- 調用
? DECLARE
????v1?employees.first_name % TYPE;
????v2?employees.salary % TYPE;
? BEGIN
???QueryEmp( 100 ,?v1,?v2);
???DBMS_OUTPUT.PUT_LINE( ' 姓名: ' || v1);
???DBMS_OUTPUT.PUT_LINE( ' 工資: ' || v2);
???QueryEmp( 103 ,?v1,?v2);
???DBMS_OUTPUT.PUT_LINE( ' 姓名: ' || v1);
???DBMS_OUTPUT.PUT_LINE( ' 工資: ' || v2);
???QueryEmp( 104 ,?v1,?v2);
???DBMS_OUTPUT.PUT_LINE( ' 姓名: ' || v1);
???DBMS_OUTPUT.PUT_LINE( ' 工資: ' || v2);
END ;

?

例 12 ．計算指定部門的工資總和，并統計其中的職工數量。

?

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( ' 溫馨提示:你需要的數據不存在! ' );
??? WHEN ?OTHERS? THEN
??????DBMS_OUTPUT.PUT_LINE(SQLCODE || ' --- ' || SQLERRM);
END ?proc_demo;

DECLARE
V_num? NUMBER ;
V_sum? NUMBER ( 8 ,? 2 );
BEGIN
??Proc_demo( 30 ,?v_sum,?v_num);
DBMS_OUTPUT.PUT_LINE( ' 溫馨提示:30號部門工資總和： ' || v_sum || ' ,人數： ' || v_num);
??Proc_demo(sal_sum? => ?v_sum,?emp_count? => ?v_num);
DBMS_OUTPUT.PUT_LINE( ' 溫馨提示:10號部門工資總和： ' || v_sum || ' ,人數： ' || v_num);
END ;

?????? 在 PL/SQL 程序中還可以在塊內建立本地函數和過程，這些函數和過程不存儲在數據庫中，但可以在創建它們的 PL/SQL 程序中被重復調用。本地函數和過程在 PL/SQL 塊的聲明部分定義，它們的語法格式與存儲函數和過程相同，但不能使用 CREATE OR REPLACE 關鍵字。

?

例 13 ：建立本地過程，用于計算指定部門的工資總和，并統計其中的職工數量；

?

DECLARE
V_num? NUMBER ;
V_sum? NUMBER ( 8 ,? 2 );
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( ' 你需要的數據不存在! ' );
??? WHEN ?OTHERS? THEN ?
??????DBMS_OUTPUT.PUT_LINE(SQLCODE || ' --- ' || SQLERRM);
END ?proc_demo;
-- 調用方法：
BEGIN
????Proc_demo( 30 ,?v_sum,?v_num);
DBMS_OUTPUT.PUT_LINE( ' 30號部門工資總和： ' || v_sum || ' ，人數： ' || v_num);
????Proc_demo(sal_sum? => ?v_sum,?emp_count? => ?v_num);
DBMS_OUTPUT.PUT_LINE( ' 10號部門工資總和： ' || v_sum || ' ，人數： ' || v_num);
END ;

6.3.3?A UTHID

過程中的 AUTHID 指令可以告訴 ORACLE ，這個過程使用誰的權限運行．默任情況下，存儲過程會作為調用者的過程運行，但是具有設計者的特權．這稱為設計者權利運行．

?

例 14 ：建立過程，使用 AUTOID DEFINER ；

?

Connect?HR / qaz
DROP ? TABLE ?logtable;
CREATE ? table ?logtable?(userid? VARCHAR2 ( 10 ),?logdate?date);

CREATE ? OR ? REPLACE ? PROCEDURE ?logexecution?
????AUTHID?DEFINER
IS
BEGIN
??? INSERT ? INTO ?logtable?(userid,?logdate)? VALUES ?( USER ,?SYSDATE);
END ;

GRANT ? EXECUTE ? ON ?logexecution? TO ? PUBLIC ;

CONNECT? / ? AS ?SYSDBA
GRANT ?CONNECT? TO ?testuser1?IDENTIFIED? BY ?userpwd1;

CONNECT?testuser1 / userpwd1
INSERT ? INTO ?HR.LOGTABLE? VALUES ?( USER ,?SYSDATE);
EXECUTE ?HR.logexecution

CONNECT?HR / qaz
SELECT ? * ? FROM ?HR.logtable;

?

例 15 ：建立過程，使用 AUTOID CURRENT_USER ；

?

CONNECT?HR / qaz

CREATE ? OR ? REPLACE ? PROCEDURE ?logexecution?
??AUTHID? CURRENT_USER
IS
BEGIN
??? INSERT ? INTO ?logtable?(userid,?logdate)? VALUES ?( USER ,?SYSDATE);
END ;

GRANT ? EXECUTE ? ON ?logexecution? TO ? PUBLIC ;

CONNECT?testuser1 / userpwd1
INSERT ? INTO ?HR.LOGTABLE? VALUES ?( USER ,?SYSDATE);
EXECUTE ?HR.logexecution

?

6.3.4?P RAGMA AUTONOMOUS_TRANSACTION

?

ORACLE8i 可以支持事務處理中的事務處理的概念．這種子事務處理可以完成它自己的工作，獨立于父事務處理進行提交或者回滾．通過使用這種方法，開發者就能夠這樣的過程，無論父事務處理是提交還是回滾，它都可以成功執行．

?

例 16 ：建立過程，使用自動事務處理進行日志記錄；

?

DROP ? TABLE ?logtable;

CREATE ? TABLE ?logtable(
??Username? varchar2 ( 20 ),
??Dassate_time?date,
??Mege? varchar2 ( 60 )
);

CREATE ? TABLE ?temp_table(?N? number ?);

CREATE ? OR ? REPLACE ? PROCEDURE ?log_message(p_message? varchar2 )
?? AS
??PRAGMA?AUTONOMOUS_TRANSACTION;
BEGIN
?? INSERT ? INTO ?logtable? VALUES ?(? user ,?sysdate,?p_message?);
?? COMMIT ;
END ?log_message;

BEGIN
??Log_message?(‘About? to ? insert ? into ?temp_table‘);
?? INSERT ? INTO ?temp_table? VALUES ?( 1 );
??Log_message?(‘ Rollback ? to ? insert ? into ?temp_table‘);
?? ROLLBACK ;
END ;

SELECT ? * ? FROM ?logtable;
SELECT ? * ? FROM ?temp_table;

?

例 17 ：建立過程，沒有使用自動事務處理進行日志記錄；

?

CREATE ? OR ? REPLACE ? PROCEDURE ?log_message(p_message? varchar2 )
?? AS
BEGIN
?? INSERT ? INTO ?logtable? VALUES ?(? user ,?sysdate,?p_message?);
?? COMMIT ;
END ?log_message;

BEGIN
??Log_message?( ' About?to?insert?into?temp_table ' );
?? INSERT ? INTO ?temp_table? VALUES ?( 1 );
??Log_message?( ' Rollback?to?insert?into?temp_table ' );
?? ROLLBACK ;
END ;

SELECT ? * ? FROM ?logtable;
SELECT ? * ? FROM ?temp_table;

?

6.3.5 ? 開發存儲過程步驟

??? 開發存儲過程、函數、包及觸發器的步驟如下：

?

6.3.5 .1? 使用文字編輯處理軟件編輯存儲過程源碼

??? 使用文字編輯處理軟件編輯存儲過程源碼，要用類似 WORD 文字處理軟件進行編輯時，要將源碼存為文本格式。

?

6.3.5 .2? 在 SQLPLUS 或用調試工具將存儲過程程序進行解釋

??? 在 SQLPLUS 或用調試工具將存儲過程程序進行解釋；

??? 在 SQL> 下調試，可用 START 或 GET 等 ORACLE 命令來啟動解釋。如：

SQL>START c:\stat1.sql

??? 如果使用調式工具，可直接編輯和點擊相應的按鈕即可生成存儲過程。

?

6.3.5 .3? 調試源碼直到正確

??? 我們不能保證所寫的存儲過程達到一次就正確。所以這里的調式是每個程序員必須進行的工作之一。在 SQLPLUS 下來調式主要用的方法是：

l ???????? 使用 SHOW ERROR 命令來提示源碼的錯誤位置；

l ???????? 使用 user_errors 數據字典來查看各存儲過程的錯誤位置。

?

6.3.5 .4? 授權執行權給相關的用戶或角色

如果調式正確的存儲過程沒有進行授權，那就只有建立者本人才可以運行。所以作為應用系統的一部分的存儲過程也必須進行授權才能達到要求。在 SQL*PLUS 下可以用 GRANT 命令來進行存儲過程的運行授權。

?

GRANT 語法：

?

GRANT ?system_privilege? | ?role?
TO ? user ? | ?role? | ? PUBLIC ? [ WITH?ADMIN?OPTION ]

GRANT ?object_privilege? | ? ALL ? ON ? schema .object?
TO ? user ? | ?role? | ? PUBLIC ? [ WITH?GRANT?OPTION ]

-- 例子：

CREATE ? OR ? REPLACE ? PUBLIC ?SYNONYM?dbms_job? FOR ?dbms_job

GRANT ? EXECUTE ? ON ?dbms_job? TO ? PUBLIC ? WITH ? GRANT ? OPTION

?

6.3.5 .5? 與過程相關數據字典

?

USER_SOURCE, ALL_SOURCE, DBA_SOURCE, USER_ERRORS,

ALL_PROCEDURES,USER_OBJECTS,ALL_OBJECTS,DBA_OBJECTS

?

相關的權限 :

CREATE ANY PROCEDURE

DROP ANY PROCEDURE

?

在 SQL*PLUS 中，可以用 DESCRIBE 命令查看過程的名字及其參數表。

?

DESC[RIBE] Procedure_name;

?

6.3.6 ? 刪除過程和函數

?

1 ．刪除過程

可以使用 DROP PROCEDURE 命令對不需要的過程進行刪除，語法如下：

DROP PROCEDURE [user.]Procudure_name;

?

2 ．刪除函數

可以使用 DROP FUNCTION 命令對不需要的函數進行刪除，語法如下：

?

DROP ? FUNCTION ? [ user. ] Function_name;

-- 刪除上面實例創建的存儲過程與函數
DROP ? PROCEDURE ?logexecution;
DROP ? PROCEDURE ?delemp;
DROP ? PROCEDURE ?insertemp;
DROP ? PROCEDURE ?fireemp;
DROP ? PROCEDURE ?queryemp;
DROP ? PROCEDURE ?proc_demo;
DROP ? PROCEDURE ?log_message;
DROP ? FUNCTION ?demo_fun;
DROP ? FUNCTION ?get_salary;

?

6.3.7 ??????? 過程與函數的比較

?

使用過程與函數具有如下優點：

?

1 、共同使用的代碼可以只需要被編寫和測試一次，而被需要該代碼的任何應用程序（如： .NET 、 C++ 、 JAVA 、 VB 程序，也可以是 DLL 庫）調用。

2 、這種集中編寫、集中維護更新、大家共享（或重用）的方法，簡化了應用程序的開發和維護，提高了效率與性能。

3 、這種模塊化的方法，使得可以將一個復雜的問題、大的程序逐步簡化成幾個簡單的、小的程序部分，進行分別編寫、調試。因此使程序的結構清晰、簡單，也容易實現。

4 、可以在各個開發者之間提供處理數據、控制流程、提示信息等方面的一致性。

5 、節省內存空間。它們以一種壓縮的形式被存儲在外存中，當被調用時才被放入內存進行處理。并且，如果多個用戶要執行相同的過程或函數時，就只需要在內存中加載一個該過程或函數。

6 、提高數據的安全性與完整性。通過把一些對數據的操作放到過程或函數中，就可以通過是否授予用戶有執行該過程或的權限，來限制某些用戶對數據進行這些操作。

?

過程與函數的相同功能有：

1、? 都使用 IN 模式的參數傳入數據、 OUT 模式的參數返回數據。

2、? 輸入參數都可以接受默認值，都可以傳值或傳引導。

3、? 調用時的實際參數都可以使用位置表示法、名稱表示法或組合方法。

4、? 都有聲明部分、執行部分和異常處理部分。

5、? 其管理過程都有創建、編譯、授權、刪除、顯示依賴關系等。

?

使用過程與函數的原則：

1 、如果需要返回多個值和不返回值，就使用過程；如果只需要返回一個值，就使用函數。

2 、過程一般用于執行一個指定的動作，函數一般用于計算和返回一個值。

3 、可以 SQL 語句內部（如表達式）調用函數來完成復雜的計算問題，但不能調用過程。所以這是函數的特色。

? 2011? EricHu

原創作品，轉貼請注明作者和出處，留此信息。

?

------------------------------------------------

cnBlobs: http://www.cnblogs.com/huyong/
CSDN ： http://blog.csdn.net/chinahuyong ?

?

作者： EricHu （ DB 、 C\S 、 B\S 、 WebService 、 WCF 、 PM 等）
出處： http://www.cnblogs.com/huyong/

Q Q ： 80368704?? E-Mail: 80368704@qq.com
本博文歡迎大家瀏覽和轉載，但未經作者同意必須保留此段聲明，且在文章頁面明顯位置給出原文連接，在『參考』的文章中，我會表明參考的文章來源，尊重他人版權。若您發現我侵犯了您的版權，請及時與我聯系。
更多文章請看 ? [ 置頂 ] 索引貼 —— （不斷更新中）

?

刷新評論刷新頁面返回頂部

博客園首頁博問新聞閃存程序員招聘知識庫

About

www.spiga.com.mx

Copyright ?2014 yonghu86

博客園

ORACLE PL/SQL編程之六：把過程與函數說透(窮追猛打，把根兒都拔起!)

更多文章、技術交流、商務合作、聯系博主

微信掃碼或搜索：z360901061

微信掃一掃加我為好友

QQ號聯系： 360901061

您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。

【本文對您有幫助就好】元

2元

5元

10元

20元

自定義

亚洲免费在线-亚洲免费在线播放-亚洲免费在线观看-亚洲免费在线观看视频-亚洲免费在线看-亚洲免费在线视频

6.1? 引言

6.2? 創建函數

6.3? 存儲過程

6.3.1 ? 創建過程

6.3.2 ? 調用存儲過程

6.3.3?A UTHID

6.3.4?P RAGMA AUTONOMOUS_TRANSACTION

6.3.5 ? 開發存儲過程步驟

6.3.5 .1? 使用文字編輯處理軟件編輯存儲過程源碼

6.3.5 .2? 在 SQLPLUS 或用調試工具將存儲過程程序進行解釋

6.3.5 .3? 調試源碼直到正確

6.3.5 .4? 授權執行權給相關的用戶或角色

6.3.5 .5? 與過程相關數據字典

6.3.6 ? 刪除過程和函數

6.3.7 ??????? 過程與函數的比較

About