PL/SQL 문법 부 프로그램 (프로시저, 함수) 패키지&트리거

Presentation on theme: "PL/SQL 문법 부 프로그램 (프로시저, 함수) 패키지&트리거"— Presentation transcript:

1 PL/SQL 문법 부 프로그램 (프로시저, 함수) 패키지&트리거

2 PL/SQL 문법

3 context PL/SQL의 특징 PL/SQL의 구조 PL/SQL의 자료 형과 선언 PL/SQL의 흐름제어(조건문, 반복문)
PL/SQL내에서의 SQL문 커서(Cursor) 오류 처리

4 PL/SQL 특징(1) Procedural Language/SQL의 약자
3GL의 프로시저의 구성과 SQL(4GL)의 장점을 결합 추가 기능 변수와 형의 선언 IF-THEN-ELSE문이나 루프와 같은 제어 구조 프로시저와 함수

5 PL/SQL 특징(2) 오라클 데이터베이스 서버 클라이언트 프로그램 SQL

6 PL/SQL 구조(1) 기본적인 단위는 블록 각 블록은 논리적인 작업 단위 문법 DECLARE BEGIN EXCEPTION
블록의 메인섹션이고, 반드시 필요한 섹션이다. */ EXCEPTION /* 예외 처리부 - 에러 처리 문장이 여기에 온다. */ END;

7 PL/SQL 구조(2) 간단한 작성과 실행

8 PL/SQL 구조(3) 출력은 DBMS_OUTPUT 패키지가 담당
패키지 안에 dbms_output.put_line 프로시저 사용 출력을 SQL*Plus에서 보려면 SET SERVEROUTPUT ON을 먼저 실행 orahome/sqlplus/admin/glogin.sql에서 set serveroutput on 입력

9 PL/SQL 구조(4)

10 PL/SQL 구조(5)

11 PL/SQL 자료 형 문자형 : CHAR, VARCHAR2 수치형 : NUMBER 날짜형 : DATE
기타 : %TYPE, %ROWTYPE

12 선언부 PL/SQL에서 사용되는 모든 변수와 상수, 커서를 선언하는 부분 문법 DECLARE
변수명 [CONSTANT] 변수형 [NOT NULL] [:= DEFAULT|expr] ; BEGIN

13 변수 선언 한 줄에 하나의 변수만 선언 가능 예제 주석문 DECLARE customer_no NUMBER :=100;
한 줄 단위의 주석은 ‘--’ 한 줄 이상의 주석은 ‘/*’시작하고 '*/'로 끝남 DECLARE customer_no NUMBER :=100; customer_name VARCHAR2(20); customer_addr customer.c_addr%TYPE; BEGIN

14 상수 선언 예제 일단 선언된 상수에 대해서는 실행부에서 값을 다시 지정할 수 없다.

15 PL/SQL의 흐름제어 조건문 IF문 CASE문 반복문 LOOP문 WHILE문 FOR문

16 조건문 – IF(1) 조건을 BOOLEAN 값으로 평가 IF - THEN IF 조건 THEN
실행문 조건을 만족하면 실행문을 실행 END IF ;

17 조건문 – IF(2) IF - THEN - ELSE IF 조건 THEN 실행문 1 ; ELSE 실행문 2 ; END IF ;
IF - THEN - ELSIF - ELSE IF 조건 1 THEN 실행문 1 ; ELSIF 조건 2 THEN 실행문 2 ; ELSE 실행문 3 ; END IF ;

18 조건문 – IF(3)

19 조건문 – CASE(1) 오라클 9i 이후 추가된 구문 조건이 여러 개 인 경우 유용 형식 1 형식 2 case 값
when 표현식 then 실행문 [when 표현식 then 실행문] [else 실행문] case when 조건 then 실행문 [when 조건 then 실행문] [else 실행문]

20 조건문 – CASE(2)

21 조건문 – CASE(3)

22 반복문 – LOOP(1) 문법 LOOP ... EXIT WHEN 조건; END LOOP;

23 반복문 – LOOP(2)

24 반복문 - WHILE LOOP(1) 문법 WHILE 조건 LOOP ... END LOOP;

25 반복문 - WHILE LOOP(2)

26 반복문 - FOR LOOP(1) 문법 FOR 루프변수 IN [REVERSE] 하한..상한 LOOP ... END LOOP ;

27 반복문 - FOR LOOP(2)

28 Embedded SQL PL/SQL내에서 사용될 수 있는 SQL문 PL/SQL내에서 사용될 수 없는 SQL문 DML문
트랜잭션 제어 명령어(COMMIT, ROLLBACK) PL/SQL내에서 사용될 수 없는 SQL문 DDL문 * DBMS_SQL Package를 사용하여 처리 * 기존의 SQL문법 그대로 사용 (SELECT의 경우제외)

29 Select문의 사용(1) PL/SQL내에서 Select문은 데이터 추출에 사용
구조 SELECT column_list INTO variable_list FROM table WHERE condition;

30 Select문의 사용(2)

31 Select문의 사용(3) Select 문의 잘못된 사용 예1

32 Select문의 사용(4) Select 문의 잘못된 사용 예2

33 Insert문의 사용(1)

34 Insert문의 사용(2)

35 Update문의 사용(1)

36 Update문의 사용(2)

37 Delete문의 사용(1)

38 Delete문의 사용(2)

39 Delete문의 사용(3)

40 Delete문의 사용(4)

41 커서 선언 커서란? 커서의 종류 커서의 속성 명시적 커서 암시적 커서 %FOUND : 가장 최근에 인출한 행이 있으면 TRUE
%NOTFOUND : 가장 최근에 인출한 행이 없으면 TRUE %ROWCOUNT : 가장 최근에 인출한 행의 개수 %ISOPEN : 커서가 열려있으면 TRUE

42 명시적 커서(1) 커서를 관리하는 데 필요한 모든 코드를 사용자가 직접 작성
query 결과를 첫 번째 행부터 차례대로 처리 가능 현재 처리중인 행을 추적 커서의 Life-cycle은 사용자의 제어에 달려있으며, 결과 셋 내의 레코드 접근에 대한 세부적인 수준의 제어 가능 여러 행 질의에 의해 반환되는 행 집합을 활성 집합이라 하고 활성집합의 크기는 검색조건을 만족하는 행수와 같음

43 명시적 커서(2) 커서의 선언 커서의 Life-cycle select * from branch; cursor
[RETURN 리턴타입] IS 필요한 SELECT문 OPEN : 커서가 반환될 값 들 중 첫 번째 열을 가리킴 FETCH : 현재 열을 읽어 들인 후, 다음 열로 커서 이동 CLOSE : 커서 종료 Br100 서울중구 서울시 중구 Br200 서울용산 서울시 용산구 Br300 경기구리 경기도 구리시 Br400 경기성남 경기도 성남시 select * from branch; cursor

44 명시적 커서(3)

45 명시적 커서(4)

46 명시적 커서 – 파라미터 사용(1)

47 명시적 커서 – 파라미터 사용(2)

48 명시적 커서 – 파라미터 사용(3)

49 명시적 커서 – 파라미터 사용(4)

50 암시적 커서(1) 암시적 커서란? 암시적 커서 모든 sql문에는 관련된 개별 커서가 존재
sql문을 실행하면 pl/sql은 암시적 커서를 작성하여 자동관리 암시적 커서 Embedded SQL 결과 셋 Cursor For Loop

51 암시적 커서(2) * Embedded SQL 결과셋 생성시 사용되는 커서를 암시적으로 사용한 예

52 암시적 커서(3)

53 암시적 커서(4)

54 암시적 커서(5) * Cursor For Loop를 사용한 암시적 커서의 예

55 오류 처리 PL/SQL의 오류처리 메커니즘 -> 예외(Exception)
예외 발생시 프로그램의 제어가 블록의 예외 처리부로 넘어간다. 예외의 종류 미리 정의된 예외 사용자 정의 예외 PRAGMA EXCEPTION_INIT

56 예외 처리 형식 문법 : 실행부 내의 EXCEPTION 절 BEGIN … EXCEPTION
WHEN 예외1[OR 예외2 ... ] THEN 문장; ... [WHEN 예외3 [OR 예외4 ... ] THEN ] [WHEN OTHERS THEN END;

57 예제 실행 화면

58 예제 실행 화면

59 미리 정의된 예외 미리 정의된 예외 NO_DATA_FOUND TOO_MANY_ROWS ZERO_DIVIDE
PL/SQL 코드에서 자주 발생하는 오류 선언부에 선언이 필요없음 실행중 정의된 오류 발생시 예외부로 자동 분기 NO_DATA_FOUND TOO_MANY_ROWS ZERO_DIVIDE INVALID_NUMBER INVALID_CURSOR OTHERS

60 사용자 정의 예외 사용자 정의 예외 ☞ 명시적으로 예외 발생(EXCEPTION절로 분기) 사용자가 설정한 조건
선언부에서 예외에 대한 선언이 필요 실행부에서 RAISE문을 사용하여 예외 발생 ☞ 명시적으로 예외 발생(EXCEPTION절로 분기)

61 사용자 정의 예외 DECLARE A EXCEPTION : BEGIN … RAISE A: ... EXCEPTION
WHEN A THEN END ; 예외 A가 선언 예외 A가 발생 여기에 있는 어떤 코드도 실행되지 않는다. 제어가 예외 핸들러에 전달.여기의 코드가 실행

62 예제 실행 화면

63 PRAGMA EXCEPTION_INIT
미리 정의되지 않은 예외 미리 정의된 예외를 제외한 모든 오류 선언부에 선언이 필요 실행중 오류 발생시 예외절로 자동 분기 DECLARE A EXCEPTION; PRAGMA EXCEPTION_INIT(A,-1400); BEGIN …. EXCEPTION WHEN A THEN 선언된 예외 이름과 오라클 서버 오류 번호를 결합하는 선언문

64 예제 실행화면(1)

65 예제 실행화면(2)