国产精品久久久对白,国产精品色婷婷视频,欧美第一黄色网

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

ORACLE PL/SQL 編程之六：

把過(guò)程與函數(shù)說(shuō)透 ( 窮追猛打，把根兒都拔起 !)

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

?

本篇主要內(nèi)容如下：

6.1? 引言

6.2? 創(chuàng)建函數(shù)

6.3? 存儲(chǔ)過(guò)程

6.3.1 ? 創(chuàng)建過(guò)程

6.3.2 ? 調(diào)用存儲(chǔ)過(guò)程

6.3.3?A UTHID

6.3.4?P RAGMA AUTONOMOUS_TRANSACTION

6.3.5 ? 開(kāi)發(fā)存儲(chǔ)過(guò)程步驟

6.3.6 ? 刪除過(guò)程和函數(shù)

6.3.7 ? 過(guò)程與函數(shù)的比較

?

6.1? 引言

過(guò)程與函數(shù)（另外還有包與觸發(fā)器）是命名的 PL/SQL 塊（也是用戶的方案對(duì)象），被編譯后存儲(chǔ)在數(shù)據(jù)庫(kù)中，以備執(zhí)行。因此，其它 PL/SQL 塊可以按名稱來(lái)使用他們。所以，可以將商業(yè)邏輯、企業(yè)規(guī)則寫成函數(shù)或過(guò)程保存到數(shù)據(jù)庫(kù)中，以便共享。

過(guò)程和函數(shù)統(tǒng)稱為 PL/SQL 子程序，他們是被命名的 PL/SQL 塊，均存儲(chǔ)在數(shù)據(jù)庫(kù)中，并通過(guò)輸入、輸出參數(shù)或輸入 / 輸出參數(shù)與其調(diào)用者交換信息。過(guò)程和函數(shù)的唯一區(qū)別是函數(shù)總向調(diào)用者返回?cái)?shù)據(jù)，而過(guò)程則不返回?cái)?shù)據(jù)。在本節(jié)中，主要介紹：

1． ?? 創(chuàng)建存儲(chǔ)過(guò)程和函數(shù)。

2． ?? 正確使用系統(tǒng)級(jí)的異常處理和用戶定義的異常處理。

3． ?? 建立和管理存儲(chǔ)過(guò)程和函數(shù)。

6.2? 創(chuàng)建函數(shù)

1. 創(chuàng)建函數(shù)

?

語(yǔ)法如下：

?

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
????執(zhí)行部分
???? RETURN ?expression
EXCEPTION
????異常處理部分
END ?function_name;

?

l ???????? IN,OUT,IN OUT 是形參的模式。若省略，則為 IN 模式。 IN 模式的形參只能將實(shí)參傳遞給形參，進(jìn)入函數(shù)內(nèi)部，但只能讀不能寫，函數(shù)返回時(shí)實(shí)參的值不變。 OUT 模式的形參會(huì)忽略調(diào)用時(shí)的實(shí)參值（或說(shuō)該形參的初始值總是 NULL ），但在函數(shù)內(nèi)部可以被讀或?qū)懀瘮?shù)返回時(shí)形參的值會(huì)賦予給實(shí)參。 IN OUT 具有前兩種模式的特性，即調(diào)用時(shí)，實(shí)參的值總是傳遞給形參，結(jié)束時(shí)，形參的值傳遞給實(shí)參。調(diào)用時(shí)，對(duì)于 IN 模式的實(shí)參可以是常量或變量，但對(duì)于 OUT 和 IN OUT 模式的實(shí)參必須是變量。
?

l ???????? 一般，只有在確認(rèn) function_name 函數(shù)是新函數(shù)或是要更新的函數(shù)時(shí)，才使用 OR REPALCE 關(guān)鍵字，否則容易刪除有用的函數(shù)。

?

例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( ' 你需要的數(shù)據(jù)不存在! ' );
??? WHEN ?OTHERS? THEN ?
??????DBMS_OUTPUT.PUT_LINE(SQLCODE || ' --- ' || SQLERRM);
END ?get_salary;

?

2. 函數(shù)的調(diào)用

函數(shù)聲明時(shí)所定義的參數(shù)稱為形式參數(shù)，應(yīng)用程序調(diào)用時(shí)為函數(shù)傳遞的參數(shù)稱為實(shí)際參數(shù)。應(yīng)用程序在調(diào)用函數(shù)時(shí)，可以使用以下三種方法向函數(shù)傳遞參數(shù)：

?

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

即在調(diào)用時(shí)按形參的排列順序，依次寫出實(shí)參的名稱，而將形參與實(shí)參關(guān)聯(lián)起來(lái)進(jìn)行傳遞。用這種方法進(jìn)行調(diào)用，形參與實(shí)參的名稱是相互獨(dú)立，沒(méi)有關(guān)系，強(qiáng)調(diào)次序才是重要的。

格式為：

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

?

例 2 ：計(jì)算某部門的工資總和：

?

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

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

即在調(diào)用時(shí)按形參的名稱與實(shí)參的名稱，寫出實(shí)參對(duì)應(yīng)的形參，而將形參與實(shí)參關(guān)聯(lián)起來(lái)進(jìn)行傳遞。這種方法，形參與實(shí)參的名稱是相互獨(dú)立的，沒(méi)有關(guān)系，名稱的對(duì)應(yīng)關(guān)系才是最重要的，次序并不重要。

格式為：

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

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

在這種格式中，形勢(shì)參數(shù)與實(shí)際參數(shù)成對(duì)出現(xiàn)，相互間關(guān)系唯一確定，所以參數(shù)的順序可以任意排列。

例 3 ：計(jì)算某部門的工資總和：

?

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

第三種參數(shù)傳遞格式：組合傳遞。

即在調(diào)用一個(gè)函數(shù)時(shí)，同時(shí)使用位置表示法和名稱表示法為函數(shù)傳遞參數(shù)。采用這種參數(shù)傳遞方法時(shí)，使用位置表示法所傳遞的參數(shù)必須放在名稱表示法所傳遞的參數(shù)前面。也就是說(shuō)，無(wú)論函數(shù)具有多少個(gè)參數(shù)，只要其中有一個(gè)參數(shù)使用名稱表示法，其后所有的參數(shù)都必須使用名稱表示法。

?

例 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 ;

?

無(wú)論采用哪一種參數(shù)傳遞方法，實(shí)際參數(shù)和形式參數(shù)之間的數(shù)據(jù)傳遞只有兩種方法：傳址法和傳值法。所謂傳址法是指在調(diào)用函數(shù)時(shí)，將實(shí)際參數(shù)的地址指針傳遞給形式參數(shù)，使形式參數(shù)和實(shí)際參數(shù)指向內(nèi)存中的同一區(qū)域，從而實(shí)現(xiàn)參數(shù)數(shù)據(jù)的傳遞。這種方法又稱作參照法，即形式參數(shù)參照實(shí)際參數(shù)數(shù)據(jù)。輸入?yún)?shù)均采用傳址法傳遞數(shù)據(jù)。

?????? 傳值法是指將實(shí)際參數(shù)的數(shù)據(jù)拷貝到形式參數(shù)，而不是傳遞實(shí)際參數(shù)的地址。默認(rèn)時(shí)，輸出參數(shù)和輸入 / 輸出參數(shù)均采用傳值法。在函數(shù)調(diào)用時(shí)， ORACLE 將實(shí)際參數(shù)數(shù)據(jù)拷貝到輸入 / 輸出參數(shù)，而當(dāng)函數(shù)正常運(yùn)行退出時(shí)，又將輸出形式參數(shù)和輸入 / 輸出形式參數(shù)數(shù)據(jù)拷貝到實(shí)際參數(shù)變量中。

?

3. 參數(shù)默認(rèn)值

在 CREATE OR REPLACE FUNCTION 語(yǔ)句中聲明函數(shù)參數(shù)時(shí)可以使用 DEFAULT 關(guān)鍵字為輸入?yún)?shù)指定默認(rèn)值。

?

例 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 ;

?

具有默認(rèn)值的函數(shù)創(chuàng)建后，在函數(shù)調(diào)用時(shí)，如果沒(méi)有為具有默認(rèn)值的參數(shù)提供實(shí)際參數(shù)值，函數(shù)將使用該參數(shù)的默認(rèn)值。但當(dāng)調(diào)用者為默認(rèn)參數(shù)提供實(shí)際參數(shù)時(shí)，函數(shù)將使用實(shí)際參數(shù)值。在創(chuàng)建函數(shù)時(shí)，只能為輸入?yún)?shù)設(shè)置默認(rèn)值，而不能為輸入 / 輸出參數(shù)設(shè)置默認(rèn)值。

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? 存儲(chǔ)過(guò)程

6.3.1 ? 創(chuàng)建過(guò)程

?

建立存儲(chǔ)過(guò)程

在 ORACLE SERVER 上建立存儲(chǔ)過(guò)程 , 可以被多個(gè)應(yīng)用程序調(diào)用 , 可以向存儲(chǔ)過(guò)程傳遞參數(shù) , 也可以向存儲(chǔ)過(guò)程傳回參數(shù) .

?

創(chuàng)建過(guò)程語(yǔ)法 :

?

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
?? < 執(zhí)行部分 >
EXCEPTION
?? < 可選的異常錯(cuò)誤處理程序 >
END ?procedure_name;

?

說(shuō)明： 相關(guān)參數(shù)說(shuō)明參見(jiàn)函數(shù)的語(yǔ)法說(shuō)明。

?

例 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( ' 溫馨提示:你需要的數(shù)據(jù)不存在! ' );
??? 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?是違反唯一約束條件的錯(cuò)誤代碼? */
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( ' 溫馨提示:插入數(shù)據(jù)記錄成功! ' );
EXCEPTION
??? WHEN ?empno_remaining? THEN ?
??????DBMS_OUTPUT.PUT_LINE( ' 溫馨提示:違反數(shù)據(jù)完整性約束! ' );
??? WHEN ?OTHERS? THEN
??????DBMS_OUTPUT.PUT_LINE(SQLCODE || ' --- ' || SQLERRM);
END ?InsertEmp;

例 9 ．使用存儲(chǔ)過(guò)程向 departments 表中插入數(shù)據(jù)。

?

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 ,? ' 部門編碼不能重復(fù) ' );
??? WHEN ?ept_null_error? THEN
??????RAISE_APPLICATION_ERROR( - 20001 ,? ' 部門編碼、部門名稱不能為空 ' );
??? WHEN ?ept_no_loc_id? THEN
??????RAISE_APPLICATION_ERROR( - 20002 ,? ' 沒(méi)有該地點(diǎn) ' );
END ?insert_dept;

/* 調(diào)用實(shí)例一:
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部門編碼不能重復(fù)');
???WHEN?ept_20001?THEN
??????DBMS_OUTPUT.PUT_LINE('ept_20001部門編碼、部門名稱不能為空');
???WHEN?ept_20002?THEN
??????DBMS_OUTPUT.PUT_LINE('ept_20002沒(méi)有該地點(diǎn)');
???WHEN?OTHERS?THEN
??????DBMS_OUTPUT.PUT_LINE('others出現(xiàn)了其他異常錯(cuò)誤');
END;

調(diào)用實(shí)例二：
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部門編碼不能重復(fù)');
???WHEN?ept_20001?THEN
??????DBMS_OUTPUT.PUT_LINE('ept_20001部門編碼、部門名稱不能為空');
???WHEN?ept_20002?THEN
??????DBMS_OUTPUT.PUT_LINE('ept_20002沒(méi)有該地點(diǎn)');
???WHEN?OTHERS?THEN
??????DBMS_OUTPUT.PUT_LINE('others出現(xiàn)了其他異常錯(cuò)誤');
END;
*/

?

6.3.2 ? 調(diào)用存儲(chǔ)過(guò)程

?

?? ? 存儲(chǔ)過(guò)程建立完成后，只要通過(guò)授權(quán)，用戶就可以在 SQLPLUS 、 ORACLE 開(kāi)發(fā)工具或第三方開(kāi)發(fā)工具中來(lái)調(diào)用運(yùn)行。對(duì)于參數(shù)的傳遞也有三種：按位置傳遞、按名稱傳遞和組合傳遞，傳遞方法與函數(shù)的一樣。 ORACLE 使用 EXECUTE 語(yǔ)句來(lái)實(shí)現(xiàn)對(duì)存儲(chǔ)過(guò)程的調(diào)用：

?

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 || ' 的員工已經(jīng)查到! ' );
EXCEPTION
??????? WHEN ?NO_DATA_FOUND? THEN ?
??????DBMS_OUTPUT.PUT_LINE( ' 溫馨提示:你需要的數(shù)據(jù)不存在! ' );
?????? WHEN ?OTHERS? THEN ?
??????DBMS_OUTPUT.PUT_LINE(SQLCODE || ' --- ' || SQLERRM);
END ?QueryEmp;
-- 調(diào)用
? 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 ．計(jì)算指定部門的工資總和，并統(tǒng)計(jì)其中的職工數(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;

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

?????? 在 PL/SQL 程序中還可以在塊內(nèi)建立本地函數(shù)和過(guò)程，這些函數(shù)和過(guò)程不存儲(chǔ)在數(shù)據(jù)庫(kù)中，但可以在創(chuàng)建它們的 PL/SQL 程序中被重復(fù)調(diào)用。本地函數(shù)和過(guò)程在 PL/SQL 塊的聲明部分定義，它們的語(yǔ)法格式與存儲(chǔ)函數(shù)和過(guò)程相同，但不能使用 CREATE OR REPLACE 關(guān)鍵字。

?

例 13 ：建立本地過(guò)程，用于計(jì)算指定部門的工資總和，并統(tǒng)計(jì)其中的職工數(shù)量；

?

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

6.3.3?A UTHID

過(guò)程中的 AUTHID 指令可以告訴 ORACLE ，這個(gè)過(guò)程使用誰(shuí)的權(quán)限運(yùn)行．默任情況下，存儲(chǔ)過(guò)程會(huì)作為調(diào)用者的過(guò)程運(yùn)行，但是具有設(shè)計(jì)者的特權(quán)．這稱為設(shè)計(jì)者權(quán)利運(yùn)行．

?

例 14 ：建立過(guò)程，使用 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 ：建立過(guò)程，使用 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 可以支持事務(wù)處理中的事務(wù)處理的概念．這種子事務(wù)處理可以完成它自己的工作，獨(dú)立于父事務(wù)處理進(jìn)行提交或者回滾．通過(guò)使用這種方法，開(kāi)發(fā)者就能夠這樣的過(guò)程，無(wú)論父事務(wù)處理是提交還是回滾，它都可以成功執(zhí)行．

?

例 16 ：建立過(guò)程，使用自動(dòng)事務(wù)處理進(jìn)行日志記錄；

?

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 ：建立過(guò)程，沒(méi)有使用自動(dòng)事務(wù)處理進(jìn)行日志記錄；

?

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 ? 開(kāi)發(fā)存儲(chǔ)過(guò)程步驟

??? 開(kāi)發(fā)存儲(chǔ)過(guò)程、函數(shù)、包及觸發(fā)器的步驟如下：

?

6.3.5 .1? 使用文字編輯處理軟件編輯存儲(chǔ)過(guò)程源碼

??? 使用文字編輯處理軟件編輯存儲(chǔ)過(guò)程源碼，要用類似 WORD 文字處理軟件進(jìn)行編輯時(shí)，要將源碼存為文本格式。

?

6.3.5 .2? 在 SQLPLUS 或用調(diào)試工具將存儲(chǔ)過(guò)程程序進(jìn)行解釋

??? 在 SQLPLUS 或用調(diào)試工具將存儲(chǔ)過(guò)程程序進(jìn)行解釋；

??? 在 SQL> 下調(diào)試，可用 START 或 GET 等 ORACLE 命令來(lái)啟動(dòng)解釋。如：

SQL>START c:\stat1.sql

??? 如果使用調(diào)式工具，可直接編輯和點(diǎn)擊相應(yīng)的按鈕即可生成存儲(chǔ)過(guò)程。

?

6.3.5 .3? 調(diào)試源碼直到正確

??? 我們不能保證所寫的存儲(chǔ)過(guò)程達(dá)到一次就正確。所以這里的調(diào)式是每個(gè)程序員必須進(jìn)行的工作之一。在 SQLPLUS 下來(lái)調(diào)式主要用的方法是：

l ???????? 使用 SHOW ERROR 命令來(lái)提示源碼的錯(cuò)誤位置；

l ???????? 使用 user_errors 數(shù)據(jù)字典來(lái)查看各存儲(chǔ)過(guò)程的錯(cuò)誤位置。

?

6.3.5 .4? 授權(quán)執(zhí)行權(quán)給相關(guān)的用戶或角色

如果調(diào)式正確的存儲(chǔ)過(guò)程沒(méi)有進(jìn)行授權(quán)，那就只有建立者本人才可以運(yùn)行。所以作為應(yīng)用系統(tǒng)的一部分的存儲(chǔ)過(guò)程也必須進(jìn)行授權(quán)才能達(dá)到要求。在 SQL*PLUS 下可以用 GRANT 命令來(lái)進(jìn)行存儲(chǔ)過(guò)程的運(yùn)行授權(quán)。

?

GRANT 語(yǔ)法：

?

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? 與過(guò)程相關(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 命令查看過(guò)程的名字及其參數(shù)表。

?

DESC[RIBE] Procedure_name;

?

6.3.6 ? 刪除過(guò)程和函數(shù)

?

1 ．刪除過(guò)程

可以使用 DROP PROCEDURE 命令對(duì)不需要的過(guò)程進(jìn)行刪除，語(yǔ)法如下：

DROP PROCEDURE [user.]Procudure_name;

?

2 ．刪除函數(shù)

可以使用 DROP FUNCTION 命令對(duì)不需要的函數(shù)進(jìn)行刪除，語(yǔ)法如下：

?

DROP ? FUNCTION ? [ user. ] Function_name;

-- 刪除上面實(shí)例創(chuàng)建的存儲(chǔ)過(guò)程與函數(shù)
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 ??????? 過(guò)程與函數(shù)的比較

?

使用過(guò)程與函數(shù)具有如下優(yōu)點(diǎn)：

?

1 、共同使用的代碼可以只需要被編寫和測(cè)試一次，而被需要該代碼的任何應(yīng)用程序（如： .NET 、 C++ 、 JAVA 、 VB 程序，也可以是 DLL 庫(kù)）調(diào)用。

2 、這種集中編寫、集中維護(hù)更新、大家共享（或重用）的方法，簡(jiǎn)化了應(yīng)用程序的開(kāi)發(fā)和維護(hù)，提高了效率與性能。

3 、這種模塊化的方法，使得可以將一個(gè)復(fù)雜的問(wèn)題、大的程序逐步簡(jiǎn)化成幾個(gè)簡(jiǎn)單的、小的程序部分，進(jìn)行分別編寫、調(diào)試。因此使程序的結(jié)構(gòu)清晰、簡(jiǎn)單，也容易實(shí)現(xiàn)。

4 、可以在各個(gè)開(kāi)發(fā)者之間提供處理數(shù)據(jù)、控制流程、提示信息等方面的一致性。

5 、節(jié)省內(nèi)存空間。它們以一種壓縮的形式被存儲(chǔ)在外存中，當(dāng)被調(diào)用時(shí)才被放入內(nèi)存進(jìn)行處理。并且，如果多個(gè)用戶要執(zhí)行相同的過(guò)程或函數(shù)時(shí)，就只需要在內(nèi)存中加載一個(gè)該過(guò)程或函數(shù)。

6 、提高數(shù)據(jù)的安全性與完整性。通過(guò)把一些對(duì)數(shù)據(jù)的操作放到過(guò)程或函數(shù)中，就可以通過(guò)是否授予用戶有執(zhí)行該過(guò)程或的權(quán)限，來(lái)限制某些用戶對(duì)數(shù)據(jù)進(jìn)行這些操作。

?

過(guò)程與函數(shù)的相同功能有：

1、? 都使用 IN 模式的參數(shù)傳入數(shù)據(jù)、 OUT 模式的參數(shù)返回?cái)?shù)據(jù)。

2、? 輸入?yún)?shù)都可以接受默認(rèn)值，都可以傳值或傳引導(dǎo)。

3、? 調(diào)用時(shí)的實(shí)際參數(shù)都可以使用位置表示法、名稱表示法或組合方法。

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

5、? 其管理過(guò)程都有創(chuàng)建、編譯、授權(quán)、刪除、顯示依賴關(guān)系等。

?

使用過(guò)程與函數(shù)的原則：

1 、如果需要返回多個(gè)值和不返回值，就使用過(guò)程；如果只需要返回一個(gè)值，就使用函數(shù)。

2 、過(guò)程一般用于執(zhí)行一個(gè)指定的動(dòng)作，函數(shù)一般用于計(jì)算和返回一個(gè)值。

3 、可以 SQL 語(yǔ)句內(nèi)部（如表達(dá)式）調(diào)用函數(shù)來(lái)完成復(fù)雜的計(jì)算問(wèn)題，但不能調(diào)用過(guò)程。所以這是函數(shù)的特色。

? 2011? EricHu

原創(chuàng)作品，轉(zhuǎn)貼請(qǐng)注明作者和出處，留此信息。

?

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

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
本博文歡迎大家瀏覽和轉(zhuǎn)載，但未經(jīng)作者同意必須保留此段聲明，且在文章頁(yè)面明顯位置給出原文連接，在『參考』的文章中，我會(huì)表明參考的文章來(lái)源，尊重他人版權(quán)。若您發(fā)現(xiàn)我侵犯了您的版權(quán)，請(qǐng)及時(shí)與我聯(lián)系。
更多文章請(qǐng)看 ? [ 置頂 ] 索引貼 —— （不斷更新中）

?

刷新評(píng)論刷新頁(yè)面返回頂部

博客園首頁(yè) 博問(wèn) 新聞閃存程序員招聘知識(shí)庫(kù)

About

www.spiga.com.mx

Copyright ?2014 yonghu86

博客園

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

更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主

微信掃碼或搜索：z360901061

微信掃一掃加我為好友

QQ號(hào)聯(lián)系： 360901061

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

【本文對(duì)您有幫助就好】元

2元

5元

10元

20元

自定義

日韩久久久精品,亚洲精品久久久久久久久久久,亚洲欧美一区二区三区国产精品 ,一区二区福利

6.1? 引言

6.2? 創(chuàng)建函數(shù)

6.3? 存儲(chǔ)過(guò)程

6.3.1 ? 創(chuàng)建過(guò)程

6.3.2 ? 調(diào)用存儲(chǔ)過(guò)程

6.3.3?A UTHID

6.3.4?P RAGMA AUTONOMOUS_TRANSACTION

6.3.5 ? 開(kāi)發(fā)存儲(chǔ)過(guò)程步驟

6.3.5 .1? 使用文字編輯處理軟件編輯存儲(chǔ)過(guò)程源碼

6.3.5 .2? 在 SQLPLUS 或用調(diào)試工具將存儲(chǔ)過(guò)程程序進(jìn)行解釋

6.3.5 .3? 調(diào)試源碼直到正確

6.3.5 .4? 授權(quán)執(zhí)行權(quán)給相關(guān)的用戶或角色

6.3.5 .5? 與過(guò)程相關(guān)數(shù)據(jù)字典

6.3.6 ? 刪除過(guò)程和函數(shù)

6.3.7 ??????? 過(guò)程與函數(shù)的比較

About