PL/SQL 엔진과 오라클
서버
PL/SQL은 오라클 소유권의 제품은 아니고, 오라클 서버와 오라클
툴에 도입된 기술입니다. PL/SQL의 블록이 PL/SQL 엔진에 넘겨지고 처리됩니다. 사용될 엔진은 PL/SQL 블록이 어디에서 불리어지는가에
따라 다릅니다.
PL/SQL 블록을 Pro* program, 사용자-exit, SQL*Plus, 서버 관리자 등으로부터 보내게 되면
오라클 서버에 있는 PL/SQL 엔진이 처리를 합니다.그리고 나서 그 엔진은 블록내의 SQL을 각각의 문장으로 분리시켜 SQL 문장 실행기로
넘깁니다. 즉 응용프로그램에서 오라클 서버로 블록을 송신하기 위해서 단일 전송만이 필요하다는 의미이며, 이렇게 함으로써 특히 클라이언트-서버 네트워크에서 성능을 향상
시킬 수 있습니다. 내장된 서브프로그램도 데이터베이스에 연결된 응용프로그램들에 의해 참조가
되어집니다.
성능
향상
PL/SQL은 어플리케이션의 성능을 향상시킬 수 있습니다. 장점은
실행 환경에 따라 차이가 있습니다.
주: Developer 어플리케이션의 일부로써 선언된 프로시저와 함수는 데이터베이스에 저장된 프로시저 함수와 다르지만 그 일반적인 구조는 동일합니다. 내장된 서브 프로그램은 데이터베이스 객체이며 데이터 사전에 저장됩니다. 그것들은 Developer 어플리케이션을 비롯한 많은 어플리게이션에 의해 액세스 될 수 있습니다.
PL/SQL 블록 구조
PL/SQL은 프로그램을 논리적인 블록으로 나누게 하는 구조화된 블록 언어입니다. PL/SQL 블록은 3
섹션까지로 구성됩니다: 선언 (선택적), 실행 (필수적), 예외처리 (선택적). BEGIN과 END 키워드만 필수적입니다. 변수들을
사용하는 블록에 대해 논리적으로 선언할 수 있습니다. 오류조건 (예외로 알려진) 은 블록 내에서 특별하게 처리될 수 있습니다.
변수들과 그 외의 식별자를 참조하고 선언함으로써 PL/SQL 블록 내에서 값을 저장하고 변경할 수 있습니다.
아래 테이블은 3가지 블록 섹션을 기술합니다.
|
섹 션 |
설 명 |
포 함 |
|
선 언 |
실행과 선언 섹션에서 참조되는 모든 변수, 상수,커서, 사용자 정의 예외를 포함합니다. |
선택적 |
|
실 행 |
데이터베이스 내의 데이터를 조작하기 위한 SQL 명령문과, 블록에서 데이터를 조작하기 위한 PL/SQL 명령문을 포함합니다. |
필수적 |
|
예외처리 |
실행 섹션에서 오류 또는 비정상적인 조건이 발생할 때 수행하기 위한 작업을 지정합니다. |
선택적 |
|
|
PL/SQL procedure successfully completed |
PL/SQL의 모든 단위는 하나 또는 그 이상의 블록을 포함합니다.이 블록은 다른 것으로 부터 하나로 완전히 분리되거나 중첩될 수 있습니다.
기본 단위(프로시저와 함수, 또는 서브프로그램, 그리고 익명의 블록)는 임의의 수의 중첩된 서브블록을 포함할 수 있는, 논리적 블록인
PL/SQL 프로그램을 구성합니다. 그러므로 한 블록은 다른 블록의 작은 부분이 되기도 하고 또는 코드 단위의 전체 중 일부가 될 수도
있습니다. PL/SQL의 2가지 형태인, 익명 블록과 서브프로그램 중 익명의 블록만 본 과정에서 설명합니다.
익명
블록
익명 블록은 이름이 없는 블록입니다. 그것은 실행되기 위한 어플리 케이션 안에서 선언되고 실행시간
중에 실행을 위해 PL/SQL 엔진으로 전달됩니다. 선행컴파일러 프로그램과 SQL*Plus또는 서버 관리자에서 익명의 블록을 내장할 수
있습니다.
서브프로그램
서브프로그램은 매개변수를 취할
수 있고 호출할 수 있는 이름있는 PL/SQL 블록입니다.
프로시저 또는 함수로 선언될 수 있습니다. 일반적으로 어떤 작업을
수행하기 위해 프로시저를 사용하고, 값을 계산하기 위해 함수를 사용합니다. 서버 또는 어플리케이션 수준에서 서브프로그램을 저장할 수
있습니다. Developer/2000 컴포넌트(Forms, Reports, Graphics)를 사용하여 어플리케이션(Forms또는
Reports)의 일부로서 함수와 프로시저를 선언할 수 있고 필요할 때마다 동일 어플리케이션의 트리거 (다음페이지를 보십시오.),
함수, 다른 프로시저에서 그것들을 호출할 수 있습니다.
주: 함수는 값을 반환해야 함을 제외하고는 프로시저와
동일합니다. 프로시저와 함수는 다음 PL/SQL 과정에서 설명합니다.
.
아래 테이블은 기본 PL/SQL 블록을 사용하는 다양한 다른 PL/SQL 프로그램 구성을 약술하고
있습니다.
|
프로그램 구성 |
설 명 |
사용 환경 |
|
익명 블록 |
어플리케이션에 내장되거나 대화식으로 호출됩니다. |
모든 PL/SQL 환경 |
|
내장된 프로시저 또는 함수 |
매개변수를 받아들일 수 있고 이름을 이용하여 반복적으로 호출할 수 있는 이름이 있는 PL/SQL
블록은 오라클 서버 내에서 저장됩니다. |
오라클 서버 |
|
어플리케이션 프로시저 또는 함수 |
매개변수를 받아들일 수 있고 이름을 이용하여 반복적으로 호출할 수 있는 이름이 있는 PL/SQL 블록은 Developer/2000 어플리케이션에 저장되거나 공유 라이브러리에 저장됩니다 |
Developer/2000 컴포넌트- 예를
들면,Forms |
|
패키지 |
관련된 프로시저, 함수들을 묶어 이름 붙인 PL/SQL 모듈입니다.
|
Oracle Server 와 Developer/2000 컴포넌트? 예를 들면,
Forms |
|
데이터베이스 트리거 |
데이터베이스 테이블과 관련되고 DML명령문 에 의해 트리거될 때 자동적으로 실행됩니다.
|
Oracle Server |
|
어플리케이션 트리거 |
PL/SQL 블록은 어플리케이션 이벤트와 관련되고 자동적으로 실행됩니다. |
Developer/2000 컴포넌트- 예를 들면, Forms |
************** 부서/직원 리포트 ******************
부서 : 부서번호 부서명
위치
10 ACCOUNTING NEW YORK
사원 : 사번 이름 급여
급여 등급
7871
Anderson 2000 ***
7890 Andrew 700
*
7870 Scott 2400 ****
7866
David 900 *
7891 Sylvia 3200
*****
2명의 급여가 인상되었습니다.
위와 같은 리포트를
뽑아내고 $1000보다 적은 급여를 받는 직원의 급여를 10%인상하는 프로그램을 작성하여 봅니다.(PL/SQL 변수 설명 중 v_...를
v$...로 설명을 하였으나, 실제로는 v_...가 맞습니다.)
ACCEPT p_deptno PROMPT 'Please enter the department number : '
DECLARE
v_deptno
NUMBER(2);
v_dname CHAR(14);
v_loc
CHAR(13);
v_empno emp.empno%TYPE;
v_ename
emp.ename%TYPE;
v_sal emp.sal%TYPE;
v_grade
salgrade.grade%TYPE;
v_dno emp.deptno%TYPE :=
&p_deptno;
CURSOR emp_cursor IS
SELECLT empno,
ename, sal
FROM emp
WHERE deptno =
&p_deptno;
e_no_emp EXCEPTION;
BEGIN
-- print out the
report title
DBMS_OUTPUT.PUT_LINE('*********부서/직원
리포트*********');
SELECT deptno, dname, loc
INTO v_deptno,
v_dname, v_loc
FROM dept
WHERE deptno =
v_dno;
DBMS_OUTPUT.PUT_LINE('부서 : 부서번호 부서명
위치');
DBMS_OUTPUT.PUT_LINE(' '∥v_deptno∥'
'∥v_dname∥
' '∥v_loc∥'
');
DBMS_OUTPUT.PUT_LINE('사원 : 사번 이름 급여
급여등급');
OPEN emp_cursor;
FETCH emp_cursor INTO v_empno,
v_ename, v_sal;
/* according to the salary amount,
print
out the stars */
WHILE emp_cursor%FOUND LOOP
SELECT
grade
INTO v_grade
FROM
salgrade
WHERE v_sal > losal AND v_sal
<hisal;
DBMS_OUTPUT.PUT(' '∥v_empno∥'
'∥v_ename∥
' '∥v_sal∥' ');
FOR
I in 1..v_grade LOOP
DBMS_OUTPUT.PUT('*');
END
LOOP;
DBMS_OUTPUT.PUT_LINE('');
FETCH emp_cursor INTO
v_empno, v_ename, v_sal;
END LOOP;
CLOSE
emp_cursor;
UPDATE emp
SET sal = sal *
1.1
WHERE sal < 1000;
IF SQL%ROWCOUNT = 0
THEN
RAISE
e_no_emp;
ELSE
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT ∥'명의 급여가
' ||
'인상되었습니다.');
END
IF;
COMMIT;
EXCEPTION
WHEN no_data_found
THEN
DBMS_OUTPUT.PUT_LINE('There is no matching
data.');
WHEN too_many_rows
THEN
DBMS_OUTPUT.PUT_LINE('More than one matching data.
');
WHEN e_no_emp THEN
DBMS_OUTPUT.PUT_LINE('No employee
with a smaller ' ||
'than
$1000.');
--in case of any other exception
WHEN others
THEN
DBMS_OUTPUT.PUT_LINE('Some other error
occurred.');
END;