[ORACLE] PL/SQL

PL/SQL정의

• 오라클에서 제공하는 프로그래밍 언어
• SQL과 PL/SQL을 함께 활용함으로 써 효과적인 데이터베이스 접근 가능
• 절차적인 데이터 처리가 가능

 

PL/SQL 런타임 구조

 

1. PL/SQL이 포함된 블록을 실행하면 오라클 서버 혹은 애플리케이션 서버 메모리에 상주하고 있는 PL/SQL 엔진이 해당 블록을 전달받게 된다.
2. 이후 해당 블록에 있는 SQL 문장들을 Context 변환 과정을 거친 후  오라클 서버 프로세스에 전달되어 SQL 문장을 (Parse -> Bind -> Execute -> Fetch(select한정) )을 거쳐서 쿼리를 수행한 후 리턴합니다.
3. 리턴한 결과를 다시 PL/SQL 엔진에게 전달하고 처리된 결과를 변수에 저장하기 때문에 변수를 선언해야 하고 데이터베이스에서 처리된 결과를 가지고 나머지 PL/SQL문을 실행합니다. 

여기서 알 수 있는 점은 결국 PL/SQL이 빨리 시행되기 위해서는 SQL 문장의 처리 속도가 빨라야 한다는 걸 내포하고 있습니다. PL/SQL엔진은 대부분 SQL문장의 처리 결과를 받아서 후속 작업을 진행하는데, 성능상 문제가 되는 부분은 거의 SQL 문장이 잘못 수행되는 경우입니다.

 

PL/SQL 기본구조

 

기본적으로 BLOCK형태이며 선언부, 실행부, 예외처리 부이며 실행부는 필수적 조건이며 나머지는 필요에 따라 선언합니다.

블록의 유형은 익명 블록과 저장 블록이 존재합니다. 익명 블록은 대개 일회성으로 사용하며, 저장 블록은 스키마를 구성하는 오브젝트로 파싱 된 후 서버 내부에 저장되거나 오라클 툴 안에 라이브러리 형태로 저장됩니다. 

 

 

Anonymous PL/SQL Block

 

DECLARE   --PL/SQL 블럭이 시작되는 것을 알려줍니다.
 vno NUMBER(10);  -- 데이터베이스에서 처리한 SQL문의 결과를 저장할 변수 2개를 선언
 vname VARCHAR2(100);
BEGIN -- 실행부가 시작됨을 알려줌
	
	SELECT EMPNO, NAME INTO vno, vname  -- EMP2테이블의 조건에 맞는 사원번호와 이름을 변수에 저장
	FROM EMP2
	WHERE EMPNO = 19900101;

	DBMS_OUTPUT.PUT_LINE(vno||'  '||vname); -- DBMS_OUTPUT패키지를 사용해 화면에 결과를 출력한다.
END; -- PL/SQL블럭이 종료됨을 알려줌
/ -- 해당블럭 수행

 

*DBeaver를 사용하는 경우 ctrl + shift + o를 통해 출력 값이 보이며 따로 set serveroutput on을 설정하지 않아도 되는 걸로 확인된다.

 

PL/SQL 블록 내부 SELECT절의 칼럼의 수와 변수의 수는 동일해야 하며 데이터 타입이 일치해야 합니다. 그렇지 않으면 결과를 변수에 담을 수 없어 에러가 발생하게 됩니다. 또한 데이터는 위의 경우 데이터 결과는 반드시 한건이어야 하여서 WHERE절의 단일행 조건이 반드시 사용되어야 합니다. 

 

 

PL/SQL문 내부에서 DML문과 TCL문을 사용할 수 있습니다. 하지만 DDL과 DCL문은 직접 지원하지 않으며 동적 SQL을 사용해 사용할 수 있습니다.

 

 

변수

 

일반적으로 PL/SQL 선언부에서 선언됩니다. 선언한다는 것은 해당 변수에 들어올 값에 대한 메모리 공간을 미리 확보하고, 해당 데이터 유형을 지정하며, 참조하도록 저장 공간 이름을 지정하는 의미가 있습니다. 변수는 반드시 참조되기 전 선언되어야 합니다.  변수의 범위는 일반적으로 블록의 내부입니다.  따라서 해당 블록의 실행이 종료되면 메모리에서 해당 변수는 제거됩니다.

 

 

 

단순 변수 

1. SCALAR : 단일 값을 가지는 변수의 데이터형을 직접 지정해주는 변수 할당 방식
   
   1. EX) [CONSTANT] V_NAME VARCHAR2(30) [NOT NULL] := 'TEST' ; 
2. Reference : 변수의 데이터형을 다른 칼럼에서 참조 후 지정하는 방식
   1. EX) V_NAME EMP.empno% TYPE

DECLARE 
 v_empno TEST_EMP.EMPNO%TYPE;
 v_name TEST_EMP.NAME%TYPE;
 v_pay TEST_EMP.PAY%TYPE;

BEGIN
	
	SELECT EMPNO, NAME, PAY INTO v_empno, v_name, v_pay
	FROM TEST_EMP
	WHERE empno = 19960101;

	DBMS_OUTPUT.PUT_LINE(v_empno || v_name || v_pay);
	
END;

 

이때 ROWTYPE 변수를 사용하면 *와 같은 모든 타입의 값을 받을 수 있습니다.

 

 

이와 반대되는 개념은 복합변수가 존재하며 Record TYPE, Table TYPE이 존재하지만  복합변수보다는 CURSOR를 사용해 더 쉽게 다중 행 데이터를 저장할 수 있습니다.

 

 

 

PL/SQL 제어문 사용법

 

PL/SQL문은 크게 조건문과 반복문으로 나눌 수 있으며 조건문은 IF, CASE문 등이 존재하며 반복문은 LOOP문, WHILE문, FOR문이 존재합니다.

 

 

1. IF ~ END IF 

 

 - 이 유형은 조건이 여러 개일 경우에 사용하는 가장 기본적인 IF문장입니다. 조건이 여러 개인 경우 부피가 커지기 때문에 자주 사용되지는 않습니다.

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

 

2. IF ~ THEN ~ ELSIF ~END IF 

 

 1번은 조건이 여러 개일 경우 매번 조건을 다시 시작해야 했지만 이 경우는 ELSIF를 통해 연속적으로 이어갈 수 있습니다. 마지막에 END IF문으로 닫기만 하면 됩니다.

IF CONDITION THEN
 EXEC();
ELSIF CONDITION THEN
 EXEC2();
ELSIF CONDITION THEN
 EXEC3();
END IF;

DECLARE
 v_name test_st.name%TYPE;
 v_deptno test_st.deptno1%TYPE;
 v_dept_name varchar2(20);
BEGIN
	
	SELECT name, deptno1 INTO v_name, v_deptno
	FROM test_st
	WHERE name = 'Steve Martin';

    IF (v_deptno = 101) THEN
         v_dept_name := '컴퓨터공학';
    ELSIF (v_deptno = 102) THEN
		 v_dept_name := '조소과';
	ELSIF (v_deptno = 103) THEN
		 v_dept_name := '화확과';
	ELSIF (v_deptno = 201) THEN
		 v_dept_name := '태권도과';
	END IF;

	DBMS_OUTPUT.PUT_LINE(v_dept_name);
END ;

3. 비교 조건이 2개인 경우는 IF ~ THEN ELSE ~ END IF로 동일하게 처리할 수 있습니다.

 

 

 

CASE 조건문은 IF 문장은 여러 가지 조건이 있을 경우 조건을 길게 나열해야 했지만 CASE문은 좀 더 간결하고 간단하게 조건을 파악해 분기시킬 수 있는 제어문입니다.

 

DECLARE

 v_name test_st.name%TYPE;
 v_deptno test_st.deptno1%TYPE;
 v_dept_name varchar2(20);
BEGIN
	
	SELECT name, deptno1 INTO v_name, v_deptno
	FROM test_st
	WHERE name = 'Steve Martin';

   v_dept_name := CASE v_deptno
				   	WHEN 101 THEN '컴퓨터 공학'
				   	WHEN 102 THEN '조소과'
				   	WHEN 103 THEN '화확과'
				   	WHEN 201 THEN '패디과'
				  ELSE 'default 값지정'
   				END ;
   			
	DBMS_OUTPUT.PUT_LINE(v_dept_name);
END ;

 

주의할 점은 when절에서 , 콤마를 사용해 구별하지 않는다는 점과 끝낼 때는 반드시 END라는 키워드를 사용해야 한다는 것입니다.

 

 

BASIC LOOP 문과 WHILE문의 공통점은 반복 횟수를 지정하지 않고  반복 조건을 지정하는 것입니다. 즉 조건이 맞을 동안에는 계속 반복을 수행합니다. 하지만 차이점은 해당 조건을 검사하는 시점이 다릅니다. 

 

BASIC LOOP문은 나중에 조건을 검색하는 것입니다.

DECLARE
 num NUMBER(10) := 0;

BEGIN	
	LOOP
		DBMS_OUTPUT.PUT_LINE(num);
		num := num +1;
		EXIT WHEN num > 5;
	END LOOP;
END;

최초로  PL/SQL문을 조건에 맞지 않아도 바로 실행하는 점과 이와 반대로 WHILE문은 다음과 같습니다.

 

DECLARE
 num NUMBER(10) := 0;

BEGIN	
	WHILE num < 5 loop
		DBMS_OUTPUT.PUT_LINE(num);
		num := num +1;
	END LOOP;
END;

 

 

FOR문은 반복 횟수를 지정할 수 있습니다.

FOR VAR IN [REVERSE] START..END LOOP
  Statement1;
  ...;
  
 END LOOP;

VAR는 선언부에서 선언하지 않아도 되는 변수이며 START.. END는  시작번호와 끝날 번호를 적어서 사용합니다. 

 

START와 END부분에 반드시 숫자만 들어가는 것은 아닙니다. 칼럼이나 커서 등으로 대체할 수도 있습니다.

 

다음 글로 이어서 커서부터 이어가겠습니다.