[PL/SQL] PL/SQL 기초

PL/SQL 기초

블록

PL/SQL 소스 프로그램의 기본단위. 선언부, 실행부, 예외처리부로 구성된다.

- 이름없는 블록 : 익명 블록

- 이름있는 블록 : 함수, 프로시저, 패키지 등

구조

이름부
IS(AS)
    선언부
BEGIN
    실행부
EXCEPTION
    예외처리부
END;

이름부

블록의 명칭이 오는데, 생략하게되면 익명 블록이 된다.

선언부

DECLARE로 시작되며, 실행부와 예외처리부에서 사용할 각종 변수, 상수, 커서 등을 선언한다. 중요한 점은 변수 선언이나 실행부와 예외처리부에서 사용하는 각종 문장의 끝에 반드시 세미콜론이 붙어야한다는 것이다. 사용할 변수나 상수가 없다면 생략할 수 있다.

실행부

실제 로직을 처리하는 부분. 각종 문장(일반 쿼리문, 조건문, 반복문 등)이 올 수 있고, 비즈니스 로직을 구현한다. DDL문은 사용불가능하며, DML문만 사용 가능하고, 모든 문장의 끝에는 세미콜론이 붙어야한다.

예외처리부

EXCEPTION 절로 시작되는 부분. 오류가 발생하면 처리할 내용을 기술하는 부분이며 생략 가능하다.

DECLARE
    VI_NUM  NUMBER;
BEGIN
    VI_NUM := 100;
    
    DBMS_OUTPUT.PUT_LINE(VI_NUM);
END;

위 예제는 프로시저가 완료됬다는 메시지만 출력되고, 결과물은 보이지 않는다.

결과물을 보려면 아래처럼 SET SERVEROUTPUT ON 을 명시해야한다.

SET SERVEROUTPUT ON
DECLARE
    VI_NUM  NUMBER;
BEGIN
    VI_NUM := 100;
    
    DBMS_OUTPUT.PUT_LINE(VI_NUM);
END;

하지만 이 방법은 DB에 새로 연결할 때마다 해줘야해서 매우 번거롭다. SET SERVEROUTPUT ON 없이 결과물을 볼 수는 없을까?

상단 메뉴의 보기 메뉴에서 DBMS 출력 을 클릭하면 에디터 하단에 아래와같이 DBMS 출력 창이 뜬다.

녹색 + 버튼을 누르면 아래 사진과 같이 접속 선택 창이 뜨는데, 원하는 접속을 선택하고 확인을 누른다.

DBMS 출력창에 선택한 접속명이 쓰여진 탭이 생겼다. 앞으로 실행되는 PL/SQL 구문의 결과물은 이 창을 통해 확인할 수 있다.

위 PL/SQL을 실행하면 이렇게 결과를 확인할 수 있다.

변수

변수명 데이터타입 := 초기값;

선언부에서 변수 선언을 하고 실행부에서 사용한다.

변수 선언과 동시에 초기값을 할당할 수 있는데, 미할당시 데이터타입과 상관없이 초기값은 NULL이다.

PL/SQL에서 데이터 타입은 두가지로 구분되는데, 다음과 같다.

SQL 데이터 타입
PL/SQL 데이터 타입	BOOLEAN	TRUE / FALSE / NULL
	PLS_INTEGER	-2,147,483,648과 2,147,483,647의 범위를 가진 숫자타입. NUMBER형에 비해 저장공간을 덜 차지한다.
	BINARY_INTEGER	PLS_INTEGER와 동일

NATURAL	PLS_INTEGER 중 음수 제외(0포함)
NATURALN	PLS_INTEGER 중 음수 제외(NULL 할당 불가. 선언시 반드시 초기화)
POSITIVE	PLS_INTEGER 중 양수 (0 미포함)
POSITIVEN	PLS_INTEGER 중 양수 (NULL 할당 불가. 선언시 반드시 초기화)
SIGNTYPE	PLS_INTEGER 중 -1, 0, 1
SIMPLE_INTEGER	PLS_INTEGER 중 NULL이 아닌 모든 값. 선언시 반드시 초기화

상수

변수명 CONSTANT 데이터타입 := 값;

CONSTANT 키워드를 붙여 변수와 구별하며, 선언시 반드시 초기화한다.

연산자의 우선순위

제곱 연산자 : **

양수/음수 식별 연산자 : +, -

곱셈 나눗셈 :  *, /

덧셈 뺄셈 문자열연결 연산자 : +, -, ||

비교 연산자 : =, <, >, <=, >=, <>, !=, ~=, ^=, IS NULL, LIKE, BETWEEN, IN

논리 연산자 : NOT, AND, OR

예제

VS_EMP_NAME에 EMP_NAME을, VS_DEP_NAME에 DEPARTMENT_NAME을 초기화해 출력한다.

DECLARE
    VS_EMP_NAME     VARCHAR2(80);
    VS_DEP_NAME     VARCHAR2(80);
BEGIN
    SELECT  A.EMP_NAME, B.DEPARTMENT_NAME
    INTO    VS_EMP_NAME, VS_DEP_NAME
    FROM    EMPLOYEES A
            , DEPARTMENTS B
    WHERE   A.DEPARTMENT_ID = B.DEPARTMENT_ID
    AND     A.EMPLOYEE_ID = 100;
    
    DBMS_OUTPUT.PUT_LINE(VS_EMP_NAME || ' - ' || VS_DEP_NAME);
END;

 주의해야할 점

SELECT 문의 컬럼과 INTO 절의 변수의 개수,순서,데이터타입이 동일해야한다.

 변수의 개수가 많아지면 해당 컬럼에 대응되는 변수 타입을 일일이 찾기 번거롭다. 그럴땐 어떻게 해야할까?

%TYPE을 사용하면 해당 변수에 컬럼 타입을 자동으로 가져온다.

변수명 테이블명.컬럼명%TYPE;

DECLARE
    VS_EMP_NAME     EMPLOYEES.EMP_NAME%TYPE;
    VS_DEP_NAME     DEPARTMENTS.DEPARTMENT_NAME%TYPE;
BEGIN
    SELECT  A.EMP_NAME, B.DEPARTMENT_NAME
    INTO    VS_EMP_NAME, VS_DEP_NAME
    FROM    EMPLOYEES A
            , DEPARTMENTS B
    WHERE   A.DEPARTMENT_ID = B.DEPARTMENT_ID
    AND     A.EMPLOYEE_ID = 100;
    
    DBMS_OUTPUT.PUT_LINE(VS_EMP_NAME || ' - ' || VS_DEP_NAME);
END;

PRAGMA 키워드

컴파일러가 실행되기 전에 처리하는 전처리 역할을 한다.

PL/SQL 블록의 선언부에 명시한다.

사용법

PRAGMA AUTONOMOUS_TRANSACTION

트랜잭션 처리를 담당하는데, 주 트랜잭션이나 다른 트랜잭션에 영향을 받지 않고 독립적으로 현재 블록 내부에서 데이터베이스에 가해진 변경사항을 COMMIT이나 ROLLBACK하라는 지시를 한다.

PRAMGA EXCEPTION_INIT(예외명, 예외번호)

사용자 정의 예외 처리를 할때 사용한다.

특정 예외번호를 명시해서 컴파일러에게 이 예외를 사용한다는 것을 알리는 역할을한다.

    

PRAGMA RESTRICT_REFERENCES(서브 프로그램명, 옵션)

오라클 패키지를 사용할때 선언해 놓으면 패키지가 속한 서브 프로그램(주로 함수에 사용)에서 옵션값에 따라 특정 동작을 제한할 때 사용된다.

예를 들어, RNDS라는 옵션을 주면 해당 서브 프로그램에서 테이블 데이터 쿼리를 제한하고, WNDS라는 옵션을 주면 테이블 데이터를 조작하지 못한다.

PRAGMA SERIALLY_REUSABLE

패키지 메모리 관리를 쉽게할 목적으로 사용되며, 패키지에 선언된 변수에 대해 한 번 호출된 후 메모리를 해제시킨다.

즉, 이 옵션을 설정하면 패키지 변수에 값을 할당하더라도 다음번에 호출할때는 할당한 값에 대한 메모리를 해제시켜 해당 변수는 초기값이 된다.

 [PL/SQL] 조건문 보러가기

 [PL/SQL] 반복문 보러가기