13.3. ストアドプロシージャの利用

単純なストアドプロシージャとして、SQL 文と同様のことをストアドプロシージャを用いた場合の例を示します。sample.24-1 は 6 章でも使用した 受注表 と 顧客表 を結合し、200個以上注文したことのある顧客名を出力するものです。PostgreSQL の関数では、出力する値が複数行の可能性があるため、SETOF 句を記述して複数行の出力を許可しています。PostgreSQL では返り値のデータ型を指定しなければなりませんので、RETURNS SETOF CHAR(20) としています。 これは SQL で記述された関数であるため、LANGUAGE = 'sql'としています。

 /* ストアドプロシージャの定義 */

  CREATE PROCEDURE pro_200
  IS
  BEGIN
      SELECT DISTINCT KK.顧客名 FROM 受注表 JJ,顧客表 KK
          WHERE JJ.顧客コード = KK.顧客コード
                AND JJ.受注個数 >= 200 ;
  END ;

 /* ストアドプロシージャの実行 */

  BEGIN
        pro_200
  END ;

 顧客名
 ────
 KUROKIYA
 ONSIDE

 /* 関数の定義 */

  CREATE FUNCTION func_200()
  RETURNS SETOF CHAR(20)
  AS
  '
  BEGIN
      SELECT DISTINCT KK.顧客名 FROM 受注表 JJ,顧客表 KK
          WHERE JJ.顧客コード = KK.顧客コード
                AND JJ.受注個数 >= 200 ;
  END ;
  '
  LANGUAGE 'sql' ;

 /* 関数の利用 */

  SELECT func_200() ;

 顧客名
 ────
 KUROKIYA
 ONSIDE

引数をとるストアドプロシージャの例を紹介します。sample.24-2 は、sample.24-1 を少し改良し、受注個数を引数として与えることができるようにします。つまり、指定した個数以上の注文をしたことがある顧客名を出力するストアドプロシージャです。ORACLE も PostgreSQL も引数のデータ型を指定する必要があります。引数のデータ型を、ORACLE では NUMBER 型、PostgreSQL では INTEGER 型としています。引数は 1つ目は $1、2つ目は $2 という風にして扱います。従って、ORACLE の KOSUU にあたる引数は $1 ということになります。→ 受注表 、顧客表

 /* ストアドプロシージャの定義 */

  CREATE PROCEDURE pro_JUCHU(KOSUU NUMBER)
  IS
  BEGIN
        SELECT DISTINCT KK.顧客名 FROM 受注表 JJ,顧客表 KK
               WHERE JJ.顧客コード = KK.顧客コード
                    AND JJ.受注個数 >= KOSUU ;
  END ;

 /* ストアドプロシージャの実行 */

  BEGIN
        pro_JUCHU(150)
  END ;

 顧客名
 ────
 KUROKIYA
 ONSIDE
 KUSHINANA

 /* 関数の定義 */

  CREATE FUNCTION func_JUCHU(INTEGER)
  RETURNS SETOF CHAR(20)
  AS
  '
  BEGIN
    SELECT DISTINCT KK.顧客名 FROM 受注表 JJ,顧客表 KK
      WHERE JJ.顧客コード = KK.顧客コード
            AND JJ.受注個数 >= $1 ;
  END ;
  '
  LANGUAGE 'sql' ;

 /* 関数の利用 */

  SELECT func_JUCHU(150) ;

 顧客名
 ────
 KUROKIYA
 ONSIDE
 KUSHINANA
  

ストアドプロシージャには IF 文などの制御文を使うことができます。ORACLE のストアドプロシージャを利用した例ですが、sample.24-3 は、受注個数を引数とするもので、注文数が入力された値より大きければ、大きなものだけを表示し、入力された値より大きなものがない場合は 'NO DATA' を表示するものです。NUMBER 型の SHOHIN_COUNT という変数に、受注個数が KOSUU よりも大きなもののデータ数を代入しています。SELECT COUNT(*) INTO SHOHIN_COUNT FROM ~ ; という SQL で SELECT 文で得た COUNT(*) の値を代入しています。IF 文により、COUNT = 0 の場合は 'NO DATA' を表示します。→ 受注表

 /* ストアドプロシージャの定義 */

  CREATE PROCEDURE pro_BIG(KOSUU NUMBER)
  IS
      SHOHIN_COUNT NUMBER ;
  BEGIN
      SELECT COUNT(*) INTO SHOHIN_COUNT FROM 受注表
          WHERE 受注個数 > KOSUU ;
      IF SHOHIN_COUNT = 0 THEN
          SELECT 'NO DATA' ;
      ELSE
          SELECT 受注番号,受注個数
              FROM 受注表
              WHERE 受注個数 > KOSUU
              ORDER BY 受注個数 ;
      END IF ;
  END ;

 /* ストアドプロシージャの実行 */

  BEGIN
        pro_BIG(150)
  END ;

 受注番号  受注個数
 ──────── ────
 10012    175
 10002    200
 10006    250
 10010    290
 10001    300
  

sample.24-4 は、PostgreSQL の関数で IF 文を使用した場合の例です。商品名が 'BEER' または 'WINE' の場合は、'ALCOHOL' と表示し、そうでない場合は 'NON-ALCOHOL' と表示します。→ 受注表、商品表

 /* 関数の定義 */

  CREATE FUNCTION func_CATEGORY(CHAR(20))
  RETURNS CHAR(20)
  AS
  '
  BEGIN
      IF $1 = ''BEER'' OR $1 = ''WINE'' THEN
          RETURN ''ALCOHOL'' ;
      ELSE
          RETURN ''NON-ALCOHOL'' ;
      END IF ;
  END ;
  '
  LANGUAGE 'plpgsql' ;

 /* 関数の利用 */

  SELECT JJ.受注番号, func_CATEGORY(SS.商品名)
      FROM 受注表 JJ, 商品表 SS
      WHERE JJ.商品コード = SS.商品コード
      ORDER BY 1 ;

 受注番号  CATEGORY
 ──────── ───────
 10001    NON-ALCOHOL
 10002    ALCOHOL
 10003    ALCOHOL
 10004    NON-ALCOHOL
 10005    ALCOHOL
 10006    NON-ALCOHOL
 10007    NON-ALCOHOL
 10008    ALCOHOL
 10009    NON-ALCOHOL
 10010    ALCOHOL
 10011    NON-ALCOHOL
 10012    ALCOHOL
  

sample.24-3 と sample.24-4 についてよく考えてみれば分かることですが、ORACLE のストアドプロシージャを使用することで、データベースから抽出されたデータをまとめて処理することが簡単にできることが分かります。また、PostgreSQL では関数は複数の列を出力することができませんが、SELECT 文とうまく組み合わせることで、1行ずつ処理して複数の列の値を出力することが可能になります。ストアドプロシージャでは、前章で説明したカーソルの考え方を導入すれば、同じようなことが実現できます。

実習課題 1

PostgreSQL の SQL 関数を用いて、販売管理データベースのテーブル customer の列 address の値が、"Osaka city", "Takatsuki city" の場合は "Osaka Pref."、"Uji city", "Kyoto city" の場合は "Kyoto Pref."、"Nishinomiya city ", "Kobe city" の場合は "Hyogo Pref." と表示する関数を定義しなさい。また、定義した関数を利用して、各 Pref. ごとの人数を求める SQL 文を記述しなさい。

解答例はこちら

実習課題 2

販売データベースから、テーブル accept_order 中の受注に対して、支払い金額の平均より、支払い金額が多い受注に対して "Over"、少ない受注に対して "Under" を表示する関数を、PostgreSQL の PL/pgSQL 関数を用いて定義しなさい。また、定義した関数を用いて、受注番号と "Over" または "Under" を表示する SQL 文を記述しなさい。

• 支払い金額は 6章実習課題 2 を参考にすること。

解答例はこちら

実習課題 3

販売データベースのテーブル accept_order 中のデータで、支払い金額の平均値、最大値、最小値を別のテーブルに書き込むストアドプロシージャを定義しなさい。

• このストアドプロシージャを実行したときに書き込まれるデータは、通し番号、支払い金額の平均値、最大値、最小値及びテーブルにデータを書き込んだ日である。
• 支払い金額は 6章実習課題 2 を参考にすること。
• このストアドプロシージャの定義は ORACLE 上で行うものとする。

解答例はこちら

実習課題 4

販売データベースのテーブル accept_order 中のデータで、販売した製品のタイプの台数を別テーブルに書き込むストアドプロシージャを定義しなさい。

• 製品のタイプとは、テーブル product の type 列のデータのことである。
• ストアドプロシージャが実行されたとき、一旦テーブルの内容は破棄され、現在のデータを新しく書き込むという処理が行われる。
• テーブルに書き込まれるデータは、通し番号、製品のタイプ及び台数である。
• このストアドプロシージャの定義は ORACLE 上で行うものとする。

解答例はこちら