´ÙÀ½°ú °°Àº ¿¹Á¦ UDF Äڵ尡 DB2¿¡¼ Á¦°øµË´Ï´Ù.
Á¦°øµÇ´Â ¸ðµç ¿¹µéÀÌ ÀÖ´Â À§Ä¡¿Í À̵éÀ» È£ÃâÇÏ´Â ¹æ¹ýÀº ºÎ·Ï 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ÀÇ °æ¿ì ´ÙÀ½¿¡ ÁÖÀÇÇϽʽÿÀ.
#include <sqlsystm.h>
´ÙÀ½Àº ÀÌ 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°³ÀÇ ·¹Äڵ尡 ¼±ÅõǾú½À´Ï´Ù.
À§ÀÇ ¿¹¿¡¼ ´ÙÀ½¿¡ ÁÖ¸ñÇϽʽÿÀ.
ÀÌ·¯ÇÑ °æ¿ì, ¿ÏÀüÈ÷ ÀϹÝÀûÀÎ ÇÔ¼ö ¼¼Æ®¿¡ ´ëÇØ Á¤¼ö ³ª´©±â¸¦ ¿Ïº®ÇÏ°Ô Ã³¸®Çϱâ À§ÇØ ´ÙÀ½°ú °°Àº ¼¼ °³ÀÇ Ãß°¡ÀûÀÎ ÇÔ¼ö¸¦ CREATEÇØ¾ß ÇÕ´Ï´Ù.
CREATE FUNCTION MATH."/"(SMALLINT,SMALLINT) RETURNS INT SOURCE MATH."/"(INT,INT) CREATE FUNCTION MATH."/"(SMALLINT,INT) RETURNS INT SOURCE MATH."/"(INT,INT) CREATE FUNCTION MATH."/"(INT,SMALLINT) RETURNS INT SOURCE MATH."/"(INT,INT)
¼¼ °³ÀÇ UDF°¡ Ãß°¡µÇ¾îµµ Ãß°¡ÀûÀÎ ÄÚµå´Â MATH."/"ÀÔ´Ï´Ù.
ÀÌÁ¦ ÀÌ·¯ÇÑ ³× °³ÀÇ "/" ÇÔ¼ö Á¤ÀǸ¦ »ç¿ëÇÏ¿© Á¤¼ö ³ª´©±âÀÇ »õ·Î¿î ±â´ÉÀ» Ȱ¿ëÇÏ·Á¸é, ¾ðÁ¦¶óµµ ÇÔ¼ö °æ·Î¿¡¼ SYSIBM ¾Õ¿¡ MATH¸¦ ³õ°í SQLÀ» ÀÛ¼ºÇϱ⸸ ÇÏ¸é µË´Ï´Ù.
¾ÕÀÇ ¿¹¿¡¼´Â BIGINT µ¥ÀÌÅÍ À¯ÇüÀ» °í·ÁÇÏÁö ¾Ê¾ÒÁö¸¸, ÀÌ ¿¹¸¦ È®Àå½ÃÄÑ BIGINT¸¦ ½±°Ô Æ÷ÇÔ½Ãų ¼ö ÀÖ½À´Ï´Ù.
»ç¿ëÀÚÀÇ ÅØ½ºÆ® ó¸® ÀÀ¿ëÇÁ·Î±×·¥À» º¸Á¶Çϱâ À§ÇÑ µÎ °³ÀÇ 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) °á°ú°¡ »ý¼ºµË´Ï´Ù.
»ç¿ëÀÚÀÇ 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Àý¿¡ ÁöÁ¤µÈ °ªÀ» ¼öÁ¤ÇØ¾ß ÇÒ °ÍÀÔ´Ï´Ù.
ÀÌ ¸í·É¹®À» ÂüÁ¶Çϸé¼, ´ÙÀ½¿¡ ÁÖ¸ñÇϽʽÿÀ.
ÀÌ 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°ú À§Ä¡ ÁöÁ¤ÀÚ »çÀÌÀÇ º¯È¯Àº ºñ¿ëÀÌ ¸¹ÀÌ ¼Ò¸ðµÇ´Â °ÍÀÌ ¾Æ´Õ´Ï´Ù. ÇÊ¿ä ÀÌ»óÀÇ ¸Þ¸ð¸® »çº»À̳ª ÀÔÃâ·ÂÀ» ÇÊ¿ä·Î ÇÏÁö ¾Ê½À´Ï´Ù.
´ÙÀ½ÀÇ ¿¹¿¡¼´Â 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°³ÀÇ ·¹Äڵ尡 ¼±ÅõǾú½À´Ï´Ù.
´ÙÀ½ÀÇ ¿¹¿¡¼´Â ¾ÕÀÇ 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°³ÀÇ ·¹Äڵ尡 ¼±ÅõǾú½À´Ï´Ù.
´ÙÀ½ÀÇ ¿¹¿¡¼´Â 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°³ÀÇ ·¹Äڵ尡 ¼±ÅõǾú½À´Ï´Ù.