응 평가보면 그만이야~
그래요 또 평가예요. 이제 별로 놀랍거나 두렵지도 않음..
사실 정리해서 올리고 싶지 않은데 그러면 시리즈에 순서가 안 맞아서
굉장히 불편하기 때문에...오늘도 정리를 한다.
서술형
1.DCL (Data Control Language)에 대하여 서술하시오.
DCL은 데이터에 대한 접근 권한을 부여, 회수하는 명령어들을 의미한다.
그 안에는 grant, revoke가 있으며, grant는 권한을 부여 / revoke는 권한을 회수한다.
그리고 이때 부여하는 권한 묶음들을 role이라고 말하며, 주로 사용하는 role은 자원을 관리하고 조작하는 묶음인 resource와 연결 세션을 만드는 묶음인 connect가 있다.
[ 사용법 ]
grant 접근 권한 to 계정명;
revoke 접근 권한 from 계정명;2. Set Operator 중 MINUS에 대하여 서술하시오
Set Operator도 join처럼 두 개 이상의 테이블을 연결하는 기능이지만 그 원리는 다르다.
이 중 minus의 사용법은 다음과 같다.
[ 사용법 ]
select * from tmp1; -- ABC
MINUS
select * from tmp2; -- BCD
-- 출력 : A이처럼, MINUS는 두 테이블의 출력 결과를 비교하여, 앞쪽 결과에서 뒤쪽 결과의 중복값을 제거한 뒤
앞쪽 결과만 반환한다.
3. TCL (Transaction Control Language)에 해당되는 구문들을 3가지 적고 설명하시오
먼저 트랜잭션이란 데이터베이스에 실제 반영될 작업들의 최소 단위를 의미한다.
우리가 쿼리를 통해 특정 작업을 명령하면, 트랜잭션을 거쳐 그 작업의 결과가 데이터베이스에 반영된다.
Commit;
- 커밋은 실제 우리가 쿼리로 작업한 명령들을 데이터베이스에 반영을 하는 것이다.
이 커밋이 되는 방법은 세 가지가 있는데, 트랜잭션이 꽉차거나, 커밋 명령어를 입력하거나, 자동으로 커밋이 되는 것이 있다. 이 중 자동 커밋은 DML(insert, delete, update)을 제외한 다른 SQL명령어들에 이뤄진다. - 위의 명령어들은 직접 데이터베이스를 수정하는 기능이기 때문에 트랜잭션에서 해당 명령어들을 묶어둔 후, Commit을 할 경우 그 수정 결과를 실제 데이버베이스에 반영한다.
Rollback;
- 롤백은 트랜잭션에 쌓인 작업들을 취소하는 것을 의미한다.
위에서 말한 실제 데이터베이스를 수정하는 명령어들은 커밋 전까지 트랜잭션에서 대기하고 있다.
만약 해당 명령들을 취소하고 싶다면, Rollback을 입력함으로 트랜잭션에 쌓인 명령을 모두 취소하면 된다.
Savepoint (포인트 이름);
- 말 그대로 저장위치이며, 트랜잭션에 쌓인 명령들에 대해 지점을 설정해주는 것이다.
이는 롤백이 모든 트랜잭션의 작업을 취소하기 때문에, 취소하고 싶은 작업들의 위치를 설정해줘서
롤백 시 해당 작업 이전의 상태로 돌리는 것이다.
rollback to (포인트 이름); 위처럼 사용하면 저장해놓은 시점으로 이동하는데 한 가지 주의할 점은 돌아온 과거보다 미래로는 이동할 수 없으며, 커밋과 동시에 savepoint 는 사라진다.
문제해결
[ 문제 ]
테이블 정의서에 따라 테이블을 생성하고, 요구사항을 충족할 수 있도록 시퀀스를 생성하는 SQL작성 후 정상 결과와 같은 같은 형태로 데이터를 조회하기 위하여, 데이터 삽입, 조회를 수행하였으나 정상처리되지 않는 문제가 발생하였다.
SQL구문을 분석하여 요구사항을 충족시키지 못하거나, 정상 결과가 조회되지 않은 원인과, 정상 결과가 조회될 수 있도록 하는 조치내용을 순서대로 작성하시오.
[ 작성한 SQL문 ]
-- BRANDS 테이블 생성
CREATE TABLE BRANDS(
BRAND_ID NUMBER PRIMARY KEY,
BRAND_NAME VARCHAR2(100) NOT NULL
);
-- PRODUCTS 테이블 생성
CREATE TABLE PRODUCTS(
PRODUCT_NO NUMBER PRIMARY KEY,
PRODUCT_NAME VARCHAR2(50) NOT NULL,
PRODUCT_PRICE NUMBER NOT NULL,
BRAND_CODE NUMBER REFERENCES BRANDS,
SERIAL_NO VARCHAR2(100),
SOLD_OUT CHAR(1) DEFAULT 'N' CHECK(SOLD_OUT IN ('Y', 'N'))
);
-- SEQ_BRAND_ID 시퀀스 생성
CREATE SEQUENCE SEQ_BRAND_ID
START WITH 100
INCREMENT BY 100
MAXVALUE 500
NOCYCLE;
-- SEQ_PRODUCT_NO 시퀀스 생성
CREATE SEQUENCE SEQ_PRODUCT_NO
START WITH 1
INCREMENT BY 1
MAXVALUE 10000
NOCYCLE;
-- BRANDS 테이블 데이터 삽입
INSERT INTO BRANDS VALUES (SEQ_BRAND_ID.NEXTVAL, '삼성');
INSERT INTO BRANDS VALUES (SEQ_BRAND_ID.CURRVAL, '애플');
-- PRODUCTS 테이블 데이터 삽입
INSERT INTO PRODUCTS VALUES (SEQ_PRODUCT_NO.NEXTVAL, '갤럭시S8', 800000, 100, 'S8','Y');
INSERT INTO PRODUCTS VALUES (SEQ_PRODUCT_NO.NEXTVAL, '갤럭시S9', 900000, 100, 'S9','N');
INSERT INTO PRODUCTS VALUES (SEQ_PRODUCT_NO.NEXTVAL, '갤럭시S10', 1000000, 100, 'S10','N');
INSERT INTO PRODUCTS VALUES (SEQ_PRODUCT_NO.NEXTVAL, '아이폰9S', 900000, 200, '9S','N');
INSERT INTO PRODUCTS VALUES (SEQ_PRODUCT_NO.NEXTVAL, '아이폰10S', 1000000, 200, '10S','N');
-- 결과 조회
SELECT PRODUCT_NAME, PRODUCT_PRICE, BRAND_NAME, SOLD_OUT
FROM PRODUCTS JOIN BRANDS ON (BRAND_ID = BRAND_CODE);원인
[ 요구사항 미충족 ] : 문제가 되진 않지만, 정의서와 맞지 않음
- PRODUCT_NAME VARCHAR2(50) NOT NULL -- 요구사항 길이 20
- SERIAL_NO VARCHAR2(100) -- 요구사항 : 제한조건으로 unique 설정(중복방지)
- MAXVALUE 500 -- 요구사항 : 최대값 1000
[ 설정 오류 ]
- 잘못된 시퀀스 메서트 호출과 primary key 설정
INSERT INTO BRANDS VALUES (SEQ_BRAND_ID.CURRVAL, '애플').currval 은 현재 시퀀스의 값을 한번 더 불러오는 기능이다. 따라서 시퀀스값이 오르지 않은 현재 값이 입력된다. 이때, brand_id 의 제약조건은 primary key로 기본 공백과 중복입력이제한되어 있다.
그러므로, 첫째, 메서드가 잘못 호출하였고 동시에 제약조건에 걸려 에러가 발생한다.
해결
전반적으로 주키 설정과, 외래키 설정을 하려기에 drop으로 삭제 후 다시 재생성 해주고자 한다.
drop table brands;
drop table products;
drop SEQUENCE SEQ_PRODUCT_NO;
drop SEQUENCE SEQ_BRAND_ID;[ 요구사항 재설정 ]
-- 1. PRODUCT_NAME VARCHAR2(50) NOT NULL / SERIAL_NO VARCHAR2(100)
PRODUCT_NAME VARCHAR2(20) NOT NULL
SERIAL_NO VARCHAR2(100) unique,
-------------------------------------
CREATE TABLE PRODUCTS(
PRODUCT_NO NUMBER PRIMARY KEY,
PRODUCT_NAME VARCHAR2(20) NOT NULL,
PRODUCT_PRICE NUMBER NOT NULL,
BRAND_CODE NUMBER REFERENCES BRANDS,
SERIAL_NO VARCHAR2(100) UNIQUE,
SOLD_OUT CHAR(1) DEFAULT 'N' CHECK(SOLD_OUT IN ('Y', 'N'))
);
-- 2. MAXVALUE 1000
CREATE SEQUENCE SEQ_BRAND_ID
START WITH 100
INCREMENT BY 100
MAXVALUE 1000
NOCYCLE;[ 객체 내용 변경 ]
-- 3. 시퀀스 메서드 변경
INSERT INTO BRANDS VALUES (SEQ_BRAND_ID.NEXTVAL, '애플');