Microsoft Word - 02_PLSQL_BLOCK_STRUCTURE.doc

Size: px

Start display at page:

Download "Microsoft Word - 02_PLSQL_BLOCK_STRUCTURE.doc"

민정 난
5 years ago
Views:

1 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( 실행부, 필수요소 ) EXCEPTION 1 ( 예외처리부, 생략가능 ) END ; ( 블록의끝을의미, 필수요소 ) 블록내의각부분에포함되는명령들은세미콜론 (;) 으로문장이끝난다. 블록이성공적으로처리가완료되면, 아래와같은메시지가출력될것이다. PL/SQL procedure successfully completed. ( 이결과메시지를여러분들은자주보기를바란다. ^.*) 1 예외 (EXCEPTION) 은 PL/SQL 에서발생되는에러 (ERROR) 를일컫는다. Written by AngelaLEE 2-1

2 PL/SQL 블록 기본규칙과지침사항 문장은여러줄에걸쳐질수있으나, 키워드는분리될수없다. 블록의내용을읽기쉽도록공백문자를사용하여키워드내지는문장을적절하게분리함으로써의미분석이되도록하며, 들여쓰기도권장한다. 예약어는식별자명으로사용될수없으나, Alias 로는사용될수있다. 즉이중부호 ( ) 를함께사용할수는있다. ( 예 : TABLE ) 식별자명은기본오라클 Naming Rule 을준수한다. 리터럴 ( 문자, 날짜 ) 은단일인용부호 ( ) 로둘러싼다. 널값은 NULL 상수로표현한다. NULL 에대한비교는 IS (NOT) NULL 연산자를사용해야한다. ( 참고적으로 ) 조건문이나예외처리의경우해당조건에대해아무런처리도하지않을경우 NULL ; 문장을사용하기도한다. 주석처리를하고자할경우에단일행은 - - ( 하이픈두개 ), 복수행은 /* 로시작하고 */ 로종료한다. PL/SQL 블록내의명령 ( 수식 ) 에서는오라클함수를사용할수있다. 단, 그룹함수와 DECODE 함수는 SQL 문장에포함되어야만사용될수있다. PL/SQL 에서는에러 (EXCEPTION) 처리와관련된함수에는 SQLCODE 함수와 SQLERRM 함수가있다 ( 뒷부분에서언급할것임.) 일반적으로변수의생명주기는블록내부의범위이다. 예를들어, 블록을포함하는블록의경우에바깥쪽블록에서선언된변수는포함된블록내부에서도참조될수있다. 그러나만약내부블록에바깥쪽블록에서선언된변수와동일명의변수가있었다면, 이때변수의참조는지역변수 (Local) 가우선한다. 만약블록레이블을사용했다면바깥쪽변수를동일명의변수가있는내부블록에서참조할수있다 ( 레이블을변수명앞에접두어로붙이면된다.). 물론내부블록의변수 ( 지역변수 ) 는바깥쪽블록에서참조할수없다. Written by AngelaLEE 2-2

3 << main >> DECLARE ANGEL CHAR; BEGIN DECLARE ANGEL CHAR; BEGIN main.angel := A ; END; END; PL/SQL 블록내에서사용되는연산자는 SQL 에서허용하는연산자와동일하며, 지수연산자로 ** 을추가로제공한다. ** 가가장높은우선순위를가지며, SQL 에서의우선순위와동일하게처리된다. Written by AngelaLEE 2-3

4 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_INTEGER 1 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 값을가진다. Written by AngelaLEE 2-4

5 Composite( 레코드, 배열 ) Reference( 포인터 ) 기타변수 (non-pl/sql 변수 ) LOB(Large Objects) 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 CONSTANT Datatype 변수의이름으로식별자의역할을한다. 블록내에서는유일해야한다. 오라클 Naming Rule 에합당한이름을지정해야한다. 선언되는변수의유형을직관적으로이해할수있도록의미있는접두어나접미어를사용하도록권장한다. 읽기전용변수즉상수로선언하기위한키워드로기본적으로초기값이반드시지정되어야한다.( 생략가능 ) 오라클에서허용하는데이터타입은모두지원한다. 또한 %TYPE 을사용하여테이블내의컬럼과동일한데이터형을선언할수도있고, %ROWTYPE 을사용하여테이블의레코드구조와동일형태의레코드를선언할수 Written by AngelaLEE 2-5

6 도있다. NOT NULL 이변수는항상값을가지도록제약을주는키워드로초기값이반드시지정되어야한다.( 생략가능 ) := DEFAULT 변수에기본값을부여하기위한키워드이다. expr 변수에부여할기본값을의미하며단순한값에서부터다른변수, 수식, 함수가올수있다. > EX: V_EMPNO V_DATE V_NAME NUMBER(3); DATE DEFAULT SYSDATE;; VARCHAR2(14) NOT NULL := Angela Lee ; C_RATE CONSTANT NUMBER := 0.2; V_SAL V_REC EMP.SAL%TYPE; DEPT%ROWTYPE; ( 이때, V_REC 변수는복합구조를가지게되며 DEPT 테이블내의컬럼정의도그대로내포하고있으므로, 값을참조할때는 V_REC.DEPTNO 와같이 V_REC을접두어로붙여야한다.) V_SIGNAL BOOLEAN := FALSE; 복합변수의선언 > SYNTAX(PL/SQL RECORD): * PL/SQL Record 정의와선언 1 TYPE type_name IS RECORD ( field_declaration[, field_declaration] ) ; 2 Identifier type_name 1 정의부분 : Written by AngelaLEE 2-6

7 type_name 은 RECORD 유형의이름으로일반적인프로그래밍언어에서는구조체로통용되기도한다. 여러가지유형의변수가하나의레코드 ( 구조체 ) 단위로서처리되며, 레코드내의변수 ( 필드 ) 를참조할경우에는 type_name.field_name 을사용한다. ( 참고적으로테이블내의행구조와동일하게레코드변수를선언하고자할경우에는 %ROWTYPE 키워드를사용한다.) Field_declaration 은일반변수의선언과동일한문법형태를지닌다. 2 선언부분 : 기본적으로복합형의데이터는우선정의를하고해당정의를통해실제복합변수를선언하는단계로구성된다. 이선언부분에서는실제복합변수에대한기억공간이확보되는시점이다. ( 정의부분에서는메모리에공간이확보되지는않으며단지복합데이터형에대한기술이이루어지는부분이다.) > 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) ;... DEPT_REC DNO DNAME END; Written by AngelaLEE 2-7

8 > SYNTAX(PL/SQL TABLE): * PL/SQL Table 정의와선언 1 TYPE type_name IS TABLE OF {column_type variable%type table.column%type} [NOT NULL] table%rowtype [INDEX BY BINARY_INTEGER] ; 2 Identifier type_name 1 정의부분 : type_name 은 PL/SQL Table 유형의이름으로일반적인프로그래밍언어에서는배열로통용되기도한다. 동일한유형의데이터 ( 또는데이터구조 ) 들을하나의연속적인메모리공간에확보하기위한방법을제공한다. INDEX BY 절은그배열내의요소 (element) 에접근하기위한첨자 ( 위치 ) 값으로사용되며, 범위는 BINARY_INTEGER 의범위에속한다. 2 선언부분 : 기본적으로복합형의데이터는우선정의를하고해당정의를통해실제복합변수를선언하는단계로구성된다. 이선언부분에서는실제복합변수에대한기억공간이확보되는시점이다. ( 정의부분에서는메모리에공간이확보되지는않으며단지복합데이터형에대한기술이이루어지는부분이다.) Written by AngelaLEE 2-8

9 > 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 ; SAL_TAB S_DATE STARTDATE_TAB_TYPE ; (1) (2)... BEGIN... SAL_TAB(1) := 3500 ; S_DATE(1) := SYSDATE ;... IF SAL_TAB(1).EXISTS(1) THEN END IF ;... END; Written by AngelaLEE 2-9

10 * 오라클에서는 PL/SQL TABLE 의조작을위한 Built-ins(Method) 를제공하고있다. 이때의프로시저나함수는 PL/SQL TABLE명과함께사용된다. Built-in(Method) EXISTS(n) COUNT FIRST, LAST PRIOR(n) NEXT(n) TRIM DELETE Description N 번째요소의값이존재하면 TRUE 현재존재 ( 사용 ) 하는요소들의개수 현재사용되는첨자의첫번째, 마지막값을리턴. 배열요소의위치를조정하는함수로 n 개이전의인덱스첨자값리턴 배열요소의위치를조정하는함수로 n 개다음의인덱스첨자값리턴 PL/SQL TABLE 의마지막요소를제거 (TRIM(n) 은마지막으로부터 n 개제거 ) 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 Written by AngelaLEE

11 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블록내에서는어 Written by AngelaLEE

12 떤제약도없이사용할수있다. 즉원래그대로사용이가능하다는것이다. 단, 데이터제약조건을위배하는 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블록에서간접적으로사용될수있기는하나거의사용될경우는없다. Written by AngelaLEE

13 제어문장 ( Control Structures) 오라클 PL/SQL에서는일반프로그래밍언어가가지는기본적인제어문을제공하며이제어문들은제어문들을포함할수있다.(Nested) 오라클이제공하는제어문의종류는아래와같다. 제어문 조건문 IF 문 CASE 문 반복문 BASIC LOOP 문 WHILE 문 FOR 문 Written by AngelaLEE

14 조건문 (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) 조건처 Written by AngelaLEE

15 리를위해한 if문장내에서여러번사용될수있다. ( 참고적으로 ELSIF의철자에주의하도록하자.) * ELSE IF문이하모든조건들에만족되지않는경우에수행되는부분으로, IF문에단한번마지막에올수있다. * END IF ; IF문의끝을알리는절로 END와 IF 사이에는하나이상의공백을두어야한다. Written by AngelaLEE

16 > 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 END IF; V_TAX_RATE := 0.05 ; < 참고 : IF_THEH_ELSE 문의흐름 > TRUE Condition 1 FALSE,NULL TRUE Condition 2 FALSE,NULL Statement 1 Statement 2 Statement 3 Written by AngelaLEE

17 조건문 (CASE 문 ) : 조건처리를위한다른방법을제공하는제어문이다. 오라클의 CASE 함수와비슷한로직으로처리된다. > SYNTAX: CASE selector WHEN expression1 WHEN expression2 WHEN expression N [ELSE resultn + 1 ; ] END ; THEN result1 THEN result2 THEN resultn > EX: SET SERVEROUTPUT ON DECLARE V_CHAR CHAR := UPPER( &p_char) ; V_MSG VARCHAR2(26) ; BEGIN END ; / 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) ; Written by AngelaLEE

18 반복문 (BASIC LOOP 문 ) : PL/SQL블록내에서반복처리해야명령이있는경우에사용되는문으로, 일반프로그램언어의 DO~WHILE문과일맥상통하는문이다. 반복문안에있는문장 ( 들 ) 을한번이상은수행하도록하는명령으로반복허용조건이만족하는동안반복수행되도록할수있다. > SYNTAX: LOOP Statement1 ; END LOOP ; EXIT [ WHEN condition ] ; * EXIT 기본루프 (BASIC LOOP) 를빠져나오는문으로 END LOOP ; 다음문장으로제어가넘어간다. WHEN 절이생략되면무조건루프를빠져나간다. * WHEN 기본루프 (BASIC LOOP) 종료하기위한조건을지정하는문으로해당조건을만족하면루프를빠져나간다. 만약조건을만족하지않는다면 LOOP ~ END LOOP; 사이의문장을반복하게된다. Written by AngelaLEE

19 > EX: SET SERVEROUTPUT ON DECLARE V_START NUMBER := &p_start ; V_END NUMBER := &p_end ; V_NUM NUMBER ; V_SUM NUMBER := 0 ; -- Recommanded BEGIN END ; / 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) ; QUIZ: 위의블록에서 p_start, p_end 에각각 1,10 이입력되었다면화면의결과는? 만약, p_start, p_end 에각각 10,5 가입력되었다면결과는? Written by AngelaLEE

20 반복문 (WHILE 문 ) : WHILE 문은 PL/SQL 블록내에서반복처리를수행하는문장으로, 조건이만족하는동안에는루프안의명령들을반복적으로수행하고, 조건이만족하지않을경우해당반복루프를종료한다. 이 WHILE문의경우조건이처음부터만족되지않으면, BASIC LOOP문과는다르게루프안의명령 ( 들 ) 이한번도수행되지못할수도있다. > SYNTAX: WHILE condition LOOP END LOOP ; Statement1 ; Statement2 ; * condition 반복문을종료하기위한조건절로조건이만족되는동안 (true) 에루프안의문장들을반복수행하고, 조건이불만족되면 (false, null) 루프는종료된다. ( 제어는 end loop; 문다음으로넘어간다.) Written by AngelaLEE

21 > EX: SET SERVEROUTPUT ON DECLARE V_START NUMBER := &p_start ; V_END NUMBER := &p_end ; V_NUM NUMBER ; V_SUM NUMBER := 0 ; BEGIN END ; / V_NUM := V_START ; WHILE V_NUM <= V_END LOOP V_SUM := V_SUM + V_NUM ; V_NUM := V_NUM + 1 ; END LOOP ; DBMS_OUTPUT.PUT_LINE(V_SUM) ; QUIZ: 위의블록에서 p_start, p_end 에각각 15,20 이입력되었다면화면의결과는? 만약, p_start, p_end 에각각 100,50 이입력되었다면결과는? Written by AngelaLEE

22 반복문 (FOR 문 ) : FOR문은일정한반복회수로처리하고자할경우유용한반복문이다. FOR문의반복을제어하는변수는선언부 (DECLARE) 에서선언하지않아도무방하다. 왜냐하면오라클은제어변수에대해서는묵시적으로정수형 (INTEGER) 선언을해준다. > SYNTAX: FOR counter IN [REVERSE] start.. end LOOP END LOOP ; Statement1 ; Statement2 ; * counter 반복문을제어하는정수형변수로서묵시적선언이이루어진다. Start 값에서부터 end 값까지의제어범위를가진다. 기본적인증감값은 1 이다. 이변수의생명주기는반복문 (for) 안에서만유효하며, 반복문안에서변경될수없다. * REVERSE 이는제어변수 (counter) 의값이감소하도록하는키워드이다. 주의해야할것은 REVERSE 를사용해도항상 start <= end 이어야한다는것이다. REVERSE를사용하면 end 값에서 1 씩감소하여 start값까지수행된다. Written by AngelaLEE

23 > EX: SET SERVEROUTPUT ON DECLARE V_START NUMBER := &p_start ; V_END NUMBER := &p_end ; -- V_NUM NUMBER ; // 선언하지않음. V_SUM NUMBER := 0 ; BEGIN END ; / FOR V_NUM IN V_START.. V_END LOOP V_SUM := V_SUM + V_NUM ; END LOOP ; DBMS_OUTPUT.PUT_LINE(V_SUM) ; Written by AngelaLEE

24 실습 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 END ; / SET SERVEROUTPUT OFF SET VERIFY OFF SET ECHO ON QUIZ: 의합은 V_SUM ) ; 치환변수 p_su1 과 p_su2 에각각 10.2 와 이입력된다면출력결과는? Written by AngelaLEE

25 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 명령으로재작성하시오. Written by AngelaLEE

26 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 FROM DEPT ; V_MAX_DNO INSERT INTO DEPT(DEPTNO, DNAME) COMMIT ; END ; VALUES( V_MAX_DNO, V_DNAME) ; / SET SERVEROUTPUT OFF SET VERIFY OFF SET ECHO ON QUIZ: 실행부 SELECT 리스트에서 NVL 함수의역할은? DEPT 테이블을확인하시오. 몇번의부서코드가삽입되었나요? Written by AngelaLEE

27 4. SAMPLE 테이블에아래와같은 PL/SQL 블록을실행하면어떤결과가생길까요? BEGIN FOR i IN 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; 문장의의미는? Written by AngelaLEE

28 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 Written by AngelaLEE

29 QUIZ: 사번 (P_EMPNO) 에 7788 이입력되면결과는? CHR(10) 함수가의미하는것은무엇이며, RPAD 함수의용도는? Written by AngelaLEE

30 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 := ; END LOOP ; :v_name := V_ENAME ; :v_star := V_STRING ; END ; / PRINT v_name PRINT v_star SET VERIFY OFF SET ECHO ON Written by AngelaLEE

31 QUIZ: 사번이 7788 인사원의이름과출력문자수는? * 참고적으로이예제는뒷부분에서학습하게될서브프로그램의파라미터와관련된것으로, variable 명령 ( 바인드변수 ) 과치환변수의의미도충분히이해하도록한다. Written by AngelaLEE

32 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 Written by AngelaLEE

33 QUIZ: 부서코드 (p_dno) 가 20 이입력되면결과는? V_REC 변수의구조를그림으로나타내보시오.( 데이터형과크기도포함 ) V_REC 변수를 %ROWTYPE 이아닌 PL/SQL Record 로선언한다가정하고위의블록을수정해보시오. Written by AngelaLEE

34 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 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 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 Written by AngelaLEE

35 QUIZ: 결과를확인하고, NUM_TAB 과 GUBUN_TAB 의내부구조를그림으로표현하시오. Written by AngelaLEE

Microsoft Word - 05_SUBPROGRAM.doc

Microsoft Word - 05_SUBPROGRAM.doc ORACLE SUBPROGRAM INTRODUCTION PLSQL 은오라클에서제공하는프로그래밍언어이다. 이는데이터베이스언어인 SQL 과함께효과적으로데이터베이스에접근할수있는방법을제공하고있다. Procedural LanguageSQL 의약자에서볼수있듯이절차적인기능을기본적으로가지는프로그래밍언어이다. PLSQL 은기본적으로블록 (BLOCK) 구조를가지고있다. 블록의기본적인구성은선언부