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テーブル関数の例を紹介します。

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

/*************************************/
/* The definition of the scratch pad */
/*************************************/

class item implements Serializable
{
   int custid;
   int itemid;
}
class local_ctx implements Serializable{
   int Num_Items;
   int Cur_Item;
   int store_num;
   item[] Item_List;
   public local_ctx() {}
   public local_ctx(int Num_Items, 
                    int Cur_Item,
                    int store_num,
                    item[] Item_List)
   {
      this.Num_Items = Num_Items;
      this.Cur_Item = Cur_Item;
      this.store_num = store_num;
      this.Item_List = Item_List;
   }
}

public class UserDefinedFunctions {

   /****************************/
   /* Reset the context block. */
   /****************************/
   static void Reset(local_ctx info)
   {
      info.Num_Items = 0;
      info.Cur_Item = 0;
      info.Item_List = null;
   }

   /*************************************************************/
   /* Extract all of the data now. Actually this routine just   */
   /* takes the items that Prescan built and transfers the data */
   /* out one item at a time.                                   */ 
   /*************************************************************/
   static int Extract(local_ctx info,
                      int[] custid,
                      int[] store,
                      int[] itemid)
   {
      /* check to see if there is something left to extract */
      if (info.Cur_Item == info.Num_Items)
         return 0;

      /* okay let's set the output data only if they want it */
      custid[0] = info.Item_List[info.Cur_Item].custid;
      store[0] = info.store_num;
      itemid[0] = info.Item_List[info.Cur_Item].itemid;

      /* set up for next item the next time */
      info.Cur_Item++;
      return 1;
   }

   /*************************************************************/
   /* Do a pre-scan of the text and save the data.              */
   /* The text data that this function processes is in a very   */
   /* simple format:                                            */
   /* <storenum>,<num items>:<customer id>,<item number>, ... ; */
   /* <storenum>,<num items>: ...                               */
   /*************************************************************/
   static int Prescan(local_ctx info,
                      String Text,
                      int frmstore) throws IOException
   {
      int storenum = 0;
      int num_items = 0;

      /* find the data for the store we are interested in */
      String Tscan = Text;
      int startpos = 0;
      while (startpos != (-1)) {
         int numpos = Tscan.indexOf(',',startpos);
         if (numpos == -1) return -1;
         storenum = Integer.parseInt(Tscan.substring(startpos,numpos));
         startpos = numpos+1;
         if(frmstore == storenum) {
            num_items = Integer.parseInt(Tscan.substring(startpos,
                                         Tscan.indexOf(':',startpos)));
            break;
         }
         if (Tscan.indexOf(';') == -1) return -1;
         else {
            Tscan = Tscan.substring(Tscan.indexOf(';')+1,
                                                       Tscan.length());
            startpos = 0;
            if(Tscan.equals("")) return 0;
         }
      }

      if (num_items != 0) {
         info.Item_List = new item[num_items];			
      }
      else {
         info.Num_Items = 0;
         return 0;
      }

      /* Now let's find all the entries for the store that we are */
      /* interested in. Skip to first item. */
      if (Tscan.indexOf(':')==-1 || Tscan.indexOf(';')==-1) return -1;
      Tscan = Tscan.substring(Tscan.indexOf(':')+1,Tscan.indexOf(';'));
      int pos  = 0;
      for (int i=0; i<num_items; i++) {
         info.Item_List[i] = new item();
         int nextcust = Tscan.indexOf(',',pos);
         if(nextcust == -1)  return -1;
         info.Item_List[i].custid=
                       Integer.parseInt(Tscan.substring(pos,nextcust));
         pos = nextcust + 1;
         int nextitem = Tscan.indexOf(',',pos);
         if (nextitem != -1) {
            info.Item_List[i].itemid =
                       Integer.parseInt(Tscan.substring(pos,nextitem));
            pos = nextitem + 1;
         }
         else {
            info.Item_List[i].itemid =
                 Integer.parseInt(Tscan.substring(pos,Tscan.length()));
            break;
         }
      }

      info.Num_Items = num_items;
      info.store_num = frmstore;	
      return num_items;
   }

   public static void extract_field(String Text, /* field decode */
                           int frmStore, /* data to extract */
                           int[] custid, /* 1st output column for row */
                           int[] store,  /* 2nd output column */
                           int[] item) 
   throws SQLException {

      local_ctx state_info;
      int status;
      int[] phase = new int[1];
      Tbl tbl = new Tbl();

      try{
         /* make sure the function is called in the supported context */
         switch (tbl.getPhase(phase))
         {
            /******************************************************/
            /* Process the constant expression case. Only one AMP */
            /* will participate for this example.                 */
            /******************************************************/
            case Tbl.TBL_MODE_CONST:
            /* depending on the phase decide what to do */
               switch(phase[0])
               {
                  case Tbl.TBL_PRE_INIT:
                     if(tbl.firstParticipant()){
                        /* participant */
                        return;
                     } else {
                        /* don't participate */
                        if(!tbl.optout()){		
                           throw new SQLException("Opt-out failed.",
                                                              "38U06");
                        }
                        return;
                     }		
                  case Tbl.TBL_INIT:
                     state_info = new local_ctx();
                     /* Get scratch memory to keep track of things */
                     Reset(state_info);
                     /* Pre-process the Text */
                     status = Prescan(state_info, Text, frmStore );			
                     if (status == -1) {
                        throw new SQLException(
                                   "Text had pre-scan errors","38U08");
                     }
                     tbl.allocCtx(state_info);
                     tbl.setCtxObject(state_info);
                     break;
                  case Tbl.TBL_BUILD:
                     state_info = (local_ctx)tbl.getCtxObject();
                     status = Extract(state_info,
                                      custid,
                                      store,
                                      item);

                     if (status == 0)
                        /* Have no more data, return no data sqlstate */
                        throw new SQLException("no more data","02000");
                     else if (status == -1){
                        throw new SQLException(
                                     "Text had extract error","38U09");
                     }
                     tbl.setCtxObject(state_info);
                     break;
                  case Tbl.TBL_END:
                     /* everyone done */
                     state_info = (local_ctx)tbl.getCtxObject();
                     break;
               }
               break;			
            /**********************************/
            /* Process the varying expression */
            /**********************************/
            case Tbl.TBL_MODE_VARY:
               switch(phase[0])
               {
                  case Tbl.TBL_PRE_INIT:
                     item[] itemarr = new item[3];
                     for (int i=0; i < itemarr.length; i++)
                        itemarr[i] = new item();
                     state_info = new local_ctx(0,0,0,itemarr);
                     /* get scratch memory to use from now on */
                     tbl.allocCtx(state_info);
                     break;
                  case Tbl.TBL_INIT:
                     state_info = new local_ctx();
                     /* Pre-process the Text */
                     status = Prescan(state_info, Text, frmStore );
                     if (status == -1) {
                        if (tbl.abort()) {
                           throw new SQLException(
                                   "Text had pre-scan errors","38U08");
                        }
                     }
                     tbl.setCtxObject(state_info);
                     break;
                  case Tbl.TBL_BUILD:
                     state_info = (local_ctx)tbl.getCtxObject();
                     status = Extract(state_info,
                                      custid,
                                      store,
                                      item);
                     if (status == 0)
                        /* Have no more data return no data sqlstate */
                        throw new SQLException("no more data","02000");
                     else if (status == -1) {
                        /* if I was the first then report the error */
                        if(tbl.abort()) {
                           throw new SQLException(
                                     "Text had extract error","38U09");
                        }
                        return;
                     }
                     tbl.setCtxObject(state_info);
                     break;
                  case Tbl.TBL_FINI:
                     /* initialize for the next set of data */
                     state_info = (local_ctx)tbl.getCtxObject();
                     Reset(state_info);
                     break;
                  case Tbl.TBL_END:
                     break;
                  case Tbl.TBL_ABORT:
                     break;
               }
               return;
         }
      } catch(ClassNotFoundException e) {
         e.printStackTrace();
      } catch(IOException e) {
         e.printStackTrace();
      }
   }
}