寝て起きて寝て

プログラミングが出来ない情報系のブログ

PL/SQLのPACKAGEメモ

PACKAGEの書き方と実行の仕方をまとめる (ついでに、はてなに目次が手軽に作成できるようになってたので試しに使ってみる)

1.パッケージとは

FunctionやProcedureをひとまとめにしたもの 大規模な開発だとFunctionやProcedureが散乱するので Packageを作ってまとめておいた方がいい。

PL/SQLのパッケージを使用するためには、「仕様部」と「本体部」を作成する必要がある

・仕様部

パッケージの本体部に含まれるファンクションと プロシージャ名の名前と引数戻り値を定義したもの。 ここに書いてないと怒られる。

・本体部

パッケージに含まれるファンクションとプロシージャの処理を記述していく。 またパッケージ内でどこからでもアクセスできるグローバル変数を記述できる。 ちなみに、ここで宣言するグローバル変数はファンクションとプロシージャの処理を 記述する前に書かないと正しく処理してくれない。

2.パッケージ作成

・仕様部

[構文]

CREATE OR REPLACE PACKAGE パッケージ名 AS[IS]
  --ファンクションの場合
  FUNCTION ファンクション名(
    引数 IN[OUT,INT OUT] 型名[,…]
  )RETURN 戻り値;
  
  --プロシージャの場合
  PROCEDURE プロシージャ名(
    引数 IN[OUT,INT OUT] 型名[,…]
  );
END;
/

・本体部

[構文]

CREATE OR REPLACE PACKAGE BODY パッケージ名 AS[IS]
  --グローバル変数
  変数名 型;…
  
  --ファンクションの場合
  FUNCTION ファンクション名(
    引数 IN[OUT,INT OUT] 型名[,…]
  )RETURN 戻り値
  IS
    宣言部
  BEGIN
    処理部
    RETURN 戻り値;
  EXCEPTION
    例外部
  END ファンクション名;
  
  --プロシージャの場合
  PROCEDURE プロシージャ名(
    引数 IN[OUT,INT OUT] 型名[,…]
  )
  IS
    宣言部
  BEGIN
    処理部
  EXCEPTION
    例外部
  END プロシージャ名;
END パッケージ名;
/

ほぼ普通にファンクションや、プロシージャを作成するときと同じだけど、 ENDに対応する名前を記述する必要がある。

3.適当に作ってみる

今回はプロシージャからファンクションを呼び出し、実行するパッケージを作る。

・仕様部

CREATE OR REPLACE PACKAGE SUM_PACK AS
  PROCEDURE INPUT_DATA(
    --入力パラメータ
    i_data IN NUMBER,
    --出力パラメータ
    o_data OUT NUMBER,
    result OUT VARCHAR2
  );
  FUNCTION SUM_DATA(
    i_data IN NUMBER
  )RETURN NUMBER;
END;
/

・本体部

CREATE OR REPLACE PACKAGE BODY SUM_PACK AS
  --グローバル変数
  value NUMBER := 200;
  
  PROCEDURE INPUT_DATA(
    i_data IN NUMBER,
    o_data OUT NUMBER,
    result OUT VARCHAR2
  )
  IS
  BEGIN
    SELECT SUM_DATA(i_data) INTO o_data FROM DUAL;
    result := '引数:' || i_data || '出力値:' || o_data;
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLERRM);
  END INPUT_DATA;
  FUNCTION SUM_DATA(
    i_data IN NUMBER
  )RETURN NUMBER
    IS
      o_num NUMBER := 0;
    BEGIN
      o_num := i_data + value;
    RETURN o_num;
  END SUM_DATA;
END SUM_PACK;
/

上記プログラムは引数に200を足して OUTパラメータの「result」には引数と出力値を「o_data」には出力値を渡している。

4.実行

パッケージを呼び出すには

"パッケージ名"."ストアドファンクション名"

で呼び出すことができる

では、SQLPLUSで実行する。

SQL>SET SERVEROUTPUT ON;
SQL>VARIABLE result VARCHAR2(100);
SQL>VARIABLE o_data NUMBER;
SQL>EXECUTE SUM_PACK.INPUT_DATA(3,:o_data,:result);

OUTパラメータの桁数や精度は呼び出し側の定義を継承している為、 ここの指定をさぼると下記のエラーが出るため注意(ちゃんと指定しないではまった顔)

ORA-06502: PL/SQL: 数値または値のエラー: 文字列バッファが小さすぎます。が発生しました

出力結果:

:O_DATA :RESULT
203 引数:3出力値:203

こんな感じになったはず・・・

因みにPL/SQLの無名ブロックから呼び出す場合は下記の通り

DECLARE
  i_data NUMBER;
  o_data NUMBER;
  result VARCHAR2(100);
  
BEGIN
  SUM_PACK.INPUT_DATA(&i_data,o_data,result);
   DBMS_OUTPUT.PUT_LINE(o_data);
   DBMS_OUTPUT.PUT_LINE(result);
END;
/

とりあえずこんな感じで・・・ (目次機能スゲー)

5.参考URL

ORACLE/PACKAGE編 - オラクルちょこっとリファレンス

[Oracle] プロシージャの引数の桁数は呼び出し側で決まる|Archive Redo Blog

TOraclePackageによるプロシージャの実行 | 鉄飛テクノロジー

目次を入力補助ツールバーから挿入できるようにするなど、はてな記法を便利に使えるようにしました(2件) - はてなブログ開発ブログ