[Oracle] 프로시저, 함수의 이해

■ 익명 블록과 저장 프로그램 차이점

	익명 블록	저장 서브 프로그램
이름	이름 X	이름 지정
오라클 저장	저장할 수 X	저장
컴파일	실행할 때마다 컴파일	저장할 때 한 번 컴파일
공유	공유 불가	공유하여 사용 가능
다른 응용 프로그램에서의 호출 가능 여부	X	O

 

■ 저장 서브 프로그램의 대표적인 구현 방식과 용도

서브 프로그램	용도
프로시저	일반적으로 특정 처리 작업 수행을 위한 서브프로그램. SQL문에서는 사용할 수 X
함수	일반적으로 특정 연산을 거친 결과 값을 반환하는 서브프로그램. SQL문에서 사용 가능
패키지	저장 서브프로그램을 그룹화하는 데 사용
트리거	특정 상황(이벤트)이 발생했을 때 자동으로 연달아 수행할 기능을 구현하는데 사용

■ 저장 프로시저 (stored procedure)

 

프로시저는 특정 처리 작업을 수행하는 데 사용하는 저장 서브 프로그램으로 파라미터는 용도에 따라 지정하거나 지정하지 않을 수 있다.

 

1. 파라미터가 없는 프로시저

작업 수행에 입력 데이터가 필요하지 않을 경우 사용

 

CREATE [OR REPLACE] PROCEDURE [프로시저 이름]
IS|AS
    [선언부]
BEGIN
    [실행부]
EXCEPTION
    [예외 처리부]
END    [프로시저 이름];

 

OR REPLACE (선택) : 지정한 이름을 가진 프로시저가 이미 존재하면 현재 작성한 내용으로 대체(덮어 씌우기)

IS | AS : 선언부를 시작하기 위해 IS나 AS 사용. 선언부가 존재하지 않더라도 반드시 명시해야 하며 DECLARE 키워드는 사용 X

EXCEPTION (선택) : 예외를 처리하기 위한 예외처리부

END [프로시저 이름] : 프로시저 생성의 종료를 의미. 프로시저 이름 생략 가능

 

2. 파라미터를 사용하는 프로시저

입력 데이터가 필요한 경우 파라미터를 정의. 여러 개 작성 가능

 

CREATE [OR REPLACE] PROCEDURE [프로시저 이름]
 
[(파라미터 이름1 [modes] 자료형 [ := || DEFAULT 기본값],
  파라미터 이름2 [modes] 자료형 [ := || DEFAULT 기본값],
  ...
  파라미터 이름N [modes] 자료형 [ := || DEFAULT 기본값]
)]
IS|AS
    [선언부]
BEGIN
    [실행부]
EXCEPTION
    [예외 처리부]
END    [프로시저 이름];

 

[파라미터 이름 ~ ] : 실행에 필요한 파라미터 정의. 여러 개일 경우 쉼표(,)로 구분하여 지정. 기본값과 modes는 생략가능

[파라미터의 모드]

1. IN : 프로시저를 호출할 때 값을 입력 받음 (지정하지 않으면 기본 값)
2. OUT : 호출할 때 값을 반환
3. IN OUT : 호출할 때 값을 입력 받은 후 실행 결과 값을 반환

 

■ 프로시저 오류 정보 확인

프로시저를 생성할 때 발생하는 오류를 확인할 수있는 방법 2가지

1. SHOW ERRORS(ERR) : 가장 최근에 생성되거나 변경된 서브 프로그램의 오류 정보를 출력
2. USER_ERRORS : 오류 정보를 확인할 수 있는 데이터 사전

 

■ 함수 (function)

 

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

 

[파라미터 이름 ~ ] (선택) : 함수 실행에 사용할 입력 값이 필요하면 파라미터를 지정. 필요에 따라 여러 개 정의 가능. 프로시저와 달리 IN모드만 지정 가능

RETURN [자료형] : 함수의 실행 후 반환값의 자료형을 정의

RETURN (반환 값) : 함수의 반환값을 지정

 

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

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

 

■ 커서 (CURSOR)

 

커서란 특정 SQL  문장을 처리한 결과를 담고있는 메모리 영역을 가리키는 일종의 포인터이다. 커서의 종류에는 묵시적 커서와 명시적 커서가있다.  묵시적 커서는 오라클 내부에서 자동으로 생성되어 SQL문장이 실행될 때마다 자동으로 만들어져 실행되는 커서이고 명시적 커서는 사용자가 직접 정의해서 사용하는 커서이다.