231018 Oracle SQL 'INSA' 계정_생성, 수정, 권한 확인,TABLE 생성, INSERT 문, UPDATE 문, DELETE 문, COMMIT, ROLLBACK, Transaction

SELECT * FROM user_sys_privs;

SELECT * FROM user_tab_privs;

# 오류발생, 권한이 없어 TABLE 조회 불가

SELECT * FROM hr.employees;

 

# SYS 계정에서 객체 권한 부여 확인.

SELECT * FROM user_tab_privs;
SELECT * FROM hr.employees;

■ TABLE 
    a. 행(ROW)과 열(COLUMN)으로 구성되어 있는 데이터의 저장구조
    
A. 데이블을 생성하려면 두가지를 체크해야 한다. 
    a. 테이블을 생성할 수 있는 권한. 
    CREATE TABLE 시스템 권한   

SELECT * FROM user_sys_privs;

    
    b. 테이블을 저장할 수 있는 테이블스페이스 권한
    UNLIMITED TABLESPACE 시스템 권한 : db에 생성되어 있는 모든 테이블스페이스를 사용할 수있는 권한

SELECT * FROM user_ts_quotas;

■ 테이블 생성

CREATE TABLE emp (
    id    NUMBER(4),
    name  VARCHAR2(30),
    day   DATE DEFAULT sysdate )
TABLESPACE users;

#  테이블 스페이스를 지정하지 않으면 유저 생성시에 지정 'DEFAULT TABLESPACE users'에 저장된다. 

SELECT * FROM user_users;

■ DML(Data Manipulation Language)
    a. INSERT
    b. UPDATE
    c. DELETE
    d. MERGE

 

■ TCL(Transaction Control Language) 

    a. COMMIT : DML 작업을 영구히 데이터 베이스에 저장
    b. ROLLBACK : DML 작업을 영구히 데이터 베이스에 취소
    c. SAVEPOINT : ROLLBACK 기능을 도와 주는 표시자

 

★ Transaction : 논리적으로 DML 을 하나로 묶어서 처리하는 작업 단위

영구히 저장이 된 것이 아니다.   

 

중간에 COMMIT 을 하게된다면, 

 

■ INSERT 
    a. 테이블에 새로운 행을 입력하는 SQL문
    b. INSERT INTO 소유자.테이블(컬럼, 컬럼, 컬럼, ...)
        VALUES (데이터, 데이터, 데이터,...) ;

DESC EMP

INSERT INTO insa.emp(id, name, day)
VALUES(1, '홍길동', TO_DATE('20231018','YYYYMMDD')); --Transaction 시작 시점

#  Transaction 시작 시점(insert into)까지 영구히 저장, Transaction 종료(commit)

COMMIT;

# 영구히 저장된 데이터 확인

SELECT * FROM insa.emp;

# Transaction 시작 시점

INSERT INTO insa.emp(id, name, day)
VALUES(2, '박찬호', TO_DATE('20230101','YYYYMMDD'));

INSERT INTO insa.emp(id, name, day)
VALUES(3, '윤건', TO_DATE('20210101','YYYYMMDD'));

INSERT INTO insa.emp(id, name, day)
VALUES(4, '나얼', NULL);

 

# 미리보기

SELECT * FROM insa.emp;

 

# Transaction 시작 시점(insert into)까지 영구히 취소, Transaction 종료(commit)

ROLLBACK;

 

 

# 영구히 저장된 데이터 전까지의 데이터만 확인 가능

SELECT * FROM insa.emp;

 

# INSERT 수행시 DEFAULT 값 입력하는 방법

A)

INSERT INTO insa.emp(id, name)
VALUES(2, '박찬호');

SELECT * FROM insa.emp;

 

# 오류발생, 컬럼이 지정되어 있지 않는 곳에서 데이터를 입력할 수 없다.

INSERT INTO insa.emp(id, name)
VALUES(3, '윤건', sysdate);

 

B)

# default 키워드를 지정하면 default 값이 입력된다. (보편적으로 실무적으로 많이 이용)

INSERT INTO insa.emp(id, name, day)
VALUES(3, '윤건', default);

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

ROLLBACK;

 

C) # 'name' 컬럼에 default 값이 선언되어 있지 않을 경우, default 키워드를 수행하면 null 값으로 입력된다.

INSERT INTO insa.emp(id, name, day)
VALUES(4, default, default);

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

ROLLBACK;

 

D) 
# 'day' 컬럼에 default 값이 선언되어 있더라도 null을 수행하면 null 값으로 입력된다.

INSERT INTO insa.emp(id, name, day)
VALUES(4, '나얼', NULL);

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

ROLLBACK;

 

■ UPDATE 
    a. '특정한 필드값'을 수정하는 SQL문
    b. UPDATE 소유자.테이블 
       SET 컬럼 = 새로운값, 컬럼 = 새로운값, ...
       WHERE 조건;

 

# Transaction 시작 (UPDATE) Transaction 종료(ROLLBACK)

UPDATE insa.emp
SET id=100; -- Transaction 시작 시점

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

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

 

# UNDO TABLESPACE 역할

    - DML 작업시에 이전값을 UNDO 공간에 저장한다. 
        1) ROLLBACK 
        2) 읽기 일관성

SELECT * FROM insa.emp WHERE id=2;

 

# Transaction 시작 (UPDATE) Transaction 종료(ROLLBACK)

UPDATE insa.emp
SET name = '박찬호' , day = TO_DATE('20230101','YYYYMMDD')
WHERE id = 2; -- Transaction 시작 시점

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

COMMIT; -- Transaction 시작 시점까지 저장, Transaction 종료

 

# day 컬럼의 값을 default 값을 수정

UPDATE insa.emp
SET day = default
WHERE id = 2;

SELECT * FROM insa.emp; --commit 후 데이터 확인.

COMMIT;

 

# day 컬럼의 값을 null 값을 수정

UPDATE insa.emp
SET day = null
WHERE id = 2; -- Transaction 시작 시점

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

COMMIT; -- Transaction 시작 시점까지 저장, Transaction 종료

 

■ DELETE 문
    a. 행을 삭제하는 SQL 문
    b. DELETE FROM 소유자.테이블; -- 테이블 전체 행을 삭제
    c. DELETE FROM 소유자.테이블 WHERE 조건; -- 테이블의 조건절에 해당하는 행만 삭제

 

DELETE FROM insa.emp;
SELECT * FROM insa.emp;
ROLLBACK;

 

DELETE FROM insa.emp WHERE id = 2;
SELECT * FROM insa.emp;
ROLLBACK;