20140501

오라클 NVL, NVL2 함수의 예제 오라클 쿼리에서 NULL 처리는 매우매우 중요합니다. 특히나 WHERE 절에서 NULL 처리는 원하는 데이터 추출을 위해 필수조건이 되는 경우도 있습니다. ■ NVL 함수의 필요성 --예제1 SELECT * FROM (SELECT 'ABCDEDF' AA FROM DUAL UNION ALL SELECT 'ZYDX' AA FROM DUAL UNION ALL SELECT '' AA FROM DUAL) WHERE AA NOT LIKE 'Z%' ; --예제1 결과 --Z 로 시작하지 않는 데이터 추출시 NULL 데이터 제외 --예제2 SELECT * FROM (SELECT 'ABCDEDF' AA FROM DUAL UNION ALL SELECT 'ZYDX' AA FROM DUA..

프로그래밍/Oracle 2016. 3. 3. 10:43

오라클 정규식을 활용한 전화번호 자르기 전화번호라 하면 일반 전화번호에서 핸드폰번호까지 다양한 형태의 번호가 존재02로 시작하는 번호, 080으로 시작하는 번호, 010, 011 로 시작하는 번호 등 다양한 번호의 유형이 있어 이걸 일반쿼리문으로 구분자를 넣거나, 자르기를 하는 것은쿼리문도 길어질뿐 아니라 다양한 유형에 대한 처리가 어려움 그래서 정규식을 활용한 전화번호 자르기 및 구분자 넣기SELECT REGEXP_REPLACE('전화번호','(02|031|032|033|041|042|043|051|052|053|054|055|061|062|063|064'||'|010|011|016|017|018|019'||'|030|050|060|070|080)'||'(.+)(.{4})','\1-\2-\3') FRO..

프로그래밍/Oracle 2016. 2. 22. 10:06

오라클의 procedure 나 function 사용시 예외발생에 대한 처리가 반드시 필요. 그 중에서도 원하는 예외부분이나 특정예외일때 그 예외에 따른 결과값을 원할때 사용자정의 exception 을 활용 --기본적인 형태 예외이름 EXCEPTION; PRAGMA EXCEPTION_INIT(예외이름, 오류번호); --예제 CREATE OR REPLACE FUNCTION TEST_F(IN_CHAR_DATE IN VARCHAR2) RETURN VARCHAR2 IS V_RETURN_VAL VARCHAR2(3); --사용자 정의 예외 이름 V_EXCEPTION EXCEPTION; --ORA-01841: 년은 영이 아닌 -4713 과 +4713 사이의 값으로 지정해야 합니다. 에 대한 오류번호 PRAGMA EXC..

프로그래밍/Oracle 2016. 2. 5. 17:12

오라클 비율구하기 RATIO_TO_REPORT 예제 한 회사 팀별로 팀원수가 전체인원 대비 차지 하는 비율(비중)을 구하고 싶을때 RATIO_TO_REPORT 를 활용 예제 SELECT "팀명", "인원수", RATIO_TO_REPORT("인원수") OVER() "팀원비중" FROM (SELECT '시스템팀' "팀명", 10 "인원수" FROM DUAL UNION ALL SELECT '재무팀' "팀명", 6 "인원수" FROM DUAL UNION ALL SELECT '마케팅팀' "팀명", 8 "인원수" FROM DUAL UNION ALL SELECT '업무지원팀' "팀명", 3 "인원수" FROM DUAL UNION ALL SELECT '기획팀' "팀명", 6 "인원수" FROM DUAL) 형태는 RAT..

프로그래밍/Oracle 2016. 1. 26. 14:58

오라클 컬럼 추가, 삭제, 변경 예제 오라클 테이블 설계 후, 필요에 의해 컬럼을 추가하거나, 삭제, 혹은 변경 해야하는 경우가 있습니다. 이럴경우 컬럼의 수정은 어떻게하는지 알아보겠습니다. --오라클 컬럼 추가 ALTER TABLE 테이블명 ADD (칼럼명 데이터유형) --오라클 컬럼 추가 예시 ALTER TABLE EMPLOYEE_TABLE ADD (NAME VARCHAR2(30)) --오라클 컬럼 삭제 ALTER TABLE 테이블명 DROP COLUMN 컬럼명 --오라클 컬럼 삭제 예시 ALTER TABLE EMPLOYEE_TABLE DROP COLUMN NAME --오라클 컬럼 데이터형 변경 ALTER TABLE 테이블명 MODIFY (컬럼명, 변경할 데이터형) --오라클 컬럼 데이터형 변경 예시..

프로그래밍/Oracle 2016. 1. 20. 17:37

오라클에서 INSERT, UPDATE, DELETE 등의 DML 작업시, 작업전의 테이블상태 및 데이터를 확인해야 될 경우가 있다. 이럴때 AS OF TIMESTAMP 를 활용하여, 수정전의 데이터를 확인하고, 복구 등의 작업을 할 수 있는데 간단한 예제를 보자. SELECT * FROM 테이블명 AS OF TIMESTAMP TO_TIMESTAMP('20160119 150000','YYYYMMDD HH24:MI:SS') 위 예제는 2016년 1월 19일 15시 정각 기준으로 해당테이블을 조회하여, 해당시점의 데이터를 보여준다. WHERE 절을 활용하여 특정데이터의 시점을 볼 수도 있다. SELECT * FROM 테이블명 AS OF TIMESTAMP TO_TIMESTAMP('20160119 150000',..

프로그래밍/Oracle 2016. 1. 19. 16:13

오라클 DB Lock(락) 조회 및 Session Kill(죽이기) 오라클에서 Lock(락)이 발생하는 원인은 다양 Lock 발생 원인은 추후 포스팅 예정. -TM (TABLE LOCK) 조회(보통 DDL 수행시에 발생) SELECT B.TYPE, C.OBJECT_NAME, A.SID, A.SERIAL# FROM V$SESSION A, V$LOCK B, DBA_OBJECTS C WHERE A.SID=B.SID AND B.ID1=C.OBJECT_ID AND B.TYPE='TM' ; -Session Kill(세션 죽이기) -예) SID = 524, SERIAL = 20 ALTER SYSTEM KILL SESSION '524,20' ;

프로그래밍/Oracle 2015. 12. 30. 19:02

오라클 TO_CHAR 함수 SELECT TO_CHAR(SYSATE,'YYYYM') A, TO_CHAR(SYSDATE,'MM') B, TO_CHAR(SYSDATE,'DD') C, TO_CHAR(SYSDATE,'YYYYMMDD') D, TO_CHAR(SYSDATE,'DDD') E, TO_CHAR(SYSDATE,'D') F, TO_CHAR(SYSDATE,'HH24') G, TO_CHAR(SYSDATE,'HH12') H, TO_CHAR(SYSDATE,'MI') I, TO_CHAR(SYSDATE,'SS') J, TO_CHAR(SYSDATE,'YYYYMMDD HH24:MI:SS') K FROM DUAL; YYYY : 년 MM : 월 DD : 일 YYYYMMDD : 년월일 DDD : 365일 중의 일자 D : 일주일..

프로그래밍/Oracle 2015. 12. 28. 23:51

오라클 BETWEEN 함수 오라클 BETWEEN 함수는 범위를 지정하여 조건을 걸 때 사용합니다. 간단한 예제를 통해서 알아보겠습니다. SELECT TEST_DATE FROM (SELECT '20140101' TEST_DATE FROM DUAL UNION ALL SELECT '20140110' TEST_DATE FROM DUAL UNION ALL SELECT '20140115' TEST_DATE FROM DUAL UNION ALL SELECT '20140120' TEST_DATE FROM DUAL UNION ALL SELECT '20140130' TEST_DATE FROM DUAL) 이와 같은 데이터가 있다고 했을 경우 TEST_DATE 값이 20140101 과 20140120 사이에 있는 것들만 선택하..

프로그래밍/Oracle 2014. 9. 2. 21:44

※ LENGTH : 문자열의 길이를 반환※ LENGTHB : 문자열의 BYTE 를 반환 ●쿼리 SELECT LENGTH(AA), LENGTHB(AA), LENGTH(BB), LENGTHB(BB), LENGTH(CC), LENGTHB(CC) FROM (SELECT '가나다라마' AA, '12345' BB, 'ABCDE' CC FROM DUAL); ●결과LENGTH(AA) LENGTHB(AA) LENGTH(BB) LENGTHB(BB) LENGTH(CC) LENGTHB(CC) 5 10 5 5 5 5

프로그래밍/Oracle 2014. 8. 2. 01:44

ORDER BY 구문 활용시 단순 칼럼명으로 정렬하는것이 아니라 특정칼럼의 특정값을 먼저 오게 하는것처럼 정렬순서를 변경하고 싶은경우 ORDER BY 구문과 DECODE 혹은 CASE WHEN 구문을 함께 활용하면 된다. -DECODE 사용 예제 SELECT PART, GRADE FROM (SELECT '국어' PART, 'A' GRADE FROM DUAL UNION ALL SELECT '수학' PART, 'C' GRADE FROM DUAL UNION ALL SELECT '영어' PART, 'B' GRADE FROM DUAL) ORDER BY DECODE(PART,'영어',0); -CASE WHEN 활용 예제 SELECT PART, GRADE FROM (SELECT '국어' PART, 'A' GRADE ..

프로그래밍/Oracle 2014. 3. 27. 11:06

오라클 10g 이상부터는 ORA_ROWSCN 활용 가능. ※ORA_ROWSCN가장 최근에 SCN(System Change Number) 값을 반환. 테이블의 데이터가 수정된 시간을 관리하는 칼럼이 없을 경우 사용하면 유용.※예시SELECT ORA_ROWSCN, SCN_TO_TIMESTAMP(ORA_ROWSCN) FROM 테이블명 SCN_TO_TIMESTAMP 를 활용해서 시간을 구할 수 있다.단, SCN_TO_TIMESTAMP 함수는 변환가능한 시간에 제약이 있는데 대략 5일 미만 이렇게 로우들이 각각 마지막으로 수정될 시간을 구할 수 있으며,WHERE 절을 통해 조건 또한 걸 수 있다.

프로그래밍/Oracle 2014. 3. 20. 13:36

[오라클 ADD_MONTHS 사용시 주의할점] - ADD_MONTHS : 지정한 날짜 중 해당 월에 정수를 더한 값을 반환 - 기본문법 : ADD_MONTHS(date, integer) - 주의할점 : 기준이 되는 날짜가 '말일'일 경우는, 계산된 값도 해당 월의 말일을 반환한다. 예제) SELECT ADD_MONTHS(TO_DATE('20140330','YYYYMMDD'),6), ADD_MONTHS(TO_DATE('20140331','YYYYMMDD'),6) FROM DUAL 2014년 3월 30일과 2014년 3월 31일의 6개월 후 날짜를 ADD_MONTHS 함수를 사용하여 구할경우 2014년 9월 30일로 동일한 값이 반환된다. 실제 3월 30일의 6개월 후가 9월 30일이면서, 3월 31일이 3..

프로그래밍/Oracle 2014. 3. 14. 17:12

오라클에서 IF처럼 사용할 수 있는 CASE WHEN THEN 구문 --기존 구조 CASE WHEN 조건 THEN 결과 ELSE 조건에 맞지않는 나머지 결과 END --예시 SELECT CASE WHEN CNT >= 1000 THEN '1000 이상' ELSE '1000 이하' END CNT FROM (SELECT 1000 CNT FROM DUAL)

프로그래밍/Oracle 2014. 1. 19. 19:54

데이터를 추출하면서 소계 및 합계를 구할때 사용하면 유용. --예제WITH TEST_V AS( SELECT '201311' 월, '영업' 부서, '1000' 판매량 FROM DUAL UNION ALL SELECT '201312' 월, '영업' 부서, '500' 판매량 FROM DUAL UNION ALL SELECT '201401' 월, '영업' 부서, '1200' 판매량 FROM DUAL UNION ALL SELECT '201311' 월, '내근직' 부서, '400' 판매량 FROM DUAL UNION ALL SELECT '201312' 월, '내근직' 부서, '100' 판매량 FROM DUAL UNION ALL SELECT '201401' 월, '내근직' 부서, '700' 판매량 FROM DUAL UN..

프로그래밍/Oracle 2014. 1. 14. 16:40

MOD 함수 - MOD(m,n) : m을 n 으로 나누었을 때 나머지를 반환하다 활용 1 : 나머지 구하기 SELECT MOD(3,2) FROM DUAL--결과값 : 1 활용 2 : 초를 분으로 변경하기 SELECT TRUNC(150/60)||'분'||MOD(150,60)||'초' --150초를 분/초로 변경 FROM DUAL -- 결과값 : 2분30초

프로그래밍/Oracle 2014. 1. 3. 14:47

대표 Access Type NESTED LOOPS: 맨 안쪽부터 바깥 쪽으로, 그리고 위에서부터 아래쪽으로 건별 순차적 액세스.BY INDEX ROWID Partition 되지 않은 index 를 통해 테이블의 row에 access 한다.UNIQUE SCAN Unique-index 를 통해 테이블의 row 에 access 한다. 전체 Access Type 보기(출처 : http://302.pe.kr/206)

프로그래밍/Oracle 2013. 12. 23. 10:53

블록 오라클에서, 블록은 데이터 파일 I/O의 가장 작은 단위이자 할당될 수 있는 공간의 가장 작은 단위입니다. 오라클 블록은 하나이상의 연속적인 운영체제 블록으로 이루어져 있습니다. 익스텐트 익스텐트는 여러 연속적인 데이터 블록으로 만들어진 데이터베이스 저장장소 공간 할당의 논리적 단위입니다. 하나 이상의 익스텐트가 모여 세그먼트를 이룹니다. 세그먼트에서 기존 공간이 완전히 사용되면, 오라클은 세그먼트에 대해 새로운 익스텐트를 할당합니다. 세그먼트 세그먼트는 테이블스페이스 내의 특정 논리적 저장구조에 대한 모든 데이터를 포함하는 익스텐트의 집합입니다. 예를 들어, 오라클은 각 테이블에 대해 하나 이상의 익스텐트를 할당하여, 그 테이블에 대한 데이터 세그먼트를 형성합니다. 인덱스에 대해서는 하나 이상의 ..

프로그래밍/Oracle 2013. 6. 26. 13:39

★날짜 + 숫자는 날짜형식이다 http://www.oracleclub.com/article/13255

프로그래밍/Oracle 2013. 4. 5. 14:42

문법NVL(EXPR1, EXPR2)EXPR1이 NULL 값인 경우 EXPR2의 값을 반환한다.

프로그래밍/Oracle 2012. 10. 11. 19:50