DB中存儲(chǔ)過(guò)程和函數(shù)的示例分析

這篇文章給大家分享的是有關(guān)DB中存儲(chǔ)過(guò)程和函數(shù)的示例分析的內(nèi)容。小編覺(jué)得挺實(shí)用的，因此分享給大家做個(gè)參考，一起跟隨小編過(guò)來(lái)看看吧。

勐臘ssl適用于網(wǎng)站、小程序/APP、API接口等需要進(jìn)行數(shù)據(jù)傳輸應(yīng)用場(chǎng)景，ssl證書(shū)未來(lái)市場(chǎng)廣闊！成為成都創(chuàng)新互聯(lián)公司的ssl證書(shū)銷(xiāo)售渠道，可以享受市場(chǎng)價(jià)格4-6折優(yōu)惠！如果有意向歡迎電話聯(lián)系或者加微信：18980820575（備注：SSL證書(shū)合作）期待與您的合作！

6.1 引言

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

過(guò)程和函數(shù)統(tǒng)稱(chē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í)的異常處理和用戶(hù)定義的異常處理。

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

    <類(lèi)型.變量的聲明部分>
BEGIN

    執(zhí)行部分

    RETURN expression

EXCEPTION

    異常處理部分
END function_name;

l IN,OUT,IN
OUT是形參的模式。若省略，則為IN模式。IN模式的形參只能將實(shí)參傳遞給形參，進(jìn)入函數(shù)內(nèi)部，但只能讀不能寫(xiě)，函數(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. 獲取某部門(mén)的工資總和：

--獲取某部門(mén)的工資總和
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ù)稱(chēng)為形式參數(shù)，應(yīng)用程序調(diào)用時(shí)為函數(shù)傳遞的參數(shù)稱(chēng)為實(shí)際參數(shù)。應(yīng)用程序在調(diào)用函數(shù)時(shí)，可以使用以下三種方法向函數(shù)傳遞參數(shù)：

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

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

格式為：

argument_value1[,argument_value2 …]

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

DECLARE

  V_num NUMBER;

  V_sum NUMBER;
BEGIN

  V_sum :=get_salary(10, v_num);

  DBMS_OUTPUT.PUT_LINE('部門(mén)號(hào)為:10的工資總和：'||v_sum||'，人數(shù)為：'||v_num);
END;

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

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

格式為：

argument => parameter [,…]

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

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

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

DECLARE

  V_num NUMBER;

    V_sum NUMBER;
BEGIN

    V_sum :=get_salary(emp_count => v_num, dept_no => 10);

    DBMS_OUTPUT.PUT_LINE('部門(mén)號(hào)為:10的工資總和：'||v_sum||'，人數(shù)為：'||v_num);
END;

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

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

例4：

CREATE OR REPLACE FUNCTION demo_fun(

  Name VARCHAR2,--注意VARCHAR2不能給精度，如：VARCHAR2(10)，其它類(lèi)似
  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ù)的傳遞。這種方法又稱(chēng)作參照法，即形式參數(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ǔ)法:

DB中存儲(chǔ)過(guò)程和函數(shù)的示例分析 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．用戶(hù)連接登記記錄；

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('插入部門(mén)'||v_dept_id||'成功');

EXCEPTION

   WHEN DUP_VAL_ON_INDEX THEN

      RAISE_APPLICATION_ERROR(-20000, '部門(mén)編碼不能重復(fù)');

   WHEN ept_null_error THEN

      RAISE_APPLICATION_ERROR(-20001, '部門(mén)編碼、部門(mén)名稱(chēng)不能為空');

   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, '部門(mén)300', 100, 2400);

   insert_dept(310, NULL, 100, 2400);

   insert_dept(310, '部門(mén)310', 100, 900);

EXCEPTION

   WHEN ept_20000 THEN

      DBMS_OUTPUT.PUT_LINE('ept_20000部門(mén)編碼不能重復(fù)');

   WHEN ept_20001 THEN

      DBMS_OUTPUT.PUT_LINE('ept_20001部門(mén)編碼、部門(mén)名稱(chēng)不能為空');

   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 => '部門(mén)310', v_dept_id => 310,

               v_mgr_id => 100, v_loc_id => 2400);

   insert_dept(320, '部門(mén)320', v_mgr_id => 100, v_loc_id => 900);

EXCEPTION

   WHEN ept_20000 THEN

      DBMS_OUTPUT.PUT_LINE('ept_20000部門(mén)編碼不能重復(fù)');

   WHEN ept_20001 THEN

      DBMS_OUTPUT.PUT_LINE('ept_20001部門(mén)編碼、部門(mén)名稱(chēng)不能為空');

   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;
*/ DB中存儲(chǔ)過(guò)程和函數(shù)的示例分析

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

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

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

例10：

EXECUTE logexecution;

例11：查詢(xún)指定員工記錄；

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ì)算指定部門(mén)的工資總和，并統(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)部門(mén)工資總和：'||v_sum||',人數(shù)：'||v_num);

  Proc_demo(sal_sum => v_sum, emp_count => v_num);

DBMS_OUTPUT.PUT_LINE('溫馨提示:10號(hào)部門(mén)工資總和：'||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ì)算指定部門(mén)的工資總和，并統(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)部門(mén)工資總和：'||v_sum||'，人數(shù)：'||v_num);

    Proc_demo(sal_sum => v_sum, emp_count => v_num);

DBMS_OUTPUT.PUT_LINE('10號(hào)部門(mén)工資總和：'||v_sum||'，人數(shù)：'||v_num);
END;

6.3.3 AUTHID

過(guò)程中的AUTHID 指令可以告訴ORACLE ，這個(gè)過(guò)程使用誰(shuí)的權(quán)限運(yùn)行．默任情況下，存儲(chǔ)過(guò)程會(huì)作為調(diào)用者的過(guò)程運(yùn)行，但是具有設(shè)計(jì)者的特權(quán)．這稱(chēng)為設(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 DB中存儲(chǔ)過(guò)程和函數(shù)的示例分析

6.3.4 PRAGMA 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ò)程源碼，要用類(lèi)似WORD 文字處理軟件進(jìn)行編輯時(shí)，要將源碼存為文本格式。

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

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

在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)試源碼直到正確

我們不能保證所寫(xiě)的存儲(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)的用戶(hù)或角色

如果調(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 DB中存儲(chǔ)過(guò)程和函數(shù)的示例分析

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

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

3、這種模塊化的方法，使得可以將一個(gè)復(fù)雜的問(wèn)題、大的程序逐步簡(jiǎn)化成幾個(gè)簡(jiǎn)單的、小的程序部分，進(jìn)行分別編寫(xiě)、調(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è)用戶(hù)要執(zhí)行相同的過(guò)程或函數(shù)時(shí)，就只需要在內(nèi)存中加載一個(gè)該過(guò)程或函數(shù)。

6、提高數(shù)據(jù)的安全性與完整性。通過(guò)把一些對(duì)數(shù)據(jù)的操作放到過(guò)程或函數(shù)中，就可以通過(guò)是否授予用戶(hù)有執(zhí)行該過(guò)程或的權(quán)限，來(lái)限制某些用戶(hù)對(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ù)都可以使用位置表示法、名稱(chēng)表示法或組合方法。

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

5、其管理過(guò)程都有創(chuàng)建、編譯、授權(quán)、刪除、顯示依賴(lài)關(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ù)的特色。

感謝各位的閱讀！關(guān)于“DB中存儲(chǔ)過(guò)程和函數(shù)的示例分析”這篇文章就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，讓大家可以學(xué)到更多知識(shí)，如果覺(jué)得文章不錯(cuò)，可以把它分享出去讓更多的人看到吧！

網(wǎng)站題目：DB中存儲(chǔ)過(guò)程和函數(shù)的示例分析
標(biāo)題網(wǎng)址：http://vcdvsql.cn/article10/pddjgo.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供手機(jī)網(wǎng)站建設(shè)、關(guān)鍵詞優(yōu)化、外貿(mào)建站、品牌網(wǎng)站建設(shè)、云服務(wù)器、電子商務(wù)

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶(hù)投稿、用戶(hù)轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源：創(chuàng)新互聯(lián)

猜你還喜歡下面的內(nèi)容

bl双性强迫侵犯h_国产在线观看人成激情视频_蜜芽188_被诱拐的少孩全彩啪啪漫画

DB中存儲(chǔ)過(guò)程和函數(shù)的示例分析

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 AUTHID

6.3.4 PRAGMA 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)的用戶(hù)或角色

6.3.5.5 與過(guò)程相關(guān)數(shù)據(jù)字典

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

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