Oracle PL/SQL
PL/SQL은 Procedural Language/SQL의 약자로 오라클 DBMS에 내장되어 있는 절차적 언어이다.
일반적인 프로그래밍에서 사용되는 기능(변수 선언, 제어문, 예외처리 등)을 사용하여 단순 SQL문으로 처리할 수 없는 복잡한 문제를 보다 쉽게 해결할 수 있다.
✅ PL/SQL 기본구조
Oracle PL/SQL은 크게 익명 블록과 실명? 블록이 있다.
익명 블록은 일회성 코드 블록이며, 별도의 스키마로 저장되지 않는다. 하지만 실명 블록은 특정 이름 및 형태의 스키마로 정의 및 저장된다.
▶︎ Anonymous Procedure(익명 프로시저)
익명 프로시저의 경우 일회성 코드 블록이며, 사용자의 별도 저장 관리가 없으면 사라진다.
(DECLARE로 시작하는 코드 블록)
DECLARE -- 변수 선언 BEGIN -- 실행 구문(Main Logic) EXCEPTION -- 예외 처리(필수X) WHEN OTHERS THEN END;
▶︎ Stroed Procedure(저장 프로시저)
익명 프로시저에 이름을 부여한 프로시저이다. 컴파일 이후 데이터베이스에 저장되며, 사라지지 않는다.
CREATE OR REPLACE NONEDITIONABLE PROCEDURE <프로시저명> ( P_PARAM_01 IN VARCHAR2 --파라미터01 , P_PARAM_02 IN VARCHAR2 --파라미터02 , R_RTN_01 OUT VARCHAR2 --반환값01 , R_RTN_02 OUT VARCHAR2 --반환값02 ) AS -- 변수 선언 BEGIN -- 실행 구문(Main Logic) -- RETURN값 설정 R_RTN_01 := '0'; R_RTN_02 := 'OK'; EXCEPTION -- 예외 처리(필수X) WHEN OTHERS THEN END <프로시저명> ;
▶︎ Stored Function (함수)
일반적인 프로그래밍에서 사용되는 함수와 비슷하며, 간단한 계산식/데이터 변환식 등에서 많이 사용된다.
CREATE OR REPLACE NONEDITIONABLE FUNCTION <함수명> ( P_PARAM_01 IN VARCHAR2 --파라미터 정의 ) RETURN NUMBER AS --반환 타입 정의 -- 변수 선언 BEGIN -- 실행 구문(Main Logic) EXCEPTION -- 예외 처리(필수X) WHEN OTHERS THEN END <함수명> ;
▶︎ Package(패키지)
논리적으로 관련된 또는 공통된 PL/SQL 프로그램 객체를 그룹화하여 관리하는 스키마 객체이다.
EX) 주문 관련 패키지 안에는 주문 관련 함수, 변수, 프로시저, 커서 등을 그룹화 하여 관리/사용
패키지는 크게 패키지 사양(Specification)과 패키지 본문(Body)으로 나뉜다.
외부에서의 접근은 패키지 사양(Specification)에서 선언된 객체만 접근 가능하다.
⏩️ 패키지 사양(Specification)
패키지의 공용 인터페이스를 정의하는 부분으로 함수, 변수, 프로시저, 커서 등을 선언이 작성된다.
CREATE OR REPLACE PACKAGE <패키지명> AS /* TODO enter package declarations (types, exceptions, methods etc) here */ -- 함수 선언 FUNCTION <함수명> ( P_PARAM_01 IN VARCHAR2 --파라미터 ) RETURN NUMBER; --반환 타입 -- 프로시저 선언 PROCEDURE <프로시저명> ( P_PARAM_01 IN VARCHAR2 --파라미터01 , P_PARAM_02 IN VARCHAR2 --파라미터02 , R_RTN_01 OUT VARCHAR2 --반환값01 , R_RTN_02 OUT VARCHAR2 --반환값02 ); END <프로시저명>;
⏩️ 패키지 본문(Body)
패키지 사양에서 선언한 함수, 변수, 프로시저, 커서 등의 실제 구현 코드를 작성하는 부분이다.
CREATE OR REPLACE PACKAGE BODY <패키지명> AS /* TODO enter package declarations (types, exceptions, methods etc) here */ -- 함수 선언 FUNCTION <함수명> ( P_PARAM_01 IN VARCHAR2 --파라미터 ) RETURN NUMBER AS --반환 타입 -- 변수 선언 BEGIN -- 실행 구문(Main Logic) EXCEPTION -- 예외 처리(필수X) WHEN OTHERS THEN END <함수명>; -- 프로시저 선언 PROCEDURE <프로시저명> ( P_PARAM_01 IN VARCHAR2 --파라미터01 , P_PARAM_02 IN VARCHAR2 --파라미터02 , R_RTN_01 OUT VARCHAR2 --반환값01 , R_RTN_02 OUT VARCHAR2 --반환값02 ) AS -- 변수 선언 BEGIN -- 실행 구문(Main Logic) -- RETURN값 설정 R_RTN_01 := '0'; R_RTN_02 := 'OK'; EXCEPTION -- 예외 처리(필수X) WHEN OTHERS THEN END <프로시저명>;
▶︎ Trigger(트리거)
트리거는 특정 이벤트가 발생할 때 자동으로 실행되는 일종의 저장 프로시저이다.
주로 데이터베이스의 무결성을 유지하거나 특정 작업을 자동화하기 위해 사용되며, 데이터베이스 테이블의 특정 변화(삽입, 수정, 삭제)에 따라 동작된다.
또한, DDL 언어를 사용하는 데 있어 삭제(DROP, TRUNCATE) 실행을 막을 수 있다.
(사용자 에러를 발생시킴으로서 DB의 다양한 객체의 삭제를 막을 수 있다.)
✅ 선언부
DECLARE(CREATE OR REPLACE NONEDITIONABLE FUNCTION/PROCEDURE 다음)문으로 시작되며, 실행부에서 사용할 변수, 상수, 커서, 사용자 정의 타입 등을 선언하는 부분이다.
▶︎ 변수 선언
PL/SQL에서 변수 선언은 크게 3가지 방법이 있다.
- 직접 타입을 지정하여 변수 선언
%TYPE을 사용하여 특정 테이블에 있는 컬럼 속성으로 변수 선언%ROWTYPE을 사용하여 특정 테이블의 행(ROW/RECORD) 전체 속성을 가져와 변수 선언
DECLARE v_name1 VARCHAR2(100); v_name2 table_nm.name%type; r_student table_nm%rowtype; BEGIN -- 실행 구문(Main Logic) END;
▶︎ 레코드 선언
PL/SQL에서 레코드는 구조체를 정의하는 데이터 타입으로 일반적인 프로그래밍에서 사용하는 구조체와 비슷하다.
특정 테이블의 속성을 추출하여 레코드로 그룹화 하거나, 두 개 이상의 테이블의 여러 속성을 추출하여 하나의 레코드로 그룹화 하여 사용할 수 있다.
이러한 레코드는 데이터베이스 테이블의 행을 표현하거나, 여러 관련 데이터를 그룹화할 때 주로 사용한다.
TYPE <레코드명> IS RECORD( <변수명_1> <테이블명>.<속성명_1>%TYPE, <번수명_2> <테이블명>.<속성명_2>%TYPE, <번수명_3> VARCHAR2(100) );
✅ 실행부
실행부는 BEGIN문으로 시작하여 END문으로 종료되는 부분을 의미하며, 주요 작업(Main Logic)이 실행되는 부분이다.
실행부에는 제어문, 반복문, SQL문 등이 기술된다.
▶︎ 제어문(IF)
IF 조건1 THEN -- 조건1이 맞을 때 ELSIF 조건2 THEN -- 조건2이 맞을 때 ELSE -- 모두 맞지 않을 때 END IF;
▶︎ 제어문(CASE)
CASE WHEN 조건1 THEN -- 조건1이 맞을 때 WHEN 조건2 THEN -- 조건2이 맞을 때 ELSE -- 모두 맞지 않을 때 END CASE;
▶︎ 반복문(LOOP)
LOOP문에서 적절하지 못한 조건문 또는 EXIT 키워드를 누락하게 되면, overflow가 발생되어 시스템이 다운될 수 있다.
LOOP --반복 시작 EXIT; --LOOP문 탈출 END LOOP; LOOP --반복 시작 EXIT WHEN 조건; --조건이 true이면 LOOP문 탈출 END LOOP;
▶︎ 반복문(FOR)
LOOP문 위에 반복 횟수를 지정하여 반복을 수행하는 반복문이다.
FOR i IN 1..n --반복 조건 LOOP -- n번 반복 수행 END LOOP;
▶︎ 반복문(WHILE LOOP)
WHILE 조건 LOOP -- 조건이 true이면 수행 END LOOP;
▶︎ 반복문(CURSOR FOR)
FOR 커서명 IN (SELECT * FROM 조회하고 싶은 테이블) LOOP -- 조회하고 싶은 테이블의 ROW 수만큼 반복 -- '커서명.조회하고 싶은 테이블 컬럼' -- '.' 문자를 통해 특정 테이블의 컬럼 접근 가능 END LOOP;
