SQL 8일차(1)

<PL/SQL>

        오라클 DBMS에서 사용하는 절차적 SQL 확장 언어이다.
        일반 SQL의 단점을 보완해서 변수의 정의, 조건(IF), 반복(FOR, WHILE)등을 지원하여
            다수의 SQL문을 한번에 실행할 수 있음.
        PL/SQL은 블록구조를 사용함.
            -블록구조는 {}를 사용해서 코드를 나누거나 TAB(들여쓰기)를 통해 코드를 나누는
                것을 블록구조라고 한다.
        
        PL/SQL 구조
        DECLARE         --(선택)선언부 : 변수, 상수등을 선언
        BEGIN           --(필수)실행부 : SQL문 또는 제어문 등의 로직을 기술
        EXCEPTION       --(선택)예외처리부 : 오류 발생시 처리
        END;            --(필수)PL/SQL의 마지막에 기술

 

출력 활성화

SET SERVEROUTPUT ON;

 

간단한 BEGIN END 문법으로 TEXT 출력하기

BEGIN
    DBMS_OUTPUT.PUT_LINE('HELLO ORACLE');
END;

 

1.DECLARE 선언부

        변수(식별가능한 이름을 메모리에 부여하고 메모리를 할당하고 값을 저장하다가 
            필요할 때 값을 가져오는) 및 상수를 선언하는 공간
        일반타입, 레퍼런스타입, ROW타입 변수가 있다.
    
        1-1) 일반타입 변수 선언 및 초기화
        DECLARE 
            변수명 [CONSTANT] 자료형 [:= 초기값]

 

DECLARE
    EID NUMBER; 
    ENAME VARCHAR2(20);
    PI CONSTANT NUMBER :=3.14;
BEGIN
    EID := 800;
    ENAME := '홍길동';
    
    DBMS_OUTPUT.PUT_LINE('EID : ' || EID);
    DBMS_OUTPUT.PUT_LINE('ENAME : ' || ENAME);
    DBMS_OUTPUT.PUT_LINE('PI : ' || PI);
END;
/

변수 선언과 상수 선언, 변수에 값을 대입 후 출력하기

 

DECLARE
    EID NUMBER; 
    ENAME VARCHAR2(20);
    PI CONSTANT NUMBER :=3.14;
BEGIN
    EID := &번호;
    ENAME := '&이름';
    
    DBMS_OUTPUT.PUT_LINE('EID : ' || EID);
    DBMS_OUTPUT.PUT_LINE('ENAME : ' || ENAME);
    DBMS_OUTPUT.PUT_LINE('PI : ' || PI);
END;
/

&를 사용하여 값을 입력 받는다.

 

1-2) 레퍼런스(참조) 타입 변수

    어떤 테이블의 어떤 컬럼의 데이터 타입을 참조해서 사용할지를 정하는 방식

 

DECLARE
    EID EMPLOYEE.EMP_ID%TYPE; 
    ENAME EMPLOYEE.EMP_NAME%TYPE;
    SAL EMPLOYEE.SALARY%TYPE;
BEGIN
--    EID := 800;
--    ENAME := '이이';
--    SAL := 1000000;
    
    SELECT EMP_ID, EMP_NAME, SALARY -- EMPLOYEE 테이블에서 값을 가져와 INTO 변수에
                                    --      값을 넣는다.
    INTO EID, ENAME, SAL
    FROM EMPLOYEE
    WHERE EMP_ID = &사번;
    
    DBMS_OUTPUT.PUT_LINE('EID : ' || EID);
    DBMS_OUTPUT.PUT_LINE('ENAME : ' || ENAME);
    DBMS_OUTPUT.PUT_LINE('SAL : ' || SAL);
    
END;
/

변수명    테이블.컬럼명%TYPE으로 참조할 수 있다.

SELECT문으로 테이블에서 값을 가져올 수 있다.

 

1-3) ROW타입 변수 선언

        테이블의 한 행에 대한 모든 컬럼을 한번에 담을 수 있는 변수
        변수명 테이블명%ROWTYPE

 

DECLARE
    E EMPLOYEE%ROWTYPE;
BEGIN
    SELECT *
    INTO E
    FROM EMPLOYEE
    WHERE EMP_ID = &사번;
    
    DBMS_OUTPUT.PUT_LINE(E.EMP_ID ||', ' || E.EMP_NAME ||', ' || E.BONUS);
END;
/

한 행 전체의 데이터를 가져온다.

 

2. BEGIN 실행부

<조건문>

IF 조건식 THEN

    실행내용 
END IF;

IF 조건식 THEN
    실행내용
ELSE
    실행내용
END IF;

 

IF 조건식1
    THEN 실행내용
ELSIF 조건식2
    THEN
...
ELSE 
    실행내용
END IF;

 

<반복문>

 

LOOP
     반복하고 싶은 실행문
     *EXIT WHEN 탈출조건
END LOOP;

 

탈출조건은 조건식
       1) IF 조건식 THEN EXIT; END IF;
       2) EXIT WHEN 조건식;

 

FOR 변수 IN [REVERSE] 초기값... 최종값
LOOP
    반복할 코드
END LOOP;

 

WHILE 반복문이 수행될 조건
LOOP
    반복할 명령어
END LOOP;

 

DROP TABLE TEST;

CREATE TABLE TEST(
    TNO NUMBER PRIMARY KEY,
    TDATE DATE
);

CREATE SEQUENCE SEQ_TNO;

DECLARE
    I NUMBER := 1;

BEGIN
    FOR I IN 1..10
    LOOP
        INSERT INTO TEST VALUES(SEQ_TNO.NEXTVAL, SYSDTAE);
    END LOOP;
END;
/

테이블, 시퀀스를 생성하고 FOR문에 INSERT를 추가하여 10번의 INSERT를 실행시킨다.

3. 예외처리

    예외(EXCEPTION) : 실행 중 발생하는 오류
    
    EXCEPTION
        WHEN 예외명1 THEN 처리구문1;
        WHEN 예외명2 THEN 처리구문2;
        WHEN 예외명3 THEN 처리구문3;
        ...
    시스템 예외(오라클이 미리 설정해둔 예외)
        -NO_DATA_FOUND : SELECT한 결과가 한 행도 없을 때
        -TOO_MANY_ROWS : SELECT한 결과가 여러행일 경우
        -ZERO_DIVDE : 0으로 나눌 경우
        -DUP_VAL_ON_INDEX : UNIQUE 제약조건 위배
        ...

 

BEGIN
    UPDATE EMPLOYEE
    SET EMP_ID = &변경할사번
    WHERE EMP_NAME = '노옹철';
    
EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN DBMS_OUTPUT.PUT_LINE('이미 존재하는 사번입니다.');
END;
/

노옹철의 사번을 입력받는데 같은 값이 이미 존재할 경우 예외처리를 한다.