DAY 66. [DataBase] PL/SQL

 

PL/SQL

Procedural Language extension to SQL 의 약자이다.

오라클 자체에 내장되어 있는 **절차적 언어(프로그래밍 언어)**이다.

SQL 단점을 보완하여 SQL 문장 내에서 변수의 정의, 조건 처리(IF), 반복 처리(LOOP, FOR, WHILE) 등을 지원한다.

• 다수의 SQL 문을 한 번에 실행할 수 있다.

 

✔️구조

PL/SQL 구조

 

** 선언부&예외부는 생략 가능하지만 실행부는 반드시 존재해야한다.

** 실행부에는 쿼리도 작성 가능

 

 

▪️ 선언부(DECLARE SECTION)

변수 및 상수를 선언하는 공간이다.

선언과 동시에 초기화가 가능하다.

변수 및 상수는 일반 타입 변수, 레퍼런스 타입 변수, ROW 타입 변수로 선언해서 사용할 수 있다.

 

SET SERVEROUTPUT ON;
* 프로시저를 사용하여 출력하는 내용을 화면에 보여주도록 설정하는 환경변수로 기본 값은 OFF여서 ON으로 변경 후 사용이 가능하다.

BEGIN
	DMBS_OUTPUT.PUT_LIN('HELLO WORLD');
END;
/


-- PUT_LINE 이라는 프로시저를 이용하여 출력(DBMS_OUTPUT 패키지에 속해져 있다.)

 

-- 1) 일반 타입 변수 선언 및 초기화
변수명  [CONSTANT] 자료형(크기) [:= 값];

-- 타입 변수 선언 : 변수의 선언과 초기화, 변수 값 출력
-- 데이터 타입을 직접 지정해주는 변수 = 스칼라 변수

DECLARE
	EMP_ID NUMBER;
	EMP_NAME VARCHAR2(30);
	PI CONSTANT NUMBER := 3.14;

BEGIN
	EMP_ID := 888;
	EMP_NAME := '배장남';
-- PI := 3.15; -- 에러 발생 이미 위에서 3.14로 상수로써 선언했기 때문에 바꿀 수 없다.
	DBMS_OUTPUT.PUT_LINE('EMP_ID : ' || EMP_ID);
	DBMS_OUTPUT.PUT_LINE('EMP_NAME : ' || EMP_NAME);
END;
/

-- 오라클에서 대입 연산자는 := 를 사용 ( cf. 비교 연산자는 = )

-------------------------------------------------------------------
-- 2) 레퍼런스 타입 변수 선언 및 초기화
변수명 테이블명.컬럼명%TYPE;

-- 타입 변수 선언 : 레퍼런스 변수의 선언과 초기화, 변수 값 출력
-- 참조 변수 방식
DECLARE
	EMP_ID EMPLOYEE.EMP_ID%TYPE;
	EMP_NAME EMPLOYEE.EMP_NAME%TYPE;
	SAL EMPLOYEE.SALARY%TYPE;

BEGIN
	SELECT EMP_ID, EMP_NAME, SALARY -- 조회하는 데이터의 갯수와 타입이
	INTO EMP_ID, EMP_NAME, SAL  -- 대입할 데이터의 갯수와 타입이 일치해야 한다.
	FROM EMPLOYEE
	WHERE EMP_ID = '&EMP_ID'; -- & (앰퍼샌드) 기호는 대체 변수(값을 입력)를 입력받기 위한 창을 띄어주는 구문이다.
-- WHERE EMP_NAME = '&NAME';
	DBMS_OUTPUT.PUT_LINE('EMP_ID : ' || EMP_ID);
	DBMS_OUTPUT.PUT_LINE('EMP_NAME : ' || EMP_NAME);
END;
/

---------------------------------------------------------------------
-- 3) ROW 타입 변수 선언 및 초기화
-- 변수명 테이블명%ROWTYPE;

-- 타입 변수 선언 : 한 행에 대한 ROWTYPE 변수의 선언과 초기화, 값 출력
DECLARE
	E EMPLOYEE%ROWTYPE;
BEGIN
	SELECT * 
	INTO E
	FROM EMPLOYEE
	WHERE EMP_NAME = '&사원명'

	DBMS_OUTPUT.PUT_LINE('사번 : ' || E.EMP_ID);
	DBMS_OUTPUT.PUT_LINE('이름 : ' || E.EMP_NAME);
	DBMS_OUTPUT.PUT_LINE('주빈번호 : ' || E.EMP_NO);
	DBMS_OUTPUT.PUT_LINE('급여 : ' || E.SALARY);
END;
/

-- 여기에서 E는 "하나의 테이블의 여러 컬럼의 값을 한꺼번에 저장할 수 있는 변수"
-- 테이블의 하나의 행의 타입을 참조한다. 여러 컬럼의 타입의 값을 저장할 수 있는 변수를 선언한다. 모든 컬럼을 조회하는 경우에 사용하면 편리하다.
-- 테이블 구조가 바뀌면 변수도 자동으로 바뀐다.
-- 참조 변수 방식
-- 변수에 저장되어야 할 타입을 정확하게 모를 때 사용하기도 한다.

 

 

▪️ 실행부(EXECUTABLE SECTION)

2-1) 선택문
    1) 단일 IF 구문
       [표현법]
       IF 조건식 THEN
          실행 문장
       END IF;
  -- BEGIN 구문안에서 함수 호출 가능, 변수 선언에서도 함수 사용 가능

		2) IF ~ ELSE 구문
      [표현법]
      IF 조건식 THEN
         실행 문장
      ELSE
         실행 문장
      END IF;

		3) IF ~ ELSIF ~ ELS 구문
        [표현법]
        IF 조건식 THEN
           실행 문장
        ELSIF 조건식 THEN
           실행 문장
        ...
        [ELSE
           실행 문장]
        END IF;

		4) CASE 구문 (선택문, JAVA의 SWITCH문과 비슷하다.)
       [표현법]
       CASE 비교대상
            WHEN 비교값1 THEN 결과값1
            WHEN 비교값2 THEN 결과값2
            ...
            [ELSE 결과값]
       END;

2-2) 반복문
  1) BASIC LOOP
     [표현법]
      LOOP
          반복적으로 실행시킬 구문
                    
           [반복문을 빠져나갈 조건문 작성]
          1) IF 조건식 THEN EXIT; END IF;
          2) EXIT WHEN 조건식;
      END LOOP;


	2) FOR LOOP
     [표현법]
     FOR 변수 IN (REVERSE) 초기값..최종값
     LOOP
         반복적으로 실행할 구문;
     END LOOP;

-- 반복 횟수를 알고 있는 경우에 사용한다.


	3) WHILE LOOP
		 [표현법]
		 WHILE 반복문을 빠져나갈 조건 
		 LOOP
					반복적으로 실행할 구문;
		 END LOOP;

-- WHILE LOOP은 LOOP를 빠져나갈 조건이 WHILE 뒤에 작성이 된다.

 

 

▪️ 예외처리부(EXCEPTION SECTION)

[표현법]
            DECLARE
            ...
            BEGIN
            ...
            EXCEPTION
                WHEN 예외명 1 THEN 예외처리구문 1;
                WHEN 예외명 2 THEN 예외처리구문 2;
                ...
                WHEN OTHERS THEN 예외처리구문;

- NO_DATA_FOUND : SELECT 문의 수행 결과가 한 행도 없을 경우 발생한다.
- TOO_MANY_ROWS : 한 행이 리턴되어야하는데 SELECT 문에서 여러 개의 행을 반환할 때 발생한다.
- ZERO_DIVIDE : 숫자를 0으로 나눌 때 발생한다.
- DUP_VAL_ON_INDEX : UNIQUE 제약 조건을 가진 컬럼에 중복된 데이터가 INSERT 될 때 발생한다.