SQL DML:制御文

PL/SQL

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

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;
/

ストアド

CREATE OR REPLACE PROCEDURE MyProcedure (
 引数1 IN|OUT 型,
 引数2 IN|OUT 型
)AS

 変数 型; ←ここが宣言部(DECLAREは不要)
BEGIN
 変数 := 値;
 
 SELECT ~ WHERE 列 = 引数;
 ストアド引数=バインド変数(「:」は不要)
END;

例外処理

メッセージ表示設定
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;

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;
/

制御文

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;

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
 begin
  for i in 1..3 loop
   for j in 1..3 loop
    @test.sql d s
   loop;
  end loop;
 end;
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;

動的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
 jcrec 受注m%rowtype;
 cursor cu
 is
  select col1 from 受注m
 ;
begin
 open cu;
 loop
  fetch cu into jcrec;
  exit when cu%notfound;
  データ取得対象が無い場合は処理を抜ける
  ~;
 end loop;
 close cu;
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型

カーソルを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

カーソルを動的に変更

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;