やんわり現場メモ#1【PL/SQL編】

## はじめに
初投稿・初心者エンジニアということで優しい目で見てください。
（間違っていたら直したいので教えてください）
ド基本しか扱っていません。

## 目次
<dl>
  <dt>●PL/SQLって何なん？</dt>
  <dd>・どんな言語なん</dd>
<dd>・処理全体の構成</dd>
<dt>●よく出てきた</dt>
  <dd>・PROCEDURE</dd>
  <dd>・%TYPEと%ROWTYPE</dd>
  <dd>・PACKAGEとPACKAGE BODY</dd>
  <dd>・CURSOR</dd>
  <dd>・IF</dd>
  <dd>・CASE</dd>
  <dd>・FOR/LOOP</dd>
  <dt>●おまけ：Oracleの関数</dt>
</dl>

## PL/SQLって何なん？
##### どんな言語なん
SQLでは利用できない、IF(分岐)、FOR(繰り返し)などを記述でき、
効率的に処理を行うようにできるOracleの拡張言語です。

##### 処理全体の構成
宣言部・実行部・例外処理部の大きく三つのエリアに分かれていて、
それぞれのエリアで下記の役割をもっている。
```sql
DECLARE
    -- 宣言部
    -- 変数などプログラム全体で使用するオブジェクトを定義するとこ
    message VARCHAR(2) := 'こんにちは';
BEGIN
    -- 実行部
    -- SQL(SELECTど)をそのまま書けるだけでなく、分岐や繰り返しなどの処理を書けるとこ
    IF message = 'おはよう' THEN
        DBMS_OUTPUT.PUT_LINE('messageは「おはよう」');
    ELSIF message = 'こんにちは' THEN
        DBMS_OUTPUT.PUT_LINE('messageは「こんにちは」');
    END IF;
EXCEPTION
    -- 例外処理部
    -- 処理中に発生したエラーに対応させる処理を書くとこ
    WHEN OTHERS THEN NULL;
END;
```
```sql:実行結果
messageは「こんにちは」
```

## よく出てきた
##### PROCEDURE
処理全体の構成で出てきたDECLAREと違い、一連の処理をDBに保存できる。
DECLAREだと都度処理全体を書かなきゃいけない。
Javaでいうメソッドみたいなもんだと今でも思ってる。引数も渡せる。
```sql:PROCEDUREの構成
CREATE PROCEDURE <プロシージャ名> (
    -- 引数
    <引数名> IN<受け取る値> <型>
    <引数名> OUT<処理結果を格納> <型>
    <引数名> INOUT<受け取った値に処理を加えて格納> <型>
) IS
    -- 宣言部
BEGIN
    -- 処理
EXCEPTION
    -- 例外処理
END;
```
```sql:保存したPROCEDUREを実行
SQL> CALL <プロシージャ名>(引数);
```
上記で呼び出すと実行部に書いた処理が実行される
似たような用途でFUNCTIONというのがあるが、使用しなかったので割愛。

##### %TYPEと%ROWTYPE
sampleというテーブルのカラムnum1、型NUMBER(4)を格納するのに、
変数定義で
```sql:通常の変数定義
num1 NUMBER(4);
```
で変数を用意できる、テーブルのカラムそのものの定義が変わったときに、定義もそれに合わせて修正が必要になる。
%TYPEを使えばそんな修正が必要なくなる。
```sql:%TYPE
<変数名> <テーブル名>.<カラム名>%TYPE;
-- 使用例
user_no sample.num1%TYPE;
```
定義する型をテーブルのカラムから直接参照することができるので、カラムの定義が変わっても修正が必要なくなる。
%ROWTYPEは特定のカラムではなく、テーブルのカラム全体を定義できる。
```sql:%ROWTYPE
<変数名> <テーブル名>%ROWTYPE;
-- 使用例
DECLARE
    sample_rec sample%ROWTYPE;
BEGIN
    SELECT * INTO sample_rec FROM sample WHERE num1 = '1';
    -- カラムの値を取り出したいとき、<変数名>.<カラム名>
    -- sample_recカーソルに格納したsampleテーブルのnum1カラムのデータをメッセージに送る
    DBMS_OUTPUT.PUT_LINE(sample_rec.num1); -- 1
END;
```

##### PACKAGEとPACKAGE BODY
複数のPROCEDUREを一連の処理としてまとめてくれる。
2つはセットとして作成するため、名前は同一のものにする。
作成したPACKAGE/PACKAGE BODYを実行すると、書いたPROCEDUREを実行していく。
またPACKAGE内でPROCEDUREを作成する際、文頭のCREATEは書かなくていい。
```sql:PACKAGE
CREATE PACKAGE <パッケージ名> IS
    -- PACKAGE BODYで使用するPROCEDURE
    PROCEDURE <プロシージャ名1> (引数);
    PROCEDURE <プロシージャ名2> (引数);
END <パッケージ名>;
```
```sql:PACKAGE BODY
CREATE PACKAGE BODY <パッケージ名> IS
    -- PROCEDURE1作成
    PROCEDURE <プロシージャ名1> (引数) IS
        -- 変数定義
    BEGIN
        -- 処理内容
    EXECEPTION
        -- 例外処理
    END;
    
    -- PROCEDURE2作成
    PROCEDURE <プロシージャ名2> (引数) IS
        -- 変数定義
    BEGIN
        -- 処理内容
    EXECEPTION
        -- 例外処理
    END;
END <パッケージ名>;
```

##### CURSOR
データの検索結果を1件ずつ処理するためのやつ。
SELECTで拾ってきたデータを編集して別テーブルに格納するときに使用してた。
宣言部で使用する。
```sql
DECLARE
    -- カーソル作成
    CURSOR <カーソル名> IS SELECT * FROM <テーブル名>;

    -- ROWTYPEでカーソルを実行したSELECT結果を格納する変数を定義
    rec_cur <テーブル名>.%ROWTYPE;

BEGIN
-- 作ったカーソルを実行
    OPEN <カーソル名>;
        LOOP
            FETCH <カーソル名> INTO rec_cur;
            -- 取得結果を格納し終えたら処理終わり
            EXIT WHEN <カーソル名>%NOTFOUND;
        END LOOP;
    CLOSE <カーソル名>;
END;

```

##### IF
用途はJavaと同じで分岐処理で使用する。
実行部に書く。
END IF;忘れがち。
```sql
IF <条件式> THEN <処理内容>;
    ELSIF <条件式> THEN <処理内容>;
    ELSE <処理内容>;
END IF;
```

##### CASE
IFと同じで分岐処理。
場合によってIFよりも簡単に書ける。
実行部に書く。
END CASE;忘れがち。
```sql:構文
CASE <比較対象 変数とか>
    -- 比較対象と条件値が等しければ処理内容へ
    WHEN <条件値> THEN <処理内容>;
    WHEN <条件値> THEN <処理内容>;
    ELSE <処理内容>;
END CASE;
```

##### FOR/LOOP
繰り返し処理、下記以外にもいろんなパターンがある。
実行部に書く。
END LOOP;忘れがち。
```sql:構文
-- 数値1～数値2まで繰り返し
FOR <変数> IN <数値1..数値2> LOOP
    <処理内容>
END LOOP;

-- 作成したカーソルで取得した件数文繰り返し
FOR <変数> IN <作成したカーソル名> LOOP
    <処理内容>
END LOOP;
```

## Oracleの関数
Oracleにも初めて触れたのでおまけ程度に書いておきます。

**文字列を結合する**
||で繋ぎたい文字列を並べる 
```sql
SELECT 'abc' || 'def' FROM DUAL; --abcdef
```
**文字を切り出す**
```sql
SELECT SUBSTR('abcdef',2,3) FROM DUAL; --bcd
```
**日付/数値を変換する**
```sql
SELECT TO_CHAR('2022/11/11','YYYYMM') FROM DUAL; --202211
```
**NULLだった場合別の値に置き換える**
```sql
-- sample1がNULLだった場合、sample2を使用する NULLでない場合はsample1を使用
sample1 VARCHAR2 := NULL;
sample2 VARCHAR2 := 'dammy';
SELECT NVL(sample1, sample2) FROM DUAL; --dammy
```
**NULL,NULL以外で置き換える値を変える**
```sql
-- sample1がNULLだった場合、sample2を使用する NULLでない場合はsample3を使用
sample1 VARCHAR2 := NULL;
sample2 VARCHAR2 := 'dammy1';
sample3 VARCHAR2 := 'dammy2';
SELECT NVL2(sample1, sample2, sample3) FROM DUAL; --dammy2
```
**四捨五入**
```sql
-- ROUND(数値,1)小数点第二位で四捨五入、1を変えることで四捨五入の位置を変える
SELECT ROUND(1234.567,1) FROM DUAL; --1234.6
```
**切り捨て**
```sql
SELECT TRUNC(1234.567,1) FROM DUAL; --1234.5
```

## 参考文献
sql-oracle.com
segakuin.com
saka-en.com
products.sint.co.jp
atmarkit.itmedia.co.jp