랄라라

클래스를 로딩하는 책임을 지니고 있는 추상 클래스. 

기본전략: 바이너리 이름(String)을 받아서 파일 이름으로 바꾸고 파일 시스템에서 해당하는 이름의 클래스 파일을 읽어들인다.

위임 모델(delegation model)을 사용하여 클래스와 리소스를 찾는다. 각각의 ClassLoader 인스턴스는 연관된 상위(parent) 클래스 로더를 가지고 있다. 자신이 찾아 보기전에 상위 클래스 로더에 요청하여 먼저 찾아본다. VM 내장 클래스 로더인 "부트스트랩 클래스 로더"는 상위 클래스 로더가 없고 자신이 다른 ClassLoader 인스턴스의 상위가 된다.

보통 JVM은 플랫폼-독립적인 방식으로 로컬 파일 시스템에서 클래스를 읽어들인다. 예를 들어 유닉스 시스템에서 VM은 CLASSPATH 환경 변수에 정의되어 있는 디렉토리에서 클래스를 로딩한다.

하지만 어떤 클래스들은 파일에서 읽어오지 않고 네트워크에서 가져오거나 애플리케이션이 동작하면서 만들어지는 것도 있다. defineClass 메서드는 바이트 배열을 Class 클래스 인스턴스로 변환한다. Class.newInstance를 사용하여 그렇게 새로 정의된 클래스 인스턴스를 만들 수 있다.

클래스로더에 의해 만들어지는 객체의 메소드와 생성자는 다른 클래스를 참조할 수도 있다. 그렇게 참조하는 클래스들을 판단하기 위해 VM은 원래 클래스를 생성한 클래스 로더의 loadClass 메서드를 호출한다.

예를 들어 네트워크 클래스 로더를 만들어 다른 서버에서 클래스 파일을 다운로드 할 수도 있다. 다음은 예제 코드다.

1) 왜 배열을 사용하는가?
배열은 프로그래밍하는 시간을 줄이며, 성능 향상을 시킬수 있다. 오라클과의 통신
과부하도 줄일수 있다. 예를 들어, 300명의 사원정보를 입력해야한다고 가정할때
배열이 없다면, 각사원의 정보를 insert해야하지만 배열을 사용하여 1번의 insert
문을 사용해서 입력할 수 있다.

2) 호스트 배열의 선언
아래의 예제는 배열을 선언한 예이다.

char emp_name[50][10];
int emp_number[50];
float salary[50];

VARCHAR형의 배열도 선언이 가능하다.

VARCHAR v_array[10][30];

2-1) 제한사항
object형에대한것을 제외하고, 호스트배열의 포인터를 선언 할수는 없다.
문자형배열을 제외하고, 1차원배열만을 지원한다.
아래의 예제는 유효하지 않은 선언이다.

int hi_lo_scores[25][25]; /* not allowed */

2-2) 배열의 최대크기
배열의 최대크기는 32K정도이며 이는 시스템에 따라서, 운영체제에 따라서 달라
질 수 있다.최대크기를 넘었을경우 "parameter out of range"라는 런타임에러가
발생하게 된다.

3) SQL문에서의 배열사용
SQL문에서 배열은 입력변수,출력변수로서 사용할 수 있다.

3-1) 호스트배열 참조
아래의 예제에서 25개행이 입력된다.

int emp_number[50];
char emp_name[50][10];
int dept_number[25];

/* Populate host arrays here. */
EXEC SQL INSERT INTO emp (empno, ename, deptno)
VALUES (:emp_number, :emp_name, :dept_number);

반복문을 사용해서 아래와 같이 입력하는 것도 가능한다.

for (i = 0; i < 50; i += 5)
EXEC SQL INSERT INTO emp (empno, deptno)
VALUES (:emp_number[i], :dept_number[i]);

그러나, 입력해야할 데이터가 더 많을경우 이것에 대한 처리는 할 수가 없게된다.
오라클은 호스배열수가 n개라면 n번 실행해야할 문장으로 다룬다.

3-2) 지시자 배열의 사용
지시자배열은 입력호스트배열에 NULL을 할당하기위해서 지시자변수를 사용하며,
출력호스트배열에 NULL을 감지하거나, 값이 짤려졌는지를 확인하기위해서 사용한다.
아래의 예제는 지시자배열과 함께 INSERT하는 방법을 보여주고 있다.

int emp_number[50];
int dept_number[50];
float commission[50];
short comm_ind[50]; /* indicator array */

/* Populate the host and indicator arrays. To insert a null
into the comm column, assign -1 to the appropriate
element in the indicator array. */
EXEC SQL INSERT INTO emp (empno, deptno, comm)
VALUES (:emp_number, :dept_number,
:commission INDICATOR :comm_ind);

3-3)오라클 제한 사항
VALUES, SET, INTO, 혹은 WHERE 절에서는 호스트 배열을 사용할 수 없다.
UPDATE 혹은DELETE 문의 CURRENT OF과 함께 호스트배열은 사용할 수 없다.

8) 외부 프로시져( External Procedures )
PL/SQL에서 C함수를 사용할수 있다. C함수는 동적라이브러리 내에 저장되어 있는것으로
사용하는 방법은 아래와 같다.

EXEC SQL REGISTER CONNECT USING :epctx [RETURNING :host_context] ;

epctx는 external procedure context이다. 이것을 이용하여 OCIEnv, OCISvcCtx, OCIError등과
같은 OCI핸들을 사용할 수 있게 되는것이다.

8-1) 외부 프로시져의 제한 사항
- C++외부프로시져는 지원않한다.
- 외부프로시져 Context에 연결시 추가연결은 않된다.
- EXEC SQL ENABLE THREADS문을 사용할 수 없다.
- DDL문을 사용할 수 없다.
- EXEC SQL COMMIT / ROLLBACK문을 사용할 수 없다.
- EXEC SQL OBJECT문과같은 오브젝트문을 사용할 수 없다.
- EXEC SQL LOB문을 사용할 수 없다.
- EXEC TOOLS문을 사용할 수 없다.

8-2) 외부 프로시져 생성
생성하는 예제는 아래와 같다.

CREATE OR REPLACE PROCEDURE extp1
AS EXTERNAL NAME "extp1"
LIBRARY mylib
WITH CONTEXT
PARAMETERS(CONTEXT) ;

SQL*Plus에서는 아래와 같이 사용한다.

SQL>
BEGIN
INSERT INTO emp VALUES(9999,’JOHNSON’,’SALESMAN’,7782, sysdate, 1200,150,10);
extp1;
END;

아래의 내용은 extp1.pc파일의 리스트이다.

void extp1 (epctx)
OCIExtProcContext *epctx;
{
char name[15];
EXEC SQL REGISTER CONNECT USING :epctx;
EXEC SQL WHENEVER SQLERROR goto err;
EXEC SQL SELECT ename INTO :name FROM emp WHERE empno = 9999;
return;
err: SQLExtProcError(SQL_SINGLE_ RCTX,

sqlca.sqlerrm.sqlerrmc,sqlca.sqlerrm.sqlerrml);
return;
}

9) Dynamic SQL 사용 ( Using Dynamic SQL )
PL/SQL블록내에 호스트변수를 포함하고 있지 않다면, Dynamic SQL Method 1을
사용할 수 있고, 호스트 변수를 포함하고 있다면 Dynamic SQL Method 2를 사용
할 수 있다.

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 ;
...

5) 호스트 배열 ( Host Arrays )
PL/SQL블록에서 입력호스트배열과 지시자 배열을 넘길 수 있다.

아래의 예제에서, 함수 호출시에 배열을 사용하는 salary라고 이름 붙여진
배열을 넘긴다. 이 함수는 값들중에서 중간값을 취득하기위해서 mdidan이라고
이름붙여졌으며, 이 함수의 형식적인 파라미터는 num_tab이라고 이름 붙여진
PL/SQL테이블을 포함하고 있다. 그 호출 함수는 실제 파라미터 salary에
모든값을 형식적인 파라미터 num_tab의 행에 할당한다.

...

float salary[100];

/* populate the host array */

EXEC SQL EXECUTE
DECLARE
TYPE NumTabTyp IS TABLE OF REAL
INDEX BY BINARY_INTEGER;
median_salary REAL;
n BINARY_INTEGER;
...

FUNCTION median (num_tab NumTabTyp, n INTEGER)
RETURN REAL IS
BEGIN
-- compute median
END;
BEGIN
n := 100;
median_salary := median(:salary, n);
...

END;
END-EXEC;
...

테이블 7-1은 PL/SQL테이블에서 행을 값들과 호스트 배열의 요소들 사이에
가능한 변환을 보여주고 있다. 예를들어, LONG형의 호스트 배열은
VARCHAR2, LONG, RAW, 혹은 LONG RAW형의 PL/SQL 테이블과 호환되며,
CHAR형과는 호환되지 않는다.




5-1) ARRAYLEN문
입력호스트 변수를 PL/SQL block에 넘겨야만 한다고 가정해보자. 기본적으로,
호스트 배열을 사용할때, Proc*C/C++ 전처리기는 선언된 수치를 사용한다.
하지만 선언된 수치를 전부 사용할 필요가 없을경우, ARRAYLEN문을 사용해서 작은
배열 수치를 적용할 수 있다.
적용하는 문법은 다음과 같다 :

EXEC SQL ARRAYLEN host_array (dimension) [EXECUTE];
4바이트 정수 호스트 변수 (문자는 않됨)

아래의 예제는 bonus라고 이름붙여진 C 호스트배열의 기본 수치를 그대로 사용하기
위해서 ARRAYLEN문을 사용한 예제이다.

float bonus[100];
int dimension;

EXEC SQL ARRAYLEN bonus (dimension);

/* populate the host array */
...

dimension = 25; /* set smaller array dimension */
EXEC SQL EXECUTE
DECLARE
TYPE NumTabTyp IS TABLE OF REAL
INDEX BY BINARY_INTEGER;
median_bonus REAL;

FUNCTION median (num_tab NumTabTyp, n INTEGER)
RETURN REAL IS
BEGIN
-- compute median
END;

BEGIN
median_bonus := median(:bonus, :dimension);
...
END;
END-EXEC;

위의 예제에서 PL/SQL블록에 25 배열요소가 전달되었으며 결과적으로,PL/SQL블록에
오라클 실행을 위해서 보내졌을경우 더욱 적은 호스트 배열이 보내진것이며, 이것은
시간을 절약할 수 있고, 네트웍 부하또한 줄일 수 있다.

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

6) Scrollable Cursors
Scrollable Cursor는 오라클이 SQL문장을 실행하는 곳이며, 실행되는 동안 진행되는
정보를 저장하는 작업영역이다.

커서가 실행될때, 쿼리의 결과는 결과셋(result set)이라고 불리는 행들의 셋에 위치한다.
결과셋을 순차적으로 혹은 비순차적으로 결과를 취득할 수 있다. 비순서적인 결과셋을
Scrollable Cursors라고 부른다.

Scrollable Cursor는 사용자가 결과셋을 행을 앞으로,뒤로, 무작위적 방법으로 접근
할 수 있도록 돕는다.

6-1) Scrollable Cursors의 사용
아래의 문장들은 Scrollable Cursor를 조작하거나 선언하는것을 다룬다.

DECLARE SCROLL CURSOR : DECLARE <cursor name> SCROLL CURSOR문은 쿼리와 관련된
Scrollable Cursor를 명명하는데 사용한다.

OPEN : OPEN문은 일반 커서선언과 같은 방법으로 사용한다.

FETCH : FETCH문은 무작위적 방법으로 요구되는 행을 취득하는데 사용한다.
프로그램은 위아래 혹은 처음과 끝 혹은 무작위로 한행을 취득한다.

아래의 옵션은 FETCH문에 사용할 수 있는 옵션이다.

1. FETCH FIRST 결과셋으로부터 첫번째 행을 취득한다.
2. FETCH PRIOR 현재행으로부터 이전행을 취득한다.
3. FETCH NEXT 현재위치에서 다음행를 취득한다. 이것은 일반 FETCH와 같다.
4. FETCH LAST 결과셋으로부터 마지막 행을 취득한다.
5. FETCH CURRENT 현재행을 취득한다.
6. FETCH RELATIVE n 현재행으로부터 n번째 행을 취득한다. n은 OFFSET이다.
7. FETCH ABSOLUTE n 결과셋의 시작점으로부터 n OFFSET인 n번째행을 취득한다.

아래의 예제는 결과셋으로부터 마지막 레코드를 취득하는 예제이다.

EXEC SQL DECLARE emp_cursor SCROLL CURSOR FOR
SELECT ename, sal FROM emp WHERE deptno=20;
...

EXEC SQL OPEN emp_cursor;
EXEC SQL FETCH LAST emp_cursor INTO :emp_name, :sal;
EXEC SQL CLOSE emp_cursor;

CLOSE : CLOSE문은 일반 커서의 경우와 같은 방법으로 사용하면 된다.

6-2) CLOSE_ON_COMMIT 전처리기 옵션
CLOSE_ON_COMMIT 전처리기 옵션은 COMMIT이 실행될때 모든 커서를 닫을것인지 아닌지를
선택할 수 있는 능력을 제공하는 옵션이다. 만약 MODE = ANSI일때, CLOSE_ON_COMMIT이
기본적으로 YES를 갖는다. 명시적으로 CLOSE_ON_COMMIT=NO으로 설정하면 성능이 향상된다.
왜냐하면, 커서들은 COMMIT이 실행될때 닫히지 않을것이기 때문이다.

6-3) PREFETCH 전처리기 옵션
전처리기의 PREFETCH옵션은 주어진 행수를 미리가지고옮으로해서 더욱 효율적인 쿼리가
되도록한다. 이것은 서버접속수나 전체적인 메모리 사용을 줄일수 있다. PREFETCH옵션값
의 행수는 명시적인 커서를 포함한 모든 쿼리에서 사용한다.

인라인에서 사용될때, PREFETCH옵션은 이런한 커서문장보다 선행되어야 한다.

☞ EXEC SQL OPEN cursor
☞ EXEC SQL OPEN cursor USING host_var_list
☞ EXEC SQL OPEN cursor USING DESCRIPTOR desc_name

PREFTECH값은 0에서 65535로 설정할 수 있다. 기본값은 1이다.

7) Optimizer Hints
Pro*C/C++ 전처리기는 SQL문자에서 최적화 힌트(Optimizer hints)를 지원한다.
힌트는 다음을 위해서 사용할 수 있다.
☞ Optimization approach for a SQL statement
☞ 참조테이블을 위한 접근 경로지정 (Access path for each referenced table)
☞ 조인순서(Join order for a join)
☞ 조인방법적용(Method used to join tables)

힌트는 rule-based 최적화와 cost-based 최적화를 선택하게 한다.
아래의 예제는 작업처리를 최적화하기위한 힌트를 사용한 예제이다.

EXEC SQL SELECT /*+ ALL_ROWS (cost-based) */ empno, ename, sal, job
INTO :emp_rec FROM emp
WHERE deptno = :dept_number;

cost-based optimizer와 optimizer hints에대한 더 많은 정보를 얻기위해서는
Oracle9i Application Developer’s Guide - Fundamentals장을 참조한다.

8) CURRENT OF 절
주어지 커서로부터 취득한 테이타의 최근행을 참조해서 DELETE나 UPDATE문을 사용
할 경우 CURRENT OF 커서명 절을 사용할 수 있다.
아래에 사용예제를 보여주고 있다.

EXEC SQL DECLARE emp_cursor CURSOR FOR
SELECT ename, sal FROM emp WHERE job = ’CLERK’
FOR UPDATE OF sal;
...

EXEC SQL OPEN emp_cursor;
EXEC SQL WHENEVER NOT FOUND GOTO ...
for (;;) {
EXEC SQL FETCH emp_cursor INTO :emp_name, :salary;
...

EXEC SQL UPDATE emp SET sal = :new_salary
WHERE CURRENT OF emp_cursor;
}

8-1) 제한사항
CURRENT OF은 인덱스가 구성된 테이블(index-organized table)에서는 사용할 수 없다.
CURRENT OF은 호스트 배열과 함께사용할 수 없다.
CURRENT OF과 함께 테이블을 조인해서 사용 할 수 없다.
결론적으로, CURRENT OF은 Dynamic SQL과 함께 사용할 수 없다는것이다.

9) 커서문(Cursor Statements)
아래의 예제는 프로그램에서 커서제어문의 전형적인 절차를 보여주고 있다.

...

/* 커서 선언 */
EXEC SQL DECLARE emp_cursor CURSOR FOR
SELECT ename, job
FROM emp
WHERE empno = :emp_number
FOR UPDATE OF job;

/* 커서를 열고 active set을 확인 */
EXEC SQL OPEN emp_cursor;

/* 마지막행을 취득했다면 break */
EXEC SQL WHENEVER NOT FOUND DO break;

/* 반복문에서 데이터 취득 / 처리 */
for (;;)
{
EXEC SQL FETCH emp_cursor INTO :emp_name, :job_title;

EXEC SQL UPDATE emp
SET job = :new_job_title
WHERE CURRENT OF emp_cursor;
}
...

/* 커서를 닫음 */
EXEC SQL CLOSE emp_cursor;
EXEC SQL COMMIT WORK RELEASE;
...

10) Non-Scrollable Cursor를 사용한 예제
아래의 완전한 프로그램은 커서사용법과 FETCH문의 사용법을 보여주고 있다.
부서번호를 입력받고 부서의 모든 사원의 이름을 표시하는 프로그램이다.
FETCH한후의 에러처리부분에 대한 내용을 담고 있다.
FETCH가 실패하면 오라클에러코드는 sqlca.sqlcode에 "no data found"가 리턴된다.

#include <stdio.h>

/* 호스트변수 선언 */
char userid[12] = "SCOTT/TIGER";
char emp_name[10];
int emp_number;
int dept_number;
char temp[32];
void sql_error();

/* SQL Communications Area 포함*/
#include <sqlca.h>

////////////////////////////////////////////////////////////////////////////
// Main 함수
////////////////////////////////////////////////////////////////////////////
void main()
{
emp_number = 7499;
/* 에러처리 */
EXEC SQL WHENEVER SQLERROR do sql_error("Oracle error");

/* 오라클 연결 */
EXEC SQL CONNECT :userid;
printf("Connected.n");

/* 커서 선언 */
EXEC SQL DECLARE emp_cursor CURSOR FOR
SELECT ename
FROM emp
WHERE deptno = :dept_number;

printf("Department number? ");

gets(temp);

dept_number = atoi(temp);

/* 커서오픈 및 active set 확인 */
EXEC SQL OPEN emp_cursor;
printf("Employee Namen");
printf("-------------n");
/* 데이터가 없을때까지 데이터 취득 및 처리 */
EXEC SQL WHENEVER NOT FOUND DO break;
while (1)
{
EXEC SQL FETCH emp_cursor INTO :emp_name;
printf("%sn", emp_name);
}
EXEC SQL CLOSE emp_cursor;
EXEC SQL COMMIT WORK RELEASE;
exit(0);
}

////////////////////////////////////////////////////////////////////////////
// 에러처리 함수
////////////////////////////////////////////////////////////////////////////
void sql_error(msg)
char *msg;
{
char buf[500];
int buflen, msglen;
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL ROLLBACK WORK RELEASE;
buflen = sizeof (buf);
sqlglm(buf, &buflen, &msglen);
printf("%sn", msg);
printf("%*.sn", msglen, buf);
exit(1);
}

11) Scrollable Cursor를 사용한 완전한 예제
아래의 프로그램은 Scrollable Cursor사용법을 보여주고 있으며, FETCH문을 사용한
다양한 옵션을 사용하는 방법을 보여주고 있다.

#include <stdio.h>
/* 호스트변수 선언 */
char userid[12]="SCOTT/TIGER";
char emp_name[10];
void sql_error();
/* SQL Communications Area 포함*/
#include<sqlca.h>

/////////////////////////////////////////////////////////////////////////////
// 메인 함수
/////////////////////////////////////////////////////////////////////////////
void main()
{
/* 에러 처리 */
EXEC SQL WHENEVER SQLERROR do sql_error("Oracle error");
/* 오라클 연결 */
EXEC SQL CONNECT :userid;
printf("Connected.n");
/* scrollable cursor 선언 */
EXEC SQL DECLARE emp_cursor SCROLL CURSOR FOR
SELECT ename FROM emp;
/* 커서오픈 및 active set 확인 */
EXEC SQL OPEN emp_cursor;
/* 마지막행 취득 */
EXEC SQL FETCH LAST emp_cursor INTO :emp_name;
/* 5 행을 취득 */
EXEC SQL FETCH ABSOLUTE 5 emp_cursor INTO :emp_name;
/* 10 행의 데이터 취득*/
EXEC SQL FETCH RELATIVE 5 emp_cursor INTO :emp_name;
/* 7번행 취득 */
EXEC SQl FETCH RELATIVE -3 emp_cursor INTO :emp_name;
/* 첫번째 행 취득 */
EXEC SQL FETCH FIRST emp_cursor INTO :emp_name;

/* 2번행 취득 */
EXEC SQL FETCH my_cursor INTO :emp_name;
/* 3번행 취득 */
EXEC SQL FETCH NEXT my_cursor INTO :emp_name;
/* 3번행 취득*/
EXEC SQL FETCH CURRENT my_cursor INTO :emp_name;
/* 2번행 취득 */
EXEC SQL FETCH PRIOR my_cursor INTO :emp_name;
}

/////////////////////////////////////////////////////////////////////////////
// 에러처리 함수
/////////////////////////////////////////////////////////////////////////////
void sql_error(msg)
char *msg;
{
char buf[500];
int buflen , msglen;
EXEC SQL WHENEVER SQLERROR CONTINUE;
EXEC SQL ROLLBACK TRANSACTION;
buflen = sizeof (buf);
sqlglm(buf, &buflen, &mesglen);
printf("%sn",msg);
printf("%*.sn",msglen,buf);
exit(1);
}

11-1) Positioned Update
ROWID를 사용한 Update의 예제를 보여주고 있다.
자세한 사용법은 "Universal ROWIDs"를 참조한다.

#include <oci.h>
...

OCIRowid *urowid;
...

EXEC SQL ALLOCATE :urowid;
EXEC SQL DECLARE cur CURSOR FOR
SELECT rowid, ... FROM my_table FOR UPDATE OF ...;
EXEC SQL OPEN cur;
EXEC SQL FETCH cur INTO :urowid, ...;

/* Process data */
...

EXEC SQL UPDATE my_table SET ... WHERE CURRENT OF cur;
EXEC SQL CLOSE cur;
EXEC SQL FREE :urowid;

...