PL/SQL.

Presentation on theme: "PL/SQL."— Presentation transcript:

1 PL/SQL

2 개 요 PL/SQL의 필요성 및 특징 SQL의 특징 C나 COBOL 등의 특징
4세대 언어 간단하고 적은 수의 명령어를 가짐 사용자들에게 알고리즘을 은닉함 C나 COBOL 등의 특징 3세대 언어 절차적인 알고리즘으로 구현 위와 같은 각각의 언어들의 특징들을 사용하기 위하여 사용하게 된다. 즉 3세대 언어에서 가능한 절차적 구성이 4세대 언어에서 필요할 때가 있는데, 이런 경우를 위하여 PL/SQL이라는 언어가 도입 Oracle에서의 PL/SQL은 절차적프로그래밍을 할 수 있도록 SQL을 확장

3 PL/SQL의 기초 문자집합 식별자 대문자 A – Z, 소문자 a – z 숫자 0 – 9
기호 ( ) + - * / < > = ! ~ ^ ; : % , “ # $ & _ | { } [ ] ? 탭, 스페이스, 캐리지 리턴 식별자 상수, 변수, 예외 사항, 커서, 서브 프로그램 등을 포함하는 PL/SQL 프로그램 아이템과 유닛들의 이름 문자로 시작하고, 숫자, 달러 기호($), 언더스코어(_), 숫자기호(#) 등을 조합하여 사용 예제 money$$$tree, SN#, try_again (허용됨) mine&yours, debit-amout, on/off, user id(허용되지 않음) lastname, LastName, LASTNAME(같은 것으로 간주)

4 PL/SQL의 기초 키워드 키워드(keyword)라고 불리는 식별자들은 PL/SQL에서 특별한 의미를 가지고 있기 때문에, 상수와 변수 또는 커서 등에 대한 이름으로 사용할 수 없음(통상적으로 키워드는 가독성을 위하여 대문자로 사용) 예제 DECLARE begin number (키워드 begin을 사용하므로 에러) 주석 한 줄 주석을 사용 두줄 주석을 사용

5 --처리의 시작 SELECT sal INTO salary FROM emp WHERE empno = emp_id; -- 현재 월급을 가져옴 bonus := salary * 0.15 – 보너스의 계산 /* 등급이 우수한 직원들에게 15%의 보너스를 두고, 그 외에는 보너스를 주지 않음 */ IF rating > 90 THEN bonus := salary * 0.15 /* 월급에 기본을 두고 보너스 계산 */ ELSE bonus := 0; END IF;

6 PL/SQL의 기초 데이타 타입 숫자타입 BINARY_INTEGER : -2,147,483,647 ~ 2,147,483,647사이의 정수 SUBTYPE NATURAL : 음수를 제외한 정수(0 ~ 2,147,483,647) POSITIVE : 양의 정수(1 ~ 2,147,483,647) SIGNTYPE : -1, 0 , 1의 값만 가짐

7 NUMBER : 10-130 ~ 10125사이의 고정소수점이나 부동소수점 수
SUBTYPE DEC, DECIMAL, NUMERIC : 최대 38자리의 고정 소수점 수 DOUBLE PRECISION, FLOAT:최대 126자리 이진부동소수점 수 REAL : 최대 63자리 이진 부동 소수점 수 INTEGER, INT, SMALLINT : 최대 38자리의 정수 PLS_INTEGER : -231 ~ 231 사이의 정수

8 PL/SQL의 기초 데이타 타입 문자타입 CHAR : 고정 길이의 문자열을 저장. 32,767바이트까지 저장가능
VARCHAR2:가변길이문자열을 저장. 32,767바이트까지 저장가능 사용예 : VARCHAR2(최대길이) LONG, LONG RAW : 가변길이 문자열을 저장. RAW : 이진 데이타나 이진 문자열을 저장하기 위해 사용 사용예 : RAW(최대크기)

9 기타타입 BOOLEAN : 논리적인 참, 거짓, 널값을 저장하기 위해 사용. 오직 논리 연산만 허용.
DATE : 날짜와 시간을 저장하기 위해 DATE 타입을 사용. DATE 타입의 값은 자정 이후의 시간을 초 단위도 포함하여 저장.

10 PL/SQL의 구조 PL/SQL PL/SQL 블록 블록 구조의 언어 삽입 SQL문을 지원 프로시저와 함수의 선언
조건, 반복 과 같은 제어문 변수 선언 PL/SQL 블록 선언부 (생략가능) 실행부 (필수) 예외 상황 처리부 (생략가능)

11 선언부 실행부 예외 상황 처리부 Declare Begin Exception End; 생략가능 필수

12 선언부 변수선언 한줄에 하나의 변수만 선언 가능 DECLARE v_job VARCHAR2(9);
v_count BINARY_INTEGER := 0; v_total_sal NUMBER(9,2) := 0; v_hiredate DATE; v_valid BOOLEAN := FALSE; BEGIN

13 DEFAULT의 사용 := 연산자 대신에 키워드 DEFAULT를 사용하여 변수에 초기값을 할당 DECLARE
blood_type CHAR := ‘O’; -- blood_type CHAR DEFAULT ‘O’ BEGIN

14 선언부 변수선언 NOT NULL의 사용 : 초기값을 할당하는 것 이외에, NOT NULL 제약을 변수에 부여
%TYPE 속성의 사용 v_student_name은 이미 선언된 변수 v_name과 동일한 VARCHAR2(10) 으로 선언 DECLARE v_emp_id NUMBER(4) NOT NULL := 9999; --적합 v_dept_no NUMBER(2) NOT NULL; -- 에러 BEGIN DECLARE v_name VARCHAR2(10); v_student_name v_name%TYPE; v_st_name v_name%TYPE := ‘scott’ BEGIN

15 선언부 변수선언 %ROWTYPE 속성의 사용
Student 테이블이 두 애트리뷰트 name, grade를 포함하고 있을 때, 아래 선언의 결과로 v_student_rec와 v_stu_rec변수는 student 테이블의 레코드와 동일한 복합 변수로 선언 다음과 같은 SELECT 명령에서의 v_stu_rec와 같이 %ROWTYPE 속성은 SELECT 명령에서 행을 가져 올 때 매우 유용

16 DECLARE v_student_rec student%ROWTYPE; v_stu_rec student%ROWTYPE; BEGIN v_student_rec.name := ‘JOHNSON’; v_student_rec.grade := ‘A’; ⋮ SELECT * INTO v_stu_rec FROM student WHERE ... END;

17 선언부 상수선언 키워드 CONSTANT를 명시하고 반드시 기본값을 지정 DECLARE
c_num CONSTANT NUMBER := 100; BEGIN c_num := 200; --에러 END;

18 선언부 커서 선언 커서 : SQL명령을 실행할 때, 오라클 서버는 명령이 파싱 되고, 실행되는 메모리의 영역을 오픈하는데 이 영역을 커서(Cursor)라고 함. 하나의 행만 반환하는 질의를 포함한 모든 데이타 조작 SQL 명령에 대해서는 묵시적으로 커서를 선언하고, 하나 이상의 행을 반환하는 SELECT 명령에 대해서는 사용자가 명시적으로 커서를 선언

19 구 분 묵시적 커서 명시적 커서 생성 및 관리 DBMS 사용자 선 언 SELECT, DML문에 의해 DECLARE
CURSOR C1 IS SELECT 명령; BEGIN 속 성 %NotFound %Found %ISOpen(항상 False) %RowCount %ISOpen

20 선언부 커서선언 예제 c1, c2라는 이름의 두개의 커서를 선언
c1은 emp 테이블에서 월급이 2000보다 많은 직원들의 사원번호와 이름, 직업, 웕급을 가져오는 커서 c2는 dept테이블에서 부서번호가 10번인 부서의 레코드를 가져 오는 커서이고 반환 타입은 dept 테이블에 속한 모든 열들을 가져오도록 %ROWTYPE 속성을 이용

21 DECLARE CURSOR c1 IS SELECT empno, ename, job, sal FROM emp; WHERE sal > 2000; CURSOR c2 RETURN dept%ROWTYPE IS SELECT * FROM dept WHERE deptno = 10; BEGIN

22 실행부 SQL 명령 SELECT 명령에서 INTO 절을 사용하여 SELECT 명령에 의해 반환되는 결과를 PL/SQL의 변수에 저장가능 아래의 예제는 s_dept테이블에서 id가 10인 부서이름 name 을 검색하여 그 값을 v_name에 지정 DECLARE v_name s_dept.name%TYPE; BEGIN SELECT name INTO v_name FROM s_dept WHERE id = 10; ⋮ END;

23 실행부 SQL 명령 INSERT, UPDATE, DELETE SQL 명령으로 데이타를 삽입, 갱신, 삭제 가능 BEGIN
INSERT INTO emp(empno, ename) VAULES(8288, ‘TEST’); ⋮ UPDATE dept SET dname = ‘EDUCATION’ WHERE deptno = 10; DELETE FROM emp WHERE deptno = 20; END;

24 실행부 IF문 IF – THEN IF – THEN – ELSE IF 조건 THEN 문; END IF; IF 조건 THEN
문1; ELSE 문2; END IF;

25 IF – THEN – ELSIF IF 조건1 THEN 문1; ELSEIF 조건2 THEN 문2; [ELSEIF 조건3 THEN
문3;] … [ELSE 문4;] END IF;

26 실행부 반복 실행문 반복실행문은 명령들을 여러 번 반복해서 수행 반복 실행문 : LOOP, WHILE, FOR문 LOOP

27 LOOP … EXIT WHEN 조건; END LOOP;

28 실행부 반복 실행문 WHILE 루프 WHILE 조건 LOOP … END LOOP;

29 실행부 반복 실행문 FOR 루프 FOR 루프변수 IN [REVERSE] 하한..상한 LOOP … END LOOP;

30 실행부 GOTO 문 PL/SQL에서는 GOTO문을 이용하여 무조건 분기
… <<레이블>> BEGIN … GOTO insert_row; <<insert_row>> INSERT INTO emp VALUES … END;

31 실행부 NULL 문 NULL 문은 아무 일도 하지 않는다는 사실을 명시적으로 나타냄 IF rating > 90 THEN
compute_bonus(emp_id); ELSE NULL; END IF;

32 실행부 커서의 조작 커서는 OPEN, FETCH, CLOSE문을 이용하여 제어
커서의 오픈 DECLARE CURSOR c1 IS SELECT empno, ename, job, sal FROM emp WHERE sal > 2000; ... BEGIN OPEN c1; END;

33 실행부 커서의 조작 데이타의 FETCH FETCH 명령은 결과 집합에서 한 번에 한 행만을 가져옴
FETCH c1 INTO my_empno, my_ename, my_deptno; BEGIN LOOP FETCH c1 INTO my_record; EXIT WHEN c1%NOTFOUND; … -- 데이타 레코드의 처리 END LOOP … END;

34 실행부 커서의 조작 데이타의 FETCH FOR LOOP을 이용한 예제

35 실행부 커서의 조작 CLOSE 명령은 커서를 비활성화 결과 집합이 정의 되지 않은 상태로 만듬
종료된 커서에 대한 연산은 미리 정의된 예외인 INVALID_CURSOR를 발생 DECLARE CURSOR c1 IS -- 커서의 선언 SELECT … BEGIN OPEN c1; -- 커서 오픈 … FETCH c1 INTO …; -- 데이타의 FETCH CLOSE c1; -- 커서 종료 END;

36 실행부 커서의 조작 명시적 커서의 속성 %FOUND
커서를 오픈했을 때 첫번째 FETCH가 실행되기 전에 %FOUND는 NULL값 BEGIN … LOOP FETCH c1 INTO my_ename, my_sal, my_hiredate; IF c1%FOUND THEN -- FETCH의 성공 … -- 데이타의 처리 ELSE EXIT; END IF; END LOOP; END;

37 실행부 커서의 조작 %ISOPEN BEGIN … IF c1%ISOPEN THEN -- 커서가 오픈된 상태 ELSE
OPEN c1; END IF END;

38 실행부 커서의 조작 %NOTFOUND BEGIN LOOP
%NOTFOUND는 %FOUND와 논리적으로 반대 의미 가장 최근에 FETCH가 행을 반환하면 %NOTFOUND는 거짓 가장 최근의 FETCH가 행을 반환하는데 실패하면 참 BEGIN LOOP FETCH c1 INTO my_ename, my_sal, my_hiredate; EXIT WHEN c1%NOTFOUND; … END LOOP; END;

39 실행부 커서의 조작 %ROWCOUNT 커서를 오픈했을 때, %ROWCOUNT는 0
첫번째 FETCH 이전에는 %ROWCOUNT는 0 %ROWCOUNT는 계 속 가져온 행들의 수를 기록 FETCH가 행을 가져오는데 성공할 때마다, %ROWCOUNT의 값은 하나씩 증가 BEGIN … LOOP FETCH c1 INTO my_ename, my_sal, my_hiredate; IF c1%ROWCOUNT > 10 THEN … -- 데이타의 처리 END IF; END LOOP; END;

40 예외 상황 처리부 예외 상황 사용의 장점 에러 처리 루틴을 분리함으로써 가독성을 향상시킴 안정성을 향상 시킴 BEGIN
SELECT ... ... EXCEPTION WHEN NO_DATA_FOUND THEN --모든 ‘no data found’ 에러를 처리

41 예외 상황 처리부 미리 정의된 예외 상황 예외 상황 Oracle Error SQL CODE값 내 용 No_Data_Found
+100 행이 한건도 반환되지 않음 Too_Many_Rows ORA-01422 -1422 행이 두개 이상 반환 Zero_Divide ORA-01476 -1476 제수가 0일 때 Invalid_Number ORA-01722 -1722 숫자 형태가 아닌 값을 숫자변수에 대입할 때 Login_Denied ORA-01017 -1017 잘못된 userid/password로 접속을 시도 했을 때 Not_Logged_On ORA-01012 -1012 오라클에 접속하지 않고 PL/SQL을 수행했을 때 Others 위에서 명세되지 않은 예외 상황 미리 정의된 예외 상황

42 예외 상황 처리부 사용자 정의 예외 상황 예외 상황의 선언 예외 상황의 명시적인 발생 DECLARE
미리 정의된 예외 상황과는 달리 RAISE명령에 의해 명시적으로 발생 DECLARE past_due EXCEPTION; RAISE out_of_stock;

43 예외 상황 처리부 예외 상황 전파 에러 발생시 가장 가까운 예외 상황 처리부를 찾아 실행 DECLARE temp NUMBER;
v_salary NUMBER; BEGIN SELECT salary INTO v_salary FROM s_emp WHERE id = 1; v_start_date DATE; v_date DATE; SELECT start_date INTO v_date FROM s_emp WHERE id = 99; v_date := v_start_date + 15; EXCEPTION WHEN No_Data_Found THEN message('데이타 없음'); END; temp := v_salary * 12; WHEN Too_Many_Rows THEN message('중복 데이타');

44 예외 상황 처리부 예외 상황 전파

45 서브프로그램 프로시저 구조 저장 프로시저: 재사용 가능
PROCEDURE 프로시저이름 [(매개변수1, ... [,매개변수n])] IS [변수선언] BEGIN 실행부; [예외상황 처리부] END [프로시저이름]; CREATE [OR REPLACE] PROCEDURE 프로시저이름 [(매개변수1, ... [,매개변수n])] IS [변수선언] BEGIN 실행부; [예외상황 처리부] END [프로시저이름];

46 서브프로그램 프로시저 1. emp_audit raise_salary(7844, 1000); 테이블 생성 2. emp_audit
테이블의 데이타 확인

47 서브프로그램 프로시저 raise_salary 프로시저를 포함한 PL/SQL 블록을 실행한 후, emp 테이블에서 결과를 다시 확인 3. raise_salary 프로시저 예제 실행화면

48 서브프로그램 프로시저 raise_salary(8528, 100) - empno가 8528인 emp 행이 없기 때문에 emp_audit 테이블에 에러 메시지가 저장 4. emp 테이블의 데이타와 에러메시지 확인

49 서브프로그램 프로시저 프로시저를 오라클 데이타베이스에 저장하여, 저장 프로시저로 만들면 영구적으로 재사용이 가능
CREATE [OR REPLACE] 명령을 사용하여 프로시저를 저장 OR REPLACE절은 동일한 이름의 프로시저가 이미 저장되어 있다면 그 프로시저의 내용을 지금 작성 하는 프로시저로 변경하라는 의미 CREATE [OR REPLACE] PROCEDURE 프로시저 이름 [(인자1, … [, 인자 n])] IS [변수선언] BEGIN 실행부; [예외상황 처리부] END [프로시저 이름];

50 서브프로그램 프로시저 앞의 예제

51 서브프로그램 프로시저 자신이 어떤 프로시저를 만들었는지 알고 싶다면 user_objects 데이타 딕셔너리에 질의
저장 프로시저의 인자에 대한 정보를 검색해 보고 싶으면 DESC 명령을 사용

52 서브프로그램 함수 프로시저와 유사 프로시저와의 차이점 : 함수는 미리 정의된 데이타 타입의 값을 반환
FUNCTION 함수이름 [(인자1, … [, 인자 n])] RETURN 함수_데이타 타입 IS [변수선언] BEGIN 실행부; [예외 상황 처리부] END [함수이름];

53 서브프로그램 함수 직업이 title이고 봉급이 salary일 때, sals 테이블에 기록된 최소 봉급과 최대 봉급 범위에 salary가 해당하는지 여부를 나타내는 예제 함수 이름 sal_ok는 RETURN 명령에 의해 Boolean 값을 가짐 FUNCTION sal_ok(salary REAL, title VARCHAR2) RETURN BOOLEAN IS min_sal REAL; max_sal REAL; BEGIN SELECT low_sal, high_sal INTO min_sal, max_sal FROM sals WHERE job = title; RETURN (salary >= min_sal) AND (salary <= max_sal); END sal_ok;

54 서브프로그램 1. sals 테이블 생성 함수 2. sals 테이블에 데이타 입력

55 서브프로그램 3. sal_ok 함수 예제의 실행 함수

56 서브프로그램 4. 저장함수 sal_ok의 생성 함수 5. 저장함수에 대한 정보 보기

57 서브프로그램 서브 프로그램의 인자 서브 프로그램에서 사용되는 인자들의 유형 IN IN(디폴트) , OUT, IN OUT
인자값을 함수나 프로시저에 전달할 뿐, 값을 지정 하거나 값을 반환할 수 없음 확인 예제 PROCEDURE debit_account (acct_id IN INTEGER, amount IN REAL) IS minimum_purchase CONSTANT REAL := 10.0; service_charge CONSTANT REAL := 0.50; BEGIN … IF amount < minimum_purchase THEN amount := amount + service_charge; -- 컴파일 에러 발생 END IF; END;

58 서브프로그램 서브 프로그램의 인자 OUT 값을 참조하지 않고, 서브 프로그램을 호출한 PL/SQL문에 값을 반환하는데 사용
확인예제 PROCEDURE calc_bonus (emp_id IN INTEGER, bonus OUT REAL) IS hire_date DATE; BEGIN SELECT sal * 0.10, hiredate INTO bonus, hire_date FROM emp WHERE empno = emp_id; IF MONTHS_BETWEEN(SYSDATE, hire_date) > 60 THEN bonus := bonus + 500; -- 에러발생 END IF; END;

59 서브프로그램 서브 프로그램의 인자 IN OUT 인자값을 서브프로그램에 전달하거나 호출 PL/SQL문에 값을 반환시 사용
PROCEDURE calc_bonus (emp_id IN INTEGER, bonus IN OUT REAL) IS hire_date DATE; bonus_missing EXCEPTION; BEGIN SELECT sal * 0.10, hiredate INTO bonus, hire_date FROM emp WHERE empno = emp_id; IF bonus IS NULL THEN RAISE bonus_missing; END IF; IF MONTHS_BETWEEN(sysdate, hire_date) > 60 THEN bonus := bonus + 500; EXCEPTION WHEN bonus_missing THEN END calc_bonus;

60 실행부 커서의 조작 커서는 OPEN, FETCH, CLOSE문을 이용하여 제어
커서의 오픈

61 DECLARE CURSOR c1 IS SELECT empno, ename, job, sal FROM emp WHERE sal > 2000; ... BEGIN OPEN c1; END;

62 실행부 커서의 조작 데이타의 FETCH FETCH 명령은 결과 집합에서 한 번에 한 행만을 가져옴

63 FETCH c1 INTO my_empno, my_ename, my_deptno;
BEGIN LOOP FETCH c1 INTO my_record; EXIT WHEN c1%NOTFOUND; … -- 데이타 레코드의 처리 END LOOP … END;

64 실행부 커서의 조작 데이타의 FETCH FOR LOOP을 이용한 예제

65

66 실행부 커서의 조작 CLOSE 명령은 커서를 비활성화 결과 집합이 정의 되지 않은 상태로 만듬
종료된 커서에 대한 연산은 미리 정의된 예외인 INVALID_CURSOR를 발생

67 DECLARE CURSOR c1 IS -- 커서의 선언 SELECT … BEGIN OPEN c1; -- 커서 오픈 … FETCH c1 INTO …; -- 데이타의 FETCH CLOSE c1; -- 커서 종료 END;

68 실행부 커서의 조작 명시적 커서의 속성 %FOUND
커서를 오픈했을 때 첫번째 FETCH가 실행되기 전에 %FOUND는 NULL값

69 BEGIN … LOOP FETCH c1 INTO my_ename, my_sal, my_hiredate; IF c1%FOUND THEN -- FETCH의 성공 … -- 데이타의 처리 ELSE EXIT; END IF; END LOOP; END;

70 실행부 커서의 조작 %ISOPEN %ISOPEN은 커서가 오픈되었으면 참, 그렇지 않으면 거짓

71 BEGIN … IF c1%ISOPEN THEN -- 커서가 오픈된 상태 ELSE OPEN c1; END IF END;

72 실행부 커서의 조작 %NOTFOUND %NOTFOUND는 %FOUND와 논리적으로 반대 의미
가장 최근에 FETCH가 행을 반환하면 %NOTFOUND는 거짓 가장 최근의 FETCH가 행을 반환하는데 실패하면 참

73 BEGIN LOOP FETCH c1 INTO my_ename, my_sal, my_hiredate; EXIT WHEN c1%NOTFOUND; … END LOOP; END;

74 실행부 커서의 조작 %ROWCOUNT 커서를 오픈했을 때, %ROWCOUNT는 0
첫번째 FETCH 이전에는 %ROWCOUNT는 0 %ROWCOUNT는 계속 가져온 행들의 수를 기록 FETCH가 행을 가져오는데 성공할 때마다, %ROWCOUNT의 값은 하나씩 증가

75 BEGIN … LOOP FETCH c1 INTO my_ename, my_sal, my_hiredate; IF c1%ROWCOUNT > 10 THEN … -- 데이타의 처리 END IF; END LOOP; END;

76 예외 상황 처리부 예외 상황 사용의 장점 에러 처리 루틴을 분리함으로써 가독성을 향상시킴 안정성을 향상 시킴 BEGIN
SELECT ... ... EXCEPTION WHEN NO_DATA_FOUND THEN --모든 ‘no data found’ 에러를 처리

77 예외 상황 처리부 미리 정의된 예외 상황

78 예외 상황 Oracle Error SQL CODE값 내 용 No_Data_Found ORA-01403 +100 행이 한건도 반환되지 않음 Too_Many_Rows ORA-01422 -1422 행이 두개 이상 반환 Zero_Divide ORA-01476 -1476 제수가 0일 때 Invalid_Number ORA-01722 -1722 숫자 형태가 아닌 값을 숫자변수에 대입할 때 Login_Denied ORA-01017 -1017 잘못된 userid/password로 접속을 시도 했을 때 Not_Logged_On ORA-01012 -1012 오라클에 접속하지 않고 PL/SQL을 수행했을 때 Others 위에서 명세되지 않은 예외 상황

79 예외 상황 처리부 사용자 정의 예외 상황 예외 상황의 선언 예외 상황의 명시적인 발생 DECLARE
미리 정의된 예외 상황과는 달리 RAISE명령에 의해 명시적으로 발생 DECLARE past_due EXCEPTION; RAISE out_of_stock;

80 예외 상황 처리부 예외 상황 전파 에러 발생시 가장 가까운 예외 상황 처리부를 찾아 실행

81 DECLARE temp NUMBER; v_salary NUMBER; BEGIN SELECT salary INTO v_salary FROM s_emp WHERE id = 1; v_start_date DATE; v_date DATE; SELECT start_date INTO v_date FROM s_emp WHERE id = 99; v_date := v_start_date + 15; EXCEPTION WHEN No_Data_Found THEN message('데이타 없음'); END; temp := v_salary * 12; WHEN Too_Many_Rows THEN message('중복 데이타');

82 예외 상황 처리부 예외 상황 전파

83

84 서브프로그램 프로시저 구조 PROCEDURE 프로시저이름 [(매개변수1, ... [,매개변수n])] IS [변수선언]
BEGIN 실행부; [예외상황 처리부] END [프로시저이름];

85 저장 프로시저: 재사용 가능 CREATE [OR REPLACE] PROCEDURE 프로시저이름
[(매개변수1, ... [,매개변수n])] IS [변수선언] BEGIN 실행부; [예외상황 처리부] END [프로시저이름];

86 서브프로그램 프로시저 1. emp_audit 테이블 생성 raise_salary(7844, 1000);

87 2. emp_audit 테이블의 데이타 확인

88 서브프로그램 프로시저 raise_salary 프로시저를 포함한 PL/SQL 블록을 실행한 후, emp 테이블에서 결과를 다시 확인

89 3. raise_salary 프로시저 예제 실행화면

90 서브프로그램 프로시저 raise_salary(8528, 100) - empno가 8528인 emp 행이 없기 때문에 emp_audit 테이블에 에러 메시지가 저장

91 4. emp 테이블의 데이타와 에러메시지 확인

92 서브프로그램 프로시저 프로시저를 오라클 데이타베이스에 저장하여, 저장 프로시저로 만들면 영구적으로 재사용이 가능
CREATE [OR REPLACE] 명령을 사용하여 프로시저를 저장 OR REPLACE 동일한 이름의 프로시저가 이미 저장되어 있다면 그 프로시저의 내용을 지금 작성 하는 프로시저로 변경하라는 의미

93 CREATE [OR REPLACE] PROCEDURE 프로시저 이름
[(인자1, … [, 인자 n])] IS [변수선언] BEGIN 실행부; [예외상황 처리부] END [프로시저 이름];

94 서브프로그램 프로시저 앞의 예제

95 서브프로그램 프로시저 자신이 어떤 프로시저를 만들었는지 알고 싶다면 user_objects 데이타 딕셔너리에 질의

96 저장 프로시저의 인자에 대한 정보를 검색해 보고 싶으면 DESC 명령을 사용

97 서브프로그램 함수 프로시저와 유사 프로시저와의 차이점 : 함수는 미리 정의된 데이타 타입의 값을 반환
FUNCTION 함수이름 [(인자1, … [, 인자 n])] RETURN 함수_데이타 타입 IS [변수선언] BEGIN 실행부; [예외 상황 처리부] END [함수이름];

98 서브프로그램 함수 직업이 title이고 봉급이 salary일 때, sals 테이블에 기록된 최소 봉급과 최대 봉급 범위에 salary가 해당하는지 여부를 나타내는 예제 함수 이름 sal_ok는 RETURN 명령에 의해 Boolean 값을 가짐

99 FUNCTION sal_ok(salary REAL, title VARCHAR2)
RETURN BOOLEAN IS min_sal REAL; max_sal REAL; BEGIN SELECT low_sal, high_sal INTO min_sal, max_sal FROM sals WHERE job = title; RETURN (salary >= min_sal) AND (salary <= max_sal); END sal_ok;

100 서브프로그램 1. sals 테이블 생성 함수

101 2. sals 테이블에 데이타 입력

102 서브프로그램 3. sal_ok 함수 예제의 실행 함수

103 서브프로그램 4. 저장함수 sal_ok의 생성 함수

104 5. 저장함수에 대한 정보 보기

105 서브프로그램 서브 프로그램의 인자 서브 프로그램에서 사용되는 인자들의 유형 IN IN(디폴트) , OUT, IN OUT
인자값을 함수나 프로시저에 전달할 뿐, 값을 지정 하거나 값을 반환할 수 없음 확인 예제

106 PROCEDURE debit_account (acct_id IN INTEGER, amount IN REAL) IS
minimum_purchase CONSTANT REAL := 10.0; service_charge CONSTANT REAL := 0.50; BEGIN … IF amount < minimum_purchase THEN amount := amount + service_charge; -- 컴파일 에러 발생 END IF; END;

107 서브프로그램 서브 프로그램의 인자 OUT 값을 참조하지 않고, 서브 프로그램을 호출한 PL/SQL문에 값을 반환하는데 사용
확인예제

108 PROCEDURE calc_bonus (emp_id IN INTEGER, bonus OUT REAL) IS
hire_date DATE; BEGIN SELECT sal * 0.10, hiredate INTO bonus, hire_date FROM emp WHERE empno = emp_id; IF MONTHS_BETWEEN(SYSDATE, hire_date) > 60 THEN bonus := bonus + 500; -- 에러발생 END IF; END;

109 서브프로그램 서브 프로그램의 인자 IN OUT 인자값을 서브프로그램에 전달하거나 호출 PL/SQL문에 값을 반환시 사용

110 PROCEDURE calc_bonus (emp_id IN INTEGER, bonus IN OUT REAL) IS
hire_date DATE; bonus_missing EXCEPTION; BEGIN SELECT sal * 0.10, hiredate INTO bonus, hire_date FROM emp WHERE empno = emp_id; IF bonus IS NULL THEN RAISE bonus_missing; END IF; IF MONTHS_BETWEEN(sysdate, hire_date) > 60 THEN bonus := bonus + 500; EXCEPTION WHEN bonus_missing THEN END calc_bonus;