# PL/SQL
### 常見分隔符
+, - , * , / 加, 減, 乘, 除
** 次方(如10 ** 3 為1000)
= , >= , <= , > , < 等同於, 大於等於, 小於等於, 大於, 小於
<>, '=, ~=, ^=, != 皆為不等於
:= 賦值
% 屬性索引
### 比較運算符
**LIKE**: 符合LIKE條件回傳true，否則false
**BETWEEN**: 當值有在BETWEEN X AND Y裡面，回傳true，否則false
**IN**: 當值有在IN(set)裡面，回傳true，否則false
**IS NULL**: 當值非空時會回傳true，否則false
### 邏輯運算符
**AND**: (A and B) 兩者皆true時才為true，否則為false
**OR**: (A OR B) 其中一者為true即為true，兩者皆false才為false
**NOT**: 如條件為false時反轉為true，反之亦然
### 印出資料
dbms_output.put_line('欲印出值'); PL/SQL的印出值
## PL/SQL語法結構
```sql
DECLARE
宣告區域;
BEGIN
執行操作區域;
EXCEPTION --不一定會有EXCEPTION區
捕捉例外區域;
END;
```
初始化賦值: 可以用: =或DEFAULT設預設值
```sql
counter binary_integer := 0;
greetings varchar2(20) DEFAULT 'Have a Good Day';
```
常數賦值: 用:=賦值，賦值後不可變更其值
```sql
PI CONSTANT NUMBER := 3.141592654;
```
### %TYPE V.S %ROWTYPE
%TYPE: 通常用來指定與甚麼column型別相同，當被參照變量的型別改變時,此新定義的變量的型別也會隨之改變
%ROWTYPE: 通常用來指定與甚麼table型別相同，當被參照變量的型別改變時,此新定義的變量的型別也會隨之改變
ex.
```sql
DECLARE
V_ORG_NAME ORG.NAME%TYPE --代表與ORG裡的NAME欄位相同
V_ORG ORG%ROWTYPE --代表與ORG裡的全欄位相同
BEGIN
SELECT NAME INTO V_ORG_NAME FROM ORG;
dbms_output.put_line(‘部門名稱:’ || V_ORG_NAME)
SELECT * INTO V_ORG FROM ORG;
dbms_output.put_line(‘部門名稱:’ || V_ORG.NAME)
END
```
**Into**: 將某欄位查出結果指定給某變數
```sql
declare
value_xxx tablexxx.columnxx%type;
begin
select tx.columnxx into value_xxx from tablexxx tx where tx.columnyy = 123123;
dbms_output.put_line( value_xxx );
end;
```
**Dynamic SQL**: 將值以變數動態傳入SQL的方法
```sql
declare
value_xxx t_table_xxx.column_xxx%type;
v_sql varchar2(2000) := 'select tx.column_xxx from t_table_xxx tx where tx.columnxxx = :1 and tx.columnyyy = :2' ; --宣告:xxx做為預帶入參數
begin
execute immediate v_sql into value_xxx using 'Y' ,1; --用execute immediate執行，並實際帶入參數
dbms_output.put_line( value_xxx );
end;
```
Exception
**Exception語法**: when 某exception then 執行程式碼，others包括所有exception，注意: 包覆可能出現exception的區域要像java的try catch一樣另外用一個begin…end;夾起來
```sql
declare
value_xxx table_xxx.column_xxx%type;
begin
begin
select tx.column_xxx
into value_xxx
from table_xxx tx
where tx.column_yyy in (20600152, 20600153);
dbms_output.put_line(value_xxx);
exception
when others then
dbms_output.put_line('all others exception!');
end;
end;
```
**自訂Exception**:
```sql
DECLARE
v_error exception;
v_name varchar2(20) :='cat';
BEGIN
dbms_output.put_line('I’m a ' || v_name);
if v_name = 'cat' then -- 如果滿足條件則返回v_error
raise v_error;
end if;
EXCEPTION
when v_error then
dbms_output.put_line('I m a cat exception');
END;
```
## 條件控制
**IF-THEN條件控制**
```sql
IF (a <= 20) THEN
dbms_output.put_line(‘value a<=20’);
END IF;
```
**IF-THEN-ELSE條件控制**
```sql
IF (a <= 20) THEN
dbms_output.put_line(‘value a<=20’);
ELSE
dbms_output.put_line(‘value a >20’);
END IF;
```
**IF-THEN-ELSIF條件控制**
```sql
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' );
ELSE
dbms_output.put_line('None of the values is matching');
END IF;
```
**CASE條件控制**
```sql
CASE valueXX
WHEN 'value1' THEN S1; --當valueXX的值為'value1'時
WHEN 'value2' THEN S2;
ELSE Sn;
END CASE;
```
## 迴圈
**For Loop基礎寫法**: for xxx in(SQL) loop 執行程式碼 end loop;
```sql
declare
begin
for data in (select tx.columnxxx from table_xxx tx
where tx.columnyyy in (12312, 12313)) loop
dbms_output.put_line(data.columnxxx);
end loop;
end;
```
**帶入值的For Loop寫法**: for idx in 起始..結束 loop;
```sql
DECLARE
a number(2);
BEGIN
FOR a in 10..20 LOOP
dbms_output.put_line('value of a: ' || a);
END LOOP;
END;
/
```
**While Loop基礎寫法**: WHILE 條件 LOOP 執行程式碼 END LOOP;
```sql
DECLARE
a number(2) := 10;
BEGIN
WHILE a < 20 LOOP
dbms_output.put_line('value of a: ' || a);
a := a + 1;
END LOOP;
END;
/
```
**Continue用法**: 跳出本次迴圈
```sql
declare
begin
for v_idx in 1 .. 5 loop
if v_idx = 4 then
continue;
end if;
dbms_output.put_line( v_idx || '沒被continue跳過');
end loop;
end;
```
**GOTO用法**: 直接跳到預定義好的段落<<>>，非迴圈也可使用
```sql
Declare
begin
for v_idx in 1 .. 10 loop
dbms_output.put_line('now the idx is: '||v_idx);
if v_idx = 5 then
goto stopline;
end if;
end loop;
<<stopline>>
dbms_output.put_line('stop it');
end;
```
## 集合
**Nested Table宣告集合**的語法:
```sql
type xxxType is table of 型別;
v_xxxs xxxType := xxxType (); --集合初始化
```
注意一定要**初始化型別**，集合無資料時迴圈會爆
```sql
declare
type v_name_type is table of varchar2(100); --定義一個型別
v_names v_name_type := v_name_type(); --集合初始化
begin
v_names.extend; --增加集合的size，一次增加一個
v_names.extend;
v_names(1) := 'jack'; --PL/SQL索引值起始為1，不是0!
v_names(2) := 'howard';
for idx in v_names.first .. v_names.last loop
dbms_output.put_line(v_names(idx));
end loop;
end;
```
**Associative array宣告集合**的語法:
```sql
type xxxType is table of 型別 index by 索引型別;
```
Associative array ( index-by table): **迭代取集合**方法
```sql
declare
type tcp_type is table of t_contract_product%rowtype index by binary_integer;
v_tcp tcp_type;
v_idx integer;
begin
v_tcp(3).product_id := 1001;
v_tcp(3).amount := 10000;
v_tcp(5).product_id := 1002;
v_tcp(5).amount := 20000;
v_idx := v_tcp.first; --指向索引值到第一個
loop
exit when v_idx is null; --結束條件:索引值null
dbms_output.put_line(v_tcp(v_idx).product_id || ': ' || v_tcp(v_idx).amount);
v_idx := v_tcp.next(v_idx); --像迭代器一樣持續取出下一個
end loop;
end;
```
## Cursor游標
分為**隱式游標**與**顯式游標**
隱式游標: 無法控制隱式遊標其中的信息。
針對SQL有％FOUND，％ISOPEN，％NOTFOUND，和％ROWCOUNT屬性
顯式游標: 可以定義遊標獲得更多的控製權的上下文的區域
總共有4步驟:
聲明游標
```sql
cursor xxxcursor is SQL
```
打開游標
```sql
OPEN xxxcursor;
```
獲取游標
```sql
FETCH xxxcursor INTO c_id, c_name, c_addr;
```
關閉游標
```sql
CLOSE xxxcursor;
```
**For Loop Cursor寫法**: 先宣告cursor xxxcursor is SQL指令，再for xxx in xxxcursor loop 執行程式碼 end loop;
```sql
declare
cursor xxxcursor is
select txx.columnxxx from table_xxx txx
where txx.columnyy in (1, 2);
begin
for e in xxxcursor loop
dbms_output.put_line(e.columnxxx);
end loop;
end;
```
**For loop cursor寫法 + 塞入集合裡**
```sql
declare
cursor cursorxxx is select tcp.column_xxx from table_xxx tcp where rownum <=50;
type t_value_list is table of table_xxx.column_xxx%type;
valuexxx_list t_value_list := t_value_list();
counter integer := 1;
begin
for data in cursorxxx loop
valuexxx_list.extend; --增加list大小1
valuexxx_list(counter) := data.column_xxx;
dbms_output.put_line(valuexxx_list(counter));
counter := counter +1;
end loop;
end;
```
**Loop Cursor open寫法(顯式游標寫法)**: 一樣先宣告cursor，但cursor需要open與close
```sql
declare
value_xxx table_xxx.policy_code%type;
value_yyy table_xxx.policy_id%type;
cursor p_cursor is
select txx.column_xxx,column_yyy
from table_xxx txx
where txx.column_xxx in (206, 207);
begin
open p_cursor;
loop
fetch p_cursor into value_xxx,value_yyy;
exit when p_cursor%notfound;
dbms_output.put('value_xxx='||value_xxx);
dbms_output.put_line(' value_yyy='||value_yyy);
end loop;
close p_cursor;
end;
```
## PROCEDURES V.S FUNCTION
兩者使用方法很接近，主要差別在於FUNCTION有回傳值(RETURN)，而PROCEDURE則沒有，但FUNCTION回傳時為回傳單一值，直接從JAVA call回時方便，但如果需要回傳多個值時，則建議使用PROCEDURE，OUT多個參數一次拿回。
PROCEDURE的IN/OUT/INOUT: 用在傳入參數時，語法: 參數名 [IN/OUT/INOUT] 型別，不寫時為**預設為IN**，IN代表**可傳入不可傳出**，表示該值將被從外部傳遞，OUT代表**可傳出不可傳入**，表示該參數將被用於PROCEDURE外返回一個值，INOUT則代表**可傳入也可傳出**
-建立FUNCTION或PROCEDURES時，記得切到command window，可以清楚看到建立是否成功及相關訊息
**最基礎的PROCEDURE創建語法結構**
```sql
CREATE PROCEDURE Procedure名稱(傳入參數，如果有)
IS(或AS)
宣告區域;
BEGIN
執行操作區域;
END;
/
```
範例
```sql
CREATE PROCEDURE p_get_xxx(a_in number,b_out varchar2(100),c_out varchar2(100))
AS
BEGIN
執行程式碼...
END;
```
呼叫
```sql
declare
v_in1 number;
v_out1 varchar2(100);
v_out2 varchar2(100);
begin
a_in:=17771661;
p_get_xxx(v_in1,v_out1,v_out2);
dbms_output.put_line(v_out1);
dbms_output.put_line(v_out2);
end;
```
或使用pl/sql developer的test測試(直接在方法上右鍵->test)
**最基礎的FUNCTION創建語法結構**
```sql
CREATE FUNCTION Procedure名稱(傳入參數，如果有)
IS(或AS) RETURN回傳型別;
宣告區域;
BEGIN
執行操作區域;
RETURN 回傳值;
END;
/
```
範例
```sql
CREATE FUNCTION f_get_xxx(i_value number) RETURN tablexxx.columnxxx%type
AS
valuexxx tablexxx.columnxxx%type;
BEGIN
執行程式碼...
RETURN valuexxx;
END;
```
在SQL裡呼叫(等同多一個利用function查出來的欄位)
```sql
select f_get_xxx(74470), tcp.* from tablexxx tcp where tcp.columnxx = 74470;
```
直接呼叫
```sql
begin
dbms_output.put_line( f_get_xxx(74470));
end;
```
查詢PROCEDURE或FUNCTION創建狀態(需用**大寫**):
如果STATUS欄位顯示VALID代表編譯成功，INVALID則代表失敗
```sql
begin
select * from all_objects al where al.OBJECT_NAME = 'PROCEDURE或FUNCTION名稱';
end;
```
刪除FUNCTION或PROCEDURE:
```sql
Drop FUNCTION FUNCTION名稱;
Drop PROCEDURE PROCEDURE名稱;
```
## OTHERS
**RECORD**: 功用是做為一個自訂的型別(類似class)，語法:
```
Type名稱 is record (
變數1 型別1,
變數2 型別2
);
```
**PACKAGE**: 用來將RECORD及FUNCTION、PROCEDURE包裝，分為兩大部分，Specification及Body，Specification部分主要用來做宣告/定義，Body部分則主要用來實作，最基本的Specification及Body結構如以下包覆
```sql
CREATE OR REPLACE PACKAGE 包裝名稱 [IS/AS]
END 包裝名稱;
/
CREATE OR REPLACE PACKAGE BODY 包裝名稱 [IS/AS]
END 包裝名稱;
/
```
使用:直接呼叫該package名.方法即可使用
```sql
v_info := pkg_getxxx_new.p_get_xxx(123);
```
**TRIGGER**: 觸發器，讓SQL能在特定時機時觸發對應行為，語法:
```sql
CREATE OR REPLACE TRIGGER 觸發器名稱
BEFORE [INSERT|UPDATE|DELETE]
ON 資料表
FOR EACH ROW
DECLARE
BEGIN
執行行為
END 觸發器名稱;
```
範例:在UPDATE時自動填入系統時間
```sql
create or replace trigger emp_trg
before update
on emp
for each row
declare
begin
:new.update_time :=sysdate; --新的值會自動放入sysdate
end emp_trg;
```
Sign in with Wallet
Connect another wallet