|
SQL 13章 ストアドプロシージャ (SQL 非標準)
13.3. ストアドプロシージャの利用単純なストアドプロシージャとして、SQL 文と同様のことをストアドプロシージャを用いた場合の例を示します。sample.24-1 は 6 章でも使用した 受注表 と 顧客表 を結合し、200個以上注文したことのある顧客名を出力するものです。PostgreSQL の関数では、出力する値が複数行の可能性があるため、SETOF 句を記述して複数行の出力を許可しています。PostgreSQL では返り値のデータ型を指定しなければなりませんので、RETURNS SETOF CHAR(20) としています。 これは SQL で記述された関数であるため、LANGUAGE = 'sql'としています。 -- sample.24-1 -- <<ORACLE>>
/* ストアドプロシージャの定義 */
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
<<PostgreSQL>>
/* 関数の定義 */
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 ということになります。→ 受注表 、顧客表 -- sample.24-2 -- <<ORACLE>>
/* ストアドプロシージャの定義 */
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
<<PostgreSQL>>
/* 関数の定義 */
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' を表示します。→ 受注表 -- sample.24-3 -- <<ORACLE>>
/* ストアドプロシージャの定義 */
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' と表示します。→ 受注表、商品表 -- sample.24-4 --
<<PostgreSQL>>
/* 関数の定義 */
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行ずつ処理して複数の列の値を出力することが可能になります。ストアドプロシージャでは、前章で説明したカーソルの考え方を導入すれば、同じようなことが実現できます。 実習課題 1PostgreSQL の 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 文を記述しなさい。
実習課題 3販売データベースのテーブル accept_order 中のデータで、支払い金額の平均値、最大値、最小値を別のテーブルに書き込むストアドプロシージャを定義しなさい。
実習課題 4販売データベースのテーブル accept_order 中のデータで、販売した製品のタイプの台数を別テーブルに書き込むストアドプロシージャを定義しなさい。
|
|
