[번역] Embedded PL/SQL - 01

목 차
▣ PL/SQL의 장점( Advantages of PL/SQL )
▣ Embedded PL/SQL Blocks
▣ 호스트 변수( Host Variables )
▣ 지시자 변수( Indicator Variables )
▣ 호스트 배열 ( Host Arrays )
▣ 임베디트 PL/SQL에서 커서 사용방법 (Cursor Usage in Embedded PL/SQL )
▣ Stored PL/SQL and Java Subprograms
▣ 외부 프로시져( External Procedures )
▣ Dynamic SQL 사용 ( Using Dynamic SQL )

1) PL/SQL의 장점( Advantages of PL/SQL )
이장은 PL/SQL이 제공하는 장점및 특징에 대해서 설명한다.
☞ 더 나은 성능
☞ 오라클 통합(Integration with Oracle)
☞ FOR 반복문을위한 커서(Cursor FOR Loops )
☞ 프로시져와 함수
☞ 패키지(Packages)
☞ PL/SQL테이블
☞ 사용자정의 레코드 ( User_Defined Records )

1-1) 더 나은 성능 ( Better Performance )
PL/SQL은 과부하를 줄이고, 성능을 향상시키며 생상성을 증대한다. 예를들어,
PL/SQL이 없으면 오라클은 한번에 한개의 SQL문을 처리해야만한다. PL/SQL은 서버에
SQL문 블록을 보낼 수 있다. 이것은 프로그램과 오라클간의 통신을 최소화한다.

1-2) Integration with Oracle
대부분의 오라클 데이터유형은 오라클 데이터사전에 내재된 것이다. 더욱이,
%TYPE 속성은 아래의 예처럼 사용할 수 있다.

job_title emp.job%TYPE;

위의 예처럼, 컬럼의 데이터 유형을 정확히 알 필요가 없다. 컬럼의 정의가 바뀐다고
하더라도 자동적으로 형이 변경된것으로 적용된다. 이것은 데이타에 의존적이지 않고
유지보수 비용을 줄일 수 있다.

1-3) Cursor FOR Loops
커서를 조작하고 선언하기위해서, PL/SQL에서는 DECLARE, OPEN, FETCH, CLOSE문을
사용할 필요가 없다. 대신 cursor FOR Loop문을 사용할 수 있다.
아래에 예가 있다.

DECLARE
...

BEGIN
FOR emprec IN (SELECT empno, sal, comm FROM emp) LOOP
IF emprec.comm / emprec.sal > 0.25 THEN ...
...

END LOOP;
END;

1-4) 프로시져와 함수
PL/SQL은 프로시져와 함수라고하는 두가지 서브프로그램 유형을 가지고 있다.
행위를 수행하기위해서 프로시져를 사용하고 값을 산정하기위해서 함수를 사용한다.
신규부서를 생성하는 프로시져가 필요하다면 아래의 예제와같이 하면된다.

PROCEDURE create_dept
(new_dname IN CHAR(14),
new_loc IN CHAR(13),
new_deptno OUT NUMBER(2)) IS
BEGIN
SELECT deptno_seq.NEXTVAL INTO new_deptno FROM dual;
INSERT INTO dept VALUES (new_deptno, new_dname, new_loc);
END create_dept;

인자에는 3가지 모드가 있다 : IN(default), OUT, IN OUT.
IN - 서브프로그램이 호출될때 값을 넘겨주는 파라미터
OUT - 호출자에게 값을 리턴해줄때 사용하는 파라미터
IN OUT - 초기에는 서버프로그램에 인자를 넘기며, 호출자에게 변경된값을 리턴한다.

1-5) 패키지 ( Package )
PL/SQL은 논리적인 관계유형, 프로그램 오브젝트, 서브프로그램을 패키지로 묶을
수 있도록한다. 아래의 예제에서처럼 패키지는 분류(Specification)와 몸체(Body)로
분를할수 있으며, 분류(specification)는 유형,상수,변수,예외,커서, 이용할수
있는 서브 프로그램과같은 프로그램의 인터페이스를 말한다.
몸체(body)는 커서및 서브프로그램을 정의하고, 분류를 구현한 부분이다.

PACKAGE emp_actions IS -- package specification
PROCEDURE hire_employee (empno NUMBER, ename CHAR, ...);
PROCEDURE fire_employee (emp_id NUMBER);
END emp_actions;

PACKAGE BODY emp_actions IS -- package body
PROCEDURE hire_employee (empno NUMBER, ename CHAR, ...) IS
BEGIN
INSERT INTO emp VALUES (empno, ename, ...);
END hire_employee;

PROCEDURE fire_employee (emp_id NUMBER) IS
BEGIN
DELETE FROM emp WHERE empno = emp_id;
END fire_employee;
END emp_actions;

패키지의 분류(specification)에서 선언부분만 보여지고 프로그램이 접근할 수 있으며
구현부분은 숨겨지고 접근할 수 없게 된다.

1-6) PL/SQL 테이블들
PL/SQL은 TABLE이라고 이름 붙여진 혼합 데이타 유형을 제공한다.
아래의 예제에서 NumTabTyp라고하는 테이블 유형 선언을 볼 수 있다.

...

DECLARE
TYPE NumTabTyp IS TABLE OF NUMBER
INDEX BY BINARY_INTEGER;
...

BEGIN
...

END;
...

1-7) 사용자정의 레코드( User-Defined Records )
아래의 예제처럼 사용자 정의 레코드를 사용하여 PL/SQL에 적용 할 수 있다.

DECLARE
TYPE DeptRecTyp IS RECORD
(deptno NUMBER(4) NOT NULL, -- default is NULL allowed
dname CHAR(9),
loc CHAR(14));

사용방법은 아래와 같이 선언하고 사용할 수 있다.

dept_rec DeptRecTyp;

dept_rec.dname ...

2) Embedded PL/SQL Blocks
Pro*C/C++전처리기는 하나의 SQL문처럼 PL/SQL 블록을 다룬다.
SQL문이 위치 할 수 있는 곳은 어디든지 PL/SQL 블록이 위치할 수 있으며, 아래의
예제처럼 ,EXEC SQL EXECUTE 와 END-EXEC 키워드로 감싸서 사용하면 된다.

EXEC SQL EXECUTE
DECLARE
...

BEGIN
...

END;
END-EXEC; // END-EXEC다음에는 세미콜론이 와야만 한다.

3) 호스트 변수 (Host Variables )
호스트변수는 호스트언어와 PL/SQL블록사이의 통신을 위한 키이다. 사용방법은
SQL문에서 사용하는 방법과 같이 사용하면되고, PL/SQL전체 블록에서 글로벌로
다루어진다.

3-1) Example: PL/SQL에서 호스트변수를 사용하는 예제
아래의 예제는 호스트 변수를 PL/SQL에서 어떻게 사용하는지를 보여주고 있다.
사번을 입력받고, 일목록, 고용일자, 봉급등을 표시하는 프로그램이다.

char username[100], password[20];
char job_title[20], hire_date[9], temp[32];
int emp_number;
float salary;

#include <sqlca.h>
printf("Username? n");

gets(username);
printf("Password? n");
gets(password);
EXEC SQL WHENEVER SQLERROR GOTO sql_error;
EXEC SQL CONNECT :username IDENTIFIED BY :password;
printf("Connected to Oraclen");

for (;;)
{
printf("Employee Number (0 to end)? ");
gets(temp);
emp_number = atoi(temp);
if (emp_number == 0)
{
EXEC SQL COMMIT WORK RELEASE;
printf("Exiting programn");
break;
}
/*-------------- begin PL/SQL block -----------------*/
EXEC SQL EXECUTE
BEGIN
SELECT job, hiredate, sal
INTO :job_title, :hire_date, :salary
FROM emp
WHERE empno = :emp_number;
END;
END-EXEC;
/*-------------- end PL/SQL block -----------------*/
printf("Number Job Title Hire Date Salaryn");
printf("------------------------------------n");
printf("%6d %8.8s %9.9s %6.2fn",
emp_number, job_title, hire_date, salary);
}
...
exit(0);
sql_error:
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL ROLLBACK WORK RELEASE;
printf("Processing errorn");
exit(1);

위의 예제에서는 블록내로 들어가기전에 emp_number가 설정되고 블록내에서
호스트 변수인 job_title,hire_date, salary가 설정되는것을 주목하라.

3-2) 완전한 예제
아래의 예제는 은행 이용자 번호를 입력하고, 트랜젝션 유형, 트랜젝션량,
빗혹은 사용자의 신용을 보여주고, 사용자가 존재하지 않으면, 예외를 발생한다.
트랜젝션이 완료하면 상태를 표시한다.

#include <stdio.h>
#include <sqlca.h>

char username[20];
char password[20];
char status[80];
char temp[32];
int acct_num;
double trans_amt;
void sql_error();

void main()
{
char trans_type;
strcpy(password, "TIGER");
strcpy(username, "SCOTT");
EXEC SQL WHENEVER SQLERROR DO sql_error();
EXEC SQL CONNECT :username IDENTIFIED BY :password;
printf("Connected to Oraclen");
for (;;)
{
printf("Account Number (0 to end)? ");
gets(temp);
acct_num = atoi(temp);
if(acct_num == 0)
{
EXEC SQL COMMIT WORK RELEASE;
printf("Exiting programn");
break;
}
printf("Transaction Type - D)ebit or C)redit? ");
gets(temp);
trans_type = temp[0];
printf("Transaction Amount? ");
gets(temp);
trans_amt = atof(temp);
/*----------------- begin PL/SQL block -------------------*/
EXEC SQL EXECUTE
DECLARE
old_bal NUMBER(9,2);
err_msg CHAR(70);
nonexistent EXCEPTION;
BEGIN
:trans_type := UPPER(:trans_type);

IF :trans_type = ’C’ THEN -- credit the account
UPDATE accts SET bal = bal + :trans_amt
WHERE acctid = :acct_num;

IF SQL%ROWCOUNT = 0 THEN -- no rows affected
RAISE nonexistent;
ELSE
:status := ’Credit applied’;
END IF;
ELSIF :trans_type = ’D’ THEN -- debit the account
SELECT bal INTO old_bal FROM accts
WHERE acctid = :acct_num;

IF old_bal >= :trans_amt THEN -- enough funds
UPDATE accts SET bal = bal - :trans_amt
WHERE acctid = :acct_num;
:status := ’Debit applied’;
ELSE
:status := ’Insufficient funds’;
END IF;
ELSE
:status := ’Invalid type: ’ || :trans_type;
END IF;

COMMIT;

EXCEPTION
WHEN NO_DATA_FOUND OR nonexistent THEN
:status := ’Nonexistent account’;
WHEN OTHERS THEN
err_msg := SUBSTR(SQLERRM, 1, 70);
:status := ’Error: ’ || err_msg;
END;
END-EXEC;
/*----------------- end PL/SQL block ----------------------- */
printf("nStatus: %sn", status);
}
exit(0);
}

void sql_error()
{
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL ROLLBACK WORK RELEASE;
printf("Processing errorn");
exit(1);
}

3-4) VARCHAR 유형
VARCHAR형은 가변적인 스트링을 선언할때 사용한다.
VARCHAR형이 입력 호스트 변수라면 크기를 설정해야만 하며, 출력 호스트 변수로
사용하려고 하면 오라클은 자동으로 길이를 설정한다. PL/SQL 블록에서 사용하기
위해서는 블록안으로 들어가기전에 초기화를 시켜주어야한다.
아래의 예제에서 이에대한 내용을 보여주고 있다.

int emp_number;
varchar emp_name[10];
float salary;
...

emp_name.len = 10; /* 길이 component를 초기화 */

EXEC SQL EXECUTE
BEGIN
SELECT ename, sal INTO :emp_name, :salary
FROM emp
WHERE empno = :emp_number;
...

END;
END-EXEC;
...

3-5) 제한사항
PL/SQL블록에서 포인터나 배열문법을 사용하지말라. PL/SQL 컴파일러는 C호스트
변수 표현을 이해하지 못하며, 그들을 파싱할 수 없다. 아래의 예제는 틀린 표현
이다.

EXEC SQL EXECUTE
BEGIN
:x[5].name := ’SCOTT’;
...

END;
END-EXEC;

이러한 에러를 피하기위해서는 아래의 코드로 수정하면된다.

name = x[5].name ;
EXEC SQL EXECUTE
BEGIN
:name := ...;
...

END;
END-EXEC;

4) 지시자 변수( Indicator Variables )
PL/SQL은 지시자 변수가 필요하지 않다. 왜냐하면, NULL인지를 확인하기위해서
IS NULL 연산자를 사용할 수가 있다.

IF variable IS NULL THEN .....

그리고, NULL을 할당하기위해서 할당 연산자를 사용할 수 있다. 예는 아래와같다.

variable := NULL;

아래의 예제는 지시자 변수를 사용하는 예제이다.

EXEC SQL EXECUTE
BEGIN
SELECT ename, comm
INTO :emp_name, :commission :ind_comm
FROM emp
WHERE empno = :emp_number;

IF :commission :ind_comm IS NULL THEN ...
...

END;
END-EXEC;

4-1) NULL값 핸들링( NULLs Handling )
block내로 들어갈때, 지시자 변수는 -1값을 가진다. PL/SQL은 자동으로
호스트 변수에 NULL을 할당한다. 블록을 빠져나올때, 만약에 호스트 변수가 NULL
이면, PL/SQL은 자동적으로 -1값으로 지시자 변수를 할당한다.
다음 예제에서 ind_sal이 블록내로 들어가기 전에 -1값으로 설정되었다면,
salary_missing 예외가 발생한다.

...

EXEC SQL EXECUTE
BEGIN
IF :salary :ind_sal IS NULL THEN
RAISE salary_missing;
END IF;
...

END;
END-EXEC;
...

4-2) 잘려진 값
PL/SQL은 문자열이 잘려서 호스트 변수에 설정될때 예외를 발생하지 않는다.
그러나 지시자 변수를 사용하면, PL/SQL은 스트링의 원래 길에서 그 값을 설정한다.
아래의 예제는, emp_name에 잘려진 값을 설정되면 ind_name의 값을 체크함으로서
알 수가 있게 된다.

...

EXEC SQL EXECUTE
DECLARE
...

new_name CHAR(10);
BEGIN
...

:emp_name:ind_name := new_name;
...

END;
END-EXEC;

출처 - http://younbok.egloos.com/9342722