Oracle(PL/SQL) 구성요소

변수는 다른 프로그래밍 언어에서 사용하는 변수와 개념이 같으며 선언부에서 변수 선언을 하고 실행부에서 사용한다. 변수 선언 방식은 다음과 같다.

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

변수 선언과 동시에 초깃값을 할당할수 있는데, 초깃값을 할당하지 않으면 데이터 타입과 상관없이 NULL 이된다. 

 

변수로 선언할 수 있는 테이터 타입은 크게 두가지로 구분할 수 있는데, 하나는 SQL 데이터 타입이고 다른 하나는 PL/SQL 데이터 타입이다. 사실 이 두가지를 엄격히 구분하지는 않는다. 왜냐하면  PL/SQL 데이터 타입에는 SQL 데이터 타입이 포함되어 있기 때문이다. 

PL/SQL 데이터 타입인  BOOLEAN, PLS_INTEGER,BINARY_INTEGER 등에 대해 알아 보자.

 

BOOLEAN  타입은 어떤 값이 참인지 거짓인지 판별할 때 사용하며, TRUE,FALSE,NULL 세가지 값을 갖고 있다. PLS_INTEGE의 범위는

 -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 란 키워드를 붙여 변수와 구분하며 , 선언할 때 반드시 초기화 해야 하고 실행부에서 상부를 다른 값으로 변경할 수 없다. 상수는 특정 값을 할당해 놓고 실행부에서 사용하고자 할 때 사용한다. 예를 들어 ,2.01 이란 특정 변환값이 있다고 하자 실행부에서 이 값을 곱해서 연산을 하는 부분이 많은데 만약 이 값이 3.01로 변경이 됐다면 코드 전체를 훓어 2.01을 3.01로 수정한 뒤 다시 컴파일 해야한다. 즉 이 값을 100번 사용했다면 100번 수정해야 한다는 뜻이다. 하지만 이 값을 상수로 만들면 값이 바뀌더라도 선언부 에서 한 번만 바꾸면 된다.

 

연산자

PL/SQL 블록에서는 모든  SQL 연산자를 사용할 수 있으며, 연산자 우선순위는 다음과 같다.

연산자	설명
**	제곱 연산자
+,-	양수 음수 식별 연산자
*,/	곱셈, 나눗셈
+,-,||	곱셈 나눗셈
=.<,>,<=,>=,<>,!=,~=,^=,IS NULL. LIKE. BETWEEN, IN	비교 연산자
NOT	논리 연산자
AND	논리 연산자
OR	논리 연산자

 

우선 순위가 가장 높은 ** 연산자는 제곱 연산자를 뜻하는데 PL/SQL 에서만 사용할 수 있다. 예를 들어, 2**3 는 2의 3제곱을 뜻한다. 

 

   SET SERVEROUTPUT ON
    SET TIMING ON
    DECLARE 
        a INTEGER := 2**2*3**2;
        BEGIN
            DBMS_OUTPUT.PUT_LINE('a = ' || TO_CHAR(a));
            
            END;

 

 

위 쿼리를 풀어보자면 (2²*3²) = ( 4 * 9 ) = 36 이 된 것이다.

x	y	xANDy	xORy	NOT x
TRUE	TRUE	TRUE	TRUE	FALSE
TRUE	FALSE	FALSE	TRUE	FALSE
TRUE	NULL	NULL	TRUE	FALSE
FALSE	TRUE	FALSE	TRUE	TRUE
FALSE	FALSE	FALSE	FALSE	TRUE
FALSE	NULL	FALSE	NULL	TRUE
NULL	TRUE	NULL	TRUE	NULL
NULL	FALSE	FALSE	NULL	NULL
NULL	NULL	NULL	NULL	NULL

 

주석

PL/SQL 뿐만 SQL 구문에서도 주석을 사용할수 있다. 한줄 전체를 주석 처리 할때는 맨 앞에 '--' 를 명시하고, 여러 줄의 코드에 주석을 붙일 때는 주석 처리할 부분의 처음과 끝에 '/* ' 와 '*/' 를 붙인다.

 

 

DML 문

PL/SQL 블록 상에서 변수, 상수, 연산자는 사실 부차적인 용도로 사용될 뿐이다. 실제로 PL/SQL 블록을 작성하는 원래의 목적은 테이블 상에 있는 데이터를 이리저리 가공해서 특정 로직에 따라 무언가를 처리하는 것이며, 따라서 주로 사용되는 것은  SQL 문이다.  SQL 문중 DDL은  PL/SQL 상에서 직접 쓸수 없고 DML 문만 사용한다.

 

 DECLARE 
        VS_EMP_NAME VARCHAR2(80);
        VS_DEP_NAME VARCHAR2(80);
        BEGIN 
            SELECT A.FIRST_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;
                    
                    
DECLARE 
        VS_EMP_NAME EMPLOYEES.FIRST_NAME%TYPE;
        VS_DEP_NAME DEPARTMENTS.DEPARTMENT_NAME%TYPE;
        BEGIN 
            SELECT A.FIRST_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;

VS_EMP_NAME , VS_ DEP_NAME 두개의 변수를 선언한뒤, 사원 테이블에서 사번이 100인 사원의 이름과 부서명을 가져와 변수에 할당한 뒤 출력하였다 . 여기에서 중요한점은 테이블에서 있는 데이터를 선택해 변수에 할당할때는 SELECT 문에서 INTO 절을 사용한다는 점이다.

이때 선택하는 컬럼에 따라 변수의 순서, 개수, 데이터 타입을 반드시 맞춰 줘야한다.

 

또한 사원테이블의 사원명과 부서명 타입을 동일하게 맞춰서 변수를 선언했는데 변수의 개수가 많아 지면 해당 컬럼에 대응되는 변수 타입을 일일이 찾기가 번거로울 수 있다. 이럴때는 다음과 같이 %TYPE 키워드를 쓰면 해당 변수에 컬럼 타입을 자동으로 가져온다.

%TYPE 키워드를 사용하면 일일이 변수 타입을 찾는 번거로움도 제거하고 데이터 타입을 잘못 선언할 위헙도 없앨 수 있으므로 매우 유용한 기능 중 하나다. PL/SQL 내에서는  SELECT 문 외에도 INSERT,UPDATE,MERGE,DELETE 문도 쓸수 있다.

 

PL/SQL 과 SQL의 데이터 타입 최대 크기

데이터 타입	PL/SQL 최대크기	SQL 최대크기
CHAR	32,767byte	2,000byte
NCHAR	32,767byte	2,000byte
RAW	32,767byte	2000byte
VARCHAR2	32,767byte	4000byte
NVARCHAR2	32,767byte	4000byte
LONG	32,767byte	2GB -1
LONG ROW	32,767byte	2GB
BLOB	128TB	4GB -1
CLOB	128TB	4GB -1
NCLOB	128TB	4GB -1