Oracle學習之二 PL/SQL塊編程基礎

文章目錄

• 1.1PL/SQL塊的類型
• 2.1 標量變數
• 2.2 複合變數
• 2.3 引用變數
• 2.4 LOB變數
• 3.1 注釋
• 3.2 PL/SQL代碼編寫規則

1、PL/SQL塊簡介

塊（Block）是PL/SQL的基礎程式單元，編寫PL/SQL程式實際就是編寫PL/SQL塊。要完成相對簡單的應用功能，可能只需要一個PL/SQL塊；而如果要實現複雜的應用功能，那麼可能需要一個PL/SQL塊中潛逃其他PL/SQL塊。編寫PL/SQL應用模組，塊的嵌套層次沒有限制。

PL/SQL是一種塊結構語言，這意味著PL/SQL程式可以分為邏輯塊。PL/SQL塊由3個部分組成：定義部分、執行部分、異常處理部分。其中，定義部分用於定義變數、常量、遊標、使用者自訂異常、複雜資料類型等；執行部分用於實現應用模組功能，該部分包含了要執行的PL/SQL語句和sql語句；異常處理部分用於處理執行部分可能出現的運行錯誤。

1.1PL/SQL塊的類型  ●匿名塊

所謂匿名塊就是指沒有名稱的PL/SQL塊。匿名塊既可以內嵌到應用程式中，也可以在互動環境中直接使用。例如：

declare     v_avgsal number(6,2)begin    select avg(sal) into v_avgsal  from emp where empno=&no;     dbms_output.putline('平均工資'||v_avgsal  );end;

● 子程式

子程式包括預存程序、函數、包。 通過將商業邏輯和企業規劃集中到PL/SQL子程式中，可以簡化用戶端程式的開發和維護，並且提高應用程式效能。下面簡單介紹一下各種子程式。

◆ 過程

 過程用於執行特定操作。當建立過程時，即可以指定輸入參數，也可以指定輸出參數。通過在過程中使用輸入函數，可以將應用環境的資料傳入到執行部分；通過使用輸出參數，可以將執行部分的資料傳遞到應用環境。可以使用CREATE PROCEDURE命令建立過程，樣本如下：

create procedure update_sal(name varchar2,newsal number) isbegin    update emp set sal=newsal where ename=name;end;

當調用該過程的時候，可以使用excute 或call命令。

exec update_sal('scott',1000);   call update_sal('scott',1000);

◆ 函數

函數用於返回特定的資料。當建立函數時，在函數頭部必須包含return 子句，而在函數體內必須包含return語句返回資料。可以使用create function 命令建立函數。

create function annual_income(name vachar2) return number is annual_salary number(7,2);begin    select sal*12 into annual_salary from emp where ename=name;    return annual_salary ;end;

◆ 包

包用於邏輯組合相關的過程和函數，它由包規範和包體兩部分組成。包規範用於定義公用的變數、常量、過程和函數。可以使用create package命令建立包規範。

create package emp_pkg is        procedure update_sa(name varchar2,newsal number);       function annual_income(name varchar2) return number;   end;

包規範中只包含了過程和函數的說明，而沒有過程和函數的實現代碼。包體用於實現包規範中的過程和函數，建立包體可以使用create package body 命令。

create package body emp_pkg is        procedure update_sa(name varchar2,newsal number) is

   begin

       update emp set sal=newsal where ename=name; 

  end;       function annual_income(name varchar2) return number is

       annual_salary number(7,2); 

      begin 

        select sal*12 into annual_salary from emp where ename=name; 

        return annual_salary ; 

     end;

 end;

當調用包的過程和函數時在過程和函數名之前必須要帶有包名作為首碼（包名.子程式名）。而如果要調用其他schema的包，還必須要加上Schema名作為首碼。

● 觸發器

觸發器是指隱含執行的預存程序。當定義觸發器的時候，必須指定觸發事件以及觸發操作，常用的觸發事件包含insert 、update 和delete語句，而觸發操作實際是一個PL/SQL塊，建立觸發器是使用 create trigger命令來完成。

create triggerupdate_cascade after update on dept for each row begin    update emp set deptno=:new.deptno where deptno=:old.deptno;end;

2、資料類型與變數

編寫PL/SQL程式時，若臨時儲存數值，必須要定義變數和常量；若在應用程式和子程式之間傳遞資料，必須要為它們指定資料類型。在編寫PL/SQL程式時，可以使用標量類型、複合類型、參考型別和LOB類型四種類型。

2.1 標量變數

標量變數是指只能存入單個數值的變數。最常用的變數就是標量變數。當定義標量變數時，必須要指定標量的資料類型。純量資料型別包括數字類型、字元類型、日期類型和布爾類型。

(1) 常用純量資料型別

常用的標量類型包括：

☆ VARCHAR2(n):該資料類型用於定義可變長度的字串，其中n表示指定字串的最大長度，其最大值為32767位元組。

☆ CHAR(n):該資料類型用於定義固定長度的字串，其中n表示指定字串的最大長度，其最大值為32767位元組。如果沒有指定n，則其預設值為1.

☆ NUMBER(p,s): 定義固定長度的整數和浮點數,p表示精度,用於指定數位總位元;s表示標度,用於指定小數點後的數字位元.

☆ DATE: 用於定義日期和時間資料,其資料長度固定為7位元組。

☆ TIMESTAMP:  Oracle9i中新增的資料類型，用於定義日期和時間資料。幅值的方法和date相同，但顯示資料的時候不僅會顯示日期，而且還會顯示時間和上下午標記。

☆ LONG和LONG RAW:  long資料類型用於定義變長字串，類似於varchar2，但其字串最大長度為32760位元組；long raw 用於定義變長的位元據，其資料最大長度為32760位元組。

☆ BOOLEAN: 用於定義布爾變數，其變數值為true，false或null。

☆ BINARY_INTEGER: 用於定義整數，其數值範圍在-2147483647和 2147483647之間。

☆ BINARY_FLOAT 和 BINARY_DOUBLE: 分別用於定義單精確度的浮點數和雙精確度的浮點數。這兩種資料類型主要用於高速的科學運算，當為BINARY_FLOAT 賦值時應該帶有尾碼f（1.5f），當為BINARY_DOUBLE賦值時，應該帶有尾碼d（3.23d）。

(2) 定義標量變數

如果要引用標量變數，必須首先在定義部分定義，然後才能在執行部分或異常部分使用這些標量變數。

identifier [CONSTANT] datatype [not null] [:=|default expr]

identifier :用於指定變數或常量的名稱。

CONSTANT：用於指定常量。當定義常量時。必須指定它的初始值，並且資料不能改變。

datatype：用於指定變數或常量的資料類型。

not null：用於強制初始設定變數（不能為null） ，當指定not null時，必須要為變數提供數值。

：= ：用於為變數或常量指定初始值。

expr： 用於指定初始值的PL/SQL運算式，可以是文本、其他變數、函數等。

(3) 使用標量變數

在定義部分標量變數之後，在執行部分和異常處理部分可以引用這些標量變數。在PL/SQL中為變數賦值不同於其他語句，必須要在等號前面加冒號（：=）。

declare v_ename varchar2(5);v_sal number(6,2);c_tax_rate CONSTANT number(3,2) :=0.03;v_tax_sal number(6,2);begin    select ename,sal into v_ename,v_sal from emp where empno=&no;    v_tax_sal:= v_sal*c_tax_rate;     dbms_output.putline(v_tax_sal);end;

(4) 使用%TYPE屬性

當PL/SQL變數儲存列值時，必須確保變數使用合適的資料類型和長度，否則在啟動並執行過程中可能出現PL/SQL運行錯誤。為了避免這種不必要的錯誤，可以使用PL/SQL屬性來定義變數。當使用%TYPE屬性定義變數時，它會按照資料庫列或者其他變數來確定新變數的類型和長度。

declare    v_ename emp.ename%TYPE;    v_sal emp.sal%TYPE;

c_tax_rate CONSTANT number(3,2) :=0.03;   v_tax_sal v_sal%TYPE;

begin       select ename,sal into v_ename,v_sal from emp where empno=&no;       v_tax_sal:= v_sal*c_tax_rate;       dbms_output.putline(v_tax_sal);   end;

 

2.2 複合變數

複合變數是指用於存放多個值的變數。在使用複合變數時，必須先用TYPE進行定義。當定義複合變數時，必須要使用PL/SQL的複合資料型別。PL/SQL的複合資料型別有：PL/SQL記錄類型、PL/SQL集合類型（包括PL/SQL表、巢狀表格以及varray）等四種。這四種類型分別在不同的情況下應用：處理單行單列資料時，用標量變數；處理單行多列資料時，使用PL/SQL記錄；處理單列多行資料時，使用集合；處理多行多列資料時，使用PL/SQL記錄和集合。

1) 、PL/SQL記錄（RECORD）

PL/SQL記錄類似於進階語言中的結構，每個PL/SQL記錄一般包含多個成員，它用於簡化處理單行多列的資料。

PL/SQL記錄的定義有兩種方法。第一種方法，先使用TYPE..IS RECORD 自訂記錄類型，然後定義記錄變數。第二種，使用表名%ROWTYPE屬性直接定義記錄變數。記錄變數定義完成後，可以通過‘記錄變數.記錄成員’的形式來引用記錄成員。

TYPE emp_recore_type IS RECORD(name emp.ename%type,salary emp.sal%type,title emp.job%type);emp_record emp_record_type;

以下是使用表名%ROWTYPE屬性定義的記錄變數：

emp_record emp%ROWTYPE

給記錄變數賦值的方法有兩種：一種時利用指派陳述式給每個成員分別賦值；一種時通過SELECT…INTO 語句賦值，例如：

emp_record.ename:='scott'；SELECT * INTO emp_record FROM emp WHERE eno=788;

用SELECT…INTO 語句賦值時，select語句中的列名列表的名稱和個數以及類型必須與記錄變數的成員個數、名稱、類型完全相同。2) 、集合類型

PL/SQL集合類型時類似於進階語言數組的一種複合資料型別，包括PL/SQL表、巢狀表格和變長數組三種類型。

☆ PL/SQL表

PL/SQL表也稱索引表，它與進階語言數組不同的是：進階語言數組的下標不能為負，但是PL/SQL表的下標可以是負數；進階語言數組的元素個數有限制，而PL/SQL表的元素個數沒有限制，並且下標沒有上下限。

☆ 巢狀表格

巢狀表格與PL/SQL表不同的是：進階語言數組和巢狀表格的下標都不能為負；進階語言的元素個數是有限制的，而巢狀表格的元素個數沒有限制。巢狀表格和PL/SQL表非常類似，但巢狀表格可以使用列的資料類型，而PL/SQL表不能作為表列的資料類型。當在表列中使用巢狀表格時，必須使用create type語句建立巢狀表格類型。

☆ VARRAY

VARRAY（變長數組）類似於巢狀表格，它可以作為表列和物件類型屬性的資料類型。但要注意，巢狀表格的元素個數沒有限制，而varray的元素個數是有限制的。

☆ 集合類型的內建方法

集合變數有很多內建方法，其調用格式為：集合類型變數.方法。

表2-1中列出了Oracle集合類型常用的方法。

表2-1 集合類型的方法

方法	描述	使用限制
COUNT	返回集合中的元素個數
DELETE	刪除集合中所有元素
DELETE(x)	刪除集合中下標為x的元素，如果x為null，則保持不變	對VARRAY非法
DELETE(x,y)	刪除元素下標x到y的元素，如果x>y集合保持不變	對VARRAY非法
EXIST(x)	如果集合元素x已經初始化，則返回true，否則返回false
EXTEND	在集合末尾添加一個元素	對索引表不合法
EXTEND(x)	在集合末尾添加x個元素	對索引表不合法
EXTEND(x,n)	在集合末尾添加元素n的x個副本	對索引表不合法
FIRST	返回集合中的第一個元素的下標，對於varray集合始終返回1。
LAST	返回集合中的最後一個元素的下標，對於varray集合傳回值始終等於COUNT。
LIMIT	返回varray集合的最大元素個數	對於巢狀表格和索引表集合無用
NEXT(x)	返回在元素x之後及緊挨著它的元素的值，如果該元素是最後一個元素，則返回null
FRIOR(x)	返回在元素x之前緊挨著它的元素的值，如果該元素是第一個元素，則返回null
TRIM	從集合末端開始刪除一個元素	對索引表不合法
TRIM(x)	從集合末端開始刪除x個元素	對索引表不合法

 

2.3 引用變數

引用變數是指用於存放數值指標的變數。通過使用引用變數可以使得應用程式共用相同對象，從而降低佔用空間。可以使用遊標變數（REF CURSUR）和物件類型變數REF ojb_type 兩種引用變數類型。

1)、REF CURSUR

當使顯式遊標時，需要在定義顯式遊標時指定響應的select語句，這種顯式遊標稱為靜態資料指標。當使用遊標變數時，在定義遊標變數時不需要指定select語句，從而實現動態資料指標操作。

2)、REF ojb_type

當編寫物件類型應用時，為了共用相同對象，可以使用REF引用物件類型，REF實際是指向對象執行個體的指標。

 

2.4 LOB變數

LOB變數是指用於儲存大批量資料的變數。Oracle將LOB分為兩種：內部LOB和外部LOB。內部LOB包括CLOB/BLOB/和NCLOB三種類型。他們的資料被儲存在資料庫中，並且支援事務操作（提交、復原、儲存點）。外部LOB只有BFILE一種類型，該類型的資料被儲存在作業系統檔案中，並且不支援事務操作。其中，CLOB和NCLOB用於儲存大批量字元資料，BLOB儲存大批量位元據，而BFILE則儲存指向作業系統檔案的指標。

 

3、編寫PL/SQL代碼3.1 注釋

注釋用於解釋當行代碼或者多行代碼的作用，從而提高了PL/SQL程式的可讀性。

☆ 單行注釋

使用“--”符號注釋單行代碼

☆ 多行注釋

使用“/*..*/”注釋多行代碼

3.2 PL/SQL代碼編寫規則1）標識符命名規則

當在PL/SQL中使用標識符定義變數、常量時，標識符名稱必須以字元開始，並且長度不能超過30個字元。

2）大小寫規則

關鍵字大寫，資料類型大寫，標識符和參數小寫，資料庫物件和列小寫。

3）代碼縮排

同級代碼對齊，下級代碼縮排。