- 오라클에서 제공하고 있는 내장함수(=빌트인함수)처럼 매개변수를 받아 뭔가를 처리해 그 결과를 반환하는 DB객체이며, 사용자 정의 함수 ***RETURN문과 RETURN값 존재해야만 한다.
SYNTAX CREATE OR REPLACE FUNCION 함수이름 (매개변수1,매개변수2, ...) --OR REPLACE : 있을 경우 새로운 함수로. RETURN 데이터타입 IS / AS --IS 또는 AS로 표현 가능하며 둘 다 동일한 의미임. 변수, 상수 등 선언 BEGIN 실행부
RETURN 반환값; [EXCEPTION] END [함수이름] ;
--호출문 형식 <매개변수x가 없는 함수 호출> 함수명 혹은 함수명 ()
<매개변수o가 있는 함수 호출> 함수명 (매개변수1, 매개변수2,...)
SELECT 컬럼명 INTO : 한 줄만 가져옴 데이터가 없을 시 오류남
--함수제거 DROP FUNCTION함수명 ;
FUNCTION ex) 1) 매개변수o가 있는 함수사용을 통한 호출
--함수생성
--예제 (매개변수가 있는 함수)
CREATE OR REPLACE FUNCTION TEST_FUNC(p_n1 EMP.ENAME%TYPE) -->'JONES'
RETURN DEPT.DNAME%TYPE --레퍼런스타입 중 컬럼타입 자료형
IS
V_DNAME DEPT.DNAME%TYPE; --null ->'RESEARCH'
V_DEPTNO EMP.DEPTNO%TYPE; --null ->20
BEGIN
SELECT DEPTNO INTO V_DEPTNO FROM EMP
WHERE ENAME = p_n1; -->'JONES' /20
SELECT DNAME INTO V_DNAME FROM DEPT
WHERE DEPTNO = V_DEPTNO; -->20 /'RESEARCH'
RETURN V_DNAME;
END;
--함수 호출
SELECT TEST_FUNC('JONES') FROM DUAL;
2) 익명블록을 통한 호출
--익명블록안에서 호출
SET SERVEROUT ON
DECLARE
V_DNAME DEPT.DNAME%TYPE;
BEGIN
SELECT TEST_FUNC('JONES') INTO V_DNAME FROM DUAL;
DBMS_OUTPUT.PUT_LINE(V_DNAME);
END;
매개변수가 X
--함수생성
--예제 (매개변수x가 없는 함수)
CREATE OR REPLACE FUNCTION topname_fnc
RETURN EMP.ENAME%TYPE
IS
V_ENAME EMP.ENAME%TYPE;
BEGIN
SELECT ENAME INTO V_ENAME FROM EMP
WHERE SAL = (SELECT MAX(SAL) FROM EMP);
RETURN V_ENAME;
END;
--SELECT절에서 호출 : SELECT TOPNAME_FNC FROM DUAL;
--익명블록 안에서 호출
DECLARE
V_DNAME DEPT.DNAME%TYPE;
BEGIN
SELECT topname_fnc() INTO V_DNAME FROM DUAL;
-- SELECT topname_fnc INTO V_DNAME FROM DUAL;
DBMS_OUTPUT.PUT_LINE(V_DNAME);
END;
대소문자 가릴 시 더블쿼테이션 사용
CREATE TABLE "Test1"(
"No" NUMBER(2),
"Nm" VARCHAR2(10)
); --대소문자 가릴시 더블쿼테이션 사용
INSERT INTO "Test1" VALUES (1,'TEST');
SELECT * FROM "Test1" WHERE "No" =1 ;
CREATE OR REPLACE FUNCTION my_add(n1 NUMBER, n2 NUMBER)
RETURN NUMBER
IS
V_RETURN NUMBER; --null
BEGIN
V_RETURN := N1+N2;
RETURN V_RETURN ;
END;
SELECT my_add(5, 2) FROM DUAL;
CALCULATOR
--RETURN + CASE~WHEN
CREATE OR REPLACE FUNCTION my_cal(n1 NUMBER, c VARCHAR2, n2 NUMBER) --함수에서 자료형사이즈는 지정불가
RETURN NUMBER
IS
V_RETURN NUMBER; --null
BEGIN
CASE c WHEN '+' THEN
V_RETURN := N1+N2;
WHEN '-' THEN
V_RETURN := N1-N2;
WHEN '*' THEN
V_RETURN := N1*N2;
WHEN '/' THEN
V_RETURN := N1/N2;
WHEN '%' THEN
V_RETURN := N1%N2;
ELSE
DBMS_OUTPUT.PUT_LINE('연산자가 잘못되었습니다.')
V_RETURN := NULL;
END CASE;
RETURN V_RETURN;
END;
SELECT my_cal(5,'+', 2) FROM DUAL;
2)
--RESULT + IF문
CREATE OR REPLACE FUNCTION cal2(n1 NUMBER, c VARCHAR2, n2 NUMBER) --함수에서 자료형사이즈는 지정불가
RETURN NUMBER
IS
V_RESULT NUMBER; --null
BEGIN
IF c = '+' THEN
V_RESULT := N1+N2;
ELSIF c = '-' THEN
V_RESULT := N1-N2;
ELSIF c = '*' THEN
V_RESULT := N1*N2;
ELSIF c = '/' THEN
V_RESULT := N1/N2;
ELSIF c = '%' THEN
V_RESULT := MOD(N1,N2);
ELSE
DBMS_OUTPUT.PUT_LINE('연산자가 잘못되었습니다.')
V_RESULT := NULL;
END IF;
RETURN V_RESULT;
END;
SELECT my_cal2(5,'+', 2) FROM DUAL;
--[FUNCTION] 국가코드를 이용한 국가명 출력
--[FUNCTION] 국가코드를 이용한 국가명 출력
CREATE OR REPLACE FUNCTION FN_GET_COUNTRY_NM(P_COUNTRY_ID NUMBER)
RETURN VARCHAR2
IS
VS_COUNTRY_NM COUNTRIES.COUNTRY_NAME%TYPE;
VN_CNT NUMBER := 0;
BEGIN
SELECT COUNT(*) INTO VN_CNT FROM COUNTRIES
WHERE COUNTRY_ID = P_COUNTRY_ID;
IF VN_CNT <= 0 THEN
VS_COUNTRY_NM := '해당국가 없음';
ELSE
SELECT COUNTRY_NAME INTO VS_COUNTRY_NM
FROM COUNTRIES
WHERE COUNTRY_ID = P_COUNTRY_ID;
END IF;
RETURN VS_COUNTRY_NM;
END;
SELECT FN_GET_COUNTRY_NM(52777) CN1, FN_GET_COUNTRY_NM(10000) CN2 FROM DUAL;
CREATE OR REPLACE FUNCTION topname_fnc
RETURN EMP.ENAME%TYPE
IS
V_ENAME EMP.ENAME%TYPE;
BEGIN
SELECT ENAME INTO V_ENAME FROM EMP
WHERE SAL = (SELECT MAX(SAL) FROM EMP);
RETURN V_ENAME;
END;
DECLARE
VR_ENAME EMP.ENAME%TYPE;
BEGIN
VR_ENAME := topname_fnc;
DBMS_OUTPUT.PUT_LINE(VR_ENAME);
--DBMS_OUTPUT.PUT_LINE(topname_fnc); --매개변수 명에 바로 입력
END;
CREATE OR REPLACE FUNCTION topname_fnc
RETURN DEPT%ROWTYPE
IS
V_DEPT DEPT%ROWTYPE;
BEGIN
SELECT * INTO V_DEPT FROM DEPT
WHERE DEPTNO = 10;
RETURN V_DEPT;
END;
DECLARE
D_DEPT DEPT%ROWTYPE;
BEGIN
--ROWTYPE은 SELECT문으로 출력 불가능하므로, 해당 타입의 변수로 직접 값을 대입받아야 한다.
D_DEPT := topname_fnc;
DBMS_OUTPUT.PUT_LINE(D_DEPT.DEPTNO||', '||D_DEPT.DNAME||', '||D_DEPT.LOC);
END;
