10 Subquery, Merge, Primary Key, Foreign Key

ryunyryuny 2023. 10. 19. 13:19

1. 테이블을 생성 할 수 있는 권한 확인
CREATE TABLE 시스템권한
SELECT * FROM user_sys_privs;   -- 내가 dba로부터 직접 받은 시스템권한을 확인
SELECT * FROM session_roles;    -- 내가 받은 롤에 대한 정보를 확인
SELECT * FROM role_sys_privs;   -- 내가 받은 롤안에 시스템권한 확인
SELECT * FROM session_privs;

2. 테이블스페이스를 사용할 수 있는 권한, DEFAULT TABLESPACE 확인
SELECT * FROM user_ts_quotas;   -- 테이블을 저장할 수 있는 테이블스페이스 권한
SELECT * FROM user_users;       -- 테이블스페이스 지정여부 확인

3. CTAS를 서브쿼리에 사용할 수 있는 테이블에 대해서 SELECT 객체권한이 있어야 한다.
SELECT * FROM user_tab_privs;   -- 내가 부여한, 받은 객체권한 확인
SELECT * FROM role_tab_privs;   -- 내가 받은 롤안에 객체권한 확인

<insa session>
CREATE TABLE insa.employees 
TABLESPACE users
AS
SELECT *
FROM hr.employees;

CREATE TABLE insa.mgr(
		id number(3),
		name varchar2(30),
		day date)
TABLESPACE users;

desc mgr

INSERT INTO insa.mgr(id, name, day)	-- transacion 시작 시점
VALUES(100,'홍길동',sysdate);

INSERT INTO insa.mgr(id, name, day) 
VALUES(101,'박찬호',to_date('2001,01-01','yyyy-mm-dd');

SELECT * FROM insa.mgr;			-- 미리보기

ROLLBACK;				-- transaction 시작시점까지 영구히 취소, transaction 종료
또는
COMMIT;					-- transaction 시작시점까지 영구히 저장, transaction 종료

SELECT * FROM insa.mgr;

[문제] hr.employees테이블에 있는 데이터 중에 관리자 사원들의 
    employee_id, last_name, hire_date를 insa.mgr테이블의 데이터를 이행해 주세요.

SELECT * FROM insa.mgr;

INSERT INTO insa.mgr(id, name, day)
SELECT employee_id, last_name, hire_date
FROM hr.employees e
WHERE EXISTS (SELECT 'X' 
                FROM hr.employees 
                WHERE manager_id = e.employee_id);
                
SELECT * FROM insa.mgr;

COMMIT;

------------------------------
DROP TABLE insa.mgr PURGE;

SELECT * FROM tab;  -- 내가 가진 테이블의 목록

DROP TABLE insa.emp PURGE;

-- sample
CREATE TABLE insa.emp (
                    id number(3),
                    name varchar(60),
                    dept_id number(3),
                    dept_name varchar2(30)
                    )
TABLESPACE users;

desc insa.emp

INSERT INTO insa.emp(id, name)
SELECT employee_id, last_name||' '||first_name
FROM hr.employees;

SELECT * FROM insa.emp;

COMMIT;


SELECT * 
FROM insa.emp 
WHERE id = 100;

UPDATE insa.emp 
SET name = NULL 
WHERE id = 100;

COMMIT;

■ UPDATE SUBQUERY

SELECT * 
FROM insa.emp      
WHERE id = 100;					-- 이름 날라감

SELECT last_name||' '||first_name		-- 원본 데이터 확인
FROM hr.employees
WHERE employee_id = 100;

UPDATE insa.emp
SET name = (SELECT last_name||' '||first_name	-- 업데이트
            FROM hr.employees
            WHERE employee_id = 100)
WHERE id = 100;

SELECT * 
FROM insa.emp					-- 수정확인
WHERE id = 100;

COMMIT;

SELECT *
FROM insa.emp;				-- dept_id 정보가 없다

UPDATE insa.emp
SET dept_id = (SELECT department_id
                FROM hr.employees
                WHERE employee_id = 100)
WHERE id = 100;
...					-- 이런식이면 행개수만큼 반복해야한다.

↓
UPDATE insa.emp
SET dept_id = (SELECT department_id
                FROM hr.employees
                WHERE employee_id = 자기자신의 사원번호)
↓
SELECT employee_id, department_id
FROM hr.employees e
WHERE EXISTS (SELECT 'X'
                FROM insa.emp
                WHERE id = e.employee_id);
↓
SELECT *
FROM insa.emp i
WHERE EXISTS (SELECT 'X'
                FROM hr.employees
                WHERE employee_id = i.id);
↓
★ Correlated Subquery를 이용한 UPDATE 문 ★
UPDATE insa.emp i
SET dept_id = (SELECT department_id
                FROM hr.employees 
                WHERE employee_id = i.id);	-- 수행

SELECT * FROM insa.emp;

COMMIT;

예) insa.department_name을 수정하려고 합니다.
	hr.departments테이블에 있는 department_name을 값으로 수정하려고 합니다.

SELECT *
FROM hr.departments d
WHERE EXISTS (SELECT 'X'
                FROM insa.emp
                WHERE dept_id = d.department_id);  
	-- 오류 : departments 테이블 권한이 없다.
                
SELECT * FROM user_tab_privs;	-- 객체권한 확인

-- <hr.session>
GRANT SELECT ON hr.departments TO insa;
	-- hr이 insa에 departments 테이블의 select 권한 부여
SELECT * FROM user_tab_privs;
	-- 권한 확인

SELECT *
FROM insa.emp i
WHERE EXISTS (SELECT 'X'
                FROM hr.departments
                WHERE department_id = i.dept_id);
↓
UPDATE insa.emp i
SET dept_name = (SELECT department_name
                FROM hr.departments
                WHERE department_id = i.dept_id);	-- 업데이트

SELECT * FROM insa.emp;

COMMIT;

■ DELETE SUBQUERY

SELECT *
FROM hr.employees
WHERE hire_date < to_date('2003-01-01','yyyy-mm-dd');
-- 데이터 확인

SELECT *
FROM insa.emp
WHERE id IN (SELECT employee_id
                FROM hr.employees
                WHERE hire_date < to_date('2003-01-01','yyyy-mm-dd'));
-- IN 사용 / insa.emp 테이블에 같은 조건의 정보가 있는가

DELETE FROM insa.emp
WHERE id IN (SELECT employee_id
                FROM hr.employees
                WHERE hire_date < to_date('2003-01-01','yyyy-mm-dd'));
-- insa.emp 테이블의 조건에 맞는 데이터 삭제

ROLLBACK;   -- 취소


SELECT id
FROM insa.emp i
WHERE EXISTS (SELECT 'X'
                FROM hr.employees
                WHERE hire_date < to_date('2003-01-01','yyyy-mm-dd')
                AND employee_id = i.id);
-- EXISTS 사용 / insa.emp 테이블에 같은 조건의 정보가 있는가
↓
★ Correlated Subquery를 이용한 Delete 문 ★
DELETE FROM insa.emp i
WHERE EXISTS (SELECT 'X'
                FROM hr.employees
                WHERE hire_date < to_date('2003-01-01','yyyy-mm-dd')
                AND employee_id = i.id);
-- insa.emp 테이블의 조건에 맞는 데이터 삭제

ROLLBACK;   -- 취소

[문제] insa.emp에 있는 사원중에 job_id를 한번이라도 바꾼 사원정보를 삭제해주세요.

SELECT * FROM user_tab_privs;			-- 객체 권한 확인

SELECT *
FROM insa.emp i
WHERE EXISTS (SELECT 'X'
                FROM hr.job_history
                WHERE employee_id = i.id);	-- 데이터확인

DELETE 
FROM insa.emp i
WHERE EXISTS (SELECT 'X'
                FROM hr.job_history
                WHERE employee_id = i.id);	-- 삭제

------------------------------
ROLLBACK;	-- 취소

DELETE FROM insa.emp;			-- transaction 시작
SELECT * FROM insa.emp;

CREATE TABLE insa.test(id number);	-- CREATE 문장 내부적 AUTO COMMIT 작용 ★
SELECT * FROM insa.test;

ROLLBACK;				-- transaction 종료
SELECT * FROM insa.emp;			-- 데이터 없다 -> 복구안됨 ★

■ AOTU COMMIT이 발생 할 때
- DDL(CREATE, ALTER, DROP, RENAME, TRUNCATE, COMMENT)
- DCL(GRANT, REVOKE)
- SQLPLUS에서 EXIT를 수행해서 종료를 하면 자동 COMMIT을 가지고 있다.
└ SQL Command Line
- SQLPLUS에서 conn을 이용해서 새롭게 session을 접속하는 경우 자동 COMMIT을 가지고 있다.

예)
SQL> conn insa/oracle
SQL> delete from insa.emp;
Sql> conn hr/hr -- 자동 commit을 수행한다.

★ 꼭! DML과 DDL 또는 DCL은 같은 session에서 수행하지 말자.

■ 자동 ROLLBACK 발생 할 때
- SQLPLUS를 비정상적인 종료할 때(창닫기)
- DML작업을 수행하고 있는 컴퓨터가 비정상적인 종료할 때
- CLIENT-SERVER 환경에서 NETWORK장애가 발생할 경우

■ 다중테이블 INSERT
- SOURCE TABLE에서 데이터를 추출해서 여러개의 TARGET TABLE에 데이터를 로드(INSERT)하는 INSERT 문
- ETL(Extraction(추출), Transformation(변형), Loading(적재)

1. 무조건 INSERT ALL

<hr session>
CREATE TABLE hr.sal_history		-- Table1 구조만 생성
AS
SELECT employee_id, hire_date, salary
FROM hr.employees
WHERE 1=2;

CREATE TABLE hr.mgr_history		-- Table2 구조만 생성
AS
SELECT employee_id, manager_id, salary
FROM hr.employees
WHERE 1=2;


INSERT INTO hr.sal_history(employee_id, hire_date, salary)	-- 데이터 복제
SELECT employee_id, hire_date, salary
FROM hr.employees;

INSERT INTO hr.mgr_history(employee_id, manager_id, salary)	-- 데이터 복제
SELECT employee_id, manager_id, salary
FROM hr.employees;
				--> 문제점 : hr.employees 테이블의 작업 반복 > 과부하
ROLLBACK;

↓
INSERT ALL 
INTO hr.sal_history(employee_id, hire_date, salary) VALUES(id, day, sal)
INTO hr.mgr_history(employee_id, manager_id, salary) VALUES(id, mgr, sal)
SELECT employee_id id, hire_date day, manager_id mgr, salary*1.1 sal
FROM hr.employees;

SELECT * FROM hr.sal_history;
SELECT * FROM hr.mgr_history;

ROLLBACK;
또는
COMMIT;

2. 조건 INSERT ALL

-- 테이블 생성
CREATE TABLE hr.emp_history
AS
SELECT employee_id, hire_date, salary
FROM hr.employees
WHERE 1 = 2;

CREATE TABLE hr.emp_sal
AS
SELECT employee_id, commission_pct, salary
FROM hr.employees
WHERE 1 = 2;

SELECT * FROM hr.emp_history;
SELECT * FROM hr.emp_sal;

-- 데이터 삽입
INSERT ALL
WHEN day < to_date('2005-01-01','yyyy-mm-dd') AND sal >= 5000 THEN
    INTO hr.emp_history(employee_id, hire_date, salary) VALUES(id,day,sal)
WHEN comm IS NOT NULL THEN
    INTO hr.emp_sal(employee_id, commission_pct, salary) VALUES(id,comm,sal)
SELECT employee_id id, hire_date day, commission_pct comm, salary sal
FROM hr.employees;

SELECT * FROM hr.emp_history;
SELECT * FROM hr.emp_sal;

-- 교집합
SELECT employee_id FROM hr.emp_history
INTERSECT
SELECT employee_id FROM hr.emp_sal;

SELECT *
FROM hr.emp_history e
WHERE EXISTS (SELECT 'X'
                FROM hr.emp_sal
                WHERE employee_id = e.employee_id);

3. 조건 FIRST INSERT

-- 테이블 생성
CREATE TABLE hr.sal_low
AS
SELECT employee_id, last_name, salary
FROM hr.employees
WHERE 1 = 2;

CREATE TABLE hr.sal_mid
AS
SELECT employee_id, last_name, salary
FROM hr.employees
WHERE 1 = 2;

CREATE TABLE hr.sal_high
AS
SELECT employee_id, last_name, salary
FROM hr.employees
WHERE 1 = 2;

SELECT * FROM hr.sal_low;
SELECT * FROM hr.sal_mid;
SELECT * FROM hr.sal_high;

-- 원본 데이터
SELECT employee_id id, last_name name, salary sal
FROM hr.employees;

-- 데이터 추가
INSERT FIRST
WHEN sal < 5000 THEN
    INTO hr.sal_low(employee_id, last_name, salary) VALUES(id,name,sal)
WHEN sal BETWEEN 5000 AND 10000 THEN
    INTO hr.sal_mid(employee_id, last_name, salary) VALUES(id,name,sal)
ELSE 
    INTO hr.sal_high(employee_id, last_name, salary) VALUES(id,name,sal)
SELECT employee_id id, last_name name, salary sal
FROM hr.employees;

SELECT * FROM hr.sal_low;
SELECT * FROM hr.sal_mid;
SELECT * FROM hr.sal_high;

# 기존 테이블에 컬럼을 추가하는 방법
ALTER TABLE 소유자.테이블명 ADD 칼럼명 데이터형(크기);

-- 테이블 생성
CREATE TABLE hr.oltp_emp
AS
SELECT employee_id, last_name, salary, department_id
FROM hr.employees;

CREATE TABLE hr.dw_emp
AS
SELECT employee_id, last_name, salary, department_id
FROM hr.employees
WHERE department_id = 20;

SELECT * FROM hr.oltp_emp;
SELECT * FROM hr.dw_emp;

desc hr.oltp_emp

-- 칼럼 추가
ALTER TABLE hr.oltp_emp ADD flag char(1);

desc hr.oltp_emp

SELECT * FROM hr.oltp_emp WHERE employee_id IN (201,202);
SELECT * FROM hr.dw_emp;

-- 업데이트
UPDATE hr.oltp_emp
SET flag = 'd'
WHERE employee_id = 202;

UPDATE hr.oltp_emp
SET salary = 20000
WHERE employee_id = 201;

COMMIT;

SELECT * FROM hr.oltp_emp WHERE employee_id IN (201,202);
SELECT * FROM hr.dw_emp;

[문제] hr.oltp_emp에 있는 사원들 중에 hr.dw_emp테이블에 존재하는 사원들의 정보를 출력.

SELECT *
FROM hr.oltp_emp o
WHERE EXISTS (SELECT 'X'
                FROM hr.dw_emp
                WHERE employee_id = o.employee_id);

[문제] hr.dw_emp에 있는 사원들 중에 hr.oltp_emp테이블에 존재하는 사원들의 정보를 출력.

SELECT *
FROM hr.dw_emp w
WHERE EXISTS (SELECT 'X'
                FROM hr.oltp_emp
                WHERE employee_id = w.employee_id);

[문제] hr.dw_emp에 있는 사원들 중에 hr.oltp_emp에 존재하는 사원들은 hr.oltp_emp의
    급여를 기준으로 10% 인상한 값으로 수정해주세요.

UPDATE hr.dw_emp o
SET salary = (SELECT salary * 1.1
                FROM hr.oltp_emp
                WHERE employee_id = o.employee_id);

SELECT * FROM hr.dw_emp;

[문제] hr.dw_emp에 있는 사원중에 hr.oltp_emp에 존재하는 사원에 flag칼럼의 값이
    'd'인 사원에 대해서 삭제해주세요.

SELECT *
FROM hr.dw_emp o
WHERE EXISTS (SELECT 'X'
                FROM hr.oltp_emp
                WHERE employee_id = o.employee_id
                AND flag = 'd');

DELETE
FROM hr.dw_emp o
WHERE EXISTS (SELECT 'X'
                FROM hr.oltp_emp
                WHERE employee_id = o.employee_id
                AND flag = 'd');

SELECT * FROM hr.dw_emp;

[문제] hr.oltp_emp테이블에 있는 데이터 중에 hr.dw_emp테이블에 존재하지 않은 데이터들을
    hr.dw_emp테이블에 로드해주세요.

SELECT *
FROM hr.oltp_emp o
WHERE NOT EXISTS (SELECT 'X'
                    FROM hr.dw_emp
                    WHERE employee_id = o.employee_id);

INSERT INTO hr.dw_emp(employee_id, last_name, salary, department_id)
SELECT employee_id, last_name, salary, department_id
FROM hr.oltp_emp o
WHERE NOT EXISTS (SELECT 'X'
                    FROM hr.dw_emp
                    WHERE employee_id = o.employee_id);

SELECT * FROM hr.oltp_emp;

■ MERGE(9i)
INSERT, UPDATE, DELETE문을 한꺼번에 수행하는 SQL문

SELECT * FROM hr.dw_emp;	-- Target Table: INSERT, UPDATE, DELETE 권한
SELECT * FROM hr.oltp_emp;	-- Source Table: SELECT 권한

MERGE INTO hr.dw_emp d		-- Target Table
USING hr.oltp_emp o		-- Source Table
ON (d.employee_id = o.employee_id)
WHEN MATCHED THEN		-- 매치가 되면
	UPDATE SET		-- Target Table은 위에 적었기 때문에
		d.salary = o.salary * 1.1
	DELETE WHERE o.flag = 'd'
WHEN NOT MATCHED THEN		-- 매치가 안되면
	INSERT (d.employee_id, d.last_name, d.salary, d.department_id)
	VALUES (o.employee_id, o.last_name, o.salary, o.department_id);

SELECT * FROM hr.dw_emp;

commit;
or
rollback;

DROP TABLE hr.emp PURGE;

CREATE TABLE hr.emp(id number, name varchar2(30), day date);
desc hr.emp

SELECT * FROM user_tables WHERE table_name = 'emp';
SELECT * FROM user_tab_columns WHERE table_name = 'EMP';

■ 컬럼 추가

ALTER TABLE hr.emp ADD job_id varchar2(30);

■ 컬럼 타입, 사이즈를 수정

ALTER TABLE hr.emp MODIFY job_id varchar2(20); -- 문자형

ALTER TABLE hr.emp MODIFY job_id char(30); -- 문자형 -> 문자형 가능

SELECT * FROM user_tab_columns WHERE table_name = 'emp';
desc hr.emp

ALTER TABLE hr.emp MODIFY job_id number;
# job_id 문자 타입의 컬럼을 숫자 타입의 컬럼으로 수정이 가능한 이유는
데이터가 없기 때문에 가능하다.

■ 컬럼 삭제

ALTER TABLE hr.emp DROP COLUMN job_id;

SELECT * FROM user_tab_columns WHERE table_name = 'EMP';

desc hr.emp

INSERT INTO hr.emp(id,name,day) VALUES(1,'홍길동',sysdate);
INSERT INTO hr.emp(id,name,day) VALUES(1,'박찬호',sysdate);
INSERT INTO hr.emp(id,name,day) VALUES(null,'송흥민',sysdate);
	-- 사원번호 중복 >> 데이터품질저하

SELECT * FROM hr.emp;

rollback;

■ 제약조건
- 테이블의 데이터에 대한 규칭을 만든다.
- 데이터에 대한 품질을 향상시기기 위해서 만든다.

1. PRIMARY KEY
- 테이블의 대표키
- UNIQUE(유일한 값), NOT NULL(NULL값은 허용할 수 없다)
- 테이블당 하나만 생성
- 자동으로 UNIQUE INDEX가 생성된다.

-- 이미 있는 테이블에 제약조건 추가
ALTER TABLE hr.emp ADD CONSTRAINT emp_id_pk PRIMARY KEY(id);

-- 제약조건 정보 확인
SELECT * FROM user_constraints WHERE table_name = 'EMP';
SELECT * FROM user_cons_columns WHERE table_name = 'EMP';
-- user : 내 입장에서
-- dba : dba 입장에서
-- all : 남의거

-- 인덱스 정보 확인
SELECT * FROM user_indexes WHERE table_name = 'EMP';
SELECT * FROM user_ind_columns WHERE table_name = 'EMP';

-- 데이터 추가
INSERT INTO hr.emp(id,name,day) VALUES(1,'홍길동',sysdate);
SELECT * FROM hr.emp;

INSERT INTO hr.emp(id,name,day) VALUES(1,'박찬호',sysdate);
	-- 오류 : primary key 제약조건은 unique한 값만 입력해야 한다.

INSERT INTO hr.emp(id,name,day) VALUES(null,'송흥민',sysdate);
	-- 오류 : primary key 제약조건은 null값을 입력할 수 없다.

CREATE TABLE hr.dept(dept_id number, dept_name varchar2(30));

ALTER TABLE hr.dept ADD PRIMARY KEY(dept_id);
-- 제약조건 이름을 명시하지 않으면 오라클에서 자동으로 SYS_c숫자 형식으로 생성해준다.

-- 제약조건 정보 확인
SELECT * FROM user_constraints WHERE table_name = 'DEPT';
SELECT * FROM user_cons_columns WHERE table_name = 'DEPT';

-- 인덱스 정보 확인
SELECT * FROM user_indexes WHERE table_name = 'DEPT';
SELECT * FROM user_ind_columns WHERE table_name = 'DEPT';

-- 데이터 추가
INSERT INTO hr.dept(dept_id, dept_name) VALUES(10,'총무부');
INSERT INTO hr.dept(dept_id, dept_name) VALUES(20,'인사팀');
SELECT * FROM hr.dept;
COMMIT;

desc hr.emp
ALTER TABLE hr.emp ADD dept_id number;

INSERT INTO hr.emp(id,name,day,dept_id) VALUES(1,'홍길동',sysdate,10);
SELECT * FROM hr.emp;

INSERT INTO hr.emp(id,name,day,dept_id) VALUES(2,'윤건',sysdate,30);
	-- 문제점 : 부서코드에 없는 30 코드를 등록함

rollback;

2. FOREIGN KEY
- 외래키, 참조무결성 제약조건
- 동일한 테이블이나 다른 테이블의 PRIMARY KEY, UNIQUE 제약조건을 참조한다.
- 데이터 품질
- 중복값 허용, NULL 허용
- 종속되는 행을 삭제할 수 없다.
- 참조를 당하는 테이블은 삭제할 수 없다.

-- 이미 있는 테이블에 제약조건 추가
ALTER TABLE hr.emp ADD CONSTRAINT emp_dept_id_fk
FOREIGN KEY(dept_id) REFERENCES hr.dept(dept_id);
			-- 참조 테이블(칼럼)은 먼저 제약조건이 걸려 있어야 한다.
                        
-- 제약조건 정보 확인
SELECT * FROM user_constraints WHERE table_name = 'EMP';
SELECT * FROM user_cons_columns WHERE table_name = 'EMP';

INSERT INTO hr.emp(id,name,day,dept_id) VALUES(1,'홍길동',sysdate,10);
SELECT * FROM hr.emp;

INSERT INTO hr.emp(id,name,day,dept_id) VALUES(2,'윤건',sysdate,30);
	-- 오류 : PRIMARY KEY 없는 데이터가 입력되면 안된다.

COMMIT;

DELETE FROM hr.dept WHERE dept_id = 10;
	-- 오류 : foreign key 제약조건이 걸려 있기 때문에 참조하는 자식 데이터가 있으면 삭제가 안된다.

DELETE FROM hr.dept WHERE dept_id = 20;
	-- 삭제수행 : foreign key 제약조건이 걸려 있더라도 참조하는 자식 데이터가 없으면 삭제가 가능하다.

DROP TABLE hr.dept PURGE;
	-- 오류 : foreign key 제약조건이 걸려 있기 때문에 참조하는 자식 데이터가 있으면 삭제가 안된다.

저작자표시

'Oracle SQL' 카테고리의 다른 글

12 Sequence, Synonym, Extract, 날짜함수, 분석함수, Rank (0)	2023.10.23
11 Unique, Check, Not Null, Rename, Flashback, Truncate, Comment, View (0)	2023.10.20
09-2 권한, 테이블, INSERT, UPDATE, DELETE (0)	2023.10.18
09 WITH (0)	2023.10.18
08 집합연산자, 그룹화, 계층검색 (0)	2023.10.17

'Oracle SQL'의 다른글

현재글10 Subquery, Merge, Primary Key, Foreign Key

Record.

10 Subquery, Merge, Primary Key, Foreign Key

'Oracle SQL' 카테고리의 다른 글

티스토리툴바