�ݺ�ߣ

PL/SQL
(Procedural Language / Structured Query Language)
10g Release 1

목차목차
 Overview of PL/SQLOverview of PL/SQL
 Fundamentals of the PL/SQL LanguageFundamentals of the PL/SQL Language
 PL/SQL DatatypesPL/SQL Datatypes
 Using PL/SQL Control StructureUsing PL/SQL Control Structure
 Using PL/SQL Collections and RecordsUsing PL/SQL Collections and Records
 Performing SQL Operations from PL/SQLPerforming SQL Operations from PL/SQL
 Using PL/SQL SubprogramsUsing PL/SQL Subprograms
 Using PL/SQL PackageUsing PL/SQL Package
 Handling PL/SQL ErrorsHandling PL/SQL Errors
 ThoughtThought

3
Overview of PL/SQL
 Advantage of PL/SQL
 PL/SQL 은 고성능 Transaction Processing 언어로서 다음과 같은 장점을 제공
하고 있습니다 .
– SQL 을 지원합니다 . (Support for SQL)
– 객체지향 프로그래밍을 지원합니다 . (Support for object-oriented programming)
– SQL 만을 사용시보다 더 나은 성능을 제공합니다 . (Better performance)
– 높은 생산성을 제공합니다 . (Higher productivity)
– 어디에서나 사용이 가능합니다 . (Full portability)
– Oracle 과의 융화도가 높습니다 . (Tight integration with Oracle)
– 보안성이 높습니다 . (Tight security)
• Tight Integration with Oracle
– PL/SQL 은 SQL 과의 융화가 잘 되어지는 언어입니다 . SQL 과 PL/SQL 사이에서는 datatype
을 변환 시켜줄 필요가 없습니다 .
– 일일이 Table Columns 과 Rows 의 datatype 을 알고 있을 필요가 없습니다 .
• Support for SQL
– SQL 이 Database 언어의 표준이 될 수 있었던 것은 SQL 이 유연하고 , 강력하고 , 배우기 쉽
기 때문입니다 . PL/SQL 은 이러한 SQL 을 이용하여 Database 안의 data 들을 다룰 수 있습니
다 .
– SQL 을 이용한 data 조작 , cursor 제어 , transaction 제어 명령 , 이뿐만 아니라 SQL 함
수 , 연산자 , pseudocolumns( 가짜컬럼 ) 을 사용할 수 있습니다 .

4
Overview of PL/SQL
• Better Performance
– Oracle 은 한번에 하나의 SQL 문장을 실행할 수 있습니다 . 하지만 , PL/SQL 을 사용하게 되
면 이러한 SQL 문장들을 연결 Block 으로 만들어 한번에 Oracle 에게 보낼 수 있습니다 .
( 그림 1)
– Stored Procedure 을 사용하게 되면 , Network Traffic 량을 줄일 수 있다 .
– 한번 compile 된 Stored Procedure 는 re-compile 이 필요치 않아 Procedure 사용시 빠른
결과값을 얻을 수 있다 .
그림 1. PL/SQL Boosts Performance

5
Overview of PL/SQL
• Full Portability
– Application 에서 작성된 PL/SQL 은 Oracle Database 가 구동되어지는 어떠한 운영체제나 플
랫폼에서도 사용이 가능합니다 .
 PL/SQL Architecture
Oralce 선행 Compiler 에서 PL/SQL Block 을 제출하면 Oracle Server 내의 PL/SQL 엔진이 이를 처리합니다 .
PL/SQL 엔진은 Block 내의 SQL 과 procedural 을 분리 하여 SQL 을 Oracle Server 내의 SQL Statement
Executor 에 보내고 , procedural 을 PL/SQL 엔진내의 Procedural Statement Executor 에게 보냅니다 . 그 후
PL/SQL 엔진은 SQL Statement Executor 로부터 처리되어진 SQL 결과값을 받아서 최종결과를 보여줍니다 .
그림 2. PL/SQL 처리과정

6
Fundamentals of the PL/SQL Language
 Character Set
 PL/SQL 에서는 다음과 같은 문자 및 특수문자 들을 사용할 수 있습니다 .
– A .. Z 와 a .. z
– 숫자 0 .. 9
– 심볼 : ( ) + _ * / < > = ! ~ ^ ; : . ’ @ % , ” # $ & _ | { } ? [ ]
– Tabs, Spaces, Carriage returns
• PL/SQL 에서는 대소문자를 구분하지 않습니다 .
 Lexical Units
 Delimiters
Symbol Meaning Symbol Meaning Symbol Meaning
+ addition operator * multiplication operator => association operator
% attribute indicator “ quoted identifier delimiter || concatenation operator
‘ character string delimiter = relational operator ** exponentiation operator
. component selector < relational operator << label delimiter (begin)
/ division operator > relational operator >> label delimiter (end)
( expression or list delimiter @ remote access indicator <> relational operator
) expression or list delimiter ; statement terminator != relational operator
: host variable indicator - subtraction/negation operator ~= relational operator
, item separator := assignment operator ^= relational operator

7
 Identifiers
• PL/SQL 내의 constants, exceptions, cursors, subprograms, package 또는 변수명
, 커서명으로 식별할 수 있고 사용 가능한 형태입니다 .
– phone2
– credit_limit
– LastName
– oracle$number
• 식별할 수 없고 사용이 불가능한 형태입니다 .
– mine&yours -- ‘&’ 기호 허용하지 않음
– debit-amount -- ‘-’ 기호 허용하지 않음
– on/off -- ‘/’ 기호 허용하지 않음
– user id -- 공백 허용하지 않음
• 다음은 같은 의미 입니다 . ( 대소문자를 구분하지 않음 )
– lastname
– LastName
– LASTNAME
• 최대 30 자 이상을 넘지 않습니다 .

8
 Literals
• 숫자
– 030, 6, -14, 0, +32767
– 3.14, 0.0, -12.0, +8300.00, .5, 25.
– 2E5, 1.0E-7, 3.14159e0, -1E38, -9.5e-3
• 문자
– ‘Z’, ‘&’, ‘7’, ‘ ‘, ‘z’, ‘(‘
• 문자열
– ‘Hello, world!’
– ’10-NOV-91’
– ‘ 그는 “정말 좋은 날씨입니다 .!” 라고 말했습니다 .’
– ‘$1,000,000’
• Boolean
– TRUE, FALSE, NULL
• 시간
– d1 DATE := DATE ‘2009-01-01’;
– t1 TIMESTAMP := TIMESTAMP ‘1983-09-18 00:30:01’;

9
 Comments
• 한줄 주석
– v_date DATE := DATE ‘2009-01-01’; -- 날짜를 담기 위한 변수 선언
• 여러줄 주석
– /* v_date 는 날짜를 담기 위한 변수
v_time 는 시간을 담기 위한 변수 */
v_date DATE := DATE ‘2009-01-01’;
v_time TIMESTAMP := TIMESTAMP ‘1983-09-18 00:30:01’;
 Declarations
• 다음의 형태로 변수 선언이 가능합니다 .
– DECLARE
birthday DATE; -- 변수만을 선언
age INTEGER := 27; -- 변수 선언과 동시에 초기값 입력
name VARCHAR2(10) DEFAULT ‘ 홍길동’ ; -- 기본값 = ‘ 홍길동’
id VARCHAR2(10) NOT NULL; -- NULL 허용 안 함
today birthday%TYPE; -- birthday 와 같은 데이터 형이 적용됨
Jane info%ROWTYPE; -- info 테이블의 ROW 를 대입함
email CONSTANT VARCHAR2(30); -- 상수 선언

10
 Structures
• 선언부 (declaractive part)
– 실행부에서 사용할 변수나 상수를 선언하는 부분입니다 .
– 선언부는 ‘ DECLARE’ 를 사용해서 나타냅니다 .
• 실행부 (executable part)
– 실제 처리할 로직을 담당하는 부분입니다 .
– 실행부는 ‘ BEGIN’ 으로 시작되어 ‘ END’ 로 끝나게 됩니다 .
• 예외처리부 (exception-building part)
– 실행부에서 로직을 처리하던 중 발생할 수 있는 각종 오류들에 대해 처리를 하는 부분입니다
.
– 예외처리부는 ‘ EXCEPTION’ 을 사용해서 나타냅니다 .
– 예외처리부는 생략이 가능합니다 .
DECLARE -- 선언부
counter INTEGER;
BEGIN -- 실행부 시작
counter := counter + 1;
IF counter IS NULL THEN
dbms_output.put_line(‘Result : COUNTER IS Null’);
END IF;
EXCEPTION WHEN OTHERS THEN -- 예외처리부 ( 생략가능 )
dbms_output.put_line(‘ERRORS’);
END; -- 실행부 끝남

11
 Scope and Visibility of PL/SQL Identifiers
다음 그림 3 은 변수가 선언된 위치에 따라서 유효한 범위와 변수의 영향이 미칠 수 있는 범위를 나타냅니다 .
(Outer x = 전역변수 , Inner x = 지역변수 )
그림 3. Scope and Visibility

12
PL/SQL Datatypes
 Overview of Predefined PL/SQL Datatypes
• Scalar Type 은 Number 또는 Character String 등의 것을 의미하는데 , 하나의 값만을 가질 수 있
으며 , 내부 구성요소를 갖고 있지 않습니다 .
• Composite Type 은 배열의 요소들 (elements of an array) 등의 것을 의미하는데 , 각각을 개별적으
로 다룰 수 있으며 , 내부 구성요소를 갖고 있습니다 .
• Reference Type 은 Pointer 라고도 불리우는 값들 즉 , 다른 프로그램의 Item(Values) 들을 가리키
는 주소값을 가지고 있습니다 .
• LOB(Large Obejct) Type 은 대용량 데이터를 저장하며 , Long 타입과는 달리 쉬운 검색이 가능합니
다 .
그림 4. Built-in Datatypes

13
PL/SQL Datatypes
 Converting PL/SQL Datatypes
그림 5 는 datatype 의 묵시적 변환이 가능한 경우를 나타냅니다 .
그림 5. Implicit Conversion

14
Using PL/SQL Control Structures
 Overview of PL/SQL Control Structures
다음 그림 6 은 절차적 컴퓨터 프로그램에서 사용하는 기본제어구조를 보여줍니다 .
– Selection 은 IF 와 CASE 문장이 이에 해당합니다 .
– Iteration 은 LOOP 와 EXIT 문장이 이에 해당합니다 .
– Sequence 은 GOTO 와 NULL 문장이 이에 해당합니다 .
그림 6. Control Structures

15
 IF and CASE Statements
 Using the IF-THEN Statement
IF condition THEN
sequence_of _statements
END IF;
 Using the IF-THEN-ELSE Statement
IF condition THEN
sequence_of _statements1
ELSE
END IF;
 Using the IF-THEN-ELSIF Statement
IF condition1 THEN
ELSIF condition2 THEN
ELSE
END IF;

16
 Using the CASE Statement
CASE selector
WHEN expression1 THEN sequence_of_statements1;
WHEN expression2 THEN sequence_of_statements2;
…
WHEN expressionN THEN sequence_of_statementsN;
[ELSE sequence_of_statementsN+1;]
END CASE;

17
 LOOP and EXIT Statements
 Using the LOOP Statement
LOOP
squence_of_statements
END LOOP;
 Using the EXIT Statement
LOOP
IF credit_rating < 3 THEN
EXIT; -- exit loop immediately
END IF;
END LOOP; -- control resume here;
 Using the EXIT-WHEN Statement
LOOP
FETCH c1 INTO …
EXIT WHEN c1%NOTFOUND; -- exit loop if condition is true
…
END LOOP;
CLOSE c1;

18
 Using the WHILE-LOOP Statement
WHILE condition LOOP
sequence_of_statements
END LOOP;
 Using the FOR-LOOP Statement
FOR counter IN [REVERSE] lower_bound..higher_bound LOOP
squence_of_statements
END LOOP;

19
 GOTO and NULL Statements
 Using the GOTO Statement
BEGIN
…
GOTO insert_row;
…
<<insert_row>>
INSERT INTO emp VALUES …
END;
 Using the NULL Statement
EXCEPTION
WHEN ZERO_DIVIDE THEN
ROLLBACK;
WHEN VALUE_ERROR THEN
INSERT INTO errors VALUES …
COMMIT;
WHEN OTHERS THEN
NULL;
END

20
Using PL/SQL Collections and Records
 What is a Collection?
PL/SQL 에서는 다음 3 가지의 type 의 Collection( 집합 ) 을 제공합니다 .
• Associative arrays (Index-by tables) - 연관배열
• Nested tables – 중첩 테이블
• Variable arrays (Varrays) - 고정배열
 Understanding Nested Tables
Nested table( 중첩 테이블 ) 은 Varray 와 흡사한 구조를 가집니다 . 하지만 두 가지 점에서 Varray 와
는 차이가 있는데 , 첫째 , 중첩 테이블은 선언 시에 전체 크기를 명시할 필요가 없습니다 . 둘째 ,
Varray 의 경우 요소들에 데이터를 참조할 때 각각의 요소들 순서대로 참조해야 하는데 , 중첩 테이블의
경우 순서를 지킬 필요가 없습니다 .
다음 그림 7 은 Array 와 Nested Table 와의 차이점을 나타냅니다 .
그림 7. Array versus Nested Table

21
 Understanding Varrays
Varray( 고정배열 ) 는 Variable array 의 약자로 고정 길이 (fixed number) 를 가진 배열을 의미합니다 .
고정길이를 갖고 있다는 것이 Varray 선언 시 배열의 전체 크기를 명시해야 함을 의미합니다 . 또한
Varray 는 테이블 내에 저장될 수 있는데 , 이 말의 의미는 테이블의 하나의 컬럼 타입으로 Varray 가
사용될 수 있음을 의미합니다 . 프로그래밍 언어에서 사용하는 배열과 같다고 보시면 됩니다 . 다음 그림
8 은 Varray 를 나타냅니다 .
 Understanding Associative arrays
Associative array( 연관배열 ) 는 Index-by table 이라고도 하며 키와 값의 쌍으로 구성된 Collection
으로 , 하나의 키는 하나의 값과 연관되어 있습니다 . JAVA 나 C# 과 같은 3G 언어에서 사용하는 Hash
Table 과 동일한 개념입니다 . Varray 가 요소의 인덱스를 통해 각 요소의 값에 접근하는 반면 ,
Associative array 는 키에 의해 값에 접근합니다 .
그림 8. Varray of Size 10

22
 What is a PL/SQL Record?
Collection 이 배열 형태의 data type 이라면 , Record( 레코드 ) 는 테이블 형태의 data type 입니다 . 테이블
의 column 처럼 여러 개의 필드로 구성되어 있고 , PL/SQL 블록에서 임시로 사용할 수 있는 data type 의 하나
라고 할 수 있습니다 . Collection 에 해당하는 Varray, Nested table, Associative array 는 모두 프로그래
밍 언어에서 사용하는 배열 형태의 구조를 가집니다 . 즉 이들을 구성하는 요소들의 데이터 타입은 모두 같아
야 합니다 . 반면 테이블의 column 들이 서로 다른 유형의 data type 으로 구성되듯이 Record 역시 해당
field(Record 에서는 element 란 말 대신 field 란 용어를 사용 ) 들이 각기 다른 data type 을 가질 수 있습
니다 . 프로그래밍 언어와 비교하면 Record 는 Structure( 구조체 ) 에 해당한다고 볼 수 있습니다 .
Record 가 테이블 형태의 구조를 가지고 있으므로 , 실제 대부분의 경우 테이블의 데이터를 읽어오거나 조작하
기 위해 PL/SQL 블랙 내에서 임시적인 data 저장소 역할을 수행합니다 . Record 역시 Collection 처럼
TYPE 을 사용하여 선언할 수 있습니다 .
– [ TYPE 을 사용한 구문형식 ]
TYPE 레코드이름 IS RECORD ( 필드 1 데이터타입 1, 필드 2 데이터타입 2, …);
– [ 테이블명을 사용한 구문형식 ]
레코드이름 테이블명 %ROWTYPE;
– [ 커서명을 사용한 구문형식 ]
레코드이름 커서명 %ROWTYPE;

23
Performing SQL Operations from PL/SQL
 Overview of SQL Support in PL/SQL
SQL 를 확장해서 , PL/SQL 는 힘과 사용 용이의 유일한 조합을 제안합니다 . PL/SQL 은 SQL 의 모든 data 조작
, transaction 제어 , 함수 , Pseudocolumns 그리고 제어자 등 모든 것을 사용할 수 있게 지원함으로써
Oracle 의 data 를 안전하고 유연하게 다룰 수 있게 합니다 . 또한 PL/SQL 은 session 제어 , data 제어 ,
SQL data 정의 실행 등 dynamic SQL 을 지원합니다 . 그리고 PL/SQL 은 현재의 ANSI/ISO SQL 기준을 따릅니
다 .
 Data Manipulation
– PL/SQL 은 Oracle data 를 조작할 수 있는 INSERT, UPDATE, DELETE, SELECT 그리고 LOCK
TABLE 명령어 등을 지원합니다 .
 Transaction Control
– Oracle 은 Transaction 지향입니다 . 이것은 Oracle 이 데이터 무결성을 지향하기 위해
Transaction 을 이용한다는 것을 의미합니다 .
 SQL Functions
– SQL 에서 호출하여 사용하는 COUNT 등의 함수를 지원합니다 .
 SQL Pseudocolumns
– PL/SQL 은 SQL Pseudocolumn 을 인식합니다 . : CURRVAL, LEVEL, NEXTVAL, ROWID 그리고
ROWNUM 등 .

24
 SQL Operators
– PL/SQL 은 SQL 의 모든 연산자들 ( 비교 연산자 , Row 연산자 등 ) 을 사용 가능하게 지원 합
니다
 Performing DML Operation from PL/SQL (INSERT, UPDATE, DELETE)
– 특별한 사용법 ( 기호 ) 없이 INSERT, UPDATE, DELETE 를 PL/SQL 에서 사용할 수 있습니다 .
– SQL%ROWCOUNT 를 사용하여 얼마나 많은 ROW 가 영향을 받는 등을 알 수 있습니다 .
 What is a Cursor?
SELECT 문장을 실행하면 조건에 따른 결과가 추출됩니다 . 추출되는 결과는 한 건이 될 수 도 있고 여러
건이 될 수도 있으므로 이를 ResultSet 혹은 결과집합이라고 부르기도 합니다 . 쿼리에 의해 반환되는
결과는 메모리 상에 위치하게 되는데 PL/SQL 에서는 바로 커서 (cursor) 를 사용하여 이 결과집합에 접근
할 수 있습니다 . 즉 커서를 사용하면 결과집합의 각 개별 데이터에 접근이 가능합니다 . 커서의 종류에
는 Implicit Cursor( 묵시적 커서 ) 와 Explicit Cursor( 명시적 커서 ) 가 있습니다 .
 Overview of Implicit Cursor
Implicit Cursor( 묵시적 커서 ) 란 Oracle 내부에서 각각의 쿼리 결과에 접근하여 사용하기 위한 내부
적 커서라 할 수 있습니다 . 묵시적 커서는 모든 쿼리가 실행될 때마다 오픈 됩니다 . Oracle 내부에서
접근하고 사용되는 커서이므로 선언 , 오픈 등의 작업을 할 필요가 없으며 , 명시적 커서에서 설명한 커
서 속성들을 묵시적 커서에서도 동일하게 사용할 수 있습니다 .

25
 Overview of Implicit Cursor Attributes( 묵시적 커서 속성들 )
• %FOUND : 가장 최근에 인출한 행이 있으면 TRUE
• %ISOPEN : 커서가 열려있으면 TRUE
• %NOTFOUND : 가장 최근에 인출한 행이 없으면 TRUE
• %ROWCOUNT : 가장 최근에 인출한 행의 개수
 Overview of Explicit Cursor
Explicit Cursor( 명시적 커서 ) 란 사용자가 직접 쿼리의 결과에 접근해서 이를 사용하기 위해 명시적으
로 선언한 커서를 말합니다 . 일반적으로 커서를 사용한다고 하면 명시적 커서를 사용하는 것입니다 . 다
음은 명시적 커서를 사용하기 위한 절차 입니다 .
– 커서 선언 : 커서에 이름을 주고 , 이 커서가 접근하려는 쿼리를 정의합니다 .
[ CURSOR 커서명 IS SELECT 문장 ; ]
– 커서 열기 (Open) : 커서로 정의된 쿼리를 실행하는 역할을 합니다 .
[ OPEN 커서명 ; ]
– 패치 (Fetch) : 쿼리의 결과에 접근합니다 . 보통 쿼리는 한 개 이상의 결과를 반환하므로
Loop 를 돌며 개별 값들에 접근해서 임의의 처리를 하게 됩니다 .
[ FETCH 커서명 INTO 변수… ; ]
– 커서 닫기 (Close) : 패치 작업이 끝나면 커서 처리를 끝내고 메모리상에 존재하는 쿼리의 결
과를 소멸시킵니다 .
[ CLOSE 커서명 ; ]

26
 Overview of Transaction Processing in PL/SQL
 Using COMMIT, ROLLBACK, and SAVEPOINT in PL/SQL
• COMMIT
BEGIN
UPDATE accts SET bal = my_bal - debit
WHERE acctno = 7715;
UPDATE accts SET bal = my_bal + credit
WHERE acctno = 7720;
COMMIT WORK;
END;
• ROLLBACK
DECLARE
emp_id INTEGER;
BEGIN
SELECT empno, … INTO emp_id, … FROM new_emp WHERE …
INSERT INTO emp VALUES (emp_id, …);
INSERT INTO tax VALUES (emp_id, …);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
ROLLBACK;
END;

27
• SAVEPOINT
DECLARE
emp_id emp.empno.%TYPE;
BEGIN
UPDATE emp SET … WHERE empno = emp_id;
DELETE FROM emp WHERE …
SAVEPOINT do_insert;
INSERT INTO emp VALUES (emp_id, …);
EXCEPTION
ROLLBACK TO do_insert;
END;

28
Using PL/SQL Subprograms
 What Are Subprograms?
PL/SQL Subprogram 은 parameter 와 고유의 이름을 가진 PL/SQL Block 을 말하며 , Database 객체로 존재합니
다 . 즉 필요할 때마다 호출해서 사용할 수 있다는 의미 입니다 . 이러한 Subprogram 에는 Stored
Procedure( 내장 프로시저 ) 와 Function( 함수 ) 가 있습니다 .
Stored Procedure 는 그냥 ‘프로시저’라고도 부르며 Database 상에서 사용자가 지정한 임의의 처리를 수행하는
블록을 말합니다 . Function( 함수 ) 또는 Stored Function 은 SQL 함수와 같은 종류로 , 사용자가 직접 정의
해서 작성한 함수를 말합니다 . 보통 PL/SQL 에서 함수라 하면 내장함수를 말하며 이 또한 User Defined
Function( 사용자 정의 함수 ) 혹은 그냥 ‘함수’라고도 불립니다 .
 Understanding PL/SQL Functions
SQL 에서 Function( 함수 ) 하 하면 보통 Oracle 에서 제공하는 SQL 함수들을 말하지만 , PL/SQL 에서 함수라
하면 User Defined Function( 사용자 정의 함수 ) 를 말합니다 . 그렇다고 SQL 함수와 사용자 정의 함수의 속
성이나 기능이 다른 것은 아니며 , 다만 Oracle 에서 제공하는지 아니면 사용자가 직접 작성하는지에 따라 구
분됩니다 .
함수는 SQL 함수처럼 일정한 연산을 수행한 뒤 값을 반환합니다 . 함수는 Specification( 명세부 ) 와
Body( 구현부 ) 로 나눌 수 있으며 , 구현부는 Anonymous Block 처럼 선언 , 실행 , 예외처리부로 구성 됩니다
.
[ 구문형식 ]
CREATE OR REPLACE FUNCTION 함수명 ( 파라미터 1 데이터타입 , 파라미터 2 데이터타입 , …)
RETURN 데이터타입 IS [AS]
변수선언… ;
BEGIN
처리내용… ;
RETURN 리턴값 ;
END;

29
Using PL/SQL Subprograms
• CREATE OR REPLACE FUNCTION : VIEW 를 생성할 때와 같이 CREATE OR REPLACE 를 사용해서 기존
에 작성된 함수를 수정할 경우 , 별도로 DROP 시키지 않아도 됩니다 .
• 함수명 : 생성할 함수명을 명시합니다 .
• 파라미터 1 데이터타입 , … : 함수의 파라미터로 오는 파라미터 이름과 데이터 타입을 명시합니다 .
• RETURN : 반환할 데이터 타입을 명시합니다 .
• BEGIN … END : 함수가 처리할 내용을 명시하며 , 맨 마지막에 함수가 반환할 값을 RETURN 다음에
명시합니다 .
 Understanding PL/SQL Procedures
Procedure( 프로시저 ) 는 특정한 처리를 수행하는 PL/SQL Subprogram 입니다 . 함수와 마찬가지로 프로시저
는 Database 에 저장되어 있는 객체이며 , 이러한 이유로 Stored Procedure( 내장 프로시저 ) 라고도 부릅니다
. 프로시저 역시 파라미터들을 받아 특정 처리를 수행하기는 하지만 , 함수와는 달리 값을 반환하지 않습니다
.
[ 구문형식 ]
CREATE OR REPLACE PROCEDURE 프로시저명 ( 파라미터 1 데이터타입 [IN | OUT | INOUT], …)
IS [AS]
변수선언부… ;
BEGIN
프로시저 본문처리… ;
END;
[ 실행구문형식 ]
EXEC 혹은 EXECUTE 프로시저명 ( 파라미터… );

30
Using PL/SQL Packages
 What Is a PL/SQL Package?
Function( 함수 ) 나 Procedure( 프로시저 ) 를 묶어 놓은 것을 Oracle 에서는 Package( 패키지 ) 라 부릅니다 .
좀 더 자세히 설명하면 , 패키지란 Oracle Database 에 저장된 프로시저 , 함수 뿐만 아니라 변수 , 상수 ,
커서 , Exception 들을 하나로 묶은 캡슐화된 객체를 말합니다 .
 Advantages of PL/SQL Packages
• Application 을 좀 더 효율적으로 개발할 수 있게 도와줍니다 .
• 관련된 Schema Objects 를 re-compile 할 필요 없이 수정이 가능합니다 .
• 한 번에 여러 개의 Package Objects 를 메모리로 로드할 수 있습니다 .
• Procedure( 프로시저 ) 나 Function( 함수 ) 들의 오버로딩이 가능합니다 .
• Package 내의 모든 타입 , 항목 , Subprograms 을 PUBLIC 이나 PRIVATE 으로 선언해
서 사용할 수 있습니다 .

31
 Understanding The Package Specification
Package Specification( 패키지 명세부 ) 는 패키지에서 사용할 수 타입과 커서 , 함수 , 프로시저들을
선언합니다 .
[ Example ] 에서 굵은 글자로 표시된 부분이 바로 패키지 선언 문법에 해당됩니다 . 패키지 정의는 함수
나 프로시저와 마찬가지로 ‘ CREATE OR REPLACE PACKAGE’ 구문을 사용합니다 .
그림 9. Package Scope [ Example ]
CREATE OR REPLACE PACKAGE employee_process AS
-- 타입 , 커서 , Exception 선언
TYPE EmpRecord IS RECORD (emp_id INT, salary
REAL);
TYPE DeptRecord IS RECORD (dept_id INT, loc_id
INT);
-- 위에서 선언한 EmpRecord 를 반환하는 커서 선언
CURSOR salaries RETURN EmpRecord;
-- 급여가 맞지 않을 겨우 예외처리를 위한 exception
선언
invalid_salary EXCEPTION;
--
PROCEDURE hire_employee (
first_name VARCHAR2,
last_name VARCHAR2,
… );
PROCEDURE fire_employee (emp_id INT);
FUNCTION nth_highest_salary (n INT) RETURN
EmpRecord;
END employee_process;

32
 Understanding The Package Body
Package Body( 패키지 구현부 ) 은 명키지 명세부에서 선언된 각 커서 및 Subprograms 을 포함합니다 .
패키지 구현부는 ‘ CREATE OR REPLACE PACKAGE BODY’ 구문을 사용합니다 .
[ Example ]
CREATE OR REPLACE PACKAGE BODY employee_process AS
-- 이 변수는 구현부에 선언되었으므로 PRIVATE 속성을 가집니다 .
number_hired INT;
-- 명세부에서 선언한 커서를 정의합니다 .
CURSOR salaries RETURN EmpRecord IS
SELECT employee_id, salary
FROM employees
ORDER BY salary DESC;
-- 신규사원을 등록합니다 .
PROCEDURE hire_employee (
first_name VARCHAR2,
last_name VARCHAR2,
… ) IS
new_employee_id
BEGIN
-- 신규사원 등록을 위한 , employee_id 값의 시퀀스 번호를 가져옴
SELECT employee_seq.NEXTVAL
INTO new_employee_id
FROM dual;
 계속

33
계속
-- 신규사원 등록처리 .
INSERT INTO employees (employee_id, first_name, …)
VALUES (new_employee_id, first_name, …);
number_hired := number_hired + 1;
END hire_employee;
…
-- 직급에 따른 급여액 범위에 포함되는지를 검사하여 그 결과를 반환하는 함수
FUNCTION sal_ok (j_id VARCHAR2, salary REAL)
RETURN BOOLEAN IS
min_sal REAL;
max_sal REAL;
BEGIN
-- 해당 직급의 최소 , 최대급여액을 가져옵니다 .
SELECT min_salary, max_salary
INTO min_sal, max_sal
FROM jobs
WHERE job_id = j_id;
-- 파라미터로 들어오는 salary 금액이 해당 직급의 최소와 최대금액 사이에 있을 경우에는 TRUE
를 ,
-- 그렇지 않으면 FALSE 를 반환합니다 . (ANS 조건이므로 두 조건 모두 만족해야 TRUE 를 반환합
니다 .)
RETURN (salary >= min_sal) AND (salary <= max_sal)
END sal_ok;
…
BEGIN
number_hired := 0;
END employee_process;

34
Handling PL/SQL Errors
 Overview of PL/SQL Runtime Error Handling
PL/SQL 에서 Error 상황을 조절하는 것을 Exception 이라고 부릅니다 . Exceptions 은 내부적으로 정의
(System Runtime 에 의해서 ) 하거나 사용자가 정의합니다 .
Exception 을 처리하는 부분을 예외처리부라고도 부르는데 , 예외처리부는 BEGIN( 실행부 ) 에서 로직을 처리하
던 중 발생할 수 있는 각종 오류들에 대해 처리하는 부분입니다 . 즉 , Oracle 에서 SQL 문장을 실행 시 오류
가 발생하게 되면 자동으로 ORA-XXXX 오류 번호와 함께 메시지가 반환되는데 , 사용자가 직접 이러한 메시지
를 대체하거나 오류가 발생할 경우 처리할 로직을 기술할 부분입니다 . JAVA 와 같은 프로그래밍 언어의 TRY
… CATCH 문에서 CATCH 에 해당되는 부분입니다 .
[ Example ]
DECLARE
counter INTEGER;
BEGIN
counter := 10;
counter := counter / 0;
dbms_output.put_line(counter);
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line(‘ERRORS’);
END;
위의 [ Example ] 은 counter 라는 변수를 0 으로 나누려고 시도하였을 경우 발생하는 에러에 대해 오류처리
를 하는 것입니다 . 위의 예외처리부 (EXCEPTION~) 는 생략이 가능합니다 .
* 위의 OTHERS 라는 키워드는 PL/SQL 을 작성하는 사용자가 명시한 겻 이외의 모든 오류를 잡아내는 부분이라
고 생각하시면 됩니다 .

35
 Advantages of PL/SQL Exceptions
Exceptions 을 이용하면 여러 SQL 구문에서 발생하는 잠재적인 Error 에 단지 PL/SQL Exception Handler
Block 에 추가하는 것 만으로 대처할 수 있습니다 .
사용자가 오류를 처리했다면 발생 가능한 오류를 감안하여 프로그래밍을 한 것이라 볼 수 있습니다 . 즉 발생
가능한 모든 경우의 수를 감안하여 로직에 이를 심어 놓은 것입니다 . 예를 들어 Overview 의 [ Example ] 과
같이 0 으로 나눗셈을 수행하게 되었을 경우 , 사용자가 이러한 경우를 미리 생각했다면 오류 메시지를 보여주
지 않고 0 대신 다른 값으로 나누도록 처리할 수도 있습니다 . 그리고 그 다음 로직을 처리할 수가 있는 것입
니다 . 하지만 Oracle PL/SQL 엔진에서 처리하여 메시지를 보여주게 될 경우에는 오류가 발생한 시점에서 처
리가 중단되며 , 그 다음 로직은 처리할 수가 없습니다 . Oracle 을 사용하는 Application 의 종류에 따라서
PL/SQL 엔진이 오류처리를 할 경우 Application 이 다운될 수도 있지만 사용자가 이러한 오류를 잡아낸다면
프로그램이 다운되는 현상까지는 막을 수 있는 것입니다 .
 Tips for Handling PL/SQL Errors
[ Example1 ]
DECLARE
pe_ratio NUMBER(3, 1);
BEGIN
DELETE FROM stats WHERE symbol = ‘XYZ’;
SELECT price / NVL(earning, 0) INTO pe_ratio FROM
stocks
WHERE symbol = ‘XYZ’;
INSERT INTO stats (symbol, ratio) VALUES (‘XYZ’,
pe_ratio);
EXCEPTION
NULL;
END;

36
[ Example ] 의 Exception 을 효과적으로 처리할 수 있게 소스 형태를 조금 바꾸어보면 다음과 같습니다 .
다음의 [ Example2 ] 는 Exception 과 SAVEPOINT 의 조합을 이용한 소스 코드입니다 .
[ Better Example1 ]
DECLARE
pe_ratio NUMBER(3, 1);
BEGIN
DELETE FROM stats WHERE symbol = ‘XYZ’;
BEGIN ---------------- sub-block begins
SELECT price / NVL(earnings, 0) INTO pe_ratio FROM
stocks
WHERE symbol = ‘XYZ’;
EXCEPTION
pe_ratio := 0;
END; ---------------- sub-block ends
INSERT INTO stats (symbols, ratio) VALUES (‘XYZ’, pe_ratio);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
[ Example2 ]
DECLARE
name VARCHAR2(20);
…
suffix NUMBER := 1;
 계속

37
계속
BEGIN
FOR I IN 1..10 LOOP -- try 10 times
BEGIN -- sub-block begins
SAVEPOINT start_transaction; -- mark a savepoint
/* Remove rows from a table of survey results. */
DELETE FROM results WHERE answer1 = ‘NO’;
/* Add a survey respondent’s name and answers. */
INSERT INTO results VALUES (name, …);
-- raises DUP_VAL_ON_INDEX if two respondents have the same
name
COMMIT;
EXIT;
EXCEPTION
ROLLBACK TO start_transaction; -- undo changes
suffix := suffix + 1; -- try to fix
problem
name := name || TO_CHAR(suffix);
END; -- sub-block ends
END LOOP;
END;

38
Thought
 #1. Which Better Good Between Only SQL and PL/SQL
※ 다음의 내용은 Naver 및 Google 등의 검색을 통해서 수집한 내용입니다 .
 Better Good Situation in Only SQL
PL/SQL 은 사실 Cursor( 커서 ) 프로그래밍입니다 . 커서를 이용하면 쉽고 편리하게 data 를 조작하거나
얻을 수 있습니다 . 하지만 , Database 의 성능 저하의 주범이 바로 Cursor 입니다 . 그 이유는 Cursor
라는 것이 순차적인 속성을 가지기 때문입니다 . Database 가 원래부터 다량의 data 를 한꺼번에 처리하
는 집합적인 속성을 가지고 있기 때문에 Cursor 와 Database 는 궁합이 잘 맞지 않습니다 . 비디오가게
에서 전체회원 중 비디오를 두 번 이상 빌려간 회원을 구하는 예제로 들어보겠습니다 .
[ PL/SQL 을 이용 시 자주 사용하게 되는 스타일 ]
DECLARE 변수
LOOP
회원이 빌린 비디오 개수 추적 ;
IF 회원이 빌린 비디오 수 >=2 THEN
….
END IF;
END LOOP;
END;
이런 형태로 PL/SQL 을 구성하게 되면 Database 는 전체 대여 횟수만큼 루프를 돌리게 됩니다 . 이러한
방식은 순차적인 방식으로 구현을 한 형태입니다 .
 계속

39
Thought
하지만 , 이것을 SQL 을 이용하여 집합적인 방식으로 구현해 보면 Database 는 딱 두 번의 실행만으로
결과를 낼 수 있게 됩니다 . 다음이 훨씬 Database 에 적합한 구조를 지닌 방식입니다 .
[ SQL 만을 이용하여 똑같은 결과를 출력하는 구문 ]
SELECT 회원이름
FROM 비디오 대여
MINUS
SELECT DISTINCT 회원이름
FROM 비디오대여
 Better Good Situation in PL/SQL
Application 과 Database 상에서는 많은 Network Traffic 이 발생합니다 . 예를 들어 여러 개의
SELECT, INSERT, DELETE 문을 보내려면 Only SQL 은 한번에 하나의 SQL 만을 보내야 합니다 .
[ SQL Example ]
SELECT * FROM 학적부 WHERE 이름 = ‘ 김태우’ ;
INSERT INTO 학적부 ( 이름 , 성별 , .. ) VALUES ( 김태희 , 여 , .. );
DELETE FROM 학적부 WHERE 이름 = ‘ 홍길동 ;
 계속

40
Thought
하지만 , 다음의 PL/SQL 을 사용하게 되면 Application 과 Database 는 단 한번의 Connection 만으로도
여러 개의 SQL 문을 동시에 보낼 수 있습니다 .
[ PL/SQL Example ]
DECLARE
BEGIN
SELECT * FROM 학적부 WHERE 이름 = ‘ 김태우’ ;
INSERT INTO 학적부 ( 이름 , 성별 , .. ) VALUES ( 김태희 , 여 , .. );
DELETE FROM 학적부 WHERE 이름 = ‘ 홍길동 ;
COMMIT;
END;
이러한 경우에서는 PL/SQL 이 Only SQL 을 사용하는 것보다 훨씬 Network Traffic 에 부담을 주지 않고
SQL 을 실행 시킬 수 있습니다 .

41
Thought
 #2. A Choice Between
 When Choosing Between Nested Tables and Associative Arrays?
Nested Table( 중첩 테이블 ) 은 Database Column 에 저장할 수 있습니다 . 그러나 Associative
Arrays( 연관배열 ) 은 그렇지 못합니다 . Nested Table 은 큰 테이블과 Single-Column 테이블과의
Normally Join 을 할 경우에 간단한 SQL 만으로도 가능합니다 .Associative Arrays 은 패키지가 초기화
되거나 빈번히 프로시저가 호출되는 메모리 안에서 상대적으로 적은 테이블 검색만으로 Collection 을
구성할 때 어울립니다 .
 When Choosing Between Nested Tables and Variable Arrays?
Varrays( 고정배열 ) 은 다음과 같은 상황에 유용합니다 .
- 구성요소의 개수를 알 수 있는 경우
- 구성요소들이 순차대로 모두 액세스 되어 지는 경우
Nested Tables( 중첩 테이블 ) 은 다음과 같은 상황에 유용합니다 .
- Index 값이 연속적이지 않은 경우
- Index 값을 위한 한계를 미리 정의 하지 못할 경우
- 몇몇의 구성요소를 지우거나 수정해야 할 경우 . 단 , 한번에 모든 구성요소를 행할 수 없음 .

42
Thought
 #3. Why Use a Function?
 서브쿼리를 사용한 경우
 함수를 사용한 경우
SELECT a.employee_id, a.first_name || ‘ ‘ || a.last_name names,
( SELECT b.department_name
FROM departments b
WHEREa.department_id = b.department_id ) dep_names
FROM employees a
WHERE a.department_id = 100;
CREATE OF REPLACE FUNCTION get_dep_name (dep_id NUMBER)
RETURN VARCHAR2 IS
sDepName VARCHAR2(30);
BEGIN
SELECT department_name
INTO sDepName
FROM departments
WHERE department_id = dep_id;
RETURN sDepName;
END;
SELECT a.employee_id, a.first_name || ‘ ‘ || a.last_name names,
get_dep_name(a.department_id) dep_names
FROM employees a
WHERE a.department_id = 100;

43
위의 두 경우의 예제는 employees 테이블에서 조건에 맞는 결과에 departments 테이블에 있는 부서명을 같
이 출력하는 예제 입니다 .
함수를 사용한 경우의 get_dep_name 함수는 파라미터로 부서번호가 넘어오면 해당 부서번호에 맞는 부서명을
찾아 반환하는 함수 입니다 .
두 가지 경우 모두 결과는 동일하지만 함수를 사용하였을 경우에는 조인이 발생하지 않습니다 . 부서번호를 함
수의 파라미터로 넘기면 그 부서번호에 대한 부서명을 가져올 뿐입니다 . 함수를 사용하였을 경우 차후 유지
보수 차원에서 용이하다는 장점과 소스가 단순해 진다는 장점이 있습니다 .
우리 회사의 같은 경우 WNTAS 에서 NID 타이틀을 가져올 경우 서브쿼리나 조인을 사용하지 않고 , NID_TITLE
이란 함수를 만들어서 사용하고 있습니다 .
Thought

�ݺ�ߣ

PL/SQL - 10g Release1

More Related Content

What's hot (20)

Similar to PL/SQL - 10g Release1 (20)

PL/SQL - 10g Release1