SQL 13일차

한희수·2023년 3월 30일
sql
0

빅데이터 분석 SQL

목록 보기
13/17

20230330 SQL

▶ 제약조건 다섯 가지
PRIMARY KEY
FOREIGN KEY
UNIQUE
CHECK
NOT NULL
-- 이렇게 다섯 개임. 이 외 업무별 제약 조건은 TRIGGER를 이용해서 만듦

<차례>
❍ 제약조건 삭제

❍ UNIQUE 제약조건
❍ CHECK 제약조건
❍ NOT NULL 제약조건

■ 테이블 생성시 제약조건 함께 생성
■ 테이블 이름 수정
■ 컬럼 이름 수정
■ 제약조건 이름 수정
■ 인덱스 이름 수정
■ FLASHBACK TABLE
❍ 삭제한 테이블 복원
● 제약조건 이름 수정
● 인덱스 이름 수정
■ TRUNCATE
■ COMMENT
❍ 테이블 주석 확인/생성/삭제
❍ 컬럼 주석 확인/생성/삭제
■ VIEW
CTAS 만드는 것과 같음
● VIEW인지 TABLE인지 여부 확인 방법
❍ VIEW를 삭제하는 방법
❍ VIEW를 수정하는 방법
❍ VIEW에 신규 데이터 INSERT 하기
❍ VIEW에 신규 데이터 UPDATE 하기
❍ VIEW에 신규 데이터 DELETE 하기
■ 단순 VIEW
■ 복합 VIEW

DROP TABLE hr.emp CASCADE CONSTRAINT PURGE;
DROP TABLE hr.dept CASCADE CONSTRAINT PURGE;

=> 나를 참조하는 foreign key 제약조건 삭제하고 drop 하겠음
(제약 조건 없는 테이블 삭제시에도 사용 가능)

CREATE TABLE hr.emp(id number, name varchar2(30), day date, dept_id number);
ALTER TABLE hr.emp ADD constraint emp_id_pk PRIMARY KEY(id);

SELECT*FROM user_constraints;

SELECT*FROM user_constraints WHERE table_name = 'EMP';
SELECT*FROM user_cons_columns WHERE table_name = 'EMP';
SELECT*FROM user_indexes WHERE table_name = 'EMP';
SELECT*FROM user_ind_columns WHERE table_name = 'EMP';

CREATE TABLE hr.dept(dept_id number, dept_name varchar2(30));
ALTER TABLE hr.dept ADD CONSTRAINT dept_id_pk PRIMARY KEY(dept_id); -> 못 만듦
ALTER TABLE hr.dept ADD CONSTRAINT dept_dept_id_pk PRIMARY KEY(dept_id);

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 IN ('EMP','DEPT');
SELECT*FROM user_cons_columns WHERE table_name IN ('EMP','DEPT');
SELECT*FROM user_indexes WHERE table_name IN ('EMP','DEPT');
SELECT*FROM user_ind_columns WHERE table_name IN ('EMP','DEPT');

--- foreign key는 primary key, unique key를 참조하는 제약조건
1) 다른 범위의 값 입력하는 것 방지(데이터 품질 굿)
2) 참조 대상인 pk 삭제되지 않도록 방지

INSERT INTO hr.dept(dept_id, dept_name) VALUES(10, '총무부');
INSERT INTO hr.dept(dept_id, dept_name) VALUES(20, '분석팀');

COMMIT;

INSERT INTO hr.emp(id, name, day, dept_id) VALUES(1, '홍길동', sysdate, 10);
INSERT INTO hr.emp(id, name, day, dept_id) VALUES(1, '나얼', sysdate, 10); -> 안돼
오류 보고 -
ORA-00001: unique constraint (HR.EMP_ID_PK) violated
INSERT INTO hr.emp(id, name, day, dept_id) VALUES(2, '나얼', sysdate, 30); -> 안돼
오류 보고 -
ORA-02291: integrity constraint (HR.EMP_DEPT_ID_FK) violated - parent key not found

5-6 텍스트 마이닝
5-7 머신러닝 딥러닝
8 R
9 최종 프로젝트

[도서] 데이터 품질 — 90년도 발간

INSERT INTO hr.emp(id, name, day, dept_id) VALUES(2, '나얼', sysdate, NULL);
1 행 이(가) 삽입되었습니다. => NULL, 중복값 허용
--- foreign key 제약조건에는 index 안 걸림

--- 데이터가 있을 때 pk를 걸기 어려울 수 있음(모든 값이 unique 해야 함)

❍ 제약조건 삭제
ALTER TABLE hr.emp DROP CONSTRAINT emp_id_pk; - 다른 제약조건 삭제시 이름 명시
ALTER TABLE hr.emp DROP PRIMARY KEY; --- pk 제약조건 삭제시 사용

ALTER TABLE hr.dept DROP PRIMARY KEY; --- 오류남
오류 보고 -
ORA-02273: this unique/primary key is referenced by some foreign keys
02273. 00000 - "this unique/primary key is referenced by some foreign keys"
Cause: Self-evident.
Action: Remove all references to the key before the key is to be dropped.
=> 나를 참조하고 있는 foreign key 제약조건이 있어서 오류 발생

해결방법 1)
ALTER TABLE hr.emp DROP CONSTRAINT emp_dept_id_fk;
--- 나를 참조하는 fk 제약조건 삭제
ALTER TABLE hr.dept DROP PRIMARY KEY;

해결방법 2)
ALTER TABLE hr.dept DROP PRIMARY KEY CASCADE;
--- CASCADE 옵션을 사용하면 나를 참조하는 제약조건을 모두 삭제한 후 제약조건 삭제

❍ UNIQUE 제약조건 --- (실 예)중복 아이디 체크

  • 유일한 값만 체크
  • NULL허용(pk는 null 허용하지 않음)
  • 자동으로 UNIQUE INDEX 생성(pk와 unique 만 생성)

SELECT*FROM hr.dept;
ALTER TABLE hr.dept ADD CONSTRAINT dept_name_uk UNIQUE(dept_name);

INSERT INTO hr.dept(dept_id, dept_name) VALUES(30, '분석팀'); --- 오류 나옴, uk 위반
오류 보고 -
ORA-00001: unique constraint (HR.DEPT_NAME_UK) violated

INSERT INTO hr.dept(dept_id, dept_name) VALUES(30, NULL); --- uk NULL은 허용

■ UNIQUE 제약조건 삭제하는 방법
ALTER TABLE hr.dept DROP CONSTRAINT dept_name_uk;
또 는
ALTER TABLE hr.dept DROP UNIQUE(dept_name);

❍ CHECK 제약조건

  • 조건의 값이 TRUE인 경우 INSERT, UPDATE 할 수 있도록 만드는 제약조건
  • NULL 값을 허용함
  • 중복되는 값을 허용함

ALTER TABLE hr.emp ADD sal NUMBER;
desc hr.emp;
SELECT*FROM hr.emp;

ALTER TABLE hr.emp ADD CONSTRAINT emp_sal_ck CHECK(sal >= 1000 AND sal <= 2000);
또 는
ALTER TABLE hr.emp ADD CONSTRAINT emp_sal_ck CHECK(sal BETWEEN 1000 AND 2000);

INSERT INTO hr.emp(id, name, day, dept_id, sal) VALUES(3, '윤건', sysdate, 20, 500);
오류 보고 -
ORA-02290: check constraint (HR.EMP_SAL_CK) violated
--- CHECK 제약조건에 조건식의 결과값이 false이기 때문에 오류 발생

INSERT INTO hr.emp(id, name, day, dept_id, sal) VALUES(3, '윤건', sysdate, 20, NULL);
--- CHECK 제약조건은 NULL값을 허용함

INSERT INTO hr.emp(id, name, day, dept_id, sal) VALUES(3, '윤건', sysdate, 20, 1500);
1 행 이(가) 삽입되었습니다.

UPDATE hr.emp
SET sal = 500
WHERE id = 1;
오류 보고 -
ORA-02290: check constraint (HR.EMP_SAL_CK) violated

■ CHECK 제약조건 삭제하는 방법
ALTER TABLE hr.emp DROP CONSTRAINT emp_sal_ck;
SELECT*FROM user_constraints WHERE table_name IN ('EMP','DEPT');

❍ NOT NULL 제약조건

  • NULL 값을 허용할 수 없는 제약조건

SELECT*FROM user_constraints WHERE table_name IN ('EMP','DEPT');
desc hr.emp;

  • NOT NULL 제약조건은 MODIFY를 이용해서 추가해야 함(ADD CONSTRAINT 아님!!!!!)

ALTER TABLE hr.dept MODIFY dept_name CONSTRAINT dept_name_notnull NOT NULL;
SELECT*FROM user_constraint WHERE table_name IN ('EMP', 'DEPT');
DESC hr.dept

SELECT*FROM hr.dept;
INSERT INTO hr.dept(dept_id, dept_name) VALUES (30, NULL); -- 오류
오류 보고 -
ORA-01400: cannot insert NULL into ("HR"."DEPT"."DEPT_NAME")

INSERT INTO hr.dept(dept_id, dept_name) VALUES (30, '총무부'); -- 수행됨
ROLLBACK;

ALTER TABLE hr.dept ADD CONSTRAINT dept_name_uk UNIQUE(dept_name);

INSERT INTO hr.dept(dept_id, dept_name) VALUES (30, '총무부');
오류 보고 -
ORA-00001: unique constraint (HR.DEPT_NAME_UK) violated
=> dept_name 컬럼의 NOT NULL, UNIQUE 제약조건이 생성되어 있어서 NULL값 또는 중복되는 값이 입력되면 오류 발생

■ NOT NULL 제약조건 삭제하는 방법
SELECT*FROM user_constraints WHERE table_name IN ('EMP', 'DEPT');

ALTER TABLE hr.dept DROP CONSTRAINT dept_name_notnull;
또 는
ALTER TABLE hr.dept MODIFY dept_name NULL;

DROP TABLE hr.emp CASCADE CONSTRAINT PURGE;
DROP TABLE hr.dept CASCADE CONSTRAINT PURGE;

■ 테이블 생성시 제약조건 함께 생성

  • NOT NULL 제약조건은 열 레벨 정의만 가능
  • 다른 제약조건은 열, 테이블 레벨 정의 모두 가능
  • FK 제약조건은 열 레벨에서는 FOREIGN KEY 작성X, 테이블 레벨에서는 작성해야 함

CREATE TABLE hr.dept
(dept_id number CONSTRAINT dept_dept_id_pk PRIMARY KEY,
dept_name varchar2(30));

CREATE TABLE hr.emp(
id number CONSTRAINT emp_id_pk PRIMARY KEY, -- 열 레벨 정의
name varchar(30) CONSTRAINT emp_name_nn NOT NULL,
sal number,
dept_id number CONSTRAINT emp_dept_id_fk REFERENCES hr.dept(dept_id),
CONSTRAINT emp_name_uk UNIQUE(name), -- 테이블 레벨 정의
CONSTRAINT emp_sal_ck CHECK(sal BETWEEN 1000 AND 2000));

--- NOT NULL 제약조건은 열 레벨 정의만 가능

DROP TABLE hr.emp CASCADE CONSTRAINT PURGE;

● id number PRIMARY KEY를 열 레벨 -> 테이블 레벨 정의로 내리고 싶음
CREATE TABLE hr.emp(
id number,
name varchar(30) CONSTRAINT emp_name_nn NOT NULL,
sal number,
dept_id number CONSTRAINT emp_dept_id_fk REFERENCES hr.dept(dept_id),
CONSTRAINT emp_id_pk PRIMARY KEY(id),
CONSTRAINT emp_name_uk UNIQUE(name),
CONSTRAINT emp_sal_ck CHECK(sal BETWEEN 1000 AND 2000));

● dept_id number FOREIGN KEY를 열 레벨 -> 테이블 레벨 정의로 내리고 싶음
--- 이 경우 CONSTRAINT ... FOREIGN KEY ... REFERENCES 로 작성
--- 열 레벨 정의: FOREIGN KEY 작성X, 테이블 레벨 정의: FOREIGN KEY 작성O

CREATE TABLE hr.emp(
id number,
name varchar(30) CONSTRAINT emp_name_nn NOT NULL,
sal number,
dept_id number,
CONSTRAINT emp_id_pk PRIMARY KEY(id),
CONSTRAINT emp_name_uk UNIQUE(name),
CONSTRAINT emp_sal_ck CHECK(sal BETWEEN 1000 AND 2000),
CONSTRAINT emp_dept_id_fk FOREIGN KEY(dept_id) REFERENCES hr.dept(dept_id));

언니 이거 키보드 가져온??? ~
아니야아아아 키보드 파업!!

■ 테이블 이름 수정
1) RENAME 기존 이름 TO 새로운 이름;
2) ALTER TABLE 기존 이름 RENAME TO 새로운 이름;

(테이블 만들기)
INSERT INTO hr.dept(dept_id, dept_name) VALUES (10, '총무부');
INSERT INTO hr.dept(dept_id, dept_name) VALUES (20, '분석팀');
COMMIT;
SELECT*FROM hr.dept;

INSERT INTO hr.emp(id, name, sal, dept_id) VALUES(1, '홍길동', 2000, 10);
INSERT INTO hr.emp(id, name, sal, dept_id) VALUES(2, '나얼', 1000, 20);
COMMIT;
SELECT*FROM hr.emp;

RENAME hr.emp to hr.emp_new; -- 오류(소유자 이름은 사용할 수 없음)
오류 보고 -
ORA-01765: specifying owner's name of the table is not allowed

((방법 1))
RENAME emp TO emp_new; -- 테이블 이름이 변경되었습니다.

SELECT*FROM emp_new;
SELECT*FROM user_tables WHERE table_name = 'EMP_NEW';
SELECT*FROM user_constraints WHERE table_name = 'EMP_NEW';
SELECT*FROM user_cons_columns WHERE table_name = 'EMP_NEW';
SELECT*FROM user_indexes WHERE table_name = 'EMP_NEW';
SELECT*FROM user_ind_columns WHERE table_name = 'EMP_NEW';

((방법 2))
ALTER TABLE emp_new RENAME TO emp;

SELECT*FROM hr.emp;
SELECT*FROM user_tables WHERE table_name = 'EMP';
SELECT*FROM user_constraints WHERE table_name = 'EMP';
SELECT*FROM user_cons_columns WHERE table_name = 'EMP';
SELECT*FROM user_indexes WHERE table_name = 'EMP';
SELECT*FROM user_ind_columns WHERE table_name = 'EMP';

■ 컬럼 이름 수정

  • ALTER TABLE 테이블명 RENAME COLUMN 기존 컬럼 이름 TO 새로운 컬럼 이름;

DESC hr.emp
ALTER TABLE hr.emp RENAME COLUMN id TO emp_id;

■ 제약조건 이름 수정

  • ALTER TABLE 테이블명 RENAME CONSTRAINT 기존 제약조건 이름 TO 새로운 제약조건 이름;
  • index_name은 변경되지 않음

ALTER TABLE hr.emp RENAME CONSTRAINT emp_id_pk TO emp_empid_pk;

■ 인덱스 이름 수정

  • ALTER INDEX 인덱스 이름 RENAME TO 새로운 인덱스;

ALTER INDEX emp_id_pk RENAME TO emp_id_idx;

SELECT*FROM user_indexes WHERE table_name = 'EMP';
SELECT*FROM user_ind_columns WHERE table_name = 'EMP';

■ FLASHBACK TABLE --- 휴지통 같은 기능

  • 삭제한 테이블을 복원하는 SQL문

SHOW recyclebin; -> 내용 없으면 오류 나는 듯?
-> SP2-0564: "" 객체가 부적합합니다. 설명되지 않을 수 있습니다.

SELECT*FROM user_recyclebin; -> 3개 나옴

PURGE recyclebin; -> 휴지통 비우기, Recyclebin이(가) 비워졌습니다.

CREATE TABLE hr.emp_copy
AS
SELECT*FROM hr.employees;

SELECT*FROM user_objects WHERE object_name = 'EMP_COPY';
-> LAST_DDL_TIME, TIMESTAMP 마지막 객체 수정 날짜, 날짜/시간 확인 가능

DROP TABLE hr.emp_copy; -> 아차, 잘못 드랍을 한거예요 여러분
-> 삭제한 경우 위치 그대로(우리 눈엔 안보임), 이름을 BIN$로 변경

SELECT*FROM user_recyclebin; -> 걱정할게 없어요. 쓰레기통 확인

▽▽▽(결과창)

BIN$Sp5AWYnAThyP8NZT/OOkAQ==$0 EMP_COPY DROP TABLE USERS 2023-03-30:13:55:40 2023-03-30:13:57:42 567620 YES YES 20382 20382 20382 8

SELECT*FROM user_tables WHERE table_name = 'EMP_COPY';
SELECT*FROM user_objects WHERE object_name = 'EMP_COPY';
--> 둘다 아무것도 없음(휴지통에 넣었기 때문)

SELECT*FROM "BIN$Sp5AWYnAThyP8NZT/OOkAQ==$0";
-> 휴지통 속 이름으로 데이터 확인 가능(BIN& 시작 이름 쌍따옴표 안에 입력해줘야 함)

[메모]
CTAS 테이블 복제한 경우 테이블 구조, 데이터 그대로 & 제약조건은 not null 제약조건만 복제됨

❍ 삭제한 테이블 복원
FLASHBACK TABLE emp_copy TO BEFORE DROP;

SELECT*FROM user_recyclebin; --> 암것도 없음

ALTER TABLE hr.emp_copy RENAME CONSTRAINT "BIN$vrDh3UQeTMqv9+XEJPT33A==$0" TO emp_copy_name_nn; --> 제약조건 이름 변경

DROP TABLE hr.emp_copy PURGE;
=> purge : 영구히 테이블 삭제(recyclebin으로 들어가지 않음, 복원 불가)

(( 다시 hr.emp_copy 테이블 만들기))

CREATE TABLE hr.emp_copy
AS
SELECT*FROM hr.employees;

ALTER TABLE hr.emp_copy ADD CONSTRAINT emp_copy_id_pk PRIMARY KEY(employee_id);

SELECTFROM user_constraints WHERE table_name = 'EMP_COPY';
SELECTFROM user_cons_columns WHERE table_name = 'EMP_COPY';
SELECTFROM user_indexes WHERE table_name = 'EMP_COPY';
SELECTFROM user_ind_columns WHERE table_name = 'EMP_COPY';

purge recyclebin; --> 반복 버리기 수행 중!

DROP TABLE hr.emp_copy;

SELECT*FROM user_recyclebin;

CREATE TABLE hr.emp_copy
AS
SELECT*FROM hr.employees;

DROP TABLE hr.emp_copy; --> 또 버림!

SELECT*FROM user_recyclebin; --> 휴지통 또 확인함!

FLASHBACK TABLE hr.emp_copy TO BEFORE DROP;
Flashback을(를) 성공했습니다.

SELECT*FROM user_recyclebin; --> 복원된 것 확인 가능

----- ----- 무언가 잘못했다, INDEX가 없다!!! 하지만 갠춘 ----- -----

● 제약조건 이름 수정
ALTER TABLE hr.emp_copy RENAME CONSTRAINT “BIN$Ixigkt2DR36JjsshpjI+JQ==$0” TO emp_copy_id_pk;

● 인덱스 이름 수정
ALTER INDEX “BIN$Ixigkt2DR36JjsshpjI+JQ==$0” RENAME TO emp_copy_id_idx;

■ 계속되는 드랍과 생성, 휴지통 상태는?!

SELECT*FROM hr.emp_copy;
SELECT*FROM user_recyclebin;
purge recyclebin;

SELECT*FROM hr.emp_copy;

DROP TABLE hr.emp_copy;

CREATE TABLE hr.emp_copy
AS
SELECT*FROM hr.employees;

SELECT*FROM hr.emp_copy;

DROP TABLE hr.emp_copy;

SELECT*FROM recyclebin;

▽▽▽ (결과창)

BINz46uIcpoRHmFpNvyvDs7kg==$0 EMP_COPY DROP TABLE USERS 2023-03-30:14:16:24 2023-03-30:14:26:51 BINdFhAiNdqTMuloboHve3hBA==0EMPCOPYDROPTABLEUSERS2023−03−30:14:26:572023−03−30:14:27:32BIN0 EMP_COPY DROP TABLE USERS 2023-03-30:14:26:57 2023-03-30:14:27:32 BINT+ItvIrfT4yg0IX4Vx2frg==$0 EMP_COPY DROP TABLE USERS 2023-03-30:14:30:03 2023-03-30:14:30:05

FLASHBACK TABLE hr.emp_copy TO BEFORE DROP;
-> Flashback을(를) 성공했습니다.
(recyclebin에 동일한 이름의 테이블이 있을 경우 가장 최근에 삭제된 테이블을 복원)

FLASHBACK TABLE hr.emp_copy TO BEFORE DROP;
오류 보고 -
ORA-38312: original name is used by an existing object
->> hr.emp_copy 이름으로 이미 복원해서 복원 못 함
->> 복원해야할 테이블이 이미 존재하는 경우 이름을 수정해서 복원해야 함

FLASHBACK TABLE hr.emp_copy TO BEFORE DROP RENAME TO emp_2023;
-> Flashback을(를) 성공했습니다.
-> flashback table 유료 기능임(license 없는데 사용하면 딕셔너리에 저장, 쓰지 말라고 함)

SELECT*FROM hr.emp_2023; -> 데이터 있음!!

[메모]
--index는 따로 복원하지 않음/ 테이블 복원시 자동 복원(복원 후 이름 바꿔야 함)

■ TRUNCATE

  • 테이블의 행을 전부 삭제하는 DDL SQL문
  • DELETE문과 비슷하지만 TRUNCATE문은 저장공간을 초기화 상태로 만듦(공간을 해지; resource를 줄임)
  • TRUNCATE는 ROLLBACK을 할 수 없음
    ((내 의문)) purge와는 머가 다른가? 뒤에 나오는게 다른가?

DELETE FROM hr.emp_2023;
--> 수천만 건의 데이터는 어디로 갔나?
-->> 행을 삭제(혹은 UPDATE) 하면 UNDO TABLESPACE에 저장을 함
(이유: ROLLBACK, 읽기 일관성을 위해서 이전값은 저장함)

SELEC\T*FROM hr.emp_2023;
ROLLBACK;

TRUNCATE TABLE hr.emp_2023;
-> Table HR.EMP_2023이(가) 잘렸습니다.

SELECT*FROM hr.emp_2023; -- 데이터 없음
rollback; -- truncate 문은 테이블을 초기화 상태로 만들기 때문에 rollback 불가함
SELECT*FROM hr.emp_2023; -- 데이터 없음

● 80% 버리고 20%만 남기고 싶다면?
-->>>20% 데이터 해당 테이블 생성, rename + 제약조건(not null 이외) 생성 후 기존 테이블 삭제

■ COMMENT

  • 테이블과 컬럼의 주석(설명) 만드는 SQL문

❍ 테이블 주석 확인
SELECT*FROM user_tab_comments WHERE table_name = 'EMP_2023';

❍ 테이블 주석 생성
COMMENT ON TABLE hr.emp_2023 IS '사원정보테이블';
▽▽▽ (결과창)
EMP_2023 TABLE 사원정보테이블

❍ 테이블 주석 삭제
COMMENT ON TABLE hr.emp_2023 IS ‘’; -> 이거 왜 안댕(해결됨: sqld 작따)

❍ 컬럼 주석 확인
SELECT*FROM user_col_comments WHERE table_name = 'EMP_2023';

❍ 컬럼 주석 생성
COMMENT ON COLUMN hr.emp_2023.employee_id IS '사원번호';
SELECT*FROM user_col_comments WHERE table_name = 'EMP_2023';

❍ 컬럼 주석 삭제
COMMENT ON COLUMN hr.emp_2023.employee_id IS ‘’;

● 분석할 테이블, 컬럼의 주석 내용 먼저 확인하기!
SELECT*FROM user_tab_comments WHERE table_name = 'EMPLOYEES';
SELECT*FROM user_col_comments WHERE table_name = 'EMPLOYEES';

■ VIEW --- 현장에서 쓰임이 많음

  • 하나 이상의 테이블이 있는 데이터를 논리적으로 처리하는 오브젝트(OBJECT)
  • 단지 SELECT문을 가지고 있는 객체
  • 왜? 간접 access를 하기 위해서(view를 통해서 봐야 함!)
  • 별도의 storage, 저장공간이 필요하지 않음(간접 access)
  • VIEW를 생성하려면 CREATE VIEW 시스템 권한이 있어야 함
    ---->> SELECT*FROM session_privs; --> 여기에 create view 권한이 있는지 확인

==== 안 괜찮은 방법 ====
((hr))
SELECT*FROM hr.employees;

SELECT*FROM user_tab_privs;
REVOKE SELECT ON hr.employees FROM insa; -- insa의 접속 권한 회수
REVOKE ALL ON hr.emp_20 FROM insa; -- insa의 모든 객체 권한 회수
SELECT*FROM user_tab_privs;

SELECT employee_id, last_name, first_name, job_id, department_id
FROM hr.employees;
--> insa는 다른 정보 외에 해당 정보만 볼 수 있어야 함

CREATE TABLE hr.emp_insa -- 새로운 테이블 생성
AS
SELECT employee_id, last_name, first_name, job_id, department_id
FROM hr.employees;

SELECT*FROM hr.emp_insa;
GRANT SELECT ON hr.emp_insa TO insa;
SELECT*FROM user_tab_privs;

((insa))
SELECT*FROM user_tab_privs;

((hr))
DROP TABLE hr.emp_insa PURGE; -- 테이블 삭제시 권한 부여 자동 삭제

● hr.employees 테이블 중 일부만 복제한 테이블을 insa에게 권한 부여시 문제점
1) 원본 테이블 업데이트가 복제 테이블에 반영되지 않음(sync 안 맞음, 두 개 테이블 유지, 관리 비용 큼)
2) 데이터 양이 너무 많음

======================

  • CTAS 만드는 것과 같음

CREATE VIEW 뷰 이름
AS
SELECT -- 서브쿼리
FROM

CREATE VIEW hr.emp_insa
AS
SELECT employee_id, last_name, first_name, job_id, department_id
FROM hr.employees;

SELECT*FROM hr.emp_insa;

GRANT SELECT ON hr.emp_insa TO insa;

SELECT*FROM user_tab_privs;

-->> 직접 이 table을 보면 안 돼, 간접 access 지원

● VIEW인지 TABLE인지 여부 확인 방법
1) SELECT*FROM user_objects WHERE object_name = 'EMP_INSA';

	▽▽▽(결과창)

	object_type

EMP_INSA 20390 VIEW 23/03/30 23/03/30 2023-03-30:15:40:00

2) SELECT*FROM user_views WHERE view_name = 'EMP_INSA';

	▽▽▽(결과창)

	TEXT

EMP_INSA 82 "SELECT employee_id, last_name, first_name, job_id, department_id
FROM hr.employees“
-> 이것을 불러?
->> 이렇게 view는 select문만 가지고 있음

[문제103] 부서이름별 총액 급여, 평균 급여를 access하는 dept_sum_sal 뷰를 생성한 후 insa 유저에서 dept_sum_sal 뷰를 SELECT할 수 있는 권한을 부여해 주세요.

((hr 창에서 create view, grant select 수행))
CREATE VIEW dept_sum_sal -- hr. 앞에 붙여도 되고 안해도 됨
AS
SELECT d.department_name dept_name, sum(e.salary) sumsal, round(avg(e.salary)) avgsal
FROM hr.employees e, hr.departments d
WHERE e.department_id = d.department_id
GROUP BY department_name;

GRANT SELECT ON dept_sum_sal TO insa;

((insa 창에서 확인))
SELECT*FROM user_tab_privs;
SELECT*FROM hr.dept_sum_sal; --> 내가 한 것, 이거 안 좋음

((hr 창에서 수행))

CREATE VIEW dept_sum_sal2
AS
SELECT d.department_name, sum_sal, avg_sal
FROM (SELECT department_id, sum(salary) sum_sal, round(avg(salary)) avg_sal
FROM hr.employees
GROUP BY department_id) e, hr.departments d
WHERE e.department_id = d.department_id;

--> 이게 좋음, create view할 때는 inline view SELECT문에 열 별칭 필수!!!
-->> 그룹함수, 계산 등 이루어졌을 때 열 별칭 붙이는 습관 가지기!!!
-->>> 컬럼 이름에 괄호 들어갈 수 없음!!

((잘못된 예시1))

SELECT d.department_name, sum(salary), round(avg(salary))
FROM (SELECT department_id, sum(salary), round(avg(salary))
FROM hr.employees
GROUP BY department_id) e, hr.departments d
WHERE e.department_id = d.department_id;
===>>> 오류 발생 : ORA-00904: "SALARY": invalid identifier
00904. 00000 - "%s: invalid identifier"

((잘못된 예시2))

CREATE VIEW dept_sum_sal
AS
SELECT d.department_name, e.*
FROM (SELECT department_id, sum(salary), round(avg(salary))
FROM hr.employees
GROUP BY department_id) e, hr.departments d
WHERE e.department_id = d.department_id;

SELECT*FROM dept_sum_sal; --> 컬럼 안 예쁨 괄호도 많고

SELECT*FROM user_views WHERE view_name = 'DEPT_SUM_SAL2';

GRANT SELECT ON dept_sum_sal2 TO insa;

((insa))
SELECT*FROM hr.dept_sum_sal2;

❍ VIEW를 삭제하는 방법
DROP VIEW dept_sum_sal; --> 권한 부여한 경우 권한도 같이 삭제됨

❍ VIEW를 수정하는 방법

  • OR REPLACE : 동일한 이름의 VIEW가 있으면 삭제하고 생성함

CREATE OR REPLACE VIEW dept_sum_sal
AS
SELECT d.department_name 부서이름, sum_sal 총액급여, avg_sal 평균급여
FROM (SELECT department_id, sum(salary) sum_sal, round(avg(salary)) avg_sal
FROM hr.employees
GROUP BY department_id) e, hr.departments d
WHERE e.department_id = d.department_id;

	또	는

CREATE OR REPLACE VIEW dept_sum_sal
(부서이름, 총액급여, 평균급여)
AS
SELECT d.department_name, sum_sal, avg_sal
FROM (SELECT department_id, sum(salary) sum_sal, round(avg(salary)) avg_sal
FROM hr.employees
GROUP BY department_id) e, hr.departments d
WHERE e.department_id = d.department_id;

---> View DEPT_SUM_SAL이(가) 생성되었습니다.

SELECT*FROM dept_sum_sal; --> 열 별칭 변경사항 저장됨

❍ VIEW에 신규 데이터 INSERT 하기

  • 스토리지 없이 간접 access하는 select문만 있지만 insert 가능
  • VIEW에서 참조하는 테이블이 아닌 VIEW로 만든 테이블에 INSERT, 이것도 간접 ACCESS임

(((샘플 테이블 만들 것임)))

((hr))
DROP TABLE hr.test PURGE;

-> 없으면 오류, 있으면 “Table HR.TEST이(가) 삭제되었습니다.”

CREATE TABLE hr.test
AS
SELECT employee_id id, last_name name, salary sal
FROM hr.employees
WHERE department_id = 20;

Table HR.TEST이(가) 생성되었습니다.

SELECT*FROM hr.test;

CREATE OR REPLACE VIEW test_view
AS
SELECT *
FROM hr.test;

View TEST_VIEW이(가) 생성되었습니다.

SELECT*FROM hr.test_view; --> SELECT*FROM hr.test; 이거랑 결과창 동일

INSERT INTO hr.test_view(id,name,sal) VALUES(300,'홍길동',1000);

1 행 이(가) 삽입되었습니다.

commit;

SELECT*FROM hr.test_view;

201 Hartstein 13000
202 Fay 6000
300 홍길동 1000

GRANT SELECT, INSERT, UPDATE, DELETE ON hr.test_view TO insa; --다 줘야 함
--> insa에서 사용할 수 있도록

Grant을(를) 성공했습니다.

((insa))
SELECT*FROM user_tab_privs;

SELECT*FROM hr.test_view; -> view가 참조하는 테이블 꼭 쓰기!!

201 Hartstein 13000
202 Fay 6000
300 홍길동 1000

INSERT INTO hr.test_view(id,name,sal) VALUES(400,'윤건',5000)

1 행 이(가) 삽입되었습니다.

SELECT*FROM hr.test_view;

201 Hartstein 13000
202 Fay 6000
300 홍길동 1000
400 윤건 5000

((hr))

SELECT*FROM hr.test_view; --> insa에서 방금 작업한 것 반영X
SELECT*FROM hr.test; 아직 transaction 진행중이기 때문임

201 Hartstein 13000
202 Fay 6000
300 홍길동 1000

((insa))

commit;

커밋 완료.

((hr))

SELECT*FROM hr.test_view; --> insa에서 방금 작업한 것 반영O
SELECT*FROM hr.test;

201 Hartstein 13000
202 Fay 6000
300 홍길동 1000
400 윤건 5000

❍ VIEW에 신규 데이터 UPDATE 하기
((insa))

UPDATE hr.test_view
SET sal = 10000
WHERE id = 400;

1 행 이(가) 업데이트되었습니다.

commit;

커밋 완료.

((hr))

SELECT*FROM hr.test;
SELECT*FROM hr.test_view; --> insa에서 방금 작업한 것 반영O

201 Hartstein 13000
202 Fay 6000
300 홍길동 1000
400 윤건 10000

❍ VIEW에 신규 데이터 DELETE 하기
((insa))

DELETE FROM hr.test_view WHERE id = 201;

1 행 이(가) 삭제되었습니다.

commit;

커밋 완료.

((hr))

SELECT*FROM hr.test;
SELECT*FROM hr.test_view; --> insa에서 방금 작업한 것 반영O

■ 단순 VIEW

  • VIEW 안의 SELECT문에 테이블이 하나만 있는 경우
  • VIEW 안의 SELECT문에 함수를 사용하지 않은 경우
  • VIEW를 통해서 DML 작업을 수행할 수 있음

((예시))
CREATE OR REPLACE VIEW test_view
AS
SELECT *
FROM hr.test;

■ 복합 VIEW

  • VIEW 안의 SELECT문에 여러 개의 테이블을 사용하는 경우(조인 구문)
  • VIEW 안의 SELECT문에 함수를 사용하는 경우
  • VIEW를 통해서는 DML 작업을 수행할 수 없음
  • 복합 VIEW에서도 DML 작업을 수행하려면, PL/SQL을 이용해서 트리거 프로그램을 이용하면 해결할 수 있음

((예시))
CREATE VIEW dept_sum_sal2
AS
SELECT d.department_name, sum_sal, avg_sal
FROM (SELECT department_id, sum(salary) sum_sal, round(avg(salary)) avg_sal
FROM hr.employees
GROUP BY department_id) e, hr.departments d
WHERE e.department_id = d.department_id;

profile
한희수
이전 포스트

SQL 12일차

다음 포스트

SQL 14일차

0개의 댓글