[PL/SQL] PL/SQL 기초

PL/SQL (Oracle's Procedural Language extension to SQL) = 오라클에서 SQL을 확장하여 사용하는 프로그래밍 언어. 이름과 같이 절차적 프로그래밍 언어이다. 

PL/SQL을 왜 사용할까?

1. 대용량 데이터를 연산해야 할 때, WAS등의 서버로 전송해서 처리하려면 네트워크에 부하가 많이 걸릴 수 있다. 이때 프로시져나 함수를 사용하여 데이터를 연산하고 가공한 후에, 최종 결과만 서버에 전송하면 부담을 많이 줄일 수 있다.

2. 로직을 수정하기 위해 서버를 셧다운 시키지 않아도 된다. 서버에서는 단순히 DB에 프로시저를 호출하여 사용하면 된다.

3. 쿼리문을 직접 노출하지 않는 만큼, SQL injection의 위험성이 줄어든다.

4. 블록 단위로 유연하게 사용할 수 있다. 

단점도 존재한다.

1. 유지보수가 힘들다.

2. 대용량 처리가 많을 경우, DB에 부하를 줄 수 있다.

3. Git 같은 형상관리를 사용할 수 없다.

PL/SQL의 기본 구조

---

블록 : PL/SQL의 기본 단위. 선언부, 실행부, 예외처리부로 구성

• 이름부 : 블록의 명칭이 옴, 생략시 익명블록 (명칭은 함수, 프로시저 사용가능)

• 선언부 : DECLARE로 시작, 실행부와 예외처리부에서 사용할 변수, 상수, 커서 선언한다. 문장 끝에 반드시 세미콜론(;) 을 찍을 것.

• 실행부 : 실제 로직 처리하는 부분

• 예외처리부 : 로직을 처리하다가 오류가 발생하면 처리할 내용을 기술하는 부분으로 생략이 가능하다.

DECLARE 
    vi_num INTEGER := 100;
BEGIN
    DBMS_OUTPUT.PUT_LINE(vi_num);
END;

자바를 해 본 사람이라면, 전반적인 메소드 작성과 비슷하게 이해 해 볼 수있다.

    public void printNumber() {
        
        int vi_num = 100;
        
        System.out.println(vi_num);
        
    }

IS와 END는 함수의 스코프이다. { 과 } 로 이해하면 되겠다. 그리고 IS는 변수를 선언한다. (여기는 익명블록을 사용하였으므로 생략)

우선 선언부에 사용할 지역변수를 선언하고 초기화한다. 그리고 실행부에서 작동하는 로직을 코드로 작성한 다음 필요한 경우 try catch로 예외처리를 한다. 

PL/SQL 블록의 종류에는 익명블록, 함수, 프로시저가 있다.

PL/SQL의 구성요소

---

변수명 := 초기값

으로 선언하며 SQL 데이터 타입을 모두 사용할 수 있고, 따로 PL/SQL 데이터 타입을 사용할 수도 있다.

상수

상수명 CONST 데이터타입 := 상수값

변하지 않는 값은 상수로 선언한다. 예약어는 CONST이다.

PL/SQL 데이터 타입

• BOOLEAN : TRUE/FALSE/NULL
• PLS_INTEGER : -2,147,483,648 ~ 2,147,483,647, NUMBER에 비해서 비교적 공간 적게 차지. 
• 아래는 하위 타입들로 PLS_INTEGER 범위 안에 속하면서 각자 특성을 가진다. 

1. NATURAL : 음수 제외

2. NATURALN : 음수 제외인데 NULL할당 불가. 반드시 선언시 초기화 필요

3. POSITIVE : 양수

4. POSITIVEN : 양수인데 NULL할당 불가, 반드시 선언시 초기화 필요

5. SIGNTYPE : -1, 0, 1

6. SIMPLE_INTEGER : NULL이 아닌 모든 값, 반드시 선언시 초기화 필요

• BINARY_INTEGER : PLS_INTEGER 타입과 동일

연산자

**(제곱) 

+,- (양/음수 식별)

*, / (곱셈, 나눗셈)

+, -, || (덧셈, 뺄셈, 문자열 연결)

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

N0T (논리)

AND

OR

(위로 갈수록 우선순위가 높아진다)

* PL/SQL에서 사용할 수 있는 SQL문은 DML문이며, DDL문은 사용할 수 없다. (아예 방법이 없는것은 아니지만 일반적인 경우는 아니다)

* SELECT 절 안에서 변수에 값을 넣을때는 반드시 INTO 절을 사용하여야 한다.

 

DECLARE 
   vs_emp_name VARCHAR2(80);
BEGIN
   SELECT emp_name
   INTO vs_emp_name
   FROM employees
   WHERE employee_id = 100;
 
 DBMS_OUTPUT.PUT_LINE('찾아온 이름은... ' || vs_emp_name);
 
END;

* 선언부에서 변수 타입에 %TYPE을 사용하면 테이블안의 컬럼과 같은 타입으로 설정이 가능하다.

DECLARE 
   vs_emp_name EMPLOYEES.EMP_NAME%TYPE;
BEGIN
   SELECT emp_name
   INTO vs_emp_name
   FROM employees
   WHERE employee_id = 100;
 
 DBMS_OUTPUT.PUT_LINE('찾아온 이름은... ' || vs_emp_name);
 
END;

 

PRAGMA

---

컴파일러가 실행되기 전에 처리하는 전처리기의 역할을 하며 컴파일러는 런타임 떄와는 다른 결과를 내도록 동작한다.

컴파일 할 때 뭔가를 먼저 처리하라고 컴파일러에게 지시하는 역할을 함.

블록 선언부에 명시한다.

PRAGMA AUTONOMOUS_TRANSACTION

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

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

사용자 정의 예외를 처리할 때 사용. 특정 예외번호를 명시하여 컴파일러에 이 예외를 사용하라는 것을 알리는 역할을 함.

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

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

PRAGMA SERIALLY_RESUABLE

패키지 메모리 관리를 쉽게 할 목적으로 사용됨. 패키지에 선언된 변수에 대해 한 번 호출된 후 메모리를 해제시킴. 이 옵션을 설정하면, 패키지 변수에 값을 할당하더라도 다음 번에 호출할 때는 할당한 값에 대한 메모리를 해제시켜 해당 변수는 초기화 값이나 NULL이 됨.

* 라벨(LABEL) : PL/SQL 프로그램 상 특정 부분에 이름을 부여할 수 있는 것을 라벨이라고 하고, <<라벨명>> 형태로 사용함.

라벨을 붙이면 가독성도 좋고 소스관리도 쉬우며 특정 조건에 따라 GOTO 문으로 이후 로직을 처리하지 않고 특정 라벨로 이동이 가능하며, 반복문에 라벨을 붙이면 EXIT문으로 반복문블록을 빠져 나오게 할 수도 있음.