SQL DML:制御文

手続き型言語

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

PL/SQLブロック
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;

ストアド

ストアドプログラム内でPL/SQLを使用できる。
SQL DDL:ストアド定義」参照

例外処理
メッセージ表示設定
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(“DBエラー”);
  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;

SELECT文から取得
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;

制御文

IF文
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;

1つのブロックを BEGIN ~ END で囲む

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

CASE文・CASE式
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

WHERE句でのCASE文

列 = CASE 条件 THEN 0
↓ こういう書き方はできない
CASE
  WHEN 条件 THEN 列 = ~
  ELSE 列 = ~
END

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

For文
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;

Loop文
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

動的SQL

SQL文を文字列で生成・実行
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;

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 INTOUSING '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

ForLoop
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

カーソルをOPEN時に定義
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