欧美一区二区三区老妇人-欧美做爰猛烈大尺度电-99久久夜色精品国产亚洲a-亚洲福利视频一区二区

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

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

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

6.1 引言

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

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

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

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

3．   建立和管理存儲(chǔ)過程和函數(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í)參值（或說該形參的初始值總是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)起來進(jìn)行傳遞。用這種方法進(jìn)行調(diào)用，形參與實(shí)參的名稱是相互獨(dú)立，沒有關(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)起來進(jìn)行傳遞。這種方法，形參與實(shí)參的名稱是相互獨(dú)立的，沒有關(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ù)前面。也就是說，無(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í)，如果沒有為具有默認(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ǔ)過程

6.3.1 創(chuàng)建過程

建立存儲(chǔ)過程

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

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

DB中存儲(chǔ)過程和函數(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;

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

例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ǔ)過程向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, '沒有該地點(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沒有該地點(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沒有該地點(diǎn)');

   WHEN OTHERS THEN

      DBMS_OUTPUT.PUT_LINE('others出現(xiàn)了其他異常錯(cuò)誤');

END;
*/DB中存儲(chǔ)過程和函數(shù)的示例分析

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

    存儲(chǔ)過程建立完成后，只要通過授權(quán)，用戶就可以在SQLPLUS 、ORACLE開發(fā)工具或第三方開發(fā)工具中來調(diào)用運(yùn)行。對(duì)于參數(shù)的傳遞也有三種：按位置傳遞、按名稱傳遞和組合傳遞，傳遞方法與函數(shù)的一樣。ORACLE 使用EXECUTE 語(yǔ)句來實(shí)現(xiàn)對(duì)存儲(chǔ)過程的調(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ù)和過程，這些函數(shù)和過程不存儲(chǔ)在數(shù)據(jù)庫(kù)中，但可以在創(chuàng)建它們的PL/SQL 程序中被重復(fù)調(diào)用。本地函數(shù)和過程在PL/SQL 塊的聲明部分定義，它們的語(yǔ)法格式與存儲(chǔ)函數(shù)和過程相同，但不能使用CREATE
OR REPLACE 關(guān)鍵字。

例13：建立本地過程，用于計(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 AUTHID

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

例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.logexecutionDB中存儲(chǔ)過程和函數(shù)的示例分析

6.3.4 PRAGMA AUTONOMOUS_TRANSACTION

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

例16：建立過程，使用自動(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：建立過程，沒有使用自動(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 開發(fā)存儲(chǔ)過程步驟

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

6.3.5.1 使用文字編輯處理軟件編輯存儲(chǔ)過程源碼

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

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

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

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

SQL>START c:\stat1.sql

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

6.3.5.3 調(diào)試源碼直到正確

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

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

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

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

如果調(diào)式正確的存儲(chǔ)過程沒有進(jìn)行授權(quán)，那就只有建立者本人才可以運(yùn)行。所以作為應(yīng)用系統(tǒng)的一部分的存儲(chǔ)過程也必須進(jìn)行授權(quán)才能達(dá)到要求。在SQL*PLUS下可以用GRANT命令來進(jìn)行存儲(chǔ)過程的運(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 OPTIONDB中存儲(chǔ)過程和函數(shù)的示例分析

6.3.5.5 與過程相關(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;

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

1．刪除過程

可以使用DROP PROCEDURE命令對(duì)不需要的過程進(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ǔ)過程與函數(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        過程與函數(shù)的比較

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

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

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

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

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

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

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

過程與函數(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、 其管理過程都有創(chuàng)建、編譯、授權(quán)、刪除、顯示依賴關(guān)系等。

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

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

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

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

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

網(wǎng)站題目：DB中存儲(chǔ)過程和函數(shù)的示例分析
標(biāo)題網(wǎng)址：http://chinadenli.net/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)容（圖片、視頻和文字）以用戶投稿、用戶轉(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í)需注明來源： 創(chuàng)新互聯(lián)

網(wǎng)站托管運(yùn)營(yíng)