22일차 : ALTER, DROP / TCL / OBJECT

2022/12/27

 

1. ALTER / DROP

: CREATE로 정의된 내용을 수정할 때 사용, 컬럼/제약조건 추가삭제, 컬럼자료형변경 등등

 

-- 실습을 위한 테이블 생성 및 INSERT

CREATE TABLE USER_TBL(
    USER_ID     VARCHAR2(20)    PRIMARY KEY,
    USER_PW     VARCHAR2(20)    NOT NULL,
    USER_NAME   VARCHAR2(20)
);

INSERT INTO USER_TBL VALUES('user01', '1234', '유저1');

 

-- 테이블 컬럼 추가하기

-- 테이블에 컬럼을 추가(USER_ADDR 컬럼추가)
ALTER TABLE USER_TBL
ADD (USER_ADDR VARCHAR2(100));
-- 컬럼을 추가하면 이미존재하는 회원은 값이 없기 때문에 기본적으로 NULL로 세팅
ALTER TABLE USER_TBL
ADD (USER_AGE NUMBER DEFAULT 0);
-- DEFAULT값을 지정해주어 이미존재하는 회원의 AGE값에 DEFAULT값으로 세팅

 

-- 테이블 컬럼 수정하기

-- 테이블의 컬럼을 수정(USER_ADDR VARCHAR2(100) -> VARCHAR2(200))
ALTER TABLE USER_TBL MODIFY USER_ADDR VARCHAR2(200);
DESC USER_TBL; -- 컬럼에 대한 간략한 정보 조회

 

-- 테이블 컬럼 삭제하기

-- 테이블의 컬럼 삭제(USER_AGE 컬럼 삭제)
ALTER TABLE USER_TBL DROP COLUMN USER_AGE;

 

-- 제약조건 확인을 위한 쿼리문

SELECT UC.CONSTRAINT_NAME,
       UC.CONSTRAINT_TYPE,
       UCC.COLUMN_NAME,
       UC.SEARCH_CONDITION
FROM USER_CONSTRAINTS UC
JOIN USER_CONS_COLUMNS UCC ON(UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME)
WHERE UC.TABLE_NAME = 'USER_TBL';

 

-- 컬럼에 제약조건 추가

-- 컬럼에 제약조건을 추가(USER_ADDR에 NOT NULL)
ALTER TABLE USER_TBL
MODIFY USER_ADDR CONSTRAINT USER_ADDR_NN NOT NULL;
-- 이미 USER_ADDR에 NULL이 들어가있기 때문에 에러발생
-- 제약조건에 맞게 컬럼상태를 맞춰줘야함
-- (UNIQUE : 중복데이터X / NOT NULL : NULL이없어야함 등) 
-- NOT NULL은 MODIFY로 추가
UPDATE USER_TBL SET USER_ADDR = '서울시 영등포구';

-- USER_TBL USER_PHONE 컬럼추가 자료형CHAR(13)
ALTER TABLE USER_TBL ADD (USER_PHONE CHAR(13));
-- 컬럼에 제약조건을 추가(USER_PHONE UNIQUE)
-- NOT NULL을 제외한 나머지 제약조건은 ADD로 추가
ALTER TABLE USER_TBL
ADD CONSTRAINT USER_PHONE_UNQ UNIQUE(USER_PHONE);

 

-- 제약조건 삭제

-- 제약조건 삭제(NOT NULL : MODIFY / 나머지 : DROP)
-- NOT NULL 제약조건 삭제
ALTER TABLE USER_TBL MODIFY USER_ADDR NULL;
-- UNIQUE, PK, FK 제약조건삭제
ALTER TABLE USER_TBL DROP CONSTRAINT USER_PHONE_UNQ;
-- 제약조건 설정 시 이름을 알고 있어야함.

 

-- 제약조건 수정 : 지우고 다시 설정(업데이트X)

 

-- 컬럼 / 제약조건 / 테이블 이름변경

DESC USER_TBL;
-- 컬럼이름 변경(USER_PHONE -> PHONE_NUMBER)
ALTER TABLE USER_TBL
RENAME COLUMN USER_PHONE TO PHONE_NUMBER;

-- 제약조건이름 변경
ALTER TABLE USER_TBL
RENAME CONSTRAINT SYS_C007123 TO USER_ID_PK;
DESC ALTER_TEST;

-- 테이블이름 변경
ALTER TABLE USER_TBL
RENAME TO ALTER_TEST;
-- COLUMN도 CONSTRAINT도 안붙이면 TABLE

 

-- 테이블 삭제

-- 테이블 삭제
DROP TABLE ALTER_TEST;

-- 어제 실습한 MEMBER_TBL 삭제하기
DROP TABLE MEMBER_TBL CASCADE CONSTRAINT; 
-- MEMBER_TBL이 삭제가 안되는 이유 : 외래키옵션과 같은상황(제약조건이 이상해지니까)
-- MEMBER_TBL을 지우려면 얘를 참조하고있는 자식테이블을 먼저 삭제해야함
-- 귀찮으면 CASCADE CONSTRAINT 추가(MEMBER_TAB을 참조하고있던 제약조건도 모두삭제)
-- MEMBER_TBL을 참조하고 있는 테이블이 있는 경우 삭제 불가능
-- CASCADE CONSTRAINT 옵션으로 삭제하면 연결되어있는 제약조건을 삭제하고 테이블삭제

 

2. TCL

TRANSACTION : 한꺼번에 수행되어야 할 최소의 작업단위

TCL : 트랜잭션 제어 언어 / 트랜잭션 작업을 적용, 취소할 때 사용하는 SQL
COMMIT / ROLLBACK / SAVEPOINT / ROLLBACK TO

 

-- 트랜잭션은 데이터의 변화에 따라서면 적용/취소
-- DDL은 트랙잭션과 상관없음
-- DML(INSERT(ROW추가) / UPDATE(FILED변경) / DELETE(ROW삭제))만 트랜잭션에 영향
-- SELECT는 DML에 속하긴 하지만 테이블의 데이터변화가 없음 - 트랜잭션에서 예외

 

CREATE TABLE USER_TBL(
    USER_ID     VARCHAR2(20)    PRIMARY KEY,
    USER_PW     VARCHAR2(20)    NOT NULL,
    USER_NAME   VARCHAR2(20)    NOT NULL
);
-- 트랜잭션은 데이터의 변화에 따라서면 적용/취소
-- DDL은 트랙잭션과 상관없음
-- DML(INSERT(ROW추가) / UPDATE(FILED변경) / DELETE(ROW삭제))만 트랜잭션에 영향
-- SELECT는 DML에 속하긴 하지만 테이블의 데이터변화가 없음 - 트랜잭션에서 예외

INSERT INTO USER_TBL VALUES('user01', '1234', '유저1');
INSERT INTO USER_TBL VALUES('user02', '1358', '유저2');
INSERT INTO USER_TBL VALUES('user03', '2468', '유저3');

-- 지금 수행한 INSERT 3번을 하나의 트랜잭션으로 가정
-- (트랜잭션은 INSERT-UPDATE일수도잇고 내마음대로)
-- 아직 반영을 하지않음
-- 지금까지 발생한 변경사항을 저장 : COMMIT(트랜잭션을 적용하겠다.)
COMMIT;
INSERT INTO USER_TBL VALUES('user04', '4444', '유저4');

INSERT INTO USER_TBL VALUES('user05', '5555', '유저5');
-- 6,7번을 하다가 실수를 할 것같으면 굳이 5로 돌아갈 필요가없음(6부터다시작업)
-- SAVEPOINT 임시저장지점 생성
SAVEPOINT SP1;
INSERT INTO USER_TBL VALUES('user06', '6666', '유저6');
INSERT INTO USER_TBL VALUES('user07', '7777', '유저7');

ROLLBACK TO SP1; -- SAVEPOINT로 이동
COMMIT; -- 현재시점으로 적용하면서 저장된 세이브포인트 모두삭제
SELECT * FROM USER_TBL;
-- 유저1,2,3은 커밋완료 / 유저 4,5는 커밋미완료
ROLLBACK; --가장 최근에 COMMIT한 시점으로 되돌림 - 123만 있을때로 되돌아감

 

[ 짝궁(다른사람)의 DB에 접속해서 실습해보기 ]

접속 화면

다른 사람의 DB에 접속하여 테이블조회 시 INSERT를 하더라도 COMMIT이 되기 전까지의 테이블데이만 조회되는 것을 확인할 수 있음. INSERT한 데이터를 확인하려면 반드시 COMMIT을 해야함

또, 다른사람이 INSERT를 하고 COMMIT을 하지않았을 경우 내가 INSERT를 하면 에러가 발생하는 모습 확인가능.

 

3. OBJECT

3 - 1 ). VIEW

SELECT 쿼리의 실행 결과를 화면에 저장한 논리적인 가상테이블 복사된 테이블과 비슷하지만, 사용면에서 차이점존재.

GRANT CREATE VIEW TO kh; 
-- view생성권한은 resource에 없으므로 생성을 위해서는 추가로 권한 부여

 

-- 아래 쿼리문을 사용하여 테이블(복사)과 VIEW를 생성

-- 이 조회결과를 이용해서 VIEW생성, 테이블생성
SELECT EMP_NAME, EMAIL, PHONE, SALARY
FROM EMPLOYEE;

 

-- 테이블 생성

-- 테이블만들기
CREATE TABLE EMP_COPY 
AS
SELECT EMP_NAME, EMAIL, PHONE, SALARY
FROM EMPLOYEE;

 

-- VIEW 생성

-- VIEW만들기
CREATE VIEW EMP_VIEW
AS
SELECT EMP_NAME, EMAIL, PHONE, SALARY
FROM EMPLOYEE;
-- 권한을 설정해 주지 않을 경우 에러 발생
-- insufficient privileges : 권한X에러

 

-- 복사된 TABLE과 VIEW의 가장 큰 차이점 : 데이터의 상호작용

--  TABLE에서 멤버의 데이터 변경(UPDATE)

SELECT * FROM EMP_COPY;
UPDATE EMP_COPY SET SALARY = 2000000 WHERE EMP_NAME = '방명수';
COMMIT;

 

--  VIEW에서 멤버의 데이터 변경(UPDATE)

UPDATE EMP_VIEW SET SALARY = 2500000 WHERE EMP_NAME = '방명수';

-- 테이블생성시 독립적으로 사용이 가능하지만, VIEW경우 원본테이블과 연관이있다

-- EMPLOYEE테이블(원본테이블)에서는 UPDATE를 하지않았음에도 값이 변경됨

-- VIEW는 원본데이터에서 제한적인 데이터를 제공하기 위해서 사용(서로데이터를 반영할때)

 

-- 단순한 구조에서는 VIEW와 원본테이블과의 처리가 원할하게 이루어짐
-- 단, DML명령어 조작이 불가능한 경우가 있음

 

 

3 - 2 )  SEQUENCE

순차적으로 정수 값을 자동으로 생성하는 객체로, 자동번호 발생기의 역할

(시작번호와 그다음값과의 간격도 지정해줄 수 있다.)

 

-- 시퀀스의 생성과 사용법

CREATE SEQUENCE TEST_SEQ
START WITH 100      -- 시퀀스 시작값은 100
INCREMENT BY 10     -- 증가폭은 10
MAXVALUE 200        -- 시퀀스 최대값은 200
NOCYCLE;            -- 반복없음(최대값이 도달하면 끝)

/*
시퀀스 사용법
시퀀스이름.CURRVAL : 현재 시퀀스값을 반환
시퀀스이름.NEXTVAL : 다음 시퀀스값을 반환 - INCREMENT만큼 MAX까지 계속 증가
MAX에 도달하고 NOCYCLE옵션이면 NEXTVAL을 했을 때 에러발생 : 최대값에 도달했어~
*/

CREATE SEQUENCE MEMBER_SEQ
START WITH 1
INCREMENT BY 1
MAXVALUE 1000
NOCYCLE;

CREATE TABLE MEMBER_TBL_SEQ(
    MEMBER_NO       NUMBER       PRIMARY KEY,
    MEMBER_NAME     VARCHAR2(20) NOT NULL,
    MEMBER_AGE      NUMBER
);

-- 원래방법 : 이전회원의 번호를 확인하고 그 다음번호로 입력
-- 계속 확인을 해줘야해서 굉장히 불편하다.
INSERT INTO MEMBER_TBL_SEQ VALUES(1, '회원1', 20);
INSERT INTO MEMBER_TBL_SEQ VALUES(2, '회원2', 22);

-- 시퀀스를 이용한 방법 : 이전번호를 확인하지않아도 알아서 INCREMENT만큼 증가
-- 시퀀스명.NEXTVAL
INSERT INTO MEMBER_TBL_SEQ VALUES(MEMBER_SEQ.NEXTVAL, '회원1', 20);
INSERT INTO MEMBER_TBL_SEQ VALUES(MEMBER_SEQ.NEXTVAL, '회원2', 22);
INSERT INTO MEMBER_TBL_SEQ VALUES(MEMBER_SEQ.NEXTVAL, '회원3', 30);
INSERT INTO MEMBER_TBL_SEQ VALUES(MEMBER_SEQ.NEXTVAL, 
                '회원'||MEMBER_SEQ.CURRVAL, 20+MEMBER_SEQ.CURRVAL);
-- '회원'||MEMBER_SEQ.CURRVAL : 현재시퀀스값을 연결연산자를 통해 붙여 회원N이됨

INSERT INTO MEMBER_TBL_SEQ VALUES(MEMBER_SEQ.NEXTVAL, '회원16', 36);
INSERT INTO MEMBER_TBL_SEQ VALUES(MEMBER_SEQ.NEXTVAL,NULL, 37); -- 실패
INSERT INTO MEMBER_TBL_SEQ VALUES(MEMBER_SEQ.NEXTVAL, '회원17', 37);
-- INSERT가 실패했다고 시퀀스가 돌아가지 않음 
-- NEXTVAL이 돌면 일단 1이 올라가는것, 그렇기 때문에 실패 후 다시 INSERT하면
-- 17이 아닌 18이 출력되는것

ALTER SEQUENCE MEMBER_SEQ INCREMENT BY 10; -- 시퀀스 수정
-- 다시 .NEXTVAL을 하면 10이 증가되어있음
-- 다른건 모두 변경가능하지만 START는 변경불가 
-- START를 변경하고 싶으면 DROP후 재생성 해야한다.

 

3 - 3 ) INDEX

SQL명령문의 처리속도 향상을 위한 명령문

CREATE INDEX EMP_IDX
ON 
EMPLOYEE(EMP_NAME, EMP_NO, HIRE_DATE);
-- EMPLOYEE 테이블에서 EMP_NAME, EMP_NO, HIRE_DATE를 자주 사용할 경우(변경도적음)
-- 인덱스로 묶어놓음
-- 인덱스에 지정을 해놓으면 조회 시 인덱스에 지정된 애들은 인덱스에서 조회
-- 인덱스에 지정되지않은 컬럼은 테이블에서 조회
-- 현재로서는 확인할 수 없지만 데이터가 방대해질경우 처리속도에 차이가 생긴다.

DROP INDEX EMP_IDX; -- INDEX삭제

 

3 - 4 ) SYNONYM : 동의어

 -- 비공개동의어 : 생성한 사용자계정에서만 사용가능

-- 관리자 계정에서 권한부여
GRANT CREATE SYNONYM TO kh; -- SYNONYM(동의어) 생성을 위한 권한 부여

-- kh계정에서 동의어 생성

CREATE SYNONYM EMP FOR EMPLOYEE;
-- EMPLOYEE를 EMP라고 부를게 라는 의미
SELECT * FROM EMPLOYEE;
SELECT * FROM EMP; -- 동의어로도 조회가 가능하다

-- 공개 동의어 : 모든 사용자가 사용가능(관리자계정에서 생성)

-- 비공개동의어 생성 / 비공개동의어는 관리자계정에서 생성
CREATE PUBLIC SYNONYM DEPT FOR KH.DEPARTMENT;

-- kh계정에서 동의어로 쿼리문 작성시 조회가능
SELECT * FROM DEPARTMENT;
SELECT * FROM DEPT;

또다른 계정에서 공개동의어를 사용해 주고 싶으면 조회권한을 주면된다.

-- 관리자계정에서 권한부여
-- test02계정이 KH.DEPARTMENT 테이블을 조회할 수 있게 해주는 권한
GRANT SELECT ON KH.DEPARTMENT TO test02;

-- test02계정에서 다른계정.테이블 / 공개동의어로 조회가능
SELECT * FROM KH.DEPARTMENT;
SELECT * FROM DEPT; 
-- 관리자 계정에서 공개동의어 생성되었으므로 동의어로도 조회가능

단, emp는 kh가 만든 동의어이기때문에 tset02에서는 조회불가

(EMPLOYEE테이블 또한 관리자 계정에서 조회권한을 줘야한다)

-- test02계정에서 EMPLOYEE 조회
SELECT * FROM KH.EMPLOYEE;
SELECT * FROM EMP; --단, emp는 kh가 만든 동의어이기때문에 tset02에서는 조회불가

 

-- 동의어 삭제

-- 동의어삭제는 해당동의어를 생성한 계정에서 이루어져야한다.

-- kh 계정에서 삭제
DROP SYNONYM EMP;

-- 관리자계정에서 삭제
DROP PUBLIC SYNONYM DEPT;

 

-- 오늘의 실습문제 --

 

< 관리자계정 >

CREATE USER mytest IDENTIFIED BY 1234;
GRANT CONNECT, RESOURCE TO mytest;
GRANT CREATE VIEW TO mytest;
GRANT CREATE SYNONYM TO mytest;

 

< 사용자계정 >

CREATE TABLE MEMBER(
    MEMBER_NO       NUMBER          PRIMARY KEY,
    MEMBER_USERID   VARCHAR2(20)    NOT NULL UNIQUE,
    MEMBER_USERPWD  CHAR(20)        NOT NULL,
    MEMBER_NICNAME  VARCHAR2(20)    NOT NULL ,
    MEMBER_GENDER   VARCHAR2(3)     NOT NULL CHECK(MEMBER_GENDER IN('남', '여')),
    MEMBER_EMAIL    VARCHAR2(30)    NOT NULL
);

CREATE SEQUENCE MEMBER_SEQ
START WITH 1000
INCREMENT BY 1
MAXVALUE 1000000
NOCYCLE;

CREATE TABLE BOARD(
    BOARD_NO        NUMBER          PRIMARY KEY,
    BOARD_TITLE     VARCHAR2(20)    NOT NULL,
    BOARD_CONTENT   VARCHAR2(200)   NOT NULL,
    BOARD_WRITEDATE DATE            NOT NULL,
    BOARD_NICNAME   VARCHAR2(20)    NOT NULL 
                                    REFERENCES MEMBER(MEMBER_NICNAME)ON DELETE CASCADE                                   
);

CREATE SEQUENCE BOARD_SEQ
START WITH 1
INCREMENT BY 1
MAXVALUE 500000
NOCYCLE;

SELECT * FROM MEMBER;
INSERT INTO MEMBER
VALUES(MEMBER_SEQ.NEXTVAL, 'user01', '1111', '일호', '남', 'aaa@naver.com');
INSERT INTO MEMBER
VALUES(MEMBER_SEQ.NEXTVAL, 'user02', '2222', '이호', '여', 'bbb@naver.com');

INSERT INTO BOARD
VALUES(BOARD_SEQ.NEXTVAL, '1번제목', '내용이없어요', SYSDATE, '이호');
INSERT INTO BOARD
VALUES(BOARD_SEQ.NEXTVAL, '2번제목', '내용이있어요', SYSDATE, '일호');
INSERT INTO BOARD
VALUES(BOARD_SEQ.NEXTVAL, '2번제목', '내용이있어요', SYSDATE, '이호');
INSERT INTO BOARD
VALUES(BOARD_SEQ.NEXTVAL, '이건타이틀', '안뇽안뇽', SYSDATE, '이호');
SELECT * FROM BOARD;

-- VIEW 만들기 
CREATE VIEW MEMBER_VIEW
AS
SELECT MEMBER_NO, MEMBER_NICNAME, MEMBER_EMAIL, BOARD_TITLE 
FROM MEMBER
JOIN BOARD ON(MEMBER_NICNAME = BOARD_NICNAME);

SELECT * FROM MEMBER_VIEW;

-- 동의어 만들기 
CREATE SYNONYM M FOR MEMBER;
CREATE SYNONYM B FOR BOARD;

SELECT * FROM M; 
SELECT * FROM B;