기록하고 기억하는 블로그

오라클의 계층형 쿼리문 - START WITH , CONNECT BY , ORDER SIBLINGS BY

-> 상위 타입과 하위 타입간의 관계를 계층식으로 표현할 수 있게 하는 질의어

1. START WITH : 상위 타입(부모) 로 사용 될 행을 지정 (서브쿼리 가능 )

2. CONNECT BY : 상위 타입과 하위 타입간의 관계를 규정

-- PRIOR 연산자와 같이 사용하여 현재 행 이전에 상위 타입이 있을지 하위 타입이 있을지 지정

EX) CONNECT BY PRIOR  자식컬럼 = 부모컬럼 : 부모 -> 자식 계층구조

EX) CONNECT BY PRIOR  부모컬럼 = 자식컬럼 : 자식 -> 부모 계층구조

3. ORDER SIBLINGS BY : 계층형 쿼리의 정렬구문

ex) ORIGINAL 을 참조해 PARENT_NO 생성 시

SELECT LEVEL, C.* FROM 컬럼 C

START WITH PARENT_NO IS NULL

CONNECT BY PRIOR ORIGINAL_NO = PARENT_NO

ORDER SIBLINGS BY ORIGINAL_NO

--추가기능--

*LEVEL 가상컬럼

-> START WITH로 지정된 행의 LEVEL을 1로 부여하고

-> 하위 계층으로 내려갈수록 단계별 LEVEL을 1씩 증가시켜서 표현

*조건이 반영된 형태의 계층구조를 조회해고 싶다면 

FROM 절에 서브쿼리(인라인뷰) 를 사용해서

미리 조건에 맞는 형태의 행으로 구성해 둠

*TRIGGER : 테이블이나 뷰가 INSERT, UPDATE, DELETE 등의 DML문에 의해 변경될 경우

                 자동으로 실행될 내용의 정의하여 저장하는 객체(PROCEDURE)

<종류>

-실행 시기에 따라

 1)BEFORE TRIGGER

 2)AFTER TRIGGER

-SQL문에 의해 영향을 받는 ROW에 따른 분류

 1)ROW TRIGGER : 각 ROW에 대해 TRIGGER 한번씩 실행

                           트리거 생성 시 FOR EACH ROW 옵션 작성

                           OLD : 참조 전 열의 값

                           NEW : 참조 후 열의 값, 새로 INSERT/UPDATE 된 행을 가르키는 것

 2)STATEMENT TRIGGER : SQL문에 대해 한번만 실행 (DEFAULT 임)

<생성 방법>

-CREATE OR REPLACE TRIGGER `트리거명`

 BEFORE/AFTER

 INSERT/UPDATE/DELETE

 ON `테이블명`

 [FOR EACH ROW 옵션]

 [WHEN 조건]

 DECLARE 

 BEGIN

 [EXCEPTION]

 END;

 /

<EXAMPLE>

CREATE TABLE PRODUCT(
	PCODE NUMBER PRIMARY KEY,
    PNAME VARCHAR2(200) NOT NULL,
    BRAND VARCHAR(60) NOT NULL,
    PRICE NUMBER NOT NULL,
    STOCK NUMBER DEFAULT 0
);


CREATE TABLE PRO_DETAIL(
	

);

*프로시져 ( PROCEDURE) : 

 ->PL/SQL 문을 저장하는 객체

 -> 필요할 떄마다 복잡한 구문을 다시 입력할 필요 없이 간단하게 호출해 결과를 얻을 수 있음

 -> 특정 로직을 처리하기만 하고 결과값을 반환하지 않음 (FUNCTION 과의 차이)

<프로시져 생성 방법>

 CREATE OR REPLACE PROCEDURE 프로시저명

    ( 매개변수명1 (IN / OUT / IN OUT) 데이터타입 [ := DEFAULT값],

        ....

     )

  IS

      선언부

 BEGIN

      실행부

  [EXCEPTION ~]

 END [프로시저명] ;

<프로시져 실행>

 EXECUTE/EXEC  `프로시져명` ;

<EX 매개변수 없는 프로시져>

CREATE OR REPLACE PROCEDURE TEST_P
IS
BEGIN
	DELETE FROM TABLE_A;
    DELETE FROM TABLE_B;
    COMMIT;
END;
/

EXECUTE TEST_P;

SELECT * FROM USER_SOURCE; --프로시져 작성 구문 저장 출력

<EX 매개변수 있는 프로시져>

CREATE OR REPLACE PROCEDURE TEST_P2(
	P2_VAR TABLE_A.A_NAME%TYPE;   
)
IS
BEGIN
	DELETE FROM TABLE_A
    WHERE A_NAME = P2_VAR;
    COMMIT;
END;
/

EXECUTE TEST_P2('MYNAME');  --지정 입력

EXEC TEST_P2('&NAME');  -- 입력창 입력

<EX IN, OUT 매개변수 있는 프로시져>

* IN 매개변수 

    -> 프로시져 내부에서 사용될 변수

* OUT 매개변수

    -> 프로시저 외부(호출해서) 에서 사용될 변수

CREATE OR REPLACE PROCEDURE TEST_P3(
	P3_ID IN TABLE_A.A_ID%TYPE,
    P3_NAME OUT TABLE_A.A_NAME,
    P3_PHONE OUT TABLE_A.A_PHONE,
    P3_ADDRESS OUT TABLE_A.A_ADDRESS
)
IS
BEGIN
	SELECT A_NAME, A_PHONE, A_ADDRESS
    INTO P3_NAME, P3_PHONE, P3_ADDRESS
    FROM TABLE_A
    WHERE A_ID = P3_ID;
END;
/

/*
프로시져에서 OUT으로 저장되어있는 매개변수를 호출해서
외부에서 받아낸 변수가 필요하다
--> 바인딩 변수
*/

VAR RESULT_NAME VARCHAR2(30);
VAR RESULT_PHONE VARCHAR2(15);
VAR RESULT_ADDRESS VARCHAR2(200);
EXECUTE TEST_P3('&ID', :RESULT_NAME, :RESULT_PHONE, :RESULT_ADDRESS);

PRINT RESULT_NAME;
PRINT RESULT_PHONE;
PRINT RESULT_ADDRESS;  --PRINT -> 변수 출력 명령어

SET AUTOPRINT ON; -- 바인드 변수값 자동 출력 설정

*FUNCTION : 프로시져와 사용 용도가 거의 비슷하지만 프로시져와 다르게 

                   OUT 매개변수를 사용하지 않아도 결과를 되돌려 받을 수 있다 (RETURN)

 <FUNCTION 생성 방법>

 CREATE OR REPLACE FUNCTION 함수명(매개변수1 매개변수타입 , ...)

 RETURN 데이터타입

 IS

    선언부

 BEGIN

    실행부

  RETURN 반환값;

[EXCEPTION~ ]

END [함수명];

<EX>

CREATE OR REPLACE FUNCTION P_CALCUL(
	P_ID IN TABLE_A.A_ID%TYPE   --받는 매개변수
 )
 RETURN NUMBER  --리턴 타입
 IS 
 	P_INCOME TABLE_A.A_SALARY%TYPE;  -값 저장 변수 선언
    P_COMSUME TABLE_A.A_USE%TYPE;
    MONTH_NET NUMBER;
 
 BEGIN
 	SELECT A_SALARY, A_USE
    INTO P_INCOME, P_CONSUME
    FROM TABLE_A
    WHERE A_ID = P_ID;
 	
    MONTH_NET := P_INCOME - p_CONSUME ;
    RETURN MONTH_NET;
 END;
 /
 
 --사용법
 
 VAR CAL NUMBER;
 EXECUTE :CAL := P_CALCUL('&ID');
 
 --OR SELECT문에 사용
 
 SELECT A_ID, A_NAME, P_CALCUL(A_ID) 순수입
 FROM TABLE_A
 WHERE P_CALCUL(A_ID) > 300000;

* 커서(CURSOR) 

-> SELECT문 처리 결과(RESULT SET)를 담고 있는 메모리 공간에 대한 포인터

-> 커서 사용 시 여러 ROW로 나타난 처리 결과에 순차적으로 접근 가능

->  묵시적 커서:  오라클에서 자동으로 생성되어 사용되는 커서

    (묵시적 커서 속성 정보 참조 시 커서명 = SQL)
    - 커서명%ROWCOUNT : SQL 처리 결과로 얻어온 ROW 수
                       0 시작, FETCH 시 마다 1씩 증가
    - 커서명%FOUND    : 커서 영역의 ROW 수가 한 개 이상일 경우 TRUE 아님 FALSE
    - 커서명%NOTFOUND : 커서 영역의 ROW 수가 없으면 TRUE, 아님 FALSE
    - 커서명%ISOPEN   : 커서가 OPEN 상태인 경우 TRUE(묵시적 커서는 항상 FALSE)

     (EX)
     --SELECT 하면 오라클이 알아서 묵시적 커서를 생성하고 조건을 만족하는 행을 조회
     --한 행씩 이동하며 수정하고 이때 이동한 행의 개수 카운트(ROWCOUNT)
     --최종적으로 화면에 ROWCOUNT 출력

->  명시적 커서: 사용자가 직접 선언해서 사용할 수 있는 커서

            <작성법>

            1) CURSOR --커서 선언

            2) OPEN -- 커서 오픈

            3) FETCH --커서에서 데이터 추출

            4) CLOSE --커서 닫기

<EX>

DECLARE
	CURSOR C1 IS 
    	SELECT A_ID, A_NAME, A_SALARY
        FROM TABLE_A
        WHERE A_SALARY>=300000;
    ID TABLE_A.A_ID%TYPE;
    NAME TABLE_A.A_NAME%TYPE;
    SAL TABLE_A.A_SALARY%TYPE;
BEGIN
	OPEN C1:
    LOOP
    	FETCH C1 INTO ID, NAME, SAL
        EXIT WHEN C1%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(ID || '/' || NAME || '/' || SAL);
    END LOOP;
    CLOSE C1;
END;
/

<EX2 FOR +CURSOR>

-> FOR문을 이용한 커서 =  커서의 OPEN, FETCH, CLOSE 를 자동으로 해준다(선언만 하면 됨)

DECLARE
	CUSOR C2 IS
    	SELECT A_ID, A_NAME, A_SALARY
    	FROM TABLE_A
   		WHERE A_SALARY>=300000;
     P_C2 TABLE_A%ROWTYPE;
BEGIN
	FOR DEPT IN C2
    	LOOP
        	DBMS_OUTPUT.PUT_LINE(
            'ID : ' ||  P_C2.A_ID||
            'NAME : ' || P_C2.A_NAME ||
            'SALARY : '|| P2_C2.A_SALARY);
        END LOOP;
END;
/

* PL/SQL 의 IF 문

 -> IF( ) ~ THEN ~

     ELSIF ( ) ~ THEN ~

     ELSE ~

     END IF 

DECLARE 
	INFO_A TABLE_A%ROWTYPE;
 BEGIN 
 	SELECT *
    INTO INFO_A
    FROM TABLE_A
    WHERE A_ID = '&A_ID' ;
    
    IF (INFO_A.A_NAME = 'MYNAME')
    	THEN DBMS_OUTPUT.PUT_LINE('MY INFO');
    
    --ELSIF ~ THEN ~ ELSE ~
    
    END IF;
    
END;
/

* PL/SQL 의 CASE 문

 -> ~ := CASE ~

        WHEN ~ THEN~

        WHEN ~ THEN~

        WHEN ~ THEN~

        WHEN ~ THEN~

        WHEN ~ THEN~

             END:

DECLARE
	INFO_A TABLE_A%ROWTYPE;
    ID VARCHAR2(20);
BEGIN 
	SELECT * 
    INTO INFO_A
    FROM TABLE_A
    WHERE A_ID = '&AID';
    
    ID := 
    CASE TABLE_A.A_ID
    WHEN 'AA' THEN 'FRIEND1'
    WHEN 'BB' THEN 'FRUEND2'
    WHEN 'CC' THEN 'FRUEND3'
    WHEN 'DD' THEN 'FRUEND4'
    WHEN 'EE' THEN 'FRUEND5'
    
    END;
    DBMS_OUTPUT.PUT_LINE(TABLE_A.A_ID || '는 ' || ID); 
END;
/

*PL/SQL 의 반복문

-> (BASIC LOOP) : 내부에 처리문을 작성하고 마지막에 LOOP를 벗어날 조건을 명시

    LOOP

         처리문

         조건문

          ( IF 조건식 THEN EXIT

          END IF )   

             or

          ( EXIT WHEN 조건식 )      

    END LOOP;

DECLARE 
	N NUMBER :=1;
BEGIN
	LOOP
    	DBMS_OUTPUT_PUT_LINE(N);
        EXIT WHEN N=5;
        
        N := N+1;
     END LOOP;
 END;
 /

* PL/SQL 의 반복문2

 -> (FOR 문) : 

    FOR 인덱스 IN (REVERSE) 초기값 ... 최종값

     LOOP

        처리문

     END LOOP;

BEGIN 
	FOR I IN 1..5
    LOOP
    	DBMS_OUTPUT.PUT_LINE(I);
    END LOOP;
END;
/
--거꾸로 
BEGIN 
	FOR I IN REVERSE 1..5
    LOOP
    	DBMS_OUTPUT.PUT_LINE(I);
    END LOOP;
END;
/

*반복문을 이용한 INSERT

CREATE TABLE MAKE1(
	R_NUM NUMBER(3),
    R_CHAR VARCHAR2(20),
    DT DATE
);

BEGIN 
	FOR I IN 1..10
    LOOP
    	INSERT INTO MAKE1 VALUES(I, '임의의수'||I , SYSDATE+I );
    END LOOP;
END;
/

*예외처리 : 오라클 내부에 미리 정의되어 있는 예외 (약 20개)

                따로 선언할 필요 없이 발생 시 예외절에 자동 트랩됨

         - 대표적인 시스템 예외
        -- NO_DATA_FOUND :  SELECT문이 아무런 데이터 행을 반환하지 못할 때
        -- TOO_MANY_ROWS : 하나만 리턴해야하는 SELECT문이 하나 이상의 행을 반환할 때
        -- INVALID_CURSOR : 잘못된 커서 연산
        -- ZERO_DIVIDE : 0으로 나눌 때
        -- DUP_VAL_ON_INDEX : UNIQUE 제약을 갖는 컬럼에 중복되는 데이터가 INSERT될 때 

<예외처리 구문>

EXCEPTION WHEN 예외명 1 THEN 예외처리구문1

                WHEN 에외명 2 THEN 예외처리구문 2

                .....

<EXAMPLE>

DECLARE 
	NUM NUMBER := 0;
BEGIN
	NIM := 10/0;
    DBMS_OUTPUT.PUT_LINE('SUCCESS');
  EXCEPTION
  	WHEN ZERO_DIVIDE 
    THEN DBMS_OUTPUT.PUT_LINE('ZERO_DIVIDE EXCEPTION발생!');
END;
/

* PL/SQL ? 

 -> 오라클 자체에 내장되어 있는 절차적 언어 (PROCEDUAL LANGUAGE)

 -> SQL 문장 내에서 변수 정의, IF LOOP FOR WHILE 등을 지원하며 SQL의 단점 보완

*PL/SQL 구조

1) 선언부 : DECLARE ~  변수나 상수 선언

2) 실행부 : BEGIN ~ 제어문, 반복문, 함수 정의 등 로직 

3) 예외처리부 : EXCEPTION~ 예외 발생 시 해결하기 위한 문장

* PL/SQL의 장점

1. BLOCK 구조로 다수의 SQL문을 한번에 ORACLE DB로 보내 처리하므로 수행 속도 향상

2. 모든 요소는 하나 또는 두 개 이상의 블록으로 구성하여 모듈화 가능

3. 동적으로 변수 선언 가능

4. EXCEPTION 을 통해여  ORACLE SERVER ERROR 처리가능 (사용자 정의 에러 정의 및 처리도 가능)

※프로시저 사용 시 출력하는 내용을 화면에 보이도록 하는 명령어

SET SERVEROUTPUT ON;

<HELLO WORLD 출력>

BEGIN

 DBMS_OUTPUT.PUT_LINE('HELLO WORLD');

END;

/

(/ : PL/SQL 블록 종료 의미) 

<변수 선언, 초기화> 

DECLARE 
	C1 NUMBER;
    C2 VARCHAR2(30);
    C3 VARCHAR(30):= 'MY' ;
    C4 CONSTANT NUMBER := 3.141592;
BEGIN
	C1 := 999;
   	C2 := 'NAME'
END;
/

오라클의 대입연산자 ->  :=

<레퍼런스 변수>  -> 변수의 데이터 타입을 테이블 또는 뷰의 컬럼을 참조하여 지정하는 변수

 -> 종류 : %TYPE, %ROWTYPE

 -> %TYPE : 해당 컬럼의 데이터 타입을 얻음

 -> %ROWTYPE : 해당 테이블 행 전체의 타입을 얻음

DECLARE 
	ID TABLE_A.A_ID%TYPE; 			--변수 선언
    NAME TABLE_A.A_NAME%TYPE;		--자료형은 해당 테이블컬럼을 참조
    
BEGIN 
	SELECT A_ID, A_NAME 			--변수에 SELECT 값 각각 대입
    INTO ID, NAME
    FROM TABLE_A
    WHERE A_ID = '&ID';
    
    DBMS_OUTPUT.PUT_LINE(ID); 		--저장된 변수 출력
    DBMS_OUTPUT.PUT_LINE(NAME);
END;
/

오라클 값 입력창 : '&이름'

<%ROWTYPE 활용>

DECLARE 
	ROW_V TABLE_A%ROWTYPE;

BEGIN 
	SELECT * 
    INTO ROW_V
    FROM TABLE_A
    WHERE A_ID = '&ID';

	DBMS_OUTPUT.PUT_LINE('A_ID: ' || TABLE_A.A_ID);
	DBMS_OUTPUT.PUT_LINE('A-NAME: ' || TABLE_A.A_NAME);    
	DBMS_OUTPUT.PUT_LINE('A_PHONE: ' || TABLE_A.A_PHONE);    --원하는 변수 출력
    
 END;
 /

*오라클 주요 객체 : VIEW , SEQUENCE, INDEX ..

1. VIEW : SELECT문의 실행 경과(RESULT SET)을 저장하는 객체

   ->논리적 가상 테이블로서 실제 값을 저장하고 있진 않음

   ->복잡한 SELECT 문의 쉬운 재사용을 위해서 사용하고

   ->테이블의 진짜 모습을 감출 수 있어 보안상으로도 장점이 있다

※ VIEW 사용시

 - ALTER 구문은 사용이 불가하다

 - VIEW 를 이용한 DML (SELECT, UPDATE, DELETE ) 가 가능은 하나 제약이 많이 따르기 떄문에 

   보통 조회 용도로만 쓰는것이 좋다

 -VIEW 를 이용해 INSERT를 진행하면 VIEW에 삽입된것 처럼 보이지만 원본 테이블에 삽입이 되는 것이다.

<VIEW 생성방법>

CREATE  VIEW `VIEW_NAME` 

AS `SUBQUERY` 

※ 추가옵션: 

 1. OR REPLACE : CREATE 뒤

  -> 동일 뷰 이름이 존재하면 덮어 쓰고 없으면 새로 생성

 2. FORCE / NOFORCE : CREATE 뒤

  -> FORCE : 서브쿼리에 사용된 테이블이 존재하지 않아도 뷰 생성

  -> NOFORCE : 서브쿼리에 사용된 테이블이 존재해야만 뷰 생성(기본 DEFAULT) 

 3. WITH CHECK OPTION : 서브쿼리 뒤

  ->옵션을 설정한 컬럼의 값을 수정 불가능하게 함 (조건을 벗어나게 변경 불가)

 4. WITH READ ONLY : 서브쿼리 뒤

  -> 조회만 가능하도록 함 (DML 전부 수행 불가) 

2. SEQUENCE : 순차적 번호 자동 발생기 역할의 객체

 -> PRIMARY 키 값으로 주로 사용

<SEQUENCE  생성방법> 

 CREATE SEQUENCE `SE_NAME`

※ 추가옵션: 뒤에 차례로 추가 가능

 1.  START WITH N : N부터 시작 , 기본 1

 2.  INCREMENT BY N : N씩 증가함 , 기본 1

 3. MAXVALUE N : 발생시킬 최대값 지정(10의 27승까지 가능) , 기본 NOMAXVALUE

 4. MINVALUE N : 발생시킬 최소값 지정(-10의 26승까지 가능) , 기본 NOMINVALUE

 5. CYCLE : 값 순환 여부 지정, 기본 NO

 6. CACHE BYTE크기 : 캐쉬메모리 지정, 기본 20BYTE

 (시퀀스 호출 시 미리 저장되어진 값들을 가져와 반환하므로 매번 시퀀스 생성 후 반환하는 것보다 속도 향상)

<SEQUENCE  사용방법>

1. `SE_NAME` . NEXTVAL : 다음 번호를 불러온다. 시작점이면 시작값 불러옴

2. `SE_NAME` . CURRVAL : 현재 번호를 불러온다

*시퀀스는 오류 / 롤백 등과 관계없이 NEXTVAL 구문이 수행되면 무조건 숫자를 증가시킨다

<SEQUENCE  변경방법>

 ALTER SEQUENCE `SE_NAME`

 ~옵션

*START WITH 는 변경이 불가하므로 DROP 후 재생성해야한다

3. INDEX : SQL명령문 중 SELECT 처리 속도를 향상시키기 위해서 컬럼에 대해 생성하는 객체

  -> 인덱스 내부 구조는 B트리로 이루어져있으며

  -> 이진트리 특성상 자동 정렬 및 검색 속도에 장점이 생김

  -> 시스템의 부하를 줄이므로 전체 성능 향상을 시킬 수 있다.

  -> 다만 , 인덱스를 추가하기 위한 저장공간이 필요하고

  ->    인덱스 생성의 시간이 걸리며

  ->   DML이 빈번한 경우에는 오히려 성능이 저하되는 단점이 있다.

  ->  PRIMARY KEY 또는 UNIQUE 제약조건이 설정되는 경우 인덱스가 자동으로 생성된다.

<INDEX 생성방법> 

  CREATE INDEX `INDEX_NAME`

  ON `TABLE_NAME` ( `COL_NAME1`, `COL_NAME2` ...  -또는 함수명/ 함수계산식)

  (인덱스를 생성했다고 자동적으로 사용되지는 않는다)

<INDEX 사용 방법>

 -> WHERE 절에 INDEX가 추가된 컬럼이 언급되는 것이 사용 방법

*DCL : 데이터 제어 언어

-데이터베이스, 데이터베이스 내의 객체에 대한 권한을 제어(부여, 회수) 하는 언어

1. GRANT : 권한 부여

  - GRANT 권한 1 , 권한 2, ...   TO 사용자 이름;

  - 한번에 여러개 부여 가능

2. REVOKE 권한 회수

  - REVOKE 권한 FROM 사용자 이름;

권한 종류 

• CREATE SESSION   : 데이터베이스 접속 권한
• CREATE TABLE     : 테이블 생성 권한
• CREATE VIEW      : 뷰 생성 권한
• CREATE SEQUENCE  : 시퀀스 생성 권한
• CREATE PROCEDURE : 함수(프로시져) 생성 권한
• CREATE USER      : 사용자(계정) 생성 권한
• DROP USER        : 사용자(계정) 삭제 권한
• DROP ANY TABLE   : 임의 테이블 삭제 권한

계정의 종류 : 

-관리자 계정 : 모든 권한과 책임 가짐

-사용자 계정 : 업무에 필요한 최소한의 권한만 가지는 것을 원칙

** ROLE **

-권한의 묶음

-여러가지 관련된 권한들을 묶어서 한번에 부여, 회수하는 용도로 사용

1)CONNECT : 데이터베이스 접속 권한 (= CREATE SESSION)

2)RESOURCE : 데이터베이스를 사용하기 위한 기본 객체 생성 권한을 묶어둔 롤

=> TABLE 외 7가지 생성 권한 + 테이블스페이스를 기본값 자동 부여

** 객체 권한 **

-특정 객체를 조작할 수 있는 권한을 부여

GRANT 권한 종류 [(컬럼명)] | ALL 
    ON 객체명 | ROLE 이름 | PUBLIC
    TO 사용자 이름;

<EXAMPLE>

CREATE USER A IDENTIFIED BY PW; (유저 생성)

GRANT SELECT ON TABLE_T TO A (권한 부여)

REVOKE INSERT ON TABLE_T FROM A (권한 회수) 

* TCL ? TRANSACTION CONTROL LANGUAGE

  -> 트랜잭션을 관리(제어, 처리) 하는 언어

* 트랙잭션? 

 - 데이터베이스의 논리적 연산 단위

 - 데이터의 변경 사항을 묶어 하나의 트랜잭션에 담아 처리한다

 - 트랜잭션에 담겨지는 대상이 되는 SQL : INSERT, UPDATE, DELETE (DML) 만 적용

 - 명령어 -> 메모리 임시 버퍼 -> DB 과정을 거침

1. COMMIT : 메모리 버퍼에 임시 저장된 데이터 변경 사항을 DB에 실제로 반영

 => 임시 버퍼에 있던것을 DB에 전부 반영

2. ROLLBACK; : 메모리 버퍼에 임시 저장된 데이터 변경 사항을 삭제하고 마지막 COMMIT 상태로 돌아가는 것

 => 임시 버퍼 삭제하고 DB로 되돌림

3. SAVEPOINT : 저장 지점을 정의하여 ROLLBACK 시 트랜잭션 전체의 내용을 삭제하는 것이 아닌 저장된 지점까지만                        삭제하는것

  => 임시 버퍼 ROLLBACK 적용 지점을 따로 설정

EX) SAVEPOINT 포인트명1 ;

      ROLLBACK TO 포인트명1;

*주의사항

1. TCL 구문은 DML에만 적용된다

2. DML 구문 작성 중 DDL 또는 DCL 구문이 수행될 경우

   트랜잭션 내용이 바로 DB에 반영됨( 자동 COMMIT)

2. ALTER : 객체를 수정하는 구문

 - ALTER TABLE `테이블명` -수정할 내용-

 - 수정할 내용 : 컬럼 추가,삭제 / 제약조건 추가, 삭제 / 컬럼 자료형 변경 / DEFAULT 값 변경

                      테이블, 컬럼, 제약조건 이름 변경 ... 

 *컬럼 추가 : ALTER TABLE `TNAME` 

                 ADD (  `CNAME` `자료형` (DEFAULT ~ )  ) ;

 *컬럼 자료형 / DEFAULT 값 변경 : 

   ALTER TABLE `TNAME`

    MODIFY  `CNAME1` 변경자료형~`

    MODIFY `CNAME2` DEFAULT 변경값~~ ;

 *컬럼 삭제

   ALTER TABLE `TNAME`

    DROP (`CNAME`) ;

    컬럼은 최소 1개는 남아있어야 함

    FK 제약조건이 기본설정되어 있는 경우 참조되는 컬럼 삭제 안되므로 옵션 주어야 함

    CASCADE CONSTRAINTS : 무시하고 삭제

 *제약 조건 추가

    ALTER TABLE `TNAME`

     ADD (생략가능)[CONSTRAINT `제약조건명` ]  `제약조건` (`CNAME` ~ )

    <NOT NULL> 의 경우

    ALTER TABLE `TNAME`

     MODIFY (`CNAME`) NOT NULL;

 *제약 조건 삭제 (수정은 없음)

   ALTER TABLE `TNAME`

    DROP CONSTRAINT `제약조건명`

   <NOT NULL>의 경우 아래 방법도 가능

    ALTER TABLE `TNAME`

     MODIFY `CNAME` CONSTRAINT `제약조건명` NULL;

 *컬럼 이름 변경

   ALTER TABLE `TNAME`

    RENAME COLUMN `TNAME` TO `변경TNAME` ;

 *제약조건명 변경

   ALTER TABLE `TNAME`

    RENAME CONSTRAINT `제약조건명` TO `변경제약조건명`

 *테이블명 변경

   ALTER TABLE `TNAME`

    RENAME TO `변경테이블명`;

 3. DROP : 객체 삭제

    DROP TABLE `TNAME`;

   -참조관계로 삭제 불가시

     방법1 .   자식을 먼저 삭제 후 부모 삭제

     방법2 .   CASCADE CONSTRAINTS 옵션 추가

*DDL :

 -Data Definition Language

 -데이터 정의 언어.

 -객체를 만들고 수정하고 삭제하는 구문 ( CREATE , ALTER, DROP)

1. CREATE : 객체(테이블 , 인덱스 , 뷰 등..) 를 생성하는 구문

 -CREATE TABLE `이름` (

  `컬럼명`  `자료형`  (DEFAULT ~ ) (` 제약조건~` )

   ...

  )

*서브쿼리를 이용한 테이블 복사(서브쿼리로 JOIN, 지정 가능 , WHERE 조건 가능)

 CREATE TABLE BCOPY AS ( SELECT * FROM B);

  -->제약조건 중 NOT NULL 제약조건만 복사되고 나머지는 사라진다.

*오라클의 데이터타입

1.CHAR : 고정길이 문자(빈칸이 있어도 고정)

2.VARCHAR2 : 가변길이 문자(빈칸 생기면 날림)

3.NUMBER : 숫자 (정수, 실수)

4.DATE : 날짜

5.CLOB : 대용량 문자

6.BLOB : 대용량 이진 데이터

7.TIMESTAMP : 밀리세컨드 단위 추가 DATE

*컬럼에 주석 넣기

 -COMMENT ON COLUMN `테이블명`.`컬럼명` IS `내용`

*제약조건 : 데이터 무결성( 저장된 데이터의 정확성과 일관성을 보증) 을 목적으로

                조건을 설정하는 강제 제약     

 1. NOT NULL        : 널을 허용하지 않는다

  컬럼 레벨에서만 가능하다

 `컬럼명` `자료형` (생략가능)[CONSTRAINT 제약조건명] NOT NULL 

 2. UNIQUE           :  중복값을 허용하지 않는다 (NULL은 가능하다)

   컬럼 레벨     --`컬럼명` `자료형` (생략가능)[CONSTRAINT 제약조건명] UNIQUE

   테이블 레벨  --(생략가능)[CONSTRAINT 제약조건명] UNIQUE (`컬럼명1` , `컬럼명2` )

    *테이블 레벨에서 묶어서 UNIQUE 복합키 설정이 가능하다 ( 두 컬럼이 모두 중복되는 경우만 걸러냄)

 3. PRIMARY KEY   :  널과 중복을 허용하지 않으며 테이블의 식별자로 사용한다

                              한 테이블당 한 개만 설정 가능하고 테이블 레벨에서 여러 컬럼을 묶어 설정할 수 있다.

컬럼 레벨     --`컬럼명` `자료형` (생략가능)[CONSTRAINT 제약조건명] PRIMARY KEY

테이블 레벨  --(생략가능)[CONSTRAINT 제약조건명] PRIMARY KEY (`컬럼명1` , `컬럼명2` )

 4. FOREIGN KEY    : 참조된 다른 테이블의 컬럼이 제공하는 값만 사용할 수 있음

                             FOREIGN KEY 제약조건으로 테이블간에 관계가 형성됨

                             제공 되는 값 외 NULL도 사용 가능

                             참조될 수 있는 컬럼은 PRIMARY KEY / UNIQUE 만 가능하며

                             참조할 테이블의 참조 컬럼명이 생략되면

                             참조 테이블의 PRIMARY KEY를 자동 참조할 컬럼으로 설정함

컬럼 레벨     --`컬럼명` `자료형` (생략가능)[CONSTRAINT 제약조건명] REFERENCES `참조 테이블명` (`참조 컬럼명`)

테이블 레벨  --(생략가능)[CONSTRAINT 제약조건명] FOREIGN KEY (컬럼명) REFERENCES `참조 테이블명` (참조

                                                                                                                                             컬럼명)

 5. CHECK             : 저장 가능한 값 조건을 미리 설정

=> 모두 테이블레벨에서도 가능하지만 NOT NULL은 테이블 레벨에 불가능하다

컬럼 레벨     --`컬럼명` `자료형` (생략가능)[CONSTRAINT 제약조건명] CHECK (`컬럼명` 비교연산자 비교값)

테이블 레벨  --(생략가능)[CONSTRAINT 제약조건명] CHECK(`컬럼명` 비교연산자 비교값)

   *비교연산자 : > ,< ,IN, BETWEEN A AND B, ...

*FOREIGN KEY 삭제 옵션 ( FOREIGN KEY 제약조건 뒤에 추가)

 - 부모 테이블의 데이터 삭제 시 자식 테이블의 데이터를 처리하는 방법

 - 입력 없어도 ON DELETE RESTRICTED 가 DEFAULT( 참조되는 컬럼이면 삭제 불가)

 - ON DELETE SET NULL : 부모키 삭제 시 자식키를 NULL로 변경하는 옵션

 - ON DELETE CASCADE : 부모키 삭제시 자식키도 함께 삭제됨