1. PL/SQL 개요

P L / S Q L에서는 SQL 문을 사용하여 O racle 데이터와 그 데이터를 처리하는 흐름 제어문을 조작할 수 있으며 또한, 상수 및 변수를 선언하고 프로시저 및 함수를 정의하며 런타임 오류를 잡을 수 있습니다. PL/SQL에서는 이와 같이 S Q L의 데이터 조작능력과 절차적 언어의 데이터 처리 능력을 결합합니다.

P L / S Q L은 블록 구조로 이루어진 언어입니다. 즉, PL/SQL 프로그램을 구성하는 기본 단위(프로시저, 함수, 익명 블록)는 임의의 수의 중첩된 하위 블록을 포함할 수 있는 논리 블록입니다. 

블록(또는 하위 블록)을 사용하여 논리적으로 관련된 선언과 명령문을 그룹화하여 사용되는 위치 가까이에 선언을 넣을 수 있습니다. 이러한 선언은 해당 블록에 대해 지역적이며 그 블록이 완료될 때 소멸됩니다.

[ D E C L A R E

-- d e c l a r a t i o n s ]

B E G I N

-- s t a t e m e n t s

[ E X C E P T I O N

-- h a n d l e r s ]

E N D ;

PL/SQL 블록은 선언부, 실행부, 예외 처리부, 세 부분으로 구성.

(PL/SQL에서 예외란 경고 또는 오류 조건을 의미합니다.) 이 중 실행부는 반드시 필요합니다.

선언부가 우선하며 일단 선언되면 항목은 실행부에서 조작할 수 있습니다. 실행 도중 발생된 예외는 예외 처리부에서 처리됩니다.

하위 블록은 PL/SQL 블록이나 하위 프로그램의 실행 및 예외 처리부에서는 중첩될수 있지만 선언부에서는 중첩될 수 없습니다. 또한 선언부의 임의의 블록에서는 로컬 하위 프로그램을 정의할 수 있지만 정의된 블록에서만 해당 로컬 하위 프로그램을 호출할 수 있습니다.

변수와 상수

PL / SQL에서는 상수와 변수를 선언한 다음 SQL과 표현식 사용이 가능한 절차문에서 상수와 변수를 사용할 수 있습니다. 

상수나 변수를 먼저 선언해야만 다른 선언문을 비롯한 다른 명령문에서 참조할 수 있습니다.

 

변수 선언

변수는 CHAR, DATE 또는 NUMBER와 같은 SQL 데이터 유형이나 BOOLEAN 또는 BINARY INTEGER와 같은 PL/SQL 데이터 유형을 가질 수 있습니다. 

예를 들어, 4자리 숫자를 갖는 partno라는 변수와 TRUE나 FALSE와 같은 부울 값을 갖는 instock이라는 변수는 다음과 같이 선언합니다.

part_no NUMBER(4);

in_stock BOOLEAN;

또한 TABLE, VARRAY, RECORD 복합 데이터 유형을 사용하여 중첩 테이블, 가변 길이 배열(약어는 가변 배열)과 레코드를 선언할 수 있습니다.

변수에 값 할당

두 가지 방법으로 변수에 값을 할당할 수 있습니다. 첫 번째 방법은 콜론에 등호를 붙인 할당 연산자( := )를 사용하는 것입니다. 

다음 예와 같이 연산자의 좌변에는 변수를,우변에는 표현식을 넣습니다.

tax := price * tax_rate;

bonus := current_salary * 0.10;

amount := TO_NUMBER(SUBSTR(’750 dollars’, 1, 3));

valid := FALSE;

두 번째 방법은 데이터베이스 값을 선택하거나 인출하여 변수에 값을 할당하는 것입니다. 다음은 사원의 급여를 선택할 때 10 %의 보너스를 계산하는 예입니다.

SELECT sal * 0.10 INTO bonus FROM emp WHERE empno = emp_id;

bonus 변수를 다른 계산에서 사용하거나 그 값을 데이터베이스 테이블에 삽입할 수있습니다.

상수 선언

상수 선언 방법은 변수 선언과 유사하나 CONSTANT 키워드를 추가하고 즉시 해당 상수에 값을 할당해야 합니다. 이후에는 더 이상 상수 값을 할당할 수 없습니다. 

다음은 credit_limit이라는 상수를 선언하는 예입니다.

credit_limit CONSTANT REAL := 5000.00;

커서

커서라는 PL/SQL 구성을 사용하여 작업 영역의 이름을 지정하고 저장된 정보에 액세스 할 수 있으며, 암시적 커서와 명시적 커서 두 종류가 있습니다. PL/SQL은 하나의 행만 반환하는 질의를 포함하여 모든 SQL 데이터 조작문에 대해 커서를 암시적으로 선언하고 하나 이상의 행을 반환하는 질의에는 다음 예와 같이 개별적으로 행을 처리할 커서를 명시적으로 선언할 수 있습니다.

DECLARE

  CURSOR c1 IS

     SELECT empno, ename, job FROM emp WHERE deptno = 20;

복수 행 질의 처리는 파일 처리와 유사합니다. 예를 들어, COBOL 프로그램은 파일을 열고 레코드를 처리한 다음 파일을 닫습니다. 마찬가지로 PL/SQL 프로그램은 커서를 열고 질의에 의해 반환된 행을 처리한 다음 커서를 닫습니다. 파일 포인터가 열린 파일에서의 현재 위치를 표시하는 것처럼 커서는 결과 집합에서 현재 위치를 표시합니다.

OPEN, FETCH, CLOSE 문을 사용하여 커서를 제어합니다. OPEN 문은 커서와 연관된 질의를 실행하여 결과 집합을 식별하고 첫 번째 행 앞에 커서를 놓습니다. FETCH 문은 현재 행을 읽어 들인 후 다음 행으로 커서를 보냅니다. C L O S E 문은 마지막 행 처리가 끝나면 커서를 사용할 수 없도록 합니다.

커서 FOR 루프

명시적 커서가 필요한 대부분의 경우에는 OPEN, FETCH, CLOSE 문 대신 커서 FOR루프를 사용하여 코딩을 단순화할 수 있습니다. 커서 FOR 루프는 루프 인덱스를 데이터베이스에서 인출된 행을 나타내는 레코드로 암시적으로 선언한 다음 커서를 열고 결과 집합에서 행 값을 레코드 필드로 반복하여 인출한 후 모든 행이 처리되면 커서 를 닫습니다. 다음은 커서 FOR에 있는 루프가  emp_rec를 레코드로 암시적으로 선언하는 예입니다.

DECLARE

    CURSOR c1 IS

        SELECT ename, sal, hiredate, deptno FROM emp;

...

BEGIN

   FOR emp_rec IN c1 LOOP

       ...

       salary_total := salary_total + emp_rec.sal;

   END LOOP;

레코드의 각 필드를 참조하려면 구성 요소 선택자 역할을 하는 점( . ), 점 표기법을 사용합니다.

커서 변수

커서 변수는 커서와 마찬가지로 복수 행 질의의 결과 집합에서 현재 행을 가리키지만, 커서와 달리 특정 질의와 결합되지 않고 임의의 유형과 호환 가능한 질의에 대해 열릴 수 있습니다. 커서 변수는 새로운 값을 할당하고 오라클 데이터베이스에 저장된 하위 프로그램에 전달할 수 있다는 점에서 전형적인 PL/SQL 변수로 데이터 검색을 중앙 집중화할 수 있는 융통성 있고 편리한 방법을 제공합니다.

일반적으로 커서 변수를 형식 매개변수 중 하나로 선언한 내장 프로시저로 전달하여 커서 변수를 엽니다. 다음 프로시저는 선택된 질의에 대해 커서 변수 generic_cv를 엽니다.

PROCEDURE open_cv (generic_cv IN OUT GenericCurTyp,choice NUMBER) IS

BEGIN

IF choice = 1 THEN

OPEN generic_cv FOR SELECT * FROM emp;

ELSIF choice = 2 THEN

OPEN generic_cv FOR SELECT * FROM dept;

ELSIF choice = 3 THEN

OPEN generic_cv FOR SELECT * FROM salgrade;

END IF;

속성

%TYPE

%TYPE 속성은 변수 또는 데이터베이스 열의 데이터 유형을 제공합니다. 이 속성은 데이터베이스 값을 저장할 변수를 선언할 때 특히 유용합니다. 예를 들어, books라는 테이블에 title이라는 열이 있을 경우 title 열과 동일한 데이터 유형으로 mytitle이라는 변수를 선언하려면 다음과 같이 점 표기법과 %TYPE 속성을 사용하면 됩니다.

my_title books.title%TYPE;

title의 정확한 데이터 유형을 몰라도 되고 둘째, title의 데이터베이스 정의를 (예를 들어, 더 긴 문자열로) 변경하는 경우 mytitle의 데이터 유형도 실행 시간에 그에 따라 변경됩니다.

%ROWTYPE

PL/SQL에서 레코드는 데이터를 그룹화하는데 사용되며 데이터 값을 저장할 수 있는 여러 관련 필드로 구성됩니다. 

%ROWTYPE 속성은 테이블의 한 행을 나타내는 레코드유형을 제공하며 테이블에서 선택하거나 또는 커서나 커서 변수에서 인출한 데이터행 전체를 저장할 수 있습니다.

행의 열과 레코드의 해당 필드는 동일한 이름과 데이터 유형을 가집니다. 

다음은 dept_rec 레코드를 선언하는 예입니다. dept_rec 레코드의 필드는 dept 테이블의 열과 동일한 이름 및 데이터 유형을 가집니다.

DECLARE

dept_rec dept%ROWTYPE; -- declare record variable

필드를 참조하려면 다음 예와 같이 점 표기법을 사용합니다.

my_deptno := dept_rec.deptno;

사원의 이름, 급여, 고용일, 직책을 읽어 들이는 커서를 선언하려는 경우 다음과 같이 %ROWTYPE을 사용하여 동일한 정보를 저장하는 레코드를 선언할 수 있습니다.

DECLARE

CURSOR c1 IS

SELECT ename, sal, hiredate, job FROM emp;

emp_rec c1%ROWTYPE; -- declare record variable that represents a row fetched from the emp table

다음 명령문을 실행하면

FETCH c1 INTO emp_rec;

emp_rec의 ename 필드에 emp 테이블의 ename 열 값이 지정되고 sal 필드에 sal열 값이 지정됩니다. 

예제 테이블 >

 

CREATE TABLE SCOTT.DEPT
(
 DEPTNO   NUMBER (2) NOT NULL,
 DNAME    VARCHAR2 (14),
 LOC      VARCHAR2 (13)
)

;

CREATE TABLE SCOTT.EMP
(
 EMPNO      NUMBER (4) NOT NULL,
 ENAME      VARCHAR2 (10),
 JOB        VARCHAR2 (9),
 MGR        NUMBER (4),
 HIREDATE   DATE,
 SAL        NUMBER (7,2),
 COMM       NUMBER (7,2),
 DEPTNO     NUMBER (2),
 CONSTRAINT FK_DEPTNO FOREIGN KEY (DEPTNO) REFERENCES SCOTT.DEPT (DEPTNO)
)

;

CREATE TABLE SCOTT.DEPT
(
 DEPTNO   NUMBER (2) NOT NULL,
 DNAME    VARCHAR2 (14),
 LOC      VARCHAR2 (13)
)

;

 

 

 

예제 데이터 >