ORACLE PL/SQ入門

fengzj發表於2008-11-27
Oracle
ORACLE PL/SQ入門
 
六、儲存過程

1.命令格式
  儲存過程是一個PL/SQL程式塊，接受零個或多個引數作為輸入(INPUT)或輸出(OUTPUT)、或既作輸入又作輸出(INOUT)，與函式不同， 儲存過程沒有返回值，儲存過程不能由SQL語句直接使用，只能通過EXECUT命令或PL/SQL程式塊內部呼叫，定義儲存過程的語法如下：
<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--&gtPROCEDURE Name [(Parameter[,Parameter,ORACLE PL/SQ入門])]
IS|AS
  [Local Declarations]
BEGIN
  Execute statements;
  [EXCEPTION Exception Handlers]
END [Name];



2.呼叫
  儲存過程可以直接用EXECUT命令呼叫或PL/SQL程式塊內部呼叫。用EXECUT命令呼叫儲存過程的格式如下：
<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--&gtSQL>EXCUTE  Proc_Name(par1, par2…);

  儲存過程也可以被另外的PL/SQL塊呼叫，呼叫的語句是：
<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--&gtDECLARE par1, par2;
BEGIN
  Proc_Name(par1, par2…);
END;


3.釋放
  當某個儲存過程不再需要時，應將其從記憶體中刪除，以釋放它佔用的記憶體資源。釋放過程的語句格式如下：
<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--&gtSQL>DROP PROCEDURE Proc_Name;

4.例項：
  編寫儲存過程，顯示所指定僱員名所在的部門名和位置。
<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--&gtCREATE 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;

  呼叫：
<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--&gtVARIABLE vdname VARCHAR2(14);
VARIABLE vloc VARCHAR2(13);
EXECUTE DeptMesg('SMITH', :vdname£¬ :vloc);
PRINT vdname vloc; 

七、函式

1.命令格式
  函式是命名了的、儲存在資料庫中的PL/SQL程式塊。函式接受零個或多個輸入引數，有一個返回值，返回值的資料型別在建立函式時定義。定義函式的語法如下：
<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--&gtFUNCTION Name [{Parameter[,Parameter,ORACLE PL/SQ入門])]
RETURN DataTypes
IS
[Local Declarations]
BEGIN
  Execute Statements;
  [EXCEPTION Exception Handlers]
END [Name];

2.呼叫
  無論在命令列還是在程式語句中，函式都可以通過函式名稱直接在表示式中呼叫。例如：將函式Count_Num(‘女’)的返回值賦予變數Man_Num。
<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--&gtSQL>EXECUTE Man_Num := Count_Num('女');

3.釋放
  當函式不再使用時，要用DROP命令將其從記憶體中刪除，例如：
<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--&gtSQL>DROP FUNCTION Count_Num;

4.例項
  編寫一個函式以顯示該僱員在此組織中的工作天數。
<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--&gtCREATE 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;

八、觸發器

1.觸發器的建立規則：
  ①作用範圍清晰；
  ②不要讓觸發器去完成Oracle後臺已經能夠完成的功能；
  ③限制觸發器程式碼的行數；
  ④不要建立遞迴的觸發器；
  ⑤觸發器僅在被觸發語句觸發時進行集中的，全域性的操作，同使用者和資料庫應用無關。

2.可以建立被如下語句所觸發的觸發器：
  ①DML語句（DELETE，INSERT，UPDATE）；
  ②DDL語句（CREATE，ALTER， DROP）；
  ③資料庫操作（SERVERERROR，LOGON，LOGOFF，STARTUP，SHUTDOWN）。

3.注意事項
  ①觸發器可以宣告為在對記錄進行操作之前，在之前(檢查約束之前和 INSERT，UPDATE 或 DELETE 執行前)或之後(在檢查約束之後和完成 INSERT， UPDATE 或 DELETE 操作)觸發；
  ②一個 FOR EACH ROW 執行指定操作的觸發器為操作修改的每一行都呼叫一次；
  ③SELECT 並不更改任何行，因此不能建立 SELECT 觸發器.這種場合下規則和檢視更適合；
  ④觸發器和某一指定的表格有關，當該表格備刪除時，任何與該表有關的觸發器同樣會被刪除；
  ⑤在一個表上的每一個動作只能有一個觸發器與之關聯；
  ⑥在一個單獨的表上，最多隻能建立三個觸發器與之關聯，一個INSERT觸發器，一個DELETE觸發器和一個UPDATE觸發器；

4.刪除觸發器的語句格式為：
<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--&gtDROP TRIGGER name ON table;

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

5.例項
  編寫一個資料庫觸發器，當任何時候某個部門從dept表中刪除時，該觸發器將從emp表中刪除該部門的所有僱員。
<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

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

九、包

1.包頭
  建立包頭的語句格式如下：
  CREATE PACKAGE＜包名> IS
    變數、常量及資料型別定義；
    遊標定義；
    函式、過程定義和引數列表及返回型別；
  END＜包名＞；

2.包體
  建立包主體部分的語句格式如下：
  CREATE PACKAGE BODY＜包名＞
  AS
    遊標、函式、過程的具體定義；
  END＜包名＞；

3.例項

  包頭程式碼：
<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--&gt--建立包頭
CREATE PACKAGE test_package IS
  --定義變數
  man_num   NUMBER;
  woman_num NUMBER;
  --定義遊標
  CURSOR學生;

  --定義函式
  CREATE FUNCTION f_count(in sex IN 學生.sex%TYPE)
  --定義返回值型別
  RETURN NUMBER;

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

--包頭結束
END test_package;


  包體程式碼：
<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

--&gt--建立包體
CREATE PACKAGE BODY test_package AS
  --遊標具體定義
  CURSOR 學生IS
    SELECT 學號，姓名 FROM 學生 WHERE 學號 < 50;

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

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

--包體定義結束
END test_package;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/219982/viewspace-503625/，如需轉載，請註明出處，否則將追究法律責任。

相關文章