프로시저

쿼리를 마치 하나의 함수처럼 실행하기 위한 쿼리의 집합이다

구문형식

CREATE OR REPLACE PROCEDURE 프로시저명
(파라미터1 데이터타입,
….
)
IS [AS]
변수 선언부…;
BEGIN
프로시저 본문처리…;
EXCEPTION
예외처리…;
END;

프로시저 실행
구문형식

EXEC 혹은 EXECUTE 프로시저명(파라미터…);
exec hello_world;

---

create or replace procedure hello_world
is
  message varchar2(100);
begin
  message := 'Hello World';
  dbms_output.put_line(message);
end;

프로시저 실행

EXEC 혹은 EXECUTE 프로시저명(파라미터...)

exec hello_world;

create or replace procedure hello_world(p_message in varchar2)
is
begin
  dbms_output.put_line(p_message);
end;

exec hello_world('Korea');

작성된 Stored Procedure 확인

SELECT object_name, object_type
FROM user_objects
WHERE object_type = 'PROCEDURE';

Stored Procedure의 Source를 데이터 사전을 이용해서 얻음
SELECT text
FROM user_source
WHERE name = 'HELLO_WORLD';

부서테이블에 부서정보를 입력하는 프로시저를 생성

create or replace procedure add_department(
                          p_deptno in dept.deptno%type,
                          p_dname in dept.dname%type,
                          p_loc in dept.loc%type)
is
begin
  --parameter 변수에 입력받은 값으로 부서테이블의 각 컬럼에 데이터를
  --추가 그리고 정상적으로 transaction 종료
  INSERT INTO dept
  VALUES (p_deptno,p_dname,p_loc);
  COMMIT;
  
  exception when others then
    dbms_output.put_line(p_dname || 'register is failed');
    ROLLBACK;
end;

exec add_department(60,'IT SERVICE','BUSAN');

사원테이블에 사원정보를 저장

create or replace procedure register_emp(
                           e_no emp.empno%type,
                           e_name emp.ename%type,
                           e_job emp.job%type,
                           e_mgr emp.mgr%type,
                           e_sal emp.sal%type,
                           e_comm emp.comm%type,
                           e_deptno emp.deptno%type)
is
begin
  INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno)
  VALUES (e_no,e_name,e_job,e_mgr,SYSDATE,e_sal,e_comm,e_deptno);
  COMMIT;
  
  exception when others then
    dbms_output.put_line(e_name || 'register is failed!');
    ROLLBACK;
end;

execute register_emp(9000,'PETER','MANAGER',7902,6000,200,30);

부서번호를 통해서 부서명과 부서의 위치 구하기

create or replace procedure output_department(
                           p_dept_no in dept.deptno%type)
is
  d_dname dept.dname%type;
  d_loc dept.loc%type;
begin
  --parameter 변수로부터 부서번호를 받아서 해당 부서의 부서명,
  --부서의 위치 구하기
  SELECT dname,loc
  INTO d_dname,d_loc
  FROM dept
  WHERE deptno = p_dept_no;
  dbms_output.put_line(d_dname || ' ' || d_loc);
end;

exec output_department(10);

SQL문을 이용해서 검색시 하나 이상의 행이 만들어질 때 INTO를
사용하면 오류가 발생할 수 있음

create or replace procedure info_hiredate(p_year in varchar2)
is
  --%rowtype으로 데이터 타입이 지정되어 있는 사원테이블(dept)의
  --하나의 행이 가지는 모든 컬럼의 데이터 타입을 가져옴
  l_emp emp%rowtype;
begin
  SELECT empno,ename,sal
  INTO l_emp.empno,l_emp.ename,l_emp.sal
  FROM emp
  WHERE TO_CHAR(hiredate,'YYYY') = p_year;
  
  dbms_output.put_line(l_emp.empno || ' ' 
                  || l_emp.ename || ' ' || l_emp.sal);
end;

하나의 행이 반환되어 에러가 발생하지 않음

exec info_hiredate('1980');
여러개의 행이 반환되어 에러 발생
exec info_hiredate('1981');

---

커서(cursor)를 이용하면 질의 수행 결과 반환되는 여러 행을 처리할 수 있음

create or replace procedure info_hiredate(p_year in varchar2)
is
  l_emp emp%rowtype;
  --커서의 선언
  cursor emp_cur is
    SELECT empno,ename,sal
    FROM emp
    WHERE TO_CHAR(hiredate,'YYYY') = p_year;
begin
  --커서 열기
  open emp_cur;
  loop
    --커서로부터 데이터 읽기
    fetch emp_cur into l_emp.empno,l_emp.ename,l_emp.sal;
    exit when emp_cur%notfound;
    
    dbms_output.put_line(l_emp.empno || ' ' 
                   || l_emp.ename || ' ' || l_emp.sal);
  
  end loop;  
  close emp_cur;
end;

exec info_hiredate('1981');

SALES 부서에 속한 사원의 정보 보기

create or replace procedure emp_info(p_dept dept.dname%type)
is
  --커서 선언
  cursor emp_cur is
    SELECT empno,ename
    FROM emp e JOIN dept d
    ON e.deptno=d.deptno
    WHERE dname = UPPER(p_dept);
  --변수 선언
  e_emp_no emp.empno%type;
  e_emp_name emp.ename%type;
begin
  -- 커서 열기
  open emp_cur;
  
  loop
    fetch emp_cur into e_emp_no,e_emp_name;
    exit when emp_cur%notfound;
    dbms_output.put_line(e_emp_no || ' ' || e_emp_name);
  end loop;
  --커서 닫기
  close emp_cur;
end;

exec emp_info('SALES');