[자바 기초] 10일차 일지 2 - Oracle PL/SQL 이어서(ORACLE SUBPROGRAM)

15. ORACLE SUBPROGRAM들 배우기

                                    ㄴ>부, 보조 -> 함수, 메소드, 프로시져

서브 프로그램

PL 블록(Begin ~ End;) -> 서브프로그램이라고 한다 -> 함수/프로시저로 감싼다.

 

1. 프로시저

2. 함수

 

                     프로시저      함수(function)

공통점 :             f1(   )              f2(   )

구분/차이점 :    반환값X           반환값O

 

네모 속 y = x+5 는 프로시저이면서 함수

그러나 x에 10을 집어넣고 15라는 값이 나오면 그것은 -> 함수

 

f1(price) <- in이 price

f2() <- in이 없는 것

 

f1() 자체가 값이 되면 Out이 있는것(return값 = 반환값) -> 함수

값이 안되면 Out이 없는 것(return값, 반환값이 없는 것) -> 프로시저

=> in은 있건아니건 상관 없다. 반환값의 유무가 핵심

 

---

1. PROCEDURE (프로시저)

• 프로시저는 지정된 특정 처리를 실행하는 서브 프로그램의 한 유형
• 단독(standalone)으로 실행되거나 다른 프로시저나 다른 툴(Oracle Developer…)
• 또는 다른 환경(Pro*C…)등에서 호출되어 실행됨
• 생성문법

프로스저 생성문법

- Parameter Mode 비교

Parameter Mode 비교

 

f1(이 부분이 parameter)

 

실습1. 부서번호가 20번 인 사람들의 job을 'CLERK'으로 변경하는 프로시저

프로시저 실습 1

 

실습5. 생성된 프로시저의 내용을 확인합니다.

SELECT text
FROM user_source
WHERE name ='UP_SAL' ;

위의 7개의 행이 보여짐

 

2. FUNCTION(내장 함수) (<-> 외장 : 사용자정의)

내장 함수와 프로시저는 문법이나 특징이 거의 비슷하지만 차이점은 프로시저는 정해진 작업을 수행한 후 결과를 반환 할 수도 있고(OUT, IN OUT 모드 사용시) 반환 하지 않고 그냥 종료할 수도 있지만 함수는 정해진 작업을 수행 한 후 결과를 돌려준다(RETURN)는 부분임

내장함수 문법

 

함수 예제 1. 부서번호를 입력 받아 최고 급여액을 출력하는 함수

함수예제 1

 

함수 예제 5. 생성된 함수 조회하기

HR>SELECT text
  2  FROM user_source
  3  WHERE type='FUNCTION'
  4  AND name='MAX_SAL' ;

이렇게 11개의 행이 나옴

 

3. ORACLE PACKAGE (패키지)

• 패키지는 연관성이 높은 함수나 프로시저를 하나의 그룹으로 묶어두는 개념
• 패키지 선언부(Spec)와 패키지 몸체부(body)로 구성
• 패키지 선언부의 역할은 해당 패키지에 사용될 함수나 프로시저, 변수등에 대한 정의를 선언하는 부분
• 패키지 몸체부에서는 선언부에서 선언된 함수나 프로시저등이 실제 구현되는 부분임.
• 패키지 선언부에서 선언되지 않더라도 패키지 몸체부에서 사용될 수는 있지만 별로 권장사항은 아니니 가급적 선언부에서 선언하신 후 몸체부에서 사용해야 함

 

프로시저1, 프로시저2

함수1, 함수2

-> 이렇게 네개를 만들었으면 패키지 1로 묶어서 사용하는 것(포장, Group시킴)

 

1) PACKAGE 구조 : 선언부와 몸체부로 구성됨

- 패키지 선언부 생성

패키지 선언부 생성

p1이라는 패키지를 생성했으면 실행은

p1.proc1()

p1.proc2()

p1.f1()

이런 식으로 함

 

- 패키지 몸체부(Package Body) 생성

- Subprogram bodies

이 부분이 실제 작동할 서브 프로그램(프로시저, 함수 등)을 기록하는 부분입니다.

단 주의해야 할 사항은 서브프로그램의 순서입니다. 기본적으로 참조되는 변수든 서브프로그램이든 참조하는 서브프로그램보다는 먼저 정의되어야 합니다. 일반적으로 PUBLIC 의 서브프로그램은 마지막 부분에 정의합니다

 

2) 패키지 실행하기

 

패키지는 여러 환경에서 호출되어 실행될 수 있지만 생성된 패키지 오브젝트에 대한 실행권한을 가진 사용자만이 패키지를 호출하여 실행할 수 있습니다.

 

3) 패키지 삭제

패키지를 삭제할 때에는 패키지 선언부와 패키지 몸체부를 모두 삭제할 수 도 있고 패키지 몸체부만 삭제할 수도 있습니다.

 

DROP PACKAGE package_name ;

DROP PACKAGE BODY package_name ;

 

4) Package 사용 예

- 패키지는 함수도 쌀 수 있지만 주로 프로시저를 둘러싼다(그룹화?) -> 묶어서 사용한다.

 

예 1) Emp table 에서 총 급여합계와 평균 급여를 구하는 package 입니다

create or replace package emp_total
as
procedure emp_sum;
procedure emp_avg;   -- procedure 2개를 만들거라는 패키지를 만들거라고 선언 -> 2개를 BODY에서 정의해줘야 함
END emp_total;

패키지 선언부 입니다(Declare, 보통 변수 선언할 때 씀) 

 Define(정의) - 보통 프로시저는 정의를 함

Emp_sum, emp_avg 프로시저로 구성된 것을 볼 수 있습니다.

EMP_TOTAL 패키지 내용

create or replace package body emp_total as -- 요기서 위에 얘기한 2개의 procedure를 정의함 : emp_total이라는 이름으로 emp_sum, emp_avg를 묶음
	procedure emp_sum   -- 1번째 프로시저 emp_sum 정의 시작
	is
		cursor emp_total_sum is    -- 커서 emp_total_sum (줄 단위로 바뀌며 임시로 값을 담는다) -> 어떻게 보면 변수라고 볼 수도 있다(OPRN, FETCH를 해서 쓰겠구나! END로 닫아 주겠구나!)
 		  select count(*), sum(NVL(salary,0))   -- salary를 더하는데 null이면 0으로 한다.
		  from employees;
		total_num number;
		total_sum number;      -- IS부터 여기까지가 선언부 같은 느낌(number형 변수 2개 만듦)
	begin
		open emp_total_sum;
		fetch emp_total_sum into total_num, total_sum;   -- 꺼내서 total_num, total_sum에 담는다
    	  DBMS_OUTPUT.PUT_LINE('총인원수: '||total_num||' , 급여합계: '||total_sum);
		close emp_total_sum;                       -- IS부터 여기까지 PL블럭 -> POROCEDURE emp_sum으로 묶음
	end emp_sum ;  -- emp_sum 프로시저 끝

패키지 몸체부 중 Emp_sum 프로시저 부분

 

	procedure emp_avg      -- 2번째 프로시저 emp_avg 정의 시작  -> emp_sum이랑 묶어서 emp_total
	is
		CURSOR emp_total_avg is
			select count(*), avg(NVL(salary,0))
			from employees;
		total_num number;
		total_avg number;
	begin
		open emp_total_avg;
		fetch emp_total_avg into total_num, total_avg;
			DBMS_OUTPUT.PUT_LINE('총인원수'||total_num||' , 급여평균: '||total_avg);
		close emp_total_avg;      -- CURSOR부터 여기까지가 PL 블럭 -> 이름은 emp_avg
	end emp_avg;            -- 2번째 프로시저 emp_avg 끝
end emp_total;              -- 패키지 끝

패키지 몸체부 중 Emp_avg 프로시저 부분

 

- 패키지 실행하기

set serveroutput on
exec emp_total.emp_sum; -- 패키지 이름.프로시저 이름으로 실행, 프로시저라서 exec 사용
                        -- 프로시저가 아니라면 select ~ from ~을 사용했을 것

exec emp_total.emp_avg;

 

2) 생성된 패키지 조회하기

그냥 이런데서 패키지, 프로시저 보면 됨

 

- 선언부 조회하기

Select text
from user_source
WHERE type = 'PACKAGE';

- BODY 부 조회하기

Select text
from user_source
WHERE type like 'PACKAGE BODY';