[Oracle] PL/SQL (1)

 

PL/SQL

• PL/SQL(Procedural language extension to SQL)
  • SQL 문장 안에서 변수를 만들고 조건처리, 반복처리(LOOP, WHILE, FOR)를 할 수 있는 오라클 자체 내장된 절차적인 언어입니다.
  • PROCEDURE, FUNCTION, TRIGGER 등이 PL/SQL로 포함됩니다.
  • PL/SQL의 장점
    • BLOCK 구조로 다수의 SQL문이 한번에 DB로 보낼 수 있으니, 처리속도가 향상됩니다.
    • BLOCK 구조로 되어서 모듈화가 편합니다. 큰 BLOCK에 작은 BLOCK을 여러개 생성해서 다양하게 처리합니다. 
    • 단순하거나 복잡한 변수를 선언해서 사용할 수 있기 때문에 데이터를 계속 조회해야 하는 일이 적습니다.
    • 예외처리가 가능하기 때문에 에러가 발생해도 다른 루틴을 생성해서 에러가 발생하지 않은 것처럼 처리할 수 있습니다.

PL/SQL 구조

• PL/SQL은 명령의 필요한 변수나 SQL문을 모아서 구성하는데 이렇게 모아둔 하나의 구성 단위를 블록(BLOCK).
• BLOCK은 선언부(DECLARE), 실행부(BEGIN), 예외처리부(EXCEPTION)로 나눕니다.
  • DECLARE(선언부) : 개발자의 선택에 따라 생략가능. 실행에 필요한 변수, 상수 등을 선언합니다.
  • BEGIN(실행부) : 필수적으로 포함되어야 하는 부분. 조건문, 반복문 등 실제 실행되는 코드, SQL를 작성합니다.
  • EXCEPTION(예외처리부) : 생략가능하고 실행부에서 소스가 실행되다가 예외가 발생했을 때, 이를 처리해줄 내용을 작성하면 됩니다.

DECLARE
    변수, 상수 선언;
  BEGIN
    실행될 소스 코드(SQL);
  EXCEPTION
    예외처리할 내용;
  END;

아래는 SQLPLUS에서 수행한 PL/SQL 결과입니다.

정상적인 출력을 하려면, SET SERVEROUTPUT ON; 코드를 작성해주면 좋습니다.

 

PL/SQL 작성 시 주의사항입니다.

• DECLARE, BEGIN, EXCEPTION 뒤에는 ; 를 쓰지 않습니다.
• 주석은 --(한줄주석) /*다중주석*/를 사용합니다.
  • 한줄주석 -- 내용
  • 다중주석 /* 내용 */
• PL/SQL 작성을 마치고 실행하기 위해서는 마지막에 / 를 사용합니다.

 

 

PL/SQL의 변수, 상수

• 변수는 일시적으로 데이터를 저장하기 위한 요소. 이름과 데이터타입을 DECLARE부분에 작성한다.
• DECLARE부분에 작성된 변수는 BEGIN부분에서 가져와서 사용한다.
• 변수의 선언
  • 이름 데이터타입:=값 또는 SELECT쿼리 또는 수식;

• 변수의 기본값 지정

이름 데이터타입 DEFAULT 기본값(값 또는 SELECT쿼리 또는 수식);

 

• 상수 : 한 번 지정된 값이 변하지 않는 변수를 말합니다.
• 상수의 선언

이름 CONSTANT 데이터타입:=값 
--또는
SELECT쿼리 또는 수식;

• 참조형 변수를 사용할 수도 있습니다.

--열 하나만 가져오기.
이름 테이블명.열이름%TYPE:=값;
--행 하나를 통째로가져다 쓸 수 있다.
이름 테이블명%ROWTYPE;

 

 

 

 

위의 예시에서는, DEPT라는 테이블%ROWTYPE을 선언해서, 테이블 내 행의 타입 정보를 통째로 가져올 수 있습니다.

타입에 더해서 내부 값은 SELECT 문을 통해, 부서번호가 10인 경우의 모든 정보를 하나의 행으로 가져와서 DEPT_ROW에 입력합니다.

그 결과 10, ERP, 서울, 1001이 출력되는 것을 확인할 수 있습니다.

 

조건문

PL/SQL 조건문은 IF, CASE문을 사용합니다.

• IF문 사용은 다음과 같습니다.
• 기본 IF 문 : 조건이 충족되면 실행문이 실행됩니다.

IF 조건 THEN 실행문

• 다중 IF 문 : 조건을 여러개 넣고, 공통적으로 IF-THEN을 사용하며 추가적인 조건은 ELSIF-THEN-ELSE을 사용합니다.
  • 조건을 검사하여 해당 조건이 충족하는 실행문을 실행하고 모든 조건에 충족하지 않을 때 ELSE의 실행문을 실행합니다.

IF 조건1 THEN 실행문1
   ELSIF 조건2 THEN 실행문2
   ELSIF 조건3 THEN 실행문3
   ....
   ELSE 실행문N

• 조건의 종료는 END IF;를 추가합니다.

 

다음은 점수가 67점인 학생에 대해 학점을 분류하는 조건문입니다.

 

 

• CASE 문 : IF문 외에도 CASE-WHEN을 사용한 조건문을 만들 수 있습니다.
  • JAVA의 switch-case를 생각해보면 비슷합니다.

CASE 비교대상 
    WHEN 값1 THEN 실행문1;
    WHEN 값2 THEN 실행문2;
    ...
    ELSE
        실행문N;
   END CASE;

• 약간의 응용을 하면, CASE WHEN 뒤에서 조건문만 만들 수 있습니다.

CASE
    WHEN 조건식1 THEN 실행문1;
    WHEN 조건식2 THEN 실행문2;
    ELSE
        실행문N;
   END CASE;

 

반복문

• PL/SQL에서는 기본 LOOP, WHILE LOOP, FOR LOOP, Cursor FOR LOOP를 반복문으로 제공합니다.
  • 기본 LOOP : 기본 반복문입니다.
  • WHILE LOOP : 특정 조건식의 결과를 통해 반복합니다.
  • FOR LOOP : 반복 횟수를 지정해서 횟수만큼 반복합니다.
  • Cursor FOR LOOP : 커서를 통해 반복할 수 있습니다.(신기합니다.)
• 반복문 제어 명령어는 EXIT, CONTINUE 있습니다.
  • EXIT : 현재 수행중인 반복문 중단(break;) 합니다.
  • EXIT WHEN 조건 : 조건이 충족하면 반복문을 종료합니다. WHEN 조건이 중요하겠죠? WHERE가 아님에 유의합시다.
  • CONTINUE : 현재 진행중인 실행을 건너뛰고 다음번 실행으로 넘어갑니다.
  • CONTINUE WHEN 조건: 조건이 충족하면 현재 실행을 건너뜁니다.

 

• 기본 LOOP : 기본 반복문은 LOOP로 시작하고, EXIT WHEN으로 종료 조건을 걸어야 합니다. JAVA의 while loop와 비슷한 점이 있네요!

• 기본 LOOP + IF문을 사용할 수 있습니다.

  LOOP
    반복 실행될 작업;
   END LOOP;

 

• WHILE LOOP : while 문이랑 거의 동일하고, WHILE 조건문 LOOP 실행문 순으로 수행하면 됩니다.

  WHILE 조건 LOOP --자바처럼 조건이 참일때 계속 실행.
        반복 실행될 작업;
      END LOOP;

• 마지막으로, FOR LOOP가 있습니다. FOR LOOP는 시작값, 종료값을 지정할 수 있습니다.

  FOR i IN 시작값..종료값 LOOP
        반복 실행 작업;
      END LOOP;
  
  --REVERSE 넣으면 거꾸로 수행합니다!
  FOR i IN REVERSE 시작값..종료값 LOOP
        반복 실행 작업;
      END LOOP;

 

이렇게 다양한 조건문, 반복문을 학습했습니다. 응용하기 좋아 보이는데, 반대로 공부할 량이 많아보입니다!