[데이터베이스] 오라클 내용정리

VS Code에서 NLS 설정 변경

ALTER SESSION SET NLS_LANGUAGE = 'KOREAN';
ALTER SESSION SET NLS_CURRENCY = '￦';
ALTER SESSION SET NLS_DATE_LANGUAGE = 'KOREAN';
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD';

정보확인

테이블 목록 확인

SELECT * FROM tab;   -- 간단히 출력
SELECT * FROM tabs;

테이블 구조 확인

DESC 테이블명;
SELECT * FROM col WHERE tname=UPPER('테이블명');
SELECT * FROM cols WHERE table_name=UPPER('테이블명');

제약조건 확인

-- 제약 조건 확인
SELECT * FROM user_constraints WHERE table_name=UPPER('테이블명'); 

-- 컬럼에 할당된 제약조건에 대한 정보
SELECT * FROM USER_cons_columns;

-- 제약조건 및 컬럼 확인
SELECT u1.table_name, column_name, constraint_type, u1.constraint_name, search_condition 
FROM user_constraints u1
JOIN user_cons_columns u2  ON u1.constraint_name = u2.constraint_name
WHERE u1.table_name = UPPER('테이블명');

P : 기본키

C : NOT NULL 등

U : 유니크키

R : 참조키

부모,자식 테이블 확인

-- 테이블명을 참조하는 모든 테이블 목록 출력(자식 테이블 목록 출력)
SELECT fk.owner, fk.constraint_name , fk.table_name 
FROM all_constraints fk, all_constraints pk 
WHERE fk.r_constraint_name = pk.constraint_name 
     AND fk.constraint_type = 'R'
     AND pk.table_name = UPPER('테이블명')
ORDER BY fk.table_name;

-- 테이블명이 참조하고 있는 모든 테이블 목록 출력(부모 테이블 목록 출력)
SELECT table_name
FROM user_constraints
WHERE constraint_name IN (
    SELECT r_constraint_name 
    FROM user_constraints
    WHERE table_name = UPPER('테이블명') AND constraint_type = 'R'
);

-- 테이블명의 부모 테이블 목록 및 부모 컬럼 목록 출력
SELECT fk.constraint_name, fk.table_name child_table, fc.column_name child_column,
          pk.table_name parent_table, pc.column_name parent_column
FROM all_constraints fk, all_constraints pk, all_cons_columns fc, all_cons_columns pc
WHERE fk.r_constraint_name = pk.constraint_name
         AND fk.constraint_name = fc.constraint_name
         AND pk.constraint_name = pc.constraint_name
         AND fk.constraint_type = 'R'
         AND pk.constraint_type = 'P'
         AND fk.table_name = UPPER('테이블명');

주요 함수

SUBSTR

SUBSTR(char, position [, substring_length ])
문자열중 지정 위치에서 개수만큼 문자열 추출

INSTR

INSTR(string , substring [, position [, occurrence ] ])
문자열의 검색하여 위치를 반환.
문자열이 존재하지 않으면 0을 반환
검색에서 LIKE 보다 속도가 빠름

LPAD,RPAD

LPAD(expr1, n [, expr2])
RPAD(expr1, n [, expr2])
남는 왼쪽(오른쪽) 공간에 특정 문자로 채움

DECODE

DECODE(컬럼, 조건1, 결과값1, 조건2, 결과값2, ..., 기본값);
ex)
DECODE(a, 'b', 1, 2)
a라는 항목 값이 'b'와 같다면 1, 같지 않으면 2를 반환

NVL

NVL(expr1, expr2)
expr1이 Null 이면 expr2 반환하고 Null이 아니면 expr1 반환
NVL2(expr1, expr2, expr3)
expr1이 null이 아니면 expr2를 반환하고, null이면 expr3를 반환

날짜포맷

SELECT TO_DATE('80/05/05', 'YY/MM/DD') FROM dual;
SELECT TO_CHAR(TO_DATE('80/05/05', 'YY/MM/DD'), 'YYYY-MM-DD') FROM dual; -- 2080-05-05

SELECT TO_DATE('80/05/05', 'RR/MM/DD') FROM dual;
SELECT TO_CHAR(TO_DATE('80/05/05', 'RR/MM/DD'), 'YYYY-MM-DD') FROM dual; -- 1980-05-05

SELECT TO_DATE('48/05/05', 'RR/MM/DD') FROM dual;
SELECT TO_CHAR(TO_DATE('48/05/05', 'RR/MM/DD'), 'YYYY-MM-DD') FROM dual; -- 2048-05-05

'RR'을 사용할 땐 주의/ 연도를 반올림해 기준년도로 삼음

데이터 조작언어

INSERT,UPDATE,DELETE,SELECT,

MERGE

MERGE 기본 형식

MERGE 대상테이블명
           USING 비교할테이블 ON ( 조건 )
             WHEN MATCHED THEN
                 UPDATE SET 컬럼=값, 컬럼=값
            WHEN NOT MATCHED THEN
                INSERT [ (컬럼, 컬럼) ] VALUES (값, 값)
             ;

INTO : 데이터가 UPDATE되거나 INSERT 될 테이블 또는 뷰 지정
USING : 비교할 SOURCE 테이블 또는 뷰나 서브쿼리 지정
ON : UPDATE나 INSERT를 하게 될 조건
WHEN MATCHED : ON 조건절이 참인 행에 수행할 내용
WHEN NOT MATCHED : ON 조건절에 맞는 행이 없을 때 수행

데이터 제약조건

관련 데이터 사전

USER_CONSTRAINTS : 사용자의 테이블에 지정된 제약 조건에 대한 정보
EX) 제약 조건 이름, 제약 조건 타입

USER_CONS_COLUMNS : 현재 사용자가 가지고 있는 컬럼의 제약 조건에 대한 정보

조인

INNER JOIN

• EQUI JOIN
• NATURAL JOIN
• CROSS JOIN
• SELF JOIN
• NON-EQUI JOIN

OUTER JOIN

• LEFR OUTER JOIN
• RIGHT OUTER JOIN
• FULL OUTER JOIN

시퀀스

유일한 정수 값을 연속적으로 생성시키기 위해 사용하는 객체
COMMIT과 ROLLBACK과 무관하게 시퀀스는 증가함

-- 1부터 1씩 증가하는 시퀀스 작성
 CREATE SEQUENCE test_seq1
 INCREMENT BY 1
 START WITH 1
 NOMAXVLAUE
 NOCYCLE
 NOCACHE;

INCREMENT BY : 시퀀스 번호 간격. 생략하면 1. 양수면 증가 음수면 감소
START WITH : 생성될 첫번째 시퀀스 번호. 기본값은 시퀀스의 최소값
MAXVALUE : 시퀀스가 생성 가능한 최대값
MINVALUE : 시퀀스가 생성 가능한 최소값
CYCLE : 시퀀스가 최소 또는 최대값에 도달할 경우 반대로 돌아가 값을 계속 생성
NOCYCLE : 최소 또는 최대값에 도달한 이후 더이상 값을 생성하지 X
CACHE : 데이터베이스가 사전에 할당하고 메모리에 보관하는 시퀀스 수의 값
NOCACHE : 사전값이 할당되지 않음. CACHE와 NOCACHE 모두 생략 시 기본 20개의 시퀀스 번호 캐시