02 PLSQL BLOCK STRUCTURE -...
Transcript of 02 PLSQL BLOCK STRUCTURE -...
-
오라클입문(SQL&PL/SQL) 2. PL/SQL BLOCK
Written by AngelaLEE 2 - 1 www.muhanedu.net
PL/SQL BLOCK
PL/SQL 블록 – 기본 구조
PL/SQL은 기본적으로 블록(BLOCK)구조를 가지고 있다.
블록의 기본적인 구성은 선언부(DECLARE), 실행부(BEGIN), 예외처리부
(EXCEPTION)로 구성되어 있다. PL/SQL블록은 그 블록안에 블록을 포함할 수 있는
데 포함된 블록을 Nested Block이라 한다.
블록의 유형에는 크게 Anonymous PL/SQL Block과 Stored PL/SQL Block이 있다.
Stored PL/SQL Block 은 서브프로그램 또는 프로그램 단위라고도 하며, 스키마를
구성하는 오브젝트로서 서버에 저장되거나 오라클 툴안에 라이브러리 형태로 저장
된다. 이러한 서브프로그램은 복잡한 처리를 단순하게 모듈화할 수 있는 해결방안
을 제공한다. 이 장에서는 Anonymous PL/SQL Block 을 중심으로 설명되며,
Stored PL/SQL Block은 뒷 장에서 다루기로 한다..
>> PL/SQL BLOCK 기본구성
DECLARE(선언부, 생략가능)
BEGIN(실행부, 필수요소)
EXCEPTION1(예외처리부, 생략가능)
END ; (블록의 끝을 의미, 필수요소)
블록내의 각 부분에 포함되는 명령들은 세미콜론(;)으로 문장이 끝난다.
블록이 성공적으로 처리가 완료되면, 아래와 같은 메시지가 출력될 것이다.
PL/SQL procedure successfully completed.
( 이 결과 메시지를 여러분들은 자주 보기를 바란다. ^.*)
1 예외(EXCEPTION)은 PL/SQL에서 발생되는 에러(ERROR)를 일컫는다.
-
오라클입문(SQL&PL/SQL) 2. PL/SQL BLOCK
Written by AngelaLEE 2 - 2 www.muhanedu.net
PL/SQL 블록 – 기본 규칙과 지침사항
문장은 여러 줄에 걸쳐질 수 있으나, 키워드는 분리될 수 없다.
블록의 내용을 읽기 쉽도록 공백문자를 사용하여 키워드 내지는 문장을
적절하게 분리함으로써 의미분석이 되도록 하며, 들여쓰기도 권장한다.
예약어는 식별자명으로 사용될 수 없으나, Alias 로는 사용될 수 있다. 즉
이중부호(“ )를 함께 사용할 수는 있다. (예: “ TABLE ” )
식별자명은 기본 오라클 Naming Rule 을 준수한다.
리터럴(문자,날짜)은 단일인용부호(‘)로 둘러싼다. 널 값은 NULL 상수로 표
현한다. NULL에 대한 비교는 IS (NOT) NULL연산자를 사용해야 한다. (참고
적으로) 조건문이나 예외 처리의 경우 해당 조건에 대해 아무런 처리도
하지 않을 경우 NULL ; 문장을 사용하기도 한다.
주석처리를 하고자 할 경우에 단일 행은 - - (하이픈 두 개), 복수 행은 /*
로 시작하고 */ 로 종료한다.
PL/SQL 블록내의 명령(수식)에서는 오라클 함수를 사용할 수 있다. 단, 그
룹함수와 DECODE함수는 SQL 문장에 포함되어야만 사용될 수 있다.
PL/SQL 에서는 에러(EXCEPTION)처리와 관련된 함수에는 SQLCODE 함수
와 SQLERRM함수가 있다(뒷부분에서 언급할 것임.)
일반적으로 변수의 생명주기는 블록 내부의 범위이다. 예를 들어, 블록을
포함하는 블록의 경우에 바깥쪽 블록에서 선언된 변수는 포함된 블록 내
부에서도 참조될 수 있다. 그러나 만약 내부 블록에 바깥쪽 블록에서 선언
된 변수와 동일명의 변수가 있었다면, 이때 변수의 참조는 지역변수
(Local)가 우선한다. 만약 블록 레이블을 사용했다면 바깥쪽 변수를 동일
명의 변수가 있는 내부 블록에서 참조할 수 있다(레이블을 변수명 앞에
접두어로 붙이면 된다.). 물론 내부 블록의 변수(지역변수)는 바깥쪽 블록
에서 참조할 수 없다.
-
오라클입문(SQL&PL/SQL) 2. PL/SQL BLOCK
Written by AngelaLEE 2 - 3 www.muhanedu.net
> DECLARE ANGEL CHAR; BEGIN DECLARE ANGEL CHAR; BEGIN main.ANGEL := ‘A’; END; END;
PL/SQL 블록 내에서 사용되는 연산자는 SQL 에서 허용하는 연산자와 동일
하며, 지수 연산자로 ** 을 추가로 제공한다. **가 가장 높은 우선순위를
가지며, SQL에서의 우선순위와 동일하게 처리된다.
-
오라클입문(SQL&PL/SQL) 2. PL/SQL BLOCK
Written by AngelaLEE 2 - 4 www.muhanedu.net
PL/SQL 블록 – 선언부(DECLARE)
선언부(DECLARE)에는 블록내에서 사용되는 변수, 상수, 사용자 정의 예외사항
(EXCEPTION) 그리고 명시적 커서(Explicit Cursor)를 선언할 수 있다.
이 부분에서는 변수와 상수를 중심으로 설명하기로 하며, 예외와 커서는 뒷 장에서
본격적으로 다루기로 한다.
변수는 데이터를 임시적으로 저장하기 위해 메모리에 기억공간으로서 선언되며, 변
수에 저장된 값은 블록내에서 변경될 수 있다. 선언부에서 변수를 선언하면서 초기
값을 부여할 수도 있으며, 읽기전용 변수 즉 상수로서 선언할 수도 있다. 또 다른
형태의 변수라 볼 수 있는 파라미터(parameter)는 서브 프로그램과 값을 (상호)
전달하는 역할을 담당한다. 파라미터는 매개변수(Argument)라고도 한다.
PL/SQL변수는 기억장소로서 메모리에 확보되는데, 이 변수의 생명주기(Life Cycle)
는 블록시작에서부터 블록 종료이다. 즉 블록의 실행이 종료가 되면, 변수는 메모
리에서 제거됨을 의미한다. 만약 블록이 새롭게 실행된다고 해도 이전의 값을 그대
로 사용할 수는 없다. 이를 변수의 Scope Rule 이라고도 한다. 만약 전역변수
(Global Variable)를 선언하고자 한다면 패키지를 작성한다. (패키지는 뒷부분에서
다루기로 한다.)
>> 변수의 유형
PL/SQL 변수 Scalar (단일 값) CHAR/VARCHAR2 LONG/LONG RAW NUMBER BINARY_INTEGER1 PLS_INTEGER BOOLEAN DATE TIMESTAMP TIMESTAMP WITH TIME ZONE TIMESTAMP WITH LOCAL TIME ZONE INTERVAL YEAR TO MONTH INTERVAL DAY TO SECOND
1 BINARY_INTEGER(-2,147,483,647 ~ 2,147,483,647)보다 PLS_INTEGER(-2,147,483,647 ~ 2,147,483,647)가 적은 저장공간을 요구하며, 속도도 빠르다. BOOLEAN은 TRUE, FALSE, NULL값을 가진다.
-
오라클입문(SQL&PL/SQL) 2. PL/SQL BLOCK
Written by AngelaLEE 2 - 5 www.muhanedu.net
Composite(레코드, 배열…)
Reference(포인터)
LOB(Large Objects)
기타 변수 (non-PL/SQL 변수)
Bind Variable
Host Variable
기타변수는 PL/SQL 블록이 실행되는 환경(예를 들면, Precompiler, iSQLPlus, …)에
서 선언한 변수로서 실행환경과 블록내부에서 참조되어지기도 한다.
예를 들면, iSQL*Plus 또는 SQL*Plus 에서 선언되는 치환변수나 바인드 변수는 이
환경에서 실행되는 Anonymous PL/SQL 블록 내에서도 참조되어질 수 있으며 바인
드 변수의 경우에는 블록이 종료되어도 값을 유지하고 있다. 치환변수는 읽기 전용
의 의미를 가지고 있으므로 실행환경에서 블록내부로의 값을 전달하는 역할을 한
다. 참고적으로 Stored PL/SQL Block 경우에는 파라미터 또는 매개변수를 통해 실
행환경과 블록간에 값을 주고 받을 수 있고, 치환변수나 바인드 변수는 Stored
PL/SQL Block 내에서 직접 참조될 수 없다.
단순 변수의 선언
> SYNTAX:
Identifier [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expr];
Identifier 변수의 이름으로 식별자의 역할을 한다. 블록내에서는
유일해야 한다. 오라클 Naming Rule 에 합당한 이름을
지정해야 한다. 선언되는 변수의 유형을 직관적으로 이
해할 수 있도록 의미있는 접두어나 접미어를 사용하도록
권장한다.
CONSTANT 읽기전용 변수 즉 상수로 선언하기 위한 키워드로 기본
적으로 초기값이 반드시 지정되어야 한다.(생략가능)
Datatype 오라클에서 허용하는 데이터 타입은 모두 지원한다. 또
한 %TYPE 을 사용하여 테이블내의 컬럼과 동일한 데이
터 형을 선언할 수도 있고, %ROWTYPE 을 사용하여 테
이블의 레코드 구조와 동일 형태의 레코드를 선언할 수
-
오라클입문(SQL&PL/SQL) 2. PL/SQL BLOCK
Written by AngelaLEE 2 - 6 www.muhanedu.net
도 있다.
NOT NULL 이 변수는 항상 값을 가지도록 제약을 주는 키워드로 초
기값이 반드시 지정되어야 한다.(생략가능)
:=|DEFAULT 변수에 기본값을 부여하기 위한 키워드이다.
expr 변수에 부여할 기본값을 의미하며 단순한 값에서부터 다
른 변수, 수식, 함수가 올 수 있다.
> EX:
V_EMPNO NUMBER(3);
V_DATE DATE DEFAULT SYSDATE;;
V_NAME VARCHAR2(14) NOT NULL := ‘Angela Lee’;
C_RATE CONSTANT NUMBER := 0.2;
V_SAL EMP.SAL%TYPE;
V_REC DEPT%ROWTYPE;
( 이때, V_REC 변수 는 복 합구 조 를 가지게 되며 DEPT 테이블 내의 컬럼 정
의도 그 대로 내포 하고 있으 므 로 , 값을 참조 할 때는 V_REC.DEPTNO 와 같이
V_REC 을 접두 어로 붙 여야 한다.)
V_SIGNAL BOOLEAN := FALSE;
복합 변수의 선언
> SYNTAX(PL/SQL RECORD):
* PL/SQL Record 정의와 선언
① TYPE type_name IS RECORD ( field_declaration[, field_declaration]… ) ;
② Identifier type_name
① 정의부분 :
-
오라클입문(SQL&PL/SQL) 2. PL/SQL BLOCK
Written by AngelaLEE 2 - 7 www.muhanedu.net
type_name 은 RECORD 유형의 이름으로 일반적인 프로그래밍 언어
에서는 구조체로 통용되기도 한다. 여러 가지 유형의 변
수가 하나의 레코드(구조체) 단위로서 처리되며, 레코드
내의 변수(필드)를 참조할 경우에는
type_name.field_name 을 사용한다. (참고적으로 테이
블내의 행 구조와 동일하게 레코드 변수를 선언하고자
할 경우에는 %ROWTYPE 키워드를 사용한다.)
Field_declaration은 일반 변수의 선언과 동일한 문법형태를 지닌다.
② 선언부분 :
기본적으로 복합형의 데이터는 우선 정의를 하고 해당 정의를 통해 실
제 복합 변수를 선언하는 단계로 구성된다. 이 선언부분에서는 실제 복
합 변수에 대한 기억공간이 확보되는 시점이다. (정의 부분에서는 메모
리에 공간이 확보되지는 않으며 단지 복합 데이터 형에 대한 기술이
이루어지는 부분이다.)
> EX(PL/SQL RECORD):
DECLARE
....
TYPE DEPT_REC_TYPE IS RECORD
(DNO NUMBER(2),
DNAME DEPT.DNAME%TYPE) ;
DEPT_REC DEPT_REC_TYPE ;
....
EMP_REC EMP%ROWTYPE ;
BEGIN
....
DBMS_OUTPUT.PUT_LINE(DEPT_REC.DNO) ;
DBMS_OUTPUT.PUT_LINE(EMP_REC.EMPNO) ;
....
END;
DEPT_REC
DNAME DNO
-
오라클입문(SQL&PL/SQL) 2. PL/SQL BLOCK
Written by AngelaLEE 2 - 8 www.muhanedu.net
> SYNTAX(PL/SQL TABLE):
* PL/SQL Table 정의와 선언
① TYPE type_name IS TABLE OF {column_type|variable%type|table.column%type} [NOT NULL] | table%rowtype [INDEX BY BINARY_INTEGER] ;
② Identifier type_name
① 정의부분 :
type_name 은 PL/SQL Table 유형의 이름으로 일반적인 프로그래밍
언어에서는 배열로 통용되기도 한다. 동일한 유형의 데이
터(또는 데이터 구조)들을 하나의 연속적인 메모리 공간
에 확보하기 위한 방법을 제공한다.
INDEX BY 절은 그 배열내의 요소(element)에 접근하기 위한 첨자(위
치) 값으로 사용되며, 범위는 BINARY_INTEGER 의 범위
에 속한다.
② 선언부분 :
기본적으로 복합형의 데이터는 우선 정의를 하고 해당 정의를 통해 실
제 복합 변수를 선언하는 단계로 구성된다. 이 선언부분에서는 실제 복
합 변수에 대한 기억공간이 확보되는 시점이다. (정의 부분에서는 메모
리에 공간이 확보되지는 않으며 단지 복합 데이터 형에 대한 기술이
이루어지는 부분이다.)
-
오라클입문(SQL&PL/SQL) 2. PL/SQL BLOCK
Written by AngelaLEE 2 - 9 www.muhanedu.net
> EX(PL/SQL TABLE):
DECLARE
....
TYPE SAL_TAB_TYPE IS TABLE OF EMP.SAL%TYPE
INDEX BY BINARY_INTEGER ;
TYPE STARTDATE_TAB_TYPE IS TABLE OF DATE
INDEX BY BINARY_INTEGER ;
SAL_TAB SAL_TAB_TYPE ;
S_DATE STARTDATE_TAB_TYPE ;
....
BEGIN
....
SAL_TAB(1) := 3500 ;
S_DATE(1) := SYSDATE ;
....
IF SAL_TAB(1).EXISTS(1) THEN
…
END IF ;
....
END;
(1)
SAL_TAB
(2) …
-
오라클입문(SQL&PL/SQL) 2. PL/SQL BLOCK
Written by AngelaLEE 2 - 10 www.muhanedu.net
* 오 라클 에서는 PL/SQL TABLE 의 조 작을 위한 Built-ins(Method) 를 제공 하고 있다. 이때
의 프 로 시저나 함수 는 PL/SQL TABLE 명과 함께 사용 된다.
Built-in(Method) Description
EXISTS(n) N 번째 요 소 의 값이 존 재하면 TRUE
COUNT 현재 존 재(사용 )하는 요 소 들 의 개수
FIRST, LAST 현재 사용 되는 첨자의 첫번째, 마지막 값을 리
턴.
PRIOR(n) 배열 요 소 의 위치를 조 정하는 함수 로 n 개 이
전의 인덱스 첨자값 리턴
NEXT(n) 배열 요 소 의 위치를 조 정하는 함수 로 n 개 다
음 의 인덱스 첨자값 리턴
TRIM PL/SQL TABLE 의 마지막 요 소 를 제거
(TRIM(n)은 마지막으 로 부 터 n 개 제거)
DELETE PL/SQL TABLE 의 모 든 요 소 를 제거
(DELETE(n)은 n 번째 요 소 제거,
DELETE(m,n)은 m 에서 n 번째의 요 소 들 을 제
거)
* 바인드 변수(Bind Variable)는 iSQL*Plus(또는 SQL*Plus)의 variable 명령을 사용
하여 선언한다. 실행환경이나 블록내에서 참조할 때는 접두문자 콜론(:)을 변수명
앞에 붙여야 한다. 바인드 변수의 값을 출력할 때는 print 명령을 사용한다. 이때
변수명앞에 접두문자를 붙이지 않는다. (참고적으로 치환변수는 앰퍼샌드(&)를 접
두문자로 붙인다.)
* PL/SQL 블록을 실행할 경우, 블록내의 값을 화면에 출력하고 싶을 때 오라클에서
제공하는 DBMS_OUTPUT 패키지 내의 PUT_LINE 프로시저를 사용한다.
이 프로시저가 정상적으로 결과를 화면출력 하도록 하기 위해서는 iSQL*Plus(또는
SQL*Plus)에서 아래의 명령을 먼저 실행해야 한다.
SET SERVEROUTPUT ON 또는
EXECUTE DBMS_OUTPUT.ENABLE
-
오라클입문(SQL&PL/SQL) 2. PL/SQL BLOCK
Written by AngelaLEE 2 - 11 www.muhanedu.net
PL/SQL 블록 – 실행부(BEGIN~END)
실행부(BEGIN)는 실제 처리하고자 하는 명령들이 절차적으로 기술되는 부분으로,
기존의 SQL 문과 프로그래밍 언어 특징인 제어문(조건문, 반복문)을 사용하여 완벽
한 트랜잭션 처리를 수행할 수 있도록 한다.
SQL 문장
SELECT 문 : PL/SQL 블록에서의 SELECT문은 데이터베이스의 테이블로부터 한 레코드(건) 씩 읽어오는 Read 문으로 사용된다.
만약 두 건 이상의 레코드가 검색(Read)되거나 한 건도 읽지 못
하게 되면 예외(exception: TOO_MANY_ROWS,
NO_DATA_FOUND)가 발생된다. 이렇게 발생한 예외에 대해서는
예외처리부 (EXCEPTION)에서 처리할 수 있다. 검색된 여러 건을
처리하고자 할 경우에는 뒷부분에서 다루게 될 명시적 커서
(Explicit Cursor)를 사용하면 된다.
SELECT 문은 데이터 베이스로부터 읽어온 데이터(값)를 블록내의
변수에 저장해야 하는데, 이때 SELECT절은 INTO절을 가지게 된다.
INTO절에 오는 변수는 기본적으로 선언부(DECLARE)에서 선언되
어 있어야 하며, SELECT 리스트의 수와 데이터 타입이 같아야 한다.
(예)
DECLARE V_NAME EMP.ENAME%TYPE; V_SAL EMP.SAL%TYPE; V_DATE DATE; BEGIN SELECT ENAME, SAL, HIREDATE INTO V_NAME, V_SAL, V_DATE FROM EMP WHERE EMPNO = 7788;
… END;
DML 문 : 데이터베이스의 데이터를 조작하는 DML문에는 INSERT, UPDATE, DELETE, MERGE 등이 있다. 이 DML문은 PL/SQL블록 내에서는 어
-
오라클입문(SQL&PL/SQL) 2. PL/SQL BLOCK
Written by AngelaLEE 2 - 12 www.muhanedu.net
떤 제약도 없이 사용할 수 있다. 즉 원래 그대로 사용이 가능하다
는 것이다. 단, 데이터 제약조건을 위배하는 DML문은 해당
EXCEPTION을 발생하게 된다. 이 예외에 대한 처리는 예외처리부
(EXCEPTION절)에서 할 수 있다.
TCL 문 : 트랜잭션을 처리하는 COMMIT, ROLLBACK(SAVEPOINT) 명령 모
두는 PL/SQL블록 내에서 (그대로) 사용될 수 있다.
DDL 문 : DDL문은 데이터베이스 딕셔너리(Data Dictionary)를 변경하는 매우 민감한 명령이므로, PL/SQL블록 내에서 직접 사용될 수 없다.
단, 오라클에서 제공하는 Built-ins 중에 Dynamic SQL문을 처리하
는 DBMS_SQL 패키지 또는 DBMS_DDL 패키지가 있는데, 이를 사
용하면 간접적으로 PL/SQL블록에서 처리할 수 있다.
DCL 문 : 데이터 베이스에 대한 접근(보안)과 관련된 DCL문은 PL/SQL블록
에서 간접적으로 사용될 수 있기는 하나 거의 사용될 경우는 없다.
-
오라클입문(SQL&PL/SQL) 2. PL/SQL BLOCK
Written by AngelaLEE 2 - 13 www.muhanedu.net
제어 문장 ( Control Structures)
오라클 PL/SQL에서는 일반 프로그래밍 언어가 가지는 기본적인 제어문을 제공하며
이 제어문들은 제어문들을 포함할 수 있다.(Nested)
오라클이 제공하는 제어문의 종류는 아래와 같다.
제어문 조건문 IF 문 CASE 문
반복문 BASIC LOOP 문 WHILE 문 FOR 문
-
오라클입문(SQL&PL/SQL) 2. PL/SQL BLOCK
Written by AngelaLEE 2 - 14 www.muhanedu.net
조건문(IF 문) : 조건에 따라 처리를 하고자 할 경우 이 조건문을 사용한다. 오라클에서는 일반 프로그래밍 언어와 같이 IF 문을 제공한다. IF문안에 IF
문을 가질 수 있다.(Nested IF문)
> SYNTAX:
IF condition1 THEN Statements1;
[ELSIF condition2 then statements2 ; ]
[ELSE statements3 ; ]
END IF ;
* condition1 조건을 판단하는 수식으로 Boolean값(true, false, null)을 리턴한다. 이 조건이 만족하
면(true) THEN절의 statements1부분을 수
행한 후 if문을 종료한다(end if문 수행).
만약 조건을 만족하지 않게 되면
(false,null) 기본적으로 if문을 종료한다(즉,
end if;문 수행). 그러나 ELSIF 또는 ELSE
문이 있다면 해당 부분의 명령을 수행한다.
* statement n 이 부분은 하나이상의 SQL 또는 PL/SQL문장이 있는 곳으로 해당 조건이 만족하게
될 경우 선택적으로 수행된다. 이 부분에
IF문을 중첩해서 사용할 수도 있다.
* ELSIF condition1의 조건이 만족하지 않을 경우(false,null)에 다른 조건 처리를 위한 절로
서 condition2의 조건을 만족한다면
statements2 부분을 수행한 후 if 문을 종
료한다. (필요하다면) 이 부분은 여러 개의
상호 배타적인(Mutual Exclusive) 조건 처
-
오라클입문(SQL&PL/SQL) 2. PL/SQL BLOCK
Written by AngelaLEE 2 - 15 www.muhanedu.net
리를 위해 한 if문장내에서 여러 번 사용될
수 있다. (참고적으로 ELSIF의 철자에 주의
하도록 하자.)
* ELSE IF문 이하 모든 조건들에 만족되지 않는 경우에 수행되는 부분으로, IF문에 단 한번 마
지막에 올 수 있다.
* END IF ; IF문의 끝을 알리는 절로 END와 IF 사이에는 하나 이상의 공백을 두어야 한다.
-
오라클입문(SQL&PL/SQL) 2. PL/SQL BLOCK
Written by AngelaLEE 2 - 16 www.muhanedu.net
> EX:
…
IF V_SAL >= 3000 THEN V_TAX_RATE : = 0.5 ;
ELSIF V_SAL >= 2000 THEN V_TAX_RATE := 0.3 ;
ELSIF V_SAL >= 1000 THEN V_TAX_RATE := 0.1 ;
ELSE V_TAX_RATE := 0.05 ;
END IF;
…
< 참고 : IF_THEH_ELSE 문의 흐름 >
Condition 1 TRUE
Statement 1
Condition 2 TRUE
Statement 2
FALSE,NULL
Statement 3
FALSE,NULL
-
오라클입문(SQL&PL/SQL) 2. PL/SQL BLOCK
Written by AngelaLEE 2 - 17 www.muhanedu.net
조건문(CASE 문) : 조건처리를 위한 다른 방법을 제공하는 제어문이다. 오라클의
CASE 함수와 비슷한 로직으로 처리된다.
> SYNTAX:
CASE selector
WHEN expression1 THEN result1
WHEN expression2 THEN result2
…
WHEN expression N THEN resultN
[ELSE resultn + 1 ; ] END ;
> EX:
SET SERVEROUTPUT ON
DECLARE V_CHAR CHAR := UPPER(‘&p_char) ; V_MSG VARCHAR2(26) ;
BEGIN
V_MSG := CASE V_CHAR WHEN ‘A’ THEN ‘Excellent’ WHEN ‘B’ THEN ‘Very Good’ WHEN ‘C’ THEN ‘Good’ ELSE ‘Poor’ END ;
DBMS_OUTPUT.PUT_LINE(V_MSG) ;
END ;
/
-
오라클입문(SQL&PL/SQL) 2. PL/SQL BLOCK
Written by AngelaLEE 2 - 18 www.muhanedu.net
반복문(BASIC LOOP 문) : PL/SQL블록내에서 반복처리해야 명령이 있는 경우에 사용되는 문으로, 일반 프로그램 언어의 DO~WHILE문과 일맥
상통하는 문이다. 반복문 안에 있는 문장(들)을 한번 이상은 수행
하도록 하는 명령으로 반복 허용 조건이 만족하는 동안 반복 수행
되도록 할 수 있다.
> SYNTAX:
LOOP
Statement1 ; …
EXIT [ WHEN condition ] ; …
END LOOP ;
* EXIT 기본 루프(BASIC LOOP)를 빠져 나오는 문으로 END LOOP ; 다음 문장으로 제어가
넘어간다. WHEN 절이 생략되면 무조건 루
프를 빠져나간다.
* WHEN 기본 루프(BASIC LOOP) 종료하기 위한 조건을 지정하는 문으로 해당 조건을 만족하
면 루프를 빠져나간다. 만약 조건을 만족하
지 않는다면 LOOP ~ END LOOP; 사이의
문장을 반복하게 된다.
-
오라클입문(SQL&PL/SQL) 2. PL/SQL BLOCK
Written by AngelaLEE 2 - 19 www.muhanedu.net
> EX:
SET SERVEROUTPUT ON
DECLARE V_START NUMBER := &p_start ; V_END NUMBER := &p_end ; V_NUM NUMBER ; V_SUM NUMBER := 0 ; -- Recommanded BEGIN
V_NUM := V_START ; LOOP V_SUM := V_SUM + V_NUM ; V_NUM := V_NUM + 1 ; EXIT WHEN V_NUM > V_END ; END LOOP ;
DBMS_OUTPUT.PUT_LINE(V_SUM) ; END ;
/
QUIZ:
위의 블록에서 p_start, p_end 에 각각 1,10 이 입력
되었다면 화면의 결과는 ?
만약, p_start, p_end 에 각각 10,5 가 입력되었다면
결과는?
-
오라클입문(SQL&PL/SQL) 2. PL/SQL BLOCK
Written by AngelaLEE 2 - 20 www.muhanedu.net
반복문(WHILE 문) : WHILE 문은 PL/SQL 블록 내에서 반복 처리를 수행하는 문장으로, 조건이 만족하는 동안에는 루프 안의 명령들을 반복적으로
수행하고, 조건이 만족하지 않을 경우 해당 반복루프를 종료한다.
이 WHILE문의 경우 조건이 처음부터 만족되지 않으면, BASIC
LOOP문과는 다르게 루프안의 명령(들)이 한번도 수행되지 못할
수도 있다.
> SYNTAX:
WHILE condition LOOP
Statement1 ;
Statement2 ; …
END LOOP ;
* condition 반복문을 종료하기 위한 조건절로 조건이 만족되는 동안(true)에 루프안의 문장들을
반복 수행하고, 조건이 불만족되면(false,
null) 루프는 종료된다. (제어는 end loop;
문 다음으로 넘어간다.)
-
오라클입문(SQL&PL/SQL) 2. PL/SQL BLOCK
Written by AngelaLEE 2 - 21 www.muhanedu.net
> EX:
SET SERVEROUTPUT ON
DECLARE V_START NUMBER := &p_start ; V_END NUMBER := &p_end ; V_NUM NUMBER ; V_SUM NUMBER := 0 ;
BEGIN
V_NUM := V_START ; WHILE V_NUM
-
오라클입문(SQL&PL/SQL) 2. PL/SQL BLOCK
Written by AngelaLEE 2 - 22 www.muhanedu.net
반복문(FOR 문) : FOR문은 일정한 반복회수로 처리하고자 할 경우 유용한 반복문이다. FOR문의 반복을 제어하는 변수는 선언부(DECLARE)에서
선언하지 않아도 무방하다. 왜냐하면 오라클은 제어변수에 대해서
는 묵시적으로 정수형(INTEGER) 선언을 해준다.
> SYNTAX:
FOR counter IN [REVERSE] start . . end LOOP
Statement1 ;
Statement2 ; …
END LOOP ;
* counter 반복문을 제어하는 정수형 변수로서 묵시적 선언이 이루어진다. Start 값에서부터
end 값까지의 제어 범위를 가진다. 기본적
인 증감값은 1 이다. 이 변수의 생명주기는
반복문(for) 안에서만 유효하며, 반복문 안
에서 변경될 수 없다.
* REVERSE 이는 제어변수(counter)의 값이 감소하도록 하는 키워드이다. 주의해야 할 것은
REVERSE 를 사용해도 항상 start
-
오라클입문(SQL&PL/SQL) 2. PL/SQL BLOCK
Written by AngelaLEE 2 - 23 www.muhanedu.net
> EX:
SET SERVEROUTPUT ON
DECLARE V_START NUMBER := &p_start ; V_END NUMBER := &p_end ; -- V_NUM NUMBER ; //선언하지 않음. V_SUM NUMBER := 0 ;
BEGIN
FOR V_NUM IN V_START .. V_END LOOP V_SUM := V_SUM + V_NUM ; END LOOP ;
DBMS_OUTPUT.PUT_LINE(V_SUM) ;
END ;
/
-
오라클입문(SQL&PL/SQL) 2. PL/SQL BLOCK
Written by AngelaLEE 2 - 24 www.muhanedu.net
실습 – PL/SQL BLOCK
0. 사전 준비 사항
아래의 실습예제들은 각각 스크립트 파일에 저장한다. (예를 들면, 1 장의 3 번 예제라면,
p01_03.sql 로 저장한다.)
저장된 스크립트 화일을 실행하기 위해서는 start 또는 @ 을 사용한다.
만약 compile(parse)후 에러가 있었다면, show errors 명령으로 확인하여 적절한 조치를 취하
도록 한다. 해당 에러부분을 수정한 후에는 다시 스크립트를 실행하여 결과를 확인한다.
1. 두 개의 수를 입력받아 합을 출력하는 PL/SQL 블록
SET ECHO OFF SET VERIFY OFF SET SERVEROUTPUT ON
DECLARE
V_SU1 NUMBER(7,2) := &p_su1 ;
V_SU2 NUMBER(7,2) := &p_su2 ;
V_SUM NUMBER(8,2) := 0 ;
BEGIN
V_SUM := V_SU1 + V_SU2 ;
DBMS_OUTPUT.PUT_LINE( ‘첫번째 수 ’ || V_SU1 || ‘와 두번째 수 ‘ || V_SU2 ||
‘의 합은 ‘ || V_SUM ) ;
END ;
/ SET SERVEROUTPUT OFF SET VERIFY OFF SET ECHO ON
QUIZ:
치환변수 p_su1 과 p_su2 에 각각 10.2 와 82.456 이 입력된다면 출력결과는 ?
-
오라클입문(SQL&PL/SQL) 2. PL/SQL BLOCK
Written by AngelaLEE 2 - 25 www.muhanedu.net
2. 사번을 입력받아 사원(EMP)테이블에서 사원의 이름과 급여를 검색하여 출력하는 PL/SQL 블
록.
SET ECHO OFF SET VERIFY OFF SET SERVEROUTPUT ON
DECLARE
V_NAME EMP.ENAME%TYPE ;
V_SAL EMP.SAL%TYPE ;
BEGIN
SELECT ENAME, SAL
INTO V_NAEM, V_SAL
FROM EMP
WHERE EMPNO = &&p_empno ;
DBMS_OUTPUT.PUT_LINE( ‘사번 ‘ ||&p_empno || ‘의 이름은 ‘ || V_NAME ||
‘ , 급여는 ‘ || V_SAL ) ;
END ;
/
SET SERVEROUTPUT OFF SET VERIFY OFF SET ECHO ON
QUIZ:
치환변수 p_empno(사번)에 7654 가 입력되었다면 출력결과는 ?
위의 PL/SQL 블록을 VARIABLE 명렴과 PRINT 명령으로 재 작성하시오.
-
오라클입문(SQL&PL/SQL) 2. PL/SQL BLOCK
Written by AngelaLEE 2 - 26 www.muhanedu.net
3. 부서테이블(DEPT)에 새로운 행을 삽입하는 PL/SQL 블록으로 부서명은 치환변수로 입력받고
부서코드는 기존의 부서코드 중 가장 큰 부서코드보다 10 크게 부여한다.
SET ECHO OFF SET VERIFY OFF
DECLARE
V_DNAME DEPT.DNAME%TYPE := &p_dname ;
V_MAX_DNO DEPT.DEPTNO%TYPE ;
BEGIN
SELECT NVL(MAX(DEPTNO),0) + 10
INTO V_MAX_DNO
FROM DEPT ;
INSERT INTO DEPT(DEPTNO, DNAME)
VALUES( V_MAX_DNO, V_DNAME) ;
COMMIT ;
END ;
/ SET SERVEROUTPUT OFF SET VERIFY OFF SET ECHO ON
QUIZ:
실행부 SELECT리스트에서 NVL 함수의 역할은 ?
DEPT 테이블을 확인하시오. 몇 번의 부서코드가 삽입되었나요 ?
-
오라클입문(SQL&PL/SQL) 2. PL/SQL BLOCK
Written by AngelaLEE 2 - 27 www.muhanedu.net
4. SAMPLE 테이블에 아래와 같은 PL/SQL블록을 실행하면 어떤 결과가 생길까요?
BEGIN
FOR i IN 1.. 10 LOOP
IF i = 6 OR i = 8 THEN
NULL ;
ELSE
INSERT INTO SAMPLE(NO) VALUES ( i ) ;
END IF ;
COMMIT ;
END LOOP ;
END ;
/
* 참고적으로 SAMPLE 테이블을 생성하는 명령은 아래와 같다.
CREATE TABLE SAMPLE ( NO NUMBER(2)) ;
QUIZ:
SAMPLE 테이블에는 몇 건의 데이터가 저장되었나요 ?
IF 문의 NULL; 문장의 의미는 ?
-
오라클입문(SQL&PL/SQL) 2. PL/SQL BLOCK
Written by AngelaLEE 2 - 28 www.muhanedu.net
5. 사번을 입력받아 사원(EMP)테이블에서 그 해당 사원의 이름과 급여와 세금을 출력하는
PL/SQL 블록. (단, 세금은 급여액수에 따라 차등 부여된다고 가정한다.)
SET ECHO OFF SET VERIFY OFF SET SERVEROUTPUT ON DECLARE
V_EMPNO EMP.EMPNO%TYPE := &p_empno; V_ENAME EMP.ENAME%TYPE ; V_SAL EMP.SAL%TYPE ; V_TAX NUMBER ;
BEGIN SELECT ENAME, SAL INTO V_ENAME, V_SAL FROM EMP WHERE EMPNO = V_EMPNO ; IF V_SAL >= 5000 THEN
V_TAX := V_SAL * 0.05 ; ELSIF V_SAL >= 4000 THEN
V_TAX := V_SAL * 0.04 ; ELSIF V_SAL >= 3000 THEN
V_TAX := V_SAL * 0.03; ELSIF V_SAL >= 2000 THEN
V_TAX := V_SAL * 0.02 ; ELSE
V_TAX := V_SAL * 0.01 ; END IF ; DBMS_OUTPUT.PUT_LINE(RPAD(‘EMPNO = ‘,8) || V_EMPNO ||CHR(10) ||
RPAD(‘ENAME = ‘,8) || V_ENAME || CHR(10) || RPAD(‘SAL = ‘,8) || V_SAL || CHR(10) || RPAD(‘TAX = ‘,8) || V_TAX) ; END ; / SET SERVEROUTPUT OFF SET VERIFY OFF SET ECHO ON
-
오라클입문(SQL&PL/SQL) 2. PL/SQL BLOCK
Written by AngelaLEE 2 - 29 www.muhanedu.net
QUIZ:
사번(P_EMPNO)에 7788 이 입력되면 결과는 ?
CHR(10) 함수가 의미하는 것은 무엇이며, RPAD 함수의 용도는 ?
-
오라클입문(SQL&PL/SQL) 2. PL/SQL BLOCK
Written by AngelaLEE 2 - 30 www.muhanedu.net
6. 사원테이블(EMP)에서 해당 사원명의 문자수 만큼 @문자를 출력하는 PL/SQL 블록.
SET ECHO OFF
SET VERIFY OFF
VARIABLE v_name VARCHAR2(40)
VARIABLE v_star VARCHAR2(40)
DECLARE V_EMPNO EMP.EMPNO%TYPE := &p_empno; V_ENAME EMP.ENAME%TYPE ; V_LEN NUMBER(2) ; V_STRING VARCHAR2(40) := ‘’ ;
BEGIN
SELECT ENAME, LENGTH(ENAME)
INTO V_ENAME, V_LEN
FROM EMP
WHERE EMPNO = V_EMPNO ;
FOR n IN 1.. V_LEN LOOP
V_STRING := V_STRING || ‘@’ ;
END LOOP ;
:v_name := V_ENAME ;
:v_star := V_STRING ;
END ;
/
PRINT v_name
PRINT v_star
SET VERIFY OFF
SET ECHO ON
-
오라클입문(SQL&PL/SQL) 2. PL/SQL BLOCK
Written by AngelaLEE 2 - 31 www.muhanedu.net
QUIZ:
사번이 7788 인 사원의 이름과 출력 문자수는 ?
* 참고적으로 이 예제는 뒷부분에서 학습하게 될 서브프로그램의 파라미터와 관
련된 것으로, variable 명령(바인드 변수)과 치환변수의 의미도 충분히 이해하도
록 한다.
-
오라클입문(SQL&PL/SQL) 2. PL/SQL BLOCK
Written by AngelaLEE 2 - 32 www.muhanedu.net
7. 부서코드를 입력받아 해당 부서의 정보를 부서테이블(DEPT)에서 검색하여 화면에 출력하는
PL/SQL블록
SET ECHO OFF
SET VERIFY OFF
SET SERVEROUTPUT ON
DECLARE
V_REC DEPT%ROWTYPE ;
BEGIN
SELECT *
INTO V_REC
FROM DEPT
WHERE DEPTNO = &p_dno ; DBMS_OUTPUT.PUT_LINE(RPAD(‘DEPTNO = ‘,8) || V_REC.DEPTNO ||CHR(10) ||
RPAD(‘DNAME = ‘,8) || V_REC.DNAME || CHR(10) || RPAD(‘LOC = ‘,8) || V_REC.LOC) ;
END ;
/
SET SERVEROUTPUT OFF
SET VERIFY OFF
SET ECHO ON
-
오라클입문(SQL&PL/SQL) 2. PL/SQL BLOCK
Written by AngelaLEE 2 - 33 www.muhanedu.net
QUIZ:
부서코드(p_dno)가 20 이 입력되면 결과는 ?
V_REC 변수의 구조를 그림으로 나타내 보시오.(데이터 형과 크기도 포함)
V_REC 변수를 %ROWTYPE 이 아닌 PL/SQL Record 로 선언한다 가정하고 위
의 블록을 수정해 보시오.
-
오라클입문(SQL&PL/SQL) 2. PL/SQL BLOCK
Written by AngelaLEE 2 - 34 www.muhanedu.net
8. 5개의 수와 해당 수의 짝/홀수 구분을 PL/SQL Table에 저장한 후 화면에 일괄적으로 출력하
는 PL/SQL 블록.
SET ECHO OFF SET VERIFY OFF SET SERVEROUTPUT ON DEFINE V_NUM1 = 10 DEFINE V_NUM2 = 34 DEFINE V_NUM3 = 1 DEFINE V_NUM4 = 77 DEFINE V_NUM5 = 58 DECLARE
TYPE NUM_TABLE_TYPE IS TABLE OF NUMBER(3) INDEX BY BINARY_INTEGER ; TYPE GUBUN_TABLE_TYPE IS TABLE OF VARCHAR2(4) INDEX BY BINARY_INTEGER ; NUM_TAB NUM_TABLE_TYPE ; GUBUN_TAB GUBUN_TABLE_TYPE ;
BEGIN NUM_TAB(1) := & V_NUM1 ; NUM_TAB(2) := & V_NUM2 ; NUM_TAB(3) := & V_NUM3 ; NUM_TAB(4) := & V_NUM4 ; NUM_TAB(5) := & V_NUM5 ; FOR n IN 1.. 5 LOOP IF MOD(NUM_TAB(n),2) = 0 THEN GUBUN_TAB(n) := ‘EVEN’ ; ELSE GUBUN_TAB(n) := ‘ODD’ ; END IF; END LOOP ; FOR n IN 1.. 5 LOOP
DBMS_OUTPUT.PUT_LINE(RPAD(NUM_TAB(n),6)||GUBUN_TAB(n)) ; END LOOP ;
END ; / SET SERVEROUTPUT OFF SET VERIFY OFF SET ECHO ON
-
오라클입문(SQL&PL/SQL) 2. PL/SQL BLOCK
Written by AngelaLEE 2 - 35 www.muhanedu.net
QUIZ:
결과를 확인하고, NUM_TAB과 GUBUN_TAB의 내부구조를 그림으로 표현하시오.