POOR MAN'S MOODY BLUES

문자열 중간에 문자열을 끼워넣기 위해서 보통은 SUBSTR로 잘라낸 뒤 CONCAT 또는 || 로 붙이게 되는데 특정 함수로 가져와야 되는 문자열의 경우 함수를 여러번 쓸 필요없이 REGEXP_REPLACE 함수와 정규식을 이용하여 처리 가능하다. SELECT REGEXP_REPLACE(‘0224’, ‘(^.{2})’, ‘\1/‘) AS MMDD FROM DUAL; 위 쿼리문은 문자열 시작부터 문자2개까지 매치된 문자열을 해당문자열 + “/“ 로 치환하라는 것이기에 문자열 0224 는 02/24 로 변환된다. 참고. 괄호로 둘러싼 단위는 그룹을 나타냄 {숫자}는 숫자만큼 있음 .은 아무문자 1개 (공백포함) ^는 문자열의 시작 (만약 [] 안에 쓰여지는 경우 패턴 불일치의 의미)

오라클 쿼리 시 반환되는 레코드 수를 제한하는 방법을 기록해 두고자 글을 작성합니다. Oracle 12버전 이전에는 rownum 으로 레코드 순번을 조회 한 후에 where 조건에서 제한하는 방법을 사용해 왔지만 이 후 부터는 MySql, PostgreSQL 의 LIMIT 와 같이 처리가 가능해 졌다. 예제1. SELECT * FROM "TABLE_NAME" WHERE 1=1 ORDER BY "COL01" ASC NULLS LAST, "COL02" DESC NULLS LAST FETCH NEXT 10 ROWS ONLY ; ORACLE FETCH 문법은 다음과 같다. [ OFFSET offset ROWS ] FETCH NEXT [ row_count / percent PERCENT ] ROWS [ONLY /..

예를 들어 대출을 받은 고객의 대출상태 (정상, 연체)를 월별로 이력을 남겨놨다고 하자 고객의 대출상태가 변경된 월을 뽑고자 한다면 어떻게 해야 할까? WITH T (MM,ST) AS ( SELECT '201601','1' FROM DUAL UNION ALL SELECT '201602','1' FROM DUAL UNION ALL SELECT '201603','1' FROM DUAL UNION ALL SELECT '201604','2' FROM DUAL UNION ALL SELECT '201605','2' FROM DUAL UNION ALL SELECT '201606','1' FROM DUAL UNION ALL SELECT '201607','2' FROM DUAL UNION ALL SELECT '20160..

구분자로 문자열을 잘라와야 되는 경우가 있습니다. 전화번호 포맷이나 날짜포맷 문자열에서 해당 자리수 값을 가져오는 식인데 오라클 9버전 이전에 개발자들은 INSTR 함수를 이용해서 문자열을 잘라야 했습니다. 대충 SELECT SUBSTR(tel, 1, INSTR(TEL, '-', 1, 1) -1) AS S1 , SUBSTR(tel, INSTR(TEL,'-', 1, 1)+1, INSTR(tel, '-', 1, 2) - INSTR(tel, '-', 1, 1) -1) AS S2 , SUBSTR(tel, INSTR(tel, '-', 1, 2)+1) AS S3 FROM ( SELECT '02-2222-3333' AS tel FROM DUAL ) 뭐 이런식이였기에 간단한 기능인데 쿼리가 복잡해지는 문제가 있었죠. ..

펑션의 경우 FUNCTION 펑션명 ( .... ) RETURN 타입명 AUTHID 유저ID [CURRENT_USER] IS 프로시저의 경우 PROCEDURE 프로시저명 ( ..... ) AUTHID 유저ID [CURRENT_USER] IS 하면 됨. 참고 https://docs.oracle.com/apps/search/search.jsp?category=database&product=e50529-01&q=authid Oracle Help Center - Search Results for authid Database PL/SQL Language Reference, 12c Release 1 (12.1) Invoker's Rights and Definer's Rights (AUTHID Property) → ..

테이블 컬럼에 다음과 같은 제약조건을 넣어서 프로그램의 오류를 방지할 수 있다. 궁극적으로는 DATE 타입을 쓰는게 맞음 ALTER TABLE "테이블명" ADD CONSTRAINT "제약조건명" CHECK ("컬럼명"= TO_CHAR(TO_DATE("컬럼명",'yyyymmdd'),'yyyymmdd')); 시분초까지 포함이라면 조금 수정하면 되겠음

대출 프로그램 작성 시 상환계획을 임시로 보여주기 위하여 납부일이 휴일인지 여부를 확인하여 목록을 가져오기 위한 쿼리를 기록으로 남깁니다. 매월 25일를 가져오는데 휴일인 경우 익영업일로 가져오기 /* 매월 25일 중 휴일은 성탄절 하루 12월의 휴일은 성탄절 하나. 대체휴일 24, 26일중 휴일은 없음. */ SELECT TO_CHAR(TRUNC(S.DT,'MM'),'YYYYMMDD') AS SDT , CASE WHEN TO_CHAR(S.DT,'D') IN (1,7) THEN TO_CHAR((S.DT + (2-MOD( TO_CHAR(S.DT,'D') ,7))),'YYYYMMDD') ELSE TO_CHAR(S.DT,'YYYYMMDD') END AS CDT FROM ( SELECT CASE WHEN TO_..

오라클에서 날짜정보를 가져올 때 적용 포맷문자열을 기록하기 위하여 남깁니다. SELECT TO_CHAR(TRUNC(D.DT,'MM'),'YYYYMMDD') AS "해당월의 첫날" , TO_CHAR(LAST_DAY(D.DT),'YYYYMMDD') AS "해당월의 마지막날" , TO_CHAR(D.DT,'YEAR') AS "년의 영문명" , TO_CHAR(D.DT,'YYYY') AS "년도4자리" , TO_CHAR(D.DT,'YY') AS "년도2자리" , TO_CHAR(D.DT,'MONTH') AS "개월의 영문명" , TO_CHAR(D.DT,'MON') AS "개월의 영문약어" , TO_CHAR(D.DT,'MM') AS "개월2자리" , TO_CHAR(D.DT,'DAY') AS "요일" , TO_CHAR(..

타입을 별도로 선언하는 것도 좋지만 패키지로 선언해서 사용하는 것이 편리하다. PACKAGE TYPES AS /* 범용 커서 */ TYPE CURSORTYPE IS REF CURSOR; /* 정의된 레코드 */ TYPE 정의REC IS RECORD( 컬럼명1 VARCHAR2(50), 컬럼명2 NUMBER(10), 컬럼명3 NUMBER(20), 컬럼명4 NUMBER(5) ); /* 정의 커서 */ TYPE 정의TYPE IS REF CURSOR; RETURN 정의REC; END; 법용적으로 사용되는 커서의 경우 따로 정의할 필요없이 바로 바로 사용이 가능하지만 어떤 정보가 넘어올 지 알 수가 없기 때문에 변수를 선언한 후 받아야 한다. (물론 java 등에서 받을 때는 ResultSet으로 받으면 되지만....

급하게 다른 시스템의 DB서버에서 데이터 목록을 가져와서 활용해야 하는 일이 주어졌다. 보통은 API 를 작성해야 하지만 각 시스템에 API를 작성하여 적용할 시간이 부족한 관계로 DB Link를 통하여 레퍼런스 커서로 데이터를 가져오는 개발을 진행하였기에 그 기록을 남깁니다. 미리 이야기 하지만 이 방식은 절대 절대 추천하지 않습니다. 가장 큰 이유는 각 시스템 DB서버에 영향을 끼칠 가능성이 매우 크기 때문에 위험한 방식입니다. 우선 원격의 대상 DB에 레퍼런스 커서를 리턴해줄 프로시저 생성 합니다. PROCEDURE 프로시저명 ( io_cur IN OUT NUMBER ) IS mySql varchar2(1000) := 'SELECT * FROM TAB'; rcNUMBER; BEGIN io_cur :..

ORACLE 에서 사용자 정의 CURSOR를 사용하여 값을 리턴 받았을 때 닫는 문제 오라클의 프로시저 및 펑션 오브젝트에서 커서로 값을 리턴할 때 커서가 OPEN 된 상태이기 때문에 받는 쪽에서 닫아주지 않는 경우 커서 OPEN 갯수 초과로 오류가 발생할 위험이 있다. Pro* C : EXEC SQL :ref_cursor_variable; SQLPlus : Implicit PLSQL : Close ref_cursor_variable; Java : RecordSet.close(); 다른 언어들도 대략 비슷할 것 같다.

보통 ORACLE에서 날짜정보를 TO_CHAR 함수를 이용하여 문자열로 변환할 때 포맷을 다음과 같이 사용하게 된다. SELECT TO_CHAR(SYSDATE,'YYYY. MM. DD') FROM DUAL; 이 경우 결과가 2013. 03. 07 과 같이 나오게 되는데 때로는 2013. 3. 7 처럼 표기하고 싶을 때가 있다. 각각의 개월과 날수를 따로 받아서 넘버타입으로 변환 할 수도 있겠지만 간단하게 다음과 같이 해결할 수 있다. SELECT TO_CHAR(SYSDATE,'YYYY. fmMM. DD') FROM DUAL; 보면 개월 포맷 앞에 fm 이라고 붙어있는데 실행시켜 보면 다음과 같은 결과를 얻을 수 있다. 2013. 3. 7

업무에 활용할 쿼리를 만들다 보면 여러 레코드 데이터를 한줄로 표시해야 하는 경우가 있다. 최대 레코드수를 알수있다면 쉽게 해결할테지만 불특정 다수의 레코드를 가로로 표현하는 것은 단순 쿼리로만은 어렵죠. SELECT LTRIM(MAX(SYS_CONNECT_BY_PATH(S,',')),',') AS STR FROM ( SELECT S,M,ROW_NUMBER() OVER (ORDER BY N) AS LN , COUNT (*) OVER (partition by M ) cnt FROM ( SELECT 10*rownum as s , ROWNUM N , mod(rownum,3) as m FROM ( SELECT null FROM dual GROUP BY CUBE(1,2,3,4,5,6,7,8) ) WHERE ROW..

쿼리를 만들다 보면 강제로 여러 레코드를 만들어야 하는 경우가 생깁니다. 쉽게는 원장 테이블을 임의로 조회해서 만들기도 하지만 원장에 항상 데이터가 충분히 있으라는 법도 없죠. /* ============================================================= * 여러 레코드 만드는 법 * ============================================================= */ /** GROUP BY CUBE 이용 100 RECORD 초과는 안됨 8i, 9i에서 테스트 해본 결과 잘됨 */ SELECT ROWNUM FROM ( SELECT NULL FROM dual GROUP BY CUBE(1,2,3,4,5,6,7,8) ) WHERE ROWNUM

Oracle SYSTIMESTAMP 활용 예시를 기록하기 위해 작성한다. SELECT TO_CHAR(SYSTIMESTAMP, 'YYYY/MM/DD HH24:MI:SS.FF3') AS T1 , TO_CHAR(SYSTIMESTAMP + INTERVAL '0 00:00:00.0100' DAY TO SECOND, 'YYYY/MM/DD HH24:MI:SS.FF4') AS T2 FROM DUAL; 출력 결과 T1 T2 ------------------------ --------------------------- 2010/06/29 10:08:28:337 2010/06/29 10:08:28:3471