ORACLE PL/SQL編程之把過程與函數說透

作者: huyong 來源: 博客園發布時間: 2011-06-04 18:53 閱讀: 7490 次推薦: 2 原文鏈接 [收藏]

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

　　本篇主要內容如下：

　　6.1 引言

　　6.2 創建函數

　　6.3 存儲過程

　　6.3.1 創建過程

　　6.3.2 調用存儲過程

　　6.3.3 AUTHID

　　6.3.4 PRAGMA 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;

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

　　一般，只有在確認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 AUTHID

　　過程中的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 PRAGMA 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下來調式主要用的方法是：

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

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

　　6.3.5.4 授權執行權給相關的用戶或角色

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

　　GRANT語法：

　　例子：

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語句內部（如表達式）調用函數來完成復雜的計算問題，但不能調用過程。所以這是函數的特色。

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

標簽：ORACLE PL/SQL

文章列表

大師兄

IT工程師數位筆記本

大師兄發表在痞客邦留言(0) 人氣()

E-mail轉寄

IT工程師數位筆記本

If you give someone a program , you will frustrate them for a day; if you teach them how to program, you will frustrate them for a lifetime.IT 這段話的意思是，如果你交給某人一隻程式，你將折磨他一整天;如果你教會某人如何寫程式，你將折磨他一輩子。

BloggerAds