ÀÀ¿ëÇÁ·Î±×·¥ °³¹ß ¾È³»¼­

UDF ÄÚµå ¿¹

´ÙÀ½°ú °°Àº ¿¹Á¦ UDF Äڵ尡 DB2¿¡¼­ Á¦°øµË´Ï´Ù.

"¿¹: Á¤¼ö ³ª´©±â ¿¬»êÀÚ"
"¿¹: CLOB Á¢±â, ¸ðÀ½ ã±â"
"¿¹: °è¼ö±â(Counter)"

Á¦°øµÇ´Â ¸ðµç ¿¹µéÀÌ ÀÖ´Â À§Ä¡¿Í À̵éÀ» È£ÃâÇÏ´Â ¹æ¹ýÀº ºÎ·Ï B, »ùÇà ÇÁ·Î±×·¥ÀÇ ³»¿ëÀ» ÂüÁ¶ÇϽʽÿÀ.

UDF ÄÄÆÄÀÏ ¹× ¸µÅ©¿¡ °üÇÑ Á¤º¸´Â ÀÀ¿ëÇÁ·Î±×·¥ ºôµå ¾È³»¼­ Ã¥À» ÂüÁ¶ÇϽʽÿÀ.

°¢°¢ÀÇ UDF ¿¹´Â ±×¿¡ ÇØ´çµÇ´Â CREATE FUNCTION¹® ¹× ±× »ç¿ëÀ» º¸¿©ÁÖ´Â °£´ÜÇÑ ½Ã³ª¸®¿À¿Í ÇÔ²² Á¦°øµË´Ï´Ù. ÀÌ ½Ã³ª¸®¿ÀµéÀº ¸ðµÎ ´ÙÀ½ÀÇ Å×À̺í TEST¸¦ »ç¿ëÇϴµ¥, À̰ÍÀº ½Ã³ª¸®¿À¿¡¼­ ¼³¸íÇϰíÀÚ ÇÏ´Â ¹Ù¸¦ º¸¿©ÁÖ±â À§ÇØ ÀÛ¼ºµÈ °ÍÀÔ´Ï´Ù. ´ÙÀ½Àº Å×À̺í Á¤ÀÇÀÔ´Ï´Ù.

     CREATE TABLE TEST (INT1 INTEGER,
                        INT2 INTEGER,
                        PART CHAR(5),
                        DESCR CLOB(33K))

Å×ÀÌºí¿¡ µ¥ÀÌÅ͸¦ ÀÔ·ÂÇÑ ÈÄ, CLP¸¦ »ç¿ëÇÏ¿© ´ÙÀ½ÀÇ ¸í·É¹®À» ¹ßÇàÇÏ¿© ±× ³»¿ëÀ» Ç¥½ÃÇϽʽÿÀ.

     SELECT INT1, INT2, PART, SUBSTR(DESCR,1,50) FROM TEST

Ãâ·ÂÀÇ Àß º¼ ¼ö ÀÖ°Ô Çϱâ À§ÇØ CLOB Ä÷³¿¡¼­ »ç¿ëÇÑ SUBSTR ÇÔ¼ö¿¡ ÁÖÀÇÇϽʽÿÀ. »ç¿ëÀÚ´Â ´ÙÀ½°ú °°Àº CLP Ãâ·ÂÀ» ¹Þ°Ô µË´Ï´Ù.

INT1        INT2        PART  4
----------- ----------- ----- ------------------------------------------------
         16           1 brain The only part of the body capable of forgetting.
          8           2 heart The seat of the emotions?
          4           4 elbow That bendy place in mid-arm.
             2           0           -           -
         97          16 xxxxx Unknown.
       5°³ÀÇ ·¹Äڵ尡 ¼±ÅõǾú½À´Ï´Ù.

¾Æ·¡ÀÇ ¿¹¿Í ½Ã³ª¸®¿À¸¦ ÀÐÀ¸¸é¼­ Å×À̺í TESTÀÇ ¾Õ¼­ ³ª¿Â Á¤º¸¸¦ ÂüÁ¶ÇϽʽÿÀ.

¿¹: Á¤¼ö ³ª´©±â ¿¬»êÀÚ

DB2¿¡¼­ÀÇ Á¤¼ö ³ª´©±â ÀÛ¾÷¿¡¼­, ¿À·ù SQLCODE -802(SQLSTATE 22003)ÀÌ ¹ß»ýÇϰí, 0À¸·Î ³ª´©¸é ¸í·É¹®À» Á¾·áÇϱ⠶§¹®¿¡ »ç¿ëÀÚ´Â ÀÌ·¯ÇÑ Á¤¼ö ³ª´©±â ÀÛ¾÷ ¹æ½Ä¿¡ ºÒ¸¸ÀÌ ÀÖ´Ù°í °¡Á¤ÇÕ´Ï´Ù. »ç¿ëÀÚ°¡ DFT_SQLMATHWARN ±¸¼º ¸Å°³º¯¼ö¸¦ »ç¿ëÇϴ ģ±ÙÇÑ »ê¼ú ¿¬»êÀ» ÀÛµ¿ °¡´ÉÇÏ°Ô Çß´Ù¸é, DB2´Â ¿À·ù ´ë½Å ³Î(NULL)À» ¸®ÅÏÇÕ´Ï´Ù. ´ë½Å, Á¤¼ö ³ª´©±âÇÒ ¶§ ³Î(NULL)À» ¸®ÅÏÇϵµ·Ï Çϱâ À§ÇØ ÀÌ UDF¸¦ ÄÚµùÇÕ´Ï´Ù.

#include <stdlib.h>
#include <string.h>
#include <stdio.h>
#include <sqludf.h>
#include <sqlca.h>
#include <sqlda.h>
 
 /*******************************************************************
*  function divid: performs integer divid, but unlike the / operator
*                  shipped with the product, gives NULL when the
*                  denominator is zero.
*
*                This function does not use the constructs defined in the
*                "sqludf.h" header file.
*
*     inputs:  INTEGER num     numerator
*              INTEGER denom   denominator
*     output:  INTEGER out     answer
********************************************************************/
#ifdef __cplusplus
extern "C"
#endif
void SQL_API_FN divid (
   sqlint32 *num,                       /* numerator */
   sqlint32 *denom,                     /* denominator */
   sqlint32 *out,                       /* output result */
   short *in1null,                      /* input 1 NULL indicator */
   short *in2null,                      /* input 2 NULL indicator */
   short *outnull,                      /* output NULL indicator */
   char *sqlstate,                      /* SQL STATE */
   char *funcname,                      /* function name */
   char *specname,                      /* specific function name */
   char *mesgtext) {                    /* message text insert */
 
   if (*denom == 0) {      /* if denominator is zero, return null result */
      *outnull = -1;
   } else {                /* else, compute the answer */
      *out = *num / *denom;
      *outnull = 0;
   } /* endif */
}
/* end of UDF : divid */

ÀÌ UDFÀÇ °æ¿ì ´ÙÀ½¿¡ ÁÖÀÇÇϽʽÿÀ.

´ÙÀ½Àº ÀÌ UDF¿¡ ´ëÇÑ CREATE FUNCTION¹®ÀÔ´Ï´Ù.

     CREATE FUNCTION MATH."/"(INT,INT)
       RETURNS INT
       NOT FENCED
       DETERMINISTIC
       NO SQL
       NO EXTERNAL ACTION
       LANGUAGE C
       PARAMETER STYLE DB2SQL
       EXTERNAL NAME '/u/slick/udfx/div' ;

À̰ÍÀº UDFÀÇ AIX ¹öÀü¿¡ ´ëÇÑ °ÍÀÔ´Ï´Ù. ´Ù¸¥ Ç÷§ÆûÀÇ °æ¿ì EXTERNAL NAMEÀý¿¡ ÁöÁ¤µÈ °ªÀ» ¼öÁ¤ÇØ¾ß ÇÒ °ÍÀÔ´Ï´Ù.

ÀÌ ¸í·É¹®ÀÇ °æ¿ì, ´ÙÀ½¿¡ ÁÖÀÇÇϽʽÿÀ.

ÀÌÁ¦ ´ÙÀ½ÀÇ µÎ °¡Áö ¸í·É¹®À» ¼öÇàÇÏ´Â °æ¿ì(CLP ÀÔ·ÂÀÌ Ç¥½ÃµÇ¾úÀ½):

     SET CURRENT FUNCTION PATH = SYSIBM, SYSFUN, SLICK
     SELECT INT1, INT2, INT1/INT2, "/"(INT1,INT2) FROM TEST

ÀÌ Ãâ·ÂÀ» CLP¿¡¼­ ¾ò°Ô µË´Ï´Ù(DFT_SQLMATHWARN µ¥ÀÌÅͺ£À̽º ±¸¼º ¸Å°³º¯¼ö¿ÍÀÇ Ä£±ÙÇÑ »ê¼úÀ» Çã¿ëÇÏÁö ¾Ê´Â °æ¿ì).

 
 
   INT1        INT2        3           4
   ----------- ----------- ----------- -----------
            16           1          16          16
             8           2           4           4
             4           4           1           1
   SQL0802N  »ê¼ú ¿À¹öÇÃ·Î¿ì ¶Ç´Â ´Ù¸¥ »ê¼ú ¿¹¿Ü°¡ ¹ß»ýÇß½À´Ï´Ù.
   SQLSTATE=22003

»ç¿ëÀÚ°¡ CURRENT FUNCTION PATH Ư¼ö ·¹Áö½ºÅ͸¦ "/" UDF°¡ Á¤ÀÇµÈ ½ºÅ°¸¶ÀÎ MATH°¡ Æ÷ÇÔµÇÁö ¾ÊÀº ½ºÅ°¸¶µéÀÇ º´ÇÕÀ¸·Î ¼³Á¤Ç߱⠶§¹®¿¡ SQL0802N ¿À·ù ¸Þ½ÃÁö°¡ ¹ß»ýÇÕ´Ï´Ù. ±×·¯¹Ç·Î »ç¿ëÀÚ´Â DB2ÀÇ ³»Àå ³ª´©±â ¿¬»êÀÚ¸¦ ½ÇÇà½Ã۰í, ÀÌ ¿¬»êÀÚÀÇ Á¤ÀÇµÈ ±â´ÉÀº "0À¸·Î ³ª´©±â" Á¶°ÇÀÌ ¹ß»ýÇÏ¸é ¿À·ù¸¦ ¹ß»ý½Ãŵ´Ï´Ù. TEST Å×À̺íÀÇ 4¹øÂ° Çà¿¡¼­ ÀÌ Á¶°ÇÀ» Á¦°øÇÕ´Ï´Ù.

±×·¯³ª ÇÔ¼ö °æ·Î¸¦ º¯°æÇÏ¿© °æ·Î¿¡¼­ MATH¸¦ SYSIBM ¾Õ¿¡ ³õ´Â °æ¿ì, SELECT¹®À» ´Ù½Ã ¼öÇàÇϽʽÿÀ.

     SET CURRENT FUNCTION PATH = MATH, SYSIBM, SYSFUN, SLICK
     SELECT INT1, INT2, INT1/INT2, "/"(INT1,INT2) FROM TEST

±×·¯¸é ´ÙÀ½ÀÇ CLP Ãâ·Â¿¡¼­ º¼ ¼ö ÀÖµíÀÌ ¿øÇÏ´Â ÀÛµ¿ °á°ú¸¦ ¾òÀ» ¼ö ÀÖ½À´Ï´Ù.

   INT1        INT2        3           4
   ----------- ----------- ----------- -----------
            16           1          16          16
             8           2           4           4
             4           4           1           1
             2           0           -           -
            97          16           6           6
       5°³ÀÇ ·¹Äڵ尡 ¼±ÅõǾú½À´Ï´Ù.

À§ÀÇ ¿¹¿¡¼­ ´ÙÀ½¿¡ ÁÖ¸ñÇϽʽÿÀ.

¿¹: CLOB Á¢±â, ¸ðÀ½ ã±â

»ç¿ëÀÚÀÇ ÅØ½ºÆ® ó¸® ÀÀ¿ëÇÁ·Î±×·¥À» º¸Á¶Çϱâ À§ÇÑ µÎ °³ÀÇ UDF¸¦ ÄÚµùÇß´Ù°í °¡Á¤ÇϽʽÿÀ. ù¹øÂ° UDF´Â »ç¿ëÀÚÀÇ n¹øÂ° ÀÌÈÄÀÇ ÅØ½ºÆ® ¹®ÀÚ¿­À» Á¢½À´Ï´Ù. ÀÌ ¿¹¿¡¼­ Á¢´Â´Ù´Â °ÍÀÇ Àǹ̴ ¿ø·¡ n ¹ÙÀÌÆ®ÀÇ µÚ¿¡ ÀÖ´ø ºÎºÐÀ» n+1 ¹ÙÀÌÆ®ÀÇ ¾Õ ºÎºÐ ¾Õ¿¡ ³õ´Â °ÍÀÔ´Ï´Ù. Áï, ÀÌ UDF´Â ¹®ÀÚ¿­ÀÇ ¸Ç óÀ½ n ¹ÙÀÌÆ®¸¦ ¹®ÀÚ¿­ÀÇ ¸Ç µÚ·Î À̵¿ÇÕ´Ï´Ù. µÎ ¹øÂ° ÇÔ¼ö´Â ÅØ½ºÆ® ¹®ÀÚ¿­¿¡¼­ óÀ½À¸·Î ³ªÅ¸³ª´Â ¸ðÀ½ÀÇ À§Ä¡¸¦ ¸®ÅÏÇÕ´Ï´Ù. À̵é ÇÔ¼ö ¸ðµÎ udf.c ¿¹Á¦ ÆÄÀÏ¿¡ ÄÚµùµÇ¾î ÀÖ½À´Ï´Ù.

#include <stdlib.h>
#include <string.h>
#include <stdio.h>
#include <sqludf.h>
#include <sqlca.h>
#include <sqlda.h>
#include "util.h"
 
 /********************************************************************
*  function fold: input string is folded at the point indicated by the
*                 second argument.
*
*     input: CLOB    in1         input string
*            INTEGER in2         position to fold on
*            CLOB    out         folded string
*********************************************************************/
#ifdef __cplusplus
extern "C"
#endif
void SQL_API_FN fold (
   SQLUDF_CLOB     *in1,                /* input CLOB to fold */
   SQLUDF_INTEGER  *in2,                /* position to fold on */
   SQLUDF_CLOB     *out,                /* output CLOB, folded */
   SQLUDF_NULLIND  *in1null,            /* input 1 NULL indicator */
   SQLUDF_NULLIND  *in2null,            /* input 2 NULL indicator */
   SQLUDF_NULLIND  *outnull,            /* output NULL indicator */
   SQLUDF_TRAIL_ARGS) {                 /* trailing arguments */
 
   SQLUDF_INTEGER len1;
 
   if (SQLUDF_NULL(in1null) || SQLUDF_NULL(in2null)) {
      /* one of the arguments is NULL.  The result is then "INVALID INPUT" */
      strcpy( ( char * ) out->data, "INVALID INPUT" ) ;
      out->length = strlen("INVALID INPUT");
   } else {
      len1 = in1->length;               /* length of the CLOB */
 
      /* build the output by folding at position "in2" */
      strncpy( ( char * ) out->data, &in1->data[*in2], len1 - *in2
) ;
      strncpy( ( char * ) &out->data[len1 - *in2], in1->data, *in2
) ;
      out->length = in1->length;
   } /* endif */
   *outnull = 0;                        /* result is always non-NULL */
}
/* end of UDF : fold */
 
 /********************************************************************
*  function findvwl: returns the position of the first vowel.
*                  returns an error if no vowel is found
*                  when the function is created, must be defined as
*                  NOT NULL CALL.
*     inputs: VARCHAR(500) in
*     output: INTEGER      out
*********************************************************************/
#ifdef __cplusplus
extern "C"
#endif
void SQL_API_FN findvwl (
   SQLUDF_VARCHAR   *in,                /* input character string */
   SQLUDF_SMALLINT  *out,               /* output location of vowel */
   SQLUDF_NULLIND   *innull,            /* input NULL indicator */
   SQLUDF_NULLIND   *outnull,           /* output NULL indicator */
   SQLUDF_TRAIL_ARGS) {                 /* trailing arguments */
 
   short i;                             /* local indexing variable */
 
   for (i=0; (i < (short)strlen(in) &&         /* find the first vowel */
      in[i] != 'a' && in[i] != 'e' && in[i] != 'i' &&
      in[i] != 'o' && in[i] != 'u' && in[i] != 'y' &&
      in[i] != 'A' && in[i] != 'E' && in[i] != 'I' &&
      in[i] != 'O' && in[i] != 'U' && in[i] != 'Y'); i++);
   if (i == strlen( ( char * ) in )) {               /* no vowels found
*/
            /* error state */
      strcpy( ( char * ) sqludf_sqlstate, "38999" ) ;
            /* message insert */
      strcpy( ( char * ) sqludf_msgtext, "findvwl: No Vowel" ) ;
   } else {                             /* a vowel was found at "i" */
      *out = i + 1;
      *outnull = 0;
   } /* endif */
}
/* end of UDF : findvwl */

À§ÀÇ UDFÀÇ °æ¿ì ´ÙÀ½¿¡ ÁÖÀÇÇϽʽÿÀ.

´ÙÀ½Àº À̵é UDF¿¡ ´ëÇÑ CREATE FUNCTION¹®ÀÔ´Ï´Ù.

     CREATE FUNCTION FOLD(CLOB(100K),INT)
       RETURNS CLOB(100K)
       FENCED
       DETERMINISTIC
       NO SQL
       NO EXTERNAL ACTION
       LANGUAGE C
       NULL CALL
       PARAMETER STYLE DB2SQL
       EXTERNAL NAME 'udf!fold' ;
 
     CREATE FUNCTION FINDV(VARCHAR(500))
       RETURNS INTEGER
       NOT FENCED
       DETERMINISTIC
       NO SQL
       NO EXTERNAL ACTION
       LANGUAGE C
       NOT NULL CALL
       PARAMETER STYLE DB2SQL
       EXTERNAL NAME 'udf!findvwl' ;

À§ÀÇ CREATE FUNCTION¹®Àº UNIX Ç÷§ÆûÀ» À§ÇÑ °ÍÀÔ´Ï´Ù. ´Ù¸¥ Ç÷§Æû¿¡¼­´Â À§ ¸í·É¹®ÀÇ EXTERNAL NAMEÀý¿¡ ÁöÁ¤µÈ °ªÀ» ¼öÁ¤ÇØ¾ß ÇÒ °ÍÀÔ´Ï´Ù. DB2¿Í ÇÔ²² Á¦°øµÈ calludf.sqc ¿¹Á¦ ÇÁ·Î±×·¥¿¡¼­ À§ÀÇ CREATE FUNCTION¹®À» ãÀ» ¼ö ÀÖÀ» °ÍÀÔ´Ï´Ù.

ÀÌ CREATE¹®À» ÂüÁ¶Çϸ鼭 ´ÙÀ½¿¡ ÁÖÀDZí°Ô »ìÆì º¸½Ê½Ã¿À.

ÀÌÁ¦ ´ÙÀ½ ¸í·É¹®À» ¼º°øÀûÀ¸·Î ¼öÇàÇÒ ¼ö ÀÖ½À´Ï´Ù.

     SELECT SUBSTR(DESCR,1,30), SUBSTR(FOLD(DESCR,6),1,30) FROM TEST

ÀÌ ¸í·É¹®ÀÇ CLP Ãâ·ÂÀº ´ÙÀ½°ú °°½À´Ï´Ù.

     1                              2
     ------------------------------ ------------------------------
     The only part of the body capa ly part of the body capable of
     The seat of the emotions?      at of the emotions?The se
     That bendy place in mid-arm.   endy place in mid-arm.That b
     -                              INVALID INPUT
     Unknown.                       n.Unknow
       5°³ÀÇ ·¹Äڵ尡 ¼±ÅõǾú½À´Ï´Ù.

¼±ÅÃµÈ CLOB °ªÀÌ ´õ ¸ÚÁö°Ô Ç¥½ÃµÇµµ·Ï ÇÏ·Á¸é SUBSTR ³»Àå ÇÔ¼ö »ç¿ëÀ» °í·ÁÇØ º¸½Ê½Ã¿À. À̰ÍÀº Ãâ·ÂÀÌ Á¢È÷´Â ¹æ½ÄÀ» º¸¿©ÁÝ´Ï´Ù. Áï ù¹øÂ° Çຸ´Ù ªÀº CLOB °ªÀ» °®´Â µÎ ¹øÂ°, ¼¼ ¹øÂ° ¹× ´Ù¼¸ ¹øÂ° Çà¿¡¼­ °¡Àå Àß º¸À̹ǷÎ, ÀÌ·¸°Ô Á¢±â´Â SUBSTRÀÇ »ç¿ëÀ¸·Î ´õ¿í ¸íÈ®ÇØÁý´Ï´Ù. ¶ÇÇÑ ÀÔ·Â ÅØ½ºÆ® ¹®ÀÚ¿­(Ä÷³ DESCR)ÀÌ ³Î(NULL)ÀÎ °æ¿ì, INVALID INPUT ¹®ÀÚ¿­ÀÌ FOLD UDF¿¡ ÀÇÇØ ¸®ÅϵǴ ¹æ½ÄÀ» º¸¿©ÁÝ´Ï´Ù(³× ¹øÂ° Çà). ¶ÇÇÑ ÀÌ SELECT¿¡¼­´Â ÇÔ¼ö ÂüÁ¶ÀÇ ´Ü¼øÇÑ ÁßøÀ» º¸¿©Áִµ¥, FOLD¿¡ ´ëÇÑ ÂüÁ¶´Â SUBSTR ÇÔ¼ö ÂüÁ¶ÀÇ Àμö ³»¿¡ ÀÖ½À´Ï´Ù.

´ÙÀ½°ú °°Àº ¸í·É¹®À» ¼öÇàÇÏ´Â °æ¿ì,

     SELECT PART, FINDV(PART) FROM TEST

CLP Ãâ·ÂÀº ´ÙÀ½°ú °°½À´Ï´Ù.

     PART  2
     ----- -----------
     brain           3
     heart           2
     elbow           1
     -               -
     SQL0443N  "SLICK.FINDV" ·çƾ(ƯÁ¤ À̸§Àº "SQL950424135144750")ÀÌ
     Áø´Ü ÅØ½ºÆ® "findvwl: No Vowel"¿Í(°ú) ÇÔ²² ¿À·ù SQLSTATE¸¦ ¸®ÅÏÇß½À´Ï´Ù.
     SQLSTATE=38999

ÀÌ ¿¹¿¡¼­´Â 38999 SQLSTATE °ª°ú findvwl()¿¡ ÀÇÇØ ¸®ÅϵǴ ¿À·ù ¸Þ½ÃÁö ÅäÅ«ÀÌ Ã³¸®µÇ´Â ¹æ½ÄÀ» º¸¿©ÁÝ´Ï´Ù. ¸Þ½ÃÁö SQL0443NÀº ÀÌ·¯ÇÑ Á¤º¸¸¦ »ç¿ëÀÚ¿¡°Ô ¸®ÅÏÇÕ´Ï´Ù. ´Ù¼¸ ¹øÂ° ÇàÀÇ PART Ä÷³¿¡´Â ¸ðÀ½ÀÌ µé¾î ÀÖÁö ¾ÊÀ¸¸ç, À̰ÍÀº UDF¿¡¼­ ¿À·ù¸¦ Æ®¸®°ÅÇÏ´Â Á¶°ÇÀÔ´Ï´Ù.

ÀÌ ¿¹¿¡¼­ Àμö ½Â°Ý¿¡ ÁÖ¸ñÇϽʽÿÀ. PART Ä÷³Àº CHAR(5)À̰í, FINDV·Î Àü´ÞµÇ±â À§ÇØ VARCHAR·Î ½Â°ÝµË´Ï´Ù.

¸¶Áö¸·À¸·Î DB2°¡ FINDV¿¡ ´ëÇÑ CREATE¹®¿¡¼­ NOT NULL CALL ½ºÆåÀÇ °á°ú·Î¼­ ³× ¹øÂ° Çà¿¡ ´ëÇÏ¿© FINDV·ÎºÎÅÍ ³Î(NULL) Ãâ·ÂÀ» »ý¼ºÇÏ´Â ¹æ½Ä¿¡ ÁÖ¸ñÇϽʽÿÀ.

´ÙÀ½ ¸í·É¹®Àº

     SELECT SUBSTR(DESCR,1,25), FINDV(CAST (DESCR AS VARCHAR(60) ) )
     FROM TEST

CLP¿¡¼­ ½ÇÇàµÉ ¶§ ´ÙÀ½°ú °°Àº Ãâ·ÂÀ» »ý¼ºÇÕ´Ï´Ù.

     1                              2
     ------------------------- -----------
     The only part of the body           3
     The seat of the emotions?           3
     That bendy place in mid-a           3
     -                                   -
     Unknown.                            1
       5°³ÀÇ ·¹Äڵ尡 ¼±ÅõǾú½À´Ï´Ù.

ÀÌ SELECT¹®¿¡¼­´Â FINDV°¡ VARCHAR ÀÔ·Â Àμö¿¡ ´ëÇÑ ÀÛ¾÷À» ¼öÇàÇÏ´Â °ÍÀ» º¸¿©ÁÝ´Ï´Ù. À̸¦ À§ÇØ Ä÷³ DESCRÀ» VARCHAR·Î À¯Çüº¯È¯ÇÏ´Â ¹æ½ÄÀ» ÁÖÀDZí°Ô »ìÆì º¸½Ê½Ã¿À. À¯Çüº¯È¯À» ÇÏÁö ¾Ê°í´Â CLOB¿¡¼­ FINDV¸¦ »ç¿ëÇÒ ¼ö ¾ø´Âµ¥, ÀÌ´Â CLOB°¡ VARCHAR·Î ½Â°ÝµÇÁö ¾Ê±â ¶§¹®ÀÔ´Ï´Ù. ¿©±â¿¡¼­µµ ³»Àå SUBSTR ÇÔ¼ö°¡ »ç¿ëµÇ¾î DESCR Ä÷³ °ªÀÌ ´õ º¸±âÁÁ°Ô Ç¥½ÃµÇµµ·Ï ÇÕ´Ï´Ù.

¿©±â¿¡¼­µµ NOT NULL CALL·Î ÀÎÇØ ³×¹øÂ° Çà¿¡¼­ FINDV·ÎºÎÅÍÀÇ ³Î(NULL) °á°ú°¡ »ý¼ºµË´Ï´Ù.

¿¹: °è¼ö±â(Counter)

»ç¿ëÀÚÀÇ SELECT¹®¿¡¼­ ´Ü¼øÈ÷ Çà¿¡ ¹øÈ£¸¦ ºÙÀ̰íÀÚ ÇÑ´Ù°í °¡Á¤ÇϽʽÿÀ. »ç¿ëÀÚ´Â °è¼ö±â¸¦ Çϳª¾¿ Áõ°¡½ÃŰ°í ¸®ÅÏÇÏ´Â UDF¸¦ ÀÛ¼ºÇÕ´Ï´Ù. ÀÌ UDF´Â ½ºÅ©·¡Ä¡ ÆÐµå¸¦ »ç¿ëÇÕ´Ï´Ù.

#include <stdlib.h>
#include <string.h>
#include <stdio.h>
#include <sqludf.h>
#include <sqlca.h>
#include <sqlda.h>
 
/* structure scr defines the passed scratchpad for the function "ctr" */
struct scr {
   sqlint32 len;
   sqlint32 countr;
   char not_used[96];
} ;
 
 /********************************************************************
*  function ctr: increments and reports the value from the scratchpad.
*
*         This function does not use the constructs defined in the
*         "sqludf.h" header file.
*
*     input:  NONE
*     output: INTEGER out      the value from the scratchpad
*********************************************************************/
#ifdef __cplusplus
extern "C"
#endif
void SQL_API_FN ctr (
   sqlint32 *out,                           /* output answer (counter) */
   short *outnull,                      /* output NULL indicator */
   char *sqlstate,                      /* SQL STATE */
   char *funcname,                      /* function name */
   char *specname,                      /* specific function name */
   char *mesgtext,                      /* message text insert */
   struct scr *scratchptr) {            /* scratch pad */
 
   *out = ++scratchptr->countr;      /* increment counter & copy out */
   *outnull = 0;
}
/* end of UDF : ctr */

ÀÌ UDFÀÇ °æ¿ì ´ÙÀ½¿¡ ÁÖÀÇÇϽʽÿÀ.

´ÙÀ½Àº ÀÌ UDF¿¡ ´ëÇÑ CREATE FUNCTION¹®ÀÔ´Ï´Ù.

     CREATE FUNCTION COUNTER()
       RETURNS INT
       SCRATCHPAD
       NOT FENCED
       NOT DETERMINISTIC
       NO SQL
       NO EXTERNAL ACTION
       LANGUAGE C
       PARAMETER STYLE DB2SQL
       EXTERNAL NAME 'udf!ctr'
       DISALLOW PARALLELISM;

À̰ÍÀº UDFÀÇ AIX ¹öÀü¿¡ ´ëÇÑ °ÍÀÔ´Ï´Ù. ´Ù¸¥ Ç÷§ÆûÀÇ °æ¿ì EXTERNAL NAMEÀý¿¡ ÁöÁ¤µÈ °ªÀ» ¼öÁ¤ÇØ¾ß ÇÒ °ÍÀÔ´Ï´Ù.

ÀÌ ¸í·É¹®À» ÂüÁ¶Çϸ鼭, ´ÙÀ½¿¡ ÁÖ¸ñÇϽʽÿÀ.

ÀÌÁ¦ ´ÙÀ½ ¸í·É¹®À» ¼º°øÀûÀ¸·Î ¼öÇàÇÒ ¼ö ÀÖ½À´Ï´Ù.

     SELECT INT1, COUNTER(), INT1/COUNTER() FROM TEST

CLP¸¦ ¼öÇàÇÒ ¶§, ´ÙÀ½°ú °°Àº Ãâ·ÂÀÌ »ý¼ºµË´Ï´Ù.

INT1        2           3
----------- ----------- -----------
         16           1          16
          8           2           4
          4           3           1
          2           4           0
         97           5          19
5°³ÀÇ ·¹Äڵ尡 ¼±ÅõǾú½À´Ï´Ù.

µÎ¹øÂ° Ä÷³¿¡ ¿Ã¹Ù¸¥ COUNTER() Ãâ·ÂÀÌ Ç¥½ÃµÇ¾ú½À´Ï´Ù. ¼¼¹øÂ° Ä÷³¿¡¼­´Â SELECT¹®ÀÇ COUNTER()¿¡ ´ëÇÑ µÎ °³ÀÇ º°µµÀÇ ÂüÁ¶°¡ °¢°¢ÀÇ ½ºÅ©·¡Ä¡ ÆÐµå¸¦ °¡Áö°í ÀÖÀ½À» º¸¿©ÁÝ´Ï´Ù. °¢°¢ÀÇ ½ºÅ©·¡Ä¡ ÆÐµå¸¦ °¡Áö°í ÀÖÁö ¾Ê´Ù¸é, µÎ¹øÂ° Ä÷³ÀÇ Ãâ·ÂÀº ¿Ã¹Ù¸£°Ô Á¤·ÄµÈ 1 2 3 4 5 ´ë½Å 1 3 5 7 9°¡ µÉ °ÍÀÔ´Ï´Ù.

¿¹: ³¯¾¾ Å×À̺í ÇÔ¼ö

´ÙÀ½Àº ÇÁ·Î±×·¡¹Ö ¿¹ tblsrv.c¿¡¼­ DB2¿¡ ÀÇÇØ Á¦°øµÇ´Â ¿¹Á¦ Å×À̺í ÇÔ¼ö tfweather_u·Î¼­, ¹Ì±¹ ³» ¿©·¯ µµ½ÃÀÇ ³¯¾¾ Á¤º¸¸¦ ¸®ÅÏÇÕ´Ï´Ù. ÀÌµé µµ½Ã¿¡ ´ëÇÑ ³¯¾¾ Á¤º¸´Â ¿¹Á¦ ÇÁ·Î±×·¥¿¡ Æ÷ÇԵǾî ÀÖÁö¸¸, ¿¹Á¦ ÇÁ·Î±×·¥ ³» ÁÖ¼®¿¡¼­ ¾Ë ¼ö ÀÖµíÀÌ ¿ÜºÎ ÆÄÀϷκÎÅÍ ÀÐ¾î µéÀÏ ¼öµµ ÀÖ½À´Ï´Ù. ÀÌ µ¥ÀÌÅÍ¿¡´Â µµ½Ã À̸§ÀÌ ³ª¿À°í ±× ´ÙÀ½¿¡ ³¯¾¾ Á¤º¸°¡ µÚµû¸¨´Ï´Ù. ÀÌ ÆÐÅÏÀº ´Ù¸¥ µµ½ÃÀÇ °æ¿ì¿¡µµ ¹Ýº¹µË´Ï´Ù. ÀÌ Å×À̺í ÇÔ¼ö¸¦ È£ÃâÇϰí tfweather_u Å×À̺í ÇÔ¼ö¸¦ »ç¿ëÇÏ¿© °Ë»öµÈ ³¯¾¾ µ¥ÀÌÅ͸¦ ÀμâÇÏ´Â, DB2¿Í ÇÔ²² Á¦°øµÇ´Â Ŭ¶óÀÌ¾ðÆ® ÀÀ¿ëÇÁ·Î±×·¥ tblcli.sqc°¡ ÀÖÀ½¿¡ ÁÖÀÇÇϽʽÿÀ.

#include <stdlib.h>
#include <string.h>
#include <stdio.h>
#include <sql.h>
#include <sqludf.h> /* for use in compiling User Defined Function */
 
#define   SQL_NOTNULL   0   /* Nulls Allowed - Value is not Null */
#define   SQL_ISNULL   -1   /* Nulls Allowed - Value is Null */
 
/* Short and long city name structure */
typedef struct {
  char * city_short ;
  char * city_long ;
} city_area ;
 
/* Scratchpad data */
/* Preserve information from one function call to the next call */
typedef struct {
  /* FILE * file_ptr; if you use weather data text file */
  int file_pos ;  /* if you use a weather data buffer */
} scratch_area ;
 
/* Field descriptor structure */
typedef struct {
  char fld_field[31] ;                     /* Field data */
  int  fld_ind ;            /* Field null indicator data */
  int  fld_type ;                          /* Field type */
  int  fld_length ;  /* Field length in the weather data */
  int  fld_offset ;  /* Field offset in the weather data */
} fld_desc ;
 
/* Short and long city name data */
city_area cities[] = {
  { "alb", "Albany, NY"                  },
  { "atl", "Atlanta, GA"                 },
  .
  .
  .
  { "wbc", "Washington DC, DC"           },
  /* You may want to add more cities here */
 
  /* Do not forget a null termination */
  { ( char * ) 0, ( char * ) 0           }
} ;
 
/* Field descriptor data */
fld_desc fields[] = {
  { "", SQL_ISNULL, SQL_TYP_VARCHAR, 30,  0 }, /* city          */
  { "", SQL_ISNULL, SQL_TYP_INTEGER,  3,  2 }, /* temp_in_f     */
  { "", SQL_ISNULL, SQL_TYP_INTEGER,  3,  7 }, /* humidity      */
  { "", SQL_ISNULL, SQL_TYP_VARCHAR,  5, 13 }, /* wind          */
  { "", SQL_ISNULL, SQL_TYP_INTEGER,  3, 19 }, /* wind_velocity */
  { "", SQL_ISNULL, SQL_TYP_FLOAT,    5, 24 }, /* barometer     */
  { "", SQL_ISNULL, SQL_TYP_VARCHAR, 25, 30 }, /* forecast      */
  /* You may want to add more fields here */
 
  /* Do not forget a null termination */
  { ( char ) 0, 0, 0, 0, 0 }
} ;
 
/* Following is the weather data buffer for this example. You */
/* may want to keep the weather data in a separate text file. */
/* Uncomment the following fopen() statement.  Note that you  */
/* have to specify the full path name for this file.          */
char * weather_data[] = {
   "alb.forecast",
   "   34   28%    wnw   3  30.53 clear",
   "atl.forecast",
   "   46   89%   east  11  30.03 fog",
   .
   .
   .
   "wbc.forecast",
   "   38   96%    ene  16  30.31 light rain",
   /* You may want to add more weather data here */
 
   /* Do not forget a null termination */
   ( char * ) 0
} ;
 
#ifdef __cplusplus
extern "C"
#endif
/* This is a subroutine. */
/* Find a full city name using a short name */
int get_name( char * short_name, char * long_name ) {
 
    int name_pos = 0 ;
 
    while ( cities[name_pos].city_short != ( char * ) 0 ) {
       if (strcmp(short_name, cities[name_pos].city_short) == 0) {
          strcpy( long_name, cities[name_pos].city_long ) ;
          /* A full city name found */
   return( 0 ) ;
       }
       name_pos++ ;
    }
    /* Could not find such city in the city data */
    strcpy( long_name, "Unknown City" ) ;
    return( -1 ) ;
 
}
 
#ifdef __cplusplus
extern "C"
#endif
/* This is a subroutine. */
/* Clean all field data and field null indicator data */
int clean_fields( int field_pos ) {
 
    while ( fields[field_pos].fld_length != 0 ) {
       memset( fields[field_pos].fld_field, '\0', 31 ) ;
       fields[field_pos].fld_ind = SQL_ISNULL ;
       field_pos++ ;
    }
   return( 0 ) ;
 
}
 
#ifdef __cplusplus
extern "C"
#endif
/* This is a subroutine. */
/* Fills all field data and field null indicator data ... */
/* ... from text weather data */
int get_value( char * value, int field_pos ) {
 
    fld_desc * field ;
    char field_buf[31] ;
    double * double_ptr ;
    int * int_ptr, buf_pos ;
 
    while ( fields[field_pos].fld_length != 0 ) {
       field = &fields[field_pos] ;
       memset( field_buf, '\0', 31 ) ;
       memcpy( field_buf,
               ( value + field->fld_offset ),
               field->fld_length ) ;
       buf_pos = field->fld_length ;
       while ( ( buf_pos > 0 ) &&
               ( field_buf[buf_pos] == ' ' ) )
          field_buf[buf_pos--] = '\0' ;
       buf_pos = 0 ;
       while ( ( buf_pos < field->fld_length ) &&
               ( field_buf[buf_pos] == ' ' ) )
          buf_pos++ ;
       if ( strlen( ( char * ) ( field_buf + buf_pos ) ) > 0 ||
            strcmp( ( char * ) ( field_buf + buf_pos ), "n/a") != 0 ) {
          field->fld_ind = SQL_NOTNULL ;
 
          /* Text to SQL type conversion */
          switch( field->fld_type ) {
            case SQL_TYP_VARCHAR:
                 strcpy( field->fld_field,
                         ( char * ) ( field_buf + buf_pos ) ) ;
                 break ;
            case SQL_TYP_INTEGER:
                 int_ptr = ( int * ) field->fld_field ;
                 *int_ptr = atoi( ( char * ) ( field_buf + buf_pos ) ) ;
                 break ;
            case SQL_TYP_FLOAT:
                 double_ptr = ( double * ) field->fld_field ;
                 *double_ptr = atof( ( char * ) ( field_buf + buf_pos ) ) ;
                 break ;
            /* You may want to add more text to SQL type conversion here */
          }
 
       }
       field_pos++ ;
    }
   return( 0 ) ;
 
}
 
#ifdef __cplusplus
extern "C"
#endif
void SQL_API_FN weather( /* Return row fields */
              SQLUDF_VARCHAR * city,
              SQLUDF_INTEGER * temp_in_f,
              SQLUDF_INTEGER * humidity,
              SQLUDF_VARCHAR * wind,
              SQLUDF_INTEGER * wind_velocity,
              SQLUDF_DOUBLE  * barometer,
              SQLUDF_VARCHAR * forecast,
              /* You may want to add more fields here */
 
              /* Return row field null indicators */
              SQLUDF_NULLIND * city_ind,
              SQLUDF_NULLIND * temp_in_f_ind,
              SQLUDF_NULLIND * humidity_ind,
              SQLUDF_NULLIND * wind_ind,
              SQLUDF_NULLIND * wind_velocity_ind,
              SQLUDF_NULLIND * barometer_ind,
              SQLUDF_NULLIND * forecast_ind,
              /* You may want to add more field indicators here */
 
              /* UDF always-present (trailing) input arguments */
              SQLUDF_TRAIL_ARGS_ALL
            ) {
 
  scratch_area * save_area ;
  char line_buf[81] ;
  int line_buf_pos ;
 
  /* SQLUDF_SCRAT is part of SQLUDF_TRAIL_ARGS_ALL */
  /* Preserve information from one function call to the next call */
  save_area = ( scratch_area * ) ( SQLUDF_SCRAT->data ) ;
 
  /* SQLUDF_CALLT is part of SQLUDF_TRAIL_ARGS_ALL */
  switch( SQLUDF_CALLT ) {
 
    /* First call UDF: Open table and fetch first row */
    case SQL_TF_OPEN:
         /* If you use a weather data text file specify full path */
         /* save_area->file_ptr = fopen("/sqllib/samples/c/tblsrv.dat",
                                        "r"); */
         save_area->file_pos = 0 ;
         break ;
 
    /* Normal call UDF: Fetch next row */
    case SQL_TF_FETCH:
         /* If you use a weather data text file */
         /* memset(line_buf, '\0', 81); */
         /* if (fgets(line_buf, 80, save_area->file_ptr) == NULL) { */
         if ( weather_data[save_area->file_pos] == ( char * ) 0 ) {
 
            /* SQLUDF_STATE is part of SQLUDF_TRAIL_ARGS_ALL */
            strcpy( SQLUDF_STATE, "02000" ) ;
 
            break ;
         }
         memset( line_buf, '\0', 81 ) ;
         strcpy( line_buf, weather_data[save_area->file_pos] ) ;
         line_buf[3] = '\0' ;
 
         /* Clean all field data and field null indicator data */
         clean_fields( 0 ) ;
 
         /* Fills city field null indicator data */
         fields[0].fld_ind = SQL_NOTNULL ;
 
         /* Find a full city name using a short name */
         /* Fills city field data */
         if ( get_name( line_buf, fields[0].fld_field ) == 0 ) {
            save_area->file_pos++ ;
            /* If you use a weather data text file */
            /* memset(line_buf, '\0', 81); */
            /* if (fgets(line_buf, 80, save_area->file_ptr) == NULL) { */
            if ( weather_data[save_area->file_pos] == ( char * ) 0 ) {
               /* SQLUDF_STATE is part of SQLUDF_TRAIL_ARGS_ALL */
               strcpy( SQLUDF_STATE, "02000" ) ;
               break ;
            }
            memset( line_buf, '\0', 81 ) ;
            strcpy( line_buf, weather_data[save_area->file_pos] ) ;
            line_buf_pos = strlen( line_buf ) ;
            while ( line_buf_pos > 0 ) {
               if ( line_buf[line_buf_pos] >= ' ' )
                  line_buf_pos = 0 ;
           else
                  line_buf[line_buf_pos] = '\0' ;
                  line_buf_pos-- ;
               }
            }
         }
 
         /* Fills field data and field null indicator data ... */
         /* ... for selected city from text weather data */
         get_value( line_buf, 1 ) ;  /* Skips city field */
 
         /* Builds return row fields */
         strcpy( city, fields[0].fld_field ) ;
         memcpy( (void *) temp_in_f,
                 fields[1].fld_field,
                 sizeof( SQLUDF_INTEGER ) ) ;
         memcpy( (void *) humidity,
                 fields[2].fld_field,
                 sizeof( SQLUDF_INTEGER ) ) ;
         strcpy( wind, fields[3].fld_field ) ;
         memcpy( (void *) wind_velocity,
                 fields[4].fld_field,
                 sizeof( SQLUDF_INTEGER ) ) ;
         memcpy( (void *) barometer,
                 fields[5].fld_field,
                 sizeof( SQLUDF_DOUBLE ) ) ;
         strcpy( forecast, fields[6].fld_field ) ;
 
         /* Builds return row field null indicators */
         memcpy( (void *) city_ind,
                 &(fields[0].fld_ind),
                 sizeof( SQLUDF_NULLIND ) ) ;
         memcpy( (void *) temp_in_f_ind,
                 &(fields[1].fld_ind),
                 sizeof( SQLUDF_NULLIND ) ) ;
         memcpy( (void *) humidity_ind,
                 &(fields[2].fld_ind),
                 sizeof( SQLUDF_NULLIND ) ) ;
         memcpy( (void *) wind_ind,
                 &(fields[3].fld_ind),
                 sizeof( SQLUDF_NULLIND ) ) ;
         memcpy( (void *) wind_velocity_ind,
                 &(fields[4].fld_ind),
                 sizeof( SQLUDF_NULLIND ) ) ;
         memcpy( (void *) barometer_ind,
                 &(fields[5].fld_ind),
                 sizeof( SQLUDF_NULLIND ) ) ;
         memcpy( (void *) forecast_ind,
                 &(fields[6].fld_ind),
                 sizeof( SQLUDF_NULLIND ) ) ;
 
         /* Next city weather data */
         save_area->file_pos++ ;
 
         break ;
 
    /* Special last call UDF for cleanup (no real args!): Close table */
    case SQL_TF_CLOSE:
         /* If you use a weather data text file */
         /* fclose(save_area->file_ptr); */
         /* save_area->file_ptr = NULL; */
         save_area->file_pos = 0 ;
         break ;
 
  }
 
}

ÀÌ UDF Äڵ带 ÂüÁ¶Çϸ鼭 ´ÙÀ½À» ÁÖÀDZí°Ô »ìÆì º¸½Ê½Ã¿À.

´ÙÀ½Àº ÀÌ UDF¿¡ ´ëÇÑ CREATE FUNCTION¹®ÀÔ´Ï´Ù.

     CREATE FUNCTION tfweather_u()
       RETURNS TABLE (CITY VARCHAR(25),
                      TEMP_IN_F INTEGER,
                      HUMIDITY INTEGER,
                      WIND VARCHAR(5),
                      WIND_VELOCITY INTEGER,
                      BAROMETER FLOAT,
                      FORECAST VARCHAR(25))
       SPECIFIC tfweather_u
       DISALLOW PARALLELISM
       NOT FENCED
   DETERMINISTIC
         NO SQL
   NO EXTERNAL ACTION
         SCRATCHPAD
       NO FINAL CALL
       LANGUAGE C
         PARAMETER STYLE DB2SQL
       EXTERNAL NAME 'tf_dml!weather';

À§ÀÇ CREATE FUNCTION¹®Àº UNIX ¹öÀüÀÇ UDF¿¡ ´ëÇÑ °ÍÀÔ´Ï´Ù. ´Ù¸¥ Ç÷§ÆûÀÇ °æ¿ì EXTERNAL NAMEÀý¿¡ ÁöÁ¤µÈ °ªÀ» ¼öÁ¤ÇØ¾ß ÇÒ °ÍÀÔ´Ï´Ù.

ÀÌ ¸í·É¹®À» ÂüÁ¶Çϸ鼭, ´ÙÀ½¿¡ ÁÖ¸ñÇϽʽÿÀ.

¿¹: LOB À§Ä¡ ÁöÁ¤ÀÚ¸¦ »ç¿ëÇÏ´Â ÇÔ¼ö

ÀÌ UDF´Â ÀÔ·Â LOB·Î À§Ä¡ ÁöÁ¤ÀÚ¸¦ ¹Þ¾ÆµéÀ̰í ÀÔ·Â LOBÀÇ ºÎ¼Ó ÁýÇÕÀÎ ´Ù¸¥ LOB¿¡ ´ëÇÑ À§Ä¡ ÁöÁ¤ÀÚ¸¦ ¸®ÅÏÇÕ´Ï´Ù. µÎ¹øÂ° ÀÔ·Â °ªÀ¸·Î ´Ù¸¥ ±âÁØÀÌ Àü´ÞµÇ´Âµ¥, À̰ÍÀº UDF¿¡°Ô ÀÔ·Â LOB¸¦ Á¤È®ÇÏ°Ô ³ª´©´Â ¹æ¹ýÀ» ¾Ë·ÁÁÝ´Ï´Ù.

#include <stdlib.h>
#include <string.h>
#include <stdio.h>
#include <sql.h>
#include <sqlca.h>
#include <sqlda.h>
#include <sqludf.h>
#include "util.h"
 
void SQL_API_FN lob_subsetter(
          udf_locator * lob_input,   /* locator of LOB value to carve up */
          char     * criteria,          /* criteria for carving */
          udf_locator    * lob_output,  /* locator of result LOB value */
          sqlint16 * inp_nul,
          sqlint16 * cri_nul,
          sqlint16 * out_nul,
          char     * sqlstate,
          char     * funcname,
          char     * specname,
          char     * msgtext ) {
 
       /* local vars */
       short j;              /* local indexing var */
       int   rc;             /* return code variable for API calls */
       sqlint32  input_len;  /* receiver for input LOB length */
       sqlint32  input_pos;  /* current position for scanning input LOB */
       char lob_buf[100];    /* data buffer */
       sqlint32  input_rec;  /* number of bytes read by sqludf_substr */
       sqlint32  output_rec; /* number of bytes written by sqludf_append */
 
       /*---------------------------------------------
        * UDF Program Logic Starts Here
        *---------------------------------------------
        * What we do is create an output handle, and then
        * loop over the input, 100 bytes at a time.
        * Depending on the "criteria" passed in, we may decide
        * to append the 100 byte input lob segment to the output, or not.
        *---------------------------------------------
        * Create the output locator, right in the return buffer.
        */
 
       rc = sqludf_create_locator(SQL_TYP_CLOB, &lob_output);
       /* Error and exit if unable to create locator */
       if (rc) {
          memcpy (sqlstate, "38901", 5);
          /* special sqlstate for this condition */
          goto exit;
       }
       /* Find out the size of the input LOB value */
       rc = sqludf_length(lob_input, &input_len) ;
       /* Error and exit if unable to find out length */
       if (rc) {
          memcpy (sqlstate, "38902", 5);
          /* special sqlstate for this condition */
          goto exit;
       }
       /* Loop to read next 100 bytes, and append to result if it meets
        * the criteria.
        */
       for (input_pos = 0; (input_pos < input_len); input_pos += 100) {
         /* Read the next 100 (or less) bytes of the input LOB value */
         rc = sqludf_substr(lob_input, input_pos, 100,
                            (unsigned char *) lob_buf, &input_rec) ;
         /* Error and exit if unable to read the segment */
         if (rc) {
            memcpy (sqlstate, "38903", 5);
            /* special sqlstate for this condition */
            goto exit;
         }
         /* apply the criteria for appending this segment to result
          * if (...predicate involving buffer and criteria...) {
          * The condition for retaining the segment is TRUE...
          * Write that buffer segment which was last read in
          */
         rc = sqludf_append(lob_output,
                    (unsigned char *) lob_buf, input_rec, &output_rec) ;
         /* Error and exit if unable to read the 100 byte segment */
         if (rc) {
            memcpy (sqlstate, "38904", 5);
            /* special sqlstate for this condition */
            goto exit;
         }
         /* } end if criteria for inclusion met */
       } /* end of for loop, processing 100-byte chunks of input LOB
         * if we fall out of for loop, we are successful, and done.
		 */
	   *out_nul = 0;
	exit: /* used for errors, which will override null-ness of output. */
	   return;
	}

ÀÌ UDF Äڵ带 ÂüÁ¶Çϸ鼭 ´ÙÀ½À» ÁÖÀDZí°Ô »ìÆì º¸½Ê½Ã¿À.

´ÙÀ½Àº ÀÌ UDF¿¡ ´ëÇÑ CREATE FUNCTION¹®ÀÔ´Ï´Ù.

     CREATE FUNCTION carve(CLOB(50M), VARCHAR(255) )
       RETURNS CLOB(50M)
       NOT NULL CALL
       NOT FENCED
   DETERMINISTIC
         NO SQL
   NO EXTERNAL ACTION
       LANGUAGE C
         PARAMETER STYLE DB2SQL
       EXTERNAL NAME '/u/wilfred/udfs/lobudfs!lob_subsetter' ;

À̰ÍÀº UDFÀÇ AIX ¹öÀü¿¡ ´ëÇÑ °ÍÀÔ´Ï´Ù. ´Ù¸¥ Ç÷§ÆûÀÇ °æ¿ì EXTERNAL NAMEÀý¿¡ ÁöÁ¤µÈ °ªÀ» ¼öÁ¤ÇØ¾ß ÇÒ °ÍÀÔ´Ï´Ù.

ÀÌ ¸í·É¹®À» ÂüÁ¶Çϸ鼭, ´ÙÀ½¿¡ ÁÖ¸ñÇϽʽÿÀ.

ÀÌÁ¦ ´ÙÀ½ ¸í·É¹®À» ¼º°øÀûÀ¸·Î ¼öÇàÇÒ ¼ö ÀÖ½À´Ï´Ù.

     UPDATE tablex
       SET col_a = 99,
           col_b = carve (:hv_clob, '...criteria...')
       WHERE tablex_key = :hv_key;

ÀÌ UDF´Â È£½ºÆ® º¯¼ö :hv_clobÀ¸·Î Ç¥½ÃµÇ´Â CLOB °ªÀ» ºÎ¼Ó ÁýÇÕÀ¸·Î ¸¸µé°í, È£½ºÆ® º¯¼ö :hv_keyÀÇ Å° °ªÀ¸·Î Ç¥½ÃµÇ´Â ÇàÀ» °»½ÅÇÏ´Â µ¥ »ç¿ëµË´Ï´Ù.

ÀÌ °»½Å ¿¹Á¦¿¡¼­, :hv_clob´Â ÀÀ¿ëÇÁ·Î±×·¥¿¡¼­ CLOB_LOCATOR·Î Á¤Àǵ˴ϴÙ. À̰ÍÀº "Ä«ºê" UDF·Î Àü´ÞµÇ´Â À§Ä¡ ÁöÁ¤ÀÚ¿Í °°Àº °ªÀÌ ¾Æ´Õ´Ï´Ù. :hv_clob°¡ ¸í·É¹®À» ¼öÇàÇÏ´Â DB2 ¿£Áø ¿¡ÀÌÀüÆ®¿¡ "¹ÙÀεå"µÉ ¶§, À̰ÍÀº CLOB·Î¸¸ ¾Ë·ÁÁý´Ï´Ù. ±×¸®°í³ª¼­ UDF·Î Àü´ÞµÉ ¶§, DB2´Â ÀÌ °ª¿¡ ´ëÇÑ »õ·Î¿î À§Ä¡ ÁöÁ¤ÀÚ¸¦ »ý¼ºÇÕ´Ï´Ù. ÀÌ·¯ÇÑ CLOB°ú À§Ä¡ ÁöÁ¤ÀÚ »çÀÌÀÇ º¯È¯Àº ºñ¿ëÀÌ ¸¹ÀÌ ¼Ò¸ðµÇ´Â °ÍÀÌ ¾Æ´Õ´Ï´Ù. ÇÊ¿ä ÀÌ»óÀÇ ¸Þ¸ð¸® »çº»À̳ª ÀÔÃâ·ÂÀ» ÇÊ¿ä·Î ÇÏÁö ¾Ê½À´Ï´Ù.

¿¹: BASICÀ¸·Î ÀÛ¼ºÇÑ °è¼ö±â(Counter) OLE ÀÚµ¿ UDF

´ÙÀ½ÀÇ ¿¹¿¡¼­´Â Microsoft Visual BASICÀ» »ç¿ëÇÏ¿© °è¼ö±â Ŭ·¡½º¸¦ ±¸ÇöÇÕ´Ï´Ù. ÀÌ Å¬·¡½º¿¡´Â È£Ãâ Ƚ¼ö¸¦ ÃßÀûÇÏ´Â ÀνºÅϽº º¯¼ö nbrOfInvoke°¡ ÀÖ½À´Ï´Ù. Ŭ·¡½º ±¸¼ºÀÚ´Â ÀÌ ¼ýÀÚ¸¦ 0À¸·Î ÃʱâÈ­ÇÕ´Ï´Ù. Increment ¸Þ¼Òµå°¡ nbrOfInvoke¸¦ 1¾¿ Áõ°¡½Ã۰í ÇöÀç »óŸ¦ ¸®ÅÏÇÕ´Ï´Ù.

      Description="Example in SQL Reference"
      Name="bert"
      Class=bcounter; bcounter.cls
      ExeName32="bert_app.exe"
 
 
 VERSION 1.0 CLASS
 BEGIN
        SingleUse = -1  'True
 END
      Attribute VB_Name = "bcounter"
 Attribute VB_Creatable = True
 Attribute VB_Exposed = True
 Option Explicit
      Dim nbrOfInvoke As Long
 
      Public Sub increment(output As Long, _
                           output_ind As Integer, _
                           sqlstate As String, _
                           fname As String, _
                           fspecname As String, _
                           msg As String, _
                           scratchpad() As Byte, _
                           calltype As Long)
 
          nbrOfInvoke = nbrOfInvoke + 1
 
 End Sub
 
      Private Sub Class_Initialize()
        nbrOfInvoke = 0
 End Sub
 
      Private Sub Class_Terminate()
 
 End Sub

bcounter Ŭ·¡½º´Â OLE ÀÚµ¿ ¿ÀºêÁ§Æ®·Î¼­ ±¸ÇöµÇ°í, progId bert.bcounter ¾Æ·¡¿¡ µî·ÏµË´Ï´Ù. ÀÚµ¿ ¼­¹ö¸¦ ÇÁ·Î¼¼½º ³»ºÎ ÇÁ·Î¼¼½º ¶Ç´Â Áö¿ª ¼­¹ö Áß Çϳª·Î ÄÄÆÄÀÏÇÒ ¼ö ÀÖ½À´Ï´Ù. À̰ÍÀº DB2¿¡ Åõ¸íÇÕ´Ï´Ù. ´ÙÀ½ÀÇ CREATE FUNCTION¹®Àº ¿ÜºÎ ±¸ÇöÀ¸·Î increment ¸Þ¼Òµå¸¦ °®´Â UDF bcounter¸¦ µî·ÏÇÕ´Ï´Ù.

     CREATE FUNCTION bcounter () RETURNS integer
        EXTERNAL NAME 'bert.bcounter!increment'
         LANGUAGE OLE
         FENCED
         SCRATCHPAD
         FINAL CALL
         NOT DETERMINISTIC
         NULL CALL
         PARAMETER STYLE DB2SQL
         NO SQL
   NO EXTERNAL ACTION
         DISALLOW PARALLEL;

´ÙÀ½°ú °°Àº Á¶È¸ÀÇ °æ¿ì,

     SELECT INT1, BCOUNTER() AS COUNT, INT1/BCOUNTER() AS DIV FROM TEST

°á°ú´Â ÀÌÀü ¿¹¿¡¼­¿Í Á¤È®È÷ ¶È°°½À´Ï´Ù.

     INT1        COUNT       DIV
     ----------- ----------- -----------
              16           1          16
               8           2           4
               4           3           1
               2           4           0
              97           5          19
 
       5°³ÀÇ ·¹Äڵ尡 ¼±ÅõǾú½À´Ï´Ù.

¿¹: C++·Î ÀÛ¼ºÇÑ °è¼ö±â(Counter) OLE ÀÚµ¿ UDF

´ÙÀ½ÀÇ ¿¹¿¡¼­´Â ¾ÕÀÇ BASIC °è¼ö±â Ŭ·¡½º¸¦ C++·Î ±¸ÇöÇÕ´Ï´Ù. ÄÚµåÀÇ ÀϺθ¸ÀÌ ¿©±â¿¡ ³ª¿Í ÀÖÀ¸¸ç, Àüü »ùÇÃÀº /sqllib/samples/ole µð·ºÅ丮¿¡ ÀÖ½À´Ï´Ù.

Increment ¸Þ¼Òµå´Â °è¼ö±â ÀÎÅÍÆäÀ̽º ¼³¸íÀÇ ÀϺηΠ¿ÀºêÁ§Æ® ¼³¸í ¾ð¾î¿¡¼­ ¼³¸íÇÕ´Ï´Ù.

     interface ICounter : IDispatch
     {
         ...
         HRESULT increment([out] long    *out,
                           [out] short   *outnull,
                           [out] BSTR    *sqlstate,
                           [in]  BSTR    *fname,
                           [in]  BSTR    *fspecname,
                           [out] BSTR    *msgtext,
                           [in,out] SAFEARRAY (unsigned char) *spad,
                           [in]  long    *calltype);
         ...
     }

C++ÀÇ COM CCounter Ŭ·¡½º Á¤ÀÇ¿¡´Â nbrOfInvoke¿Í ÇÔ²² Increment ¸Þ¼Òµå ¼±¾ðµµ Æ÷ÇԵ˴ϴÙ.

     class FAR CCounter : public ICounter
     {
             ...
     STDMETHODIMP CCounter::increment(long    *out,
                                      short   *outnull,
                                      BSTR    *sqlstate,
                                      BSTR    *fname,
                                      BSTR    *fspecname,
                                      BSTR    *msgtext,
                                      SAFEARRAY **spad,
                                              long *calltype );
             long nbrOfInvoke;
             ...
     };

¸Þ¼ÒµåÀÇ C++ ±¸ÇöÀº BASIC ÄÚµå¿Í ºñ½ÁÇÕ´Ï´Ù.

     STDMETHODIMP CCounter::increment(long    *out,
                                      short   *outnull,
                                      BSTR    *sqlstate,
                                      BSTR    *fname,
                                      BSTR    *fspecname,
                                      BSTR    *msgtext,
                                      SAFEARRAY **spad,
                                      long *calltype)
     {
 
       nbrOfInvoke = nbrOfInvoke + 1;
       *out = nbrOfInvoke;
 
       return NOERROR;
     };

À§ÀÇ ¿¹¿¡¼­ sqlstate ¹× msgtext´Â BSTR* À¯ÇüÀÇ [out] ¸Å°³º¯¼öÀÔ´Ï´Ù. Áï DB2´Â NULL¿¡ ´ëÇÑ Æ÷ÀÎÅ͸¦ UDF·Î Àü´ÞÇÕ´Ï´Ù. ÀÌµé ¸Å°³º¯¼ö °ªÀ» ¸®ÅÏÇϱâ À§ÇØ UDF´Â ¹®ÀÚ¿­À» ÇÒ´çÇϰí À̸¦ DB2·Î ¸®ÅÏÇϸç(¿¹¸¦ µé¾î *sqlstate = SysAllocString (L"01H00")), DB2´Â ¸Þ¸ð¸®¸¦ ÇØÁ¦ÇÕ´Ï´Ù. fname¿Í fspecname ¸Å°³º¯¼ö´Â [in] ¸Å°³º¯¼öÀÔ´Ï´Ù. DB2´Â ¸Þ¸ð¸®¸¦ ÇÒ´çÇϰí UDF¿¡ ÀÇÇØ ÀÐÈù °ªÀ» Àü´ÞÇϰí, DB2´Â ¸Þ¸ð¸®¸¦ ÇØÁ¦ÇÕ´Ï´Ù.

CCounter Ŭ·¡½ºÀÇ factory Ŭ·¡½º´Â °è¼ö±â ¿ÀºêÁ§Æ®¸¦ ÀÛ¼ºÇÕ´Ï´Ù. factory Ŭ·¡½º¸¦ ´ÜÀÏ »ç¿ë ¶Ç´Â º¹¼ö »ç¿ë ¿ÀºêÁ§Æ®·Î µî·ÏÇÒ ¼ö ÀÖ½À´Ï´Ù(ÀÌ ¿¹¿¡¼­´Â ³ª¿ÀÁö ¾ÊÀ½).

     STDMETHODIMP CCounterCF::CreateInstance(IUnknown FAR* punkOuter,
                                             REFIID riid,
                                             void FAR* FAR* ppv)
     {
 
       CCounter *pObj;
             ...
       // create a new counter object
       pObj = new CCounter;
             ...
     };

CCounter Ŭ·¡½º´Â Áö¿ª ¼­¹ö·Î ±¸ÇöµÇ¾úÀ¸¸ç, À̰ÍÀº progId bert.ccounter ¾Æ·¡¿¡ µî·ÏµÇ¾ú½À´Ï´Ù. ´ÙÀ½ÀÇ CREATE FUNCTION¹®Àº ¿ÜºÎ ±¸ÇöÀ¸·Î Increment ¸Þ¼Òµå¸¦ °®´Â UDF ccounter¸¦ µî·ÏÇÕ´Ï´Ù.

    CREATE FUNCTION ccounter () RETURNS integer
      EXTERNAL NAME 'bert.ccounter!increment'
         LANGUAGE OLE
         FENCED
         SCRATCHPAD
         FINAL CALL
         NOT DETERMINISTIC
         NULL CALL
         PARAMETER STYLE DB2SQL
         NO SQL
   NO EXTERNAL ACTION
         DISALLOW PARALLEL;

´ÙÀ½ Á¶È¸¸¦ ó¸®ÇÏ´Â µ¿¾È DB2´Â Ŭ·¡½º CCounterÀÇ ¼­·Î ´Ù¸¥ µÎ °³ÀÇ ÀνºÅϽº¸¦ ÀÛ¼ºÇÕ´Ï´Ù. Á¶È¸¿¡¼­ °¢ UDF ÂüÁ¶¿¡ ´ëÇÏ¿© ÇϳªÀÇ ÀνºÅϽº°¡ ÀÛ¼ºµË´Ï´Ù. ccounter UDF µî·Ï¿¡ ½ºÅ©·¡Ä¡ ÆÐµå ¿É¼ÇÀÌ ÁöÁ¤µÇ¹Ç·Î, µÎ °³ÀÇ ÀνºÅϽº°¡ Àüü Á¶È¸¿¡ Àç»ç¿ëµË´Ï´Ù.

     SELECT INT1, CCOUNTER() AS COUNT, INT1/CCOUNTER() AS DIV FROM TEST

°á°ú´Â ÀÌÀü ¿¹¿¡¼­¿Í Á¤È®È÷ ¶È°°½À´Ï´Ù.

     INT1        COUNT       DIV
     ----------- ----------- -----------
              16           1          16
               8           2           4
               4           3           1
               2           4           0
              97           5          19
 
       5°³ÀÇ ·¹Äڵ尡 ¼±ÅõǾú½À´Ï´Ù.

¿¹: BASICÀ¸·Î ÀÛ¼ºÇÑ ¸ÞÀÏ OLE ÀÚµ¿ Å×À̺í ÇÔ¼ö

´ÙÀ½ÀÇ ¿¹¿¡¼­´Â Microsoft ExchangeÀÇ ¸Þ½ÃÁö Çì´õ Á¤º¸¿Í ºÎºÐÀûÀÎ ¸Þ½ÃÁö ÅØ½ºÆ®¸¦ °Ë»öÇϱâ À§ÇØ °ø¿ë ¸Þ¼Òµå list¸¦ °ø°³ÇÏ´Â Microsoft Visual BASICÀ» »ç¿ëÇϴ Ŭ·¡½º¸¦ ±¸ÇöÇÕ´Ï´Ù. ¸Þ¼Òµå ±¸Çö½Ã MAPI(¸Þ½Ã¡ API)¿¡ ´ëÇÑ OLE ÀÚµ¿ ÀÎÅÍÆäÀ̽º¸¦ Á¦°øÇÏ´Â OLE ¸Þ½Ã¡À» »ç¿ëÇÕ´Ï´Ù.

 Description="Mail OLE Automation Table Function"
 Module=MainModule; MainModule.bas
 Class=Header; Header.cls
 ExeName32="tfmapi.dll"
 Name="TFMAIL"
 
 VERSION 1.0 CLASS
 BEGIN
   MultiUse = -1  'True
 END
 Attribute VB_Name = "Header"
 Attribute VB_Creatable = True
 Attribute VB_Exposed = True
 Option Explicit
 
 Dim MySession As Object
 Dim MyMsgColl As Object
 Dim MyMsg As Object
 Dim CurrentSender As Object
 Dim name As Variant
 Const SQL_TF_OPEN = -1
 Const SQL_TF_CLOSE = 1
 Const SQL_TF_FETCH = 0
 
 
 Public Sub List(timereceived As Date, subject As String, size As Long, _
                 text As String, ind1 As Integer, ind2 As Integer, _
                 ind3 As Integer, ind4 As Integer, sqlstate As String, _
                 fname As String, fspecname As String, msg As String, _
                 scratchpad() As Byte, calltype As Long)
 
 
     If (calltype = SQL_TF_OPEN) Then
 
        Set MySession = CreateObject("MAPI.Session")
 
        MySession.Logon ProfileName:="Profile1"
        Set MyMsgColl = MySession.Inbox.Messages
 
        Set MyMsg = MyMsgColl.GetFirst
 
     ElseIf (calltype = SQL_TF_CLOSE) Then
 
        MySession.Logoff
        Set MySession = Nothing
 
       Else
 
       If (MyMsg Is Nothing) Then
 
          sqlstate = "02000"
 
       Else
 
          timereceived = MyMsg.timereceived
          subject = Left(MyMsg.subject, 15)
          size = MyMsg.size
          text = Left(MyMsg.text, 30)
 
          Set MyMsg = MyMsgColl.GetNext
 
     End If
     End If
 End Sub

Å×À̺í ÇÔ¼ö OPEN È£Ãâ¿¡¼­ CreateObject¹®Àº ¸ÞÀÏ ¼¼¼ÇÀ» ÀÛ¼ºÇϰí, logon ¸Þ¼Òµå´Â ¸ÞÀÏ ½Ã½ºÅÛÀ¸·Î ·Î±×¿ÂÇÕ´Ï´Ù(»ç¿ëÀÚ À̸§°ú ¾ÏÈ£ ¹ßÇàÀº ¹«½ÃµÊ). ¸ÞÀÏ ¼ö½Å »óÀÚÀÇ ¸Þ½ÃÁö ÄÝ·º¼ÇÀº ù¹øÂ° ¸Þ½ÃÁö¸¦ °Ë»öÇϴµ¥ »ç¿ëµË´Ï´Ù. FETCH È£Ãâ¿¡¼­ ¸Þ½ÃÁö Çì´õ Á¤º¸¿Í ÇöÀç ¸Þ½ÃÁöÀÇ Ã³À½ 30ÀÚ°¡ Å×À̺í ÇÔ¼ö Ãâ·Â ¸Å°³º¯¼ö¿¡ ÁöÁ¤µË´Ï´Ù. ¾Æ¹«·± ¸Þ½ÃÁöµµ ³²¾Æ ÀÖÁö ¾ÊÀº °æ¿ì, SQLSTATE 02000ÀÌ ¸®Åϵ˴ϴÙ. CLOSE È£Ãâ¿¡¼­, ÀÌ ¿¹´Â ·Î±× ¿ÀÇÁÇÏ°í ¼¼¼Ç ¿ÀºêÁ§Æ®¿¡ ¾Æ¹«°Íµµ ¼³Á¤ÇÏÁö ¾Ê´Âµ¥, À̰ÍÀº ´Ù¸¥ º¯¼ö°¡ À̰ÍÀ» ÂüÁ¶ÇÏÁö ¾Ê´Â ÇÑ ÀÌÀü¿¡ ÂüÁ¶µÈ ¿ÀºêÁ§Æ®¿Í ¿¬°üµÈ ½Ã½ºÅÛ ¹× ¸Þ¸ð¸® ÀÚ¿øÀ» ¸ðµÎ ¸±¸®½ºÇÏ´Â °ÍÀÔ´Ï´Ù.

´ÙÀ½Àº ÀÌ UDF¿¡ ´ëÇÑ CREATE FUNCTION¹®ÀÔ´Ï´Ù.

     CREATE FUNCTION MAIL()
         RETURNS TABLE (TIMERECIEVED DATE,
                        SUBJECT VARCHAR(15),
                        SIZE INTEGER,
                        TEXT VARCHAR(30))
         EXTERNAL NAME 'tfmail.header!list'
         LANGUAGE OLE
         PARAMETER STYLE DB2SQL
         NOT DETERMINISTIC
         FENCED
         NULL CALL
         SCRATCHPAD
         FINAL CALL
         NO SQL
         EXTERNAL ACTION
         DISALLOW PARALLEL;

´ÙÀ½Àº »ùÇà Á¶È¸ÀÔ´Ï´Ù.

     SELECT * FROM TABLE (MAIL()) AS M
 
     TIMERECEIVED SUBJECT         SIZE        TEXT
     ------------ --------------- ----------- ------------------------------
     01/18/1997   Welcome!               3277 Welcome to Windows Messaging!
     01/18/1997   Invoice                1382 Please process this invoice. T
     01/19/1997   Congratulations        1394 Congratulations to the purchas
 
       3°³ÀÇ ·¹Äڵ尡 ¼±ÅõǾú½À´Ï´Ù.


[ ÆäÀÌÁöÀÇ ¸Ç À§ | ÀÌÀü ÆäÀÌÁö | ´ÙÀ½ ÆäÀÌÁö | ¸ñÂ÷ | »öÀÎ ]