10/20 25-7 [SUBPROGRAM] FUNCTION

함수 들어가기 전

PL/SQL에는 (스토어드)프로시저 와 함수라는 두가지 유형의 SUBPROGRAM이 있다.

SELECT구문 뿐만 아니라 기타 DML문등을 이용하거나 혹은 프로그래밍적인 요소등을 사용하여 처리하기 복잡한 여러가지 데이터베이스 작업등을 처리 할 수 있도록 만들어진 데이터베이스 객체이다. 말은 너무 어려워서 내가 처리될 것 같음... 걍 자바에서 메소드 같은 개념이라고 생각하자!!

FUNCTION(함수)

사용자가 PL/SQL구문을 사용하여 직접 오라클 에서 제공하는 내장함수와 같은 기능을 정의 한 것 ! 내가 만드는 메소드 느낌? 함수는 IN 파라미터만 사용할 수 있으며, "반드시 반환" 될 값의 데이터 타입을 RETURN문에 선언해야 하며 "단일값만 반환" 된다. 자바에서 리턴값이 있는 메소드와 같다고 생각하자!

 

구문이 좀 까다로우니까 잘 익혀두자!

[함수 구문]

 CREATE OR REPLACE FUNCTION 함수명                     =>ALTER가 없어용 ... 
  [ ( 
        매개변수1 IN 자료형,    --> IN 생략 가능  외부에서 전달받아서 자료에 넣는거...
        매개변수2 IN 자료형     --> 자료형 정의시 ** 자리수 지정 안함**
                   
  ) ]
RETURN 자료형        --> 자리수 지정 안함 ,반환타입만 지정  ; 도 넣으면 안 됨...!!!
  IS   

                        [변수 선언]   ==> 요 때는 자리수 지정 가능!!

  BEGIN

               함수 내용

               RETURN(값);   =>반환값은 무조건 있어 함

  END;
   /

 

[FUNCTION 호출]

    방법1. 바로 사용

  SELECT 함수명(매개변수1,매개변수2) FROM DUAL ;

    방법2. 바인드변수를 선언해서 사용하는 방법
  VAR  HAP 자료형;   
  EXCUTE :HAP  := 함수명(매개변수1,매개변수2) ;  =>  EXCUTE는 함수나 프로시저 실행할 때 쓰는 명령어!

                                                              =>바인드변수는 : 를 꼭 붙여준다   

  PRINT HAP; 

 

직접 해보는게 더 빠름!

---

[누적합을 구하는 함수 ]

CREATE OR REPLACE FUNCTION getTotal(fnum /*IN*/ NUMBER, snum NUMBER )   -->IN은 생략 가능
                 --반환타입 정의(자리수 랑  ; 안돼)
        RETURN NUMBER
IS
        --함수에서 사용할 변수 선언(자리수지정O , ;반드시 넣어야)
          hap NUMBER := 0 ;          
BEGIN
        FOR k IN fnum..snum LOOP
            hap := hap + k;            
        END LOOP;
         --값 반환
         RETURN hap;
END;
/
SELECT getTotal(1,100) FROM DUAL;     --바로출력하거나
VAR hap NUMBER ;              ----바인드 변수 선언해서
EXECUTE  :hap := getTotal(1,1000);
PRINT hap;                   -출력

 

---

[EMP 에서 이름을 첫글자만 나오게 나머지는 * 로 숨겨서 출력하기]

 

CREATE OR REPLACE FUNCTION initname (init VARCHAR2 )
            RETURN VARCHAR2
IS
           /* INIT VARCHAR2 := NULL;*/  --- 변수선언 안하고 바로 넣어도 됨! 아하!!
BEGIN
         RETURN  RPAD(SUBSTR(INIT,1,1),LENGTH(INIT),'*');
            
END;
/
SELECT initname('CHOI') FROM DUAL;   --잘 되는지 호출해보고
VAR init VARCHAR2(500) 
EXECUTE :init := initname('CHOI');
PRINT init ;

----- EMP 에 적용하기!
SELECT initname(ENAME) FROM EMP;

 

---

[날자타입을 문자로 반환해서 EMP 입사일에 반영하기]

 

CREATE OR REPLACE FUNCTION to_string (val DATE)
             RETURN VARCHAR2
IS          
BEGIN
             RETURN TO_CHAR(val,'yyyy-mm-dd');
END;
/
VAR val VARCHAR2(500) 
EXECUTE :val := to_string(sysdate);
PRINT val;

 

SELECT HIREDATE , to_string(HIREDATE) FROM emp;

 

++ SCOTT에서 만든 함수를 HR계정에서도 사용 할 수 있음!!

우선 함수를 사용할 수 있는 권한을 받아야 하기 때문에 SYSTEM 계정에 가서

 

GRANT execute ON SCOTT.initname TO HR; 해주고

HR에선 E-MAIL을 출력 해 보자

SELECT EMAIL,SCOTT.initname(EMAIL) FROM employees;