[ORACLE] 프로시저(Procedure) 작성 방법

프로시저란,

데이터베이스 내에서 특정 작업을 수행하는 코드 블록이다. 주로  여러 작업을 일괄적으로 처리할 때 사용한다.

함수와 다르게 반환 값이 없고, 대신에 출력 매개변수를 통해 결과를 반환할 수 있다.

프로시저 작성 방법

-- 1. 프로시저 선언
CREATE OR REPLACE PROCEDURE 프로시저_이름( 

-- 2. 매개변수 선언
     매개변수 [ IN || OUT ] 타입; 
)
IS
-- 3. 변수 선언
NAME VARCHAR2(10) := 'TEST'  -- := 연산자로 변수에 값을 할당 

--4. 프로시저 본문
BEGIN
[EXCEPTION 예외처리]
END;

프로시저 본문 안에 들어갈 수 있는 문법 정리

1. SELECT INTO 문 : SELECT 문의 결과를 변수에 저장

DECLARE -- 변수선언
    my_variable NUMBER;
BEGIN
    SELECT COUNT(*) INTO my_variable
    FROM employees
    WHERE department_id = 10;
    DBMS_OUTPUT.PUT_LINE('Number of employees: ' || my_variable);
END;
--SELECT INTO 문을 사용하여 SQL 쿼리의 결과를 변수에 저장
--employees 테이블에서 department_id가 10인 직원 수를 세어 my_variable 변수에 저장
--DBMS_OUTPUT.PUT_LINE을 사용하여 결과를 출력

 

2. IF THEN ELSE : 조건에 따라 다른 작업을 수행

DECLARE
    my_variable NUMBER := 5;
BEGIN
    IF my_variable < 10 THEN --my_variable 변수가 10보다 작으면 'Value is less than 10'을 출력
        DBMS_OUTPUT.PUT_LINE('Value is less than 10');
    ELSIF my_variable = 10 THEN  --my_variable 변수가 10이면 'Value is 10'을 출력
        DBMS_OUTPUT.PUT_LINE('Value is 10');
    ELSE --그 외의 경우에는 'Value is greater than 10'을 출력
        DBMS_OUTPUT.PUT_LINE('Value is greater than 10');
    END IF;
END;

 

3 - 1. LOOP : 기본 LOOP 문은 무한 루프이고, EXIT WHEN 문을 사용하여 조건이 충족될 때 루프를 종료

DECLARE
    counter NUMBER := 1;
BEGIN
    LOOP
        DBMS_OUTPUT.PUT_LINE('Counter value: ' || counter);
        counter := counter + 1;
        EXIT WHEN counter > 5;  -- counter 변수가 5보다 커지면 루프를 종료
    END LOOP;
END;

 

3 - 2. WHILE LOOP : 조건이 참일 경우 루프를 실행

DECLARE
    counter NUMBER := 1;
BEGIN
    WHILE counter <= 5 LOOP  --counter 변수가 5보다 작거나 같을 때까지 루프를 실행
        DBMS_OUTPUT.PUT_LINE('Counter value: ' || counter);
        counter := counter + 1;
    END LOOP;
END;

 

3 - 3. FOR LOOP : 범위를 정하 루프를 실행

BEGIN
    FOR counter IN 1..5 LOOP
        DBMS_OUTPUT.PUT_LINE('Counter value: ' || counter);
    END LOOP;
END;

 

4. CURSOR : SELECT 문의 결과를 한 행씩 처리

-- department_id가 10인 employees 테이블의 데이터를 조회하고, 
-- 각 직원의 ID와 이름을 출력하기
DECLARE
    CURSOR emp_cursor IS
        SELECT employee_id, first_name, last_name
        FROM employees
        WHERE department_id = 10;
    emp_record emp_cursor%ROWTYPE; -- %ROWTYPE : 특정 테이블이나 커서의 행 구조와 동일한 레코드 변수 선언
BEGIN
    OPEN emp_cursor;  -- 커서 열기
    LOOP
        FETCH emp_cursor INTO emp_record; -- 커서에서 행을 가져와 emp_record에 저장
        EXIT WHEN emp_cursor%NOTFOUND;  -- 커서의 모든 행을 처리하면 루프 종료
        DBMS_OUTPUT.PUT_LINE('Employee ID: ' || emp_record.employee_id || ', Name: ' || emp_record.first_name || ' ' || emp_record.last_name);
    END LOOP;
    CLOSE emp_cursor; -- 커서 닫기
END;

 

5. 예외처리 : 실행 중 생기는 오류를 처리

DECLARE
    my_variable NUMBER;
BEGIN
    my_variable := 1 / 0; -- 오류 발생
EXCEPTION  --EXCEPTION 블록에서 오류를 처리
    WHEN ZERO_DIVIDE THEN  -- ZERO_DIVIDE 예외가 발생하면 아래 메시지를 출력
        DBMS_OUTPUT.PUT_LINE('Division by zero is not allowed');
    WHEN OTHERS THEN  -- 다른 예외 발생시 아래 메시지 출력
        DBMS_OUTPUT.PUT_LINE('An unexpected error occurred');
END;