[KOSTA] Spring 기반 Cloud 서비스 개발자 양성 과정 28일차 - PL/SQL 실습

📃 사원번호 100번에 해당하는 사원의 이름과 부서명을 출력

DECLARE
    V_NAME VARCHAR2(50);
    V_DEPTNAME VARCHAR2(50);
    
BEGIN
    SELECT LAST_NAME, DEPARTMENT_NAME
    INTO V_NAME, V_DEPTNAME
    FROM EMPLOYEES E, DEPARTMENTS D
    WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
    AND DEPARTMENT_ID = 100;
    DBMS_OUTPUT.PUT_LINE('검색된 사원 정보');
    DBMS_OUTPUT.PUT_LINE(V_NAME || '  ' || V_DEPTNAME);
    
END;

📃 40번 부서의 정보를 ROWTYPE을 이용해서 출력

DECLARE
    DEPT_RECORD DEPT%ROWTYPE;
    
BEGIN
    SELECT * INTO DEPT_RECORD
    FROM DEPT
    WHERE DEPTNO = '40';
    
DBMS_OUTPUT.PUT_LINE(DEPT_RECORD.DEPTNO || '  '
		|| DEPT_RECORD.DNAME || '  ' ||DEPT_RECORD.LOC);
        
END;

📃 10~120 중 임의이 부서 버호를 받아서 해당부서의 평균 급여에 따라서 등급이 출력되도록 하자.

IF

DECLARE
 V_DEPT NUMBER := ROUND(DBMS_RANDOM.VALUE(10, 120) -1);
 V_AVG NUMBER(20);
 
BEGIN
  SELECT AVG(SALARY)
  INTO V_AVG
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = V_DEPT
  GROUP BY DEPARTMENT_ID;
  
  IF V_AVG <= 3000 THEN
    DBMS_OUTPUT.PUT_LINE('낮음');
  ELSIF V_AVG <= 6000 THEN
    DBMS_OUTPUT.PUT_LINE('보통');
  ELSE
    DBMS_OUTPUT.PUT_LINE('높음');
  END IF;
  
END;

CASE WHEN

DECLARE
 V_DEPT NUMBER := ROUND(DBMS_RANDOM.VALUE(10, 120) -1);
 V_AVG NUMBER(20);
 
BEGIN
  SELECT AVG(SALARY)
  INTO V_AVG
  FROM EMPLOYEES
  WHERE DEPARTMENT_ID = V_DEPT
  GROUP BY DEPARTMENT_ID;
  
  CASE WHEN V_AVG BETWEEN 1 AND 300 THEN
	DBMS_OUTPUT.PUT_LINE('낮음');
  WHEN V_AVG BETWEEN 3000 AND 6000 THEN
	DBMS_OUTPUT.PUT_LINE('보통');
  ELSE
	DBMS_OUTPUT.PUT_LINE('높음');
  END CASE;
  
END;

🎲 구구단 출력

DECLARE 
 I NUMBER := 0;
 DAN NUMBER := 0;
 R NUMBER := 0;
 
 BEGIN
 FOR I IN 2..9 LOOP

 FOR DAN IN 1..9 LOOP

 DBMS_OUTPUT.PUT_LINE(I || ' * ' || DAN || ' = ' || I*DAN);
 
 END LOOP;
 DBMS_OUTPUT.PUT_LINE('====');
 END LOOP;
END;

🎲 신입사원 등록시 잘못된 부서번호에 대해서 예외처리

DECLARE
  P_DEPARTMENT_ID NUMBER := 50000;
  P_CNT NUMBER := 0;
  EX_INVALID_DEPTID EXCEPTION;
  V_EMPLOYEE_ID EMPLOYEES2.EMPLOYEE_ID%TYPE;

BEGIN
  SELECT COUNT(*) INTO P_CNT
	FROM EMPLOYEES2
	WHERE DEPARTMENT_ID = P_DEPARTMENT_ID;
  IF P_CNT = 0 THEN
	RAISE EX_INVALID_DEPTID;
  END IF;

  SELECT MAX(EMPLOYEE_ID) +1
	INTO V_EMPLOYEE_ID
	FROM EMPLOYEES;

  INSERT INTO EMPLOYEES2(EMPLOYEE_ID, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID, DEPARTMENT_ID)
              VALUES(V_EMPLOYEE_ID, 'AA', 'AA@AA.COM', SYSDATE, 'AD_VP', P_DEPARTMENT_ID);


EXCEPTION
  WHENEX_INVALID_DEPTID THEN
	DBMS_OUTPUT.PUT_LINE('해당 부서가 존재하지 않습니다.');
  WHEN OTHERS THEN
	DBMS_OUTPUT.PUT_LINE('기타 예외');

END;