[Oracle] SQL문 정리 5

Cursor

 - 질의 결과가 Multi Row일 경우 커서를 사용

DECLARE
   --커서선언
   CURSOR department_cursors IS
	SELECT department_id, department_name, location_id
	FROM departments;
   department_record department_cursors%ROWTYPE;

 BEGIN
  --커서 열기
  OPEN department_cursors;

  LOOP
     FETCH department_cursors
       INTO department_record.department_id,
	 department_record.department_name,
	 department_record.location_id;
      EXIT WHEN department_cursors%NOTFOUND;

      dbms_output.put_line(department_record.department_id ||'   '||
	department_record.department_name ||'    '||
	department_record.location_id);

  END LOOP;
 
  CLOSE department_cursors;

END;

 

 

Q. 커서를 이용하여 사원의 정보를 출력하라. 

    ( 사원번호, 사원이름(first_name), 급여, 급여누계 ) 

DECLARE
  CURSOR employee_cursors IS
	SELECT employee_id, first_name, salary
		FROM employees;
  
  employee_record employee_cursors%ROWTYPE;
  v_totalsalary NUMBER := 0;

BEGIN
  OPEN employee_cursors;

  dbms_output.put_line('======================');
  dbms_output.put_line('사원번호     사원이름      급여     급여누계');
  dbms_output.put_line('======================');

  LOOP
    FETCH employee_cursors INTO employee_record;
    EXIT WHEN employee_cursors %NOTFOUND;

    v_totalsalary := v_totalsalary + employee_record.salary;
     dbms_output.put_line(employee_record.employee_id ||'       '||
	employee_record.first_name ||'      '|| employee_record.salary ||'      '||
	v_totalsalary);
   END LOOP;
   CLOSE employee_cursors;

END;

 

 -- FOR..IN

 
DECLARE
  CURSOR employee_cursors IS
	SELECT employee_id, first_name, salary
		FROM employees;
  
  employee_record employee_cursors%ROWTYPE;
  v_totalsalary NUMBER := 0;

BEGIN
 
  dbms_output.put_line('======================');
  dbms_output.put_line('사원번호     사원이름      급여     급여누계');
  dbms_output.put_line('======================');

  FOR employee_record IN employee_cursors LOOP
    v_totalsalary := v_totalsalary + employee_record.salary;
    dbms_output.put_line(employee_record.employee_id ||'       '||
	employee_record.first_name ||'      '|| employee_record.salary ||'      '||
	v_totalsalary);
  END LOOP;

END;

 

 

Proceduer

 - 자주 사용하는 PL/SQL 블록을 재사용하기 위해  모듈화한 것

 

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

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;
/
--프로시저 실행
EXECUTE listByDeptno(50);

 

Q. 기존 jobs를 jobs2로 복사

    프로시저를 이용해서 job_id, job_title, min_salary, max_salary를 입력받아 jobs2테이블에 새로운 row를 추가

  CREATE TABLE jobs2 AS SELECT * FROM jobs;

  CREATE OR REPLACE PROCEDURE my_new_jobs_proc(
	p_job_id IN jobs2.job_id%TYPE,
	p_job_title IN jobs2.job_title%TYPE,
	p_min_salary IN jobs2.min_salary%TYPE,
	p_max_salary IN jobs2.max_salary%TYPE)
  IS

  BEGIN
	INSERT INTO jobs2(job_id, job_title, min_salary, max_salary)
	VALUES(p_job_id, p_job_title, p_min_salary, p_max_salary);

	COMMIT;
 END;
/
EXECUTE my_new_jobs_proc('a', 'a1', 1000, 5000);

 

Q. jobs2테이블의 job_id Column에 Primary Key 제약조건 추가

   프로시저를 이용하여 동일한 job_id를 체크

   (no : insert 실행, yes : 업데이트 실행)

  ALTER TABLE jobs2
	ADD CONSTRAINT job2_job_id_pk PRIMARY KEY(job_id)

 CREATE OR REPLACE PROCEDURE my_new_jobs_proc2(
	p_job_id IN jobs2.job_id%TYPE,
	p_job_title IN jobs2.job_title%TYPE,
	p_min_salary IN jobs2.min_salary%TYPE,
	p_max_salary IN jobs2.max_salary%TYPE)
 IS
    v_cnt NUMBER :=0;

 BEGIN
    --동일한 job_id 체크
    SELECT COUNT(*) INTO v_cnt
	FROM jobs2
	WHERE job_id = p_job_id;

    IF v_cnt = 0 THEN
        INSERT INTO jobs2(job_id, job_title, min_salary, max_salary)
	VALUES(p_job_id, p_job_title, p_min_salary, p_max_salary);
    ELSE
        UPDATE jobs2
	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;
/
EXECUTE my_new_jobs_proc2('a', 'a100', 1300, 15000);
EXECUTE my_new_jobs_proc2('a1', 'a1', 100, 1000);

 

* 매개변수 DEFAULT 값 설정

CREATE OR REPLACE PROCEDURE my_new_jobs_proc3(
	p_job_id IN jobs2.job_id%TYPE,
	p_job_title IN jobs2.job_title%TYPE,
	p_min_salary IN jobs2.min_salary%TYPE := 100,
	p_max_salary IN jobs2.max_salary%TYPE := 10000)
 IS
    v_cnt NUMBER :=0;

 BEGIN
    --동일한 job_id 체크
    SELECT COUNT(*) INTO v_cnt
	FROM jobs2
	WHERE job_id = p_job_id;

    IF v_cnt = 0 THEN
        INSERT INTO jobs2(job_id, job_title, min_salary, max_salary)
	VALUES(p_job_id, p_job_title, p_min_salary, p_max_salary);
    ELSE
        UPDATE jobs2
	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;
/
EXECUTE my_new_jobs_proc3('b2', 'b222');

 

* 매개변수 OUT, IN

   IN    : 외부에서 프로시저를 사용하기 위해 인자값을 입력할 때, 인자를 내부 변수에 할당

   OUT : 프로시저가 호출 및 종료 시 OUT이 명시된 변수를 외부로 반환

   INOUT : 위의 두가지 기능 모두 수행

CREATE OR REPLACE PROCEDURE my_new_jobs_proc4(
	p_job_id IN jobs2.job_id%TYPE,
	p_job_title IN jobs2.job_title%TYPE,
	p_min_salary IN jobs2.min_salary%TYPE := 100,
	p_max_salary IN jobs2.max_salary%TYPE := 10000,
	p_result OUT NUMBER)
 IS
    v_cnt NUMBER :=0;

 BEGIN
    --동일한 job_id 체크
    SELECT COUNT(*) INTO v_cnt
	FROM jobs2
	WHERE job_id = p_job_id;

    IF v_cnt = 0 THEN
        p_result := 1;
        INSERT INTO jobs2(job_id, job_title, min_salary, max_salary)
	VALUES(p_job_id, p_job_title, p_min_salary, p_max_salary);
    ELSE
        p_result := 2;	
        UPDATE jobs2
	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;

 

프로시저 실행

DECLARE
  p_result NUMBER;
BEGIN
  my_new_jobs_proc4('c1', 'c11', 100, 1000, p_result);
  IF p_result = 1 THEN
     dbms_output.put_line('추가 되었습니다.');
  ELSE
     dbms_output.put_line('수정 되었습니다.');
  END IF;
END;

 

 

Q. hr) 사원번호를 입력받아 이름을 반환하는 함수를 구현 (사원이 없으면 사원이 없다고 출력)

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;
/   	

SELECT get_emp_name(100) FROM dual;

 

 

트리거(Trigger)

 - 특정 테이블의 데이터에 변경이 이루어 졌을 때 자동으로 다른 어떤 작업이 함께 수행되도록 설정하는 것

 

CREATE TABLE emp13(
    empno NUMBER PRIMARY KEY,
    ename VARCHAR2(20),
    job VARCHAR2(20)
 );

CREATE OR REPLACE TRIGGER trg_01
	AFTER INSERT
	ON emp13
	BEGIN
	   dbms_output.put_line('신입사원이 추가 되었습니다.');
END;
/
INSERT INTO emp13 VALUES(3, '홍길동', '개발');

 

 

트리거 유형

 - 문장레벨 트리거 - DML문을 실행할 때 한번만 트리거 실행

 - 행 레벨 트리거(FOR EACH ROW) - DML문 여러번 실행 => 트리거 여러번 수행

CREATE TABLE sal01(
   salno NUMBER PRIMARY KEY,
   sal NUMBER,
   empno NUMBER REFERENCES emp13(empno)
 )

 CREATE SEQUENCE sal01_salno_seq;

 CREATE OR REPLACE TRIGGER trg02
	AFTER INSERT
	ON emp13
	FOR EACH ROW
	BEGIN
	  INSERT INTO sal01 VALUES(sal01_salno_seq.NEXTVAL, 3000, :NEW.empno);
 END;


  - INSERT -> :OLD - NULL, :NEW -> 입력값
  - UPDATE -> :OLD ->변경전 값,  :NEW -> 변경후 값
  - DELETE -> :OLD -> 삭제전 값,  :NEW -> NULL


 INSERT INTO emp13 VALUES(2, '박길동', '개발');

 

 

Q.  사원이 삭제되면 그 사원의 급여 정보 (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;

 DELETE FROM emp13 WHERE empno = 2;