Stored Function(사용자 함수) - 펑션

-- Stored Function(사용자 함수)

    스토어드 함수는 시스템 함수처럼 쿼리에서 호출하거나 저장 프로시저처럼 EXECUTE 문을 통해 실행할 수 있다.

    프로시져와 비슷하지만 값을 돌려준다. 

-----------------------------------------------------

-- 형식(인수나 RETURN 에서는 자료형의 크기를 명시하지 않는다.)

CREATE [OR REPLACE] FUNCTION 함수이름

[(

   매개변수1 IN 자료형,

   매개변수2 IN 자료형;

)]

RETURN datatype;  --프로시져와 다른부분

IS 

   [변수의 선언]

BEGIN

   .............

    RETURN (값);

   [EXCEPTION]

   .............

END;

-----------------------------------------------------

-- 함수 목록 확인

SELECT object_name FROM user_procedures;

-- **펑션 만들기**

--1부터100까지 합

CREATE OR REPLACE FUNCTION fnsum

(

  n number

)

RETURN NUMBER -- 돌려줄 자료형을 적어준다.

IS

  s NUMBER := 0;

BEGIN

  FOR i IN 1..n LOOP --FOR 문안의 변수 i 는 자동 선언되므로 선언하지 않아도 된다.

    s := s+ i;

  END LOOP;

  

  RETURN s;

END;

/

--fnsum 확인

SELECT fnsum(100) FROM dual; --프로시져의 EXEC실행문과 다르게 SELECT문을 쓴다.

------------------------------------------------------

--남여 성별 구하기

CREATE OR REPLACE FUNCTION fngender

(

  ssn VARCHAR2 --프로시져나 펑션에서 인수에 컬럼폭을 지정안함

)

RETURN VARCHAR2 -- 리턴형 역시 컬럼폭 크기 설정 못함

IS

  s VARCHAR2(4) :='여자'; -- 리턴으로 되돌려줄 변수를 선언과 동시에 사용

BEGIN

  IF MOD(SUBSTR(ssn, 8,1),2) = 1 THEN

    s := '남자';

  END IF;

  

  RETURN s;

END;

/

--인사테이블의 주민번호를 이용하여 fngender 펑션 사용

SELECT name,fngender(ssn) FROM insa;

------------------------------------------------------

--남여 성별 구하기

CREATE OR REPLACE FUNCTION fnbirth

(

  ssn VARCHAR2 

)

RETURN DATE

IS

BEGIN

  RETURN TO_DATE(SUBSTR(ssn,1,6), 'RRMMDD'); --리턴문에 식을 대입하여 실행문 작성

  

END;

/

--인사테이블의 주빈번호를 이용하여 fngender 펑션 사용

SELECT name,fngender(ssn),fnbirth(ssn) FROM insa;

------------------------------------------------------

--나이구하기

CREATE OR REPLACE FUNCTION fnage

(

  ssn VARCHAR2 

)

RETURN NUMBER

IS

  n NUMBER;

BEGIN

  n:=TRUNC(MONTHS_BETWEEN(SYSDATE, TO_DATE(SUBSTR(ssn,1,6), 'RRMMDD'))/12);

  

  RETURN n;

END;

/

--인사테이블의 주빈번호를 이용하여 fngender 펑션 사용

SELECT name,fngender(ssn),fnage(ssn) FROM insa;

------------------------------------------------------

학점을 구하는 fnhak 함수 만들기

  CREATE OR REPLACE FUNCTION fnhak

  (

   s NUMBER --hak으로 인수를 받고

   )

   RETURN NUMBER

   IS

      n NUMBER(3,1);  --n으로 결과를 넘겨준다

   BEGIN

      IF s >= 95 THEN

      n:=4.5;

      ELSIF s>= 90 THEN

      n:=4.0;

      ELSIF s>= 85 THEN

      n:=3.5;

      ELSIF s>= 80 THEN

      n:=3.0;

      ELSIF s>= 75 THEN

      n:=2.5;

      ELSIF s>= 70 THEN

      n:=2.0;

      ELSE

      n:=0.0;

      END IF;

      

      RETURN n;

    END;

    /

    

    SELECT fnhak(100) FROM dual;

   ------------------------------------------------------

   

   -- 프로시져, 함수 목록 확인

SELECT * FROM user_procedures;