Oralce之PL/SQL程式設計(遊標)

PL/SQL(Procedural Language/SQL)是Oracle在資料庫中引入的一種過程化程式語言。

PL/SQL塊結構

宣告部分　　執行部分（必須的）　　異常處理部分

[declare]

　　--宣告部分，可選

BEGIN

　　--執行部分，必須
　　
[exception]

　　--異常處理部分，可選

END

單行註釋：--

多行註釋：/*　　*/

基本資料型別：

number　　varchar2　　char　　long　　nchar  nvarchar2　　date　　boolean

特殊資料型別：

%type,宣告一個與指定列名稱相同的資料型別。

declare var_job emp.job%type;

record:記錄型別資料

type emp_type is record
(
var_ename varchar2(20),

var_job varchar2(20),

var_sal number
);

%rowtype:結合了%type和record特性，可以根據資料表中的行結構定義一種特殊的資料型別，用來儲存從資料表中檢索到的一行資料
rowVar_emp emp%rowtype;定義能夠儲存emp表中一行資料的變數rowVar_emp

定義變數：var_countryname varchar2(50):='中國';

定義常量: con_day constant integer:=365;

PL/SQL遊標

顯示遊標和隱式遊標：

顯示遊標：

例子

1.宣告一個檢索emp表中僱員資訊的遊標，然後開啟遊標，並指定檢索職務是“MANAGER"的僱員資訊，接著使用 fetch...into語句和while迴圈

讀取遊標中的所有僱員資訊，最後輸出讀取的僱員資訊

declare
cursor cur_emp(var_job in varchar2:='SALESMAN')
is select empno,ename,sal
from emp
where job=var_job;
type record_emp is record
(
var_empno emp.empno%type,
var_ename emp.ename%type,
var_sal emp.sal%type
);
emp_row record_emp;
begin
open cur_emp('MANAGER');
fetch cur_emp into emp_row; --讀取遊標中內容，將遊標指標移動到結果集中的第一行
while cur_emp%found loop
dbms_output.put_line(emp_row.var_ename||'的編號是'||emp_row.var_empno||',工資是'||emp_row.var_sal);
fetch cur_emp into emp_row;
end loop;
close cur_emp;
end;
/

遊標屬性：
%found:如果SQL語句至少影響到一行資料，則該屬性為true，否則為false

%notfound:與上面的功能相反

%rowcount:返回受影響的行數

%isopen:遊標開啟時，返回true,關閉時，false

隱式遊標

在執行一個SQL 語句時，Oracle會自動建立一個隱式遊標。這個遊標是記憶體中處理該語句的工作區域。

例子：

在SCOTT模式下，把emp表中銷售員的工資上調20%，然後使用隱式遊標sql的%rowcount屬性輸出上調
工資的員工數量

begin
update emp set sal = sal*(1+0.2)  where job='SALESMAN';
if sql%notfound then
dbms_output.put_line('沒有僱員調整工資');
else
dbms_output.put_line('有'||sql%rowcount||'個僱員工資上調20%');
end if;
end;
/

for語句迴圈遊標：

例子：

使用隱式遊標和for語句檢索出職務是銷售員的僱員資訊並輸出

begin
for emp_record in (select empno,ename,sal from emp where job='SALESMAN')
loop
dbms_output.put_line('僱員編號:'||emp_record.empno);
dbms_output.put_line('; 僱員名稱：'||emp_record.ename);
dbms_output.put_line('; 僱員工資：'||emp_record.sal);
end loop;
end;
/

使用顯示遊標和for語句檢索出部門編號是30的僱員資訊並輸出

declare
cursor cur_emp is
select * from emp
where deptno=30;
begin
for emp_record in cur_emp
loop
dbms_output.put('僱員編號：'||emp_record.empno);
dbms_output.put('僱員名稱：'||emp_record.ename);
dbms_output.put_line('僱員職務：'||emp_record.job);
end loop;
end;
/

綜上，在使用遊標（隱式，顯示）的for迴圈中，可以宣告遊標。但不用進行開啟遊標、讀取遊標、關閉遊標等操作，這些由Oracle系統內部自動完成

PL/SQL異常處理

在[exception]程式碼塊中

根據異常產生的機制和原理，可將Oracle系統異常分為以下兩大類：

1.預定義異常

定義在Oracle的核心PL/SQL庫中，使用者可以在自己的PL/SQL異常處理部分使用名稱對其進行標識。對這種異常情況的處理，使用者無須在程式中定義，它們由Oracle自動引發。

系統預定義異常	說明
ZERO_DIVIDE	除數為零時引發的異常
ACCESS_INTO_NULL	企圖為某個未初始化物件的屬性賦值
COLLECTION_IS_NULL	企圖使用未初始化的集合元素
CURSOR_ALREADY_OPEN	企圖再次開啟一個已經開啟過的遊標，但在重開啟之前，遊標未關閉
INVALID_CURSOR	執行一個非法的的遊標操作，如，關閉一個未開啟的遊標
INVALID_NUMBER	企圖將一個字串轉換成一個無效的數字而失敗
LOGIN_DENIED	企圖使用無效的使用者名稱或密碼連線資料庫
NO_DATA_FOUND	SELECT INTO 語句沒有返回資料
ROWTYPE_MISMATCH	主遊標變數與PL/SQL遊標變數的返回型別不相容
SELF_IS_NULL	使用物件型別時，使用空物件呼叫其方法
SUBSCRIPT_BEYOND_COUNT	元素下表超過巢狀表或VARRY中的元素
SUBSCRIPT_OUTSIDE_LIMIT	企圖使用非法索引號引用巢狀表或VARRY中的元素
SYS_INVALID_ROWID	字串向ROWID轉換時的錯誤，因為該字串不是一個有效的ROWID值
TIMEOUT_ON_RESOURCE	Oracle在等待資源時超時
TOO_MANY_ROWS	執行SELECT INTO 語句時，結果集超過一行引發的異常

2.自定義異常

有兩種

A-錯誤編碼異常

定義錯誤編碼異常編號為“-00001”的異常變數，然後想dept表中插入一條能夠“違反唯一性約束條件”的記錄，

最後在exception程式碼中輸出異常提示資訊

declare
primary_iterant exception;
pragma exception_init(primary_iterant,-00001);--關聯錯誤號和異常變數名
begin
insert into dept values(10,'軟體開發部','深圳');
exception
when primary_iterant then
dbms_output.put_line('主鍵不允許重複!');
end;
/

B-業務邏輯異常

自定義一個異常變數，在向dept表中插入資料時，若判斷loc欄位為null，則使用raise語句引發異常，並將程式執行流程轉入到exception部分進行處理

declare
null_exception exception;
dept_row dept%rowtype;
begin
dept_row.deptno:=66;
dept_row.dname:='公共部';
insert into dept
values(dept_row.deptno,dept_row.dname,dept_row.loc);
if dept_row.loc is null then
raise null_exception;
end if;
exception
when null_exception then
dbms_output.put_line('loc欄位的值不許為null');
rollback;
end;
/