쌍용교육 -PL/SQL 수업 21일차 PL/SQL 서브프로그램

 

PL/SQL 서브 프로그램

입력받은 값으로부터 10%의 세율을 얻는 함수

함수

입력받은 값으로부터 10%의 세율을 얻는 함수
--DECLARE와 같은 느낌으로 사용
CREATE OR REPLACE FUNCTION tax(p_value IN NUMBER) -- tax -> 함수명, p_value -> 파라미터, IN은 생략가능
 RETURN NUMBER -- NUMBER -> 자료형
 IS
 
 BEGIN
  RETURN p_value*0.1;
 END;

<Function TAX이(가) 컴파일되었습니다.>

함수폴더를 클릭해서 컴파일이 되었는지 TAX 파일확인

• 호출해보기

SELECT TAX(100) FROM dual;

SELECT empno,ename,sal,TAX(sal) tax FROM emp;

급여와 커미션을 합쳐서 세금 계산

CREATE OR REPLACE FUNCTION tax2(p_sal IN emp.sal%TYPE,
                                                     p_comm IN emp.comm%TYPE)
 RETURN NUMBER
 
IS

BEGIN
    RETURN (p_sal + NVL(p_comm,0))*0.1;
END;

<Function TAX이(가) 컴파일되었습니다.>

함수폴더를 클릭해서 컴파일이 되었는지 TAX2 파일확인

• 호출해보기

SELECT empno,ename,sal+NVL(comm,0) 실급여 ,comm,TAX2(sal,comm) AS tax
FROM emp;

• 급여(커미션 포함)에 대한 세율을 다음과 같이 정의함. 급여가 월 $1,000보다 적으면 세율을 5% 적용하며, $1,000이상 $2000이하면 10%, 2,000%을 초과하면 20%를 적용함.

CREATE OR REPLACE FUNCTION tax3(p_sal IN emp.sal%TYPE,
                                                    p_comm emp.comm%TYPE)
                                                    
 RETURN NUMBER
 
 IS
 --변수 선언
 l_sum NUMBER;
 l_tax NUMBER;
 
 BEGIN
 l_sum := p_sal + NVL(p_comm,0);
 
 IF l_sum <1000   THEN
 l_tax := l_sum*0.05;
 ELSIF l_sum <=2000 THEN
 l_tax := l_sum*0.1;
 
 ELSE l_tax := l_sum*0.2;
 END IF;
    RETURN l_tax;
    
 END;

<Function TAX이(가) 컴파일되었습니다.>

함수폴더를 클릭해서 컴파일이 되었는지 TAX3 파일확인

SELECT empno,ename,sal+NVL(comm,0) 실급여 ,
TAX3(sal,comm) AS tax
FROM emp;

• 사원번호를 통해서 급여를 알려주는 함수

CREATE OR REPLACE FUNCTION emp_salaries(emp_no NUMBER)
 RETURN NUMBER
 
 IS
 --변수 선언
 nSalaries NUMBER(9);
 BEGIN
 SELECT sal --커서를 명시하지않은 이유 : 커서는 여러행이 만드는 것 그치만 이건 단일 행을 만드는 것이기 때문에 커서를 사용하지 않아도됨.
 --결과행이 단일 행일경우 INTO를 이용해서 읽어온 값을 변수에 담을 수 있음.
 INTO nSalaries
 FROM emp
 WHERE empno = emp_no;
 
 RETURN nSalaries;
 END;

Function EMP_SALARIES이(가) 컴파일되었습니다.

SELECT EMP_SALARIES(7839) FROM dual; --7839가 함수 안에 들어갈 때 테이블에 의존하는 것이지 테이블 밖에 있을 땐 의존하지 않으므로 dual을 써줘야됨.

 SELECT empno,ename,EMP_SALARIES(empno) FROM emp;

• 부서번호를 전달하면 부서명을 구할 수 있는 함수

CREATE OR REPLACE FUNCTION get_dept_name(dept_no NUMBER)
 
 RETURN VARCHAR2
 IS
 sDeptName VARCHAR2 (30);
 BEGIN
 SELECT dname
 INTO sDeptName
 FROM dept
 WHERE deptno = dept_no;
 RETURN sDeptName;
 END;

SELECT GET_DEPT_NAME(10) FROM dual;

 SELECT deptno,GET_DEPT_NAME(deptno) "Department Name " FROM dept;

JOIN을 했던것을 함수로 만든 코드

SELECT empno,ename,sal,GET_DEPT_NAME(deptno) "Department Name "FROM emp;