第二章 PL/SQL 塊結(jié)構(gòu)和組成元素

PL/SQL 塊

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

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

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

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

PL/SQL 塊可以分為三類：

無名塊：動態(tài)構(gòu)造浮禾，只能執(zhí)行一次交胚。

子程序：存儲在數(shù)據(jù)庫中的存儲過程、函數(shù)及包等盈电。當(dāng)在數(shù)據(jù)庫上建立好后可以在其它程序中調(diào)用它們蝴簇。

觸發(fā)器：當(dāng)數(shù)據(jù)庫發(fā)生操作時，會觸發(fā)一些事件匆帚，從而自動執(zhí)行相應(yīng)的程序熬词。

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

PL/SQL 塊中可以包含子塊；

子塊可以位于 PL/SQL 中的任何部分吸重；

子塊也即 PL/SQL 中的一條命令互拾；

標(biāo)識符

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

標(biāo)識符名不能超過 30 字符晤锹；

第一個字符必須為字母摩幔；

不分大小寫；

不能用’-‘(減號);

不能是 SQL 保留字鞭铆。提示 : 一般不要把變量名聲明與表中字段名完全一樣 , 如果這樣可能得到不正確的結(jié)果 .

例如：下面的例子將會刪除所有的紀(jì)錄或衡，而不是 KING 的記錄；
DECLARE    
 Ename varchar2(20) := ’KING’; 
BEGIN  
 DELETE FROM emp WHERE ename=ename; 
END; 
變量命名在 PL/SQL 中有特別的講究车遂，建議在系統(tǒng)的設(shè)計階段就要求所有編程人員共同遵守一定的要求封断，使得整個系統(tǒng)的文檔在規(guī)范上達(dá)到要求。下面是建議的命名方法：

標(biāo)識符命名規(guī)則例子

程序變量 V_name V_name

程序常量 C_Name C_company_name

游標(biāo)變量 Name_cursor Emp_cursor

異常標(biāo)識 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

標(biāo)識符	命名規(guī)則	例子
程序變量	V_name	V_name
程序常量	C_Name	C_company_name
游標(biāo)變量	Name_cursor	Emp_cursor
異常標(biāo)識	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é)果送到標(biāo)準(zhǔn)輸出中 .
2.在使用上述過程之前必須將 SQL * PLUS 的環(huán)境參數(shù) SERVEROUTPUT
設(shè)置為 ON, 否則將看不到輸出結(jié)果 : set serveroutput on
3.可以用 SELECT 語句對記錄變量進(jìn)行賦值,只要保證記錄字段與查詢結(jié)果列表中的字段相配即可剪况。

使用%TYPE
定義一個變量教沾，其數(shù)據(jù)類型與已經(jīng)定義的某個數(shù)據(jù)變量的類型相同，或者與數(shù)據(jù)庫表的某個列的數(shù)據(jù)類型相同译断，這時可以使用%TYPE授翻。
使用%TYPE 特性的優(yōu)點(diǎn)在于：
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)點(diǎn)在于：
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 個元素.

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

關(guān)系運(yùn)算符

運(yùn)算符意義

= 等于

<> , != , ~= , ^= 不等于

< 小于

> 大于

<= 小于或等于

>= 大于或等于

運(yùn)算符	意義
=	等于
<> , != , ~= , ^=	不等于
<	小于
>	大于
<=	小于或等于
>=	大于或等于

一般運(yùn)算符

運(yùn)算符意義

+ 加號

- 減號

* 乘號

/ 除號

:= 賦值號

=> 關(guān)系號

.. 范圍運(yùn)算符

｜｜字符連接符

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

邏輯運(yùn)算符

運(yùn)算符意義

IS NULL 是空值

BETWEEN AND 介于兩者之間

IN 在一列值中間

AND 邏輯與

OR 邏輯或

NOT 取返,如 IS NOT NULL, NOT IN

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

變量賦值

在 PL/SQL 編程中淮菠，變量賦值是一個值得注意的地方男公，它的語法如下：
variable  := expression ;    
variable 是一個 PL/SQL 變量, expression 是一個 PL/SQL 表達(dá)式.

字符及數(shù)字運(yùn)算特點(diǎn)

空值加數(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 的變量作用范圍特點(diǎn)是：

變量的作用范圍是在你所引用的程序單元（塊屋摔、子程序、包）內(nèi)替梨。即從聲明變量開始到該塊的結(jié)束钓试。

一個變量（標(biāo)識）只能在你所引用的塊內(nèi)是可見的。

當(dāng)一個變量超出了作用范圍副瀑，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;