データベースの教育例３（Oracle PL/SQL編）

2020年1月19日

オラクルを使う上でまず最初にSQL*PlusやSQL Developerを使ってテーブルの内容を確認したりデータを編集したりします。
例えば、当初は簡潔だった検索用のSELECT文が、WHERE条件やGROUP化、マスターや他のトランザクションテーブルとの結合などが必要となり、長文かつ処理時間のかかるSQLに変化していきます。
SQLは、基本的には集合を取り扱うものですのでプログラマーの立場からすると、ちょっとしたことのために大掛かりな集合演算を行っているようで歯がゆい時があります。

そんな時、PL/SQLが有効です。早速見ていきましょう。

PL/SQLとはどんなものか

PL/SQLは、PL/SQLブロックやストアードプロシージャやファンクションを作成するためのプログラム言語です。
PL/SQLブロックは、複数のSQLを一括実行するための仕組みで、ストアードプロシージャやファンクションは、PL/SQLブロックをDB内にコンパイル済みの形式で保存し再利用できるようにしたデータベースオブジェクトです。
具体的に見ていきましょう。

ちょっと面倒なSQLを記述してみる

例えば、EMP表とDEPT表を例にとると、EMP表とDEPT表をDEPTNOカラムで結合し、結合が失敗した場合はDNAMEに”BOARD”り表示するSQLを考えてみましょう。

SQLで実行しようとすると、以下のようになります。（あくまで例示で様々な方法があります。）

select ed.empno,ed.ename,nvl(ed.dname,’BOARD’) from
(select e.empno,e.ename,d.dname from emp e ,dept d where e.deptno=d.deptno(+) ) ed

上記SQLは、黄色下線の副問い合わせにて外部結合を行いその結果がNULLの場合BOARDに置換しています。
やりたいことに直接的なアプローチではないような感じです。

PL/SQLブロックで記述してみる

上記と同じ処理を、PL/SQLブロックで記述してみましょう。以下のようになります。

declare
　cursor c_empdept is select e.empno,e.ename,d.dname from emp e ,dept d where e.deptno=d.deptno(+);
　r_empdept c_empdept%ROWTYPE;
　begin
　　open c_empdept;
　　loop
　　　fetch c_empdept into r_empdept;
　　　exit when c_empdept%notfound;
　　　if r_empdept.dname is null then
　　　　dbms_output.put_line(r_empdept.empno|| ‘,’||r_empdept.ename || ‘,’|| ‘BOARD’);
　　　else
　　　　dbms_output.put_line(r_empdept.empno|| ‘,’||r_empdept.ename || ‘,’|| r_empdept.dname);
　　　end if;
　　end loop;
　　close c_empdept;
　end;
/

SQLよりは長文なのですが、IF文で判定している点がわかりやすいといえます。ポイントは、emp表とdept表を外部結合したカーソル（青下線）を作成し、取得したデータのNULL判定（黄下線）を実施している部分です。

ストアードプロシージャ化して実行してみる

PL/SQLブロックで動作ができてしまえば、ストアー度プロシージャ化は簡単です。
1行目のdeclareの部分を、create procedure 名前 is に置き換えればOKです。

create procedure empdept is
　cursor c_empdept is select e.empno,e.ename,d.dname from emp e ,dept d where e.deptno=d.deptno(+);
　r_empdept c_empdept%ROWTYPE;
　begin
　　open c_empdept;
　　loop
　　　fetch c_empdept into r_empdept;
　　　exit when c_empdept%notfound;
　　　if r_empdept.dname is null then
　　　　dbms_output.put_line(r_empdept.empno|| ‘,’||r_empdept.ename || ‘,’|| ‘BOARD’);
　　　else
　　　　dbms_output.put_line(r_empdept.empno|| ‘,’||r_empdept.ename || ‘,’|| r_empdept.dname);
　　　end if;
　　end loop;
　　close c_empdept;
　end;
/

ストアードプロシージャの実行は、以下のように行います。

SQL> exec empdept;
7566,JONES,RESEARCH
7788,SCOTT,RESEARCH
7902,FORD,RESEARCH
7369,SMITH,RESEARCH
7876,ADAMS,RESEARCH
7698,BLAKE,SALES
7499,ALLEN,SALES
7521,WARD,SALES
7654,MARTIN,SALES
7844,TURNER,SALES
7900,JAMES,SALES
7839,KING,BOARD
7782,CLARK,BOARD
7934,MILLER,BOARD
PL/SQLプロシージャが正常に完了しました。

以下に簡単に補足します。

create procedure文であらかじめコンパイル済みのオブジェクトを実行するので、実行時間は短くなります。
他のデータベースオブジェクトと同じくcreate or replace でプロシージャを作成するのが一般的です。
プロシージャには引数を設定可能です。
プロシージャに戻り値を加えたい場合は、ファンクション化（関数化）できます。
複数のファンクションやプロシージャをパッケージとしてまとめて管理できます。

PL/SQLの基本的な構文

PL/SQLを概観したところで、基本的な構文を確認しましょう。
IF文などの基本的な構文は、pascal（現在も Deplphi が生き残っています）を参考にしたといわれています。

基本構造

PL/SQLの基本的な構造は、以下のようにdeclareから始まる宣言部を記述してカーソルや変数を宣言し、beginとendの間に処理内容を書いていきます。また、exception部には例外のキャッチ処理を記述します。

declare
　カーソルや変数を宣言
　begin
　　処理内容
　 exception
　　 例外処理
　end;

カーソル定義とPL/SQLレコード

先ほどのサンプルの以下の部分です。

cursor c_empdept is select e.empno,e.ename,d.dname from emp e ,dept d where e.deptno=d.deptno(+);
r_empdept c_empdept%ROWTYPE;

カーソルは、”cursor カーソル名 is ”で定義します。その後に続くのは、通常のSQL文です。
2行目は、ちょっとわかりにくいと思いますが、PL/SQLレコードと呼ばれるもので、カーソルが返却する型と同じ型を定義しています。具体的には、r_empdeptは、カーソル”c_empdep”が返却するempno、ename、dnameを持つ構造体と考えるとよいでしょう。

カーソルのオープンとFETCH

データベースへのアクセスには多くの場合カーソルを使用しますが、カーソルは、ファイルと同じようなイメージでオープン、クローズ、フェッチ操作を行います。
FETCHの結果が０件の判定は、カーソル属性”c_empdept%notfound”で判定します。

open c_empdept;
　fetch c_empdept into r_empdept;
　　exit when c_empdept%notfound;
close c_empdept;

LOOP

繰返し処理を行う場合には、LOOPを使います。他の言語でいうところのDOやWHENです。
以下のように使用します。

loop
　 exit when 条件;
end loop;

IF文

IF文も多くの説明は不要でしょう。以下のように使用します。

if 条件 then
　条件成立時の処理;
else
　条件不成立時の処理;
end if;

カーソルFOR LOOP

先ほどの例では、カーソルを定義してLOOPを使ってFETCHしてテーブルデータにアクセスしましたが、カーソルFOR LOOPを使うと、カーソルの明示的なクローズや最終レコードの判定が不要となり以下のように簡潔に記述できます。

for r_empdept in c_empdept loop
　if r_empdept.dname is null then
　　dbms_output.put_line ….
　else
　　dbms_output.put_line ….
end loop;

標準パッケージ

今回の例では、コンソール出力に”dbms_output”パッケージを使いました。
これは、オラクル社が提供する標準パッケージの一つです。
標準パッケージには、この他にもファイル出力用のUTL_FILEやHTTP出力用のUTL_HTTP、空間検索用のSDO_GEOMなどたくさんの種類があります。以下を参考にしてください。

[blogcard url=”https://docs.oracle.com/cd/E16338_01/appdev.112/b56262/intro.htm”]

その他留意点

PL/SQLでプログラム作成するうえでの一般的な留意点をいくつか書いておきます。

1.スラッシュによる一括実行指定
SQL*PlusなどからPL/SQLブロックを実行したりストアードプロシージャを登録したりする場合は、最後にスラッシュ（/）が必要です。
これは、複数行のSQLに対して、”ここで終了”を意味するものです。（正確には複数行のDDLの終了を示すSQL*Plusの仕様です。）
2.SERVEROUTPUT環境変数
SQL*Plusを起動してそのままPL/SQLブロックを起動しても、DBMS_OUTPUTパッケージからコンソール出力されません。
これは、SQL*Plusにてオラクルサーバーからのメッセージを出力する設定がされていない（デフォルト：OFF）ためです。
これを有効にするには、以下のコマンドを発行します。

SQL>set serveroutput on

3.USER_ERRORSテーブル
PL/SQLコンパイル時（SQL発行時）に発生したエラーは、user_errorsビューに出力されます。
以下のようなイメージになります。

SQL> select line,position,substr(text,1,100) from user_errors;
LINE POSITION SUBSTR(TEXT,1,100)
——- ———— ——————————————————–
2 4 PLS-00103: 記号””が見つかりました。次のうちの1つが入るとき:
begin function pragma procedure subtype type <an identifi
3 4 PLS-00103: 記号””が見つかりました。次のうちの1つが入るとき:
begin function pragma procedure subtype type <an identifi
4 4 PLS-00103: 記号””が見つかりました。次のうちの1つが入るとき:
begin function pragma procedure subtype type <an identifi

まとめ

今回は、PL/SQLの基本を確認しました。
実際の業務では、既存のストアードプロシージャを改修したり、性能の出ないSQLをプロシージャ化して性能改善したりするケースが多いと思います。
更には、SQL Developerを使うとブレークポイントを設定したデバッグが実行できるため生産性が上がります。
まずは、SQL*Plusを使って今回例示したような簡単なコードを書いて基本を確認してみるとよいと思います。

よかったらシェアしてね！

URLをコピーしました！

URLをコピーしました！