231019 Oracle SQL 'INSA' 계정_ 테이블생성 권한, UPDATE SUBQUERY, DELETE SUBQUERY,correlated subquery, AUTO COMMIT

A. 테이블을 생성할 수 있는 권한 확인
# SYS 계정에서도 확인 가능.
# CREATE TABLE 시스템권한.

SELECT * FROM user_sys_privs;
SELECT * FROM session_roles;
SELECT * FROM role_sys_privs;
SELECT * FROM session_privs;

B. 테이블 스페이스 사용할 수 있는 권한, DEFAULT TABLESPACE 확인
# SYS 계정에서도 확인 가능.

SELECT * FROM user_ts_quotas;
SELECT * FROM user_users;

C. CTAS 를 서브쿼리에 사용할 수 있는 테이블에 대해서는 SELECT 객체권한이 있어야 한다.
# SYS 계정에서도 확인 가능.

select * from user_tab_privs;
select * from role_tab_privs;

 

# test table 생성

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;

# DATA 로드

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

INSERT INTO insa.mgr (id, name, day)
VALUES (101, '박찬호', to_date('20010101', 'yyyymmdd')); 

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

ROLLBACK; -- Transaction 시작 시점까지 영구 취소, Transaction 종료

SELECT * FROM insa.mgr; -- ROLLBACK 확인.

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

INSERT INTO insa.mgr(id, name, day)
SELECT employee_id, upper(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; -- INSERT 문 확인

COMMIT; --저장

# insa.emp TABLE 생성

CREATE TABLE insa.emp (
    id         NUMBER(3),
    name       VARCHAR2(60),
    dept_id    NUMBER(3),
    dept_name  VARCHAR2(30)
)
TABLESPACE users;

# Transacion 

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

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

COMMIT; -- Transaction 종료

 

# SELECT  확인

SELECT * FROM insa.emp;

 

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

COMMIT;

SELECT * 
FROM insa.emp 
WHERE ID = 100;

■ UPDATE SUBQUERY 

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; --저장

# UPDATE 

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 e
                 WHERE employee_id = '자기자신의 사원번호');

# 존재 확인

SELECT *
FROM HR.employees e
WHERE EXISTS ( SELECT 'X'
               FROM insa.emp
               WHERE id = e.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 --MAIN QUERY 절
SET dept_id = ( SELECT department_id
                FROM hr.employees
                WHERE employee_id = i.id ); -- i.id 후보행 값

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

COMMIT; --영구히 저장

SELECT *
FROM insa.emp;

insa.dept_name 를 수정하려 한다. 
hr.departments 테이블에 있는 department_name 을 값으로 수정하려 한다. 

# 원인(권한이 없다.) 오류발생 : ORA-00942: table or view does not exist

SELECT *
FROM hr.departments d
WHERE EXISTS (
        SELECT 'x'
        FROM insa.emp
        WHERE dept_id = d.department_id );

# 권한 확인

select * from user_tab_privs;

# 권한 부여 <hr session>;

SELECT *
FROM hr.departments d
WHERE EXISTS (
        SELECT 'x'
        FROM insa.emp
        WHERE dept_id = d.department_id );
        
SELECT *
FROM insa.emp i
WHERE EXISTS (
        SELECT 'x'
        FROM hr.departments 
        WHERE department_id = i.dept_id );

         
# UPDATE dept_name ;

UPDATE insa.emp i --MAIN QUERY 절
SET dept_name = ( SELECT department_name
                  FROM hr.departments 
                  WHERE department_id = i.dept_id );
                  
SELECT *
FROM insa.emp; --미리보기

COMMIT; --영구히 저장

SELECT *
FROM insa.emp;

■ DELETE SUBQUERY 

SELECT * 
FROM hr.employees
WHERE hire_date < to_date('20030101', 'yyyymmdd');

SELECT *
FROM insa.emp
WHERE id IN ( SELECT employee_id
              FROM hr.employees
              WHERE hire_date < to_date('20030101', 'yyyymmdd') );
              
DELETE FROM insa.emp
WHERE id IN ( SELECT employee_id
              FROM hr.employees
              WHERE hire_date < to_date('20030101', 'yyyymmdd'));

[문제] 아래 쿼리문으로 exists 문으로 변형

SELECT *
FROM insa.emp
WHERE id IN ( SELECT employee_id
              FROM hr.employees
              WHERE hire_date < to_date('20030101', 'yyyymmdd') );

변형

SELECT *
FROM insa.emp i
WHERE EXISTS ( SELECT 'X'
               FROM hr.employees
               WHERE hire_date < to_date('20030101', 'yyyymmdd') 
               AND employee_id = i.id );

★ correlated subquery 를 이용한 delete 문

DELETE FROM insa.emp i
WHERE EXISTS ( SELECT 'X'
               FROM hr.employees
               WHERE hire_date < to_date('20030101', 'yyyymmdd') 
               AND employee_id = i.id );

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

# 오류발생 : 'insa'가 'hr.job_history' 에 대한 권한 없음
ORA-00942: table or view does not exist

SELECT * FROM hr.job_history;

# 권한 부여

SELECT * FROM hr.job_history;

# job_id를 한번일도 바꾼 사원정보 조회

SELECT * 
FROM insa.emp i 
WHERE EXISTS ( SELECT 'X'
               FROM HR.job_history
               WHERE employee_id = i.id );

# job_id를 한번일도 바꾼 사원정보 DELETE

DELETE FROM insa.emp i
WHERE EXISTS ( SELECT 'X'
               FROM HR.job_history
               WHERE employee_id = i.id );

★ 주의할 것! 

DELETE FROM insa.emp; --transaction 시작

SELECT * FROM insa.emp; 

CREATE TABLE insa.test(id number); -- AUTO COMMT 기능
--    a. 자동 COMMIT 기능
--    b. 내부적 insert, update 기능
--    c. table 생성 및 정보 저장 기능

SELECT *
FROM insa.emp; 

ROLLBACK;

SELECT * FROM insa.emp;

■ 자동 COMMIT 발생 할때, AUTO COMMIT 현상 
    A. DDL(CREATE, ALTER, DROP, RENAME, TRUNCATE, COMMENT)
    B. DCL(GRANT, REVOKE)
    C. SQLPULS 에서 exit 를 수행해서 종료를 하면 자동 COMMIT 를 가지고 있다. 
    D. SQLPULS 에서 conn 를 이용해서 새롭게 session 을 접속하는 경우 자동 COMMIT 를 가지고 있다. 
    E.CMD(명령 프롬프트), Run SQL Command Line
        SQL> conn insa/oracle
        SQL> delete from insa.emp;
        ...
        SQL> conn hr/hr -- 자동 commit 을 수행한다.
    ★ DML과 DCL은 같은 SESSION 에서 수행하지 말자! 

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