Function
주어진 입력값을 바탕으로 결과값을 계산하는 블록 코드
특징
- 사용자에 의해 정의될 수도 있고, 미리 정의될 수도 있다.
- 함수 이름은 수학과 관련이 있다.
- 결과 반환이 필수이다.
차이점
- 쿼리를 실행한 후에 코드나 호출한 함수에게 결과값을 반환한다.
- DML을 가지는 함수들은 SQL문으로부터 호출될 수 없다. (autonomous transaction 함수들은 호출될 수 있다.)
- 입력값에 따른 결과를 제공할 때마다 매번 컴파일된다.
- 결과 집합을 반환할 수 없다. (1개)
- Stored Procedure에서 호출될 수 있다.
- 데이터를 읽을 때만 사용된다.
- 함수의 반환문은 제어권과 결과값을 호출한 프로그램에게 반환한다.
try-catch블록을 지원하지 않는다.SELECT문 안에서 동작할 수 있다.- 트랜잭션 관리를 허용하지 않는다.
- 테이블 변수만 사용할 수 있으면, 함수 내에서 임시 테이블은 생성될 수 없다.
- input parameter만 가질 수 있다.
예시
CREATE [OR REPLACE] FUNCTION function_name(@parameter_name AS type [, ...])
RETURN return_data -- 필수
{IS | AS}
BEGIN
-- program code
[EXCEPTION
exception_section]
END [function_name];Procedure
instruction이나 command의 집합
특징
- 명령 집합을 순서대로 실행한다.
- 결과 반환이 필수가 아니다.
차이점
IN OUT이나OUT을 활용하여 결과값을 반환할 수 있다.- SQL문에서 호출될 수 없다.
- 프로시저는 한 번만 컴파일되며 필요할 때마다 호출될 수 있다.
- 여러 결과 값을 반환할 수 있다. (0~n개)
- 함수는 프로시저를 호출할 수 없다.
- 데이터를 읽고 수정하는 데 사용된다.
- 프로시저의 반환문은 호출한 프로그램에게 결과값이 아닌 제어권만 반환할 수 있다.
- 에러 핸들링을 위해
try-catch블록을 지원한다. SELECT문 안에서 작동할 수 없다.- 트랜잭션 관리를 허용한다.
- 임시 데이터 저장을 위해 임시 테이블과 테이블 변수를 사용할 수 있다.
- input과 output parameter를 가질 수 있다.
예시
CREATE OR REPLACE PROCEDURE procedure_name(param_name IN type, return_value_name OUT type)
IS
BEGIN
UPDATE .. WHERE EMPLOYEE_NO = param_name;
COMMIT;
SELECT .. INTO return_value FROM ..;
END;