17.00 - 17.05 - Javaメソッドの実装 - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL外部ルーチン プログラミング

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
17.05
Published
2020年6月
Content Type
プログラミング リファレンス
Publication ID
B035-1147-170K-JPN
Language
日本語 (日本)

ここでは、単純なマッピングによる以前のSQL関数定義に対応する、単純なマッピングによる集約Java UDFの例を紹介します。

import com.teradata.fnc.*;
import java.io.*;
import java.sql.*;

class Storage implements Serializable{
   double total;

   public Storage() { total = 0.0; }
}

public class UDFExample {

   public static int simpleSum(Phase phase, Context[] context, int x)
   throws SQLException
   {
      try{
         Storage s1 = null;
         /* AGR_DETAIL, AGR_COMBINE, and AGR_FINAL phases
            use the value from the aggregate storage area. */
         if(phase.getPhase()>Phase.AGR_INIT &&
            phase.getPhase()<Phase.AGR_NODATA){

            s1 = (Storage)context[0].getObject(1);
         }

         switch (phase.getPhase()) {

               /* The AGR_INIT phase is executed once. */
               case Phase.AGR_INIT:
                  s1 = new Storage();
                  context[0].initCtx(s1);
                  /* Fall through to detail phase also. */

               case Phase.AGR_DETAIL:
                  /* Perform SUM */
                  s1.total += x;
                  break;

               case Phase.AGR_COMBINE:
                  /* SUM between AMPs. */
                  Storage s2 = (Storage)context[0].getObject(2);
                  s1.total += s2.total;
                  break;

               case Phase.AGR_FINAL:
                  /* Final, return SUM. */
                  return s1.total;

               case Phase.AGR_NODATA:
                  /* Not expecting no data. */
                  return -1;

               default:
                  throw new SQLException("Invalid Phase", "38U05");
         }

         /* Save the intermediate SUM in the aggregate storage. */
         context[0].setObject( 1, s1 );
      }catch(Exception ex){
         throw new SQLException(ex.getMessage(),"38101");
      }
      return -1;
   }
}