サイトアイコン PL／SQL日記

PL/SQLの書き方

oreno-admin


ここではORACLE社が提供するPL/SQL言語の書き方や基本文法を分かりやすく解説しています。

基本構文と処理の流れ

PL/SQLは宣言部、処理部、例外処理部の3つから成り、この順番で上から順にぷ処理が実行されていきます。

簡単なサンプルプログラムを↓を参考に解説していきます。

DECLARE
    -- 宣言部
    msg VARCHAR2(30);
BEGIN
    -- 処理部
    msg := 'Hello World';
    DBMS_OUTPUT.PUT_LINE(msg);
EXCEPTION
  -- 例外処理部
  WHEN OTHERS THEN
     DBMS_OUTPUT.PUT_LINE('ERROR');
END;
/

DECLARE：宣言部

DECLARE文から始まる宣言部では、プログラム内で使用する変数や定数、カーソル、ユーザー定義例外の宣言を行ないます。

上記のサンプルプログラムでは、「msg VARCHAR2(30);」として、msgという名前の変数をVARCHAR2型の30桁でデータタイプで定義しています。

変数の宣言などが不要なプログラムでは、DECLARE文は省略可能です。↓のプログラムでは変数の宣言が不要なため、DECLAREを省略しています。

BEGIN
    -- 処理部
    DBMS_OUTPUT.PUT_LINE('Hello World');
EXCEPTION
  -- 例外処理部
  WHEN OTHERS THEN
     DBMS_OUTPUT.PUT_LINE('ERROR');
END;
/

BEGIN~END：実行部（ブロック）

BEGIN文からEND文までのプログラムを実行部（ブロック）と呼びます。

実行部にはプログラムで実行したい処理（ビジネスロジック）を記述します。
実行したい処理の文末ごとに;（セミコロン）を記述します。

実行部（ブロック）の終了には、処理終了を表すEND;を記述します。

上記のサンプルプログラムでは「msg := ‘Hello World’;」でmsg変数に’Hello World’という文字列を格納しています。その後、「DBMS_OUTPUT.PUT_LINE(msg);」でmsg変数に格納された値をメッセージとして出力しています。

実行部はDECLAREと違い、省略することはできません。

※BEGIN～ENDのネスト

これまで開発現場ではネストしたことはありませんでしたが、複数のBEGIN～ENDをネストすることもできました。

ただ、今回はトランザクションの単位や変数のスコープについて確認していません。ネストする場合は十分確認してください。

以下、今回確認したサンプルコードです。

BEGIN
    DBMS_OUTPUT.PUT_LINE(1);
    BEGIN
        DBMS_OUTPUT.PUT_LINE(2);
    END;
END;
/
SQL> @sample4.sql
1
2

PL/SQLプロシージャが正常に完了しました。

EXCEPTION（例外処理部）

EXCEPTION文から始まる部分を例外処理部または例外処理と呼びます。

例外処理では、PL/SQLのプログラムの実行時にエラーが発生した場合の処理を記述します。例外処理をすることで、エラー時に任意の処理を実行させることや、処理を継続させることが出きます。

サンプルプログラムでは「DBMS_OUTPUT.PUT_LINE(‘ERROR’);」として、エラーが発生した場合にはメッセージに’ERROR’という文字列を出力しています。

実際の開発ではエラー番号やエラーの内容を出力させます。

例外処理は省略することが可能です。

基本的な処理の書き方や用語

変数・定数の宣言（定義）

変数、定数を宣言（定義）する基本構文は次の通りです。
変数は定義だけ行う場合と、定義と同時に初期値を代入することもできます。

定数はデータ型の前にCONSTANT句を付けることで定義することが出来ます。
定数として定義した値は変更することが出来ません。

-- 変数の定義
変数名　データ型　[NOT NULL];

-- 初期値の代入
変数名　データ型　:= 初期値;

-- 定数の定義
定数名　CONSTANT データ型 := 初期値;

簡単なサンプルソースは以下になります。

DECLARE
    -- 変数の定義
    msg1 VARCHAR2(30);

    -- 初期値の代入
    msg2 VARCHAR2(30) := 'Hello World2';

    -- 宣言部
    msg3 CONSTANT VARCHAR2(30) := 'Hello World3';
BEGIN
    -- 処理部
    msg1 := 'Hello World1';
    DBMS_OUTPUT.PUT_LINE(msg1);
    DBMS_OUTPUT.PUT_LINE(msg2);
    DBMS_OUTPUT.PUT_LINE(msg3);
END;
/

実行すると次のように結果が返ってきます。

SQL> set serveroutput on
SQL> @sample2.sql
Hello World
Hello World2
Hello World3

PL/SQLプロシージャが正常に完了しました。

値の代入

宣言した変数には値を代入することが出来ます。代入演算子「:=」を使い、値を代入します。

次のコードでは、msg1という変数に’Hello World1’という文字列を代入しています。

msg1 := 'Hello World1';

SQLの実行

PL/SQLの実行部ではSQLを実行することが出来ます。

次のプログラムでは、SQLをそのまま記述しテーブルにレコードをINSERTしています。

BEGIN
    INSERT INTO tab1 VALUES('00001','Suzuki','D0001',28);
    COMMIT;
END;
/

また、SELECTした値を変数に格納することも簡単にできます。次のプログラムではレコード件数を取得し変数に格納し、メッセージとして出力しています。

DECLARE
    cnt NUMBER(10) := 0;
BEGIN
    SELECT COUNT(*) INTO cnt FROM tab1;
    DBMS_OUTPUT.PUT_LINE(cnt);
END;
/

実行結果は次のようになります。

SQL> set serveroutput on
SQL> @count1.sql
1

PL/SQLプロシージャが正常に完了しました。

コメント

コメントは単一行と複数行と挿入することが出来ます。単一行は「–」で、複数行は「/*～*/」で記述します。

-- 単一行のコメント

/*～*/ 複数行のコメント

実際に記述すると次のようになります。

DECLARE
    -- 単一行のコメント
    msg VARCHAR2(30);
BEGIN
    /* ここから
       処理部 */
    msg := 'Hello World';
    DBMS_OUTPUT.PUT_LINE(msg);
END;
/

/（スラッシュ）

スラッシュは、最後に実行されたSQLまたはSQLバッファに格納されているPL/SQLブロックを実行します。

似たようなコマンドに「RUN」というものがありますが、RUNコマンドを実行するとプログラムの内容（コマンド）も表示されました。

SQL> @sample1.sql
Hello World

PL/SQLプロシージャが正常に完了しました。

SQL> RUN
  1  DECLARE
  2      msg VARCHAR2(30);
  3  BEGIN
  4      msg := 'Hello World';
  5      DBMS_OUTPUT.PUT_LINE(msg);
  6  EXCEPTION
  7    WHEN OTHERS THEN
  8       DBMS_OUTPUT.PUT_LINE('ERROR');
  9* END;
Hello World

PL/SQLプロシージャが正常に完了しました。

入力（置換変数）

置換変数を使うことで簡単にユーザーからの入力を変数に格納することが出来ます。

単一&置換変数はプログラム内で出現する度に値を入力し直します。二重&置換変数はプログラム内で初めの1度だけ入力を行い、以降の処理では同じ値を使い回します。

--単一&置換変数
name := '&name';

--二重&置換変数
name := '&&name';

次のサンプルソースでは「name := ‘&name’;」として、name変数にユーザーからの値を入力させています。

DECLARE
    name VARCHAR2(30);
BEGIN
    name := '&name';
    DBMS_OUTPUT.PUT_LINE(name || 'さん、こんにちは');
EXCEPTION
  WHEN OTHERS THEN
     DBMS_OUTPUT.PUT_LINE('ERROR');
END;
/

実行すると次のような結果が返ってきます。

SQL> @sample3.sql
nameに値を入力してください: test
旧   4:     name := '&name';
新   4:     name := 'test';
testさん、こんにちは

PL/SQLプロシージャが正常に完了しました。

出力（標準出力・コンソール出力）

標準出力にメッセージを出力するときは、DBMS_OUTPUTパッケージを使います。

メッセージが出力されない場合は「SET SERVEROUTPUT ON;」というコマンドを事前に実行しましょう。

次のように任意の値や文字列を指定しメッセージを出力することが出来ます。

BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello World');
END;
/

また、変数に格納された値を出力することもできます。

DECLARE
    -- 宣言部
    msg VARCHAR2(30) := 'Hello World';
BEGIN
    DBMS_OUTPUT.PUT_LINE(msg);
END;
/

PL/SQLの実行

PL/SQLの処理はテキストファイルに保存しておき、SQLPLUSから実行することが出来ます。

プログラムを実行するときは「@ファイル名」を指定します。

SQL> set serveroutput on
SQL> @sample1.sql
Hello World

PL/SQLプロシージャが正常に完了しました。

「start ファイル名」と書くこともできます。

SQL> start sample1.sql
Hello World

PL/SQLプロシージャが正常に完了しました。

set linesize/set wrap off/set serverout on

PL/SQLのプログラムをSQLPLUSで実行するときはset linesizeやset wrap off、set serverout onを指定することがあります。

・set linesize
　1行の長さを指定します。デフォルトは80文字です。

・set wrap off
　折り返しをOFFにします。

・set serverout on
　PL/SQL の標準出力をONにします。デフォルトはOFFでDBMS_OUTPUTの出力結果が確認でいないのでONにします。また任意のバッファサイズを指定することが出来ます。

ファイルの拡張子について

テキストファイルの拡張子は「txt」のままでもファイル実行できました。他に問題ないのか分かりませんが。

テキストエディタは拡張子をsqlにしておくと、SQL用の表示をしてくれて非常に見やすいので、ミスが減ります。また、開発現場での作成ルールなどもあると思うので十分に確認してください。

SQL> @sample1.txt
Hello World

PL/SQLプロシージャが正常に完了しました。
モバイルバージョンを終了