[SQL][SQLD][SQLP] 절차형 SQL (PL/SQL)

절차형 SQL

절차형 프로그래밍 언어인 C언어처럼 SQL에도 절차 지향적인 프로그래밍이 가능하도록 벤더별로 프로그래밍 언어와 같은 절차형 SQL을 제공한다.

 

오라클에서는 PL(Procedural Language)/SQL을 제공하며 SQL Server에서는 T-SQL을 제공한다.

 

본 포스팅은 오라클의 PL/SQL을 아주 간단하게만 설명한다.

 

 

 

PL/SQL 구조

PL/SQL은 기본적으로 BLOCK 구조로 되어있다. BLOCK 안에는 기본적으로 DECLARE, BEGIN, EXCEPTION으로 구성되어 있으며 마지막에는 END로 BLOCK의 끝을 표현한다.

 

DECLARE : 선언부라고 하며 BEGINE ~ END에서 사용할 변수(상수)에 대하여 정의하는 부분이다. 정의할 게 없다면 생략 가능하다.

• 변수란 데이터의 임시 저장영역으로 반복해서 재사용하기 위해 사용한다.
• 변수를 사용할 수 있는 범위는 해당 블록 시작(BEGIN)에서 블록의 끝(END)에서만 사용가능하다.
• 변수의 명칭은 반드시 문자로 시작해야 하며, 문자나 숫자, 특수문자를 포함할 수 있다. 단 변수명은 30 bytes 이하로 명명해야 한다.
• 특정 값으로 미리 초기화 가능하다. 
• 데이터를 할당하려면 ':='을 사용한다.
• 상수로 사용하려면 변수명 다음에 CONSTANT 키워드를 사용하면 된다.
• 변수의 데이터 타입은 직접 지정할 수 있지만, 다른 테이블에서 참조해서 지정할 수 있다.
• 아래는 변수 선언의 예시이다.

  DECLARE
  	V_PI CONSTANT NUMBER := 3.14;			-- 상수로 선언하였으며 변경 불가능하다.
  	v_name VARCHAR2(100) := '홍길동';			-- 변수로 선언하였으며 디폴트 값으로 미리 '홍길동'을 할당
  	v_addr VARCHAR2(100); 				-- 변수로 선언하였으며 디폴트 값을 선언하지 않았다.
  	v_you VHARCHAR2(100) := '&your_name'; 		-- 해당 블록을 실행할때마다 사용자한테 입력받는다.​
  	v_deptId DEPT.DEPT_ID%TYPE			-- DEPT 테이블의 DEPT_ID 컬럼의 데이터타입을 참조한다.
  	v_temp	DEPT%ROWTYPE		-- DEPT 테이블의 선언된 컬럼의 모든 데이터타입 아무거나 들어갈 수 있다.

 

BEGIN : 실행 부라고 하며 처리하고자 하는 SQL문과 필요한 로직을 기술하는 부분이다.

 

EXCEPTION : 예외처리 부라고 하며 실행 도중에 에러가 발생할 경우에 에러를 처리하는 로직을 기술하는 부분이다. 생략 가능하다.

EXCEPTION
WHEN 예외명1 THEN
	예외처리문장;
WHEN 예외명2 OR 예외명3 THEN
	예외처리문장;
WHEN OTHERS THEN
	나머지_예외처리문장;

 

 

 

PL/SQL 특징

DECLARE, BEGIN, EXCEPTION, END와 같은 예약어는 마지막에 세미콜론(;)을 붙이지 않는다. 이외의 명령어는 세미콜론(;)을 붙인다.

 

BLOCK안에 새로운 BLOCK을 포함할 수 있다. 이를 Nested Block이라고 표현한다.

 

IF, LOOP 등의 조건을 사용하여 절차적인 프로그래밍이 가능하다.

 

여러 SQL 문장을 Block으로 묶고 한 번에 Block 전부를 서버로 보내기 때문에 통신량을 줄일 수 있다.

 

PL/SQL로 작성된 코드는 PL/SQL 엔진이 처리하고, 일반적인 SQL 문장은 SQL 실행기가 처리한다.

 

PL/SQL에서는 DML 문장은 직접적으로 쿼리를 작성하여 사용할 수 있다. 하지만 DDL, DCL, TCL은 직접적으로 지원하지 않는다. 동적 SQL(런타임에 문자열로 작성되는 SQL)을 사용하면 가능하긴 하다. 동적 SQL은 execute immediate를 이용한다.

BEGIN
	execute immediate 'TRUNCATE TABLE 사원';
END

 

PL/SQL을 저장해서 쉽게 사용할 수 있다. 저장하여 사용할 경우 Stored PL/SQL Block이라고 한다. 저장하지 않고 사용할 경우에는 Anonymous PL/SQL Block이라고 한다.

 

 

PL/SQL SELECT

• PL/SQL의 SELECT는 SELECT 한 데이터를 INTO 뒤에 변수에 값을 저장한다.
• SELECT 컬럼 항목의 수와 INTO 변수 항목의 수는 동일해야 한다.
• 반드시 로우의 결과는 1건만 나타나야 한다.
• 아래 예시는 사원 ID가 '1'인 사원의 사원명이 v_name에 저장된다.

  DECLARE
  	v_name VARCHAR2(10);
  
  BEGIN
  	SELECT 사원명 INTO v_name FROM 사원 WHERE 사원ID = '1'; 
  
  END​

 

 

PL/SQL DML (INSERT, UPDATE, DELETE)

• 기본적인 DML문을 BEGIN 문장에 사용하면 된다.
• 사용자로부터 입력을 받고자 한다면 선언부에 &을 사용하여 입력받는다.
• 아래 예시는 사원 테이블에 INSERT를 하는 예시이다.

  DECLARE
  	v_id	VARCHAR2(4) := '&id';
  	v_name	VARCHAR2(10) := '&name';
      
  BEGIN
  	INSERT INTO 사원 VALUES(v_id, v_name);
  
  END

 

 

PL/SQL DML 제어문

IF 조건문

IF (조건) THEN
	실행문장;
ELSE
	실행문장;
END IF;


IF (조건) THEN
	실행문장;
ELSEIF (조건) THEN
	실행문장;
END IF;​

 

CASE 조건문

CASE 변수
	WHEN 조건1 THEN 결과1
	WHEN 조건1 THEN 결과1
	...
	WHEN 조건N THEN 결과N
ELSE 디폴트결과
END;

 

LOOP 반복문

LOOP
	문장;
    EXIT 조건;
ENd LOOP;

 

WHILE 반복문

WHILE 조건 LOOP
	문장;
END LOOP;

 

FOR 반복문

FOR 변수 IN 시작숫자..끝숫자 LOOP
	문장;
END LOOP;

 

 

Stored PL/SQL Block

PL/SQL을 마치 함수(프로그램)처럼 정의해서 사용할 수 있다. 이를 Stored PL/SQL Block이라고 한다. 저장하지 않고 이름 없이 사용하는 것은 Anonymous PL/SQL Block이라고 한다. 아래는 두 개의 차이점을 정리한 내용이다.

 

Stored PL/SQL Block	Anonymous PL/SQL Block
한 번 컴파일되어 데이터베이스에 저장됨	사용시마다 컴파일되고, 데이터베이스에 저장하지 않음
파라미터와 리턴값이 준재	파라미터와 리턴값이 존재하지 않음
다른 프로그램에서 호출 가능	다른 프로그램에서 호출 불가능

 

Stored PL/SQL Block의 종류는 Procedure, Function, Package, Trigger가 있다.

 

 

Procedure(프로시저)

• 가장 기본적인 유형으로 한 번 컴파일된 후 데이터베이스에 저장되어 사용되고, 다른 프로그램에서 호출 가능하다.
• 아래는 데이터베이스에 Procedure를 생성하는 방법이다.

  CREATE PROCEDURE p_test -- 프로시저 명칭을 입력한다.
  (
  	파라미터1 IN 데이터타입,
  	리턴값1 OUT 데이터타입,
  	둘다가능 IN OUT 데이터타입
  )
  IS
  	변수1 데이터타입;
  
  PL/SQL Block​

• 파라미터1은 호출하는 곳에서 전달해줘야 하는 파라미터를 의미하므로 IN을 사용한다. 생략 가능하다.
• 리턴값1은 호출하는 곳에서 리턴 값을 받을 리턴값을 의미하므로 OUT을 사용한다.
• 둘 다 가능은 파라미터와 리턴 값이 된다는 의미로 IN OUT을 사용한다.
• Procedure을 호출하는 방법은 아래와 같다.
  

  EXEC p_test() -- 인자가 없는 Procedure일 경우
  
  EXEC p_test(파라미터1, 리턴값1, 둘다가능) -- 인자가 있는 Procedure일 경우

 

Function(함수)

• Function은 반드시 리턴 값이 존재하는 Block이다.
• 아래는 데이터베이스에 Function을 생성하는 방법이다.

  CREATE FUNCTION f_test -- 함수 명칭을 입력한다.
  (
  	파라미터1 IN 데이터타입,
  	리턴값1 OUT 데이터타입,
  	둘다가능 IN OUT 데이터타입
  )
  RETURN 데이터타입
  IS
  	변수1 데이터타입;
  
  PL/SQL Block​

• 위에서 설명한 Procedure와 거의 동일하다. 차이점은 반드시 리턴 값이 존재하므로 RETURN을 사용한다. 그리고 리턴 값은 Blobk문에서 RETURN을 사용하여 리턴한다.
• Function은 일반적으로 알고 있는 오라클 함수처럼 사용할 수 있다.
• Procedure와 Function은 자율적으로 트랜잭션을 처리할 수 있다.

 

Package(패키지)

• Package는 연광성이 있는 변수, 상수, PL/SQL Block 등을 논리적으로 하나의 그룹으로 만드는 특수한 형태이다. Package를 만들고 그 안에 여러 가지 항목을 작성하여 사용하면 된다.
• Package는 인터페이스와 같이 사용방법을 기술한 선언부(스펙)와 실제 구현이 담겨 있는 바디부로 나뉜다.
• 아래는 데이터베이스에 Package를 생성하는 방법이다.
  

  -- 선언부
  CREATE PACKAGE pack_test
  IS
    상수명 CONSTANT 데이터타입;
    예외명 EXCEPTION;
    변수명 데이터타입;
  
    PROCEDURE 프로시저명칭 (변수타입들..);
    FUNCTION 함수명칭 (변수타입들..) RETURN 반환타입;
  END pack_test;
  
  
  -- 바디부
  CREATE PACKAGE BODY pack_test
  IS
    상수명 CONSTANT 데이터타입;
    예외명 EXCEPTION;
    변수명 데이터타입;
  
    PROCEDURE 프로시저명칭 -- 프로시저 생성구문
        ...
    END 프로시저명칭;
  
    FUNCTION 함수명칭 -- 함수 생성구문
        ...
    END 함수명칭;
  END pack_test;

   
• 호출하는 방법은 패키지명. 명칭처럼 사용하면 된다. ex) pack_test.getList;

 

Trigger(트리거)

• Trigger는 특정 이벤트가 발생할 때 자동으로 수행되는 Block을 의미한다. 따라서 트리거는 직접적으로 실행할 수는 없다.
• 자동으로 수행하게 하여 데이터의 무결성과 일관성을 유지하도록 한다.
• 트리거는 TCL을 이용하여 트랜잭션을 제어할 수 없다.
• 특정 이벤트가 발생하기 전(BEFORE EVENT), 특정 이벤트가 발생 후(AFTER EVENT) 등을 설정할 수 있다.
• 이벤트에는 INSERT, UPDATE, DELETE, DDL, DROP, TRUNCATE 등을 사용할 수 있다.
• DELETE ON TRIGGER 의 경우,  :OLD 는 삭제 전 데이터를 , :NEW 는 삭제 후 데이터를 나타낸다.
• UPDATE TRIGGER 에서, :OLD 에는 수정 전, :NEW 에는 수정 후 값이 들어간다.
• 아래는 Trigger를 생성하는 방법이다.

  CREATE [OR REPLACE] TRIGGER trigger_name
  BEFORE | AFTER
       trigger_event ON table_name
       [FOR EACH ROW]
       [WHEN (condition)]
       PL/SQL block
       
       
  CREATE TRIGGER 인서트제약트리거
  BEFORE UPDATE ON 사원연봉
  BEGIN
  	IF (:NEW.입력자) NOT IN ('대표') THEN
  		RAISE_APPLICATION_ERROR(-9999,'대표만 수정할 수 있습니다.');
  	END IF;
  END

 

CURSOR(커서)

커서를 사용하면 처리된 SQL문장의 결과 집합에 접근할 수 있다. 

 

묵시적 커서는 바로 이전에 실행된 SQL문에 대하여 커서를 가지게 되어 특별한 키워드를 이용해 결과 집합에 대한 정보를 얻을 수 있다.

SQL%FOUND
SQL%NOTFOUND
SQL%ROWCOUNT

 

명시적 커서는 직접 작성한 SQL 문장을 커서로 만들어 사용하는 방법이다. 순서는 다음과 같다.

1. 커서 선언 : 사용할 커서에 이름을 부여하고 SQL문을 작성한다.

   CURSOR 커서명[(매개변수들)]
   IS
   SQL문장;

2. 커서 열기 : 선언한 커서를 OPEN한다. 매개변수가 존재할 경우 매개변수를 함께 전달한다.

   OPEN 커서명[(매개변수들)];​

3. 커서 패치 : 해당 SQL문을 실행하여 원하는 작업을 한다.

   -- 일반적으로 패치한 데이터를 모두 접근하기 위해 루프를 사용한다.
   LOOP
   FETCH 커서명 INTO 변수들; -- 수행한 SQL문에 데이터를 변수에 저장한다.
   EXIT WHEN 커서명%NOTFOUND; -- 더 이상 데이터가 없을 경우 루프를 종료한다.
   END LOOP;​

4. 커서 닫기 : 커서 사용을 완료하면 닫아준다.

   CLOSE 커서명;​