LoginSignup
1
2
@yoshida_t(yoshida)

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

Posted at

【背景】

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

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

目次

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

９.例外処理

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

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

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

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

・事前定義例外

PL/SQLで使用できる定義（宣言）する必要がない例外処理
※元々定義されている

例外処理 エラー番号　 エラー内容
NO_DATA_FOUND ORA-01403 SELECT INTO文で1行も戻されない
TOO_MANY_ROWS ORA-01422 SELECT INTO文で複数行が戻された

他にも色々・・・
　

事前定義例外
DECLARE
--宣言部分（任意）
BIGIN
--実行部分
EXCEPTION
WHEN NO DATA FOUND THEN
DBMS_OUTPUT.PUT_LINE('NO DATA FOUND');
END;

・ユーザー独自の定義例外

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

ユーザー独自の定義例外
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 文字列の文字数を戻す
文字ファンクション例　UPPER　LOWER
SELECT UPPER(name), LOWER(name)  FROM test;

UPPER(name) LOWER(name)
----------- -----------
TAROU       tarou
HANAKO      hanako
--UPPERは大文字に、LOWERは小文字に文字列を変換している。
文字ファンクション例　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文字目から最後の文字までを抜き出している。
文字ファンクション例　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は右から指定した文字列を詰める。
文字ファンクション例　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 数値を切り捨てる
数値ファンクション例　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 日時を切り捨てる
日付ファンクション例　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ファンクションの第１引数に１ヶ月を足して出力している。
日付ファンクション例　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 データを日時型に変換する
変換ファンクション例
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値を戻す
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
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 行数をカウントする
集計ファンクション例
SELECT SUM(nenrei) FROM test;

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

１１.制御構造

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

処理 和名　　　 処理内容
IF ELSE 条件毎による処理 条件毎による処理を実行可能
WHEN CASE 特定の値ごとの処理 指定した変数が指定の条件（値）の場合の処理を実行可能
FOR LOOP ループ処理 特定の回数から特定の回数までのループ処理を行う
WHILE ループ処理 指定の回数分または、値のある分だけループ処理を行う
制御構造：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;
制御構造：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;
制御構造：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への現場参画の機会がある場合は改めてこの資料をぜひ読んで頂いて参画への前向きな検討をしてみてください！

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

1
2
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
2