ORACLE PL/SQLとは　〜例外処理・ORACLE関数・分岐処理等〜

#### 【背景】
ネット上でのPL/SQLについての文献が少ない為、現場で培ってきたPL/SQLについての知識をまとめようと思います。

前回の資料をご覧になって頂いている場合は既知情報になりますが、私はPL/SQLの現場経験がありますが１年と３ヶ月ほどなので作成資料について誤記等がございましたらご指導ご鞭撻のほど宜しく御願い致します。
　
前回の資料↓

https://qiita.com/yoshida_t/items/d4c0e3d8ed5fcac6bcec

---
### 目次
９.例外処理
１０.使用可能なオラクル関数（ファンクション）
１１.制御構造
１２.資料２についてのまとめ

９.例外処理
---
PL/SQLでは例外処理というものが存在。
基本的なものとしては下記の2種類。
例外処理とはPL/SQLの処理の中でエラーが起きた際の処理内容を記載する処理部分になる。

|例外処理|内容　| 
|---|---|
|事前定義例外  |ORACLEが事前に定義している一般的なエラー|
|ユーザー独自の定義例外  |開発者が独自に定義するエラー  |

おさらいになりますが、PL/SQLとしての基本構造は以下になります。

|処理|和名　　　|処理内容  | 
|---|---|---|
|DECLARE  |宣言部分|定数や変数、カーソルを宣言（定義）する箇所| 
|BEGIN  |実行部分|実際の実行部分。SQL文の実行やDECLAREで宣言した定数や変数、カーソルを使用することも可能  |
|**EXCEPTION**  |例外処理|BEGINで実行している際にエラー例外が発生した場合に処理される。ユーザー独自のエラー定義を設定することも可能  |
|END  |-|-  |

```PLSQL:基本構造
DECLARE
--宣言部分（任意）
BIGIN
--実行部分
EXCEPTION
--例外処理部分
END;
```
---
#### ・事前定義例外
PL/SQLで使用できる定義（宣言）する必要がない例外処理
※元々定義されている
|例外処理|エラー番号　|エラー内容 | 
|---|---|---|
|NO_DATA_FOUND  |ORA-01403|SELECT INTO文で1行も戻されない|
|TOO_MANY_ROWS |ORA-01422 |SELECT INTO文で複数行が戻された|

他にも色々・・・
　
 ```PLSQL:事前定義例外
DECLARE
--宣言部分（任意）
BIGIN
--実行部分
EXCEPTION
WHEN NO DATA FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO DATA FOUND');
END;
```
https://www.shift-the-oracle.com/plsql/exception/predefined-exception.html

#### ・ユーザー独自の定義例外
ユーザーが事前に定義（宣言）しておく必要がある例外処理

```PLSQL:ユーザー独自の定義例外
DECLARE
--宣言部分（任意）
USER_EXCEPTION EXCEPTION;
BIGIN
--実行部分
RAISE USER_EXCEPTION
EXCEPTION
WHEN USER_EXCEPTION THEN
DBMS_OUTPUT.PUT_LINE('USER_EXCEPTION');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('OTHERS');
END;
```

１０.使用可能なオラクル関数（ファンクション）
---
PL/SQLでは基本的にORACLEの関数が使用可能。
使用可能なオラクル関数（ファンクション）について下記に記載。

#### ・文字ファンクション

|ファンクション|説明 
|---|---|
|UPPER |文字列を大文字に変換する|
|LOWER |文字列を小文字に変換する| 
|CONCAT|文字列を連結する|
|SUBSTR|文字列から部分文字列を抜き出す|
|LPAD |文字列の左側に文字を埋め込む|
|RPAD |文字列の右側に文字を埋め込む|
|REPLACE |文字列を置換する|
|TRIM |文字列の前後にある文字を取り除く|
|LENGTH |文字列の文字数を戻す|

```PLSQL:文字ファンクション例　UPPER　LOWER
SELECT UPPER(name), LOWER(name)  FROM test;

UPPER(name) LOWER(name)
----------- -----------
TAROU       tarou
HANAKO      hanako
--UPPERは大文字に、LOWERは小文字に文字列を変換している。
```

```PLSQL:文字ファンクション例　SUBSTR
--①
SELECT SUBSTR('12345678901234567890', 8, 10)  FROM test;

SUBSTR('12345678901234567890', 8, 10)
-------------------------------------
8901234567
--先頭8文字目から10文字分を抜き出している。

--②
SELECT SUBSTR('12345678901234567890', 8)  FROM test;

SUBSTR('12345678901234567890', 8)
-------------------------------------
8901234567890
--先頭8文字目から最後の文字までを抜き出している。

--①
SELECT SUBSTR('12345678901234567890', -8)  FROM test;

SUBSTR('12345678901234567890', -8)
-------------------------------------
34567890
--末尾より前の8文字目から最後の文字までを抜き出している。
```

```PLSQL:文字ファンクション例　LPAD RPAD
SELECT LPAD(name, 10, 'X'), RPAD(name, 10, 'X')  FROM test;

name    LPAD(name, 10, '*') RPAD(name, 10, '*')
------- ------------------- -------------------
TAROU   *****TAROU          TAORU*****
HANAKO  ****HANAKO          HANAKO****
--LPADは左から指定した文字列を詰める。LOWERは右から指定した文字列を詰める。
```

```PLSQL:文字ファンクション例　REPLACE
--①
SELECT REPLACE('ABCDEFGHIJ', 'BC', 'bc')  FROM dual;
--※dualテーブルはORACLE上に自動生成されるダミーテーブル

REPLACE('ABCDEFGHIJ', 'BC', 'bc')
---------------------------------
AbcDEFGHIJ
--第２引数で指定した文字列を第３引数で指定した文字列で置き換える。

--②
SELECT REPLACE('ABCDEFGHIJ', 'BC')  FROM dual;
--※dualテーブルはORACLE上に自動生成されるダミーテーブル

REPLACE('ABCDEFGHIJ', 'BC')
---------------------------
ADEFGHIJ
--第３引数を省略すると指定した文字列は削除される。
```

---
#### ・数値ファンクション
|ファンクション|説明 
|---|---|
|ROUND |数値を四捨五入する| 
|TRUNC |数値を切り捨てる|

```PLSQL:数値ファンクション例　ROUND
SELECT ROUND(19.97), ROUND(19.97, 1), ROUND(19.97, -1) FROM dual;
--※dualテーブルはORACLE上に自動生成されるダミーテーブル

ROUND(19.61) ROUND(19.61, 1) ROUND(19.61, -1)
------------ --------------- ----------------
19           19.6            20
--ROUNDファンクションの第１引数に「1」の場合は小数点第1位で四捨五入。
--「-1」の場合は整数部1桁目で四捨五入。
```

---
#### ・日時ファンクション
|ファンクション|説明 
|---|---|
|ADD_MONTHS |数ヶ月または数ヶ月後の日時を戻す| 
|MONTHS_BETWEEN |２つの日時の期間を月単位で戻す|
|NEXT_DAY |特定の曜日の日付を戻す|
|LAST_DAY |月の最終日の日付を戻す|
|SYSDATE |データベースサーバーのOSの日時を戻す|
|ROUND |日時を丸める|
|TRUNC |日時を切り捨てる|

```PLSQL:日付ファンクション例　ADD_MONTHS
SELECT ADD_MONTHS('2024/7/27 11:41:20', 1) FROM dual;
--※dualテーブルはORACLE上に自動生成されるダミーテーブル

ADD_MONTHS('2024/7/27 11:41:20', 1)
---------------
2024/8/27 11:41:20
--ADD_MONTHSファンクションの第１引数に１ヶ月を足して出力している。
```

```PLSQL:日付ファンクション例　ROUND
SELECT ROUND(TO_DATE('2024/7/27 11:41:20')) FROM dual;
--※dualテーブルはORACLE上に自動生成されるダミーテーブル

ROUND(TO_DATE('2024/7/27 11:41:20'))
---------------
2024/8/27 00:00:00
--ROUNDファンクションの第１引数が00:00:00~11:59:59の場合は当日の00:00:00を戻す
--ROUNDファンクションの第１引数が12:00:00~23:59:59の場合は翌日の00:00:00を戻す
```

---
#### ・変換ファンクション
|ファンクション|説明 
|---|---|
|TO_CHAR |データを文字型に変換する| 
|TO_NUMBER |データを数値型に変換する|
|TO_DATE |データを日時型に変換する|

```PLSQL:変換ファンクション例
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM dual;
--※dualテーブルはORACLE上に自動生成されるダミーテーブル

TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS')
---------------
2024-7-27 11:41:20
--日付型のデータを文字列に変換しています。
--後続処理でSUBSTR関数を使用して文字列の切り取りなどが行えます
```

---
#### ・NULL関連ファンクション
|ファンクション|説明 
|---|---|
|NVL |NULLを指定した値に置き換える| 
|NVL2 |NULLかどうかに応じて異なる値を戻す|
|NULLIF |値が等しい場合にNULLを戻す|
|COALESCE |複数の値のうち、最初に見つかった非NULL値を戻す|

```PLSQL:NULL関連ファンクション例　NVL
--test3の「nenrei」カラムがNULL
SELECT name, nenrei FROM test;

name       nenrei
---------- -------- 
test1       61
test2       24
test3       

----------------------------------------------------------------------
--NVLを使用した場合
SELECT name, NVL(nenrei,0) FROM test;

name       nenrei
---------- -------- 
test1       61
test2       24
test3       0
```


```PLSQL:NULL関連ファンクション例　NVL2
--NVL2：第１引数がNULLではない場合は、第２引数を取得。
--     第１引数がNULLの場合は、第3引数を取得
--test3の「nenrei」カラムがNULL
SELECT name, nenrei FROM test;

name       nenrei
---------- -------- 
test1       61
test2       24
test3       

----------------------------------------------------------------------
--NVL2を使用した場合
SELECT name, NVL2(nenrei, nenrei, XX) FROM test;

name       nenrei
---------- -------- 
test1       61
test2       24
test3       XX -- ⇨test3に元々「44」と入っている場合は「44」と出力される。
```


---
#### ・集計ファンクション
|ファンクション|説明 
|---|---|
|SUM |合計を求める| 
|AVG |平均値を求める|
|MAX |最大値を求める|
|MIN |最小値を求める|
|COUNT |行数をカウントする|


```PLSQL:集計ファンクション例
SELECT SUM(nenrei) FROM test;

SUM(nenrei)
---------------
1231
--testテーブルに存在するnenreiカラムの集計値を出力している。
```


１１.制御構造
---
PL/SQLでもJavaなどの言語と変わらず条件分岐を行う事が可能になっています。
基本的な構文としては下記になります。

|処理|和名　　　|処理内容  | 
|---|---|---|
|IF ELSE  |条件毎による処理|条件毎による処理を実行可能| 
|WHEN CASE  |特定の値ごとの処理|指定した変数が指定の条件（値）の場合の処理を実行可能 |
|FOR LOOP  |ループ処理|特定の回数から特定の回数までのループ処理を行う  |
|WHILE |ループ処理|指定の回数分または、値のある分だけループ処理を行う  |

```PLSQL:制御構造：IF THEN ELSE
DECLARE
    v_score NUMBER := 85;
BEGIN
    -- 条件分岐の開始
    IF v_score >= 90 THEN
        -- v_score が 90 以上の場合の処理
        DBMS_OUTPUT.PUT_LINE('Grade: A');
    ELSIF v_score >= 80 THEN
        -- v_score が 80 以上 90 未満の場合の処理
        DBMS_OUTPUT.PUT_LINE('Grade: B');
    ELSE
        -- 上記以外の場合の処理
        DBMS_OUTPUT.PUT_LINE('Grade: C');
    END IF; -- IF ブロックの終了
END;


```

```PLSQL:制御構造：WHILE LOOP
-- WHILE LOOP
DECLARE
    v_counter NUMBER := 1; -- ループカウンタを1に初期化
BEGIN
    -- v_counterが5以下である限り、ループを実行
    WHILE v_counter <= 5 LOOP
        -- 現在のカウンタの値を出力
        DBMS_OUTPUT.PUT_LINE('Counter: ' || v_counter);
        
        -- カウンタを1増やす
        v_counter := v_counter + 1;
    END LOOP; -- ループの終了
END;
```

```PLSQL:制御構造：CASE WHEN
-- CASE文
DECLARE
    v_grade CHAR(1) := 'B'; -- グレードを'B'に初期化
BEGIN
    -- グレードに基づいて処理を分岐
    CASE v_grade
        WHEN 'A' THEN 
            DBMS_OUTPUT.PUT_LINE('Excellent'); -- グレードが'A'の場合の処理
        WHEN 'B' THEN 
            DBMS_OUTPUT.PUT_LINE('Good'); -- グレードが'B'の場合の処理
        WHEN 'C' THEN 
            DBMS_OUTPUT.PUT_LINE('Average'); -- グレードが'C'の場合の処理
        ELSE 
            DBMS_OUTPUT.PUT_LINE('Poor'); -- それ以外の場合の処理
    END CASE; -- CASE文の終了
END;

```

１２.資料２についてのまとめ
---
前回の資料ではPL/SQLについての前提の知識を中心に資料としてまとめていましたが、今回については例外処理と実際にPL/SQLでプログラムを組む際に使用できる関数（ファンクション）や分岐処理、ループ処理などを中心に記載しました。

私は以前「ORACLE MASTER Silver DBA」の資格取得をする過程で学習をした事があること、実際に働いている現場でDB（ORACLE）に関わる事が多かった事があり、オラクルに元々用意されている関数（ファンクション）について知見がありました。

ですが全くSQLに触れてきたことがない方もいらっしゃると思い、今回オラクル関数（ファンクション）について詳しく記載をしました。

PL/SQLと言っても**SQLの知識が基本**になり、SQL経験やJavaなどの経験がある方であれば取り掛かりやすい言語だと思います。

　
PL/SQLへの現場参画の機会がある場合は改めてこの資料をぜひ読んで頂いて参画への**前向きな検討**をしてみてください！

改めてですが間違っている箇所等ございましたらご指導ご鞭撻の程宜しくお願い致します。
ここまで閲覧頂き有難うございます！