JAVA 웹개발 과정 국비 21일차 정리

21일차

※ 배우는 과정이므로 정확하지 않을 수 있습니다.
잘못된 점은 알려주시면 수정하도록 하겠습니다!

1. DDL - ALTER

객체의 정보 또는 설정을 수정하는 기능
값이 아닌 테이블 구조를 변경한다.

1) ALTER TABLE 테이블명 ADD (컬럼명 자료형 제약조건)

새로운 컬럼을 추가한다.

contact 테이블을 만든다.

CREATE TABLE contact(
    id number CONSTRAINT id_pk PRIMARY KEY,
    name VARCHAR(20) CONSTRAINT name_nn NOT NULL,
    contact VARCHAR(20) CONSTRAINT contact_uk NOT NULL,
    gender VARCHAR(10) CHECK (gender IN ('남','여')) NOT NULL
);

새로운 컬럼을 추가한다.

ALTER TABLE contact ADD(address VARCHAR(100));

만약 아래와 같이 NOT NULL을 제약조건으로 가지는 컬럼을 생성하려면, 테이블이 비어있어야 하므로 주의하자. 비어있지 않은 테이블에 생성한다면 table must be empty to add mandatory (NOT NULL) column 이라는 에러가 뜬다.
기존에 데이터가 있을 때 컬럼을 생성하면 기존의 데이터가 가지게 되는 새 열의 값이 NULL 이 되는데, 이는 NOT NULL 조건과 맞지 않기 때문에 사전에 차단하는 것이다.

ALTER TABLE contact ADD(address VARCHAR(100) NOT NULL);

이를 해결하려면 DEFAULT 값을 주거나, 제약조건을 따로 걸어주면 된다(6, 7번 참조).

ALTER TABLE contact ADD(address VARCHAR(100) DEFAULT '없음' NOT NULL);

2) ALTER TABLE 테이블명 DROP COLUMN 컬럼명

컬럼 삭제하기

ALTER TABLE contact DROP COLUMN gender;
ALTER TABLE contact DROP COLUMN address;

3) ALTER TABLE 테이블명 RENAME COLUME 컬럼명 TO 컬럼명

컬럼명 변경하기

ALTER TABLE contact RENAME COLUMN address TO address1;

4) ALTER TABLE 테이블명 RENAME TO 테이블명

테이블명 변경하기

ALTER TABLE contact RENAME TO members;

5) ALTER TABLE 테이블명 DROP CONSTRAINT 제약조건이름

컬럼 제약 조건 삭제하기

contact 컬럼에 NOT NULL을 빼고 싶다.
그렇다면 우선 제약 조건의 이름을 알아야 한다.
이전 시간에서 배운 사용자 정의 제약조건을 모아둔 딕셔너리를 소환하여 이름을 확인해준다.
테이블을 소문자로 생성했어도 대문자로 적어주어야한다.

SELECT * FROM user_constraints WHERE table_name='MEMBERS';

별명을 정해주지 않았다면 SYS_C000000 와 비슷하게 생겼을 것이고, 만약 별명을 정해줬다면 별명이 그대로 나올 것이다.
constraint_name을 그대로 ALTER 문에 써주면 된다.
’ ‘ (작은따옴표)는 쓰지 않는다.

ALTER TABLE members DROP constraint CONTACT_UK;
ALTER TABLE members DROP constraint SYS_C007141;

6) ALTER TABLE 테이블명 MODIFY (컬럼명 자료형 제약조건)

컬럼 자료형 변경하기 & 제약조건 추가하기

VARCHAR(100) 에서 VARCHAR(200)은 가능하지만 반대로 VARCHAR(100) 에서 VARCHAR(50) 는 기존 값을 손상시킬 위험이 있기 때문에 작게 변경은 불가능하다.
변경된 컬럼의 자료형은 DESC 로 볼 수 있다.

-- 자료형 VARCHAR(100)에서 VARCHAR(200)으로 변경
ALTER TABLE contact MODIFY(address VARCHAR(200));
-- NOT NULL 제약조건 추가하기
ALTER TABLE members MODIFY (contact VARCHAR(20) NOT NULL);
-- 제약 조건 별명 설정도 가능
ALTER TABLE members 
MODIFY (name VARCHAR(20) CONSTRAINT name_nn NOT NULL);

7) ALTER TABLE 테이블명 ADD CONSTRAINT 제약조건이름 제약조건(컬럼명)

제약조건 추가하기

위 ⑥번과 동일한 기능을 가지지만 자료형을 쓰지 않아도 된다.
단, NOT NULL 조건은 쓸 수 없다. (MODIFY로만 가능)

ALTER TABLE members ADD CONSTRAINT name_uk UNIQUE(name);

2. SEQUENCE

특정 시작 값으로부터 지정된 값 간격으로 지속적으로 증가(감소)하는 값을 기억해주는 객체

카페 메뉴를 저장하는 테이블 cafe_menu를 만들어서, 음료 정보들을 넣어주었다.

INSERT INTO cafe_menu VALUES(1001,'Americano',2000);
INSERT INTO cafe_menu VALUES(1002,'Cafe Latte',2500);
INSERT INTO cafe_menu VALUES(1003,'Cafe Mocha',3000);

지금은 한개의 테이블의 3개의 값이라서 id를 기억하기 쉽지만, 테이블이 늘어나고 값이 수백, 수천으로 늘어나면 id값을 각각 기억하기 어려워진다.
이런 불편한 점을 해결하기 위해 1씩 증가하는 id 값을 자동으로 만들어주는 객체를 만들어놓았는데, 이것을 sequence라고 한다.
이 sequence에는 여러 문법을 아래와 같이 적용할 수 있다.

START WITH 숫자 : 시작하는 숫자 지정
INCREMENT BY 숫자 : 값을 숫자 만큼 증가
MAX VALUE : 최대값 제한
NOMAXVALUE : 최대값 제한 없음 (무한대)
NOCACHE : 캐싱 사용하지 않음
- 캐싱 : 시퀀스 성능을 향상시키기 위해 값을 20개 정도씩 미리 끌어오는 것
  캐싱을 사용하면 이 값들을 다 소비하지 않으면 다음에 쓰지 않는 값을 막 가져와서 저장한다.
  이것을 방지하려면 캐싱을 사용하지 않으면 된다.

아래와 같이 사용하면, 1004번부터는 숫자를 직접 적지 않아도 자동으로 저장되기 시작하며, 값을 넣어 줄 때는 값 대신 시퀀스 이름 뒤 .NEXTVAL을 적어주면 된다.

CREATE SEQUENCE cafe_menu_seq 
START WITH 1004 INCREMENT BY 1 NOMAXVALUE NOCACHE;
-- 값 넣어주기
INSERT INTO cafe_menu VALUES(cafe_menu_seq.NEXTVAL,'Orange Juice',4000);

3. TCL - COMMIT, ROLLBACK

Transaction Control Language
COMMIT / ROLLBACK

1) 트랜잭션(Transaction)이란?

오라클 내에서 사용하는 논리적 작업의 단위
개발자 관점에서 작업의 원자성을 확보한다.
* 작업의 원자성 : 쿼리가 완전히 실패하거나 완전히 성공하게 하는 것

cafe_menu를 다 저장했는데 Orange Juice가 메뉴에서 빠져서 데이터를 지우려고 했다.
DELETE를 쓰다가 모르고 조건을 설정해주지 않아서 4가지 메뉴가 전부 지워져버렸다.
이럴 때는 어떻게 해야할까?

일단 DELETE FROM cafe_menu를 실행해도 DB에 다이렉트로 반영되지 않는다.
어떤 쿼리를 실행했냐에 따라 DBMS -> DB 다이렉트 반영일 수도 있고, DBMS -> ? -> DB 이렇게 어떤 것을 거쳐서 DB로 갈 수 있다.
여기서의 ?가 바로 트랜잭션이다.

DDL / TCL / TCL : DBMS -> DB 다이렉트 반영
DML : DBMS -> Transaction -> DB 반영
* DDL / DCL 은 자동으로 COMMIT이 된다.

DML을 사용하게 되면 이 쿼리들은 트랜잭션이라고 불리는 논리적 단위로 뭉쳐서 한곳에 잠시 정체되어있다.
그렇다면 데이터는 왜 사라진걸까?
사실 사라진 것이 아니다. DBMS가 삭제된듯한 모습을 보여준 것이다.(가짜로)
따라서 이때, 두가지 명령을 할 수 있는데 바로 COMMIT과 ROLLBACK이다.

트랜젝션을 DB에 반영해라 : COMMIT (최종확정명령)
트랜젝션을 취소해라 : ROLLBACK

커밋해버리면 되돌릴 수 없다. 따라서 여러번 생각하고 진행해야 한다.

2) 작업의 원자성

그럼 트랜잭션은 실수를 방지하기 위한 용도일까?
아니다.

쇼핑몰 DB를 만든다고 생각해보자.

고객이 구매 행동을 한다.
구매내역/판매내역/택배목록… 등 여러 테이블에 값이 들어가게 될 것이다.
이 때, 구매내역과 판매내역에는 값이 제대로 들어가서 확인이 가능
하지만, 택배목록에는 에러가 생겨 값이 저장되지 못했다.
구매자는 택배를 싱글벙글 기다렸지만 택배는 오지 않을 것이다…
이런 상황이 반복되면 구매자들은 구매를 하지 않을 것이고 쇼핑몰은 망한다…

위와 같은 상황이 생긴다면, 차라리 ‘구매부터 실패였다면 알기 쉬웠을텐데…’ 라는 생각을 자동적으로 하게 된다.
여러 값이 있을 때, 몇 가지는 성공하고 몇 가지는 실패하는 것보다 모두 성공하거나 모두 실패하는 것이 더 나을 때가 있다.
바로 이러한 상황을 구현하기 위해 트랜잭션을 이용하고 TCL을 사용하는 것이다.

4. DCL - GRANT, REVOKE

Data Control Language
GRANT(권한부여) / REVOKE(권한회수)

1) GRANT TO

GRANT CONNECT TO 계정명

접속 권한 부여
GRANT RESOURCE TO 계정명

자신의 계정의 객체를 생성/수정/삭제하는 권한 부여
GRANT SELECT/UPDATE/DELETE/INSERT ON 계정명1.테이블명 TO 계정명2

계정명2에 계정명1의 테이블에 SELECT/UPDATE/DELETE/INSERT 권한 부여

-- test계정을 만들고, kh.employee 테이블에 select 및 update 권한을 부여한다.
CREATE USER test IDENTIFIED BY test;
GRANT RESOURCE TO test;
GRANT SELECT, UPDATE ON kh.employee TO test;
-- test계정으로 접속하여 선동일의 이름을 선동이로 변경해본다.
UPDATE kh.employee SET emp_name='선동이' WHERE emp_name='선동일';

2) REVOKE FROM

GRANT와 동일하지만 TO 대신 FROM을 쓴다는 것을 잘 기억하자.

REVOKE CONNECT FROM 계정명

접속 권한 회수
REVOKE RESOURCE FROM 계정명

자신의 계정의 객체를 생성/수정/삭제하는 권한 회수
REVOKE SELECT/UPDATE/DELETE/INSERT ON 계정명1.테이블명 FROM 계정명2

계정명2에 계정명1의 테이블에 SELECT/UPDATE/DELETE/INSERT 권한 회수

3) Script Running 무한로딩

여러 계정을 옮겨다니면서 테이블 레코드를 수정했더니 쿼리를 실행하는데 실행이 되지 않고 무한 로딩이 되면서 멈추는 현상이 발생했었다.
강사님이 나중에 프로젝트 할 때 꼭!! 발생하는 문제라고 기억해두라고 하셨는데, 내가 바로 걸릴지 몰랐다..^^
허겁지겁 다른 계정에서 COMMIT 을 완료하니 무한 로딩이 풀리고 정상동작하였다.
다른 계정을 사용할 때 꼭 COMMIT을 해주는 습관을 들이자!

5. Object - View

가상 테이블

1) View의 특징

원본을 복사하여 수정사항이 반영되지 않는 테이블과는 달리 수정내용을 반영하는 테이블이다.
물리적으로 존재하지 않지만 있는 것처럼 보인다.
민감한 정보를 숨긴 뷰를 만들어 권한을 따로 부여하거나 필요한 데이터만 뷰로 만들어 사용하는 등 효율적인 사용이 가능하다.
뷰를 수정해도 원본이 수정되지만, 수정 조건이 까다롭다. (거부될 수도 있음)

2) View의 생성

RESOURCE 권한은 뷰를 생성하는 기능을 포함하지 않기 때문에, GRANT CREATE로 권한을 다시 줘야한다.

-- system계정
GRANT CREATE view TO kh;

CREATE문에 AS를 사용하여 보고싶은 정보만 테이블 또는 뷰를 떼어낼 수 있다.

CREATE VIEW employee_dev_view
AS
SELECT emp_id,emp_name,emp_no,email FROM employee;

3) View의 종류

단일뷰(단순뷰) : 일반적인 테이블 안에서 데이터를 떼옴
복합뷰(GROUP BY, JOIN, UNION 등을 사용) : DML 사용 불가(예외도 있지만 기본적으로 불가)

Twitter Facebook LinkedIn

KKONG