PL/SQL 塊

PL/SQL 程序由三個塊組成，即聲明部分、執(zhí)行部分、異常處理部分

• PL/SQL 塊的結(jié)構(gòu)如下：

DECLARE   
/* 聲明部分 : 在此聲明 PL/SQL用到的變量 , 類型及游標，以及局部的存儲過程和函數(shù) */ 
BEGIN     
/*  執(zhí)行部分 :  過程及 SQL 語句  , 即程序的主要部分  */ 
EXCEPTION    
/* 執(zhí)行異常部分 : 錯誤處理  */ 
END; 

其中執(zhí)行部分是必須的。

• PL/SQL 塊可以分為三類：

• 無名塊：動態(tài)構(gòu)造，只能執(zhí)行一次。
• 子程序：存儲在數(shù)據(jù)庫中的存儲過程、函數(shù)及包等。當在數(shù)據(jù)庫上建立好后可以在其它程序中調(diào)用它們。
• 觸發(fā)器：當數(shù)據(jù)庫發(fā)生操作時，會觸發(fā)一些事件，從而自動執(zhí)行相應(yīng)的程序。

PL/SQL 結(jié)構(gòu)

• PL/SQL 塊中可以包含子塊；
• 子塊可以位于 PL/SQL 中的任何部分；
• 子塊也即 PL/SQL 中的一條命令；

標識符

PL/SQL 程序設(shè)計中的標識符定義與 SQL 的標識符定義的要求相同。
要求和限制有：

• 標識符名不能超過 30 字符；
• 第一個字符必須為字母；
• 不分大小寫；
• 不能用’-‘(減號);
• 不能是 SQL 保留字。 提示 : 一般不要把變量名聲明與表中字段名完全一樣 , 如果這樣可能得到不正確的結(jié)果 .

例如：下面的例子將會刪除所有的紀錄，而不是 KING 的記錄；

DECLARE    
 Ename varchar2(20) := ’KING’; 
BEGIN  
 DELETE FROM emp WHERE ename=ename; 
END; 

變量命名在 PL/SQL 中有特別的講究，建議在系統(tǒng)的設(shè)計階段就要求所有編程人員共同遵守一定的要求， 使得整個系統(tǒng)的文檔在規(guī)范上達到要求。下面是建議的命名方法：

標識符	命名規(guī)則	例子
程序變量	V_name	V_name
程序常量	C_Name	C_company_name
游標變量	Name_cursor	Emp_cursor
異常標識	E_name	E_too_many
表類型	Name_table_type	Emp_record_type
表	Name_table	Emp
記錄類型	Name_record	Emp_record
SQL*Plus 替代變量	P_name	P_sal
綁定變量	G_name	G_year_sal

PL/SQL 變量類型

在前面的介紹中，有系統(tǒng)的數(shù)據(jù)類型，也可以自定義數(shù)據(jù)類型。下表是 ORACLE 類型和 PL/SQL 中的變量類型的合法使用列表：

• 變量類型

在 ORACLE8i 中可以使用的變量類型有：

• 復(fù)合類型

ORACLE 在 PL/SQL 中除了提供象前面介紹的各種類型外,還提供一種稱為復(fù)合類型的類型---記錄和表.

• 記錄類型
  記錄類型是把邏輯相關(guān)的數(shù)據(jù)作為一個單元存儲起來，稱作 PL/SQL RECORD 的域(FIELD)，其作用是存放互不相同但邏輯相關(guān)的信息。
  定義記錄類型語法如下:

 TYPE record_type IS RECORD(    
   Field1 type1  [NOT NULL]  [:= exp1 ],    
   Field2 type2  [NOT NULL]  [:= exp2 ],    
   . . .   . . .    
   Fieldn typen  [NOT NULL]  [:= expn ] ); 

例 1 ：

declare
     type test_rec is record(          
          l_name varchar2(30),
          d_id number(4));
     v_emp test_rec;
begin
     v_emp.l_name := 'Tom';
     v_emp.d_id :=1234;
     dbms_output.put_line(v_emp.l_name || ',' || v_emp.d_id);
end;

declare
     type test_rec is record(          
          l_name varchar2(30),
          d_id number(4));
     v_emp test_rec;
begin
     select last_name,department_id into v_emp
     from employees
     where employee_id = 200;
     dbms_output.put_line(v_emp.l_name || ',' || v_emp.d_id);
end;

提示
1.DBMS_OUTPUT.PUT_LINE 過程的功能類似于 Java 中的 System.out.println() 直接將輸出結(jié)果送到標準輸出中 .
2.在使用上述過程之前必須將 SQL * PLUS 的環(huán)境參數(shù) SERVEROUTPUT
設(shè)置為 ON, 否則將看不到輸出結(jié)果 : set serveroutput on
3.可以用 SELECT 語句對記錄變量進行賦值,只要保證記錄字段與查詢結(jié)果列表中的字段相配即可。

• 使用%TYPE
  定義一個變量，其數(shù)據(jù)類型與已經(jīng)定義的某個數(shù)據(jù)變量的類型相同，或者與數(shù)據(jù)庫表的某個列的數(shù)據(jù)類型 相同，這時可以使用%TYPE。
  使用%TYPE 特性的優(yōu)點在于：
  1.所引用的數(shù)據(jù)庫列的數(shù)據(jù)類型可以不必知道；

2.所引用的數(shù)據(jù)庫列的數(shù)據(jù)類型可以實時改變。

declare
     type test_rec is record(          
          l_name employee.last_name%type,
          d_id employee.department_id%type);
     v_emp test_rec;
begin
     select last_name,
     department_id into v_emp
     from employees  where employee_id = 200;
     dbms_output.put_line(v_emp.l_name || ',' || v_emp.d_id);
end;

• 使用%ROWTYPE
  PL/SQL 提供%ROWTYPE 操作符, 返回一個記錄類型, 其數(shù)據(jù)類型和數(shù)據(jù)庫表的數(shù)據(jù)結(jié)構(gòu)相一致。
  使用%ROWTYPE 特性的優(yōu)點在于：
  1.所引用的數(shù)據(jù)庫中列的個數(shù)和數(shù)據(jù)類型可以不必知道；
  2.所引用的數(shù)據(jù)庫中列的個數(shù)和數(shù)據(jù)類型可以實時改變。

declare
     v_emp employee%rowtype;
begin
     select * into v_emp
     from employees where employee_id = 200;
     dbms_output.put_line(v_emp.last_name || ',' || v_emp.department_id || ',' || v_emp.hire_date);
end;

• PL/SQL 表(嵌套表)
  PL/SQL 程序可使用嵌套表類型創(chuàng)建具有一個或多個列和無限行的變量, 這很像數(shù)據(jù)庫中的表. 聲明嵌 套表類型的一般語法如下:

TYPE type_name IS TABLE OF  
{datatype | {variable | table.column} % type | table%rowtype}; 

declare
     type dep_table_type is table of departments%rowtype;
     my_dep_table dep_table_type := dep_table_type();
begin
     my_dep_table.extend(5);
    
     for i in 1 .. 5 loop
     select * from departments
     where department_id = 200 + 10 * i;
     end loop;

     dbms_output.put_line(my_dep_table.count());
     dbms_output.put_line(my_dep_table(1).department_id);
end;

說明
1.在使用嵌套表之前必須先使用該集合的構(gòu)造器初始化它. PL/SQL 自動提供一個帶有相同名字的構(gòu)造器作為集合類型.
2.嵌套表可以有任意數(shù)量的行. 表的大小在必要時可動態(tài)地增加或減少: extend(x) 方法添加 x 個空元素到集合末尾; trim(x) 方法為去掉集合末尾的 x 個元素.

運算符和表達式(數(shù)據(jù)定義)

• 關(guān)系運算符

運算符	意義
=	等于
<> , != , ~= , ^=	不等于
<	小于
>	大于
<=	小于或等于
>=	大于或等于

• 一般運算符

運算符	意義
+	加號
-	減號
*	乘號
/	除號
:=	賦值號
=>	關(guān)系號
..	范圍運算符
｜｜	字符連接符

• 邏輯運算符

運算符	意義
IS NULL	是空值
BETWEEN AND	介于兩者之間
IN	在一列值中間
AND	邏輯與
OR	邏輯或
NOT	取返,如 IS NOT NULL, NOT IN

變量賦值

在 PL/SQL 編程中，變量賦值是一個值得注意的地方，它的語法如下：

variable  := expression ;    

variable 是一個 PL/SQL 變量, expression 是一個 PL/SQL 表達式.

• 字符及數(shù)字運算特點

空值加數(shù)字仍是空值：NULL + < 數(shù)字> = NULL
空值加（連接）字符，結(jié)果為字符：NULL || <字符串> = < 字符串>

• BOOLEAN 賦值

布爾值只有 TRUE, FALSE 及 NULL 三個值。

• 數(shù)據(jù)庫賦值

數(shù)據(jù)庫賦值是通過 SELECT語句來完成的，每次執(zhí)行 SELECT語句就賦值一次，一般要求被賦值的變量與 SELECT中的列名要一一對應(yīng)。如：

DECLARE 
     emp_id    emp.empno%TYPE :=7788; 
     emp_name emp.ename%TYPE; 
     wages     emp.sal%TYPE; 
BEGIN 
     SELECT ename, NVL(sal,0) + NVL(comm,0) INTO emp_name, wages  
     FROM emp WHERE empno = emp_id;      
     DBMS_OUTPUT.PUT_LINE(emp_name||’----‘||to_char(wages)); 
END; 

提示： 不能將 SELECT 語句中的列賦值給布爾變量

• 可轉(zhuǎn)換的類型賦值

• CHAR 轉(zhuǎn)換為 NUMBER：
  使用 TO_NUMBER 函數(shù)來完成字符到數(shù)字的轉(zhuǎn)換，如：
  v_total := TO_NUMBER(‘100.0’) + sal;
• NUMBER 轉(zhuǎn)換為 CHAR：
  使用 TO_CHAR 函數(shù)可以實現(xiàn)數(shù)字到字符的轉(zhuǎn)換，如：
  v_comm := TO_CHAR(‘123.45’) || ’元’ ;
• 字符轉(zhuǎn)換為日期：
  使用 TO_DATE 函數(shù)可以實現(xiàn) 字符到日期的轉(zhuǎn)換，如：
  v_date := TO_DATE('2001.07.03','yyyy.mm.dd');
• 日期轉(zhuǎn)換為字符：
  使用 TO_CHAR 函數(shù)可以實現(xiàn)日期到字符的轉(zhuǎn)換，如：
  v_to_day := TO_CHAR(SYSDATE, 'yyyy.mm.dd hh24:mi:ss') ;

變量作用范圍及可見性

在 PL/SQL 編程中，如果在變量的定義上沒有做到統(tǒng)一的話，可能會隱藏一些危險的錯誤，這樣的原因 主要是變量的作用范圍所致。與其它高級語言類似，PL/SQL 的變量作用范圍特點是：

• 變量的作用范圍是在你所引用的程序單元（塊、子程序、包）內(nèi)。即從聲明變量開始到該塊的結(jié)束。
• 一個變量（標識）只能在你所引用的塊內(nèi)是可見的。
• 當一個變量超出了作用范圍，PL/SQL 引擎就釋放用來存放該變量的空間（因為它可能不用了）。
• 在子塊中重新定義該變量后，它的作用僅在該塊內(nèi)。

注釋

在PL/SQL里，可以使用兩種符號來寫注釋，即：

• 使用雙 ‘-‘ ( 減號) 加注釋 PL/SQL允許用 – 來寫注釋，它的作用范圍是只能在一行有效。
  如： V_Sal NUMBER(12,2); -- 工資變量。
• 使用 /* */ 來加一行或多行注釋，如： /***********************************************/
  /* 文件名： department_salary.sql */ /***********************************************/
• 提示：被解釋存放在數(shù)據(jù)庫中的 PL/SQL 程序，一般系統(tǒng)自動將程序頭部的注釋去掉。只有在 PROCEDURE 之后的注釋才被保留；另外程序中的空行也自動被去掉

簡單例子

• 簡單數(shù)據(jù)插入例子

/* 本例子僅是一個簡單的插入，不是實際應(yīng)用。  */ 
DECLARE 
      v_ename   VARCHAR2(20) := ‘Bill’; 
      v_sal       NUMBER(7,2) :=1234.56; 
      v_deptno   NUMBER(2) := 10; 
      v_empno   NUMBER(4) := 8888; 
BEGIN 
      INSERT INTO emp ( empno, ename, JOB, sal, deptno , hiredate )   
             VALUES ( v_empno, v_ename, ‘Manager’, v_sal, v_deptno,  
             TO_DATE(’1954.06.09’,’yyyy.mm.dd’) ); 
      COMMIT; 
END; 

• 簡單數(shù)據(jù)刪除例子

/* 本例子僅是一個簡單的刪除例子，不是實際應(yīng)用。  */  
DECLARE 
     v_empno   number(4) := 8888; 
BEGIN 
     DELETE FROM emp WHERE empno=v_empno; 
     COMMIT; 
END;