17.00 - 17.05 - UDT入力列と出力列を持つ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
日本語 (日本)

以下は、入力列を受け入れ、UDT型または複合型の出力列を返すことができるJavaテーブル演算子の例です。コントラクト関数は、UDT型またはCDT型の処理方法をそれぞれ示します。

/*
CREATE FUNCTION jtblop_udtvals()  
RETURNS TABLE VARYING USING FUNCTION jtblop_udtvals_contract
LANGUAGE JAVA NO SQL  
PARAMETER STYLE SQLTABLE
EXTERNAL NAME 'TBLOPUDT_JAR:jtblop_udtvals.execute';
*/
public class jtblop_udtvals implements TableOperator 
{
   public jtblop_udtvals() {};

public int contract(RuntimeContract contract, ResultSet rsin[], ResultSet rsout[])
throws SQLException 
{
   /* Get number of input streams */
   int incount = contract.getInputInfo().getIncount();
  
   /* Get input columns. */
   MetaData iCols;
   int total_colcount = 0;
   for(int i = 0; i < incount; i++)
   {        
      if (rsin[i] != null)
      {
         iCols = ((TeradataResultSet)rsin[i]).getTeradataMetaData();
         total_colcount += iCols.getColumnCount();        
                    
      }
   }
        
   /* Create an equal number of output columns. */
      
   ColumnDefinition OutCols[] = new ColumnDefinition[total_colcount];

   /* Copy input columns to output columns. */
   int ocount = 0;
   for(int j = 0; j < incount; j++)
   {
      MetaData inCols = ((TeradataResultSet)rsin[j]).getTeradataMetaData();;
      UDTBaseInfo udtbaseinfo[] = contract.getInputInfo().getUDTMetadata(j);
      for (int i=0;i<inCols.getColumnCount();i++) 
      {
         OutCols[ocount]=new ColumnDefinition("O"+ocount, inCols.getTeradataColumnType(i+1));
         OutCols[ocount].setDisplayLength(inCols.getColumnDisplaySize(i+1));      
   
         TeradataType t = TeradataType.get(inCols.getTeradataColumnType(i+1));
         switch (t) 
         {
            case VARCHAR_DT:
            case CHAR_DT: 
               OutCols[ocount].setCharset(inCols.getPrecision(i+1));
               break;
            case BLOB_REFERENCE_DT:
            case CLOB_REFERENCE_DT:
            case VARBYTE_DT: 
            case XML_DT:
            case JSON_DT:     
            case ST_GEOMETRY_DT:
               OutCols[ocount].setCharset(inCols.getPrecision(i+1));
               OutCols[ocount].setDisplayLength(inCols.getColumnDisplaySize(i+1));
               break;
            case DECIMAL1_DT:
            case DECIMAL2_DT:
            case DECIMAL4_DT:
            case DECIMAL8_DT:
            case DECIMAL16_DT:
               OutCols[ocount].setPrecision(inCols.getPrecision(i+1));
               OutCols[ocount].setScale(inCols.getScale(i+1));
            case NUMBER_DT:
               OutCols[ocount].setPrecision(inCols.getPrecision(i+1));
               OutCols[ocount].setScale(inCols.getScale(i+1));
               break;
            case TIME_DT:
            case TIMESTAMP_DT:
            case TIME_WTZ_DT:
            case TIMESTAMP_WTZ_DT:
            case PERIOD_DT:
               OutCols[ocount].setPrecision(inCols.getPrecision(i+1));
               break;
            case INTERVAL_SECOND_DT:
            case INTERVAL_DTS_DT:
            case INTERVAL_HTS_DT:
            case INTERVAL_MTS_DT:
            case INTERVAL_DTM_DT:     // DAY TO MINUTE
               OutCols[ocount].setPrecision(inCols.getPrecision(i+1));
               OutCols[ocount].setScale(inCols.getScale(i+1));
               break;
            case ARRAY_DT:
            case UDT_DT:
            case MBR_DT:
            case MBB_DT:
               OutCols[ocount].setUdtName(inCols.getUdtTypeName(i+1));
          
         }
         OutCols[ocount].setPeriodType(inCols.getPeriodType(i+1));
         ocount++;
      }
   }

   /* Define output columns. */
   contract.setOutputInfo(0, OutCols); 
   contract.complete();
   return 1;          
}

public void execute(RuntimeContract contract, ResultSet rsin[], ResultSet rsout[])
throws SQLException 
{  
   /* Tblop Execute code */  
}  /* execute */
}