PostgreSQL Plus JDBCドライバユーザーズガイド

目次 索引

第1章 PostgreSQL Plus JDBCドライバ

1.3 PostgreSQL Plus JDBCドライバの使用方法

1.3.2 プロシジャのOUTパラメータの使用方法

PostgreSQL Plusでは、プロシジャはサポートされていません。通常、DDLでのプロシジャ定義を関数定義で代替して使用します。DDL上は、関数定義を使用しますが、JDBCアプリケーションの記述は、CallableStatementクラスを使用した通常のプロシジャ呼出しとして処理することができます。

また、プロシジャにOUTパラメータが定義されている場合は、関数定義とユーザ定義型を組み合わせることで、CallableStatementを使用したプロシジャ呼出しとして処理することができます。

OUTパラメータ使用時の関数定義

Create function func_name (パラメータの並び) returns ret_type as '
関数処理定義..

• 関数名 func_name は一意になるようにしてください。
• 関数名 func_name はプログラムから呼び出すプロシジャ名となります。
• 関数の復帰値 ret_type に指定するユーザ定義型には、関数に指定するすべてのパラメータを定義してください。
  その場合、関数のパラメータの数、データ型、および順番を必ず一致させて定義してください。
  パラメータの数、データ型、または順番が一致していない場合、call文の実行時に未定義エラーの例外が通知されます。
• 関数定義は、JavaアプリケーションからはCallableStatementクラスにより、プロシジャとして呼び出すことができますが、システムカタログとしては、関数として定義されています。
• 関数のパラメータの並びには、以下のデータ型を指定することができます。
  ・基本型

共通のテーブル定義の例を以下に示します。

create table profile_tab( id int not null,
                          name varchar(50),
                          age int ,
                          blood char(2),
                          salary int,
                          primary key(id));

例1 : 従業員のプロフィールテーブルから、従業員番号に対応した、従業員名、年齢、血液型を取り出すプロシジャの呼出し例

プロシジャ定義の例

create procedure selectprofile( id_var in integer,
                                name_var out varhcar(50),
                                age_var out integer,
                                blood_var out char(2)) as
begin
  select name, age, blood
    into name_var, age_var, blood_var from profile_tab
    where id = id_var ;
end;

1. ユーザ定義型の定義
   PostgreSQLの関数定義では、パラメータのモードを指定することができません。そのため、複数の値を返却できるように、ユーザ定義型を定義し、それを関数の復帰値とします。
   ユーザ定義型には、INパラメータ、OUTパラメータを含め、すべての関数のパラメータを定義してください。関数のパラメータの数、データ型、および順番は、必ず関数で定義した要素と一致させてください。

   create type type1 as ( id int, name varchar(50), age int, blood char(2));

2. 関数定義
   1.で定義したユーザ定義型を復帰値に指定した関数を、定義します。
   関数のパラメータの数、データ型、および順番は、必ずユーザ定義型で定義した要素と一致させてください。

   create function selectprofile( int, varchar(50), int, char(2)) returns type1 as ' select id, name, age, blood from profile_tab where id = $1; ' language 'sql';

3. Javaアプリケーションの記述
   以下に、Javaアプリケーションの記述例を示します。

   import java.sql.*; ... Connection conn=null; CallableStatement cstmt=null; try { conn=...; // データベースのコネクションの確立 cstmt=conn.prepareCall(“{call selectprofile(?,?,?,?)}”); cstmt.setInt(1,11111); // inパラメータ　従業員番号”11111”のデータを検索 cstmt.registerOutParameter(2,java.sql.Types.VARCHAR); // OUT cstmt.registerOutParameter(3,java.sql.Types.INTEGER); // OUT cstmt.registerOutParameter(4,java.sql.Types. CHAR); // OUT // 実行 cstmt.execute(); // OUTパラメータの参照 System.out.println(“name = “+cstmt.getString(2)); System.out.println(“age = “+cstmt.getInt(3)); System.out.println(“blood = “+cstmt.getString(4)); } catch (SQLException sqle){ System.err.println(sqle.toString()); } finally { // 後処理 try{ if (cstmt != null){ cstmt.close(); cstmt=null; } } catch(Exception e){} try{ if (conn!=null){ conn.close(); conn=null; } } catch(Excpetion e) {} }

   JDBCアプリケーションの詳細は、J2SEのマニュアルを参照してください。

例2 : 従業員のプロフィールテーブルから、従業員番号に対応した、従業員名、年齢、血液型，基本給与を返却する例（基本給与からは、年齢に応じて保険料を差し引いた額を取り出す）

プロシジャ定義の例

create procedure selectprofile2( id_var in integer,
                                name_var out varhcar(50),
                                age_var out integer,
                                blood_var out char(2),
                                salary_var out integer ) as
 begin
  select name, age, blood, salary 
    into name_var, age_var, blood_var, salary_var from profile_tab
    where id = id_var ;
  if age_var >= 20 and age_var < 30 then
     salary := salary - 10000;
  end if;
  if age_var >= 30 and age_var < 40 then
     salary := salary -15000;
  end if;
  if age_var >= 40 and age_var < 50 then
     salary := salary -18000;
  end if;
  if age_var >= 50 and age_var < 60 then
     salary := salary -20000;
  end if;
  return;
end;

1. ユーザ定義型の定義
   例1と同様に、定義する関数のパラメータをすべて要素にもつ、ユーザ定義型を定義します。

   create type type2 as ( id int, name varchar(50), age int, blood char(2), salary int );

2. 関数定義
   type2を復帰値のデータ型とする関数のselectprofile2を定義します。

   create function selectprofile2( int, varchar(50), int, char(2), int) returns type2 as ' declare id_var int; name_var varchar(50); age_var int; blood_var char(2); salary_var int; ret_var record; begin select id, name, age, blood, salary into id_var, name_var, age_var, blood_var, salary_var from profile_tab where id = $1 ; if age_var >= 20 and age_var < 30 then salary_var := salary_var - 10000; end if; if age_var >= 30 and age_var < 40 then salary_var := salary_var - 15000; end if; if age_var >= 40 and age_var < 50 then salary_var := salary_var - 18000; end if; if age_var >= 50 and age_var < 60 then salary_var := salary_var - 20000; end if; select id_var, name_var, age_var, blood_var, salary_var into ret_var ; return ret_var; end; ' language 'plpgsql';

   language plpgsqlを使用する場合は、RECORD型を定義し、返却する内容をRECORD型変数に代入します。その変数をreturn文に指定することで、関数の復帰時、指定したデータ型でタイプキャストされて値が返却されます。詳細は、“PostgreSQL 文書”の“II.SQL言語 − 8.12.疑似データ型”を参照してください。
   

3. Javaアプリケーションの記述
   以下に、Javaアプリケーションの記述例を示します。

   import java.sql.*; .. Connection conn=null; CallableStatement cstmt=null; try { conn=...; // データベースのコネクションの確立 cstmt=conn.prepareCall(“{call selectprofile2(?,?,?,?,?)}”); cstmt.setInt(1,11111); // inパラメータ　従業員番号”11111”のデータを検索 cstmt.registerOutParameter(2,java.sql.Types.VARCHAR); // OUT cstmt.registerOutParameter(3,java.sql.Types.INTEGER); // OUT cstmt.registerOutParameter(4,java.sql.Types.CHAR); // OUT cstmt.registerOutParameter(5,java.sql.Types.INTEGER); // OUT // 実行 　cstmt.execute(); // OUTパラメータの参照 System.out.println(“name = “+cstmt.getString(2)); System.out.println(“age = “+cstmt.getInt(3)); System.out.println(“blood = “+cstmt.getString(4)); System.out.println(“salary = “+cstmt.getInt(5)); } catch (SQLException sqle){ System.err.println(sqle.toString()); } finally { // 後処理 try{ if (cstmt != null){ cstmt.close(); cstmt=null; } } catch(Exception e){} try{ if (conn!=null){ conn.close(); conn=null; } } catch(Excpetion e) {} }

   JDBCアプリケーションの詳細は、J2SEのマニュアルを参照してください。

目次 索引