곰곰히 생각해보면

1. 연도별 일자 구하기
SELECT TO_CHAR(TO_DATE(:yyyy||'1231', 'yyyymmdd'), 'ddd') dayChk FROM dual;
ex) 2018-365 / 2019--365 / 2020--366 / 2021--365

2. 시간 더하기
※ 기본형은 sysdate이지만 편의상 보기위해 to_char를 이용하여 표시함
1) 3시간 더하기 hour
select to_char(sysdate,'YYYY-MM-DD hh24:mi:ss') aa, 
        to_char(sysdate+ (interval '3' hour),'YYYY-MM-DD hh24:mi:ss') bb from dual;
- 결과 : 기본 2020-11-17 10:14:40=> 변경 2020-11-17 13:14:40
2) 3분 더하기 minute
select to_char(sysdate,'YYYY-MM-DD hh24:mi:ss') aa,
         to_char(sysdate+ (interval '3' minute),'YYYY-MM-DD hh24:mi:ss') bb from dual;
- 결과 : 기본 2020-11-17 10:16:35=> 변경 2020-11-17 10:19:35
3) 3초 더하기 second
select to_char(sysdate,'YYYY-MM-DD hh24:mi:ss') aa, 
         to_char(sysdate+ (interval '3' second),'YYYY-MM-DD hh24:mi:ss') bb from dual;

오랜만에 VIEW 생성 및 권한 처리가 필요하여 작업하며 기록에 남긴다.

1. 뷰 생성하기(테이블 및 컬럼은 예시 기재)
CREATE OR REPLACE FORCE VIEW "계정명"."V_뷰이름" 
("성명", "사번", "직위", "년차", "생년월일", "양력구분", "재직상태", "최종진급일", "전직급승진일") 
AS 
SELECT  NAME                        AS 성명
      , SABUN                          AS 사번
      , JIKWEE_CD                     AS 직위
      , F_COM_GET_JIKWEE_YEUNCHA(SABUN, TO_CHAR(SYSDATE, 'YYYYMMDD')) AS 년차
      , BIR_YMD                        AS 생년월일
      , DECODE(LUN_TYPE,1,'양',2,'음') AS 양력구분
      , STATUS_CD                    AS 재직상태 
      , CURR_JIKGUB_YMD          AS 최종진급일
      , NVL(ORD_YMD,CURR_JIKGUB_YMD) AS 전직급승진일
FROM (
                       SELECT A.NAME       AS NAME             -- 성명
                               , A.RES_NO     AS RES_NO           -- 주민번호
                               , A.BIR_YMD    AS BIR_YMD          -- 생년월일
                               , A.EMP_YMD    AS EMP_YMD       -- 입사일
                               , A.GEMP_YMD   AS GEMP_YMD    -- 그룹입사일
                               , A.LUN_TYPE   AS LUN_TYPE         -- 양음구분(1;양, 2:음)
                               , C.*
                              , F_COM_GET_CURR_JIKWEE_YMD(A.ENTER_CD,A.SABUN,TO_CHAR(SYSDATE,'YYYYMMDD'))
                                AS CURR_JIKGUB_YMD
                       FROM 인사마스터 A
                               , 인사기본 C
                       WHERE A.ENTER_CD = C.ENTER_CD
                          AND A.SABUN    = C.SABUN
                          AND A.RET_YMD IS NULL
                          AND C.STATUS_CD NOT IN('RA')
                          AND TO_CHAR(SYSDATE, 'yyyymmdd') BETWEEN C.SDATE AND NVL(C.EDATE, '99991231')
              )  
              WHERE SABUN NOT IN ('testinsa') -- 테스트계정                           
                 order by STATUS_CD, SABUN, NAME;

2. 권한주기 : 타 계정                            
grant select on ROSEBAY.V_INSA_INFO to INSA_INF;
                     계정명.뷰이름             to 권한 부여할 계정명
! 유저 계정 생성하기
create user testUser identified ROSEBAY
※  USER를 생성하기 위해서는 USER생성 권한이 있는 사용자로 접속해야 한다.

3. 타 계정(INSA_INF)에서 조회하기
select * from ROSEBAY.V_INSA_INFO;

4. 동의어(synonym) 부여하기
create synonym V_INSA_INF for ROSEBAY.V_INSA_INFO;
         동의어로 사용할 이름 for 원래 이름
select * from V_INSA_INF ; 
( = select * from ROSEBAY.V_INSA_INFO; )

참고자료
유저생성 : http://www.gurubee.net/lecture/1005

위와 같은 값이 출력된다고 할 때 110 CODE의 RESULT_MON컬럼과 RMON처럼 연산이 필요할 때라면?
아우터 조인을 활용해 보자.

SELECT
  A.SEQ, A.SABUN, A.CODE, A.RESULT_MON,
  DECODE(A.CODE,'110',(A.RESULT_MON-B.RESULT_MON), A.RESULT_MON) RMON
FROM TB_PAY A
LEFT JOIN TB_PAY B 
ON A.ENTER_CD = B.ENTER_CD --PK KEY1
AND A.SEQ= B.SEQ
AND A.SABUN = B.SABUN
AND B.CODE= '113'  
WHERE A.SEQ = '201907001'     
AND A.SABUN = 'rosebay'
AND A.RESULT_MON IS NOT NULL    
AND A.CODE IN ('110','113','230')
ORDER BY A.CODE

기본적이고 자주 쓰는 명령어들이지만 가끔 문법적으로 생각나지 않을 때 보기 위해 기록.

INSERT INTO 테이블 추가
INSERT INTO 테이블명 (AA,BB,CC) --컬럼명1,컬럼명2,컬럼명3
           VALUES(aa,bb,cc);

ALTER 컬럼 추가
ALTER TABLE 테이블 이름 ADD (컬럼명 타입);
ALTER 컬럼 구조 변경
ALTER TABLE 테이블 이름 MODIFY (컬럼명 타입);

CONSTRAINT 제약조건의 생성

CONSTRAINT 제약조건의 재생성
1) PK 키를 삭제 후 다시 생성 ! 스키마(접속시)명 생략 가능
- 삭제 : ALTER TABLE 스키마명.테이블명
   DROP CONSTRAINT SYS_C00626892; --기존PK KEY 명칭
- 추가1) : 시스템 랜덤이름으로 추가
ALTER TABLE 스키마명.테이블명
-- ADD CONSTRAINT PK명 PRIMARY KEY
   ADD ( PRIMARY KEY
  (ENTER_CD, YYYY, HALF_CD, BRANCH_CD)); --컬럼명1,컬럼명2,컬럼명3,컬럼명4
- 추가2) : 본인이 정한 PK별칭으로 추가
ALTER TABLE 테이블명
ADD CONSTRAINT 별칭_PK PRIMARY KEY (HOUSE_CODE, CONT_SEQ, CONT_COUNT) ;
2) NOT NULL 컬럼 추가
- ALTER TABLE 테이블명 MODIFY 컬럼명 NOT NULL;

회사에서 연말정산 패치로 패치파일을 계속 넘겨주는데,
SQL문에 툴에서 '&'가 자꾸 걸린다.

이 때 사용하는 명령어를 남겨본다.

SET DEFINE OFF

SQL Developer 등에서 오류가 날 때(&으로 변수를 찾을 때 이용해 보자.)

참고
구루비 : http://www.gurubee.net/lecture/1148

DB에서 제일 어려운 게 계층형 구조이다. 몇 년이 지나도 아직도 넘나 어려운 것...

오늘도 위기의 오류는 ORA-01436: CONNECT BY의 루프가 발생되었습니다.

이 문제는 오류문구에 답이 있는데 루프를 도는데 문제가 발생했다는 것으로
1) 데이터엔 문제가 없는데 쿼리에 문제가 있다.
: Connect by 절에 조인 조건 누락
2) 쿼리엔 문제가 없는데 데이터에 문제가 있다. 
: 순환되는 자료 오류

2)의 케이스인 경우 간단한 해결 방법으로는 Connect By 뒤에 NOCYCLE을 추가하는 것이다.
말 뜻 그대로 루프 사이클을 돌리지 않겠다는 건데...

제대로 고치려면 1) 쿼리 자체의 수정 혹은 2)Data의 수정이 필요하다.

특히나, 오류가 발생되는 지점을 확인하기 위해서는 CONNECT_BY_ISCYCLE을
조회하는 컬럼에 추가하여 오류 지점이면 1, 아니면 0으로 구분할 수 있으며,
WHERE CONNECT_BY_ISCYCLE = 1을 추가하여 찾아 고쳐보도록 한다.

Ex)
Select LEVEL AS _Lev
        ,Col1
        ,Col2
        ,Col3
        ,Col4
        ,CONNECT_BY_ISCYCLE
FROM (
          Select A.ColSub1
                  ,A.ColSub2
                  ,A.ColSub3
                  ,A.ColSub4
           From Ttest1 A, Ttest2 B
           Where A.ColSub1 = B.ColSub1
            ...
           Group By ColSub1, ColSub2, ColSub3, ColSub4
           ) A
Start With A.Col1 IS NULL
Connet By NOCYCLE PRIOR A.Col2 = A.Col3 --Join 조건

필자의 경우 A.Col2 = A.Col3 데이터가 동일하여 계층 구조를 만드는데 오류가 발생되었음

주저리주저리... 부모를 의미하는 Col3와 Col2 데이터가 동일 = 자기자신을 가르킨 형태..
운영 데이터랑 테스트 데이터가 일치하지 않는데다가 오류 메시지도 데이터 세팅 모두 끝낸 상태에서 쿼리를 조회하니 그때서야 오류가 나와서 하아...

참고
구루비 : http://www.gurubee.net/article/60617 오류 케이스 찾는 방법 설명

계층쿼리 실행 순서
Start With => Connect By => Where

1. 특수문자 제거
SELECT REGEXP_REPLACE('Abcde/2019', '[[:punct:]]') AS Chg FROM DUAL;
=> Abcde2019

2. 날짜 치환

3. 문자 위치 찾기
- @ 위치 찾기(숫자 반환)
SELECT INSTR('ABC1234defg!@#','@',1,1) AS Search FROM DUAL;
=> 13
SELECT INSTR('ABC@1234@defg!@#','@',1,1) AS Search FROM DUAL;
: @의 1번째 위치를 찾는다.
=> 4
SELECT INSTR('ABC@1234@defg!@#','@',1,2) AS Search FROM DUAL;
: @의 2번째 위치를 찾는다.
=> 9

4. 문자 자르기(위치로 자르기)
SELECT SUBSTR('20191212',1,4) AS cut FROM DUAL;
=>2019
* 응용1 : 특정 문자 위치를 찾은 숫자반환으로 위치 자르기
SELECT SUBSTR('20191212>AAA>BCCC>',1,INSTR('20191212>AAA>BCCC>','>',1,2)) CUT_CHK FROM DUAL
: INSTR('20191212>AAA>BCCC>','>',1,2) 위치는 13. 즉, SUBSTR('20191212>AAA>BCCC>',1,13) 과 같은 의미
=> 20191212>AAA>
* 응용2 : 찾은 위치-1(> 찾은 위치-1)
SELECT SUBSTR('20191212>AAA>BCCC>',1,INSTR('20191212>AAA>BCCC>','>',1,2)-1) CUT_CHK FROM DUAL
: INSTR('20191212>AAA>BCCC>','>',1,2)-1 위치는 12. 즉, SUBSTR('20191212>AAA>BCCC>',1,12) 과 같은 의미
=> 20191212>AAA

반복적으로 숫자 증가시켜 입력(INSERT)하기
: 동일한 내용을 특정 숫자만 추가해서 입력하여야 할 경우 사용한다.

예시) 1~20까지 숫자 증가시켜 입력시키기
DECLARE
  P_SQLCODE VARCHAR2(1000);
  P_SQLERRM VARCHAR2(1000);  
  i BINARY_INTEGER := 0;
BEGIN
-- for문 loop
  FOR i in 1..20 LOOP
  BEGIN  
    INSERT INTO TB_TEST001 
    values
    ('ABC','19G02',i,'P14',1,sysdate,'rosebay')
    ;  
  EXCEPTION
  WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE( 'cnt='||i );
        P_SQLERRM := 'cnt='||i||'의 TB_TEST001 UPDATE시 => ' || SQLERRM;
        P_COM_SET_LOG_NOCOMMIT('ABC','PAP','AAA','10','cnt='||i ||SQLERRM , 'ADMIN');
        RETURN;
  END;
  END Loop;
END;       

1. 기존 사용하던 테이블 컬럼 설정 이용하여 Backup 테이블 만들기 
CREATE TABLE 신규테이블_BAK AS
SELECT * FROM 기존테이블 ;

COMMENT ON TABLE "스키마명"."신규테이블_BAK"  IS '기존테이블_BAK';

2. 기존 사용하던 테이블의 데이터 백업 테이블에 복사하기(데이터 copy)
INSERT INTO 신규테이블_BAK
SELECT * FROM 데이터가있는기존테이블명 
WHERE 절(특정 조건 있을 시/전부 복사하려면 WHERE 절 생략)

정렬에 흔히 사용하는 Order by에서 DESC, ASC 외에 조건을 주고 싶을 때가 있다.

특히, 정렬을 하는데 Null이 문제가 될 경우 아래와 같이 사용해 보자.

Order by 컬럼명 DESC Nulls Last => Null값을 뒤로 나오도록 함
Order by 컬럼명 DESC Nulls First => Null값을 먼저 나오도록 함

작성예>
SELECT * 
FROM TABLE명
ORDER BY COLUMN명1, COLUMN명2 NULLS FIRST, COLUMN명3 DESC NULLS LAST;