ORACLE PL/SQ入門

艾雪溪 2009-12-11

展開全文

存儲過程：

1.命令格式
存儲過程是一個PL/SQL程序塊，接受零個或多個參數(shù)作為輸入(INPUT)或輸出(OUTPUT)、或既作輸入又作輸出(INOUT)，與函數(shù)不同，存儲過程沒有返回值，存儲過程不能由SQL語句直接使用，只能通過EXECUT命令或PL/SQL程序塊內(nèi)部調(diào)用，定義存儲過程的語法如下：

PROCEDURE Name [(Parameter[,Parameter,

])]
IS|AS
  [Local Declarations]
BEGIN
  Execute statements;
  [EXCEPTION Exception Handlers]
END [Name];

2.調(diào)用
存儲過程可以直接用EXECUT命令調(diào)用或PL/SQL程序塊內(nèi)部調(diào)用。用EXECUT命令調(diào)用存儲過程的格式如下：

SQL>EXCUTE Proc_Name(par1, par2…);

存儲過程也可以被另外的PL/SQL塊調(diào)用，調(diào)用的語句是：

DECLARE par1, par2;
BEGIN
Proc_Name(par1, par2…);
END;

3.釋放
當某個存儲過程不再需要時，應(yīng)將其從內(nèi)存中刪除，以釋放它占用的內(nèi)存資源。釋放過程的語句格式如下：

SQL>DROP PROCEDURE Proc_Name;

4.實例：
編寫存儲過程，顯示所指定雇員名所在的部門名和位置。

CREATE OR REPLACE PROCEDURE DeptMesg(pename emp.ename%TYPE,
                                      pdname OUT dept.dname%TYPE,
                                      ploc    OUT dept.loc%TYPE) AS
BEGIN
  SELECT dname, loc
    INTO pdname, ploc
    FROM emp, dept
   WHERE emp.deptno = dept.deptno
     AND emp.ename = pename;
END;

調(diào)用：

VARIABLE vdname VARCHAR2(14);
VARIABLE vloc VARCHAR2(13);
EXECUTE DeptMesg('SMITH', :vdname￡? :vloc);
PRINT vdname vloc;

函數(shù)：

1.命令格式
函數(shù)是命名了的、存儲在數(shù)據(jù)庫中的PL/SQL程序塊。函數(shù)接受零個或多個輸入?yún)?shù)，有一個返回值，返回值的數(shù)據(jù)類型在創(chuàng)建函數(shù)時定義。定義函數(shù)的語法如下：

FUNCTION Name [{Parameter[,Parameter,

])]
RETURN DataTypes
IS
[Local Declarations]
BEGIN
Execute Statements;
[EXCEPTION Exception Handlers]
END [Name];

2.調(diào)用
無論在命令行還是在程序語句中，函數(shù)都可以通過函數(shù)名稱直接在表達式中調(diào)用。例如：將函數(shù)Count_Num(‘女’)的返回值賦予變量Man_Num。

SQL>EXECUTE Man_Num := Count_Num('女');

3.釋放
當函數(shù)不再使用時，要用DROP命令將其從內(nèi)存中刪除，例如：

SQL>DROP FUNCTION Count_Num;

4.實例
編寫一個函數(shù)以顯示該雇員在此組織中的工作天數(shù)。

CREATE OR REPLACE FUNCTION Hire_Day(no emp.empno%TYPE) RETURN NUMBER AS
   vhiredate emp.hiredate%TYPE;
   vday      NUMBER;
BEGIN
  SELECT hiredate INTO vhiredate FROM emp WHERE empno = no;
   vday := CEIL(SYSDATE - vhiredate);
  RETURN vday;
END;

觸發(fā)器：

1.觸發(fā)器的創(chuàng)建規(guī)則：
①作用范圍清晰；
②不要讓觸發(fā)器去完成Oracle后臺已經(jīng)能夠完成的功能；
③限制觸發(fā)器代碼的行數(shù)；
④不要創(chuàng)建遞歸的觸發(fā)器；
⑤觸發(fā)器僅在被觸發(fā)語句觸發(fā)時進行集中的，全局的操作，同用戶和數(shù)據(jù)庫應(yīng)用無關(guān)。

2.可以創(chuàng)建被如下語句所觸發(fā)的觸發(fā)器：
①DML語句（DELETE，INSERT，UPDATE）；
②DDL語句（CREATE，ALTER， DROP）；
③數(shù)據(jù)庫操作（SERVERERROR，LOGON，LOGOFF，STARTUP，SHUTDOWN）。

3.注意事項
①觸發(fā)器可以聲明為在對記錄進行操作之前，在之前(檢查約束之前和 INSERT，UPDATE 或 DELETE 執(zhí)行前)或之后(在檢查約束之后和完成 INSERT， UPDATE 或 DELETE 操作)觸發(fā)；
②一個 FOR EACH ROW 執(zhí)行指定操作的觸發(fā)器為操作修改的每一行都調(diào)用一次；
③SELECT 并不更改任何行，因此不能創(chuàng)建 SELECT 觸發(fā)器.這種場合下規(guī)則和視圖更適合；
④觸發(fā)器和某一指定的表格有關(guān)，當該表格備刪除時，任何與該表有關(guān)的觸發(fā)器同樣會被刪除；
⑤在一個表上的每一個動作只能有一個觸發(fā)器與之關(guān)聯(lián)；
⑥在一個單獨的表上，最多只能創(chuàng)建三個觸發(fā)器與之關(guān)聯(lián)，一個INSERT觸發(fā)器，一個DELETE觸發(fā)器和一個UPDATE觸發(fā)器；

4.刪除觸發(fā)器的語句格式為：

DROP TRIGGER name ON table;

一個觸發(fā)器由三部分組成:觸發(fā)事件或語句、觸發(fā)限制和觸發(fā)器動作。觸發(fā)事件或語句是指引起激發(fā)觸發(fā)器的SQL語句，可為對一指定表的INSERT、UNPDATE或DELETE語句。觸發(fā)限制是指定一個布爾表達式，當觸發(fā)器激發(fā)時該布爾表達式是必須為真。觸發(fā)器作為過程，是PL/SQL塊，當觸發(fā)語句發(fā)出、觸發(fā)限制計算為真時該過程被執(zhí)行。

5.實例
編寫一個數(shù)據(jù)庫觸發(fā)器，當任何時候某個部門從dept表中刪除時，該觸發(fā)器將從emp表中刪除該部門的所有雇員。

CREATE OR REPLACE TRIGGER del_emp_deptno
   BEFORE DELETE ON dept
  FOR EACH ROW
BEGIN
  DELETE FROM emp WHERE deptno = :OLD.deptno;
END;

包：

1.包頭
創(chuàng)建包頭的語句格式如下：
CREATE PACKAGE＜包名> IS
變量、常量及數(shù)據(jù)類型定義；
游標定義；
函數(shù)、過程定義和參數(shù)列表及返回類型；
END＜包名＞；

2.包體
創(chuàng)建包主體部分的語句格式如下：
CREATE PACKAGE BODY＜包名＞
AS
游標、函數(shù)、過程的具體定義；
END＜包名＞；

3.實例

包頭代碼：
包體代碼：

--創(chuàng)建包頭
CREATE PACKAGE test_package IS
  --定義變量
   man_num   NUMBER;
   woman_num NUMBER;
  --定義游標
   CURSOR學(xué)生;

  --定義函數(shù)
  CREATE FUNCTION f_count(in sex IN 學(xué)生.sex%TYPE)
  --定義返回值類型
  RETURN NUMBER;

  --定義過程
  CREATE PROCEDURE p_count(in_sex IN 學(xué)生.sex%TYPE, out_num OUT NUMBER);

--包頭結(jié)束
END test_package;

--創(chuàng)建包體
CREATE PACKAGE BODY test_package AS
  --游標具體定義
  CURSOR 學(xué)生IS
    SELECT 學(xué)號，姓名 FROM 學(xué)生 WHERE 學(xué)號 < 50;

  --函數(shù)具體定義
  FUNCTION f_count(in_sex IN學(xué)生.sex%TYPE)
  --定義返回值類型
   RETURN NUMBER IS
     out_num NUMBER;
    --函數(shù)體
  BEGIN
    IF in_sex = '男' THEN
      SELECT count(sex) INTO out_num FROM 學(xué)生 WHERE性別＝'男';
    ELSE
      SELECT count(sex) INTO out_num FROM 學(xué)生 WHERE 性別＝'女';
    END IF;
    --返回函數(shù)值
    RETURN(out_num);
    --函數(shù)定義結(jié)束
  END f_count;

  --過程具體定義
  PROCEDURE p_count(in_sex IN學(xué)生.sex%TYPE， out_num OUT NUMBER) AS
    --過程體
  BEGIN
    IF in_sex = '男' THEN
      SELECT count(sex) INTO out_num FROM 學(xué)生 WHERE性別 = '男';
    ELSE
      SELECT count(sex) INTO out_num FROM 學(xué)生 WHERE 性別＝ '女';
    END IF;
    --過程定義結(jié)束
  END P_count;

--包體定義結(jié)束
END test_package;