[Oracle] PL/SQL 기초5 - 함수(Function) 첫번째 : 사용자 정의 함수(User Defined Function)
안녕하세요. 갓대희 입니다. 이번 포스팅은 [ [Oracle] PL/SQL 기초 - 함수(1) - 사용자 정의 함수 ] 입니다. : )
0.Oracle Function
▶ 1. 함수(Function)란?
- 이번에 설명하는 함수는 오라클에서 제공하는 내장함수(TO_CHAR, TO_DATE 등)가 아닌 사용자 정의 함수((User Defined Function)이다.
- 특정 기능들을 모듈화, 재사용 할 수 있어서 복잡한 쿼리문을 간결하게 만들수 있다.
하지만, 함수 사용이 쿼리 속도를 느리게 만드는 원인(인덱스를 타지 않는 등)이 될 수 있으니 꼭 필요한 경우가 아니라면 권장하진 않고 싶다.
차선으로 함수 캐싱, 함수 기반 인덱싱 하는 방법들이 있지만, 이부분은 이번엔 논하지 않을 예정이다.
- 호출한 곳으로 반드시 하나의 값을 리턴해 줘야되는 PL/SQL Stored Program이다.
즉, PL/SQL 블록 내에서 RETURN문을 통해 반드시 값을 리턴해야 한다.
- 프로시저는 PL/SQL 문으로 실행하지만, 함수는 식의 일부로서 사용한다.
- RETURN 절에 지정된 것과 동일한 datatype으로 RETURN 값을 설정해야 한다.
- 함수 작성시 권한이 필요하다.
자신의 스키마 : CREATE PROCEDURE 권한
다른 사용자의 스키마 : CREATE ANY PROCEDURE 권한
- 기본적으로 DML(Insert/update/delete)문을 사용할 수 없다.
물론, 굳이 사용할 필요도 없으나 BEGIN 전에 'pragma autonomous_transaction;'을 선언하여 사용하게 할 수는 있다.
▶ 2. 함수(Function) 기본 문법
CREATE [OR REPLACE] FUNCTION function_name [(
argument...
)]
RETURN datatype -- 반환되는 값의 datatype
IS [AS]
[선언부]
BEGIN
[실행부 - PL/SQL Block]
[EXCEPTION]
[EXCEPTION 처리]
RETURN 변수; -- 리턴문 필수
END;
- [ ] : 대괄호 안의 구문은 생략 가능함을 의미 한다.
- OR_REPLACE : 함수생성 DDL 명령어 / 이미 존재하는 펑션이라면 기존 내용을 지우고 재생성 한다. 생략 가능하다.
- function_name : 사용자 지정 함수명.
- argument : 매개변수1, 매개변수2, 매개변수3... 매개변수 선언.
- datatype : VARCHAR2, NUMBER, DATE 등 반환할 데이터 타입을 지정한다.
※ 이해를 위해 한글 표현시
CREATE OR REPLACE FUNCTION 함수명 (
변수명 IN VARCHAR2
)
RETURN VARCHAR2 -- 반환되는 값의 datatype
IS
변수 VARCHAR2(10);
BEGIN
SELECT 리턴할값
INTO 변수
FROM DUAL
WHERE 1 = 변수;
RETURN 변수; -- 리턴문 필수
END;
▶ 3. 함수(Function) 작성 및 사용 예시
EX) 함수 작성 예시
CREATE OR REPLACE FUNCTION FN_GET_DEPT_NAME(
P_DEPT_NO IN VALCHAR2
) RETURN VARCHAR2
IS
V_TEST_NAME VARCHAR2(10);
BEGIN
SELECT DELP_NAME
INTO V_TEST_NAME
FROM DEPT
WHERE DEPT_NO = P_DEPT_NO;
RETURN V_TEST_NAME;
END;
EX) 함수 사용 예시
- SELECT문장에서 편하게 사용 할 수 있다.
SELECT FN_GET_DEPT_NAME('01') FROM DUAL;
EX) 예전에 만들어 보았던 사업자번호 유효성 체크 함수도 참고 하자.
- 사업자번호 유효성 체크 Function
2017/11/29 - [4. Database/4_1 쿼리_Func_Proc] - [Oracle] 사업자번호 유효성 체크 Function (오라클)
이번 포스팅에선 기본적인 사용자 정의 함수를 사용하는 방법을 알아 보았고, 다음 포스팅에선 하나의 값을 리턴하는 것이 아닌, Data Set, 즉 마치 테이블과 같은 결과를 리턴할 수 있는 Table Function에 대해 알아 볼 예정이다.
'4. Database > 4_3 Oracle 기본' 카테고리의 다른 글
[Oracle] PL/SQL 기초7 - 컬렉션(Collection) : VARRAY, Nested Table, 연관 배열, 메소드 등 (0) | 2020.07.25 |
---|---|
[Oracle] PL/SQL 기초6 - 함수(Function) 두번째 : 테이블 함수(Table Function, Pipeline Table Function) (0) | 2020.07.24 |
[Oracle] PL/SQL 기초5 - 함수(Function) 첫번째 : 사용자 정의 함수(User Defined Function) (3) | 2020.07.12 |
[Oracle] 전역 임시 테이블 (Global Temporary Table) (0) | 2019.12.17 |
[Oracle] PL/SQL 기초4 - 프로시저(Procedure) (6) | 2018.12.23 |
[Oracle] Transaction이란? (0) | 2018.11.26 |
갓대형님 형님 게시글을 잘 보는 중입니다.
근데 질문이 있어요 사용자 함수 첫번째 예시 bigin에서 dept테이블을 참조하고 있는데 그냥 저렇게 밖에있는 테이블을 참조해도 되나요? 아니면 저 테이블이 dual함수처럼 오라클에서 막 참조할수 있는 함수라서 가능한건가요?
다른 테이블의 변수들을 참조하려면 TYPE is record를 이용한 복합변수를 활용해야 가능한거 아닌가요?
답글
잘 이해를 한건지 모르겠지만,
제가 사용한 dept는
Oralce을 공부할 때 사용하는 기초적인 테이블들 중 하나입니다.
지금 제 pc에 있는게 다 있는 건 아닌것 같지만 dept, emp, sal 등 기초 데이터 넣는 쿼리를 첨부드릴테니 참고하세요~
비밀댓글입니다
답글