Day 44. [Oracle SQL] COMMIT, ROLLBACK, SEQUENCE, 테이블 구조만 복사, 구조 변경

트랜잭션 관리

- 관계형 데이터베이스에서 실행되는 여러 개의 sql 명령문을 하나의 논리적 작업 단위로 처리하는 개념

- COMMIT : 트랜잭션의 정상적인 종료

트랜잭션내의 모든 SQL 명령문에 의해 변경된 작업 내용을 디스크에 영구적으로 저장하고 트랜잭션을 종료

INSERT, DELETE, UPDATE와 같은 명령문

 

 

- ROLLBACK : 트랜잭션의 전체 취소

트랜잭션내의 모든 SQL 명령문에 의해 변경된 작업 내용을 전부 취소하고 트랜잭션을 종료

제일 마지막 COMMIT에서 그 전 COMMIT으로 돌아감

 

---

시퀀스

-- SEQUENCE : 유일한 식별자
-- 기본 키 값을 자동으로 생성하기 위하여 일련번호 생성 객체
-- 여러 테이블에서 공유 가능

 

EX) 테이블 생성할 때, STUDNO와 같이 순서대로 번호를 하나씩 할당하여 기본키로 지정하고자 할때 사용 

 

CURRVAL

-- 시퀀스에서 생성된 현재 번호를 확인

NEXTVAL

-- 시퀀스에서 다음 번호 생성

 

CURRVAL, NEXTVAL

-- INSERT, UPDATE 문에서 사용

-- 서브쿼리, GROUP BY, HAVING, ORDER BY, DISTINCT와 함께 사용할 수 없으며, 컬럼의 기본값으로 사용할 수 없음

 

-- 시작값 3으로 하고 2씩 증가하는 최대값 100의 SEQUENCE 생성, 단 이름은 SEQ_TEST로
CREATE SEQUENCE SEQ_TEST
START WITH 3
INCREMENT BY 2
MAXVALUE 100;

-- CURRVAL, NEXTVAL

SELECT SEQ_TEST.CURRVAL FROM DUAL;
SELECT SEQ_TEST.NEXTVAL FROM DUAL;

DROP SEQUENCE SEQ_TEST;

---

기존 테이블의 구조만 복사

-- 서브쿼리를 이용한 테이블 생성시 데이터는 복사하지 않고 기존 테이블의 구조만 복사 가능

-- 서브쿼리의 WHERE 조건절에 거짓이 되는 조건을 지정하여 출력 결과 집합이 생성되지 않도록 지정

-- 사용법

 

CREATE TABLE table이름
AS SELECT *
	FROM source table
	WHERE condition;

 

-- condition : 줄력 결과가 항상 거짓인 조건을 명시. WHERE 1 = 2

---

테이블 구조 변경

SELECT * FROM PROFESSOR;

-- PROFNO, NAME, POSITION, SAL, COMM, USERID, HIREDATE, DEPTNO

CREATE TABLE PROF_SECOND
AS SELECT PROFNO, NAME, POSITION, SAL, COMM, USERID, HIREDATE, DEPTNO FROM PROFESSOR;

SELECT * FROM PROF_SECOND;

RENAME PROFESSOR TO PROF;
RENAME PROF2 TO PROFESSOR;

---

--INSERT 명령묷에서 묵시적인 방법을 이용하여 부서 테이블의 부
--서번호와 부서 이름을 입력하고 나머지 칼럼은 NULL을 입력하여라
INSERT INTO DEPARTMENT(DEPTNO, DNAME)
VALUES (300, '생명공학부');

SELECT * FROM DEPARTMENT;

INSERT INTO PROFESSOR(PROFNO, NAME, POSITION, HIREDATE, DEPTNO)
VALUES (9920, '최윤식', '조교수', TO_DATE('2006/01/01', 'YYYY/MM/DD'), 102);

INSERT INTO PROFESSOR VALUES (9910, '백미선', 'white', '전임강사', 200, SYSDATE, 10, 101);

CREATE TABLE TBL_BOARD(
    BNO NUMBER,
    TITLE VARCHAR2(1000),
    CONTENT CLOB,
    HITCOUNT NUMBER DEFAULT 0,
    REGDATE DATE DEFAULT SYSDATE,
    WRITER VARCHAR2(200)
);
DROP TABLE TBL_BOARD;

CREATE SEQUENCE SEQ_BOARD;

SELECT * FROM TBL_BOARD;

INSERT INTO TBL_BOARD  (BNO, TITLE, CONTENT, WRITER) VALUES (SEQ_BOARD.NEXTVAL, '제목1', '내용1', 'javaman');
INSERT INTO TBL_BOARD  (BNO, TITLE, CONTENT, WRITER) VALUES (SEQ_BOARD.NEXTVAL, '제목2', '내용2', 'ksj000417');
INSERT INTO TBL_BOARD  (BNO, TITLE, CONTENT, WRITER) VALUES (SEQ_BOARD.NEXTVAL, '제목3', '내용3', 'coffee');
INSERT INTO TBL_BOARD  (BNO, TITLE, CONTENT, WRITER) VALUES (SEQ_BOARD.NEXTVAL, '제목4', '내용4', 'odung');
INSERT INTO TBL_BOARD  (BNO, TITLE, CONTENT, WRITER) VALUES (SEQ_BOARD.NEXTVAL, '제목5', '내용5', 'kyongbow');

INSERT INTO TBL_BOARD
SELECT * FROM TBL_BOARD;

INSERT INTO TBL_BOARD(BNO, TITLE, CONTENT, WRITER)
SELECT SEQ_BOARD.NEXTVAL, TITLE, CONTENT, WRITER FROM TBL_BOARD;

-- 한 페이지에 10개씩 보여줌
SELECT * FROM TBL_BOARD ORDER BY 1 DESC;

SELECT *
FROM (SELECT A.*, ROWNUM AS RNUM
    FROM (
        SELECT * FROM TBL_BOARD ORDER BY 1 DESC
    ) A
    WHERE ROWNUM <= 10
)
WHERE RNUM > 10;

SELECT A.*, ROWNUM
FROM (SELECT * FROM TBL_BOARD ORDER BY 1 DESC) A
WHERE ROWNUM BETWEEN 10 AND 20;

CREATE TABLE HEIGHT_INFO(
    STUDNO NUMBER,
    NAME VARCHAR2(10),
    HEIGHT NUMBER
);

CREATE TABLE WEIGHT_INFO(
    STUDNO NUMBER,
    NAME VARCHAR2(10),
    HEIGHT NUMBER
);

TRUNCATE TABLE HEIGHT_INFO;
TRUNCATE TABLE WEIGHT_INFO;

INSERT ALL 
INTO HEIGHT_INFO VALUES(STUDNO, NAME, HEIGHT)
INTO WEIGHT_INFO VALUES(STUDNO, NAME, HEIGHT)
SELECT * FROM STUDENT WHERE GRADE = '2';

SELECT * FROM HEIGHT_INFO;
SELECT * FROM WEIGHT_INFO;

-- 학생 테이블에서 2학년 이상의 학생을 검색하여 height_info 테이블에는 키가 170보다
-- 큰 학생의 학번, 이름, 키를 입력하고 weight_info 테이블에는 몸무게가 70보다 큰 
-- 학생의 학번, 이름, 몸무게를 각각 입력하여라.
INSERT ALL -- 첫번째 WHEN에서 만족하면 들어가고, 두번째 WHEN에서도 들어갈 수 있음
WHEN HEIGHT > 170 THEN
INTO HEIGHT_INFO VALUES(STUDNO, NAME, HEIGHT)
WHEN WEIGHT > 70 THEN
INTO WEIGHT_INFO VALUES(STUDNO, NAME, WEIGHT)
SELECT * FROM STUDENT WHERE GRADE >= '2';
-- 둘 다 들어갈 수 있는 IF, IF문

SELECT * FROM HEIGHT_INFO;
SELECT * FROM WEIGHT_INFO;

INSERT FIRST -- 첫번째 WHEN에 만족하면 첫번째만 들어가고, 두번째는 안들어감
WHEN HEIGHT > 170 THEN
INTO HEIGHT_INFO VALUES(STUDNO, NAME, HEIGHT)
WHEN WEIGHT > 70 THEN
INTO WEIGHT_INFO VALUES(STUDNO, NAME, WEIGHT)
SELECT * FROM STUDENT WHERE GRADE >= '2';
-- 하나만 들어갈 수 있는 ELSE IF문

SELECT * FROM HEIGHT_INFO;
SELECT * FROM WEIGHT_INFO;

CREATE TABLE SALES (
    SALES_NO NUMBER,
    WEEK_NO NUMBER,
    SALES_MON NUMBER,
    SALES_TUE NUMBER,
    SALES_WED NUMBER,
    SALES_THU NUMBER,
    SALES_FRI NUMBER
);

TRUNCATE TABLE SALES;
INSERT INTO SALES VALUES (1101, 4, 100, 150, 80, 60, 120);
INSERT INTO SALES VALUES (1101, 5, 300, 300, 230, 120, 150);

CREATE TABLE SALES_DATA (
    SALES_NO     NUMBER,
    WEEK_NO      NUMBER,
    DAY_NO       NUMBER,
    SALES        NUMBER
);

SELECT * FROM SALES;

INSERT ALL
INTO SALES_DATA VALUES(SALES_NO, WEEK_NO, 1, SALES_MON)
INTO SALES_DATA VALUES(SALES_NO, WEEK_NO, 2, SALES_TUE)
INTO SALES_DATA VALUES(SALES_NO, WEEK_NO, 3, SALES_WED)
INTO SALES_DATA VALUES(SALES_NO, WEEK_NO, 4, SALES_THU)
INTO SALES_DATA VALUES(SALES_NO, WEEK_NO, 5, SALES_FRI)
SELECT * FROM SALES ORDER BY 2;

TRUNCATE TABLE SALES_DATA;

SELECT * FROM SALES_DATA ORDER BY 2, 3;
SELECT * FROM SALES ORDER BY 1;

-- SALES_DATA를 기반으로 주차별 평균 매출, 매출합을 구하시오.
SELECT WEEK_NO, AVG(SALES), SUM(SALES)
FROM SALES_DATA
GROUP BY WEEK_NO
ORDER BY 1;

SELECT 
    SALES_NO, 
    WEEK_NO,
    MAX(CASE WHEN DAY_NO = 1 THEN SALES END) AS SALES_MON,
    MAX(CASE WHEN DAY_NO = 2 THEN SALES END) AS SALES_TUE,
    MAX(CASE WHEN DAY_NO = 3 THEN SALES END) AS SALES_WED,
    MAX(CASE WHEN DAY_NO = 4 THEN SALES END) AS SALES_THU,
    MAX(CASE WHEN DAY_NO = 5 THEN SALES END) AS SALES_FRI
FROM SALES_DATA S
GROUP BY SALES_NO, WEEK_NO;

SELECT SALES_NO, WEEK_NO, 1 AS DAY_NO, SALES_MON AS SALES FROM SALES UNION
SELECT SALES_NO, WEEK_NO, 2 AS DAY_NO, SALES_TUE AS SALES FROM SALES UNION
SELECT SALES_NO, WEEK_NO, 3 AS DAY_NO, SALES_WED AS SALES FROM SALES UNION
SELECT SALES_NO, WEEK_NO, 4 AS DAY_NO, SALES_THU AS SALES FROM SALES UNION
SELECT SALES_NO, WEEK_NO, 5 AS DAY_NO, SALES_FRI AS SALES FROM SALES;

SELECT 
--    TRUNC((RNUM-1)/7) AS WEEKNO,
    MAX(CASE WHEN MOD(RNUM, 7) = 1 THEN RNUM END) 일, -- MAX 도 가능
    MAX(CASE WHEN MOD(RNUM, 7) = 2 THEN RNUM END) 월,
    MAX(CASE WHEN MOD(RNUM, 7) = 3 THEN RNUM END) 화,
    MAX(CASE WHEN MOD(RNUM, 7) = 4 THEN RNUM END) 수,
    MAX(CASE WHEN MOD(RNUM, 7) = 5 THEN RNUM END) 목,
    MAX(CASE WHEN MOD(RNUM, 7) = 6 THEN RNUM END) 금,
    MAX(CASE WHEN MOD(RNUM, 7) = 0 THEN RNUM END) 토
FROM (SELECT ROWNUM RNUM FROM DICT WHERE ROWNUM <= 31)
GROUP BY TRUNC((RNUM-1)/7)
ORDER BY TRUNC((RNUM-1)/7);

-- 달력만들기
SELECT ROWNUM FROM DICT WHERE ROWNUM <= TO_NUMBER(LAST_DAY(SYSDATE));

-- 서브쿼리를 이용하여 학번이 10201인 학생의 학년과 학과 번호를 
-- 10103 학번 학생의 학년과 학과 번호와 동일하게 수정하여라

SELECT STUDNO, GRADE, DEPTNO
FROM STUDENT
WHERE STUDNO IN (10201, 10103);

UPDATE STUDENT
SET(GRADE, DEPTNO) = (SELECT GRADE, DEPTNO FROM STUDENT WHERE STUDNO = 10103)
WHERE STUDNO = 10201; -- 하지는 않음

-- 학생 테이블에서 학번이 20103인 학생의 데이터를 삭제하여라
DELETE FROM STUDENT WHERE STUDNO = 20103;

-- 학생 테이블에서 컴퓨터공학과에 소속된 학생을 모두 삭제하여라.
DELETE FROM STUDENT WHERE DEPTNO = (SELECT DEPTNO FROM DEPARTMENT WHERE DNAME = '컴퓨터공학과');

CREATE TABLE PROFESSOR_TEMP AS
SELECT *
FROM PROFESSOR
WHERE POSITION = '교수';

SELECT * FROM PROFESSOR;
SELECT * FROM PROFEsSOR_TEMP;

UPDATE PROFESSOR_TEMP SET
POSITION = '명예교수';

INSERT INTO PROFESSOR_TEMP
VALUES (9999, '김도경', 'aron21', '전임강사', 200, sysdate, 10, 101);

MERGE INTO PROFESSOR P
USING PROFESSOR_TEMP P2
ON (P.PROFNO = P2.PROFNO)
WHEN MATCHED THEN
    UPDATE SET POSITION = P2.POSITION
WHEN NOT MATCHED THEN
    INSERT VALUES (P2.PROFNO, P2.NAME, P2.USERID, P2.POSITION, P2.SAL, P2.HIREDATE, P2.COMM, P2.DEPTNO);

SELECT * FROM TBL_TEST;
DROP TABLE ADDRESS;
CREATE TABLE ADDRESS(
    ID NUMBER(3) PRIMARY KEY,
    NAME VARCHAR2(50),
    ADDR VARCHAR2(100),
    PHONE VARCHAR2(30),
    EMAIL VARCHAR2(100)
);

DESC ADDRESS;

SELECT * FROM ADDRESS;

INSERT INTO ADDRESS (ID, NAME, ADDR)
VALUES (101, '고길동', '영등포');

INSERT INTO ADDRESS (ID, NAME, ADDR)
VALUES (NULL, '고길동', '영등포');
-- CANNOT INSERT NULL INTO (...);
-- PRIMARY KEY라 NULL값 허용 X

SELECT * FROM TAB;

INSERT INTO ADDRESS
VALUES (1, 'HGDONG', 'SEOUL', '123-4567', 'GDHONG@CWUN.AC.KR');

CREATE TABLE ADDRESS_SECOND(ID, NAME, ADDR, PHONE, E_MAIL) AS -- AS 필수
SELECT * FROM ADDRESS;

DROP TABLE ADDRESS_SECOND;
SELECT * FROM ADDRESS_SECOND;

SELECT ID COL1, NAME COL2, ADDR COL3, PHONE COL4, EMAIL COL5 
FROM ADDRESS WHERE 1=0; -- 1=0 거짓이라 내용 X >> 구조만 들고옴