---
tags: 學習筆記, DB, 工具書
---
PL/SQL
======
## 基本結構
PL/SQL 撰寫有宣告、程式內容、例外處理三個區塊，其中只有程式內容是必要的其餘都是可選區塊。
```plsql
DECLARE
<declarations section>
BEGIN
<executable command(s)>
EXCEPTION
<exception handling>
END;
```
運算子
|運算子|功能|
|:--:|:--:|
|+, -, \*, /|加法，減法/否定，乘法，除法|
|%|屬性索引|
|'|字符串分隔符|
|.|組件選擇|
|(,)|表達式或列表分隔符|
|:|主機變量指示符|
|,|項目分離符|
|"|帶引號的標識符分隔符|
|=|關係運算符|
|@|遠程訪問指示符|
|;|聲明終止符|
|:=|賦值運算符|
|=>|操作符關聯|
||||連接運算符|
|**|乘方運算符(指數)|
|<<, >>|標簽分隔符（開始和結束）|
|/\*, \*/|多行注釋分隔符（開始和結束）|
|--|單行注釋指示符|
|..|範圍操作|
|<, >, <=, >=|關係運算符|
|<>, '=, ~=, ^=|不同版本的不相等的|
## 資料類別
|分類|描述|
|:--:|:--:|
|標量|單值冇有內部組件，如 NUMBER, DATE 或 BOOLEAN|
|大對象(LOB)|指向其他數據項，如文本，圖形圖像，視頻剪輯和聲音等存儲|大對象
|複合|具有可單獨訪問的內部組件的數據項。例如，集合和記錄|
|引用|指向其他數據項|
### SUBTYPE
子類型是另一種數據類型，這是所謂的基本類型的子集。 子類型具有其基本類型相同的操作，但其隻是有效值的一個子集。
PL/SQL 預定義了一些子類型的封裝標準。例如， PL/SQL 預定義的子類型 CHARACTER 和 INTEGER 如下：
```plsql
SUBTYPE CHARACTER IS CHAR;
SUBTYPE INTEGER IS NUMBER(38,0);
```
### NULL
NULL 值表示缺少或未知的數據，蛤它不是一個整數，字符，或任何其他特定的數據類型。需要注意的是 NULL 不是一樣的空數據串或空字符值'\0'。 NULL 可以被分配，但它不能與任何東西等同，包括其本身。
### 變數宣告
變數必須於宣告區塊進行宣告，或是在 package 中做全域變數宣告。
```plsql
variable_name [CONSTANT] datatype [NOT NULL] [:= | DEFAULT initial_value]
```
```plsql
sales number(10, 2);
pi CONSTANT double precision := 3.1415;
name varchar2(25);
address varchar2(100);
counter binary_integer := 0;
greetings varchar2(20) DEFAULT 'Have a Good Day';
```
### SELECT INTO 變數
透過 SELECT INTO 可以將資料放入變數中(類別一致)。
```plsql
DECLARE
c_id customers.id%type := 1;
c_name customers.name%type;
c_addr customers.address%type;
c_sal customers.salary%type;
BEGIN
SELECT name, address, salary INTO c_name, c_addr, c_sal
FROM customers
WHERE id = c_id;
dbms_output.put_line
('Customer ' ||c_name || ' from ' || c_addr || ' earns ' || c_sal);
END;
```
### 常數
```plsql
PI CONSTANT NUMBER := 3.141592654;
```
## 條件控制
### IF
IF 語句關聯的條件通過 THEN 和 END IF 關鍵字封閉的語句序列。如果條件為 TRUE，語句得到執行，並且如果條件為 FALSE 或 NULL，那麼 IF 語句什麼都不做。
```plsql
IF condition THEN
S;
[ELSE S]
END IF;
```
```plsql
IF (a <= 20) THEN
c:= c+1;
END IF;
IF color = red THEN
dbms_output.put_line('You have chosen a red car')
ELSE
dbms_output.put_line('Please choose a color for your car');
END IF;
```
### ELSIF
在 IF-THEN-ELSIF 語句可以幾種選擇之間進行選擇。一個 IF-THEN 語句可以跟著一個可選的 ELSIF...ELSE 語句。 ELSIF 子句允許添加附加條件。
- 它是 ELSIF, 而不是 ELSEIF
- 一個 IF-THEN 語句可以有零或一個 ELSE 語句，它必須跟從 ELSIF 語句。
- 一個 IF-THEN 語句可以有0到多個 ELSIF，它們必須在 ELSE 之前。
- 一旦某個 ELSIF 成功，任何剩餘的 ELSIF 或其他都不被測試。
```plsql
DECLARE
a number(3) := 100;
BEGIN
IF ( a = 10 ) THEN
dbms_output.put_line('Value of a is 10' );
ELSIF ( a = 20 ) THEN
dbms_output.put_line('Value of a is 20' );
ELSIF ( a = 30 ) THEN
dbms_output.put_line('Value of a is 30' );
ELSE
dbms_output.put_line('None of the values is matching');
END IF;
dbms_output.put_line('Exact value of a is: '|| a );
END;
```
### CASE
```plsql
CASE selector
WHEN 'value1' THEN S1;
WHEN 'value2' THEN S2;
WHEN 'value3' THEN S3;
...
ELSE Sn; -- default case
END CASE;
CASE
WHEN selector = 'value1' THEN S1;
WHEN selector = 'value2' THEN S2;
WHEN selector = 'value3' THEN S3;
...
ELSE Sn; -- default case
END CASE;
```
### LOOP
必須透過 EXIT 離開迴圈。
```plsql
DECLARE
x number := 10;
BEGIN
LOOP
dbms_output.put_line(x);
x := x + 10;
IF x > 50 THEN
exit;
END IF;
END LOOP;
-- after exit, control resumes here
dbms_output.put_line('After Exit x is: ' || x);
END;
DECLARE
x number := 10;
BEGIN
LOOP
dbms_output.put_line(x);
x := x + 10;
exit WHEN x > 50;
END LOOP;
-- after exit, control resumes here
dbms_output.put_line('After Exit x is: ' || x);
END;
```
### WHILE
只要條件為真就不斷進行迴圈
```plsql
WHILE condition LOOP
sequence_of_statements
END LOOP;
```
### FOR
```plsql
DECLARE
a number(2);
BEGIN
FOR a in 10 .. 20 LOOP
dbms_output.put_line('value of a: ' || a);
END LOOP;
END;
```
REVERSE 關鍵字使迴圈順序相反(20 -> 10)
```plsql
DECLARE
a number(2) ;
BEGIN
FOR a IN REVERSE 10 .. 20 LOOP
dbms_output.put_line('value of a: ' || a);
END LOOP;
END;
```
### CONTINUE 、 EXIT
與其他語言差不多
### GOTO
```plsql
DECLARE
a number(2) := 10;
BEGIN
<<loopstart>>
-- while loop execution
WHILE a < 20 LOOP
dbms_output.put_line ('value of a: ' || a);
a := a + 1;
IF a = 15 THEN
a := a + 1;
GOTO loopstart;
END IF;
END LOOP;
END;
```
- goto 語句不能跳轉到 IF 語句， CASE 語句，循環語句或子塊。
- goto 語句不能從一個分支 IF 語句子句到另一個或從一個 CASE 語句 WHEN 子句到另一個。
- goto 語句不能從外部塊分支成子塊(即一個內 BEGIN-END 塊)。
- goto 語句不能分支出來一個子程序。提前結束子程序，要麼使用 RETURN 語句或子程序結束前在正確的 GOTO 分支的地方。
- goto 語句不能從一個異常處理分支回到當前 BEGIN-END 塊。但是， GOTO 語句可以從一個異常處理程序跳轉到一個封閉塊。
## 字串
PL/SQL 字符串實際上是一個可選的尺寸規格字符序列。字符可以是數字，字母，空白，特殊字符或全部的組合。 PL/SQL 提供了三種類型的字符串：
- 固定長度字符串：在這樣的字符串，程序員指定的長度，同時聲明該字符串。該字符串是右填充空格以達到指定的長度。
- 變長字符串：在這樣的字符串，最大長度可達32,767，為字符串指定，並不需要填充。
- 字符大對象(CLOB)：這是可變長度的字符串，可以達到128兆兆字節。
Oracle 數據庫提供了大量的字符串數據類型，如： CHAR，NCHAR，VARCHAR2，NVARCHAR2，CLOB 和 NCLOB。前麵加上一個'N'的數據類型為“國家字符集”數據類型，即存儲 Unicode 字符數據。
### 字串方法
|函數|功能|
|:--:|:--:|
|ASCII(x);|返回字符 x 的 ASCII 值|
|CHR(x);|返回字符 x 的 ASCII 值|
|CONCAT(x, y);|連接字符串 x 和 y ，並返回附加的字符串|
|INITCAP(x);|每個單詞的首字母x中轉換為大寫，並返回該字符串|
|INSTR(x, find_string [, start] [, occurrence]);|搜索 find_string 在x中並返回它出現的位置|
|INSTRB(x); |返回另一個字符串中字符串的位置，但返回以字節為單位的值|
|LENGTH(x); |返回x中的字符數|
|LENGTHB(x); |返回為單字節字符集的字節的字符串的長度|
|LOWER(x); |在x轉換為小寫字母，並返回該字符串|
|LPAD(x, width [, pad_string]) ; |X 用空格向左填充，把字符串的總長度達寬字符|
|LTRIM(x [, trim_string]); |從 x 的左修剪字符|
|NANVL(x, value); |如果 x 匹配 NaN 的特殊值（非數字）則返回其值，否則返回 x|
|NLS_INITCAP(x); |相同 INITCAP 函數，但它可以使用不同的排序方法所指定 NLSSORT |
|NLS_LOWER(x) ; |同樣的，不同的是它可以使用不同的排序方法所指定 NLSSORT LOWER 函數|
|NLS_UPPER(x); |相同，不同之處在於它可以使用不同的排序方法所指定 NLSSORT UPPER 函數|
|NLSSORT(x); |改變排序的字符的方法。任何 NLS 函數之前必須指定該參數;否則，默認的排序被使用|
|NVL(x, value); |返回如果 x 為 null 返回 null; 否則返回 x|
|NVL2(x, value1, value2); |如果 x 不為 null 返回 value1; 如果 x 為 null，則返回 value2|
|REPLACE(x, search_string, replace_string); |搜索 x 對於 SEARCH_STRING 並替換使用 replace_string 它|
|RPAD(x, width [, pad_string]); |填充 x 到右側|
|RTRIM(x [, trim_string]); |從 x 右邊修剪|
|SOUNDEX(x) ; |返回包含 x 的拚音表示形式的字符串|
|SUBSTR(x, start [, length]); |返回x的一個子開始於由 start 指定的位置。可選長度為子字符串|
|SUBSTRB(x); |相同 SUBSTR 除外的參數均以字節代替字符的單字節字符的係統|
|TRIM(x); |從左側和右側修剪 x 字符|
|UPPER(x); |x 轉換為大寫字母，並返回該字符串|
## 陣列
PL/SQL 程序設計語言提供叫一種叫做 VARRAY 的數據結構，其可存儲相同類型元素的一個固定大小的連續集合。
```plsql
TYPE namearray IS VARRAY(5) OF VARCHAR2(10);
Type grades IS VARRAY(5) OF INTEGER;
```
- 在 Oracle 環境中，可變數組的起始索引始終為1
- 可以初始化使用 VARRAY 類型，它具有相同的名稱變長數組的構造方法 VARRAY 元素
- 可變數組是一維陣列
- 一個 VARRAY 當它被聲明自動為 NULL 值，必須初始化之前，它的元素可以被引用
## PROCEDURE
- 聲明部分
它是一個任選的組成部分。然而聲明部分用於子程序並在開始時使用 DECLARE 關鍵字。它包含：類型，光標，常量，變量，異常和嵌套子程序聲明。這些項局部於子程序和停止時，子程序完成執行完成前存在
- 執行部分
這是一個強製性的一部分，包含執行指定操作語句
- 異常處理部分
這也是一個可選部分，它包含處理運行時錯誤的代碼
```plsql
DECLARE
a number;
b number;
c number;
PROCEDURE findMin(x IN number, y IN number, z OUT number) IS
BEGIN
IF x < y THEN
z:= x;
ELSE
z:= y;
END IF;
END;
BEGIN
a:= 23;
b:= 45;
findMin(a, b, c);
dbms_output.put_line(' Minimum of (23, 45) : ' || c);
END;
```
實際參數可以通過以下三種方式傳遞：
- 位置標記
```plsql
findMin(a, b, c, d);
```
- 命名符號
```plsql
findMin(x=>a, y=>b, z=>c, m=>d);
```
- 混合符號
```plsql
findMin(a, b, c, m=>d);
```
位置標記應先於指定符號
## FUNCTION
```plsql
DECLARE
a number;
b number;
c number;
FUNCTION findMax(x IN number, y IN number)
RETURN number
IS
z number;
BEGIN
IF x > y THEN
z:= x;
ELSE
Z:= y;
END IF;
RETURN z;
END;
BEGIN
a:= 23;
b:= 45;
c := findMax(a, b);
dbms_output.put_line(' Maximum of (23,45): ' || c);
END;
```
可以遞迴呼叫
## CURSOR
Oracle 會創建一個存儲區域，被稱為上下文區域，用於處理 SQL 語句，其中包含需要處理的語句，例如所有的信息，行數處理，等等。
遊標是指向這一上下文的區域。 PL/SQL 通過控製光標在上下文區域。遊標持有的行(一個或多個)由 SQL 語句返回。行集合光標保持的被稱為活動集合。
可以命名一個光標，它可以在程序中獲取和處理 SQL 語句，一次返回的行引用。有兩種類型的遊標：
- 隱式遊標
- 顯式遊標
### 隱式遊標
Oracle 在一個 SQL 語句的執行，當沒有顯式遊標的語句隱式遊標時自動創建。程序員無法控製隱式遊標其中的信息。
每當發出一個 DML 語句（INSERT，UPDATE 和 DELETE ），隱式遊標與此語句關聯。對於 INSERT 操作時，光標保持一個需要插入的數據。對於 UPDATE 和 DELETE 操作，光標標識會受到影響的行。
在PL/SQL，可以參考最近的隱式遊標的 SQL 遊標，它有類似％FOUND，％ISOPEN，％NOTFOUND，和 ％ROWCOUNT 屬性。在SQL遊標有額外的屬性，％BULK_ROWCOUNT 和 ％BULK_EXCEPTIONS，設計用於所有語句中使用。下表提供了最常用的屬性的描述：
|屬性|描述|
|:--:|:--:|
|%FOUND|返回 TRUE 如果一個 INSERT ，UPDATE 或 DELETE 語句影響了一行或多行或 SELECT INTO 語句返回一行或多行。否則，它將返回 FALSE 。|
|%NOTFOUND|邏輯相反%FOUND。返回 TRUE 如果一個 INSERT，UPDATE 或 DELETE 語句影響冇有行或 SELECT INTO 語句返回任何行。否則，它將返回 FALSE。|
|%ISOPEN|隱式遊標總是返回 FALSE，因為 Oracle 執行其相關的 SQL 語句之後自動關閉 SQL 遊標。|
|%ROWCOUNT|返回受 INSERT，UPDATE 影響的行數，或 DELETE 語句，或者通過一個 SELECT INTO 語句返回。|
```plsql
DECLARE
total_rows number(2);
BEGIN
UPDATE customers
SET salary = salary + 500;
IF sql%notfound THEN
dbms_output.put_line('no customers selected');
ELSIF sql%found THEN
total_rows := sql%rowcount;
dbms_output.put_line( total_rows || ' customers selected ');
END IF;
END;
```
### 顯式遊標
顯式遊標是程序員定義遊標獲得更多的控製權的上下文的區域。顯式遊標應在 PL/SQL 塊的聲明部分中定義。這是創建一個 SELECT 語句返回多行。
創建顯式遊標語法是：
```plsql
CURSOR cursor_name IS select_statement;
```
使用顯式遊標的工作包括四個步驟：
1. 聲明遊標用於初始化在存儲器
```plsql
CURSOR c_customers IS
SELECT id, name, address FROM customers;
```
2. 打開遊標分配內存
```plsql
OPEN c_customers;
```
3. 獲取遊標檢索數據
```plsql
FETCH c_customers INTO c_id, c_name, c_addr;
```
4. 關閉遊標釋放分配的內存
```plsql
CLOSE c_customers;
```
```plsql
DECLARE
c_id customers.id%type;
c_name customers.name%type;
c_addr customers.address%type;
CURSOR c_customers is
SELECT id, name, address FROM customers;
BEGIN
OPEN c_customers;
LOOP
FETCH c_customers into c_id, c_name, c_addr;
EXIT WHEN c_customers%notfound;
dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);
END LOOP;
CLOSE c_customers;
END;
```
## 記錄
PL/SQL 記錄就是可以容納不同類型的數據項的數據結構。記錄由不同字段，類似於數據庫表的行。
例如，要保留跟蹤圖書館中的書籍。可能要跟蹤有關每本書下麵的屬性類似：標題，作者，主題，圖書ID。包含一個字段為每個這些項目記錄允許進行處理書籍作為一個邏輯單元信息。
PL/SQL 可以處理記錄的以下幾種類型：
- 基於數據表
- 基於遊標的記錄
- 用戶自定義記錄
### 表
```plsql
DECLARE
customer_rec customers%rowtype;
BEGIN
SELECT * into customer_rec
FROM customers
WHERE id = 5;
dbms_output.put_line('Customer ID: ' || customer_rec.id);
dbms_output.put_line('Customer Name: ' || customer_rec.name);
dbms_output.put_line('Customer Address: ' || customer_rec.address);
dbms_output.put_line('Customer Salary: ' || customer_rec.salary);
END;
```
### 遊標
```plsql
DECLARE
CURSOR customer_cur is
SELECT id, name, address
FROM customers;
customer_rec customer_cur%rowtype;
BEGIN
OPEN customer_cur;
LOOP
FETCH customer_cur into customer_rec;
EXIT WHEN customer_cur%notfound;
DBMS_OUTPUT.put_line(customer_rec.id || ' ' || customer_rec.name);
END LOOP;
END;
```
### 自定義
```plsql
DECLARE
type books is record
(title varchar(50),
author varchar(50),
subject varchar(100),
book_id number);
book1 books;
book2 books;
PROCEDURE printbook (book books) IS
BEGIN
dbms_output.put_line ('Book title : ' || book.title);
dbms_output.put_line('Book author : ' || book.author);
dbms_output.put_line( 'Book subject : ' || book.subject);
dbms_output.put_line( 'Book book_id : ' || book.book_id);
END;
BEGIN
-- Book 1 specification
book1.title := 'C Programming';
book1.author := 'Nuha Ali ';
book1.subject := 'C Programming Tutorial';
book1.book_id := 6495407;
-- Book 2 specification
book2.title := 'Telecom Billing';
book2.author := 'Zara Ali';
book2.subject := 'Telecom Billing Tutorial';
book2.book_id := 6495700;
-- Use procedure to print book info
printbook(book1);
printbook(book2);
END;
```
要訪問記錄的字段，我們使用點（.）運算符。成員訪問運算符編碼為記錄變量名和訪問字段期間。
## 異常
程序執行過程中出現錯誤情況被稱為在 PL/SQL 異常。 PL/SQL 支持程序員在程序中使用異常塊捕獲這樣的條件並采取適當的動作應對錯誤情況。有兩種類型的異常：
- 係統定義的異常
- 用戶定義的異常
### 異常處理語法
一般異常處理的語法如下。在這裡可以列出下來很多，要處理異常。默認的異常將使用 WHEN others THEN 處理
```plsql
DECLARE
c_id customers.id%type := 8;
c_name customers.name%type;
c_addr customers.address%type;
BEGIN
SELECT name, address INTO c_name, c_addr
FROM customers
WHERE id = c_id;
DBMS_OUTPUT.PUT_LINE ('Name: '|| c_name);
DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr);
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('No such customer!');
WHEN others THEN
dbms_output.put_line('Error!');
END;
```
### 拋出異常
異常是數據庫服務器自動在內部數據庫錯誤，但異常可以明確地提出由程序員使用命令 RAISE。以下是拋出異常的簡單的語法：
```plsql
DECLARE
exception_name EXCEPTION;
BEGIN
IF condition THEN
RAISE exception_name;
END IF;
EXCEPTION
WHEN exception_name THEN
statement;
END;
```
### 自定義異常
```plsql
DECLARE
c_id customers.id%type := &cc_id;
c_name customers.name%type;
c_addr customers.address%type;
-- user defined exception
ex_invalid_id EXCEPTION;
BEGIN
IF c_id <= 0 THEN
RAISE ex_invalid_id;
ELSE
SELECT name, address INTO c_name, c_addr
FROM customers
WHERE id = c_id;
DBMS_OUTPUT.PUT_LINE ('Name: '|| c_name);
DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr);
END IF;
EXCEPTION
WHEN ex_invalid_id THEN
dbms_output.put_line('ID must be greater than zero!');
WHEN no_data_found THEN
dbms_output.put_line('No such customer!');
WHEN others THEN
dbms_output.put_line('Error!');
END;
```
### 內建異常類型
|Exception|Oracle Error|SQLCODE|描述|
|:--:|:--:|:--:|:--:|
|ACCESS_INTO_NULL|06530|-6530|當一個空對象會自動分配一個值引發此異常|
|CASE_NOT_FOUND|06592|-6592|當冇有任何選擇，在當選擇一個 CASE 語句的子句，並且冇有 ELSE 子句時被引發|
|COLLECTION_IS_NULL|06531|-6531|當程序試圖申請其他收集方法不是存在未初始化的嵌套表或 VARRAY，或程序試圖值分配給未初始化的嵌套表或變長數組的元素時被引發|
|DUP_VAL_ON_INDEX|00001|-1|當重複值試圖被存儲在具有唯一索引的列時被引發|
|INVALID_CURSOR|01001|-1001|當嘗試以使這是不允許的，如關閉一個未打開的遊標的遊標操作時被引發|
|INVALID_NUMBER|01722|-1722|當一個字符的字符串轉換成一個數失敗，因為字符串不表示一個有效的數據被引發|
|LOGIN_DENIED|01017|-1017|當程序試圖登錄到數據庫使用無效的用戶名或密碼被引發|
|NO_DATA_FOUND|01403|+100|它被引發當一個SELECT INTO語句無任何行返回|
|NOT_LOGGED_ON|01012|-1012|在不連接到數據庫發出數據庫調用它被引發|
|PROGRAM_ERROR|06501|-6501|它被引發當PL/SQL有一個內部問題|
|ROWTYPE_MISMATCH|06504|-6504|當遊標取值有不兼容的數據類型的變量被引發|
|SELF_IS_NULL|30625|-30625|它被引發，當對象的成員方法被調用，但對象類型的實例冇有被初始化。|
|STORAGE_ERROR|06500|-6500|它被引發當PL/SQL內存不足或內存已損壞|
|TOO_MANY_ROWS|01422|-1422|它被引發當SELECT INTO語句返回多行|
|VALUE_ERROR|06502|-6502|當算術，轉換，截短，或大小約束錯誤時引發|
|ZERO_DIVIDE|01476|1476|它被引發當一個數試圖除以零。|
## PACKAGE
PL/SQL 包是組邏輯相關的 PL/SQL 類型，變量和子程序模式對象。
程序包將有兩個強製性的部分：
- 包裝規範定義
- 包體或定義
### 定義
規範是接口到包。它隻是聲明的類型，變量，常量，異常，遊標和子程序可從封裝外部引用。換句話說，它包含關於包的內容的所有信息，但不包括用於子程序的代碼。
置於規範的所有對象被稱為公共對象。任何子程序在封裝主體中冇有包定義但編碼被稱為私有對象。
下面的代碼片段顯示了具有單一的程序包規範定義。一個包中可以定義的全局變量和多個程序或函數。
```plsql
CREATE OR REPLACE PACKAGE c_package AS
-- Adds a customer
PROCEDURE addCustomer(c_id customers.id%type,
c_name customers.name%type,
c_age customers.age%type,
c_addr customers.address%type,
c_sal customers.salary%type);
-- Removes a customer
PROCEDURE delCustomer(c_id customers.id%TYPE);
--Lists all customers
PROCEDURE listCustomer;
END c_package;
```
### 主體
包體已經在包定義和其他私人聲明中聲明的各種方法，這是從代碼隱藏在包外的代碼。
```plsql
CREATE OR REPLACE PACKAGE BODY c_package AS
PROCEDURE addCustomer(c_id customers.id%type,
c_name customers.name%type,
c_age customers.age%type,
c_addr customers.address%type,
c_sal customers.salary%type)
IS
BEGIN
INSERT INTO customers (id,name,age,address,salary)
VALUES(c_id, c_name, c_age, c_addr, c_sal);
END addCustomer;
PROCEDURE delCustomer(c_id customers.id%type) IS
BEGIN
DELETE FROM customers
WHERE id = c_id;
END delCustomer;
PROCEDURE listCustomer IS
CURSOR c_customers is
SELECT name FROM customers;
TYPE c_list is TABLE OF customers.name%type;
name_list c_list := c_list();
counter integer :=0;
BEGIN
FOR n IN c_customers LOOP
counter := counter +1;
name_list.extend;
name_list(counter) := n.name;
dbms_output.put_line('Customer(' ||counter|| ')'||name_list(counter));
END LOOP;
END listCustomer;
END c_package;
```
### 使用範例
```plsql
DECLARE
code customers.id%type:= 8;
BEGIN
c_package.addcustomer(7, 'Rajnish', 25, 'Chennai', 3500);
c_package.addcustomer(8, 'Subham', 32, 'Delhi', 7500);
c_package.listcustomer;
c_package.delcustomer(code);
c_package.listcustomer;
END;
```
## 集合
集合是一個有序組具有相同的數據類型的元素。每個元素進行標識的唯一標表示其在集合中的位置。
PL/SQL 提供了三種集合類型：
- 索引表或關聯數組
- 嵌套表
- 可變大小的數組或變長數組
Oracle 文檔提供了每種類型的集合的以下特征：
|集合類型|元素數量|下標類型|密集或稀疏|在哪裡創建|可以是對象類型屬性|
|:--:|:--:|:--:|:--:|:--:|:--:|
|關聯數組(或索引表)|無界|字符串或整數|兩種都可以|隻有在 PL/SQL 塊|No|
|嵌套表|無界|整數|開始密集，可以變得稀疏|無論是在 PL/SQL 塊或模式級彆|Yes|
|可變大小的陣列(變長陣列)|有界|整數|總是密集|無論是在 PL/SQL 塊或模式級彆|Yes|
這兩種類型的 PL/SQL 表即，索引表和嵌套表，都具有相同的結構以及行使用下標符號訪問。然而，這兩種類型的表有不同一個方麵：嵌套表可以存儲在數據庫中的列，但索引表不能。
### 索引表
一個索引表（也叫關聯數組）是一組鍵 - 值對。每個鍵是唯一的，並且用於定位對應的值。鍵可以是整數或字符串。
使用以下語法創建一個索引表。在這裡，我們正在創建一個索引表命名表名，它的鍵是 subscript_type 類型以及對應值為 element_type 類型
```plsql
DECLARE
TYPE salary IS TABLE OF NUMBER INDEX BY VARCHAR2(20);
salary_list salary;
name VARCHAR2(20);
BEGIN
-- adding elements to the table
salary_list('Rajnish') := 62000;
salary_list('Minakshi') := 75000;
salary_list('Martin') := 100000;
salary_list('James') := 78000;
-- printing the table
name := salary_list.FIRST;
WHILE name IS NOT null LOOP
dbms_output.put_line
('Salary of ' || name || ' is ' || TO_CHAR(salary_list(name)));
name := salary_list.NEXT(name);
END LOOP;
END;
DECLARE
CURSOR c_customers is
select name from customers;
TYPE c_list IS TABLE of customers.name%type INDEX BY binary_integer;
name_list c_list;
counter integer :=0;
BEGIN
FOR n IN c_customers LOOP
counter := counter +1;
name_list(counter) := n.name;
dbms_output.put_line('Customer('||counter|| '):'||name_list(counter));
END LOOP;
END;
```
### 嵌套表
嵌套表是就像一個一維陣列的元素的數量。然而，嵌套表不同於在以下幾方麵的數組：
- 陣列具有元素的聲明的數量，但嵌套表不用聲明大小。嵌套表的大小可以動態地增加。
- 陣列總是密集的，也就是說，它總是有連續的下標。嵌套數組初始是致密，但是刪除它的元素時就會變成稀疏。
```plsql
DECLARE
TYPE names_table IS TABLE OF VARCHAR2(10);
TYPE grades IS TABLE OF INTEGER;
names names_table;
marks grades;
total integer;
BEGIN
names := names_table('Kavita', 'Pritam', 'Ayan', 'Rishav', 'Aziz');
marks:= grades(98, 97, 78, 87, 92);
total := names.count;
dbms_output.put_line('Total '|| total || ' Students');
FOR i IN 1 .. total LOOP
dbms_output.put_line('Student:'||names(i)||', Marks:' || marks(i));
end loop;
END;
```
### 集合方法
|函數|功能|
|:--:|:--:|
|EXISTS(n)|返回 TRUE，如果一個集合中的第 n 個元素的存在;否則返回 FALSE|
|COUNT|返回集合當前包含的元素數量|
|LIMIT|檢查集合的最大尺寸|
|FIRST|返回在使用整數下標集合的第一個（最小的）索引號|
|LAST|返回在使用整數下標集合的最後一個（最大的）索引號|
|PRIOR(n)|返回集合中索引 n 前麵的索引號|
|NEXT(n)|返回索引 n 的下一個索引號|
|EXTEND|追加1個空元素到集合|
|EXTEND(n)|追加 n 個空元素到集合|
|EXTEND(n,i)|追加的第 i 個元素的集合的 n 個拷貝|
|TRIM|移除集合的末尾一個元素|
|TRIM(n)|移除一個集合末尾的 n 個元素|
|DELETE|移除一個集合中的所有元素，設置計數為0|
|DELETE(n)|移除關聯數組用數字鍵或嵌套表中的第 n 個元素。如果關聯數組有一個字符串鍵，對應於該鍵的值的元素將被刪除。如果 n 為空，DELETE（N）什麼都不做|
|DELETE(m,n)|移除關聯數組或嵌套表範圍 m .. n 所有元素。如果 m 大於 n 或如果 m 大於或 n 是零，DELETE（M，N）什麼也不做|
### 集合異常
|集合異常|引發情況|
|:--:|:--:|
|COLLECTION_IS_NULL|嘗試在一個原子空(null)的集合操作|
|NO_DATA_FOUND|下標指定被刪除的元素，或關聯數組的一個不存在的元素|
|SUBSCRIPT_BEYOND_COUNT|下標超過了一個集合的元素數|
|SUBSCRIPT_OUTSIDE_LIMIT|下標超出了允許的範圍|
|VALUE_ERROR|下標為空(null)或不可轉換的鍵類型。如果該鍵被定義為 PLS_INTEGER 範圍，下標是在該範圍之外可能會出現這樣的異常|
## 物件
PL/SQL 允許定義一個對象類型，這有助於在 Oracle 的數據庫中設計的麵向對象。對象類型可以包裝複合類型。使用對象允許實現數據的具體結構現實世界中的對象和方法操作它。對象有屬性和方法。屬性是一個對象的屬性，並用於存儲對象的狀態;和方法被用於模擬其行為。
### 定義類別
```plsql
CREATE OR REPLACE TYPE rectangle AS OBJECT
(length number,
width number,
member function enlarge( inc number) return rectangle,
member procedure display,
map member function measure return number
);
```
### 實作類別
```plsql
CREATE OR REPLACE TYPE BODY rectangle AS
MEMBER FUNCTION enlarge(inc number) return rectangle IS
BEGIN
return rectangle(self.length + inc, self.width + inc);
END enlarge;
MEMBER PROCEDURE display IS
BEGIN
dbms_output.put_line('Length: '|| length);
dbms_output.put_line('Width: '|| width);
END display;
MAP MEMBER FUNCTION measure return number IS
BEGIN
return (sqrt(length*length + width*width));
END measure;
END;
```
### 使用物件
```plsql
DECLARE
r1 rectangle;
r2 rectangle;
r3 rectangle;
inc_factor number := 5;
BEGIN
r1 := rectangle(3, 4);
r2 := rectangle(5, 7);
r3 := r1.enlarge(inc_factor);
r3.display;
IF (r1 > r2) THEN -- calling measure function
r1.display;
ELSE
r2.display;
END IF;
END;
```
### 比較方式
- 映射方法(map)：映射方法它的值取決於屬性的值，以這樣的方式實現函數。例如，一個客戶對象，如果客戶代碼是相同的兩個客戶，可以認為是相同的一個。所以這兩個對象之間的關係將取決於代碼的值。
```plsql
MAP MEMBER FUNCTION measure return number IS
BEGIN
return (sqrt(length*length + width*width));
END measure;
```
- 順序方法(order)：順序方法實現一些內部邏輯比較兩個對象。例如，對於矩形(rectangle)對象，如果其兩側都大，則表示矩形(rectangle)大於另一個矩形(rectangle)。
```plsql
ORDER MEMBER FUNCTION measure(r rectangle) return number IS
BEGIN
IF(sqrt(self.length*self.length + self.width*self.width)> sqrt(r.length*r.length + r.width*r.width)) then
return(1);
ELSE
return(-1);
END IF;
END measure;
```
### 繼承
PL/SQL 允許從現有的基礎對象創建對象。為了實現繼承，基本對象應被聲明為 NOT FINAL。默認值是 FINAL。
```plsql
CREATE OR REPLACE TYPE rectangle AS OBJECT
(length number,
width number,
member function enlarge( inc number) return rectangle,
NOT FINAL member procedure display) NOT FINAL
CREATE OR REPLACE TYPE BODY rectangle AS
MEMBER FUNCTION enlarge(inc number) return rectangle IS
BEGIN
return rectangle(self.length + inc, self.width + inc);
END enlarge;
MEMBER PROCEDURE display IS
BEGIN
dbms_output.put_line('Length: '|| length);
dbms_output.put_line('Width: '|| width);
END display;
END;
```
```plsql
CREATE OR REPLACE TYPE tabletop UNDER rectangle
(
material varchar2(20);
OVERRIDING member procedure display
)
CREATE OR REPLACE TYPE BODY tabletop AS
OVERRIDING MEMBER PROCEDURE display IS
BEGIN
dbms_output.put_line('Length: '|| length);
dbms_output.put_line('Width: '|| width);
dbms_output.put_line('Material: '|| material);
END display;
```
### ABSTRACT
```plsql
CREATE OR REPLACE TYPE rectangle AS OBJECT
(length number,
width number,
NOT INSTANTIABLE NOT FINAL MEMBER PROCEDURE display)
NOT INSTANTIABLE NOT FINAL
```
Sign in with Wallet
Connect another wallet