[번역] Embedded PL/SQL - 03

목 차
▣ 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 )

6) Embedded PL/SQL에서 커서 사용
프로그램에서 동시에 사용할 수 있는 커서의 최대수는 OPEN_CURSOR 파라미터에
의해서 초기에 결정된다. 아래의 계산은 사용된 커서의 최대수를 결정하는 방법을
보여주고 있다. 이와같은 방법으로 계산된 커서의 합계는 OPEN_CURS0RS를 초과
할 수 없다.

SQL statement cursors
PL/SQL parent cursors
PL/SQL child cursors
+ 6 cursors for overhead
--------------------------
Sum of cursors in use

연결수를 초과하면 오라클은 에러를 발생한다.

7) Stored PL/SQL and Java Subprograms
SQL*Plus와같은 오라클 툴을 사용해서 생성된 subprogram을 stored subprogram이라고
부른다. 데이터 사전에 한번 컴파일되고 저장되면 데이터베이스 오브젝트가되고,
이것은 다시 컴파일하지 않고 재 실행 할 수 있다.

PL/SQL block이나 스토어드 프로시져내의 subprogram이 오라클로 보내졌을때
이 subprogram을 inline subprogram이라고 부른다. 오라클은 inline subprogram을
컴파일하고 그것을 SGA(System Global Area)에 저장한다. 하지만 소스 자체나 오브젝트
코드를 데이터 사전에 저장 하지는 않는다.

패키지내에 정의된 subprogram을 packaged subprogram이라고 부르면 패키지에 정의되지
않은 stored subprogram은 standalone subprogram이라고 부른다.

7-1) Stored Subprogram 생성
호스트 프로그램에 CREATE FUNCTION, CREATE PROCEDURE, CREATE PACKAGE를 SQL문에
끼워 넣을 수 있다. 아래의 예제를 보자.

EXEC SQL CREATE
FUNCTION sal_ok (salary REAL, title CHAR)
RETURN BOOLEAN AS
min_sal REAL;
max_sal REAL;
BEGIN
SELECT losal, hisal INTO min_sal, max_sal
FROM sals
WHERE job = title;
RETURN (salary >= min_sal) AND
(salary <= max_sal);
END sal_ok;
END-EXEC;
위의 예제에서 주목할 것은 EXEC SQL EXECUTE가 아닌 EXEC SQL로 시작하며
END-EXEC로 끝난다.

다음에 나올 예제는 get_employees라는 프로시져를 포함한 패키지를 생성한다.
batch크기는 다른 stored subprogram이나 클라이언트 프로그램에 있는 프로시져를
호출 하면서 결정된다.

프로시져는 OUT형식의 파라미터로 PL/SQL테이블을 3개 선언한다. 그리고 사원 데이터를
취득한다. 실제 파라미터는 호스트배열이다. 프로시져가 끝났을때 자동적으로 호스트
배열에 PL/SQL테이블에 모든 행값들을 할당한다.

EXEC SQL CREATE OR REPLACE PACKAGE emp_actions AS
TYPE CharArrayTyp IS TABLE OF VARCHAR2(10)
INDEX BY BINARY_INTEGER;
TYPE NumArrayTyp IS TABLE OF FLOAT
INDEX BY BINARY_INTEGER;
PROCEDURE get_employees(
dept_number IN INTEGER,
batch_size IN INTEGER,
found IN OUT INTEGER,
done_fetch OUT INTEGER,
emp_name OUT CharArrayTyp,
job_title OUT CharArrayTyp,
salary OUT NumArrayTyp);
END emp_actions;
END-EXEC;

EXEC SQL CREATE OR REPLACE PACKAGE BODY emp_actions AS

CURSOR get_emp (dept_number IN INTEGER) IS
SELECT ename, job, sal FROM emp
WHERE deptno = dept_number;

PROCEDURE get_employees(
dept_number IN INTEGER,
batch_size IN INTEGER,
found IN OUT INTEGER,
done_fetch OUT INTEGER,
emp_name OUT CharArrayTyp,
job_title OUT CharArrayTyp,
salary OUT NumArrayTyp) IS
BEGIN
IF NOT get_emp%ISOPEN THEN
OPEN get_emp(dept_number);
END IF;
done_fetch := 0;
found := 0;
FOR i IN 1..batch_size LOOP
FETCH get_emp INTO emp_name(i),
job_title(i), salary(i);
IF get_emp%NOTFOUND THEN
CLOSE get_emp;
done_fetch := 1;
EXIT;
ELSE
found := found + 1;
END IF;
END LOOP;
END get_employees;
END emp_actions;
END-EXEC;

7-2) Stored PL/SQL 혹은 Java Subprogram 호출
호스트 프로그램에서 stored subprogram를 호출하기위해서는, anonymous PL/SQL block
을 사용하든지, CALL문을 사용 할 수 있다.

7-2-1) Anonymous PL/SQL Block
아래의 예제를 보면, raise_salary라는 standalone프로시져를 호출한다.

EXEC SQL EXECUTE
BEGIN
raise_salary(:emp_id, :increase);
END;
END-EXEC;

stored subprogram들은 파라미터를 가질 수 있다. 위의 예제에서 실제 파라미터는
emp_id와 increase이다.

EXEC SQL EXECUTE
BEGIN
emp_actions.raise_salary(:emp_id, :increase);
END;
END-EXEC;

위의 예제는 emp_actions 패키지에 저장된 프로시져를 사용하는 예제이다.

아래의 예제는 get_employees 프로시져를 반복적으로 호출하고 데이터가 없을때까지
사원데이터를 보여준다. 이예제에서 3개의 형식적인 파라미터는 PL/SQL테이블이며
이에 상응하는 실제 파라미터는 호스트 배열이다.

/******************************************************************************
/*
/* Sample Program 9: stored procedure 호출
/* SCOTT/TIGER 계정으로 오라클에 연결하고 여러개의 호스트 배열
/* 을 선언하고, CALLDEMO package에 선언한 GET_EMPLOYEES을 사용하여
/* ASIZE값까지 값을 취득한다.
/*
*******************************************************************************/

#include <stdio.h>
#include <string.h>
EXEC SQL INCLUDE sqlca.h;
typedef char asciz[20];
typedef char vc2_arr[11];
EXEC SQL BEGIN DECLARE SECTION;
/* 사용자 정의 문자열 */
EXEC SQL TYPE asciz IS STRING(20) REFERENCE;
/* 사용자정의 VARCHAR 배열요소 */
EXEC SQL TYPE vc2_arr IS VARCHAR2(11) REFERENCE;

asciz username;
asciz password;
int dept_no; /* 쿼리를 위한 부서 */
vc2_arr emp_name[10]; /* 리턴받는 이름 배열 */
vc2_arr job[10];
float salary[10];
int done_flag;
int array_size;
int num_ret; /* 리턴되는 행수 */
EXEC SQL END DECLARE SECTION;
long SQLCODE;
void print_rows(); /* 프로그램 출력처리 함수 */
void sql_error(); /* 에러처리 함수 */

///////////////////////////////////////////////////////////////////////////////
// 메인함수
///////////////////////////////////////////////////////////////////////////////
void main()
{
int i;
char temp_buf[32];
/* Connect to ORACLE. */
EXEC SQL WHENEVER SQLERROR DO sql_error();
strcpy(username, "scott");
strcpy(password, "tiger");
EXEC SQL CONNECT :username IDENTIFIED BY :password;
printf("nConnected to ORACLE as user: %snn", username);
printf("Enter department number: ");
gets(temp_buf);
dept_no = atoi(temp_buf);/* Print column headers. */
printf("nn");
printf("%-10.10s%-10.10s%sn", "Employee", "Job", "Salary");
printf("%-10.10s%-10.10s%sn", "--------", "---", "------");
/* Set the array size. */
array_size = 10;
done_flag = 0;
num_ret = 0;
/* Array fetch loop.

* The loop continues until the OUT parameter done_flag is set.
* Pass in the department number, and the array size--
* get names, jobs, and salaries back.
*/
for (;;)
{
EXEC SQL EXECUTE
BEGIN calldemo.get_employees
(:dept_no, :array_size, :num_ret, :done_flag,
:emp_name, :job, :salary);
END;
END-EXEC;
print_rows(num_ret);
if (done_flag)
break;
}
/* Disconnect from the database. */
EXEC SQL COMMIT WORK RELEASE;
exit(0);
}

///////////////////////////////////////////////////////////////////////////////
// 출력함수
///////////////////////////////////////////////////////////////////////////////
void print_rows(n)
int n;
{
int i;
if (n == 0)
{
printf("No rows retrieved.n");
return;
}
for (i = 0; i < n; i++)
printf("%10.10s%10.10s%6.2fn",
emp_name[i], job[i], salary[i]);
}

///////////////////////////////////////////////////////////////////////////////
// 에러처리함수
///////////////////////////////////////////////////////////////////////////////
void sql_error()
{
char msg[512];
int buf_len, msg_len;
EXEC SQL WHENEVER SQLERROR CONTINUE;
buf_len = sizeof(msg);
sqlglm(msg, &buf_len, &msg_len);
printf("nORACLE error detected:");
printf("n%.*s n", msg_len, msg);
EXEC SQL ROLLBACK WORK RELEASE;
exit(1);
}

7-2-2) 원격 접근
원격접근해서 아래와 같이 PL/SQL을 사용할 수 있다.
dallas라고 하는 원격에 있는 데이터 베이스의 raise_salary프로시져를 호출해서
사용하는 예제를 보여주고 있다.

EXEC SQL EXECUTE
BEGIN
raise_salary@dallas(:emp_id, :increase);
END;
END-EXEC;

아래와 같이 synonym을 사용해서 위치 투명성을 제공할 수도 있다.

CREATE PUBLIC SYNONYM raise_salary
FOR raise_salary@dallas;

7-2-3) CALL문
CALL문은 아래와같은 형태를 가진다.
EXEC SQL
CALL [schema.] [package.]stored_proc[@db_link](arg1, ...)
[INTO :ret_var [[INDICATOR]:ret_ind]] ;

schema : 프로시져가 포함하고 있는 스키마
package : 프로시져가 포함된 패키지
stored_proc : Java 혹은 PL/SQL stored procedure
db_link : remote database link
arg1... : 넘겨지는 파라미터
ret_var : 결과를 추출하는 선택적인 호스트 변수
ind_var : ret_var을 위한 선택적인 지시자 변수

7-2-4) CALL문 예제
정수로 인자를 넘겨받고 팩토리얼로 리턴받는 fact함수를 생성하는 예제이다.

EXEC SQL CREATE OR REPLACE PACKAGE BODY mathpkg as
function fact(n IN INTEGER) RETURN INTEGER AS
BEGIN
IF (n <= 0) then return 1;
ELSE return n * fact(n - 1);
END IF;
END fact;
END mathpkge;
END-EXEC.

Pro*C/C++에서 CALL문을 사용해서 fact함수를 사용하는 예는 다음과 같다.
...

int num, fact;
...

EXEC SQL CALL mathpkge.fact(:num) INTO :fact ;
...

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