[Oracle] PL/SQL문법 13. PL/SQL 기본문법

PL/SQL (Procedural Language extension to SQL)

오라클 자체에 내장되어있으며 SQL을 확장한 절차적 언어이다
오라클의 표준 데이터 엑세스 언어로, 프로시저 생성자를 SQL과 완벽하게 통합한다
SQL문장 내에서 변수의 정의, 조건처리(IF), 반복문 처리(LOOP, FOR, WHILE), 예외처리 등을 지원하며 SQL문의 단점(앞에 해당하는 것들이 없음)을 보완한다
다수의 SQL문을 한번에 실행 가능하다

PL/SQL 특징

[ 특징 ]

• 프로그래밍 언어와 데이터 처리의 통합

  • PL/SQL의 본질적인 목적은 데이터 처리를 보다 효율적으로 하는것에 있다
  • 따라서 프로그래밍 언어처럼 구성하지만 SELECT, DELETE, INSERT, UPDATE, MERGE와 같은 일반 SQL문을 별도의 작업 없이 사용할 수 있다

• 블록 단위 구성

  • 선언부, 실행부, 예외처리부로 나뉜 블록 구조로 이루어져 있다
  • DECLARE - BEGIN - EXCEPTION - END

• 순차 처리 지원

  • 순차 처리를 지원하는 언어로 제어문, 반복문의 사용이 가능하다
  • IF, LOOP, FOR WHILE 등

• 예외처리 지원

  • 실행중 정상적인 흐름에 위반되는 상황이 발생하는 것에 대해 예외처리를 진행할 수 있다

• 패키지를 사용한 모듈화를 지원

  • 연관성을 가지는 타입, 상수, 변수, 커서, 예외처리, 서브프로그램들을 엮어서 하나의 응용 단위인 패키지로 구성할 수 있다

• 트리거

  • 데이터베이스에서 발생하는 이벤트를 처리하는 기능을 제공한다
  • 이벤트 발생시에 트리거는 자동으로 실행되어 작성된 PL/SQL 로직을 호출하게 된다

• 객체 지향 프로그래밍 지원

  • JAVA, C++, C#과 같이 객체 타입을 제공한다

• 다양한 내장 API

  • 오라클 DBMS 는 수많은 API를 제공하고 있다
  • 보통 패키지나 함수, 프로시저 등으로 제공되며 PL/SQL에서도 사용 가능한 다양한 내장 API를 지원한다

• 플랫폼 독립적

  • 어떤 운영체제인지와는 별개로 같은 오라클 버전이라면 PL/SQL 구동이 가능하다

• SQL 구문 및 함수의 사용 가능하다

  • 사용 가능 : Query/DML/TML구문 , 단일행 숫자, 단일행 문자, 데이터형 변환(TO_DATE, TO_CHAR…), 날짜
  • 사용 불가 : DDL/DCL(제한적으로 가능) 구문, DECODE, GREATEST, LEAST, 그룹 함수들

[ 장점 ]

• 대용량 데이터를 연산해야 할 때 프로시져나 함수를 사용하여 데이터 가공 후 최종결과만 서버에 전송하게 되면 네트워크나 서버의 부담을 많이 줄일 수 있다(= 네트워크 트래픽 최소화 가능)

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

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

• SQL과 긴밀히 통합되어 있기 때문에 JAVA의 JDBC, C의 PRO*C를 사용하는 것보다 훨씬 쉽고 단순하다

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

• 이식성이 좋다, 따라서 데이터베이스가 설치된 플랫폼/운영체제에 무관하게 이식 하능하며 이식에 따른 코드 수정또한 불필요하다

[ 단점 ]

• SQL처럼 옵티마이저의 데이터 처리 절차 최적화를 받지 못한다
  => 단순 SQL보다 처리속도가 느릴 수 있다

• 유지보수가 힘들다

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

• Git과 같은 형상관리를 사용할 수 없다

• 최근에는 버전관리 및 유지보수가 힘든 점과 ORACLE 외에 MSSQL 등 다른 DBMS를 사용하는 경우도 잦아져 점점 PL/SQL 사용이 지양되는 추세이다

PL/SQL 기본 구조

구조	설명
DECLARE SECTION (선언부)	DECLARE로 시작 변수나 상수를 선언 및 초기화 하는 부분
EXECUTABLE SECTION (실행부)	BEGIN으로 시작 SQL문, 제어문, 반복문, 함수 정의 등 로직을 기술하는 부분
EXCEPTION SECTION (예외처리부)	EXCEPTION으로 시작 예외발생시 해결하기 위한 구문을 미리 기술하는 부분
END	END;로 끝맺음 실행문을 종료하는 부분으로 이 뒤에 /까지 붙여주어야한다

• 선언부와 예외처리부는 생략이 가능하다

[ DECLARE 선언부 ]

[표현법]
DECLARE
  --변수/상수 선언 및 초기화(생략가능)
  변수명 [CONSTANT] 자료형 [NOT NULL] [:=값];
BEGIN
  실행부
END;
/

-- 예시
SET SERVEROUTPUT ON;
/*(프로시저를 사용하여 출력하는 내용을 화면에 보여주도록
    설정하는 환경변수로 기본값은 OFF여서 ON으로 변경해주어야 한다)*/

DECLARE
  EID NUMBER;
  ENAME VARCHAR2(20);
  PI CONSTANT NUMBER := 3.14;
BEGIN
  EID := &번호;
  ENAME := '&이름';
/* 사용자가 입력한 변수에서 바로 문자형으로 자동형변환이 되지 않기 때문에
  위와같이 ''로 감싸서 강제 형변환을 해준 것이다
  아예 사용자가 값을 입력할때 입력값에 ''를 포함해서 넣어주면 &이름 으로만 써도 무방하다 */
  DBMS_OUTPUT.PUT_LINE('EID : ' || EID);
  DBMS_OUTPUT.PUT_LINE('ENAME : ' || ENAME);
  DBMS_OUTPUT.PUT_LINE('PI : ' || PI);
END;
/

• DECLATE 선언부

  • 변수나 상수를 선언하는 공간이다
  • 변수/상수를 선언하는 동시에 초기화도 가능하다
  • 변수에는 일반타입, 레퍼런스, ROW타입 등이 있다
  • CONSTANT를 붙이면 상수가 되며 초기값을 반드시 할당해야한다 (실행부에서 다른값을 할당하게 되면 오류가 발생한다)
  • NOT NULL을 붙이면 해당 변수가 값을 반드시 포함하도록 한다
  • 값 초기화가 되어있지 않다면 그냥 빈값으로 출력된다(NULL값이 아닌 말그대로의 빈값)

• := : 오라클식 대입 연산자이다 (=과 헷갈리지 말것, PL/SQL에서 = 는 대입이 아닌 동등 연산을 의미한다)

• &이름
  => & 기호를 붙이면 사용자가 직접 값을 입력할 수 있는 입력창을 띄운다
  => 이름 위치에 넣은 문자열이 입력창의 설명으로서 같이 뜨게 된다
  => SELECT문 안에서 사용할 시엔 자동 형변환이 되어서 ‘‘로 감싸줄 필요가 없지만 그 외에 문자열로서 값을 받아야 할 때엔 ‘‘를 사용하여 강제 형변환을 해주어야 한다
  => NUMBER 타입은 따로 형변환해줄 필요가 없다

• || : 연결 연산자로 문자열들을 더해서 하나의 문자열로 만들어준다

• / : 각각의 PL문은 /로 구분하기 때문에 END;로 마침표를 찍었더라도 꼭 /를 기입하여 구분해주어야 한다

• SERVEROUTPUT 옵션 : DBMS_OUTPUT.PUT_LINE(); 으로 출력하는 내용을 화면에 보여주도록 하려면 해당 옵션을 켜주어야 한다

• 만약 0.3 등, 0으로 시작하는 숫자타입을 출력하게 되면 앞의 0이 사라지고 그냥 .3으로만 출력되게 된다
  => 오라클에서는 정수값의 앞자리가 0이면 해당 0을 생략해버리니 주의

레퍼런스 변수

DECLARE
  [표현법] 변수명 테이블명.컬럼명%TYPE;

  EID A_TABLE.ID_COLUMN%TYPE;
  ENAME A_TABLE.NAME_COLUMN%TYPE;

BEGIN
  SELECT ID_COLUMN, NAME_COLUMN
  INTO EID, ENAME
  FROM A_TABLE
  WHERE ID_COLUMN = &아이디;

  DBMS_OUTPUT.PUT_LINE('EID : ' || EID || ', ENAME : ' || ENAME);
END;
/

• 원하는 테이블 안, 특정 컬럼의 데이터타입을 참조해서 해당 타입으로 타입을 지정하게된다
• SELECT 구문으로 테이블의 컬럼들을 불러와서 INTO에 기입한 변수명에 해당 컬럼의 조회된 값을 차례대로 넣어주게 된다
• 일반적인 변수에는 하나의 값만을 넣을 수 있으므로 WHERE절 등을 사용해서 조회되는 컬럼의 값을 하나로 만들어주어야 에러가 나지 않는다
• SELECT 구문 안에서는 자동 형변환이 일어나기 때문에 &를 사용했더라도 따로 ‘’ 처리를 해줄 필요가 없다

ROWTYPE 변수

DECLARE
  [표현법] 변수명 테이블명%ROWTYPE;

  T A_TABLE%ROWTYPE;

BEGIN
  SELECT * INTO T
  FROM A_TABLE
  WHERE ID_COLUMN = &아이디;

  -- [사용법] 변수명.컬럼명
  DBMS_OUTPUT.PUT_LINE(`ID_COLUMN : ` || T.ID_COLUMN);
  DBMS_OUTPUT.PUT_LINE(`NAME_COLUMN : ` || T.NAME_COLUMN);
  DBMS_OUTPUT.PUT_LINE(`ELSE_COLUMN : ` || T.ELSE_COLUMN);
END;
/

• 테이블의 한 행에 대한 모든 컬럼 값을 한꺼번에 담을 수 있는 변수이다
• 변수명.컬럼명 식으로 원하는 컬럼의 값을 꺼내서 사용 가능하다

[ BEGIN 실행부 ]

조건문 IF ~ THEN ~ END IF

DECLARE
  AID A_TABLE.ID_COLUMN%TYPE;
  ABONUS A_TABLE.BONUS_COLUMN%TYPE;

BEGIN
  SELECT ID_COLUMN, BONUS_COLUMN
    INTO AID, ABONUS
  FROM A_TABLE
  WHERE ID_COLUMN = &아이디;

  IF ABONUS >= 0.5
    THEN DBMS_OUTPUT.PUT_LINE('보너스 50% 이상');
  ELSIF BONUS >= 0.3
    THEN DBMS_OUTPUT.PUT_LINE('보너스 30% 이상');
  ELSIF ABONUS >= 0.1
    THEN DBMS_OUTPUT.PUT_LINE('보너스 10% 이상');
  ELSE DBMS_OUTPUT.PUT_LINE('보너스가 10% 미만입니다');
  END IF;

END;
/

• 표현법 : IF 조건식 THEN 실행내용; [ELSIF 조건식 THEN 실행내용;] [ELSE 실행내용;] END IF;
• ELSIF는 생략 혹은 얼마든지 추가 가능하며 JAVA의 if문에서 else if 와 같은 역할을 한다
  => 추가적인 조건을 제시하고 해당 조건을 만족했을 때의 실행내용을 기술한다
  => 위에서 아래로 차례대로 조건식을 검증하게 된다
• ELSE는 생략 혹은 단 한번만 기술 가능하며 JAVA의 if문에서 else와 같은 역할을 한다
  => 위에서 선언된 조건을 충족하지 못한 모든 값들에 대한 실행 내용을 기술한다
• 마지막엔 반드시 END IF; 로 IF문을 종료시켜주어야 한다

조건문 CASE ~ WHEN ~ THEN

DECLARE
  AID A_TABLE.ID_COLUMN%TYPE;
  ACODE A_TABLE.CODE_COLUMN%TYPE;
  STR VARCHAR(10);

BEGIN
  SELECT ID_COLUMN, CODE_COLUMN
    INTO AID, ACODE
  FROM A_TABLE
  WHERE ID_COLUMN = &아이디;

  STR := CASE ACODE
            WHEN 'A1' THEN '인사팀'
            WHEN 'A2' THEN '영업팀'
            WHEN 'A3' THEN '마케팅팀'
            WHEN 'A4' THEN '회계팀'
            ELSE '관리팀'
          END;

  DBMS_OUTPUT.PUT_LINE(AID || ' 은(는) ' || STR || '입니다');
END;
/

• 표현법 : CASE 비교대상자 WHEN 동등비교값1 THEN 결과값1 WHEN 동등비교값2 THEN 결과값2... [ELSE 결과값] END;
• WHEN 동등비교값 THEN 결과값 : 원하는 만큼 추가 가능하며 비교대상자로 넣은 값과의 동등비교를 수행하여 TRUE일 경우 결과값을 반환하게 된다
• ELSE 결과값 : 위에서 선언된 값들과 맞지 않는 그 모든 케이스들에 대한 결과값을 주고싶을 경우 기술한다
• 마지막엔 반드시 END;로 CASE WHEN THEN문을 종료시켜주어야 한다

반복문 BASIC LOOP

DECLARE
  I NUMBER := I;
BEGIN
  LOOP
    DBMS_OUTPU.PUT_LINE(I);
    I := I+1;
    EXIT WHEN I=6;
    -- IF I =6 THEN EXIT; 로 작성해도 동일하게 작동한다
  END LOOP;
END;
/

• 표현식 : LOOP 반복적으로 실행할 구문; 반복문을 빠져나갈수 있는 구문 END LOOP;
• 반복문을 빠져나갈 수 있는 구문
  • IF 조건식 THEN EXIT END IF;
  • EXIT WHEN 조건식;
• LOOP를 빠져나간 후엔 END LOOP로 꼭 종료시켜주어야 한다

반복문 FOR LOOP

BEGIN
  FOR I IN 1..5
  LOOP
    DBMS_OUTPUT.PUT_LINE(I);
    -- I := I+1; 과 같은 구문은 오류가 발생한다
    -- DECLARE에서 정의내린 변수가 아니기때문에 직접적으로 값을 변경시키거나 할 수 없기때문
  END LOOP;

END;
/

• 표현법 : FOR 변수 IN [REVERSE] 초기값..최종값 LOOP 반복수행할 구문; END LOOP;
• 변수에 초기값에서부터 1씩 증가시켜서 최종값까지 차례대로 담기게 된다
• REVERSE를 사용할 경우 변수에 할당되는 값이 최종값부터 시작되어 초기값으로 끝난다

반복문 WHILE LOOP

DECLARE
  I NUMBER := 1;

BEGIN
  WHILE I < 6
  LOOP
    DBMS_OUTPUT.PUT_LINE(I);
    I := I + 1;
  END LOOP;

END;
/

• 표현식 : WHILE 조건식 LOOP 반복수행할 구문; END LOOP;
• 조건식이 TRUE일 동안 계속해서 반복하게 된다

[ EXCEPTION 예외처리부 ]

DECLARE
  RESULT NUMBER;

BEGIN
  RESULT := 10 / &숫자;
  -- 별도의 예외처리 없이 0으로 나눌 경우 "divisor is equal to zero" 에러발생하게 됨
  DBMS_OUTPUT.PUT_LINE('결과 : ' || RESULT);

EXCEPTION
  -- 각각의 에러 코드를 다 외우기 힘들기 때문에 OTHERS를 많이 사용하게 될 것임
  WHEN ZERO_DIVIDE THEN DBMS_OUTPUT.PUT_LINE('0으로 나눌수 없습니다');
  WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('나누기 연산시 에러가 발생하였습니다');

END;
/

• 표현식 : EXCEPTION WHEN 예외1 THEN 예외처리구문1; WHEN 예외2 THEN 예외처리구문2;... [WHEN OTHERS THEN 예외처리구문;]

• 시스템 예외 예시

  • 오라클에서 미리 정의해둔 예외들의 예시이다
  • NO_DATA_FOUND : SELECT한 결과가 한 행도 없는 경우
  • TOO_MANY_ROWS : SELECT한 결과가 여러행인 경우
  • ZERO_DICIDE : 나누기 연산시 0으로 나누는 경우
  • DUP_VAL_ON_INDEX : UNIQUE 제약조건에 위배되었을 때
• OTHERS : 위에서 WHEN으로 제시한 예외들 외에 다른 예외들 전부를 처리할 수 있게끔 한다