SQL DML：制御文

ProceduralLanguageSQL
OracleDatabas用プログラム言語
非手続き型言語であるSQLを手続き型言語に拡張している。
変数、CASE、LOOP等の制御文を利用した上でSQLを利用できる。
「辞書/手続き型・非手続き型言語」参照
・ストアドプログラムの内部に記述
・SQL*Plus等のクライアントプログラムから実行するスクリプトファイルに記述
・SQL*Plus等のクライアントプログラムから直接記述
等の使い方ができる。

PL/SQLを元にした言語。名称はPL/pgSQL

DECLARE
　宣言部
　変数 型;
BEGIN
　変数 := 値;
　
　SELECT ～ WHERE 列 = 変数; ←エラー
　PL/SQL内のSQLへ変数を埋め込むには置換変数、バインド変数を利用する
　SELECT ～ WHERE 列 = :バインド変数;
　SELECT ～ WHERE 列 = &置換変数;
END;
/

VAR myVal NUMBER;
SQL*Plusの変数(バインド変数)はPL/SQLブロック外で宣言
「Oracle SQL*Plus使用方法/バインド変数」参照
DECLARE
　:myVal = 5;
　SQL*Plus変数も操作可能
BEGIN
　
END;
/

DECLARE
　宣言部
　変数 型;
BEGIN
　処理
END;

エラーが発生するとMS-DOSのerrorlevel変数に1が格納される
\set ON_ERROR_STOP
\set AUTOCOMMIT off
BEGIN;
～;
COMMIT;

メッセージ表示設定
SET SERVEROUTPUT ON

DECLARE
　独自の例外を定義
　MYERROR EXCEPTION;
BEGIN
　～
　IF ～ THEN
　　例外を発生させる
　　RAISE MYERROR;
　END IF;
　
EXCEPTION
　WHEN MYERROR THEN
　　画面にメッセージ表示
　　DBMS_OUTPUT.PUT_LINE(“独自エラー”);
　　ROLLBACK;
　　
　WHEN SQLERROR THEN
　　DBMS_OUTPUT.PUT_LINE(“ＤＢエラー”);
　　ROLLBACK;
　　
　WHEN OTHERS THEN
　　DBMS_OUTPUT.PUT_LINE(“その他のエラー”);
　　ROLLBACK;
END;
/

BEGIN TRY
　　～
END TRY

BEGIN CATCH
　　
END CATCH;

DECLARE
　PROCEDURE TEST(prm NUMBER)
　IS
　BEGIN
　　UPDATE ～ SET ～ = prm;
　END;

BEGIN
　ローカルサブプログラム実行
　TEST(5);
END;

DECLARE
　変数 型;
　VAL1 NUMBER;
　VAL2 VARCHAR(10);
　VAL3 表名.列名%TYPE;
　「SQL DML：データ型」参照
BEGIN
　:VAL1 = 5;
　:VAL2 = '5';
END;

RETURNS
　INT;
　戻り値の型
　VOID;
　戻り値無しの場合
DECLARE
　変数 型;
　VAL1 INT;
　VAL2 VARCHAR(10) := '5';
　宣言と同時に初期化も可能
BEGIN
　VAL1 := 5;
　VAL2 := '5';
END;

SET SERVEROUTPUT ON

DECLARE
　VAL1 表名.列名1%TYPE;
　VAL1 表名.列名2%TYPE;
BEGIN
　抽出結果を格納
　PL/SQLでは単純なSELECT文発行はエラー
　SELECT 列名1, 列名2 INTO VAL1,VAL2
　FROM 表名
　WHERE ROWNUM <= 1
　ORDER BY ～;
　
　DBMS_OUTPUT.PUT_LINE(VAL1);
　DBMS_OUTPUT.PUT_LINE(VAL2);
　
　ORACLEではUPDATE文にORDER BYを使用できない為、↑で主キーを取得して条件とする
　UPDATE 表名 SET 列名 = ～
　WHERE 列名1 = VAL1 AND 列名2 = VAL2;
　
END;
/

RETURNS
　INT;
DECLARE
　VAL INT;
BEGIN
　SELECT 列 INTO VAL FROM 表;
　RETURN VAL;
END;

DECLARE
　　myNum Number := 10;
　　myChar VARCHAR2(255);
BEGIN
　　IF (myNum = 0) THEN
　　　　myChar := 'ZERO';
　　ELSIF (myNum > 0) THEN
　　ELSE IFでもELSEIFでもない
　　　　myChar := 'OVER';
　　ELSE
　　　　myChar := 'UNDER';
　　END IF;
　　
　　SYS.DBMS_OUTPUT.PUT_LINE(myChar);
END;

１つのブロックを BEGIN ～ END で囲む

IF (@prmTEST = 1)
　　BEGIN
　　
　　END
ELSE IF (@prmTEST = 2)
　　BEGIN
　　
　　END
ELSE
　　BEGIN
　　　　IF (@prmTEST = 3)
　　　　　　BEGIN
　　　　　　
　　　　　　END
　　END
　　

【CASE式】
CASE 判定対象 WHEN 判定対象 THEN ～
＝しか評価できない。IS NULL や 不等号等は×。

SELECT
　NAME,
　CASE NAME
　　WHEN 'AAA' THEN ～
　　WHEN NULL THEN ～：×
　　WHEN (NULL) THEN ～：エラー
　　WHEN IS (NULL) THEN ～：エラー
　　ELSE ～
　END NullOrNotNull
FROM MYTABLE;

【CASE文】
CASE WHEN 判定対象 評価式 判定対象 THEN ～

SELECT
　CASE
　　WHEN ID IN (1,3,5) THEN 'GroupA'
　　WHEN ID > 10 THEN 'GroupB'
　　ELSE 'GroupC'
　END ID,
　CASE
　　WHEN NAME IS NULL THEN 'NULL'
　　WHEN NAME IS NOT NULL THEN 'NOT NULL'
　END NAME,
　CASE
　　WHEN MYDATE BETWEEN '2000-01-01' AND '2010-12-31' THEN ～
　　ELSE ～
　END MYDATE
FROM MYTABLE;

(参考：DECODE関数使用時)
SELECT
　NAME,
　DECODE(NAME ,
　　'AAA' , ～
　　'BBB' , ～
　　～ (デフォルト値))
　END FieldNm
FROM MYTABLE;

※デフォルト値省略時、指定条件に当てはまらない場合は(NULL)

↑をCASE文で表す場合
SELECT
　NAME,
　CASE NAME
　　WHEN 'AAA' THEN ～
　　WHEN 'BBB' THEN ～
　　ELSE ～
　END FieldNm
FROM MYTABLE;

Oracleと同じ

SELECT
　　ProjCd,
　　DiaCd,
　　CASE DiaCd
　　　　WHEN '1' THEN '平日'
　　　　WHEN '2' THEN '土日祝'
　　　　ELSE ''
　　END　　AS 'DiaNm'
FROM
　　TimeTei

SELECT
　　ProjCd,
　　DiaCd,
　　CASE
　　　　WHEN DiaCd IN ('1') THEN '平日'
　　　　WHEN DiaCd IN ('2','3') THEN '休日'
　　END　　AS 'DiaNm'
FROM
　　TimeTei

Select * From 表名
Where
(
　CASE
　　WHEN 条件 THEN colmA
　　WHEN 条件 THEN colmB
　END
) = 100
AND colC =
(
　CASE
　　WHEN 条件 THEN 100
　　WHEN 条件 THEN 200
　END
)

↓
Select * From table1
Where colmA = 100 AND colC = 100

SELECT
　　*
WHERE

　　dbo.TNM_WorkTerm.SiyoKbn = @UseKbnON
　　
　　AND dbo.TBL_Proj.ProjectCd >=
　　　　CASE
　　　　　　WHEN @ProjectCd = 0 THEN 1
　　　　　　ELSE @ProjectCd
　　　　END　'※AND dbo.TBL_Proj.ProjectCd >= 1
　　
　　AND dbo.TBL_Proj.ProjectCd <=
　　　　CASE
　　　　　　WHEN @ProjectCd = 0 THEN 999999
　　　　　　ELSE @ProjectCd
　　　　END　'※AND dbo.TBL_Proj.ProjectCd <= 999999

DECLARE
　　～
BEGIN
　for i in 1..3 loop
　　for j in 1..3 loop
　　　@test.sql d s
　　loop;
　end loop;
END;

DECLARE
　i INT;
　j INT;
BEGIN
　for i in 1..3 loop
　　for j in 1..3 loop
　　　INSERT INTO 表 VALUES(i, j);
　　loop;
　end loop;
END;

BEGIN
　loop
　　～
　　exit when 条件式
　　exit when 値 > 5;
　end loop;
END;

SET SERVEROUTPUT ON;
BEGIN
　DBMS_OUTPUT.PUT_LINE(～);
　
EXCEPTION
　DBMS_OUTPUT.PUT_LINE('デバッグメッセージ');
　DBMS_OUTPUT.PUT_LINE('SQL コード = ' || SQLCODE);
　DBMS_OUTPUT.PUT_LINE('エラー内容 = ' || SQLERRM(SQLCODE));
END;

RAISE (エラーレベル) (メッセージ文字列)
RAISE INFO 'aaa%aaa%aaa', 111, 222;
→aaa111aaa222aaa

WK_SQL VARCHAR(2000);

WK_SQL := '';　
WK_SQL := WK_SQL || 'UPDATE ～ FROM ～ WHERE ';

IF ～ THEN
　WK_SQL := WK_SQL || ' (列名1) = ''oracle'' AND ';
END IF;
IF ～ THEN
　WK_SQL := WK_SQL || ' (列名2) = ''pl/sql'' AND ';
END IF;

↑SQL文を実行
EXECUTE IMMEDIATE WK_SQL;

WK_SQL VARCHAR(2000);

INSERT/UPDATE/DELETE文
WK_SQL := '';　
WK_SQL := WK_SQL || 'UPDATE ～ FROM ～ ';
WK_SQL := WK_SQL || 'WHERE ～ = :=val1 AND ～ = val2 ';

↑SQL文に値をバインドして実行
EXECUTE IMMEDIATE WK_SQL USING '1', '1';
※バインド変数：val1、val2は宣言しなくて良い

INTO句
EXECUTE IMMEDIATE WK_SQL INTO ～ USING '1', '1';
INTO句は動的SQL文の文字列に入れられない
EXECUTE IMMEDIATE 'SELECT (列) INTO (変数) FROM (表)' は×
EXECUTE IMMEDIATE 'SELECT (列) FROM (表)' INTO (変数) は○

カーソル内のSELECT文
OPEN cu FOR 'SELECT ～ FROM ～ WHERE ～ = :=val1 AND ～ = val2 ' USING 1, '1';
カーソルOPEN時にバインド変数に値を埋め込み
LOOP
　FETCH cu INTO r;
　EXIT WHEN cu%NOTFOUND;
　～
END LOOP;
CLOSE cu;

declare
　rec 受注m%rowtype;
　cursor cu
　is
　　select col1 from　受注m
　;
begin
　open cu;
　loop
　　fetch cu into rec;
　　exit when cu%notfound;
　　データ取得対象が無い場合は処理を抜ける
　　～;
　end loop;
　close cu;
end;

DECLARE
　cur CURSOR FOR SELECT 列 FROM 表;
　または
　cur CURSOR IS SELECT 列 FROM 表;
　rec RECORD;
BEGIN
　OPEN cur;
　LOOP
　　FETCH cur INTO rec;
　　EXIT WHEN NOT FOUND;
　　または
　　IF NOT FOUND THEN
　　　～
　　　EXIT;
　　END IF;
　　INSERT INTO 表 VALUES ( rec.列 );
　END LOOP;
END

(1)カーソルの宣言
DECLARE ［カーソル名］CURSOR FOR［SELECT文］

DECLARE TestCursor CURSOR FOR
　SELECT ～ FROM TestTable

(2)カーソルを開く
OPEN ［カーソル名］

OPEN TestCursor

(3)1行ごとにデータを取り出す
FETCH NEXT FROM ［カーソル名］ INTO ［カーソル用変数］
WHILE ［条件式］
　BEGIN
　［処理内容］
　END

DECLRE @TestColumn1 INT
DECLRE @TestColumn2 INT

WHILE (@@fetch_status = 0)
カーソル全件Loop
　BEGIN
　　FETCH NEXT FROM TestCursor INTO @TestColumn1,@TestColumn2
　　カーソルから値を取り出し、@TestColumn1、2へ格納
　　
　　PRINT @TestColumn1 + ' ' + TestColumn2
　END

(4)カーソルを閉じる
CLOSE [カーソル名]
DEALLOCATE [カーソル名]

CLOSE TestCursor
DEALLOCATE TestCursor

declare
　cursor cu is select * from 受注m where 品番='a001';
begin
　for cu_rec in cu loop
　　～;
　end loop;
end;
変数(cu_rec)は宣言不要
カーソルのOPEN、データ取得、終了、CLOSE処理は記述不要
変数の型はTYPE、ROWTYPE型

DECLARE
　cur CURSOR FOR SELECT 列 FROM 表;
BEGIN
　FOR rec IN cur LOOP
　　INSERT INTO 表 VALUES ( rec.列 );
　END LOOP;
END

TYPE CURSOR_INFO IS REF CURSOR;
cu CURSOR_INFO;

BEGIN
　OPEN cu FOR 'SELECT ～ FROM ～';
　LOOP
　　FETCH cu INTO r;
　　EXIT WHEN cu%NOTFOUND;
　　～
　END LOOP;
CLOSE cu;
END

BEGIN
　FOR rec IN ( SELECT 列 FROM 表 ) LOOP
　FOR rec IN SELECT 列 FROM 表 LOOP
　　INSERT INTO 表 VALUES ( rec.列 );
　END LOOP;
END

IF ( ～ ) THEN
　OPEN cu FOR 'SELECT ～ FROM ～';
ELSIF ( ～ ) THEN
　OPEN cu FOR 'SELECT ～ FROM ～ WHERE ～ = :=val1 ' USING 1;
ELSE ( ～ ) THEN
　OPEN cu FOR 'SELECT ～ FROM ～ WHERE ～ = :=val1 AND ～ = val2 ' USING 1, '1';
END IF;

LOOP
　FETCH cu INTO r;
　EXIT WHEN cu%NOTFOUND;
　～
END LOOP;
CLOSE cu;

DECLARE
　引数を持つ
　cur CURSOR(prm1 INT, prm2 TEXT) FOR SELECT 列 FROM 表 WHERE 列1 = prm1 AND 列2 = prm2;
BEGIN
　FOR rec IN cur(5, 'aaa') LOOP
　　INSERT INTO 表 VALUES ( rec.列 );
　END LOOP;
END