PL/SQL

• PL/SQL
  • 実行
  • PL/SQLファイルをsqlplusの引数として渡す
  • %TYPE
  • %ROWTYPE
  • 定数
  • 標準出力
  • EXIT WHEN
  • LOOP
    • FOR LOOP
  • 分岐
    • IF
    • CASE
  • SELECT INTO
  • カーソル
  • カーソル FOR ループ
  • パラメータ付きカーソル
  • FOR UPDATE
  • WHERE CURRENT OF
    • 修正前
    • 修正後
  • カーソルの種類
    • 明示カーソル
    • 暗黙カーソル
  • Exception
    • RAISE
    • RAISE_APPLICATION_ERROR
    • others
    • PRAGMA
  • ストアド・サブプログラム
    • 構文
      • Function
    • 実行
    • Execute権限付与
    • 実装の確認
  • エラーの表示
    • SHOW ERRORS
    • USER ERRORS ビュー
  • INSERT SELECT
  • 依存/参照オブジェクト
    • 直接
    • 再コンパイル

実行

-- ファイル名:hello_world.sql
-- 宣言部
DECLARE
  message VARCHAR2(50);
-- 処理部
BEGIN
  message := 'Hello, world!';
  DBMS_OUTPUT.PUT_LINE(message);
END;
/

sqlplusで下記実行。

SET SERVEROUTPUT ON
@hello_world.sql

PL/SQLファイルをsqlplusの引数として渡す

形としては、普通にsqlplusでSQLを実行するのと同じ。

• DBMS_OUTPUT.PUT_LINE関数で標準出力するのであれば、ファイルの頭の方にSET SERVEROUTPUT ONを記載しておく。
• sqlファイルの末尾にexitが必要。ない場合、sqlplusから抜けられない。

sqlplus ${認証情報} @${file_path}

%TYPE

既存のテーブルの列の型と同じ定義を使う。

hoge table1.row1%TYPE

%ROWTYPE

既存のテーブルの定義を型として使う。

hoge table1%ROWTYPE

定数

HOGE CONSTANT VARCHAR2 NOT NULL DEFAULT "HOGE"
PIYO CONSTANT VARCHAR2 NOT NULL := "PIYO"

標準出力

SET SERVEROUTPUT ON

DBMS_OUTPUT.PUTLINE("文字列")

EXIT WHEN

条件を満たしたらループを抜ける。

SET SERVEROUTPUT ON

DECLARE
  c_count NUMBER := 0;
BEGIN
  LOOP
    c_count := c_count + 1;
    DBMS_OUTPUT.PUT_LINE('c_count = ' || c_count);
    EXIT WHEN c_count = 5;
  END LOOP;
END;
/
exit;

LOOP

LOOP
   EXIT WHEN 条件
END LOOP

FOR LOOP

SET SERVEROUTPUT ON
DECLARE
BEGIN
    FOR c_count IN 1..5 LOOP
        DBMS_OUTPUT.PUT_LINE('c_count = ' || c_count);
    END LOOP;
END;
/
exit;

c_count = 1
c_count = 2
c_count = 3
c_count = 4
c_count = 5

分岐

IF

IF THEN

ELSIF

ELSE

END IF

CASE

CASE value
    WHEN 10 THEN

    ELSE

END CASE

SELECT INTO

SELECTの結果は必ず1件でなければならない。

SELECT
    カラム名
INTO
    変数名
FROM (通常のSQL)

カーソル

DECLARE
    CURSOR a IS (SELECT文)
    b a%ROWTYPE;
BEGIN
    OPEN a;
        LOOP
            FETCH a INTO b
            EXIT WHEN a%NOTFOUND;
                bを使った処理
        END LOOP
    CLOSE a;
END;
/

カーソル FOR ループ

DECLARE
    CURSOR emp_cursor IS
        SELECT employee_id, first_name, last_name
        FROM employees
        WHERE department_id = 10;
BEGIN
    FOR emp_record IN emp_cursor LOOP
        DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_record.employee_id || ', Name: ' || emp_record.first_name || ' ' || emp_record.last_name);
    END LOOP;
END;
/

パラメータ付きカーソル

DECLARE
  CURSOR emp_cur(d_no NUMBER) IS SELECT empno,ename FROM emp
                                         WHERE deptno = d_no;
  d_var  NUMBER;
BEGIN
  -- 標準入力から値を取る。
  d_var := &DEPTNO;
  
  -- 引数を取れるカーソル。
  FOR emp_rec IN emp_cur(d_var) LOOP
    DBMS_OUTPUT.PUT_LINE(emp_rec.empno||' '||emp_rec.ename);
  END LOOP;
END;
/

FOR UPDATE

DECLARE
  CURSOR emp_cur IS
    SELECT (略) FOR UPDATE;
  -- 該当行をロックする。通常、Selectはロックされないが、Selectでもロックする。

FOR UPDATE
  OF <列名>
  [NOWAIT | WAIT n]
  -- NOWAIT : ロックされていたら即エラー
  -- WAIT n : ロックされていたらn秒待つ

WHERE CURRENT OF

修正前

DECLARE
    CURSOR emp_cur IS SELECT sal, empno FROM emp WHERE deptno = 10;
BEGIN
    FOR emp_rec IN emp_cur LOOP
        IF emp_rec.sal < 2500 THEN
            UPDATE emp SET sal = sal + 100 WHERE empno = emp_rec.empno;
        END IF;
    END LOOP;
END
/

修正後

DECLARE
    CURSOR emp_cur IS SELECT sal, empno FROM emp WHERE deptno = 10 FOR UPDATE;
BEGIN
    FOR emp_rec IN emp_cur LOOP
        IF emp_rec.sal < 2500 THEN
            UPDATE emp SET sal = sal + 100 WHERE CURRENT OF emp_cur;
        END IF;
    END LOOP;
END
/

カーソルの種類

• 明示カーソル
  • 明示的にオープン/クローズする
• 暗黙カーソル
  • SQL内部で使用される。

明示カーソル

DECLARE
    CURSOR emp_cur -- 省略

• emp_cur%NOTFOUND
  • 直前で取れなかったら、TRUE。
• emp_cur%FOUND
  • NOTFOUNDの逆
• emp_cur%ROWCOUNT
  • これまでに取り出された件数
• emp_cur%ISOPEN
  • OPENだったらTRUE。

暗黙カーソル

BEGIN
    DELETE ...
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT)
END;
/

• emp_cur%NOTFOUND
  • 処理される行がない時、TRUE。
• emp_cur%FOUND
  • NOTFOUNDの逆
• emp_cur%ROWCOUNT
  • これまでに取り出された件数
• emp_cur%ISOPEN
  • OPENだったらTRUE。

Exception

othersが例外名。
例外名一覧

Exceptionのあり/なしで挙動が異なる。

• Exceptionあり
  • トランザクション継続
• Exceptionなし
  • ロールバックされる

実装例

RAISE

実装例

RAISE_APPLICATION_ERROR

通常のExceptionはメッセージが固定だが、任意のORA-エラーコードを発生できる。 エラーコードは-20000〜-20999の間。
メッセージは2048バイト以内。

実装例

others

othersは複数の例外を受けられるが、そのままだと何が発生したかわからない。
コードとメッセージを取得する方法がある。

• sqlcode
  • エラー番号
• sqlerrm
  • エラーメッセージ

実装例

PRAGMA

無名の内部例外。
Oracleで定義されていない例外に名前をつけて、個別処理できるようにする。

実装例ではLOOP中にcommitされることで、cursor_err -> ORA-1002が発生する。

実装例

ストアド・サブプログラム

無名ブロックとは異なり、PL/SQLサブプログラム(プロシージャおよびファンクション)は、別々にコンパイルしてOracleデータベースに格納し、起動できます。

Oracle® Database Oracleプリコンパイラのためのプログラマーズ・ガイド 12c リリース1 (12.1) - ストアド・サブプログラム

構文

CREATE {FUNCTION | PROCEDURE | PACKAGE} out_put
IS
BEGIN
    (出力)
END;
/

Function

SQLの中から呼ぶことができるが、下記の制限がある。

• Selectのみ
• 引数から値を返すような実装はNG。
• SQLがサポートしていない型は使えない

実行

BEGIN
    out_put
END;
/

Execute権限付与

GRANT EXECUTE ON out_put TO user1;

実装の確認

SELECT text FROM user_source WHERE user = 'OUT_PUT';

• where
  • name : オブジェクト名
  • type : オブジェクト型
  • line : 行番号
  • text : ソースコード

データディクショナリに登録される。

エラーの表示

SHOW ERRORS

SHOW ERRORS procedure out_put

USER ERRORS ビュー

select line, text 
from user_errors 
where name = 'OUT_PUT';

INSERT SELECT

VALUESは不要。

INSERT INTO テーブル名 (
    カラム1, カラム2
) 
SELECT カラム1, カラム2 FROM テーブル名 WHERE 条件

依存/参照オブジェクト

プロシージャ/ファンクション内で参照するテーブル等を指す。
直接、間接がある。呼ぶ側が依存オブジェクト。

USER_DEPENDENCIESビューで確認できる。

直接

select name, type 
from user_dependencies 
where referenced_name = '名前';

再コンパイル

参照オブジェクトが変更された時のステータスを参照できる。

select 
  object_name,
  object_type,
  status 
from
  user_objects 
where
   object_name in ('名前');