[20110712] Oracle Transaction & DDL(CREATE, DROP, ALTER, COMMENT, TRUNCATE)

-- Transaction 이란?
-- 한 개 이상의 실행될 SQL 문장의 집합
-- 데이터의 일관성을 보장
-- 행의 LOCK 처리를 기본으로 함
-- 데이터의 변경 시 융통성을 제공하고, 사용자의 프로세스가
-- 예기치 않게 중단되거나, 시스템 장애가 발생하여 데이터의
-- 일관성이 어렵게 된 경우에도 일관성을 보장하기 위한
-- 시스템적 응용

-- TCL(Trancnsaction Control Language)
-- commit : 보류중인 데이터를 영구적인 데이타베이스로
--          변경사항을 저장하고 현재의 TRANSACTION 을 종료한다.
-- rollback : 보류중인 데이터의 변경사항을 모두 되돌리고
--            현재의 트랜젝션을 종료한다.
-- rollback TO [a] : 특성지점을 지정하지 않으면 모든 트랜젝션을 취소한다.
-- savepoint : 현재의 트랜젝션의 저장점을 표시한다.

-- Transaction 의 범위
-- 아래의 사항들은 자동으로 TRANSACTION 이 적용된다.
-- 1) DDL 문이 실행된 경우
-- 2) DCL 문이 실행된 경우
-- 3) 명시적으로 commit 이나 rollback  이 실행되지
-- 않은 상태에서 exit 를 해서 sqlplus 를 종료할 때
-- 강제적으로 Transaction 이 적용되는 경우
-- 1) 시스템 장애가 발생할 경우

-- 사원번호 7724 번의 부서번호를 20번으로 변경하시오.
COMMIT; -- 기존 정보를 저장한다.

SELECT *
FROM DEPT

SELECT deptno FROM EMP WHERE empno = 7782;

UPDATE EMP
SET deptno = 20
WHERE empno = 7782;

COMMIT; -- 수정된 정보를 저장한다.

-- 다시 정보를 기존 10으로 변경하자
UPDATE EMP
SET deptno = 10
WHERE empno = 7782;

SAVEPOINT aaa; -- aaa라는 SAVEPOINT 를 만들자

-- DEPT 테이블에 60, MARKETTING , PUSAN 을 넣자
INSERT INTO DEPT
VALUES (60, 'MARKETTING', 'PUSAN');

SELECT * FROM DEPT; -- 수정된 정보를 확인 하자

ROLLBACK TO aaa; -- SAVEPOINT aaa 로 ROLLBACK 하자

SELECT * FROM DEPT; -- 기존 원상태로 ROLLBACK 이 되었는지 확인 해보자
-- 그런데 실제로 데이터가 변경되는 것이 아니다.
-- 그러므로 현 변경된 데이터는 10으로 노출 되지만 실제 데이터는 COMMIT 전으로 되어 있다.
-- SAVEPONT 는 ROLLBACK 과 관련이 있는거지 COMMIT 과는 별개다

-- Data DICTIONARY
-- Data Dictuonary 는 MetaData 이다.

SELECT table_name
FROM DICTIONARY

SHOW USER; -- 현재 로그인한 계정을 보여준다.

-- SQL DDL CREATE, DROP

CREATE TABLE dept1	-- DEPT1 테이블을 생성한다.
(
	deptno	NUMBER(3),
    dname	VARCHAR2(20),
    LOC		VARCHAR2(30)
);

DROP TABLE DEPT1; -- DEPT1 테이블을 삭제 한다.

-- Table 의 생성규칙
-- 1) 반드시 문자로 시작한다.
-- 2) 1~ 30 자 까지 가능
-- 3) A ~ Z 의 대소문자, 0 ~ 9 의 숫자, 특수기호( _ , $ , # ) 가능
-- 4) 오라클의 키워드 사용불가
-- 5) 대소문자 구별안함
-- 6) 공백 사용 할 수 없다.

-- Syntax (신텍스)

CREATE TABLE table_name
(
	COLUMN_NAME		datatype,
    COLUMN_NAME 	datatype...
);

-- 서브쿼리에 의한 방법
CREATE TABLE dept1
AS
SELECT * FROM DEPT;
-- DEPT 테이블에 있는 정보를 그대로 dept1 에 넣어라
-- 좋은점은 따로 insert 를 할 필요가 없다. (Copy를 한다고 보면 된다)

-- 테이블 구조만 복사하는 방법을 해보자
CREATE TABLE dept2
AS
SELECT * FROM DEPT
WHERE 0 > 1;
-- where 0 > 1 1이 0보다 작을수가 없다..
-- where 의 말도안되는 조건의 데이터가 없어 데이터는 복사가 안되고
-- 테이블의 구조만 복사가 된다.

--EMP테이블에 있는 사원 중 1981년에 입사한 사원들만 있는 테이블을 만들겠다.
CREATE TABLE EMP81
AS
SELECT * FROM EMP
WHERE TO_CHAR(hiredate, 'YYYY') = 1981;

-- EMP 테이블에 직업이 SALESMAN 인 사원의 사원번호, 사원이름을 EMP2 에 생성하자.
CREATE TABLE emp2
AS
SELECT empno, ename FROM EMP
WHERE job = 'SALESMAN';

-- EMP2 테이블에 JOB 컬럼을 추가 할 것이다.
ALTER TABLE EMP2
ADD
(
	job		VARCHAR2(10)
);

-- EMP2 테이블의 ENAME 칼럼 구조를 변경 하고 데이터를 INSERT 해보자.
ALTER TABLE EMP2
MODIFY
(
	ename	VARCHAR2(20)
);
INSERT INTO EMP2
VALUES (999, 'ABCDEFGHIJKLMNOPQRSTU', 'DEVELOPER');

-- EMP2 테이블의 ENAME 구성을  15자리로 줄여 보자.
ALTER TABLE EMP2
MODIFY
(
	ename	VARCHAR2(15)
);
-- 기존에 있던 데이터가 15자 이상이 있기 때문에 SQL 실행시 오류가 난다.

-- EMP2 테이블의 EMPNO 의 구조를 CHAR(5) 로 변경 하겠다.
ALTER TABLE EMP2
MODIFY
(
	empno	CHAR(5)
);

-- DDL Truncate

TRUNCATE TABLE EMP2;

-- RENAME
-- EMP81 이라는 테이블을 EMP3 로 이름 변경 한다.
RENAME EMP81 TO EMP3;

--
DESC DICTIONARY;

-- DICTIONARY 테이블의 TABLE_NAME 컬럼의 USER로 시작 하는 데이터를 출력 한다
SELECT * FROM DICTIONARY
WHERE TABLE_NAME LIKE 'USER_%';

-- DICTIONARY 테이블의 TABLE_NAME 컬럼의 USER_TAB_ 로 시작 하는 데이터를 출력 한다
SELECT * FROM DICTIONARY
WHERE TABLE_NAME LIKE 'USER_TAB_%';


DESC USER_TAB_COMMENTS;

-- EMP3 테이블 컬럼에 '연습용 테이블' 코멘트를 설정한다.
COMMENT ON TABLE EMP3 IS '연습용 테이블';
SELECT * FROM USER_TAB_COMMENTS
WHERE TABLE_NAME = UPPER('emp3');

-- EMP3 테이블 컬럼에 EMP3 hiredate '1981년에 입사한 입사년월일' 코멘트를 설정한다.
COMMENT ONE COLUMN EMP3.hiredate IS '1981년에 입사한 입사년월일';

DESC USER_COL_COMMENTS;
SELECT * FROM USER_COL_COMMENTS
WHERE TABLE_NAME = UPPER('emp3');

JDBC 가서 사용할 Transaction JAVA API
Java.sql -> Connection -> Commit ( void commit() throws SQLException )
Java.sql -> Connection -> Rollback ( void roolback() throws SQLException )

Java.sql -> Connection -> Roolback ( void rollback(Savepoint savepoint) throws SQLException )

Java.sql -> Savepoint-> Java.sql Interface Savepoint