[轉]ORACLE PL/SQ入門 - Web/.Net 開發(fā) - 博客園

pursue2012 2009-10-10

展開全文

ORACLE PL/SQ入門

最近學習flash開發(fā)，需求是flash上面顯示的數據必須通過ORACLE存儲過程從數據庫中取，用存儲過程主要是為了能夠通過寫存儲過程來改變flash端顯示的信息，而不需要更改代碼，臨時找了這篇文章充充電，很有收獲，現在共享出來。轉自：http://reonlyrun.cnblogs.com
一、塊
    1.塊結構
        1)塊的三個部分
        2)塊語法
    2.塊的命名和匿名
    3.塊的執(zhí)行
二、變量、常量與字符集
    1.變量
        1)聲明變量
        2)給變量賦值
    2.常量
    3.有效字符集
三、分支語語句
    1.條件
        1)IF條件判斷邏輯結構
        2)CASE表達式
    2.循環(huán)
        1)LOOP…EXIT…END循環(huán)控制語句
        2)WHILE…LOOP循環(huán)控制語句
        3)FOR…LOOP循環(huán)控制語句
    3.跳轉
    4.嵌套
四、異常
    1.簡介
    2.預定義異常
    3.自定義異常
五、游標
    1.聲明游標
    2.打開游標
    3.從游標中取數據
    4.關閉游標
    5.隱式游標
    6.實例
    7.游標的屬性
        1)%ISOPEN屬性
        2)%FOUND屬性
        3)%NOTFOUND屬性
        4)%ROWCOUNT屬性
六、存儲過程
    1.命令格式
    2.調用
    3.釋放
    4.實例：
七、函數
    1.命令格式
    2.調用
    3.釋放
    4.實例
八、觸發(fā)器
    1.觸發(fā)器的創(chuàng)建規(guī)則：
    2.可以創(chuàng)建被如下語句所觸發(fā)的觸發(fā)器：
    3.注意事項
    4.刪除觸發(fā)器的語句格式為：
    5.實例
九、包
    1.包頭
    2.包體
    3.實例

ORACLE PL/SQ入門

一、塊

1.塊結構
PL/SQL是一種塊結構的語言，組成PL/SQL程序的單元是邏輯塊，一個PL/SQL 程序包含了一個或多個邏輯塊，每個塊都可以劃分為三個部分。

1)塊的三個部分
①聲明部分（Declaration section）
    聲明部分包含了變量和常量的數據類型和初始值。這個部分是由關鍵字DECLARE開始，如果不需要聲明變量或常量，那么可以忽略這一部分。
②執(zhí)行部分（Executable section）
    執(zhí)行部分是PL/SQL塊中的指令部分，由關鍵字BEGIN開始，所有的可執(zhí)行語句都放在這一部分，其他的PL/SQL塊也可以放在這一部分。
③異常處理部分（Exception section）
    這一部分是可選的，在這一部分中處理異?；蝈e誤，對異常處理的詳細討論在后面進行。

2)塊語法
PL/SQL塊語法結構如下：

[DECLARE]
  Declaration Statements
BEGIN
  Executable Statements
  [EXCEPTION Exception Handlers]
END

PL/SQL塊中的每一條語句都必須以分號結束，SQL語句可以多行，但分號表示該語句的結束。一行中可以有多條SQL語句，他們之間以分號分隔。每一個PL/SQL塊由BEGIN或DECLARE開始，以END結束。注釋由--標示。

2.塊的命名和匿名
PL/SQL程序塊可以是一個命名的程序塊也可以是一個匿名程序塊，匿名程序塊可以用在服務器端也可以用在客戶端。
執(zhí)行部分包含了所有的語句和表達式，執(zhí)行部分以關鍵字BEGIN開始，以關鍵字EXCEPTION結束，如果EXCEPTION不存在，那么將以關鍵字END結束。分號分隔每一條語句，使用賦值操作符:=或SELECT INTO或FETCH INTO給每個變量賦值，執(zhí)行部分的錯誤將在異常處理部分解決，在執(zhí)行部分中可以使用另一個PL/SQL程序塊，這種程序塊被稱為嵌套塊。
所有的SQL數據操作語句都可以用于執(zhí)行部分，PL/SQL塊不能在屏幕上顯示SELECT語句的輸出。SELECT語句必須包括一個INTO子串或者是游標的一部分，執(zhí)行部分使用的變量和常量必須首先在聲明部分聲明，執(zhí)行部分必須至少包括一條可執(zhí)行語句，NULL是一條合法的可執(zhí)行語句，事物控制語句COMMIT和ROLLBACK可以在執(zhí)行部分使用，數據定義語言(Data Definition language)不能在執(zhí)行部分中使用，DDL語句與EXECUTE IMMEDIATE一起使用或者是DBMS_SQL調用。

3.塊的執(zhí)行
SQL*PLUS中匿名的PL/SQL塊的執(zhí)行是在PL/SQL塊后輸入/來執(zhí)行。
命名的程序與匿名程序的執(zhí)行不同，執(zhí)行命名的程序塊必須使用EXECUTE關鍵字。
如果在另一個命名程序塊或匿名程序塊中執(zhí)行這個程序，那么就不需要EXECUTE關鍵字。
注意：如果在PL/SQL Developer中執(zhí)行需在EXECUTE前后加上BEGIN和END關鍵字。

二、變量、常量與字符集

1.變量

1)聲明變量
聲明變量的語句格式如下：
Variable_Name [CONSTANT] databyte [NOT NULL] [:=DEFAULT EXPRESSION]
注意：可以在聲明變量的同時給變量強制性的加上NOT NULL約束條件，此時變量在初始化時必須賦值。

2)給變量賦值
給變量賦值有兩種方式：
①直接給變量賦值

eno := 7369;
myname := 'SCOTT';

②用戶交互賦值

eno := &empno;

運行時系統會提示用戶輸入empno，用戶輸入的值將存入eno變量。
③通過SQL SELECT INTO 或FETCH INTO給變量賦值

SELECT EMP_NAME INTO MyName FROM EMPLOYEES WHERE EMPID = eno;

注意：只有在該查詢返回一行的時候該語句才可以成功否則就會拋出異常。

2.常量
常量與變量相似，但常量的值在程序內部不能改變，常量的值在定義時賦予，聲明方式與變量相似，但必須包括關鍵字CONSTANT。常量和變量都可被定義為SQL和用戶定義的數據類型。
為了減少這部分程序的修改，編程時使用%TYPE、%ROWTYPE方式聲明變量，使變量聲明的類型與表中的保持同步，隨表的變化而變化，這樣的程序在一定程度上具有更強的通用性。

3.有效字符集
① 所有的大寫和小寫英文字母；
② 數字0-9；
③ 符號：0＋一*／＜＞＝！一；:.‘＠％，“‘＃“＆＿｝｛｝？[]；
PL/SQL標識符的最大長度是30個字符，并且不區(qū)分字母的大小寫。但是適當地使用大小寫，可以提高程序的可讀性。

例如：定義如下若干類型變量，常量。

DECLARE
  ORDER_NO    NUMBER(3);
  CUST_NAME   VARCHAR2(20);
  ORDER_DATE  DATE;
  EMP_NO      INTEGER := 25;
  PI CONSTANT NUMBER := 3.1416;
BEGIN
  NULL;
END;

①算術操作符

+	加
-	減
*	乘
/	除
**	乘方

②關系操作符

<	小于
<=	小于等于
>	大于
>=	大于等于
=	等于
!=	不等于
<>	不等于
:=	賦值

③比較操作符

IS NULL	如果操作數為NULL返回TRUE
LIKE	比較字符串值
BETWEEN	驗證值是否在范圍之內
IN	驗證操作數在設定的一系列值中

④邏輯操作符

AND	兩個條件都必須滿足
OR	只要滿足兩個條件中的一個
NOT	取反

三、分支語語句

1.條件

1)IF條件判斷邏輯結構
If條件判斷邏輯結構有三種表達方式。
①表達式一：

IF Condition THEN
Statement;
END IF;

該表達式的功能為：若條件為真，執(zhí)行then后的語句；否則，跳出條件語句執(zhí)行end if后的語句。
②表達式二：

IF Condition THEN
Statements_1;
ELSE
Statements_2;
END IF;

該表達式的功能為：如果條件為真執(zhí)行then后的語句，否則執(zhí)行else后的語句。
③表達式三：

IF Condition1 THEN
  Statements_1;
ELSEIF Condition2 THEN
  Statements_2;
ELSE
  Statements_3;
END IF;

該表達式的功能為：如果if后的條件成立，執(zhí)行then后面的語句，否則判斷elseif后面的條件，條件成立執(zhí)行第二個then后面的語句，否則執(zhí)行else后的語句。這是條件語句嵌套。IF 可以嵌套，可以在IF 或IF ..ELSE語句中使用IF或IF…ELSE語句。

2)CASE表達式
CASE語句的基本格式如下：

CASE Grade
  WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE ('Excellent');
  WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE ('Very Good');
  WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE ('Good');
  WHEN 'D' THEN DBMS_OUTPUT.PUT_LINE ('Fair');
  WHEN 'F' THEN DBMS_OUTPUT.PUT_LINE ('Poor');
  ELSE DBMS_OUTPUT.PUT_LINE ('No such grade');
END CASE;

CASE語句的功能：首先設定變量的值作為條件，然后順序檢查表達式，一旦從中找到與條件匹配的表達式值，就停止CASE語句的處理。

2.循環(huán)

1)LOOP…EXIT…END循環(huán)控制語句
LOOP循環(huán)語句是其中最基本的一種，格式如下：

LOOP
Statements;
END LOOP;

這種循環(huán)語句是沒有終止的，如果不人為控制的話，其中的Statements將會無限地執(zhí)行。一般可以通過加入EXIT語句來終結該循環(huán)。

2)WHILE…LOOP循環(huán)控制語句
WHILE…LOOP循環(huán)控制語句的格式如下：

WHILE Condition
LOOP
Statements;
END LOOP;

WHILE…LOOP有一個條件與循環(huán)相聯系，如果條件為TRUE，則執(zhí)行循環(huán)體內的語句，如果結果為FALSE，則結束循環(huán)。

3)FOR…LOOP循環(huán)控制語句
FOR…LOOP循環(huán)控制語句的格式如下：

FOR Counter IN [REVERSE] Start_Range…End_Range
LOOP
Statements;
END LOOP;

LOOP和WHILE循環(huán)的循環(huán)次數都是不確定的，FOR循環(huán)的循環(huán)次數是固定的，Counter是一個隱式聲明的變量，初始值是Start_Range，第二個值是Start_Range + 1，直到End_Range,如果Start_Range等于End _Range，那么循環(huán)將執(zhí)行一次。如果使用了REVERSE關鍵字，那么范圍將是一個降序。

3.跳轉
GOTO語句的格式如下：
GOTO LABEL;
執(zhí)行GOTO語句時，控制會立即轉到由標簽標記的語句（使用<<>>聲明）。PL/SQL中對GOTO語句有一些限制，對于塊、循環(huán)、IF語句而言，從外層跳轉到內層是非法的。

DECLARE
  X         NUMBER(3);
  Y         NUMBER(3);
  V_COUNTER NUMBER(2);
BEGIN
  X := 100;
  FOR V_COUNTER IN 1 .. 10 LOOP
    IF V_COUNTER = 4 THEN
      GOTO end_of_loop;
    END IF;
    X := X + 10;
  END LOOP;
  <<end_of_loop>>
  Y := X;
  dbms_output.put_line('Y:'||Y);
END;

輸出結果為“Y：130”。

4.嵌套
程序塊的內部可以有另一個程序塊這種情況稱為嵌套。嵌套要注意的是變量，定義在最外部程序塊中的變量可以在所有子塊中使用，如果在子塊中定義了與外部程序塊變量相同的變量名，在執(zhí)行子塊時將使用子塊中定義的變量。子塊中定義的變量不能被父塊引用。同樣GOTO語句不能由父塊跳轉道子塊中，反之則是合法的。

四、異常

1.簡介
異常處理塊中包含了與異常相關的錯誤發(fā)生以及當錯誤發(fā)生時要進行執(zhí)行和處理的代碼。異常部分的語法一般如下：

BEGIN
  EXCEPTION
  WHEN Excep_Name1 THEN
    Statements1;
  WHEN Excep_Name2 THEN
    Statements2;
  WHEN OTHERS THEN
    Statements3;
END;

2.預定義異常
簡單列一下常用的吧：

異常名	異常標題	異常號
ACCESS_INTO_NULL	ORA-06530	-6530
CASE_NOT_FOUND	ORA-06592	-6592
COLLECTION_IS_NULL	ORA-06531	-6531
CURSOR_ALREADY_OPEN	ORA-06511	-6511
DUP_VAL_ON_INDEX	ORA-00001	-1
INVALID_CURSOR	ORA-01001	-1001
INVALID_NUMBER	ORA-01722	-1722
LOGIN_DENIED	ORA-01017	-1017
NO_DATA_FOUND	ORA-01403	-1403
NOT_LOGGED_ON	ORA-01012	-1012
PROGRAM_ERROR	ORA-06501	-6501
ROWTYPE_MISMATCH	ORA-06504	-6504
SELF_IS_NULL	ORA-30625	-30625
STORAGE_ERROR	ORA-06500	-6500
SUBSCRIPT_BEYOND_COUNT	ORA-06533	-6533
SUBSCRIPT_OUTSIDE_LIMIT	ORA-06532	-6532
SYS_INVALID_ROWID	ORA-01410	-1410
TIMEOUT_ON_RESOURCE	ORA-00051	-51
TOO_MANY_ROWS	ORA-01422	-1422
VALUE_ERROR	ORA-06502	-6502
ZERO_DIVIDE	ORA-01476	-1476

以上異常說明：

異常名	說明
ACCESS_INTO_NULL	Your program attempts to assign values to the attributes of an uninitialized (atomically null) object.
CASE_NOT_FOUND	one of the choices in the WHEN clauses of a ASE tatement is selected, and there is no ELSE clause.
COLLECTION_IS_NULL	Your program attempts to apply collection methods other than EXISTS to an uninitialized (atomically null) nested table or varray, or the program attempts to assign values to the elements of an uninitialized nested table or varray.
CURSOR_ALREADY_OPEN	Your program attempts to open an alrea*** open cursor. A cursor must be closed before it can be reopened. A cursor FOR loop automatically opens the cursor to which it refers. So, your program cannot open that cursor inside the loop.
DUP_VAL_ON_INDEX	Your program attempts to store duplicate values in a database column that is constrained by a unique index.
INVALID_CURSOR	Your program attempts an illegal cursor operation such as closing an unopened cursor.
INVALID_NUMBER	In a SQL statement, the conversion of a character st* into a number fails because the st* does not represent a valid number. (In procedural statements, VALUE_ERROR is raised.) This exception is also raised when the LIMIT-clause expression in a bulk FETCH statement does not evaluate to a positive number.
LOGIN_DENIED	Your program attempts to log on to Oracle with an invalid username and/or password.
NO_DATA_FOUND	A SELECT INTO statement returns no rows, or your program references a deleted element in a nested table or an uninitialized element in an index-by table. SQL aggregate functions such as AVG and SUM always return a value or a null. So, a SELECT INTO statement that calls an aggregate function never raises NO_DATA_FOUND. The FETCH statement is expected to return no rows eventually, so when that happens, no exception is raised.
NOT_LOGGED_ON	Your program issues a database call without being connected to Oracle.
PROGRAM_ERROR	PL/SQL has an internal problem.
ROWTYPE_MISMATCH	The host cursor variable and PL/SQL cursor variable involved in an assignment have incompatible return types. For example, when an open host cursor variable is passed to a stored subprogram, the return types of the actual and formal parameters must be compatible.
SELF_IS_NULL	Your program attempts to call a MEMBER method on a null instance. That is, the built-in parameter SELF (which is always the first parameter passed to a MEMBER method) is null.
STORAGE_ERROR	PL/SQL runs out of memory or memory has been corrupted.
SUBSCRIPT_BEYOND_COUNT	Your program references a nested table or varray element using an index number larger than the number of elements in the collection.
SUBSCRIPT_OUTSIDE_LIMIT	Your program references a nested table or varray element using an index number (-1 for example) that is outside the legal range.
SYS_INVALID_ROWID	The conversion of a character st* into a universal rowid fails because the character st* does not represent a valid rowid.
TIMEOUT_ON_RESOURCE	A time-out occurs while Oracle is waiting for a resource.
TOO_MANY_ROWS	A SELECT INTO statement returns more than one row.
VALUE_ERROR	An arithmetic, conversion, truncation, or size-constraint error occurs. For example, when your program selects a column value into a character variable, if the value is longer than the declared length of the variable, PL/SQL aborts the assignment and raises VALUE_ERROR. In procedural statements, VALUE_ERROR is raised if the conversion of a character st*** into a number fails. (In SQL statements, INVALID_NUMBER is raised.)
ZERO_DIVIDE	Your program attempts to divide a number by zero.

3.自定義異常
異常不一定必須是Oracle返回的系統錯誤，用戶可以在自己的應用程序中創(chuàng)建可觸發(fā)及可處理的自定義異常，調用異常處理需要使用RAISE語句。
異常情態(tài)的傳播指的是當在程序塊的聲明、執(zhí)行、異常部分分別出現異常情態(tài)時，或在本塊中沒有相應的異常處理器時會將這個異常情態(tài)傳播到哪里，會去激發(fā)那個塊中的處理器。傳播規(guī)則是這樣的：當一個異常情態(tài)是在塊的執(zhí)行部分引發(fā)的(最常見的)，PL/SQL使用下面的規(guī)則確定激活哪個異常處理器。
① 若當前塊對該異常情態(tài)設置了處理器，則執(zhí)行它并成功完成該塊的執(zhí)行，然后控制轉給包含塊。
② 若當前塊沒有該處理器，則通過在包含塊中引發(fā)它來傳播異常情態(tài)。然后對包含塊執(zhí)行PL/SQL的異常操作。另外，無論是在聲明部分引發(fā)了一個異常情態(tài)，還是在異常處理部分引發(fā)，則該異常情態(tài)將立即傳播給包含塊。在包含塊引用上述規(guī)則進行異常情態(tài)的處理，即使在當前塊設置了OTHERS處理器也不會被執(zhí)行。
五、游標
Oracle游標是一種用于輕松的處理多行數據的機制。如果沒有游標，Oracle開發(fā)人員必須單獨地、顯式地取回并管理游標查詢選擇的每一條記錄。游標的另一項功能是，它包含一個跟蹤當前訪問的記錄的指針，這使程序能夠一次處理多條記錄。

1.聲明游標
聲明游標的語句格式如下：

DECLARE Cursor_Name IS SELECT Statement

聲明游標完成了下面兩個目的：
① 給游標命名.
② 將一個查詢與游標關聯起來。

2.打開游標
打開游標的語句格式如下：

OPEN Cursor_Name;

打開游標將激活查詢并識別活動集，可是在執(zhí)行游標取回命令之前，并沒有真正取回記錄。OPEN命令還初始化了游標指針，使其指向活動集的第一條記錄。游標被打開后，直到關閉之前，取回到活動集的所有數據都是靜態(tài)的。換句話說，游標忽略所有在游標打開之后，對數據執(zhí)行的SQL DML命令(INSERT、UPDATE、DELETE和SELECT)，因此只有在需要時才打開它，要刷新活動集，只需關閉并重新打開游標即可。

3.從游標中取數據
FETCH命令以每次一條記錄的方式取回活動集中的記錄。通常將FETCH命令和某種迭代處理結合起來使用，在迭代處理中，FETCH命令每執(zhí)行一次，游標前進到活動集的下一條記錄。
FETCH命令的語句格式如下：

FETCH Cursor_Name INTO Record_List;

執(zhí)行FETCH命令后，活動集中的結果被取回到PL/SQL變量中，以便在PL/SQL塊中使用。每取回一條記錄，游標的指針就移向活動集的下一條記錄。

4.關閉游標
CLOSE語句關閉以前打開的游標。
CLOSE語句的格式：

CLOSE Cursor_Name;

5.隱式游標
隱式游標也可以叫做SQL游標。和顯式的游標不同，不能對一個SQL游標顯式的執(zhí)行OPEN、CLOSE和FETCH語句。Oracle隱式的打開SQL游標、處理SQL游標、然后再關閉該游標。Oracle提供隱式游標的主要目的就是利用這些游標的屬性來確定SQL語句運行的情況。

6.實例
一個游標應用的完整程序代碼：

DECLARE
  CURSOR C1 IS
    SELECT VIEW_NAME FROM ALL_VIEWS WHERE ROWNUM <= 10 ORDER BY VIEW_NAME;
  VNAME VARCHAR2(40);
BEGIN
  OPEN C1;
  FETCH C1 INTO VNAME;
  WHILE C1%FOUND LOOP
    FETCH C1 INTO VNAME;
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(C1%ROWCOUNT) || '' || VNAME);
  END LOOP;
  CLOSE C1;
END;

7.游標的屬性

1)%ISOPEN屬性
該屬性功能是測試游標是否打開，如果沒有打開游標就使用fetch語句將提示錯誤。

DECLARE
  TempSal EMPLOYEES.SAL%TYPE;
  CURSOR MyCursor IS
    SELECT * FROM EMPLOYEES WHERE SAL > TempSal;
  CursorRecord MyCursor%ROWTYPE;
BEGIN
  TempSal := 800;
  IF MyCursor%ISOPEN THEN
    FETCH MyCursor INTO CursorRecord;
      dbms_output.put_line(to_char(CursorRecord.EMP_NAME));
  ELSE
    dbms_output.put_line('游標未打開!');
  END IF;
END;

輸出結果為“游標未打開!”。

2)%FOUND屬性
該屬性功能是測試前一個fetch語句是否有值，有值將返回true，否則為false。

DECLARE
  TempSal EMPLOYEES.SAL%TYPE;
  CURSOR MyCursor IS
    SELECT * FROM EMPLOYEES WHERE SAL > TempSal;
  CursorRecord MyCursor%ROWTYPE;
BEGIN
  TempSal := 800;
  OPEN MyCursor;
  FETCH MyCursor INTO CursorRecord;
  IF MyCursor%FOUND THEN
    dbms_output.put_line(to_char(CursorRecord.EMP_NAME));
  ELSE
    dbms_output.put_line('未發(fā)現數據!');
  END IF;
END;

輸出結果為“tom”。

3)%NOTFOUND屬性
該屬性是%found屬性的反邏輯，常被用于退出循環(huán)。

DECLARE
  TempSal EMPLOYEES.SAL%TYPE;
  CURSOR MyCursor IS
    SELECT * FROM EMPLOYEES WHERE SAL > TempSal;
  CursorRecord MyCursor%ROWTYPE;
BEGIN
  TempSal := 800;
  OPEN MyCursor;
  FETCH MyCursor INTO CursorRecord;
  IF MyCursor%NOTFOUND THEN
    dbms_output.put_line(to_char(CursorRecord.EMP_NAME));
  ELSE
    dbms_output.put_line('發(fā)現數據！');
  END IF;
END;

輸出結果為“發(fā)現數據！”

4)%ROWCOUNT屬性
該屬性用于返回游標的數據行數。

DECLARE
  TempSal EMPLOYEES.SAL%TYPE;
  CURSOR MyCursor IS
    SELECT * FROM EMPLOYEES WHERE SAL > TempSal;
  CursorRecord MyCursor%ROWTYPE;
BEGIN
  TempSal := 800;
  OPEN MyCursor;
  FETCH MyCursor INTO CursorRecord;
    dbms_output.put_line(to_char(MyCursor%ROWCOUNT));
END;

輸出結果為“1” 。

六、存儲過程

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

PROCEDURE Name [(Parameter[,Parameter,

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

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

SQL>EXCUTE Proc_Name(par1, par2…);

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

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

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

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;

調用：

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

七、函數

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

FUNCTION Name [{Parameter[,Parameter,

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

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

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

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

SQL>DROP FUNCTION Count_Num;

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

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后臺已經能夠完成的功能；
③限制觸發(fā)器代碼的行數；
④不要創(chuàng)建遞歸的觸發(fā)器；
⑤觸發(fā)器僅在被觸發(fā)語句觸發(fā)時進行集中的，全局的操作，同用戶和數據庫應用無關。

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

3.注意事項
①觸發(fā)器可以聲明為在對記錄進行操作之前，在之前(檢查約束之前和 INSERT，UPDATE 或 DELETE 執(zhí)行前)或之后(在檢查約束之后和完成 INSERT， UPDATE 或 DELETE 操作)觸發(fā)；
②一個 FOR EACH ROW 執(zhí)行指定操作的觸發(fā)器為操作修改的每一行都調用一次；
③SELECT 并不更改任何行，因此不能創(chuàng)建 SELECT 觸發(fā)器.這種場合下規(guī)則和視圖更適合；
④觸發(fā)器和某一指定的表格有關，當該表格備刪除時，任何與該表有關的觸發(fā)器同樣會被刪除；
⑤在一個表上的每一個動作只能有一個觸發(fā)器與之關聯；
⑥在一個單獨的表上，最多只能創(chuàng)建三個觸發(fā)器與之關聯，一個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.實例
編寫一個數據庫觸發(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
變量、常量及數據類型定義；
游標定義；
函數、過程定義和參數列表及返回類型；
END＜包名＞；

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

3.實例

包頭代碼：

--創(chuàng)建包頭
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;

包體代碼：

--創(chuàng)建包體
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;