DB2 함수 모음

db2 함수 정리

=> 기본정보보기
DESCRIBE TABLE table-name [show detail]
DESCRIBE SELECT * FROM tablename;

=> 인덱스 정보보기 
DESCRIBE INDEXES FOR TABLE table-name [show detail]

=> 등록 테이블 리스트 보기
LIST  TABLES  FOR  ALL ;

=> LOCK 상태 확인
GET SNAPSHOT FOR LOCKS ON depsdb
  유지되는 잠금, 현재 잠금대기중인 에이전트, 응용프로그램명,
  응용프로그램 상태, 총대기시간, 모드상태 등을 확인

SELECT * FROM staff FETCH FIRST 5 ROWS ONLY

라고 하면 하면 처음 5개의 row가 나옵니다.

SELECT bus_mkt_id, svc_mgmt_num, svc_cd, svc_num, cell_equip_modl_cd, line_num
  FROM coispc.vcell_num
 WHERE svc_mgmt_num = ?
 ORDER BY eff_dt_tm desc FETCH FIRST 1 ROWS ONLY
  WITH UR

SELECT INTEGER(SUBSTR(CHAR(CURRENT DATE),1,1)||SUBSTR(CHAR(CURRENT DATE),3,2)||SUBSTR(CHAR(CURRENT DATE),6,2)||SUBSTR(CHAR(CURRENT DATE),9,2)),
       INTEGER(SUBSTR(CHAR(CURRENT TIMESTAMP),12, 2) || SUBSTR(CHAR(CURRENT TIMESTAMP),15,2) || SUBSTR(CHAR(CURRENT TIMESTAMP),18,2) || SUBSTR(CHAR(CURRENT TIMESTAMP),21,1))
FROM SYSIBM.SYSDUMMY1
WITH UR

CREATE FUNCTION month_between (p_start date, p_end date)
RETURNS SMALLINT
BEGIN atomic
      DECLARE v_year_diff  SMALLINT DEFAULT 0;
      DECLARE v_month_diff SMALLINT DEFAULT 0;
      DECLARE v_diff       SMALLINT DEFAULT 0;
            
      SET v_year_diff  = YEAR(p_start) - YEAR(p_end);
      SET v_month_diff = MONTH(p_start) - MONTH(p_end);

      IF v_year_diff != 0 THEN
         set v_diff = v_year_diff * 12;
      END if;

      SET v_diff = v_diff + v_month_diff;

      RETURN v_diff;
END@

=> Oracle의 Decode기능
ex1)
SELECT  rownumber,CASE WHEN  zip_code BETWEEN '100091' AND '100091' THEN '91'
                WHEN  zip_code BETWEEN '100092' AND '100092' THEN '92'
                WHEN  zip_code BETWEEN '100093' AND '100093' THEN '93'
                WHEN  zip_code BETWEEN '100094' AND '100094' THEN '94'
                WHEN  zip_code BETWEEN '100095' AND '100095' THEN '95'
             ELSE   '99'
          END
  FROM (
        SELECT zip_code,ROWNUMBER() OVER (ORDER BY zip_code) AS rownumber
          FROM zipcode
       ) AS t
 WHERE ROWNUMBER  BETWEEN 20 AND 30

ex2)
SELECT  ROWNUMBER,CASE zip_code
                  WHEN  '100091' THEN '91'
                  WHEN  '100092' THEN '92'
                  WHEN  '100093' THEN '93'
                  WHEN  '100094' THEN '94'
                  WHEN  '100095' THEN '95'
                  ELSE   '99'
                  END 
  FROM (                      
        SELECT  zip_code,ROWNUMBER() OVER (ORDER BY zip_code) AS rownumber
          FROM zipcode
       ) AS t
 WHERE ROWNUMBER  BETWEEN 20 AND 30

=> INTEGER형으로 변환

ex)
SELECT INTEGER(zip_code)
  FROM zipcode
 FETCH FIRST 5 ROWS ONLY

=> DOUBLE형으로 변환

ex)
SELECT DOUBLE(zip_code)
  FROM zipcode
 FETCH FIRST 5 ROWS ONLY

=> SUBSTR

ex)
SELECT SUBSTR(zip_code,1,3)
  FROM zipcode
 FETCH FIRST 5 ROWS ONLY

=> CHAR

ex)
SELECT CHAR(doseo)
  FROM zipcode
 FETCH FIRST 5 ROWS ONLY

=> COALESCE - Oracle Nvl()기능 컬럼 타입에 따라 인수를 결정한다. COALESCE(문자형,문자형표시)  COALESCE(숫자형,숫자형표시)

ex)
SELECT COALESCE(doseo,'1')
  FROM zipcode
 FETCH FIRST 5 ROWS ONLY

=> ||
문자연결기능
SELECT COALESCE(doseo,'1') || zip_code
  FROM zipcode
 FETCH FIRST 5 ROWS ONLY

=> page기능

SELECT t.zip_code,page
FROM (
       SELECT zip_code,((ROWNUMBER() OVER() -1)/ 10+1) AS page
         FROM zipcode
     ) AS t
 WHERE t.page = 3
 FETCH FIRST 100 ROWS ONLY
  WITH UR

=>  year 구하기
ex1)
SELECT  YEAR(CURRENT TIMESTAMP)
  FROM SYSIBM.SYSDUMMY1
  WITH UR

ex2)
SELECT  YEAR('2004-05-16')
  FROM SYSIBM.SYSDUMMY1
  WITH UR

ex3)
SELECT  YEAR(CURRENT DATE)
  FROM SYSIBM.SYSDUMMY1
  WITH UR

=>  month 구하기

ex1)
SELECT  MONTH(CURRENT TIMESTAMP)
  FROM SYSIBM.SYSDUMMY1
  WITH UR

ex2)
SELECT  MONTH('2004-08-16')
  FROM SYSIBM.SYSDUMMY1
  WITH UR

=>  day 구하기

ex1)
SELECT  DAY(CURRENT TIMESTAMP)
  FROM SYSIBM.SYSDUMMY1
  WITH UR
EX2)
SELECT  DAY('2004-08-16')
  FROM SYSIBM.SYSDUMMY1
  WITH UR

=>  week 구하기
ex)
SELECT  WEEK('2004-05-16')
  FROM SYSIBM.SYSDUMMY1
  WITH UR

@@@ time 구하기

ex)
SELECT  CURRENT TIME
  FROM SYSIBM.SYSDUMMY1
  WITH UR

=>  dayofyear 구하기(오늘이 365일중 몇번째 날짜)
ex)
SELECT  DAYOFYEAR(CURRENT DATE)
  FROM SYSIBM.SYSDUMMY1
  WITH UR

=>  dayname 구하기(요일 이름)

ex)
SELECT  DAYNAME(CURRENT DATE)
  FROM SYSIBM.SYSDUMMY1
  WITH UR

=>  CONCAT 문자연결함수

ex)
SELECT  CONCAT('111','22222 ')
  FROM SYSIBM.SYSDUMMY1
  WITH UR

=>  MOD 나머지 함수

ex)
SELECT  MOD(11111,100)
  FROM SYSIBM.SYSDUMMY1
  WITH UR

=>  value 함수 - COALESCE와 동일한 기능

ex)
SELECT  VALUE(CURRENT DATE,'2004-08-16')
  FROM SYSIBM.SYSDUMMY1
  WITH UR

=>  abs 함수 절대값 함수

ex)
SELECT  ABS(-51234)
  FROM SYSIBM.SYSDUMMY1
  WITH UR

=>  lcas 함수 대문자를 소문자로

ex)
SELECT  LCASE('ABCD')
  FROM SYSIBM.SYSDUMMY1
  WITH UR;

=>  ucase 함수 소문자를 대문자로
ex)
SELECT  LCASE('abcd')
  FROM SYSIBM.SYSDUMMY1
  WITH UR;

=>  multiply_alt 두 수를 곱한다.
ex)
SELECT MULTIPLY_ALT(3,20)
  FROM SYSIBM.SYSDUMMY1
  WITH UR;

=>  round
ex)
SELECT  ROUND(873.726, 2), ROUND(873.726, 1), ROUND(873.726, 0), ROUND(873.726,-1),

        ROUND(873.726,-2), ROUND(873.726,-3), ROUND(873.726,-4)
  FROM SYSIBM.SYSDUMMY1
  WITH UR;

=>  week_iso 함수
ex)
SELECT  WEEK_ISO(CURRENT DATE)     SELECT  WEEK_ISO('1997-12-28')
  FROM SYSIBM.SYSDUMMY1              FROM SYSIBM.SYSDUMMY1        
  WITH UR;                           WITH UR;                     

=>  dayofweek_iso 해당주에서 몇일에 해당하는지
ex)
SELECT   DAYOFWEEK_ISO(CURRENT DATE)    SELECT   DAYOFWEEK_ISO('2004-08-16')
  FROM SYSIBM.SYSDUMMY1                   FROM SYSIBM.SYSDUMMY1              
  WITH UR;                                WITH UR;                           

SELECT  callback_dt,                                          
        callback_tm,                                         
        COUNT(seqno),                                        
        COUNT(custname),                                     
        telno_1||'-'|| telno_2||'-'|| telno_3  AS tel_number 
  FROM callback
 GROUP BY  callback_dt,callback_tm,telno_1||'-'|| telno_2||'-'|| telno_3
FETCH FIRST 5 ROWS ONLY;

SELECT  *  FROM
(  SELECT  ROWNUMBER() OVER() AS rownum,statement_text
   FROM  explain_statement
) AS t
WHERE t.rownum = 2
FETCH  FIRST 100  ROWS  ONLY

=>  outer joinSELECT CASE  WHEN a.relation = '1' THEN '본인'
             WHEN a.relation = '2' THEN '배우자' 
             WHEN a.relation = '3' THEN '자녀'  
             WHEN a.relation = '4' THEN '부모' 
             WHEN a.relation = '5' THEN '형제자매' 
             WHEN a.relation = '6' THEN '기타' 
             ELSE '기타'
        END AS kwan,a.name,a.fsocial_no  AS  fsocial_no, 
 CASE SUBSTR(a.fsocial_no,7,1) 
      WHEN '1' THEN YEAR(CURRENT DATE - DATE('19'||SUBSTR(A.fsocial_no,1,2)||'-'||SUBSTR(A.fsocial_no,3,2)||'-'||'01')) 
      WHEN '2' THEN YEAR(CURRENT DATE - DATE('19'||SUBSTR(A.fsocial_no,1,2)||'-'||SUBSTR(A.fsocial_no,3,2)||'-'||'01')) 
      WHEN '3' THEN YEAR(CURRENT DATE - DATE('20'||SUBSTR(A.fsocial_no,1,2)||'-'||SUBSTR(A.fsocial_no,3,2)||'-'||'01')) 
      WHEN '4' THEN YEAR(CURRENT DATE - DATE('20'||SUBSTR(A.fsocial_no,1,2)||'-'||SUBSTR(A.fsocial_no,3,2)||'-'||'01')) 
      WHEN '5' THEN YEAR(CURRENT DATE - DATE('19'||SUBSTR(A.fsocial_no,1,2)||'-'||SUBSTR(A.fsocial_no,3,2)||'-'||'01')) 
      WHEN '6' THEN YEAR(CURRENT DATE - DATE('19'||SUBSTR(A.fsocial_no,1,2)||'-'||SUBSTR(A.fsocial_no,3,2)||'-'||'01')) 
      WHEN '7' THEN YEAR(CURRENT DATE - DATE('20'||SUBSTR(A.fsocial_no,1,2)||'-'||SUBSTR(A.fsocial_no,3,2)||'-'||'01')) 
      WHEN '8' THEN YEAR(CURRENT DATE - DATE('20'||SUBSTR(A.fsocial_no,1,2)||'-'||SUBSTR(A.fsocial_no,3,2)||'-'||'01')) 
      WHEN '9' THEN YEAR(CURRENT DATE - DATE('18'||SUBSTR(A.fsocial_no,1,2)||'-'||SUBSTR(A.fsocial_no,3,2)||'-'||'01')) 
  ELSE YEAR(CURRENT DATE - DATE('18'||SUBSTR(a.fsocial_no,1,2)||'-'||SUBSTR(a.fsocial_no,3,2)||'-'||'01'))
         END  AS  YEARS,
  VALUE(b.company_nm,'') AS COMPANY_NM,
  VALUE(b.dept,'') AS DEPT,
  VALUE(b.duty,'') AS DUTY, 
  VALUE(b.offi_tel_1,'') || VALUE(b.offi_tel_2,'') || VALUE(b.offi_tel_3,'') AS offi_tel, 
  CASE WHEN a.live_yn = '1' THEN '동거'
       ELSE ''
  END AS home 
 FROM cust.family_info A LEFT OUTER JOIN euc20.customer b ON ( a.fsocial_no =  b.social_no ) 
WHERE a.social_no = '6611211010815'
ORDER BY  fsocial_no

===========================================================================

=>  MOD : 나머지 연산

   문법 : MOD(expression, expression)

   > 첫 번째 인수를 두 번째 인수로 나눈 나머지를 리턴한다. 첫 번째 인수가 음수일 경우에만 결과가 음수가 된다.

   > 함수의 결과

      - 두 인수가 모두 SMALLINT이면 SMALLINT이다.

      - 한 인수는 INTEGER이고 다른 인수는 INTEGER 또는 SMALLINT이면 INTEGER이다.

      - 한 인수는 BIGINT이고 다른 인수는 BIGINT, INTEGER 또는 SMALLINT이면 결과는 BIGINT이다.

   > 결과가 NULL 이 될 수 있다. 즉, 인수가 NULL일 경우, 결과는 NULL이 된다.

=> ROUND : 반올림 연산

   문법 : ROUND(expression1, expression2)

   > ROUND 함수는 expression2가 양수일 경우에는 소수점의 오른쪽으로, expression2가 0 또는 음수일 경우에는 소숫점의 왼쪽으로 expression2 자리로 반올림된 expression1을 리턴한다.

[예제-1]

각각 2, 1, -1, -2, -3, -4 소수 자리로 반올림된 873.726의 값을 계산한 결과는 아래와 같다.

VALUES(

     ROUND(873.726, 2),

     ROUND(873.726, 1),

     ROUND(873.726, 0),

     ROUND(873.726, -1),

     ROUND(873.726, -2),

     ROUND(873.726, -3),

     ROUND(873.726, -4) )

[예제-1의 결과]

     1      |      2         |       3      |      4       |      5      |     6        |   7

----------------------------------------------------------------------

873.730   |   873.700   |   874.000  |  870.000  | 900.000  | 1000.000  | 0.000

[예제-2]

양수와 음수를 사용하여 계산

VALUES (

      ROUND(3.5, 0),

      ROUND(3.1, 0),

      ROUND(-3.1, 0),

      ROUND(-3.5, 0) )

[예제-2의 결과]

    1    |   2   |    3   |    4

---------------------------

   4.0  |  3.0  | -3.0  |  -4.0

=> FLOOR : 인수보다 작거나 같은 최대 정수 값을 리턴

   문법 : FLOOR(expression)

=> LCASE 또는 LOWER : 소문자로 변환된 문자열 리턴 (반대개념 : UPPER)

   문법 : LOWER(expression)

=> LEFT : expression1의 가장 왼쪽에 있는 expression2 바이트로 구성되는 문자열을 리턴.

   문법 : LEFT(expression1, express2)

=>  LENGTH : 길이를 리턴

   문법 : LENGTH(expression)

=>  LOCATE : expression2 내에서 첫 번째 expression1 어커런스의 시작 위치를 리턴.

   문법 : LOCATE(expression1, expression2 [, expression3])

   > expression2 내에서 첫 번째 expression1 어커런스의 시작 위치를 리턴한다. 선택적 expression3이 지정되는 경우, 검색이 시작되는 expression2의 문자 위치를 나타낸다. expression1이 expression2에 없을 경우, 값 0이 리턴된다.

=>  LTRIM : string-expression의 시작 부분에서 공백을 제거

   문법 : LTRIM(string-expression)

=>  NULLIF : 인수가 같을 경우에는 NULL 값을 리턴하고, 그 외에는 첫 번째 인수 값을 리턴

   문법 : NULLIF(expression, expression)

   > NULLIF(e1, e2) 를 사용한 결과는 다음 표현식을 사용한 결과와 동일하다.

      CASE WHEN e1 = e2 THEN NULL ELSE e1 END

=>  POSSTR : 한 문자열(source-string) 내에서 다른 문자열(search-string)의 첫 번째 시작 위치를 리턴

   문법 : POSTSTR(source-string, search-string)

   > POSSTR 함수는 한 문자열(source-string) 내에서 다른 문자열(search-string)의 첫 번째 시작 위치를 리턴한다. search-string 위치에 대한 숫자는 1(0이 아님)에서 시작된다.

=>  POWER : expression1의 값을 expression2의 승수로 리턴

   문법 : POWER(expression1, expression2)

=>  QUARTER : 지정된 날짜에 대한 분기를 나타낸다.

   문법 : QUARTER(expression)

   

   > 인수에 지정된 날짜에 대한 분기를 나타내는 1 ~ 4 범위의 정수 값을 리턴한다.

   > 인수는 날짜나 시간소인의 유효한 문자열 표현이어야 한다.

   > 함수의 결과는 INTEGER 이며, 결과가 NULL 이 될 수 있다. 즉, 인수가 NULL인 경우, 결과는 NULL 값이 된다.

=> REPEAT : 두 번째 인수가 지정된 배수만큼 반복되는 첫 번째 인수로 구성된 문자열을 리턴

   사용예 : REPEAT(expression1, expression2)

=> REPLACE : expression1에 있는 expression2의 모든 어커런스를 expression3으로 대체

   문법 : REPLACE(expression1, expression2, expression3)

=> SUBSTR : 문자열의 부속 문자열을 리턴

   문법 : SUBSTR(string, start, length)

=> TIMESTAMPDIFF : 두 시간소인의 차이에 따라 첫 번째 인수가 정의한 유형의 측정된 간격 수를 리턴

   문법 : TIMESTAMPDIFF(유형, expression1, expression2)

   > 유형 : 1 - 분할초, 2 - 초, 4 - 분, 8 - 시간, 16 - 일, 32 - 주, 64 - 월, 128 - 분기, 256 - 년

  [사용예제] 다음 예에서는 두 시간소인 사이의 시간(분)인 4277을 리턴한다.

    TIMESTAMPDIFF(4, CHAR(TIMESTAMP('2001-09-29-11.25.42.483219') - TIMESTAMP('20019-26-12.07.58.065497')))

=> TO_CHAR : 문자 템플리트를 사용하여 형식화된 시간소인의 문자 표현을 리턴

   문법 : TO_CHAR(timestamp-expression, format-string)

    >> format-string 속성 : YYYY-MM-DD HH24:MI:SS

=> TO_DATE : 문자 템플리트를 사용하여 해석된 문자열에서 시간소인을 리턴

   문법 : TO_DATE(string-expression, format-string)

    >> format-string 속성 : YYYY-MM-DD HH24:MI:SS

=> TRUNCATE 또는 TRUNC : expression2가 양수이면 소수점 오른쪽으로, expression2가 0 또는 음수이면 소수점 왼쪽으로 expression2 자리로 절단된 expression1을 리턴

   문법 : TRUNCATE(expression1, expression2)

[사용예] 각각 2, 1, 0, -1, -2 소수 자릿수로 절단된 숫자 873.726을 표시

    VALUES (

           TRUNC(873.726, 2),

           TRUNC(873.726, 1),

           TRUNC(873.726, 0),

           TRUNC(873.726, -1),

           TRUNC(873.726, -2),

           TRUNC(873.726, 2) )

[결과]

    1       |      2      |      3       |     4      |     5        |    6

---------------------------------------------------------

873.720  |  873.700  |  873.000  | 870.000  |  800.000  | 0.000

=> COALESCE : NULL이 아닌 첫 번째 인수를 리턴(VALUES와 동일)

   문법 : COALESCE(expression, expression, ....)

[사용예] EMPLOYEE 테이블에 있는 모든 행에서 직원 번호(EMPNO)와 급여(SALARY)를 선택할 때 급여가 빠지는 경우(즉, NULL일 경우) 0값이 리턴

SELECT EMPNO, COALESCE(SALARY, 0)

   FROM EMPLOYEE

=>  CONCAT : 두 문자열 인수의 병합을 리턴 (||와 동일)

   문법 : CONCAT(expression1, expression2)

=> . DIGITS : 숫자의 문자열 표현을 리턴

   문법 : DIGITS(expression)

   > 인수가 NULL이 될 수 있는 경우, 결과는 NULL 이 될 수 있다. 즉, 인수가 NULL일 경우 결과는 NULL이 된다.

    - 인수가 SMALLINT 이면 5,

                 INTEGER이면 10,

                 BIGINT이면 19 만큼의 문자열을 (앞에서부터)0으로 채운다.

  [사용예]

    SELECT DIGITS((int('0200000') + 99999))
       FROM sysibm.sysdummy1

=> EXCEPT 또는 EXCEPT ALL : 두 개의 다른 결과 테이블(R1과 R2)에서 R2에 해당하는 행이 없는 R1의 모든 행으로 구성한다.

EXCEPT ALL은 중복되는 행을 포함하지만, EXCEPT는 중복되는 행을 제거한다.

 >> 테이블 R1                                                >> 테이블 R2

------------------------------------         -----------------------------------

   주민번호   |  이름   |  전화번호                    주민번호 |   이름    | 전화번호

------------------------------------         -----------------------------------

  123456       | 홍길동 | 02-111-1234                 122222   |  김길동   | 02-145-2365

------------------------------------         -----------------------------------

  122222       | 김길동 | 02-145-2365

------------------------------------

[사용 예]

(SELECT * FROM R1)

EXCEPT

(SELECT * FROM R2)

수행결과 :

------------------------------------

   주민번호   |  이름   |  전화번호        

------------------------------------

  123456       | 홍길동 | 02-111-1234    

------------------------------------

[위와 동일한 쿼리문]

SELECT *

    FROM R1

  WHERE NOT EXISTS (SELECT *

                                     FROM T2)

=> INTERSECT 또는 INTERSECT ALL : 두 개의 다른 결과 테이블(R1과 R2)에서 R1과 R2 둘 다에 있는 모든 행으로 구성한다.

INTERSECT ALL 은 중복되는 행을 포함하지만, INTERSECT는 중복되는 행을 제거한다.

[사용 예]

(SELECT * FROM R1)

INTERSECT

(SELECT * FROM R2)