DB2 함수 사용법.

DB2 함수 사용법.

Programming/ORACLE 2010. 5. 24. 17:55

출처: 피천사님의 블로그 http://p1004.egloos.com/686572/

기본정보보기
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 join
SELECT 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이 된다.

2. ROUND : 반올림 연산

   문법 : ROUND(expressionŋ, expressionŌ)

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

[예제-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

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

   문법 : FLOOR(expression!)

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

   문법 : LOWER(expression!)

5. LEFT : expressionŋ의 가장 왼쪽에 있는 expressionŌ 바이트로 구성되는 문자열을 리턴.

   문법 : LEFT(expressionŋ, express2)

6. LENGTH : 길이를 리턴

   문법 : LENGTH(expression!)

7. LOCATE : expressionŌ 내에서 첫 번째 expressionŋ 어커런스의 시작 위치를 리턴.

   문법 : LOCATE(expressionŋ, expressionŌ [, expressionō])

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

8. LTRIM : string-expression!의 시작 부분에서 공백을 제거

   문법 : LTRIM(string-expression!)

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

   문법 : NULLIF(expression!, expression!)

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

CASE WHEN e1 = e2 THEN NULL ELSE e1 END

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

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

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

11. POWER : expressionŋ의 값을 expressionŌ의 승수로 리턴

   문법 : POWER(expressionŋ, expressionŌ)

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

문법 : QUARTER(expression!)

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

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

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

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

   사용예 : REPEAT(expressionŋ, expressionŌ)

14. REPLACE : expressionŋ에 있는 expressionŌ의 모든 어커런스를 expressionō으로 대체

문법 : REPLACE(expressionŋ, expressionŌ, expressionō)

15. SUBSTR : 문자열의 부속 문자열을 리턴

   문법 : SUBSTR(string, start, length)

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

   문법 : TIMESTAMPDIFF(유형, expressionŋ, expressionŌ)

   > 유형 : 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')))

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

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

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

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

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

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

19. TRUNCATE 또는 TRUNC : expressionŌ가 양수이면 소수점 오른쪽으로, expressionŌ가 0 또는 음수이면 소수점 왼쪽으로 expressionŌ 자리로 절단된 expressionŋ을 리턴

   문법 : TRUNCATE(expressionŋ, expressionŌ)

[사용예] 각각 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

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

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

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

SELECT EMPNO, COALESCE(SALARY, 0)

   FROM EMPLOYEE

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

   문법 : CONCAT(expressionŋ, expressionŌ)

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

   문법 : DIGITS(expression!)

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

- 인수가 SMALLINT 이면 5,

   INTEGER이면 10,

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

[사용예]

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

23. 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)

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

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

[사용 예]

(SELECT * FROM R1)

INTERSECT

(SELECT * FROM R2)

저작자표시

'Programming > ORACLE' 카테고리의 다른 글

오라클 Merge 함수의 사용법  (0) 2010.08.13
관련글 관련글 더보기
- 오라클 Merge 함수의 사용법

ABOUT ME

공부하는 민군 공부하는 민군

'Programming > ORACLE' 카테고리의 다른 글

티스토리툴바

ABOUT ME

'Programming > ORACLE' 카테고리의 다른 글

관련글 관련글 더보기

티스토리툴바