[SQL] SQL 문법 실습_#4 (function)

SOSO·2022년 5월 19일
0

실습 

목록 보기
6/7
post-thumbnail

📌 SQL 문법 실습_#4

1)두 숫자를 제공하면 덧셈을 해서 결과값을 반환하는 함수를 정의하시오.
함수명 : add_num

create or replace function add_num(num1 integer,
                                   num2 integer)
  return integer
is
begin
  return num1 + num2;
end;
SELECT ADD_NUM(2,5) FROM dual;
SELECT ename, ADD_NUM(sal,NVL(comm,0)) 실급여 FROM emp;

2)부서번호를 입력하면 해당 부서에서 근무하는 사원수를 반환하는 함수를
정의하시오.
함수명 : get_emp_count

create or replace function get_emp_count(
                                 dept_no emp.deptno%type)
  return integer
is
  emp_count integer;
begin
  SELECT COUNT(empno)
  INTO emp_count
  FROM emp WHERE deptno=dept_no;
  
  return emp_count;
end;
SELECT deptno, dname, GET_EMP_COUNT(deptno) 사원수 FROM dept;

3)emp테이블의 입사입을 입력하면 근무연차를 구하는 함수를 정의하시오.
(소수점 자리 절삭)
함수명 : get_info_hiredate

create or replace function get_info_hiredate(
                                  hire_date emp.hiredate%type)
  return number
is
begin
  return TRUNC(MONTHS_BETWEEN(SYSDATE,hire_date)/12);
end;
SELECT ename, GET_INFO_HIREDATE(hiredate) 근무연차 FROM emp;

4)emp테이블을 이용해서 사원번호를 입력하면 해당 사원의 관리자 이름을
구하는 함수를 정의하시오.

함수명 : get_mgr_name

create or replace function get_mgr_name(emp_no emp.empno%type)
  return varchar2
is
  m_name varchar2(10);
begin
  SELECT ename
  INTO m_name
  FROM emp WHERE empno = (SELECT mgr FROM emp 
                          WHERE empno=emp_no);
  return m_name;                        
end;

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

create or replace function get_mgr_name(emp_no emp.empno%type)
  return varchar2
is
  m_name varchar2(10);
begin
  SELECT m.ename
  INTO m_name
  FROM emp e, emp m
  WHERE e.mgr = m.empno
  AND e.empno = emp_no;
  
  return m_name;
end;
SELECT GET_MGR_NAME(7369) FROM dual;
SELECT empno,ename,GET_MGR_NAME(empno) FROM emp;

5)emp테이블을 이용해서 사원번호를 입력하면 급여등급을 구하는 함수를
정의하시오.(case문 이용, 4000이상(A),3000이상4000미만(B),
2000이상3000미만(C),1000이상2000미만(D) 나머지 F)
함수명 : get_sal_grade

create or replace function get_sal_grade(emp_no emp.empno%type)
  return char
is
  sgrade char(1);
begin
  SELECT CASE WHEN sal>=4000 THEN 'A'
              WHEN sal>=3000 AND sal<4000 THEN 'B'
              WHEN sal>=2000 AND sal<3000 THEN 'C'
              WHEN sal>=1000 AND sal<2000 THEN 'D'
              ELSE 'F'
        END grade -- SQL문장 안에서는 END CASE X, END O
  INTO sgrade
  FROM emp
  WHERE empno = emp_no;
  return sgrade;
end;

SELECT ename, sal, GET_SAL_GRADE(empno) 급여등급 FROM emp;

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

create or replace function get_sal_grade2(emp_no emp.empno%type)
  return number
is
  sgrade number;
begin
  SELECT s.grade
  INTO sgrade
  FROM emp e, salgrade s
  WHERE e.sal BETWEEN s.losal AND S.hisal
  AND e.empno = emp_no;
  
  return sgrade; 
end;
SELECT ename, sal, GET_SAL_GRADE2(empno) 급여등급 FROM emp;

6)사원번호를 입력하면 근무지를 구하는 함수
함수명 : find_loc

create or replace function find_loc(emp_no emp.empno%type)
  return varchar2
is
  dept_loc varchar2(14);
begin
  SELECT d.loc
  INTO dept_loc
  FROM emp e INNER JOIN dept d
  ON e.deptno = d.deptno
  WHERE e.empno = emp_no;
  
  return dept_loc;
end;
SELECT FIND_LOC(7698) FROM dual;
SELECT empno, ename, FIND_LOC(empno) FROM emp;
profile
한다 열심히

0개의 댓글