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

📃 커서를 이용하여 사원의 정보 출력

DECLARE
  CURSOR employee_cursors IS	
	SELECT EMPLOYEE_ID, FIRST_NAME, SALARY
	FROM EMPLOYEES;
  employee_record employee_cursors%ROWTYPE;
  v_total NUMBER :=0;

BEGIN
  OPEN employee_cursors;

  LOOP
	FETCH employee_cursors
	INTO employee_record.employee_id,
	        employee_record.first_name,
	        employee_record.salary;
	EXIT WHEN employee_cursors%NOTFOUND;
	v_total := v_total + employee_record.salary;

	dbms_output.put_line(employee_record.employee_id ||'  '||
			employee_record.first_name ||'  '||
			employee_record.salary||'  '||
			v_total);
  END LOOP;

END;

📃 부서번호를 입력 받아 사원리스트를 출력

CREATE OR REPLACE PROCEDURE listByDeptno(p_deptno 
				IN employees.department_id%TYPE)
IS 
  CURSOR employee_cursors IS
	SELECT * FROM employees
		WHERE department_id = p_deptno;
  employee_record employee_cursors%ROWTYPE;

BEGIN
  dbms_output.put_line('===================사원리스트================');

  FOR employee_record IN employee_cursors LOOP
	dbms_output.put_line(p_deptno ||'  '|| employee_record.employee_id ||'  '||
			employee_record.last_name);
  END LOOP;

END;

📃 jobs테이블 job_id 제약조건(pk) 추가
프로시저를 이용하여 동일한 job_id 체크
NO => insert
YES => update

ALTER TABLE JOB2 ADD CONSTRAINT JOB2_JOB_ID_PK PRIMARYKEY(JOB_ID);

CREATE OR REPLACE PROCEDURE my_new_job_proc2(p_job_id IN job2.job_id%TYPE,
					p_job_title IN job2.job_title%TYPE,
					p_min_salary IN job2.min_salary%TYPE,
					p_max_salary IN job2.max_salary%TYPE)

IS
  v_cnt NUMBER := 0;

BEGIN

  SELECT COUNT(*) INTO v_cnt FROM job2
	WHERE job_id = p_job_id;

  IF v_cnt = 0 THEN
	INSERT INTO job2(job_id,job_title,min_salary,max_salary)
	VALUES(p_job_id,p_job_title,p_min_salary,p_max_salary);

  ELSE
	UPDATE job2 SET job_title = p_job_title,
			min_salary = p_min_salary,
			max_salary = p_max_salary
		WHERE job_id = p_job_id;

  END IF;
  COMMIT;

END;

📃 사원번호를 입력 받아 이름을 반환하는 함수

CREATE OR REPLACE FUNCTION get_emp_name(
			p_employee_id employees.employee_id%TYPE)
	RETURN VARCHAR2
IS
	result VARCHAR2(50) := null;

BEGIN
  SELECT last_name 
	INTO result
	FROM employees
	WHERE employee_id = p_employee_id;

  RETURN result;

  EXCEPTION
	WHEN NO_DATA_FOUND THEN
		RETURN '해당 사원 없음';

END;

📃 사원이 삭제되면 그 사원의 급여정보(SAL01)테이블에서 해당 로우도 함께 삭제

CREATE OR REPLACE TRIGGER TRG_03
	AFTER DELETE
	ON EMP13
	FOR EACH ROW
	BEGIN
	DELETE FROM SAL01 WHERE EMPNO = :OLD.EMPNO;
	DBMS_OUTPUT.PUT_LINE('급여정보에서도 삭제');

END;