Microsoft PowerPoint - PLSQL.ppt

Similar documents
Microsoft PowerPoint - PLSQL.ppt

13주-14주proc.PDF

Microsoft Word - PLSQL.doc

Microsoft Word - 04_EXCEPTION.doc

Microsoft Word - 05_SUBPROGRAM.doc

Microsoft Word - 03_SQL_CURSOR.doc

ESQL/C

목차 BUG 문법에맞지않는질의문수행시, 에러메시지에질의문의일부만보여주는문제를수정합니다... 3 BUG ROUND, TRUNC 함수에서 DATE 포맷 IW 를추가지원합니다... 5 BUG ROLLUP/CUBE 절을포함하는질의는 SUBQUE

(Microsoft Word - PLSQL\300\314\266\365.doc)

I T C o t e n s P r o v i d e r h t t p : / / w w w. h a n b i t b o o k. c o. k r

Microsoft Word - 02_PLSQL_BLOCK_STRUCTURE.doc

歯PLSQL10.PDF

DBMS & SQL Server Installation Database Laboratory

歯sql_tuning2

강의 개요

@OneToOne(cascade = = "addr_id") private Addr addr; public Emp(String ename, Addr addr) { this.ename = ename; this.a

다양한 예제로 쉽게 배우는 오라클 SQL 과 PL/SQL

10.ppt

ALTIBASE HDB Patch Notes

Microsoft Word - 07_TRIGGER.doc

목차 BUG DEQUEUE 의 WAIT TIME 이 1 초미만인경우, 설정한시간만큼대기하지않는문제가있습니다... 3 BUG [qp-select-pvo] group by 표현식에있는컬럼을참조하는집합연산이존재하지않으면결괏값오류가발생할수있습니다... 4

단답형 (26 회기출문제 ) 1. 아래와같은테이블이있을때아래의 SQL 결과에대해서 Oracle, SQL Server 순서로적으시오 TAB1 COL1 CHAR(10) COL2 CHAR(10) INSERT INTO TAB1 VALUES ('1',''); INSERT INT

PowerPoint Presentation

슬라이드 1

90

Microsoft Word - SQL튜닝_실습교재_.doc

MySQL-.. 1


FlashBackt.ppt

6장. SQL

SQL Tuning Business Development DB

Jerry Held

untitled

Microsoft PowerPoint - 10Àå.ppt

배치프로그램에서튜닝대상 SQL 추출하기 엑셈컨설팅본부 /DB 컨설팅팀박성호 배치프로그램의성능문제를진단하기위해트레이스를사용할수없고, 개별 SQL 에대한성 능점검은비효율적인경우에어떻게배치프로그램의성능문제를제대로파악하고개선안을도 출할것인가? 복잡한로직을가지고있는프로그램 (

Bind Peeking 한계에따른 Adaptive Cursor Sharing 등장 엑셈컨설팅본부 /DB 컨설팅팀김철환 Bind Peeking 의한계 SQL 이최초실행되면 3 단계의과정을거치게되는데 Parsing 단계를거쳐 Execute 하고 Fetch 의과정을통해데이터

슬라이드 제목 없음

ORANGE FOR ORACLE V4.0 INSTALLATION GUIDE (Online Upgrade) ORANGE CONFIGURATION ADMIN O

윈도우시스템프로그래밍

Microsoft PowerPoint - chap02-C프로그램시작하기.pptx

TITLE

RDB개요.ppt

InsertColumnNonNullableError(#colName) 에해당하는메시지출력 존재하지않는컬럼에값을삽입하려고할경우, InsertColumnExistenceError(#colName) 에해당하는메시지출력 실행결과가 primary key 제약에위배된다면, Ins

Microsoft Word - [Unioneinc] 특정컬럼의 통계정보 갱신_ _ldh.doc

다양한 예제로 쉽게 배우는 오라클 SQL 과 PL/SQL

다양한 예제로 쉽게 배우는 오라클 SQL 과 PL/SQL

Chap 11. PL-SQL

´ÙÁß Row °á°ú¸¦ ´ÜÀÏÇàÀ¸·Î Äĸ¶·Î ºÐ¸®ÇØ Ãâ·ÂÇÏ´Â ¹æ¹ý

목차 BUG offline replicator 에서유효하지않은로그를읽을경우비정상종료할수있다... 3 BUG 각 partition 이서로다른 tablespace 를가지고, column type 이 CLOB 이며, 해당 table 을 truncate

Tablespace On-Offline 테이블스페이스 온라인/오프라인

3 S Q L A n t i p a t t e r n s Trees/intro/parent.sql CREATE TABLE Comments ( comment_id SERIAL PRIMARY KEY, parent_id BIGINT UNSIGNED, comment TEXT

Spring Boot/JDBC JdbcTemplate/CRUD 예제

문서 템플릿

다양한 예제로 쉽게 배우는 오라클 SQL 과 PL/SQL

chap 5: Trees

다른 JSP 페이지호출 forward() 메서드 - 하나의 JSP 페이지실행이끝나고다른 JSP 페이지를호출할때사용한다. 예 ) <% RequestDispatcher dispatcher = request.getrequestdispatcher(" 실행할페이지.jsp");

슬라이드 1

Microsoft PowerPoint - e pptx

C# Programming Guide - Types

ORACLE-SQL

最即時的Sybase ASE Server資料庫診斷工具

금오공대 컴퓨터공학전공 강의자료

윈도우시스템프로그래밍

Microsoft Word - 기술노트[19회] Flashback.doc

USER GUIDE

제4장 기본 의미구조 (Basic Semantics)

<C1A62038B0AD20B0ADC0C7B3EBC6AE2E687770>

설계란 무엇인가?

구축환경 OS : Windows 7 그외 OS 의경우교재 p26-40 참조 Windows 의다른버전은조금다르게나타날수있음 Browser : Google Chrome 다른브라우저를사용해도별차이없으나추후수업의모든과정은크롬사용 한

<4D F736F F F696E74202D20B8B6C0CCC5A9B7CEC7C1B7CEBCBCBCAD202839C1D6C2F7207E203135C1D6C2F >

Microsoft Word - 06_PACKAGE.doc

MS-SQL SERVER 대비 기능

제목을 입력하세요.

슬라이드 1

8 장데이터베이스 8.1 기본개념 - 데이터베이스 : 데이터를조직적으로구조화한집합 (cf. 엑셀파일 ) - 테이블 : 데이터의기록형식 (cf. 엑셀시트의첫줄 ) - 필드 : 같은종류의데이터 (cf. 엑셀시트의각칸 ) - 레코드 : 데이터내용 (cf. 엑셀시트의한줄 )

다양한 예제로 쉽게 배우는 오라클 SQL 과 PL/SQL

PowerPoint 프레젠테이션

ETL_project_best_practice1.ppt

임베디드시스템설계강의자료 6 system call 2/2 (2014 년도 1 학기 ) 김영진 아주대학교전자공학과

Oracle Database: Introduction to SQL(Oracle Database: Fundamentals I 권장이수과정 Previous programm 교육목표내장프로시저와함수생성및 유연성증대를위해오버로드된패키지서브프로그 업무과제해결을위한트리거 PL/S

A Hierarchical Approach to Interactive Motion Editing for Human-like Figures

WINDOW FUNCTION 의이해와활용방법 엑셈컨설팅본부 / DB 컨설팅팀정동기 개요 Window Function 이란행과행간의관계를쉽게정의할수있도록만든함수이다. 윈도우함수를활용하면복잡한 SQL 들을하나의 SQL 문장으로변경할수있으며반복적으로 ACCESS 하는비효율역

JVM 메모리구조

PRO1_09E [읽기 전용]

Microsoft PowerPoint 세션.ppt

예제소스는 에서다운로드하여사용하거나툴바의 [ 새쿼리 ]( 에아래의소스를입력한다. 입력후에는앞으로실습을위해서저장해둔다. -- 실습에필요한 Madang DB 와 COMPANY DB 를모두생성한다. -- 데이터베이스생성 US

Microsoft PowerPoint - additional01.ppt [호환 모드]

예제 1.1 ( 관계연산자 ) >> A=1:9, B=9-A A = B = >> tf = A>4 % 4 보다큰 A 의원소들을찾을경우 tf = >> tf = (A==B) % A

PostgreSQL 2 Uniersity of California at Berkeley ( ) 에서개발된관계형데이터베이스서버인 Ingres 가시초 ( 후에 Computer Associates 에인수됨 ) 1

Microsoft PowerPoint - 3ÀÏ°_º¯¼ö¿Í »ó¼ö.ppt

Microsoft PowerPoint Python-DB

[ 목차 ] 5.1 데이터베이스프로그래밍개념 5.2 T-SQL T-SQL 문법 5.3 JAVA 프로그래밍 2

Microsoft PowerPoint 자바-기본문법(Ch2).pptx

Result Cache 동작원리및활용방안 엑셈컨설팅본부 /DB 컨설팅팀김철환 개요 ORACLE DBMS 를사용하는시스템에서 QUERY 성능은무엇보다중요한요소중하나이며그 성능과직접적인관련이있는것이 I/O 이다. 많은건수를 ACCESS 해야만원하는결과값을얻을수있는 QUER

객관식 1. 아래의쿼리를만족하는결과를가장잘설명한것은? SELECT A.* FROM HR.EMPLOYEES A, HR.EMPLOYEES B WHERE 1=1 AND A.MANAGER_ID = B.EMPLOYEE_ID AND B.SALARY >= ANY A.SALARY;

MySQL-Ch10

PowerPoint Presentation

Microsoft PowerPoint - chap06-2pointer.ppt

PowerPoint 프레젠테이션

<4D F736F F F696E74202D20B8AEB4AABDBA20BFC0B7F920C3B3B8AEC7CFB1E22E BC8A3C8AF20B8F0B5E55D>

Transcription:

PL/SQL Consultant 주종면 jina6678@yahoo.co.kr 1 - 목차 - 1장. PL/SQL 2장. 커서와에러처리 3장. 패키지와트리거 4장. PL/SQL의관리 2

1 PL-SQL 의개념 3 SQL 의장점과단점 < 장점 > 오라클 DB 지역정보부서코드지역명 1 서울 2 부산 SQL (1) 사용자가이해하기쉬운단어로구성 (2) 쉽게배울수있다. (3) 복잡한로직을간단하게작성할수있다. (4) ANSI 에의해문법이표준화되어있다. < 단점 > (1) 반복처리를할수없다.(Loop) (2) 비교처리를할수없다.(IF) (3) Error 처리를할수없다.( 예외처리 ) (4) SQL문을캡슐화할수없다. (5) 변수선언을할수없다. (6) 실행할때마다분석작업후실행 (7) Network Traffic을유발한다. 4

PL/SQL 의생성과실행 오라클 DB 급여계산 Procedure SALARY_CAL EMP 테이블 DEPT 테이블 SQL> Create Procedure SALARY_CAL Select * INTO :a,m :b From emp Where id = 10; If SQL%NotFound then. SQL> Execute SALARY_CAL; 1. 반복처리를할수있다.(Loop) 2. 비교처리를할수있다.(IF) 3. Error 처리를할수있다.( 예외처리 ) 4. SQL문을캡슐화할수있다.( 데이터의보안및무결성 ) 5. 변수선언을할수있다. 6. 실행할때마다분석된결과를실행만하기때문에성능이빠르다. 7. Network Traffic이감소된다. 5 PL/SQL 의종류 Anonymous Procedure DECLARE Stored Procedure Package Stored Function EXCEPTION Trigger Object Type 6

PL/SQL-BLOCK 구조. 선언부 ( 변수, 상수 ) 1) SCLAR 변수 2) TABLE 변수 3) RECORD 변수. 예외처리부 1) 미리정의된예외 -NO_DATA_FOUND -TOO_MANY_ROWS 2) 정의안된예외 - PRAGMA_EXCEPTION_INIT 3) SQLCODE, SQLERRM DECLARE EXCEPTION. 실행부 1) SQL 문 - Select / Update 문 - Insert / Delete 문 - Commit / Rollback 문 2) 비교문 - IF ~ THEN ~ ELSE ~ 3) 제어문 - BASIC LOOP - FOR IN LOOP - WHILE LOOP 4) CURSOR 속성 - %RIWCOUNT -%FOUND - %NOTFOUND -%ISOPEN 7 PL/SQL 의기본문법 구분 Anonymous Procedure Stored Procedure 형식 Declare ~ ~ Exception ~ Create or Replace Procedure [ 프로시저명 ] Exception Stored Function Create or Replace Function [ 함수명 ] Return Exception Package Create or Replace Package [ 패키지명 ] ~ Create or Replace Package Body ~ Trigger Create or Replace Trigger [ 트리거명 ] AFTER [BEFORE] [ 조건 ] 8

Stored Function 9 Syntax Create [or Replace] Function [ 함수명 ] ( argument1 [mode] data_type1, argument2 [mode] data_type2, Return data_type; IS [AS] Return ( 변수 ); Exception. Drop Function [ 함수명 ] ;. [ 함수명 ] : 데이터베이스내에저장될프로시저이름. Argument : 운영체계에서프로시저내로어떤값을전달할때의변수. Mode : IN, OUT, IN OUT. Data_Type : Argument 변수의데이터타입. ~ End : 실행하려는처리절차 10

함수개발절차 1. O/S 의편집기로파일생성 (chk_sal.sql) Create Function CHK_SAL IS ( v_sal number; ) Return number; v_chk := v_sal * 0.01; return ( v_chk ); 2 $ sqlplus scott/tiger SQL> @chk_sal.sql 3 Compiler (pcode 생성 ) 4 SQL> VARIABLE v_sal number SQL> EXECUTE :v_sal := chk_sal (7900); CHK_SAL 11 Function 의실행방법. EXECUTE 명령어에의한실행 (SQL*Plus 환경 ) SQL> VARIABLE v_sal number SQL> EXECUTE :v_sal := chk_sal (7900); SQL> PRINT v_sal v_sal --------------------- 10. 표현식의일부로서호출될수있습니다. SQL> SELECT empno, sal, chk_sal(empno) FROM emp WHERE deptno = 10; SQL> SELECT SUM(sal) FROM emp WHERE deptno = 10;. Select-list절, Where과 Having절. Connect By, Start With, Order By, Group By 절. Insert문의 Values절과 Update문의 Set절 12

Stored Procedure 13 Syntax Create [or Replace] Procedure [ 프로시저명 ] ( argument1 [mode] data_type1, argument2 [mode] data_type2, IS [AS] Exception. Drop Procedure [ 프로시저명 ] ;. [ 프로시저명 ] : 데이터베이스내에저장될프로시저이름. Argument : 운영체계에서프로시저내로어떤값을전달할때의변수. Mode : IN, OUT, IN OUT. Data_Type : Argument 변수의데이터타입. ~ End : 실행하려는처리절차 14

프로시저개발절차 1. O/S의편집기로파일생성 (salary_cal.sql) Create Procedure SALARY_CAL Select * INTO :a,m :b From emp Where id = 10; If SQL%NotFound then. / 2 $ sqlplus scott/tiger SQL> @salary_cal.sql 3 Compiler (pcode 생성 ) 4 SQL> EXECUTE salary_cal SALARY_CAL 15 PL/SQL 에서 SQL 문 SELECT [column-1],, [column-n] INTO {variable-1,, variable-n} FROM table_name WHERE [ 조건절 ] ; INSERT INTO table_name VALUES ( [variable-1,, variable-n] ); UPDATE table_name WHERE [ 조건절 ] ; SET [column-1] = value] DELETE FROM table_name WHERE [ 조건절 ] ; 16

Procedure 의실행방법. EXECUTE 명령어에의한실행 (SQL*Plus 환경 ) SQL> EXECUTE a_dept ( v_name, 서울 );. Anonymous Procedure 에서호출에의한실행 SQL> Declare ( v_name IN varcahr2) a_dept ( v_name, 서울 );. 하나의 Stored Procedure 에서호출에의한실행 SQL> Create or Replace Procedure a_emp ( v_loc IN varcahr2) a_dept ( 총무과, v_loc); 17 매개변수의종류 % sqlplus scott/tiger Sql> variable c number; Sql> Execute test(1234, 100, :c); Sql> print c; c ------- 1234 Create Procedure test ( a IN number; b IN OUT number; c OUT number; C := 1234; EXCEPTION 18

매개변수의전달방법. 위치에의한전달방법 (Positional Method). 변수명에의한전달방법 (Named Method). 위치 / 변수명에의한전달방법 (Combination Method) SQL> Create or Replace Procedure a_dept ( v_name IN varcahr2, v_loc IN varchar2) INSERT INTO dept VALUES (seq_dept.nextval, v_name, v_loc); SQL> a_dept ( 총무과, 서울 ); a_dept ( v_loc => 서울, v_name => 전산과 ); a_dept ( 자재과, v_loc => 부산 ); Positional Method Named Method Combination Method 19 PL/SQL 변수 DECLARE EXCEPTION. SCLAR 변수 V_sal number := 10; V_name varchar2;. References 변수 (%TYPE형) V_empno emp.empno%type;. References 변수 (%ROWTYPE형) V_emp emp%rowtype;. Composite 변수 (RECORD 형 ) TYPE t_emp IS RECORD (emp_id number(5); emp_name varchar2(20);) V_emp t_emp;. Composite 변수 (TABLE 형 ) TYPE t_emp IS TABLE OF emp%rowtype INDEX BY BINARY_INTEGER; V_emp t_emp;. Bind 변수 Variable V_empno number; 20

Sclar 변수 변수명 [CONSTANT] [data_type] [Not Null] [:= DEFAULT [ 표현식 ]]; DECLARE V_no NUMBER; V_name VARCHAR2(15) DEFAULT unknown V_loc CONSTANT NUMBER(7, 2) := 0.2; NUMBER 정수, 소수점을포함한숫자 BINARY_INTEGER CHAR VARCHAR2 LONG LONG RAW -2147483647 ~ +2147483647 사이의정수고정길이문자가변길이문자대용량의고정길이문자 (2GB) 대용량의이진 (Binary) 문자 (2GB) DATE BOOLEAN 날짜와시간 참과거짓을표현 (True, False) 21 Type 변수 변수명 Table 명.Column 명 %TYPE ; DECLARE V_no dept.deptno%type; V_name dept.dname%type; V_loc dept.loc%type; * Dept 테이블 * Deptno number(2) Dname varchar2(13) Loc varchar2(14) 22

RowType 변수 변수명 Table 명 %ROWTYPE ; DECLARE V_dept dept%rowtype; v_dept.deptno := 10 ; v_dept.dname := 주종면 ; v_dept.deptno := 서울 ; * Dept 테이블 * Deptno number(2) Dname varchar2(13) Loc varchar2(14) 23 Record 변수 TYPE [type명] IS RECORD ( field-1 data_type-1,, field-n data_type-n); Record명 Type명 ; DECLARE Type dept_record_type IS RECORD ( deptno NUMBER(2), dname VARCHAR2(13), loc VARCHAR2(14)); Dept_record dept_record_type; dept_record.deptno := 10; dept_record.dname := 주종면 ; dept_record.loc := 서울 ; 24

Composite 변수 TYPE [type명] IS TABLE OF { Column_type Or variable%type or 테이블명.column명%type } INDEX BY BINARY_INTEGER; 식별자 Type명 ; DECLARE Type ename_table_type IS TABLE OF emp.ename%type INDEX BY BINARY_INTEGER; Ename_table ename_table_type; ename_table(1) := 주종면 ; ename_table(2) := 주종면 ; IF ename_table.exists(1) THEN ; 25 Bind 변수 Bind 변수 % sqlplus scott/tiger Sql> variable c number; Sql> Execute test(1234, 100, :c); Sql> print c; c ------- 1234 Create Procedure test ( a IN number; b IN number; c OUT number; C := 1234; EXCEPTION 26

PL/SQL 블록작성지침 1. 블록내에 SQL 문을여러번작성할수있습니다. 2. 식별자는최대 30 문자로작성할수있습니다. 3. 식별자는테이블또는컬럼명과동일할수없습니다. 4. 식별자는알파벳문자로시작해야합니다. 5. 문자와날짜타입은단일인용부호로표시합니다. 6. 주석은단일라인인경우 2 개의대쉬 ( - -) 여러라인인경우에는 /* ~ */ 로표시합니다. 7. 블록내에는 CREATEST, LEAST, DECODE, 그룹함수를사용할수없습니다. 27 비교문 IF [ 조건절 ] THEN [ 처리문장 ] END IF; IF [ 조건절 ] THEN [ 처리문장 1] ELSE [ 처리문장 2] END IF; IF [ 조건절 1] THEN [ 처리문장 1] ELSIF [ 조건절 2] THEN [ 처리문장 2] ELSE [ 처리문장 3] END IF; 28

반복문 LOOP [ 처리문장 ] EXIT [WHEN [ 조건절 ] END LOOP; FOR [ 변수 ] IN [REVERSE] 최소값.. 최대값 LOOP [ 처리문장 ] END LOOP; WHILE [ 조건절 ] LOOP [ 처리문장 ] END LOOP; 29 프로시저와함수의차이점 프로시저함수 PL/SQL 문장으로실행 (EXECUTE 명령문 ) RETURN 데이터형이없음 하나이상의값을반환 표현식의일부로호출또는 Execute 로실행 (SELECT 문의 Seletct-List, WHERE 절과 HAVING 절, CONNECT BY, START WITH 절, INSERT 문의 VALUES 절, UPDATE 문의 SET 절 ) 하나의 RETURN 데이터형이존재 하나의값만반환 30

2 Cursor 와에러처리 31 CURSOR. PL/SQL 블록내에서실행되는 SELECT 문을의미합니다 1. 암시적커서 ( Implict Cursor ) SELECT empno, ename INTO :v_no, :v_ename FROM emp WHERE deptno = 10; 2. 명시적커서 ( Explict Cursor ) Cursor C1 IS SELECT empno, ename FROM emp WHERE deptno = 20; OPEN C1; LOOP FETCH C1 INTO v_no, v_ename END LOOP; CLOSE C1; EMPNO ENAME DEPTNO ---------- ---------- -------------- 7369 SMITH 10 7499 ALLEN 30 7521 WARD 30 7566 JONES 20 7654 MARTIN 30 7698 BLAKE 30 7782 CLARK 20 32

명시적 CURSOR CURSOR cursor_name IS [ SELECT 문 ] ; CURSOR 선언 OPEN cursor_name; OPEN FETCH LOOP FETCH cursor_name INTO 변수 ; EXIT WHEN [ 처리내용 ]; END LOOP; CLOSE CLOSE cursor_name; 33 명시적커서의종류 CURSOR [ 커서명 ] IS [SELECT 절 ]; OPEN [ 커서명 ]; FOR FETCH [ 커서명 ] INTO [ 변수명 ]; EXIT WHEN [ 조건절 ]; END LOOP; CLOSE [ 커서명 ]; CURSOR [ 커서명 ] IS [SELECT 절 ]; FOR [Record명] IN [ 처리부 ] END LOOP; [ 커서명 ] LOOP 34

CURSOR 의속성 종류설명 %ROWCOUNT 커서의현재 Row Count 를리턴 %FOUND 커서가현재조건을만족하는지를리턴 %NOTFOUND 커서가현재조건을만족하지않는지리턴 %ISOPEN 커서가현재 open 되어있는지를리턴 35 매개변수와커서 CURSOR [ 커서명 ] [ (parameter_name data_type,. ) ] IS [SELECT {column_list} FROM table_name WHERE 조건 = parameter_name]; OPEN [ 커서명 ]( parameter_value,. ) ; FOR FETCH [ 커서명 ] INTO [ 변수명 ]; EXIT WHEN [ 조건절 ]; END LOOP; CLOSE [ 커서명 ]; 36

Where Current Of 절 CURSOR [ 커서명 ] IS [SELECT {column_list} FROM table_name; OPEN [ 커서명 ]; FOR FETCH [ 커서명 ] INTO [ 변수명 ]; update [table_name] set column = value where current of [ 커서명 ] ; EXIT WHEN [ 조건절 ]; END LOOP; CLOSE [ 커서명 ]; 37 에러처리방법 1. PL/SQL 블록내의 SQL문이정상적으로실행되지못할때에러가발생합니다. 2. 에러가발생하면 EXCEPTION절에의해처리할수있습니다. 3. 자주발생하는에러처리기는오라클사에서기본적으로제공하며, 자주발생하지않는에러는사용자가직접정의할수있습니다. Predefined (Internal) Exception UnDefined Exception User Defined Internal Exception Exception Trapping Function 38

미리정의된에러처리 DECLARE EXCEPTION WHEN [ 예외조건-1] THEN [ 처리문장-1]; WHEN [ 예외조건-2] THEN [ 처리문장-2];. WHEN OTHERS THEN [ 처리문장-n]; 예외조건 NO_DATA_FOUND NOT_LOGGED_ON TOO_MANY_ROWS VALUE_ERROR ZERO_DEVIDE INVALID_CURSOR INVALID_NUMBER DUP_VAL_ON_INDEX 설명조건을만족하는행이없는경우 DB에접속하지않은채실행하는경우 Sclar 변수에여러개의리턴행을저장하는경우지정된변수길이보다큰값을저장하는경우변수값을 0 값으로나누려는경우잘못된커서연산이발생하는경우수의문자열변환이잘못된경우이미저장된값을다시입력하는경우 39 미리정의되지않은에러처리 DECLARE 예외조건-1 EXCEPTION; PRAGMA EXCEPTION_INIT ( [ 예외조건-1], [ 에러코드 ]); EXCEPTION WHEN [ 예외조건-1] THEN [ 처리문장-1];. WHEN OTHERS THEN [ 처리문장-n]; DECLARE e_invalid EXCEPTION; PRAGMA EXCEPTION_INIT (e_invalid, -2292); EXCEPTION WHEN e_invalid THEN [ 처리문장-1];. WHEN OTHERS THEN [ 처리문장-n]; 40

사용자가정의하는에러처리 DECLARE 예외조건-1 EXCEPTION; DECLARE e_invalid EXCEPTION; RAISE 예외조건 -1; RAISE e_invalid; EXCEPTION WHEN [ 예외조건-1] THEN [ 처리문장-1];. WHEN OTHERS THEN [ 처리문장-n]; EXCEPTION WHEN e_invalid THEN [ 처리문장-1];. WHEN OTHERS THEN [ 처리문장-n]; 41 예외트래핑함수 DECLARE * 트래핑함수 예외조건 설 명 SQLCODE SQLERRM 에러코드를리턴해줍니다. 에러코드에대한에러메시지를리턴해줍니다. EXCEPTION * SQLCODE 종류 예외조건 설 명 WHEN OTHERS THEN v_code := SQLCODE; v_mwssage := SQLERRM; 0 1 +100 에러없이정상적으로실행되었습니다. 사용자가정의한에러가발생하였습니다. 해당조건을만족하는행을찾지못했습니다. RAISE_APPLICATION_ERROR (-01403, 데이터가존재하지않음 ); 양수 * 에러출력함수 다른오라클서버에러 Raise_Application_Error ( error_code, message, {TRUE FALSE} ) 42

Dynamic-SQL 43 Dynamic-SQL SELECT empno, ename FROM emp Where deptno = :c 검색조건을입력하시오! Condition 10 SELECT empno, ename, deptno FROM emp Where deptno = 10 Declare sql_stmt VARCHAR2(200); emp_rec emp%rowtype; v_deptno NUMBER(2) := 10; sql_stmt := 'SELECT empno, ename FROM emp WHERE deptno = :id'; EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING v_deptno; 44

예제 DECLARE sql_stmt VARCHAR2(200); emp_id NUMBER(4) := 7934; salary NUMBER(7,2); dept_id NUMBER(2) := 50; dept_name location emp_rec VARCHAR2(14) := 'PERSONNEL'; VARCHAR2(13) := 'DALLAS'; emp%rowtype; EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)'; sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)'; EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location; sql_stmt := 'SELECT * FROM emp WHERE empno = :id'; EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id; sql_stmt := 'UPDATE emp SET sal = 2000 WHERE empno = :1 RETURNING sal INTO :2'; EXECUTE IMMEDIATE sql_stmt USING emp_id RETURNING INTO salary; EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = :num USING dept_id; 45 Dynamic-SQL 의명시적커서 DECLARE emp_rec emp%rowtype; sql_stmt VARCHAR2(200); my_job VARCHAR2(15) := 'CLERK'; TYPE EmpCurTyp IS REF CURSOR; emp_cv EmpCurTyp; sql_stmt := 'SELECT * FROM emp WHERE job = :j'; OPEN emp_cv FOR sql_stmt USING my_job; LOOP FETCH emp_cv INTO emp_rec; EXIT WHEN emp_cv%notfound; DBMS_OUTPUT.PUT_LINE(emp_rec.empno ' ' emp_rec.ename); END LOOP; CLOSE emp_cv; 커서의선언커서의시작데이터의인출커서의종료 46

3 Package 와 Trigger 47 Package 48

패키지 1) 자주사용되는프로그램과로직을모듈화할수있습니다. 2) 응용프로그램을쉽게개발할수있습니다. 3) 프로그램의처리흐름을노출하지않아보안기능이좋습니다. 4) 프로그램에대한유지보수작업이편해집니다. 5) 같은이름의프로시저와함수를여러개생성할수있습니다. 49 Syntax 패키지명세 CREATE PACKAGE [package_name] IS 전역변수선언 Procedure [procedure_name1]; CREATE PACKAGE BODY [package_name] IS 지역변수선언 패키지몸체 Function [function_name1] IS. Procedure [procedure_name1] IS function1(); 50

패키지의실행 CREATE PACKAGE comm_package IS g_comm number := 10; Procedure reset_comm (v_comm IN number); End comm_package; CREATE PACKAGE BODY comm_package IS Procedure reset_comm IS v_comm IN number; Function validate_comm IS. SQL> EXECUTE comm_package.reset_comm(1500); SQL> EXECUTE scott. comm_package.reset_comm(1500); 51 오버로딩 1) 패키지내에서동일한이름의프로시저를여러개만들수있습니다. 2) 매개변수의개수, 순서, 데이터타입은달라야합니다. CREATE PACKAGE BODY comm_package IS Procedure reset_comm ( a OUT number, b IN number) IS Procedure reset_comm ( c OUT number, d IN varchar2) IS 52

Forward Declaration. 어떤프로시저나함수를호출할때해당프로시저보다먼저정의되어야합니다. CREATE PACKAGE BODY comm_package IS Procedure reset_comm ( a OUT number, b IN number) IS Procedure chk_sal ( ) v_sal := reset_comm(1, 2); 53 One-Time Only 프로시저. 패키지가사용자세션에서처음으로호출될때 One-Time Olny 프로시저가한번실행됩니다. CREATE PACKAGE BODY comm_package IS Procedure reset_comm ( a OUT number, b IN number) IS SELECT AVG(comm) INTO g_comm FROM emp; 54

패키지함수. 호출되는패키지내의테이블과변수에대한읽기 / 쓰기를제한할때사용되는함수입니다. PRAGMA RESTRICT_REFERENCES ( function_name, WNDS, [WNPS] [RNDS] [RNPS] ); 데이터베이스내의테이블을수정할수없습니다. 패키지내의변수값을수정할수없습니다. 데이터베이스내의테이블을 SELECT 할수없습니다. 패키지내의전역변수값을수정할수없습니다. 55 오라클제공패키지 종류예제 DBMS_OUTPUT PL/SQL 블록내의변수데이터를화면에출력할때사용합니다. DBMS_SQL PL/SQL 블록내에서 Dynamic-SQL 문을구현할때사용합니다. DBMS_JOB 특정 PL/SQL 블록을스케쥴링하여실행할때사용합니다. DBMS_DDL PL/SQL 블록내에서 DDL 문을실행할때사용합니다. DBMS_PIPE DB 에접속된다른사용자에게메시지를전송할때사용합니다. DBMS_AQ DBMS_PIPE 의향상된기능으로 DB 에접속된다른사용자에게메시지를전송할때사용합니다. DBMS_SESSION DB 에접속된다른사용자의정보를참조할때사용합니다. UTL_FILE O/S 상의텍스트파일을 PL/SQL 내에서읽을때사용합니다. 56

Trigger 57 Data Integrity 사원관리 입력 수정 삭제 사원번호 2001 성 명 주종면 급 여 10000 종료 Create trigger chk_emp Before update or delete or update on emp If Len(:new.no) <> 5 Then Dbms_output.put_line( 입력할수없음!! ); end if; 메시지사원번호는 5 자리입니다!!!! Create table emp ( no number(4) Primary Key, name varchar2(10) Not Null, Loc vaㅋrchar2(15) CHECK (Loc in ( 서울, 부산 )), jumin_no char(15) Unique, deptno number(2) References dept(deptno)); 58

트리거 오라클 DB Database 트리거 CHECK_SALARY CREATE TRIGGER CHECK_SALARY BEFORE update or delete or insert on EMP IF (to_char(sysdate, DY ) IN ( SAT, SUN )) THEN raise_application_error(-20500, 주말에는변경할수없습니다!! ); END IF; EMP EMPNO ENAME SALARY ----------------------------------------------- 7838 KING 5000 7698 BLAKE 2850 7369 CLERK 800 7788 SCOTT 3000 SQL> UPDATE emp SET sal= sal* 2; ORA-20500 Error 주말에는변경할수없습니다!! 시스템날자 : 2002. 7. 6( 토 ) 59 트리거구성요소 구성요소 가능한값 설 명 트리거유형트리거타이밍트리거이벤트트리거몸체 Statement Level Row Level BEFORE AFTER INSERT UPDATE DELETE PL/SQL 블록 트리거몸체의내용이몇번이나실행되는가? 사용자가트리거이벤트를유발시킬때데이터베이스트리거를언제실행하는가? 테이블상에어떤데이터조작연산이트리거를발생시키는가? 어떤이벤트가발생하면어떤작업이수행되는가? 트리거조건 WHEN [ 조건 ] 사용자의트리거이벤트중에조건을만족하는데이터만트리거합니다. 60

Statement Trigger. 기본테이블에 DML문이실행되면여러행에대한변경이발생하더라도트리거가한번만발생합니다. EMP 사원번호사원명직급급여부서번호 1 주종면차장 300 10 2 주영현과장 250 10 3 홍경옥과장 200 20 UPDATE EMP SET 급여 = 급여 * 1.1 ; 61 Syntax CREATE TRIGGER trigger_name IS [ 타이밍 ] [ 이벤트 -1] [OR 이벤트 -2 OR,,,,,,,] ON table_name,,,,,,, CREATE TRIGGER check_emp IS BEFORE INSERT or UPDATE or DELETE ON emp,,,,,,, 62

Row-Level Trigger. 기본테이블에 DML문이실행될때여러행에대한변경이발생하면각행에대해트리거가발생합니다. EMP 사원번호사원명직급급여부서번호 1 주종면차장 300 10 2 주영현과장 250 10 3 홍경옥과장 200 20 UPDATE EMP SET 급여 = 급여 * 1.1 ; 63 Syntax CREATE TRIGGER trigger_name IS [ 타이밍 ] [ 이벤트 -1] [OR 이벤트 -2 OR,,,,,,,] ON table_name FOR EACH ROW [WHEN 조건절 ],,,,,,, CREATE TRIGGER check_emp IS BEFORE INSERT or UPDATE or DELETE ON emp FOR EACH ROW WHEN ( :new.job = SALESMAN ),,,,,,, 64

Create trigger backup_emp Before update or delete or update on emp If Inserting then Insert into back_emp(deptno, dname, sal, gu) Values(:new.deptno,:new.name,:new.sal, 입력 ); Elsif Updating then Insert into back_emp Values(:new.deptno,:new.name,:new.sal, :old.deptno,:old.name,:old.sal, 수정 ); Elsif Deleting then Insert into back_emp Values(:old.deptno,:old.name,:old.sal, 삭제 ); end if; 65 트리거의변경 1) 트리거의상태를비활성화또는활성화할수있습니다. ALTER TRIGGER trigger_name DISABLE ENABLE; 2) 해당테이블과관련된모든트리거의상태를비활성화또는활성화할수있습니다. ALTER TABLE table_name DISABLE ENABLE ALL TRIGGER; 3) 트리거를재컴파일할수있습니다. ALTER TRIGGER trigger_name COMPILE; 4) 트리거를삭제할수있습니다. DROP TRIGGER trigger_name; 66

프로시저와트리거의차이점 프로시저 CREATE PROCEDURE 문법사용 트리거 CREATE TRIGGER 문법사용 생성하면소소코드와실행코드 (P-CODE) 가생성됨 EXECUTE 명령어로실행 COMMIT, ROLLBACK 실행가능 생성하면소소코드와실행코드 (P-CODE) 가생성됨 생성후자동실행 COMMIT, ROLLBACK 실행안됨 67 관리규칙 -1. 변경중인테이블로부터데이터를참조하지마라. EMP Foreign-Key UPDATE EMP SET SAL = 1500 WHERE empno=30; 사원번호사원명직급 sal 부서번호 1 주종면차장 300 10 Error ora-00491 2 주영현과장 250 10 3 홍경옥과장 200 20 CREATE OR REPLACE TRIGGER cascade_updates1 BEFORE UPDATE on emp FOR EACH ROW DECLARE v_sal emp.sal%type; SELECT max(sal) INTO v_sal FROM emp; 68

관리규칙 -2. 제약테이블의기본키, 외래키, 유일키컬럼에대해데이터를변경하지말것. UPDATE DEPT deptno = 30 WHERE deptno=10; SET EMP Foreign-Key 사원번호 사원명 직급 급여 부서번호 1 주종면 차장 300 10 2 주영현 과장 250 10 부서정보 부서번호 부서명 지역코드 10 전산과 1 20 경영지원과 1 3 홍경옥과장 200 20 CREATE OR REPLACE TRIGGER cascade_updates AFTER UPDATE on DEPT FOR EACH ROW UPDATE emp SET emp.deptno = :new.deptno WHERE emp.deptno = :old.deptno; 69 트리거의응용범위 범위 설 명 보안 데이터베이스내테이블에대한변경을제한할수있습니다 감사 사용자들의데이터베이스사용에대한모든내용을감시할수있습니다. 데이터의무결성 테이블에원치않는데이터가저장되는것을방지할수있습니다. 테이블의복제 기본테이블에대한똑같은복사태이블을온라인으로생성, 관리할수있습니다. 연속적작업수행 기본테이블에데이터가입력되면또다른테이블에데이터를변경하는연속적인작업을할수있습니다. 70

4 PL/SQL 의관리 71 PL/SQL 의관리 User_objects User_trigger User_source 오라클 DB 급여계산 Procedure SALARY_CAL SQL> Create Procedure CHK_SAL IS (v_no number; ) Select * INTO :a,m :b From emp Where id = 10; If SQL%NotFound then. DBMS_OUTPUT.PUTLINE(..); Compile Error.. DESCRIBE User_errors DBMS_OUTPUT 패키지 72

USER_OBJECTS. 데이터베이스내의모든객체를참조할수있습니다. 컬럼 설 명 OBJECT_NAME 객체의이름 OBJECT_ID OBJECT_TYPE CREATED 데이터베이스가객체를관리하는번호 객체의유형 (TABLE, PROCEDURE, FUNCTION, PACKAGE) 객체를마지막으로수정한날짜 TIMESTAMP 객체가마지막으로컴파일된날짜와시간 STATUS 객체의사용가능여부 (VALID, INVALID) 73 USER_SOURCE. PL/SQL 블록의 Source 내용을참조할수있습니다. 컬럼 설 명 NAME TYPE LINE 객체의이름 객체의유형 (TABLE, PROCEDURE, FUNCTION, PACKAGE) PL/SQL 블록의 SOURCE 코드의행번호 TEXT PL/SQL 블록의 SOURCE 코드 74

USER_ERRORS. PL/SQL 블록생성시발생한에러를참조할수있습니다. 컬럼 설 명 NAME TYPE SEQUENCE 객체의이름 객체의유형 (TABLE, PROCEDURE, FUNCTION, PACKAGE) 에러를표시하기위한일련번호 LINE 에러가발생한소스코드의행번호 POSITION 에러가발생한행의위치 TEXT 에러내용 75 USER_TRIGGER. 트리거의종류와상태정보를참조할수있습니다. 컬럼 설 명 TRIGGER_NAME 트리거의이름 TRIGGER_TYPE 트리거의유형 (BEFORE, AFTER, INSTEAD OF) TRIGGER_EVENT 트리거이벤트 (UPDATE, INSERT, DELETE) TABLE_LINE REFERENCING _NAMES WHEN_CLAUSE 트리거이벤트가되는테이블 :OLD, :NEW 에대해사용된이름 행레벨트리거에사용된 WHEN 절의내용 STATUS 트리거의사용가능여부 76

DESCRIBE 명령어. 트리거에사용된 IN/OUT 매개변수의상태를참조할수있습니다. SQL> DESCRIBE CHK_SAL FUNCTION CHK_SAL RETURN NUMBER Argument Name_Type In/Out Derfault -------------------------------------------------------------------------------- V_NO NUMBER IN 77 USER_DEPENDENCIES CREATE PROCEDURE A IS (V_NO NUMBER;). V_NO := B( SALES ); CREATE PROCEDURE B IS (V_JOB VARCHAR2;). SELECT * INTO.. FROM EMP; CREATE TABLE EMP (EMPNO NUMBER, ENAME VARCHAR2(10), JOB VARCHAR2(15),.. );. 상호간에참조하는 PL/SQL 블록의의존관계를참조할수있습니다. SQL> SELECT * FROM USER_DEPENDENCIES WHERE referenced_name IN ( EMP ); NAME Type Referenced_Name Referenced_Name ------------------------------------------------------------------------------------------- A PROCEDURE B PROCEDURE B PROCEDURE EMP TABLE 78

DEPTREE 와 IDEPTREE VIEW. 상호간에참조하는 PL/SQL 블록의의존관계를 TREE 형태로참조할수있습니다. SQL> @$HOME/rdbms/admin/utldtree.sql SQL> EXECUTE deptree_fill ( TABLE, SCOTT, EMP ); SQL> SELECT nested_level, type, name FROM deptree ORDER BY seq# ; Nasted_Level Type Name ------------------------------------------------------------------------------------------- 0 PROCEDURE A 1 PROCEDURE B 79