はじめに
初投稿・初心者エンジニアということで優しい目で見てください。
（間違っていたら直したいので教えてください）
ド基本しか扱っていません。
目次
- ●PL/SQLって何なん？
- ・どんな言語なん
- ・処理全体の構成
- ●よく出てきた
- ・PROCEDURE
- ・%TYPEと%ROWTYPE
- ・PACKAGEとPACKAGE BODY
- ・CURSOR
- ・IF
- ・CASE
- ・FOR/LOOP
- ●おまけ：Oracleの関数
PL/SQLって何なん？
どんな言語なん
SQLでは利用できない、IF(分岐)、FOR(繰り返し)などを記述でき、
効率的に処理を行うようにできるOracleの拡張言語です。
処理全体の構成
宣言部・実行部・例外処理部の大きく三つのエリアに分かれていて、
それぞれのエリアで下記の役割をもっている。
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;
messageは「こんにちは」
よく出てきた
PROCEDURE
処理全体の構成で出てきたDECLAREと違い、一連の処理をDBに保存できる。
DECLAREだと都度処理全体を書かなきゃいけない。
Javaでいうメソッドみたいなもんだと今でも思ってる。引数も渡せる。
CREATE PROCEDURE <プロシージャ名> (
-- 引数
<引数名> IN<受け取る値> <型>
<引数名> OUT<処理結果を格納> <型>
<引数名> INOUT<受け取った値に処理を加えて格納> <型>
) IS
-- 宣言部
BEGIN
-- 処理
EXCEPTION
-- 例外処理
END;
SQL> CALL <プロシージャ名>(引数);
上記で呼び出すと実行部に書いた処理が実行される
似たような用途でFUNCTIONというのがあるが、使用しなかったので割愛。
%TYPEと%ROWTYPE
sampleというテーブルのカラムnum1、型NUMBER(4)を格納するのに、
変数定義で
num1 NUMBER(4);
で変数を用意できる、テーブルのカラムそのものの定義が変わったときに、定義もそれに合わせて修正が必要になる。
%TYPEを使えばそんな修正が必要なくなる。
<変数名> <テーブル名>.<カラム名>%TYPE;
-- 使用例
user_no sample.num1%TYPE;
定義する型をテーブルのカラムから直接参照することができるので、カラムの定義が変わっても修正が必要なくなる。
%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は書かなくていい。
CREATE PACKAGE <パッケージ名> IS
-- PACKAGE BODYで使用するPROCEDURE
PROCEDURE <プロシージャ名1> (引数);
PROCEDURE <プロシージャ名2> (引数);
END <パッケージ名>;
CREATE PACKAGE BODY <パッケージ名> IS
-- PROCEDURE1作成
PROCEDURE <プロシージャ名1> (引数) IS
-- 変数定義
BEGIN
-- 処理内容
EXECEPTION
-- 例外処理
END;
-- PROCEDURE2作成
PROCEDURE <プロシージャ名2> (引数) IS
-- 変数定義
BEGIN
-- 処理内容
EXECEPTION
-- 例外処理
END;
END <パッケージ名>;
CURSOR
データの検索結果を1件ずつ処理するためのやつ。
SELECTで拾ってきたデータを編集して別テーブルに格納するときに使用してた。
宣言部で使用する。
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;忘れがち。
IF <条件式> THEN <処理内容>;
ELSIF <条件式> THEN <処理内容>;
ELSE <処理内容>;
END IF;
CASE
IFと同じで分岐処理。
場合によってIFよりも簡単に書ける。
実行部に書く。
END CASE;忘れがち。
CASE <比較対象 変数とか>
-- 比較対象と条件値が等しければ処理内容へ
WHEN <条件値> THEN <処理内容>;
WHEN <条件値> THEN <処理内容>;
ELSE <処理内容>;
END CASE;
FOR/LOOP
繰り返し処理、下記以外にもいろんなパターンがある。
実行部に書く。
END LOOP;忘れがち。
-- 数値1～数値2まで繰り返し
FOR <変数> IN <数値1..数値2> LOOP
<処理内容>
END LOOP;
-- 作成したカーソルで取得した件数文繰り返し
FOR <変数> IN <作成したカーソル名> LOOP
<処理内容>
END LOOP;
Oracleの関数
Oracleにも初めて触れたのでおまけ程度に書いておきます。
文字列を結合する
||で繋ぎたい文字列を並べる
SELECT 'abc' || 'def' FROM DUAL; --abcdef
文字を切り出す
SELECT SUBSTR('abcdef',2,3) FROM DUAL; --bcd
日付/数値を変換する
SELECT TO_CHAR('2022/11/11','YYYYMM') FROM DUAL; --202211
NULLだった場合別の値に置き換える
-- sample1がNULLだった場合、sample2を使用する NULLでない場合はsample1を使用
sample1 VARCHAR2 := NULL;
sample2 VARCHAR2 := 'dammy';
SELECT NVL(sample1, sample2) FROM DUAL; --dammy
NULL,NULL以外で置き換える値を変える
-- sample1がNULLだった場合、sample2を使用する NULLでない場合はsample3を使用
sample1 VARCHAR2 := NULL;
sample2 VARCHAR2 := 'dammy1';
sample3 VARCHAR2 := 'dammy2';
SELECT NVL2(sample1, sample2, sample3) FROM DUAL; --dammy2
四捨五入
-- ROUND(数値,1)小数点第二位で四捨五入、1を変えることで四捨五入の位置を変える
SELECT ROUND(1234.567,1) FROM DUAL; --1234.6
切り捨て
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