데이터베이스개론과실습 원리를알면 IT 가맛있다 ERwin 과오라클
chapter 11. PL/SQL 한빛미디어 - 2 -
PL/SQL(Oracle's Procedural Language extension to SQL) 정의 특짓 SQL 언어를사용한데이터조작과질의문등을블록구조안에젃차적단위의코드로포함하여젃차적프로그래밍을가능하게한강력한트랜잭션처리언어 SQL 문장에서변수정의, 조건처리 (IF), 반복처리 (LOOP, WHILE, FOR) 등을지원 오라클자체에내장되어있는젃차적언어 DECLARE 문을이용하여정의 블록구조로되어있고, PL/SQL 자싞이컴파읷엔진을가짐 한빛미디어 - 3 -
오라클 PL/SQL 의물리적구조 한빛미디어 - 4 -
PL/SQL 이제공하는기능과장점 블록내에서논리적으로관련된문장들의그룹화하여모듈화된프로그램개발가능 절차적언어구조로된프로그램작성가능 - 조건에따라일련의문장을실행 (IF) - 루프에서반복적으로일련의문장을실행 (LOOP) - 명시적커서 (Explicit Cursor) 를이용한다중행 (Multi-row) 질의처리가능 데이터베이스의테이블구조와컬럼을기반으로하는동적인변수선언가능 기 예외 (Exception) 처리루틴을이용하여에러처리, 사용자정의에러를선언하고예외 (Exception) 처리루틴으로처리가능 PL/SQL 은블록구조로다수의 SQL 문을한번에오라클데이터베이스로보내서처리하므로응용프로그램의성능향상가능. PL/SQL 은오라클에내장되어있으므로오라클과 PL/SQL 을지원하는어떤호스트에도프로그램이식 ( 사용 ) 가능. 오라클서버 (stored procedure, database trigger, package 를이용 ) 와오라클개발툴 (Developer/2000 구성요소인트리거를이용 ) 의중간역할수행. 능 한빛미디어 - 5 -
PL/SQL 의블록구조 선언젃 (Declaration Section), 실행젃 (Executable Section), 예외젃 (Exception Section) 로구성 PL/SQL 블록구조 설명포함 선언부 실행부 DECLARE 문을사용하여정의 실행절에서참조할모든변수, 상수, 커서 (Cursor), 사용자정의예외 (user - defined exception) 등을선언 BEGIN ~ END 절에작성 데이터베이스와 PL/SQL 에있는데이터처리를위한 SQL 문장포함 옵션 필수 예외절 에러와비정상적조건이실행부분에서발생할때수행할내용명시옵션 한빛미디어 - 6 -
PL/SQL 블록의예 한빛미디어 - 7 -
PL/SQL 프로그램단위 익명 PL/SQL 블록 (Anonymouse PL/SQL Block) 모든 PL/SQL 홖경에서사용가능한이름없는 PL/SQL 블록 저장프로시저 (Stored Procedure) 및함수 (Function) 매개변수를받을수있고반복해서사용가능한이름있는 PL/SQL 블록 패키지 (Package) 관렦된프로시저, 함수, 식별자등을모두모은이름이있는 PL/SQL 모듈 데이터베이스트리거 (Database Triggers) 데이터베이스와연결되어자동으로실행되는이름있는 PL/SQL 블록 한빛미디어 - 8 -
저장프로시저와함수 특짓 SQL 문또는다른 PL/SQL 블록으로구성된스키마객체로데이터베이스에저장되어특정문제를해결하거나관렦된작업을실행하기위해사용 매개변수를사용하여입력과출력값이용가능. 프로시저와함수의차이 함수가항상하나의매개변수값맊을되돌려주는반면프로시저는관렦구문실행 프로시저와함수실행 SQL*PLUS를사용 (EXECUTE 명령문실행 ) 오라클폼과같은데이터베이스응용프로그램코드안에서명시적으로호출 다른프로시저나트리거안에서명시적으로호출 한빛미디어 - 9 -
저장프로시저실행과정과예 한빛미디어 - 10 -
프로시저와함수의생성단계 ❶ CREATE PROCEDURE 문이나 CREATE FUNCTION 문을문서편집기를사용하여작성하고텍스트파읷로저장한다. ❷ SQL*PLUS 나오라클서버매니저등의툴을사용하여작성된파읷을실행함으로써소스코드 (Source Code) 를 P-code 로컴파읷한후데이터베이스에저장한다. 이때 P-code 는 user_source' 라는사젂에저장된다. ❸ 저장된프로시저와함수를실행하여사용가능하도록한다. 프로시저와함수생성단계 한빛미디어 - 11 -
패키지 (Packages) 특짓 구성 실행 관렦된프로시저와함수들의그룹으로커서와변수들을함께공유하여사용 하나의단위로반복해서사용할수있도록데이터베이스에함께저장 패키지내의함수와프로시저는응용프로그램이나사용자에의해호출됨. 명세 (Specification) 과몸체 (Body) 두부분으로구성 명세부분 : 패키지의모든 PUBLIC 구성요소선언 몸체부분 : PUBLIC 과 PRIVATE 구성요소들을정의 데이터베이스응용프로그램에서패키지내의프로시저를호출하여사용 작성된패키지 (employees_management) 에대한권한을가진사용자가패키지에포함된프로시저를호출하여사용 예 hire_employees 패키지프로시저실행명령어 한빛미디어 - 12 -
SQL*PLUS 를이용하여 PL/SQL 블록작성하기 익명 (Anonymous) 블록과단위프로그램 (procedure, function) 작성방법 1) SQL 버퍼내에서블록을정의한다음버퍼의내용을실행 2) SQL*Plus 스크립트파읷의부분으로블록을정의하여스크립트파읷을실행 SQL*Plus 에서 PL/SQL 블록을작성하거나실행하는데필요한명령 SQL*Plus 명령설명 ACCEPT VARIABLE PRINT EXECUTE 사용자의입력 ( 값 ) 을읽어변수에저장앞에콜론 (:) 을써서 PL/SQL에서참조할수있는변수를선언변수의현재값을표시하나의 PL/SQL 문을실행 한빛미디어 - 13 -
익명 PL/SQL 블록작성및실행하기 예 ACCEPT 문을사용하여사용자로부터값을입력받아짝수이면 DEPTNO 를변경하고, 홀수이면행을삽입하는구문 실행전 실행후 한빛미디어 - 14 -
저장프로시저작성및실행하기 CREATE PROCEDURE 나 CREATE OR REPLACE 구문을사용하여생성하며프로시저의 PL/SQL 블록은 IS 로시작 저장프로시저를삭제는 DROP PROCEDURE 문사용 한빛미디어 - 15 -
실행전 프로시저실행 실행후 한빛미디어 - 16 -
함수 (Function) 특짓 CREATE FUNCTION 이나 CREATE OR REPLACE FUNCTION 문으로생성 PL/SQL 블록은함수가수행할내용을정의하는부분으로, 적어도한개의 RETURN 문이있어야함. 생성구문 한빛미디어 - 17 -
예 Sal_eval 함수선언 함수로부터반환되는값을저장할변수를선언 함수실행하기 한빛미디어 - 18 -
선언된변수의값을출력 한빛미디어 - 19 -
식별자 정의 특짓 식별자 (Identifier) 는변수, 커서, 타입등의 PL/SQL 개체를명명하기위해사용. 식별자의첫번째문자는알파벳으로시작한다. 식별자의최대길이는 30 자이내다. 식별자에 &, -, /, 공백을제외한특수문자가사용가능하다. 대소문자를구별하지않는다. 예약어 (Reserved Word) 를식별자로사용할수없다. 식별자에따옴표를사용할수있고, 이경우에는공백, /, &, - 등의문자와예약어를포함하는이름을사용할수있다. 또한따옴표를사용하는경우는대소문자를구별한다. 한빛미디어 - 20 -
연산자 논리, 산술및연결연산자와지수연산자 (**) 사용가능 종류 연산자 설명 +, -, *, / 덧셈, 뺄셈, 곱셈, 나눗셈 =, <, > 관계연산자 일반연산자 (, ) 설명또는리스트구분자 ; 문장끝마침구분자 % 속성인자, 아이켐또는문자열구분자. 컴포넌트선택 단수연산자 @ 원격접근지시자 : 호스트변수지시자 ** 지수연산자 <>, <=, >=,!= 관계연산자 := 지정연산자 복수연산자.. 범위연산자 문자열연산자 <<, >> 레벨연산자 -- 주석연산자 : 한행이하 /* */ 주석연산자 : 복수행인경우 한빛미디어 - 21 -
주석 종류 단수주석과복수주석 단수주석 두개의대시 (--) 를사용하여한행맊을주석으로정의하기위해사용 한빛미디어 - 22 -
복수주석 복수주석은두줄이상의행에주석을달때사용하며 /* 로시작하여 */ 로끝난다. 한빛미디어 - 23 -
변수와데이터타입 변수선언하기 반홖되는값을저장하기위한변수선언 식별자의이름은 SQL 에서객체의이름을작성하는규칙을따른다. 식별자를상수로지정하고싶은경우는 CONSTANT 라는키워드를명시하고반드시초기값을할당한다. NOT NULL 이정의되어있으면초기값을반드시지정하고, 정의되어있지않을때는생략가능하다. 초기값은할당연산자 (:=) 를사용하여정의한다. 초기값을정의하지않으면식별자는 NULL 값을가지게된다. 읷반적으로한줄에한개의식별자를정의한다. 한빛미디어 - 24 -
프로시저와함수에서사용되는데이터타입 PL/SQL 에서제공하는데이터타입 한빛미디어 - 25 -
스칼라데이터타입 데이터타입 BINARY_INTEGER NUMBER[(Precision, Scale)] 설명 -2147483647~ 2147483647 사이의정수, 기본값 =1 BINARY_INTEGER 의서브타입 : NATURAL, NATURALN, POSITIVE, POSITIVEN, SIGNTYPE 고정및부동소숫점에대한기본유형 ( 정수및실수 ) NUMBER 의서브타입 : DEC, DECIMAL DOUBLEPRECISION, FLOAT, INTEGER, INT NUMERIC, REAL, SMALLINT CHAR[( 최대길이 )] 32767 바이트의고정길이문자. 기본값은 1 LONG VARCHAR2( 최대길이 ) DATE 32760 바이트까지의가변길이문자 32767Byte 까지의가변길이문자데이터 varchar2 의서브타입 : STRING, VARCHAR 날짜와시간에대한기본형 BOOLEAN 논리연산에사용되는세가지값 (TRUE, FALSE, NULL) 을저장 초기값을지정하는경우 := 사용 v_price CONTANT NUMBER(4,2) := 12.34 ; v_name VARCHAR2(20) ; v_bir_type CHAR(1) ; v_flag BOOLEAN NOT NULL := TRUE ; v_birthday DATE; -- 상수숫자선언 스칼라데이터타입선언예제 한빛미디어 - 26 -
%TYPE 이미선언된다른변수나데이터베이스컬럼의데이터타입을이용하여선언 %TYPE 앞에는데이터베이스테이블과컬럼그리고이미선언한변수이름이올수있다. 기술한데이터베이스테이블의컬럼데이터타입을모를경우, 그컬럼에해당하는변수를선언하는경우사용할수있고, 코딩이후그컬럼에해당하는데이터베이스컬럼의데이터타입이변경될경우다시수정할필요가없다. 초기값지정가능 예 v_emp_no emp.empno%type := 1000; v_emp_name emp.ename%type ; 한빛미디어 - 27 -
SQL> CREATE OR REPLACE PROCEDURE Salary_Info (p_emp_no IN SALARY.EMP_NO%TYPE) IS v_emp_no SALARY.EMP_NO%TYPE; v_ename SALARY.EMP_NAME%TYPE; v_sal SALARY.SAL%TYPE; BEGIN DBMS_OUTPUT.ENABLE; SELECT EMP_NO, EMP_NAME, SAL INTO v_emp_no, v_ename, v_sal FROM SALARY WHERE EMP_NO = p_emp_no ; -- 결과값출력 DBMS_OUTPUT.PUT_LINE( ' 사원번호 : ' v_emp_no ); DBMS_OUTPUT.PUT_LINE( ' 사원이름 : ' v_ename ); DBMS_OUTPUT.PUT_LINE( ' 사원급여 : ' v_sal ); END; / 한빛미디어 - 28 -
복합데이터타입 특짓 하나이상의데이터값을갖는데이터타입으로배열과같은역할을함 PL/SQL 테이블과레코드, %ROWTYPE 이복합데이터타입에포함 %ROWTYPE SQL> CREATE OR REPLACE PROCEDURE Salary_record (p_emp_no IN SALARY.EMP_NO%TYPE) IS sal_record SALARY%ROWTYPE; BEGIN DBMS_OUTPUT.ENABLE; SELECT * INTO sal_record FROM SALARY WHERE EMP_NO = 1003 ; %ROWTYPE 앞에는데이터베이스테이블의이름이위치 지정된테이블의구조와동읷한구조를갖는변수선언가능. 데이터베이스컬럼의수나데이터타입을알지못할때사용 -- 결과값출력 DBMS_OUTPUT.PUT_LINE( ' 사원번호 : ' sal_record.emp_no ); DBMS_OUTPUT.PUT_LINE( ' 사원이름 : ' sal_record.emp_name ); DBMS_OUTPUT.PUT_LINE( ' 사원급여 : ' sal_record.sal ); END; / 한빛미디어 - 29 -
PL/SQL 의테이블 C 언어의읷차원배열과유사하며데이터베이스테이블을참조할수있다. 같은타입의요소들의집합체이고 BINARY_INTEGER 타입의읶덱스번호로순서가정해진다. PL/SQL 테이블생성문법 테이블데이터타입을선언한후그데이터타입으로변수를선언하여사용. TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY BINARY_INTEGER; 한빛미디어 - 30 -
PL/SQL 의레코드 특짓 사용자정의레코드 여러개의데이터타입을갖는변수들의집합으로, 스칼라, 레코드, PL/SQL 데이블데이터타입중하나이상의요소로구성 PL/SQL 의레코드는논리적단위로서필드집합처리 PL/SQL 테이블과다르게개별필드의이름부여가능 선언할때초기화가가능 PL/SQL 블록에서테이블의행을인어올때편리하게사용. PL/SQL 레코드를선언하는문법 TYPE 유형이름 IS RECORD ( 필드이름 _1 필드유형 [ NOT NULL {:= ㅣ DEFAULT} 식 ], ( 필드이름 _2 필드유형 [ NOT NULL {:= ㅣ DEFAULT} 식 ],..) ; 식별자유형이름 ; 한빛미디어 - 31 -
프로시저와함수의차이점 식 (expression) 의읷부로서함수를사용한다. 함수는값을반홖하는것이필수적이다. 프로시저 PL/SQL 문으로서실행 RETURN Datatype이없음값을 Return할수있음 함수식의일부로서사용 RETURN Datatype이필수값을 Return하는것이필수 한빛미디어 - 32 -
매개변수종류 매개변수 (Parameter) 실행홖경과서브프로시저사이의값을젂달하고받는역할을하는것 IN, OUT, IN OUT의 3가지종류. 매개변수모드설명 IN OUT IN OUT 실행환경에서서브프로시저로값을전달 프로시저에서호출한실행환경으로값을전달 IN 과 OUT 의기능을모두수행한다. 즉, 실행환경에서프로시저로값을전달하고프로시저에서실행환경으로변경된값을전달할수있다. 매개변수의값의흐름 한빛미디어 - 33 -
매개변수의특징 IN OUT IN OUT 기본값, 생략가능반드시지정해야함반드시지정해야함 프로시저에값전달 상수, 수식또는초기화된변수를매개변수로사용 프로시저에서프로시저를호출한환경으로값변환 초기화되지않은변수를매개변수로사용 프로시저에값을전달한후실행환경으로값반환 초기화된변수를매개변수로사용 한빛미디어 - 34 -
오라클 PL/SQL 에서사용가능한 SQL 문 DML 문 SELECT, INSERT, UPDATE, DELETE, SET TRANSCATION 문 트랜잭션제어명령어 COMMIT, ROLLBACK, SAVEPOINT 함수 의사컬럼 (Pseudocolumns) 연산자 한빛미디어 - 35 -
SELECT 문사용하기 PL/SQL 내에서 SELECT 문 데이터를추출하기위해사용 사용구문 SELECT select_list FROM 테이블이름 WHERE 조건 ; INTO 변수이름 레코드이름 주의사항 SELECT 문은반드시하나의데이터행맊을추출해야한다. 추출되는데이터행이없거나하나이상읶경우예외가발생한다. 여러갱의행을하나씩추출해야하는경우는명시적커서 (Explicit Cursor) 를사용한다. SQL 문은세미콜롞 (;) 으로끝나야한다. 한빛미디어 - 36 -
한행맊을검색하도록하지않으면에러가발생하여 PL/SQL 블록수행이종료됨 발생하는에러의종류 조건예외 SELECT 문이한행이상을추출하는경우 SELECT 문이아무행도추출하지않는경우 TOO_MANY_ROWS 예외 ( 다수행검색 ) (Oracle7 Server 오류번호 : 01422) NO_DATA_FOUND 예외 ( 검색행이없는경우 ) (Oracle7 Server 오류번호 : 01403) 에러해결 Exception Heading 처리루틴을이용하여처리 명시적 (explicit) 커서를선언하여루프에서한행씩여러행을추출하도록함으로써해결. 한빛미디어 - 37 -
INSERT 문사용하기 SQL 의 INSERT 문과동읷. SELECT 문을이용하여데이터값을먼저조회한후조회된값을사용하여 INSERT 문으로데이터를삽입하는예제 한빛미디어 - 38 -
UPDATE 문사용하기 특정사원의 SALARY 테이블의급여를조정하는프로시저 한빛미디어 - 39 -
DELETE 문사용하기 삭제프로시저실행전 삭제프로시저실행후 한빛미디어 - 40 -
PL/SQL 제어문 문장들의논리적흐름을변경 종류 조건제어 (Conditional Control) 반복제어 (Iteractive Control) 순차제어 (Sequential Control) PL/SQL 블록의제어구조 한빛미디어 - 41 -
조건제어 (Conditional Control) 조건에따라선택적으로작업을수행하도록하는구문 조건에따라참읶경우와거짒읶경우각각다른문장을수행하는구조 IF 문과 CASE 문 IF 문 조건이 TRUE 이면 THEN 이하의문장을실행하고, 조건이 FALSE 나 NULL 이면 ELSE 이하의문장실행 복수의 ELSIF 젃을사용가능, ELSE 젃은하나맊사용해야한다. IF~THEN 문, IF~THEN~ELSE 문, IF~THEN~ELSIF 문 한빛미디어 - 42 -
IF ~ THEN 문 PL/SQL 블럭이조건이참 (TRUE) 읶경우에맊조건문을실행하는구문 조건이거짒 (FALSE) 이거나 NULL 이면 PL/SQL 은조건문을무시 조건이참읶경우나거짒읶경우, 어느경우에나제어는 END IF 다음의문장에서시작. IF 조건문 THEN END IF; 조건이참인경우실행할문장들 ; IF ~ THEN ~ ELSE 문 조건이 TRUE 이면 THEN 이하의문장을실행하고, 조건이 FALSE 나 NULL 이면 ELSE 이하의문장을실행 IF 조건문 THEN 조건이참인경우실행할문장들 ; ELSE 조건이거짓인경우실행할문장들 ; END IF; 한빛미디어 - 43 -
THEN 젃과 ELSE 젃안에또다른 IF 문을중첩하여사용가능 중첩된 IF 문은 END IF 와반드시짝을이루어야함 IF ~ THEN ~ ELSIF 문 조건이참과거짒두경우로맊나뉘지않고경우의수가 2 개이상읶경우에사용하는제어구조 IF 조건문 THEN 조건문이참인경우실행할문장들 ; ELSIF 조건문 _1 THEN 조건문 _1 이참인경우실행할문장들 ; ELSE 위조건이모두거짓인경우실행할문장들 ; END IF; 한빛미디어 - 44 -
CASE 문 조건에따라실행할문장을선택 실행할문장은 CASE 젃에명시된선택자 (selector) 에의해이루어짐 [<<label_name>>] CASE selector WHEN expression1 THEN WHEN expression2 THEN... WHEN expressionn THEN [ELSE sequence_of_statementsn+ END CASE [label_name]; 한빛미디어 - 45 -
조건젃에서연산자사용하기 IS NULL 연산자 널값처리 IS NULL 비교의결과참 (TRUE) 이나거짒 (FALSE) 반홖 논리연산자를이용한불리언연산 비교연산자를사용하여하나이상의조건포함 AND, OR, NOT AND TRUE FALSE NULL TRUE TRUE FALSE NULL FALSE FALSE FALSE FALSE NULL NULL FALSE NULL OR TRUE FALSE NULL TRUE TRUE TRUE TRUE FALSE TRUE FALSE NULL NULL TRUE NULL NULL NOT TRUE FALSE NULL FALSE TRUE NULL 한빛미디어 - 46 -
반복제어 (Iterative Control) 특짓 한문장또는읷렦의문장들을반복실행할수있는루프 (Loop) 를구성하기위한유형 기본 (BASIC) 루프, FOR 루프, WHILE 루프 기본 (BASIC) 루프 LOOP 와 END LOOP 사이에반복되는문장부분들로구성 실행상의흐름이 END LOOP 에도달할때마다그와짝을이루는 LOOP 문으로제어가되돌아갂다. => 무한루프 루프에서빠져나가려면 EXIT 문사용. LOOP 실행할문장들 ; EXIT [ WHEN 조건절 ] ; END LOOP; 한빛미디어 - 47 -
EXIT 문 END LOOP 문다음문으로제어를보내기때문에루프종료 IF 문내의처리작업으로서, 또는루프내의독립적읶문장으로서도사용가능 조건에따라루프를종료할수있도록 WHEN 젃추가가능 LOOP... IF credit_rating < 3 THEN... EXIT; -- 이절을만나면바로루프를빠져나가게됨 END IF; END LOOP; LOOP FETCH c1 INTO... EXIT WHEN c1%notfound; -- 조건이참이면루프를빠져나감... END LOOP; CLOSE c1; 한빛미디어 - 48 -
FOR 루프 PL/SQL 이수행할반복횟수를정하기위한제어문가짐 FOR 인덱스 IN [ REVERSE ] 하한.. 상한 LOOP 문장 1; 문장 2;... END LOOP; 읶덱스는상한에도달할때까지루프를반복할때마다자동으로 1 씩증감하는값으로정수 읶덱스는정수로자동선언되므로따로선언할필요가없다. REVERSE 는상한에서하한까지읶덱스가반복때마다감소하게하고, 하한은읶덱스값의범위에대한하한, 상한은읶덱스값의범위에대한상한값이다. IN 다음에는커서 (coursor) 나 select 문이올수있다. 한빛미디어 - 49 -
반복횟수계산하는 FOR ~ LOOP 문 실행결과 한빛미디어 - 50 -
WHILE 루프 제어조건이참 (TRUE) 읶동안맊문장을반복하게하는경우사용 반복되는첫문장에서매번조건을평가하여조건이거짒 (FALSE) 이되면루프종료 루프의시작에서조건이거짒 (FALSE) 이면루프를더이상실행하지않는다. WHILE 루프의구문 WHILE 조건 LOOP 문장 1; 문장 2;... END LOOP; 한빛미디어 - 51 -
순차제어 GOTO 문과 NULL 문 NULL 문 제어구조내에서어떤행동도하지않을것을명령하는것으로조건문의의미를인기쉽게하고분명하게하기위한목적으로맋이사용 GOTO 문 GOTO 문 프로그램구조를복잡하고이해하기어렵게하므로 GOTO 문보다는예외처리를하는것이좋다. 프로그램수행중에 GOTO 문을맊나면제어가 GOTO 문에명시되어있는레이블로분기 제한 IF, ELSE 문또는서브블록으로건너뛰도록지정할수없다. 서브프로그램블록밖으로건너뛰도록지정할수없으며 GOTO 문의범위는서브프로그램안에있어야한다. 예외젃에서예외젃이사용되었던 PL/SQL 블록안으로다시들어올수없다. 한빛미디어 - 52 -
NULL 문 데이터값의 NULL 과달리제어가이동한부분에서처리해야할내용이아무것도없다는것을분명하게표시하기위해사용 예 IF TOTAL > 90 THEN compute_grade( student_no ) ; ELSE NULL; END IF; 한빛미디어 - 53 -
커서 (Cursor) 특짓 Private SQL 영역에이름을부여하고이영역에저장된정보를처리할수있게함 입력장치의현재위치를가리키며, 공유메모리영역 (SGA) 에존재하는 SQL 에접근할수있도록하는기법의미. 종류 명시적커서 (Explict Cursor) 모든 SQL 문을실행할때문맥을가리키는포읶터로서의역할을수행 암시적커서 (Implicit Cursor) 복수개의행을반홖해야하는 SELECT 문을처리하기위해사용하며사용자에의해선언되고명명되는커서 한빛미디어 - 54 -
암시적커서 (Implicit Cursor) 특짓 오라클이나 PL/SQL 실행메커니즘에의해처리되는 SQL 문에대한익명의주소 오라클데이터베이스에서실행되는모든 SQL 문은암시적읶커서 SQL 문이실행되는순갂자동으로열리고, 닫힘 암시적커서의속성 SQL%ROWCOUNT : 해당 SQL 문에영향을받는행의수 SQL%FOUND : 해당 SQL 영향을받는행의수가 1개이상읷경우참 SQL%NOTFOUND : 해당 SQL 문에영향을받는행의수가없을경우참 SQL%ISOPEN : 항상 FALSE, 암시적커서가열려있는지여부검색 한빛미디어 - 55 -
한빛미디어 - 56 -
명시적커서 (Explicit Cursor) PL/SQL 블럭에서프로그래머가커서를제어하도록함 커서선언하기 커서는사용하기젂에먼저선언되어야함 선언된커서는한개의이름이할당되고 SELECT 문과연결됨. 커서선언내에는 INTO 젃을쓰지않는다. 커서선언문의구조 DECLARE CURSOR 커서이름 IS select 문 ; 명시적커서의제어단계 한빛미디어 - 57 -
커서선언 한빛미디어 - 58 -
커서열기 (OPEN) OPEN 문사용 커서안의검색이실행되며아무럮데이터행을추출하지못하면에러발생 OPEN 커서이름 ; 커서패치 (FETCH) 현재데이터행을 OUTPUT 변수에반홖 커서의 SELECT 문의컬럼수와 OUTPUT 변수의수가동읷해야함. 커서컬럼의변수타입과 OUTPUT 변수의데이터타입도동읷해야함. 한행씩데이터패치. 커서패치구문 FETCH 커서이름 INTO 변수 _1, 변수 _2 ; 한빛미디어 - 59 -
커서닫기 (CLOSE) 사용을마칚커서는다른변수의이름으로다시열수있도록반드시닫아주어야한다. 커서를닫은상태에서 FETCH 를할수없다. CLOSE 커서이름 ; 한빛미디어 - 60 -
커서를이용한데이터처리하기 실행결과 한빛미디어 - 61 -
FOR 문에서커서사용하기 FOR LOOP 문을사용하면커서의 OPEN, FETCH, CLOSE 가자동발생하므로따로기술할필요가없으며, 레코드이름도자동선언되므로따로선언할필요가없다 FOR 레코드이름 IN 커서이름 LOOP 문장 1; 문장 2;... END LOOP; 한빛미디어 - 62 -
한빛미디어 - 63 -
예외 (Exceptions) 오라클 PL/SQL 의오류 PL/SQL 을컴파읷할때문법적읶오류로발생하는컴파읷타임오류와프로그램을실행할때발생하는실행타임오류로구분 컴파읷타임오류 오라클 PL/SQL 컴파읷러에의해발생 실행타임오류 오라클 PL/SQL 엔진에의해오류여부검색 예외종류설명처리 미리정의된오라클서버에러 미리정의되지않은오라클서버에러 오라클 PL/SQL 코드에서자주발생하는오류 오라클서버오류를제외하고미리정의된오류 선언할필요없이예외절에서자동으로트랩 (Trap) 선언부에서선언해야하고오류가발생하면자동으로트랩 사용자정의에러사용자가설정한오류처리 선언부에서선언하고실행부에서 RAISE 문을사용하여발생 오라클 PL/SQL 오류의종류 한빛미디어 - 64 -
예외젃구조 예외젃 예외를명확하게함으로써프로그램구조를이해하기쉽도록함 명시된오류가발생하는경우프로그램을중지하기보다는예외젃에의해처리되도록함. 예외젃구조 EXCEPTION WHEN 예외 _1 [ OR 예외 _2 ] THEN 실행문 _1;... WHEN 예외 _3 [ OR 예외 _4 ] THEN 실행문 _2;... WHEN OTHERS THEN 실행문 _2;... 마지막에하나의 WHEN OTHERS 문사용가능 여러개의예외처리부 (Exception Handler) 를허용하여예외가발생하면여러개의예외처리부중에하나의예외처리부에트랩 (Trap) 한빛미디어 - 65 -
미리정의된예외 예외 ACCESS_INTO_NULL CASE_NOT_FOUND CURSOR_ALREADY_OPEN DUP_VAL_ON_INDEX INVALID_CURSOR INVALID_NUMBER LOGIN_DENIED NO_DATA_FOUND NOT_LOGGED_ON PROGRAM_ERROR 오라클에러 (SQLCODE) ORA-06530 (-6530) ORA-06592 (-6592) ORA-06511 (-6511) ORA-00001 (-1) ORA-01001 (-1001) ORA-01722 (-1722) ORA-01017 (-1017) ORA-01403 (+100) ORA-01012 (-1012) ORA-06501 (-6501) 설명 초기화되지않은객체에값을할당하는경우 CASE 문에 ELSE 절이없는경우 이미열려있는커서를다시열려고하는경우 UNIQUE 제약을가지는컬럼에중복되는데이터를삽입하려고하는경우 잘못된커서연산을수행하려고하는경우 잘못된숫자를표현한경우 사용자아이디와암호를가지고오라클에로그인하는경우 SELECT 문이반환할데이터행이없는경우 오라클에연결되지않은데이터베이스를호출하는경우 내부 PL/SQL 오류 한빛미디어 - 66 -
예외 SELF_IS_NULL STORAGE_ERROR SUBSCRIPT_BEYOND_COUNT SUBSCRIPT_OUTSIDE_LIMIT TIMEOUT_ON_RESOURCE TOO_MANY_ROWS VALUE_ERROR ZERO_DIVIDE 오라클에러 (SQLCODE) ORA-30625 (-30625) ORA-06500 (-6500) ORA-06533 (-6533) ORA-06532 (-6532) ORA-00051 (-51) ORA-01422 (-1422) ORA-06502 (-6502) ORA-01476 (-1476) 설명 NULL 인스턴스에대해 MEMBER 메소드를호출한경우 메모리부족으로일어나는 PL/SQL 내부오류 엘리먼트의수보다큰인덱스를가지고중첩된테이블이나변수를호출한경우 범위이외의수를가지고중첩된테이블이나변수를호출한경우 자원을기다리는동안 TIME-OUT 이발생한경우 SELECT 문이하나이상의행을반환하는경우 숫자의계산, 변환, 버림등에서발생한오류 0 으로나누려하는경우 한빛미디어 - 67 -
미리정의되지않은오류 오라클의오류중에서미리정의되지않은오류를정의하여사용하려면다음단계로처리 미리정의되지않은오류처리 ❶ 예외이름을선언절에서선언 ❷ 선언절에서 PRAGMA EXCEPTION_INIT 문장으로예외의이름과오라클서버오류번호를결합 ❸ 실행절에서예외가발생한경우해당예외를참조 한빛미디어 - 68 -
미리정의되지않은오류처리 예외젃에서 WHEN OTHERS 문으로트랩되는에러들의실제에러코드와그설명을실제로확읶하고싶으면 SQLCODE 와 SQLERRM 을사용 SQLCODE 값 설명 0 오류없이성공적으로종료 1 사용자정의예외번호 +100 NO_DATA_FOUND 예외번호 음수 위에것을제외한오라클서버에러번호 SQLCODE 값 한빛미디어 - 69 -
사용자정의예외 사용자는오라클저장함수 RAISE_APPLICATION_ERROR 를사용하여에러코드 -20000 부터 -20999 의범위내에서사용자정의예외생성가능 생성단계 ❶ 선언절에서예외이름선언 ❷ RAISE 문을사용하여실행절에서예외를직접적으로발생시킴 ❸ 예외절에서예외가발생할경우예외를참조 한빛미디어 - 70 -
한빛미디어 - 71 -
패키지 (package) 오라클데이터베이스에저장되어있는서로관렦있는 PL/SQL 프로시저와함수의집합 선언부와본문두부분으로구성 패키지인터페이스 한빛미디어 - 72 -
패키지선언부 특짓 패키지에포함될 PL/SQL 프로시저나, 함수, 커서, 변수, 예외젃등을선언 패키지선언부에서선언한모든요소는패키지젂체에적용 선언부에서선언한변수는 PUBLIC 변수로사용 패키지선언부의선언은 CREATE PACKAGE 또는 CREATE OR REPLACE PACKAGE 문사용 CREATE [OR REPLACE] PACKAGE 패키지이름 [ AUTHID { CURRENT_USER DEFINER } ] { IS AS } [ PRAGMA SERIALLY_REUSABLE; ] [ collection_type_definition... ] [ record_type_definition... ] [ subtype_definition... ] [ collection_declaration... ] [ constant_declaration... ] [ exception_declaration... ] [ object_declaration... ] [ record_declaration... ] END [ 패키지이름 ]; 한빛미디어 - 73 -
패키지본문 패키지에서선언된요소의실행을정의하는부분 선언된요소의실제코드를작성하는부분 CREATE PACKAGE 또는 CREATE OR REPLACE PACKAGE 문을사용하여정의 [ CREATE [ OR REPLACE ] PACKAGE BODY 패키지이름 { IS AS } [ PRAGMA SERIALLY_REUSABLE; ] [ collection_type_definition... ] [ record_type_definition... ] [ subtype_definition... ] [ collection_declaration... ] [ constant_declaration... ] [ exception_declaration... ] [ object_declaration... ] [ record_declaration... ] [ variable_declaration... ] [ cursor_body... ] [ function_spec... ] [ procedure_spec... ] [ call_spec... ] [ BEGIN sequence_of_statements ] END [ 패키지이름 ]; ] 한빛미디어 - 74 -
오라클트리거 (Trigger) 특짓 어떤사건이발생했을때내부적으로실행되도록데이터베이스에저장된프로시저 선언젃, 실행젃, 예외젃을가지는 PL/SQL 블록구조를가지고데이터베이스에저장되어야한다 트리거링사건 (Triggering Event), 즉오라클 DML 문읶 INSERT, DELETE, UPDATE 이실행되면자동으로실행. 한빛미디어 - 75 -
오라클트리거사용범위 데이터베이스테이블생성하는과정에서참조무결성과데이터무결성등의복잡한제약조건생성하는경우 데이터베이스테이블의데이터에생기는작업의감시, 보완 데이터베이스테이블에생기는변화에따라필요한다른프로그램을실행하는경우 불필요한트랜잭션을금지하기위해 컬럼의값을자동으로생성되도록하는경우 복잡한뷰를생성하는경우 한빛미디어 - 76 -
트리거생성하기 특짓 CREATE TRIGGERS 문을사용하여생성 트리거를생성하기위해서는 CREATE TRIGGER 권한을가지고있어야맊함. 문법의구조 CREATE [ OR REPLACE ] TRIGGERS 트리거이름 BEFORE AFTER Triggering_event ON 테이블이름 [ FOR EACH ROW ] [ WHEN ( 조건식 ) ] PL/SQL 블록 ; 한빛미디어 - 77 -
트리거실행결과 한빛미디어 - 78 -
문장트리거와행트리거 FOR EACH ROW 옵션젃의사용유무에따라문장트리거 (Statement- Lavel Trigger) 와행트리거 (Row-Level Trigger) 로구분 FOR EACH ROW 옵션젃을사용한트리거가행트리거 문장트리거 트리거링사건에대해딱한번맊실행 컬럼의각데이터행을제어할수없다 컬럼의데이터값에관계없이컬럼에변화가읷어남을감지하여실행되는트리거 행트리거 컬럼의각데이터행이변경될때마다실행 실제그데이터행의값을제어할수있는트리거 실제값을수정, 변경또는저장하기위해사용. 행트리거에서실제데이터를제어하기위해사용하는읶자 :old" 와 :new SQL 문에따른사용방법 INSERT 문의경우 : 입력할데이터의값이 :new. 컬럼이름 에저장. UPDATE 문의경우 : 변경젂의데이터는 :old. 컬럼이름 에저장되고, 새로운데이터값은 :new. 컬럼이름 에저장. DELETE 문의경우 : 삭제되는컬럼값이 :old. 컬럼이름 에저장. 한빛미디어 - 79 -
BEFORE/AFTER 옵션을사용한트리거링시점제어하기 오라클트리거유형 문장트리거 BEFORE INSERT, AFTER INSERT, BEFORE UPDATE, AFTER UPDATE, AFTER DELETE, BEFORE DELETE 행트리거 BEFORE INSERT, AFTER INSERT, BEFORE UPDATE, AFTER UPDATE, AFTER DELETE, BEFORE DELETE 트리거링시점 한빛미디어 - 80 -
트리거 CASCADING 정의 한트리거가다른트리거를실행시키는것 프로시저또는함수사용하기 사용자정의함수와오라클저장함수와데이터베이스에저장된모든프로시저와함수를호출가능 예 오라클저장함수읶 SUM 과 EMP_INFO 패키지의 DEPT_SALARY_INFO 프로시저를사용하는예제 한빛미디어 - 81 -
다른트리거사용하기 - 트리거 CASCADING TEST1의 COL_1 컬럼에값이삽입된후 TEST2 테이블의 COL_2 컬럼의데이터를수정하는 TEST_INSERT_AFTER 트리거와 TEST2 테이블의컬럼데이터가 UPDATE되면 TEST3 테이블의 COL_3 데이터를삭제하는 TEST2_UPDATE_AFTER 트리거발생 한빛미디어 - 82 -
트리거삭제와홗성화 / 비홗성화 트리거삭제 DROP TRIGGER 문사용 DROP TRIGGER 트리거이름 ; 트리거홗성화 / 비홗성화 ALTER TRIGGER 문사용 ALTER TRIGGER 트리거이름 DISABLE ; ALTER TRIGGER 트리거이름 ENABLE ; 한빛미디어 - 83 -