sql-study-day28

19 - 3 함수

• 프로시저와 함수의 차이점

특징	프로시저	함수
실행	EXECUTE 명령어 또는 다른 PL/SQL 서브프로그램 내에서 호출하여 실행	변수를 사용한 EXECUTE 명령어 또는 다른 PL/SQL 서브프로그램에서 호출하여 실행하거나 SQL문에서 직접 실행 가능
파라미터 지정	필요에 따라 지정하지 않을 수도 있고 여러 개 지정할 수도 있으며 IN, OUT, IN OUT 세 가지 모드를 사용할 수 있음	프로시저와 같게 지정하지 않을 수도 있고 여러 개 지정할 수 있지만 IN 모드(또는 생략)만 사용
값의 반환	실행 후 값의 반환이 없을 수도 있고 OUT, IN OUT 모드의 파라미터 수에 따라 여러 개 값을 반환할 수 있음	반드시 하나의 값을 반환해야 하며 값의 반환은 프로시저와 달리 OUT, IN OUT 모드의 파라미터를 사용하는 것이 아니라 RETURN절과 RETURN문을 통해 반환

함수 생성하기

• 함수는 반환 값의 자료형과 실행부에서 반환할 값을 RETURN절 및 RETURN문으로 명시해야 한다.
• 실행부의 RETURN문이 실행되면 함수 실행은 즉시 종료된다.

// 기본 형식
CREATE [OR REPLACE] FUNCTION 함수 이름
[(파라미터 이름1 [IN] 자료형1, 1.
  파라미터 이름2 [IN] 자료형2,
  ...
  파라미터 이름N [IN] 자료형N
)]
RETURN 자료형 2.
IS | AS
  선언부
BEGIN
  실행부
 RETURN (반환 값); 3.
EXCEPTION
  예외 처리부
END [함수 이름];

번호	설명
1.	함수 실행에 사용할 입력 값이 필요하면 파라미터를 지정합니다. 파라미터 지정은 생략 가능하며 필요에 따라 여러 개 정의할 수 있습니다. 프로시저와 달리 IN 모드만 지정합니다. :=, DEFAUTL 옵션으로 기본값을 지정할 수도 있습니다.
2.	함수의 실행 후 반환 값의 자료형을 정의합니다.
3.	함수의 반환 값을 지정합니다.

// 함수 생성하기
CREATE OR REPLACE FUNCTION func_aftertax(
   sal IN NUMBER
)
RETURN NUMBER
IS
   tax NUMBER := 0.05;
BEGIN
   RETURN (ROUND(sal - (sal * tax)));
END func_aftertax;
/

함수 실행하기

• PL/SQL로 실행할 때는 함수 반환 값을 대입받을 변수가 필요하다.

PL/SQL로 함수 실행하기

SQL문에서 함수 실행하기

• 함수에 정의한 파라미터와 자료형이 일치한다면 내장 함수와 마찬가지로 특정 열 또는 열 데이터 간에 연산 가공된 데이터를 입력하는 것도 가능하다.

함수 삭제하기

19 - 4 패키지

• 패키지(package는 업무나 기능 면에서 연관성이 높은 프로시저, 함수 등 여러 개의 PL/SQL 서브프로그램을 하나의 논리 그룹으로 묶어 통합∙관리하는 데 사용하는 객체를 뜻한다.

장점	설명
모듈성	서브프로그램을 포함한 여러 PL/SQL 구성 요소를 모듈화할 수 있습니다. 모듈성은 잘 묶어 둔다는 뜻으로 프로그램의 이해를 쉽게 하고 패키지 사이의 상호 작용을 더 간편하고 명료하게 해 주는 역할을 합니다. 즉 PL/SQL로 제작한 프로그램의 사용 및 관리에 큰 도움을 줍니다.
쉬운 응용 프로그램 설계	패키지에 포함할 서브프로그램은 완벽하게 완성되지 않아도 정의가 가능합니다. 이 때문에 전체 소스 코드를 다 작성하기 전에 미리 패키지에 저장할 서브프로그램을 지정할 수 있으므로 설계가 수월해집니다.
정보 은닉	제작 방식에 따라 패키지에 포함하는 서브프로그램의 외부 노출 여부 또는 접근 여부를 지정할 수 있습니다. 즉 서브프로그램을 사용할 때 보안을 강화할 수 있습니다.
기능성 향상	패키지 내부에는 서브프로그램 외에 변수∙커서∙예외 등도 각 세션이 유지되는 동안 선언해서 공용(public)으로 사용할 수 있습니다. 예를 들어 특정 커서 데이터는 세션이 종료되기 전까지 보존되므로 여러 서브프로그램에서 사용할 수 있습니다.
성능 향상	패키지를 사용할 때 패키지에 포함한 모든 서브프로그램이 메모리에 한 번에 로딩되는데 메모리에 로딩된 후의 호출은 디스크 I/O를 일으키지 않으므로 성능이 향상됩니다.

▶︎ PL/SQL 서브프로그램의 제작∙사용∙관리∙보안∙서능 등에 좋은 영향을 끼친다.

패키지 구조와 생성

• 패키지는 프로시저, 함수와 달리 보통 두 부분으로 나누어 제작한다. 하나는 명세(specification) 또 하나는 본문(body)이라고 부른다.

패키지 명세

• 패키지 명세는 패키지에 포함할 변수, 상수, 예외, 커서 그리고 PL/SQL 서브프로그램을 선언하는 용도로 작성한다.
• 패키지 명세에 선언한 여러 객체는 패키지 내부뿐만 아니라 외부에서도 참조할 수 있다.

// 기본 형식
CREATE [OR REPLACE] PACKAGE 패키지 이름
IS | AS
  서브프로그램을 포함한 다양한 객체 선언
END [패키지 이름];

패키지 생성하기

패키지 명세 확인하기(USER_SOURCE 데이터 사전으로 조회)

패키지 명세 확인하기(DESC 명령어로 조회)

패키지 본문

• 패키지 본문에는 패키지 명세에서 선언한 서브프로그램 코드를 작성한다.
• 패키지 명세에 선언하지 않은 객체나 서브프로그램을 정의하는 것도 가능하다.
• 패키지 본문에만 존재하는 프로그램은 패키지 내부에서만 사용할 수 있다.
• 패키지 본문 이름은 패키지 명세 이름과 같게 지정해야 한다.

// 기본 형식
CREATE [OR REPLACE] PACKAGE BODY 패키지 이름
IS | AS
  패키지 명세에서 선언한 서브프로그램을 포함한 여러 객체를 정의
  경우에 따라 패키지 명세에 존재하지 않는 객체 및 서브프로그램도 정의 가능
END [패키지 이름];

패키지 본문 생성하기

서브프로그램 오버로드

• 서브프로그램 이름은 중복될 수 없다. 하지만 같은 패키지에서 사용하는 파라미터의 개수, 자료형, 순서가 다를 경우에 한해서만 이름이 같은 서브프로그램을 정의할 수 있다. 이를 서브프로그램 오버로드(subprogram overload)라고 한다.
• 서브프로그램 오버로드는 보통 같은 기능을 수행하는 여러 서브프로그램이 입력 데이터를 각각 다르게 정의할 때 사용한다. 또한 서브프로그램 종류가 같아야 오버로드가 가능하다.
• 프로시저와 이름이 같은 함수를 정의할 수는 없다.

// 기본 형식
CREATE [OR REPLACE] PACKAGE 패키지 이름
IS | AS
  서브프로그램 종류 서브프로그램 이름(파라미터 정의);
  서브프로그램 종류 서브프로그램 이름(개수나 자료형, 순서가 다른 파라미터 정의);
END [패키지 이름];

프로시저 오버로드하기

패키지 본문에서 오버로드된 프로시저 작성하기

패키지 사용하기

• 패키지를 통해 그룹화된 변수, 상수, 예외, 커서 그리고 PL/SQL 서브프로그램은 패키지 이름과 마침표(.)와 사용할 객체 이름으로 사용할 수 있다.

패키지 이름.객체 이름;

패키지에 포함된 서브프로그램 실행하기

패키지 삭제하기

• 패키지 명세와 본문을 한 번에 삭제하거나 패키지 본문만 삭제할 수도 있다. 하지만 패키지에 포함된 서브프로그램을 따로 삭제하는 것은 불가능하다.

// 패키지 명세와 본문을 한 번에 삭제하기
DROP PACKAGE 패지키 이름;

// 패키지의 본문만을 삭제
DROP PACKAGE BODY 패키지 이름;

---