PL/SQLを2000行程書いたので、最後の力を振り絞りまとめる

はじめに

PL/SQLを2000行ほど書いたので、最後の力を振り絞ってまとめ記事を作成しました。

1. PL/SQLの良いところ

親和性

PL/SQLはORACLE DATABASEのSQLを拡張サポートする言語として開発された。下記のようにSQLとの親和性が高い。

• ほとんどのSQL関数を利用可能
• SQLの結果セットとカーソルに介入し処理が行える。
• SQLとPL/SQLのデータ型を変換する必要がない。

パフォーマンス・管理性

PL/SQLはORACLE DATABASEにコンパイルされるので呼び出し元の環境に依存せず利用できる。

• 再利用可能な関数ライブラリを提供することが出来る。
• 通信のオーバーヘッドを減らすことが出来る。

2. PL/SQLの使いどころ

ユーザ定義関数

ユーザ定義関数を定義することで事前定義関数のように使うことが出来る。
例えば、コード変換や金額計算など呼び出し元に共通の結果を返したい場合に使えるのではないかと思う。

SQL

SELECT convertF(code) from hoge;

ユーザ定義関数

FUNCTION convertF(code IN NUMBER) RETURN VARCHAR2
AS
BEGIN
/* 変換処理 */
END convertF;

プロシージャ

データパッチやデータ移行用SQLをPL/SQLで作成することで、一つのSQLファイルで幅広い対応が可能になる。

データパッチ.sql

DECLARE
/* データ型・変数定義など */
BEGIN
/* 必要な処理 */
END;

3. PL/SQLを使ってみる

学習するにあたって下記二つのサイトは体系的に学べて、詳しく書かれているのでおすすめです。
e-learning、オラクル研修、LMS（学習管理システム）のiStudy
Oracle Database PL/SQL言語リファレンス

ここからは少し使ってみて解説書きます。

準備

EMPテーブルとPROCプロシージャを作成します。

EMP表

CREATE TABLE SCOTT.EMP (	
 EMPNO NUMBER(4,0), 
 ENAME VARCHAR2(10 BYTE), 
 JOB VARCHAR2(9 BYTE), 
 MGR NUMBER(4,0), 
 HIREDATE DATE, 
 SAL NUMBER(7,2), 
 COMM NUMBER(7,2), 
 DEPTNO NUMBER(2,0), 
 CONSTRAINT PK_EMP PRIMARY KEY ("EMPNO")
);

PROCプロシージャ

create or replace PROCEDURE proc(empNo IN VARCHAR2, c OUT SYS_REFCURSOR)
AS
  /* SQL取得結果用のレコード定義 */
  TYPE EMP_ROW IS RECORD
  (
    EMPNO NUMBER(4,0),  
    ENAME VARCHAR2(10 BYTE),
    JOB VARCHAR2(9 BYTE),
    MGR NUMBER(4,0),
    HIREDATE DATE,
    SAL NUMBER(7,2),
    COMM NUMBER(7,2),
    DEPTNO NUMBER(2,0)
  );
  /* レコード型の変数を宣言 */
  e_row EMP_ROW;
BEGIN
  /* SQLを実行し、結果セットとカーソルを取得 */
  OPEN c FOR 
    'SELECT 
      * 
     FROM
      EMP e '
  || ' WHERE 
      EMPNO IN (:empno1, :empno2)' USING empNo, 7499;
  /* 取得結果をループしコンソール出力 */
  LOOP
    FETCH c INTO e_row;
    EXIT WHEN c%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(e_row.EMPNO);
  END LOOP;
END;

実行

無名プロシージャ（PROC実行用）

文字列の7369を入力値としてPROCプロシージャを呼び出します。
PROCプロシージャからの受け取りはカーソル型です。

DECLARE
  empNo VARCHAR2(10);
  c SYS_REFCURSOR;
BEGIN
  empNo := '7369';
  proc(empNo, c);
END;

結果

無名プロシージャを実行すると、empNo 7369とempNo 7499がコンソール出力されました。

解説

入力と出力

このプロシージャの入力は文字列型のemp_noでリターンがカーソル型のcです。
or replaceは同名のプロシージャがあれば書き替えます。（省略可能）

create or replace PROCEDURE proc(empNo IN VARCHAR2, c OUT SYS_REFCURSOR)

レコード型の定義

型定義や変数宣言はAS~BEGINの宣言部で行います。
今回はレコード型を定義して変数として宣言しています。
レコード型はSELECT句の結果を入れる為に使います。

宣言部

AS
  /* SQL取得結果用のレコード定義 */
  TYPE EMP_ROW IS RECORD
  (
    EMPNO NUMBER(4,0),  
    ENAME VARCHAR2(10 BYTE),
    JOB VARCHAR2(9 BYTE),
    MGR NUMBER(4,0),
    HIREDATE DATE,
    SAL NUMBER(7,2),
    COMM NUMBER(7,2),
    DEPTNO NUMBER(2,0)
  );
  /* レコード型の変数を宣言 */
  e_row EMP_ROW;
BEGIN

レコード型の簡略表記

レコード型ですが、単一表の「全てのカラム」と「一つのカラム」の定義と宣言を1行で書けます。

EMP表の全てのカラム

e_row emp%ROWTYPE;

EMP表のENAMEカラム

e_ename emp.ename%TYPE;

尚、表結合を行う場合はこの書き方は出来ないようです。

プロシージャ処理部

BEGIN~ENDが処理部になります、ロジックはここに書きます。
このプロシージャではSELECT文の結果をコンソール出力しています。

BEGIN
  /* SQLを実行し、結果セットとカーソルを取得 */
  OPEN c FOR 
    'SELECT 
      * 
     FROM
      EMP e '
  || ' WHERE 
      EMPNO IN (:empno1, :empno2)' USING empNo, 7499;
  /* 取得結果をループしコンソール出力 */
  LOOP
    FETCH c INTO e_row;
    EXIT WHEN c%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(e_row.EMPNO);
  END LOOP;
END;

バインド変数

SQL文にバインド変数を使うことが出来ます。
:xxxをSQL文字列に埋め込んでUSING xxxでバインドします。

' WHERE EMPNO IN (:empno1, :empno2)' USING empNo, 7499;

• 複数バインド可能でその場合はUSING xxx, xxxのように,区切りで記述します。
  :xxxの前から順にバインドされます。
• 変数、リテラル値どちらもバインド可能です。

動的SQL

OPEN カーソル FOR SQL文字列は動的SQLも出来ます。
その場合、今回のSQLのように文字列連結（||）をうまく使います。

||でSELECT句とWHERE句を連結している

BEGIN
  /* SQLを実行し、結果セットとカーソルを取得 */
  OPEN c FOR 
    'SELECT 
      * 
     FROM
      EMP e '
  || ' WHERE 
      EMPNO IN (:empno1, :empno2)' USING empNo, 7499;

例えば、PROCプロシージャの入力値empNoがNULLの場合、WHERE句を指定したくない時は下記のようにします。

入力値empNoがNULLだったらWHERE句なし

 tmpWhere VARCHAR2(100);
BEGIN
 IF empNo IS NULL THEN
  tmpWhere := '';
 ELSE
  tmpWhere := ' WHERE EMPNO IN (:empno1, :empno2) ';
 END IF;

 OPEN c FOR 
  'SELECT 
    * 
   FROM
    EMP e '
|| tmpWhere USING empNo, 7499;

※上記SQLはtmpWhereが''の際にUSINGでのバインドに失敗してエラーになります。
動的SQLとバインド変数を一緒に使用する際は:xxxがなくならないように注意が必要です。

引数のデフォルト指定

PROCプロシージャの入力値empNoをデフォルト値NULLにしたい場合は下記のようにします。

create or replace PROCEDURE proc(empNo IN VARCHAR2 DEFAULT NULL, c OUT SYS_REFCURSOR)

尚、位置表記法と名前表記法どちらでも呼び出せます。
（呼び出し先引数名=>呼び出し元変数名）

名前表記

DECLARE
  empNo VARCHAR2(10);
  c SYS_REFCURSOR;
BEGIN
  empNo := '7369';
  proc(c => c);
END;

関数を作る

PROCプロシージャをPROC関数に書き換える。

create or replace function proc(empNo IN VARCHAR2) RETURN SYS_REFCURSOR
AS
c SYS_REFCURSOR
...
BEGIN
...
RETURN c；
END;

変更点

• CREATE宣言をprocedureからfunctionにする。
• procedureは引数のOUTに指定した変数に代入したものを呼び出し元に返すが、functionはRETURNで返す。

最後に

PL/SQLはあくまでSQLの拡張言語なので他の言語との親和性は高くないと思います。
例えば、PHPで使うことを考え実装したときにO/RマッパーやI/Fライブラリで課題を抱えることがあったのでご注意ください。