PostgreSQL Plus V1.0L10 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 '
関数処理定義..

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

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 ユーザガイド”の“疑似データ型”を参照してください。

  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のマニュアルを参照してください。


目次 索引 前ページトップページ

All Rights Reserved, Copyright(C) 富士通株式会社 2004