프로시저 생성과 호출

프로시저 생성과 EXEC 호출, 커서 FOR 루프 출력 및 부서와 직업을 매개변수로 바꾸는 실습

CREATE OR REPLACE PROCEDUREEXEC커서 FOR LOOPDBMS_OUTPUTSERVEROUTPUT%TYPE

#핵심 요약

프로시저는 CREATE OR REPLACE PROCEDURE 이름 IS ... BEGIN ... END;로 만들고 SQL*Plus에서 EXEC 이름으로 실행함
사람이 불러야 실행됨

괄호(매개변수)가 없는 프로시저는 이름만으로 실행함
정해진 규칙대로 매번 같은 일을 하는 배치 작업이 이런 모양임

DBMS_OUTPUT.PUT_LINE의 출력은 SQL*Plus에서 SET SERVEROUTPUT ON을 해야 보임

매개변수 타입을 emp_t.deptno%type처럼 쓰면 테이블 컬럼 타입을 따름

#개념과 동작 원리

실습은 이행 비교 기준 데이터(emp_m)를 건드리지 않으려고 복사본 emp_t를 만들어 썼음
emp_m의 컬럼 구성은 이 수업 자료 페이지에 없음
예제와 INSERT 문이 쓰는 컬럼은 empno, ename, sal, deptno 네 개임

FOR r IN (SELECT...) LOOP ... END LOOP;는 SELECT 결과를 한 행씩 r에 담아 반복함
실습에서는 각 행을 CHR(9)(탭)으로 구분해 출력했음

#필수 명령어 또는 SQL

#실습 테이블

SQL*Plus (객체 소유 계정)객체 또는 설정을 변경함

CREATE TABLE emp_t AS SELECT * FROM emp_m;
ALTER TABLE emp_t ADD CONSTRAINT emp_t_pk PRIMARY KEY (empno);

결과 해석: 개별 실행 출력은 기재 없음

#매개변수 없는 프로시저

SQL*Plus (객체 소유 계정)객체 또는 설정을 변경함

CREATE OR REPLACE PROCEDURE p_emp_list
IS
BEGIN
  FOR r IN (SELECT empno, ename, sal, deptno
              FROM emp_t
             ORDER BY deptno, empno) LOOP
    DBMS_OUTPUT.PUT_LINE(r.deptno || CHR(9) || r.empno || CHR(9) ||
                         RPAD(r.ename, 8) || r.sal);
  END LOOP;
END;
/

SQL*Plus (객체 소유 계정)현재 세션 설정만 변경함

SET SERVEROUTPUT ON
EXEC p_emp_list

결과 해석:

WHERE deptno=처럼 우변이 비어 있으면 컴파일되지 않음

#부서번호를 매개변수로(문제 2의 답)

SQL*Plus (객체 소유 계정)객체 또는 설정을 변경함

CREATE OR REPLACE PROCEDURE p_emp_list
( p_deptno IN emp_t.deptno%type )
IS
BEGIN
  FOR r IN (SELECT empno, ename, sal, deptno
              FROM emp_t
             WHERE deptno = p_deptno
             ORDER BY deptno, empno) LOOP
    DBMS_OUTPUT.PUT_LINE(r.deptno || CHR(9) || r.empno || CHR(9) ||
                         RPAD(r.ename, 8) || r.sal);
  END LOOP;
END;
/

SQL*Plus (객체 소유 계정)조회만 함

EXEC p_emp_list(30)

결과 해석:

CREATE OR REPLACE로 같은 이름을 다시 만들면 기존 정의가 바뀜

#직업으로 조회(문제 3의 답)

SQL*Plus (객체 소유 계정)객체 또는 설정을 변경함

CREATE OR REPLACE PROCEDURE emp_job
( p_job IN emp.job%type )
IS
BEGIN
  FOR r IN (SELECT empno, ename, sal, job
              FROM emp
             WHERE job = p_job
             ORDER BY deptno, empno) LOOP
    DBMS_OUTPUT.PUT_LINE(r.job || CHR(9) || r.empno || CHR(9) ||
                         RPAD(r.ename, 8) || r.sal);
  END LOOP;
END;
/

SQL*Plus (객체 소유 계정)조회만 함

EXEC emp_job('SALESMAN');

결과 해석:

연습 문제는 대상 테이블을 emp_t에서 일반 실습 테이블 emp로 바꾸게 했음
ORDER BY deptno는 SELECT 목록에 없는 컬럼이지만 FROM 테이블의 컬럼이라 정렬에 쓸 수 있음