프로시저(DB)

프로시저란?

프로시저는 일련의 SQL 문과 PL/SQL 블록을 캡슐화한 재사용 가능한 코드 블록입니다. 프로시저는 특정 작업을 수행하고, 필요에 따라 매개 변수를 받아 다양한 작업을 처리할 수 있습니다. 프로시저는 데이터베이스 내에 저장되며, 호출 시 필요한 작업을 수행합니다.

프로시저의 구성 요소

1. 프로시저 헤더:
   • 프로시저의 이름과 매개 변수를 정의합니다.
2. 프로시저 본문:
   • 실행부: 실제 작업을 수행하는 SQL 문과 PL/SQL 블록을 포함합니다.
   • 예외 처리부: 오류 발생 시 처리 방법을 정의합니다.

기본 프로시저 작성

간단한 프로시저를 작성하여 메시지를 출력해보겠습니다.

CREATE OR REPLACE PROCEDURE p_test IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('Oracle 시험 잘보세요');
    DBMS_OUTPUT.PUT_LINE('Okim');
    DBMS_OUTPUT.PUT_LINE('O33333');
END;

프로시저 실행

EXEC p_test;

매개 변수가 있는 프로시저 작성

이제 매개 변수를 받아서 메시지를 출력하는 프로시저를 작성해보겠습니다.

CREATE OR REPLACE PROCEDURE p_test2(name IN VARCHAR2) IS
BEGIN
    DBMS_OUTPUT.PUT_LINE(name || '님 시험 합격입니다.');
END;

프로시저 실행

EXEC p_test2('KIM');

사용자 테이블 생성 및 데이터 삽입 프로시저

사용자 정보를 저장하는 userlist 테이블을 생성하고, 데이터를 삽입하는 프로시저를 작성합니다.

테이블 생성

DROP TABLE userlist;

CREATE TABLE userlist(
    id VARCHAR2(10),
    name VARCHAR2(20),
    age NUMBER,
    addr VARCHAR2(50)
);

프로시저 작성

CREATE OR REPLACE PROCEDURE p_userlist(
    id IN userlist.id%TYPE := 'zoo',
    name IN userlist.name%TYPE DEFAULT '김연아',
    age IN userlist.age%TYPE := 10,
    addr IN userlist.addr%TYPE := NULL
)
IS
BEGIN
    INSERT INTO userlist (id, name, age, addr)
    VALUES (id, name, age, addr);

    DBMS_OUTPUT.PUT_LINE('insert 정보는 ' || id || ' ' || name || ' ' || age || ' ' || NVL(addr, 'NULL'));
END;

프로시저 실행

EXEC p_userlist('jin', 'lee', 20, '서울'); -- 모든 필드 제공
EXEC p_userlist; -- 기본값 사용
EXEC p_userlist(id => '길동', age => 33); -- 일부 필드 제공

테이블 데이터 조회

SELECT * FROM userlist;

최고 급여 상위 5명 출력 프로시저

이번에는 emp 테이블에서 급여가 가장 높은 상위 5명의 정보를 출력하는 프로시저를 작성해보겠습니다.

프로시저 작성 (방법 1)

CREATE OR REPLACE PROCEDURE p_empMaxSal IS
    v_emprow emp%ROWTYPE;
BEGIN
    FOR i IN 1..5 LOOP
        SELECT * INTO v_emprow FROM
            (SELECT * FROM emp WHERE sal IS NOT NULL ORDER BY sal DESC)
            WHERE ROWNUM <= i;
        DBMS_OUTPUT.PUT_LINE(v_emprow.empno || ' ' || v_emprow.ename || ' ' || v_emprow.sal);
    END LOOP;
END;

프로시저 실행

EXEC p_empMaxSal;

프로시저 작성 (방법 2)

CREATE OR REPLACE PROCEDURE emp_top5 IS
    v_emprow emp%ROWTYPE;
BEGIN
    FOR i IN 1..5 LOOP
        SELECT * INTO v_emprow FROM
            (SELECT * FROM (SELECT * FROM emp ORDER BY sal DESC) WHERE ROWNUM <= i ORDER BY sal, ename)
            WHERE ROWNUM = 1 ORDER BY sal ASC;
        DBMS_OUTPUT.PUT_LINE(v_emprow.empno || ' ' || v_emprow.ename || ' ' || v_emprow.sal);
    END LOOP;
END;

프로시저 실행

EXEC emp_top5;