Oracle PL/SQL 강좌
함수(FUNCTION) 20 34 99,999+

by 김정식 함수 FUNCTION RETURN VAR EXECUTE [2002.01.20]


함수(Function)란?

  보통 값을 계산하고 결과 값을 반환하기 위해서 함수를 많이 사용 한다.

  대부분 구성이 프로시저와 유사 하지만 IN 파라미터만 사용 할 수 있다.

  반드시 반환 될 값의 데이터 타입을 RETURN문에 선언해야 한다.

  또한 PL/SQL블록 내에서 RETURN문을 통해서 반드시 값을 반환해야 한다.

함수 문법

 
-- PL/SQL 블록에는 적어도 한 개의 RETURN 문이 있어야 한다. 
-- PL/SQL Block은 함수가 수행 할 내용을 정의한 몸체부분이다.    
CREATE OR REPLACE FUNCTION function name 
  [(argument...)] 
  
  RETURN datatype
    -- datatype은 반환되는 값의 datatype입니다. 

IS 

  [변수 선언 부분]

BEGIN

  [PL/SQL Block]
   
  -- 리턴문이 꼭 존재해야 합니다
  RETURN 변수; 
        
END;     
    

함수 작성 예제

SQL> CREATE OR REPLACE FUNCTION FC_update_sal
     (v_empno         IN    NUMBER)

      -- 리턴되는 변수의 데이터타입을 꼭 정의해야 합니다
      RETURN  NUMBER

    IS
    
    -- %type 변수가 사용(스칼라 데이터 타입 참고)
    v_sal  emp.sal%type;

    BEGIN

    UPDATE emp
    SET sal  = sal  * 1.1
    WHERE empno  = v_empno;

    COMMIT;

    SELECT sal
    INTO v_sal
    FROM emp
    WHERE empno = v_empno;

    -- 리턴문이 꼭 존재해야 합니다
    RETURN v_sal;

   END;   
   /   
    

함수 실행 예제


-- 먼저 함수의 반환값을 저장 할 변수 선언. 
SQL> VAR salary NUMBER; 

-- EXECUTE 문을 이용해 함수를 실행합니다. 
SQL>EXECUTE :salary := FC_update_sal(7900); 

-- PRINT문을 사용하여 출력 
SQL>PRINT salary;
 
    SALARY
----------
      1045
    
-- 아래와 같이 SELECT문장에서도 사용 할 수 있다. 
SQL> SELECT ename, FC_update_sal(sal) FROM emp;
    

- 강좌 URL : http://www.gurubee.net/lecture/1042

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

by 김정식 [2004.09.17 13:52:39]
답변]
SYS_REFCURSOR 를 이용한 방법이나 ref cursor 를 이용한 방법으로 구현할 수 있습니다.

다만 이렇게 작성된 펑션을 테스트하실때 oracle 8i 이하 버젼의 sqlplus 에서는
에러를 만나게되므로 oracle 9i 이상 sqlplus 에서는 결과를 확인하시기 바랍니다.


> 방법 1

CREATE OR REPLACE FUNCTION multi_func(no NUMBER)
RETURN SYS_REFCURSOR
IS
cur SYS_REFCURSOR;
BEGIN
open cur for
select empno
from emp
where deptno=no;

return cur;
null;
END;
/

select multi_func(10) empno from dual;

by 김정식 [2004.09.17 13:53:54]
오라클 사용자 팁 게시판에서
"여러개의 seletc 결과(record)를 return하는 function" 글을 참고해 보세요..

by 왕초보 [2005.05.04 13:09:46]
function에서 파라미터로 테이블명을 받아서
from절에 테이블명을 받은 파라미터값으로 쓰려고 합니다...
가능한가요? 가능하다면 방법좀 알려주세요...^^

by 도치세상 [2006.01.18 12:53:49]
프로시져와 함수로 구분하여 사용하는 가장 큰이유는 무엇인가요? 제가 완전 초보라 프로시져와 함수의 차이를 잘 모르겠습니다. 어쩔때 프로시져를 쓰고 어쩔때 함수를 쓰는 것인지....

by 까만아이 [2006.04.03 12:25:15]
시근땀님 ㅋ 완전 웃기세요~! 푸흣 ㅎㅎ
덕분에... 지루하게 공부하다가 한번웃을수 있어서 기분전환도 되고,
힘이 나네여 ㅋ
앞으로도 멋진(?) 리플 기대할게여 ^ㅠ^

by 네로 [2006.04.11 18:45:13]
강좌를 처음부터 쭈욱 보고 있는데.. 시근땀님...넘 웃겨요..ㅋㅋ
너도 스칼라땜에 이게 먼가..하고 있었는데..ㅋㅋ

by 나그네 [2006.09.15 23:22:18]
자바로 치면 펑션은 리턴형이있는 거고..
프로시즈는 없는거 같다..꼭

by 손님 [2013.02.25 13:03:50]

그게 맞는것같은데여..


by 엽 [2006.09.27 10:31:12]
9i 이구요
sql plus 에선 되느데 toad 에선 execute 문장이 에러나네요.

-sql plus-
PL/SQL 처리가 정상적으로 완료되었습니다.
-toad-
begin
:salary := FC_update_sal(7900);
end;
ORA-01008: 일부 변수가 결합되지 않았습니다

by 시근땀 [2006.09.28 15:52:56]
오랜만에 들렀습니다.
DB는 정말 해도해도 어려운 것인가 봅니다.
곧 추석이네요. 추석끝나면 새로운 플젝 본격 시작인데... ㄷㄷㄷ
아줌마들은 얼른 송편 만드시고,
아가씨들은 얼른 남편 만드세요.
애인 없는 저는 카트나 하면서 추석을 보내야할 듯 합니다.
심심하신 분들 저랑 카트나 하시게요.

by 헉;; [2007.02.22 09:22:01]
SELECT sal INTO v_sal FROM emp WHERE empno = v_empno;
여기에서
INTO v_sal 이거 하는일이 뭐... 뭐죠?

by 채용근 [2007.03.19 09:33:53]
sal에서 나오는 컬럼의 값들을 v_sal에다 넣는다. 이런 뜻입니당

by 다솜여우 [2007.11.06 00:38:35]
님들 덕분에 재미있게 공부합니다....
저도 앤이 없어서 공부만 한다눈....ㅡ.ㅡ;;

by 김윤경 [2007.11.07 19:04:53]
함수에서 이 부분이 있으니까 실행이 안되는데요...ㅠㅠ
UPDATE emp
SET sal = sal * 1.1
WHERE empno = v_empno;

COMMIT;
토드에서
ORA-14551: 질의 안에 DML 작업을 수행할 수 없습니다
ORA-06512: "SCOTT.FC_UPDATE_SAL", 줄 13에서
이렇게 나와요.

by 박세웅 [2008.04.08 11:50:25]
근데 결과 값을 컴파일 해서 바로 볼려면 그냥 맨 처음에 set serveroutput on 하면 되지 않나요?? 이거 하곤 다른건가??;;

by 박세웅 [2008.04.08 11:56:38]
질의에 대한 권한이 부족한건 아닐까요? 글쌔요,... 님이 /as sysdba로 로긴 하셨으면 모를까;;;강좌에 grant 를 참조 하시는게 좋을듯;;아닌가;; 허접한 답변입니다..

by 김광수 [2008.10.15 10:20:56]
-- Toad에서 안돌아가서 돌아가도록 고쳐 봤습니다. 참고하세요~^^
CREATE OR REPLACE FUNCTION FC_update_sal
(v_empno IN NUMBER)

RETURN NUMBER

IS

v_sal emp.sal%type;

BEGIN
UPDATE emp
SET sal = sal * 1.1
WHERE empno = v_empno;

COMMIT;

SELECT sal
INTO v_sal
FROM emp
WHERE empno = v_empno;

RETURN v_sal;

END FC_update_sal;
/

by ㅠㅠ [2009.11.16 15:53:38]
함수만들면 자꾸 에러나요ㅠㅠ

by finecomp [2009.11.18 15:37:22]
음...에러 내용은 없군요...어떻게요?

by 전병호 [2010.01.05 19:16:53]
저도 이 에러가 납니다.고수분들 도와 주세요.
ORA-14551: 질의 안에 DML 작업을 수행할 수 없습니다
ORA-06512: "SCOTT.FC_UPDATE_SAL", 줄 13에서

by 전병호 [2010.01.05 19:57:06]
select 문에서 update가 있는 function 사용시 나타나는 문제 입니다.
1시간만에 원인을 밝혔군요..ㅠ.ㅠ
고수의 길은 멀고도 험한가 봅니다.....

by 임동후 [2010.01.27 15:39:51]
오 이 런거 있는거 처음 알았어요 ;;
VAR salary NUMBER;
오라클에 이런게 있었구나 ㅋ

by 김민성 [2010.02.08 20:01:38]
Aqua Data Studio에서는 어떻게 실행 해야 하는지 고수님들 부탁드립니다.
도스에서는 실행이 되는데.....

by 김정식 [2010.04.17 22:31:12]
HTML이 깨져서 함수 문법 순서가 잘못되었네요..
정상적으로 동작하도록 수정하였습니다.

by maxmania [2011.02.15 14:13:12]
함수내에서 DML문을 사용하기 위해서는
IS아래에 PRAGMA AUTONOMOUS_TRANSACTION;
문을 통해서 별도의 트랜잭션 처리를 해줘야 하는 것으로 알고 있습니다~

by 프리어 [2011.10.16 23:41:42]
maxmania님이 설명한대로 PRAGMA AUTONOMOUS_TRANSACTION;를 추가하니까 에러가 생기지 않고 정상적으로 되네요.. 감사합니다.~^^

by 돌이맹이 [2012.02.28 15:27:26]
잘 보고 갑니다.

by 손님 [2012.03.13 15:17:30]
function 가로 안에 파라미터를 꼭 작성해야 하나요?

CREATE OR REPLACE FUNCTION FC_update_sal 
(v_empno IN NUMBER) 

여기서 
CREATE OR REPLACE FUNCTION FC_update_sal ()

이런식으로는 안되나요? 파라미터 받을 필요가 없는 function 만드려고 하는데..

by 손님 [2012.03.13 15:17:41]
function 가로 안에 파라미터를 꼭 작성해야 하나요?

CREATE OR REPLACE FUNCTION FC_update_sal 
(v_empno IN NUMBER) 

여기서 
CREATE OR REPLACE FUNCTION FC_update_sal ()

이런식으로는 안되나요? 파라미터 받을 필요가 없는 function 만드려고 하는데..

by 손님 [2012.10.31 09:54:57]

댓글을 보고 분석해보니 요점은 마지막에  END FC_update_sal; 로 마무리를 하면 함수생성이되고

select문장에서 DML어쩌고 오류가나면 IS밑에 PRAGMA AUTONOMOUS_TRANSACTION; 넣어주면 되는군요 !! ㅎ


by 손님 [2013.05.28 14:33:05]

FUNCTION 을 실행시켜서 얻어진 값 

EXECUTE : salary := FC_update_sal ()

을 실행 시켜서 반환된 값을 프로시져에 다시 파라미터 값으로 넣을수 있나요??

execute procedure_name (salary) ; 이런식으로요.....

집에가서 확인 해봐야겠네요...


by 신화씨앤씨 [2013.10.22 21:33:01]

잘 보고 갑니다~^^


by 준티 [2015.12.21 13:38:59]

감사합니다


by 크레이지황 [2016.04.03 20:55:03]

function보다 procedure가 더 쉽게 와닿네요

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입