Oracle PL/SQL 프로시저 설명
1. 개념
• **프로시저(Procedure)**는 특정 작업을 수행하는 PL/SQL 서브프로그램입니다. • 함수와 달리 프로시저는 값을 반환하지 않으며, 주로 반복되는 작업이나 다양한 기능을 묶어두는 데 사용됩니다. • 입력 매개변수를 받아서 로직을 처리한 후, 결과는 출력 매개변수를 통해 반환할 수 있습니다.
⸻
2. 주요 특징
• 반환 값 없음: 프로시저는 값을 반환하지 않음. • 매개변수 사용 가능: 입력, 출력, 입력/출력 매개변수를 사용할 수 있음. • 다양한 용도: 데이터 수정, 트랜잭션 처리, 복잡한 로직 등을 처리할 때 사용.
⸻
3. 프로시저 문법
CREATE OR REPLACE PROCEDURE 프로시저이름 (
매개변수명 데이터타입 [IN | OUT | IN OUT]
) IS
-- 변수 선언부
BEGIN
-- 로직 처리 NULL; -- 여기에 실제 처리 로직이 들어감
END;
• IN: 입력 매개변수 (값을 전달받음) • OUT: 출력 매개변수 (값을 반환) • IN OUT: 입력과 출력 모두 가능한 매개변수
⸻
4. 프로시저 설명
| 항목 | 설명 |
|---|---|
| 프로시저(Procedure) | 값을 반환하지 않는 PL/SQL 서브프로그램 |
| 사용 목적 | 복잡한 작업을 서브프로그램으로 캡슐화하여 재사용 가능 |
| 매개변수 | IN, OUT, IN OUT 매개변수 사용 가능 |
| 예시 | EXECUTE 프로시저명(매개변수); |
⸻
=
5. 프로시저 예시 ===
CREATE OR REPLACE PROCEDURE add_employee (
p_emp_id IN NUMBER, p_name IN VARCHAR2, p_salary IN NUMBER, p_department IN VARCHAR2
) IS BEGIN
INSERT INTO employees (emp_id, name, salary, department) VALUES (p_emp_id, p_name, p_salary, p_department); COMMIT;
END;
프로시저 호출 예시:
EXECUTE add_employee(101, 'John Doe', 5000, 'Sales');
⸻
6. 주요 활용 사례
• 데이터 삽입/수정: INSERT, UPDATE, DELETE 등 반복적인 데이터 조작 • 비즈니스 로직 캡슐화: 여러 SQL 문을 하나의 프로시저에 묶어서 복잡한 로직을 구현 • 에러 처리: 예외 처리 구문을 프로시저 내에서 정의하여 안정성 증가
⸻
예외 처리와 매개변수 예시
⸻
1. 예외 처리(Exception Handling)
Oracle PL/SQL 프로시저는 EXCEPTION 블록을 사용해 오류 발생 시 적절한 조치를 취할 수 있습니다.
예외 처리 포함한 프로시저 예시
CREATE OR REPLACE PROCEDURE delete_employee (
p_emp_id IN NUMBER
) IS BEGIN
DELETE FROM employees WHERE emp_id = p_emp_id;
IF SQL%ROWCOUNT = 0 THEN
RAISE_APPLICATION_ERROR(-20001, '해당 사번의 직원이 존재하지 않습니다.');
END IF;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('오류 발생: ' || SQLERRM);
END;
특징 설명 (미디어위키 표 형식):
| 구문 | 설명 |
|---|---|
| EXCEPTION | 오류 발생 시 실행할 블록 시작 |
| WHEN OTHERS THEN | 모든 예외를 포괄적으로 처리 |
| SQLERRM | 오류 메시지 반환 |
| RAISE_APPLICATION_ERROR | 사용자 정의 오류 메시지 출력 (에러 코드 범위: -20001 ~ -20999) |
⸻
==
2. IN, OUT, IN OUT 매개변수 예시 ====
예제: 직원 급여 인상 후 결과 반환
CREATE OR REPLACE PROCEDURE raise_salary (
p_emp_id IN NUMBER, p_raise_percent IN NUMBER, p_new_salary OUT NUMBER
) IS BEGIN
UPDATE employees SET salary = salary + salary * p_raise_percent / 100 WHERE emp_id = p_emp_id;
SELECT salary INTO p_new_salary FROM employees WHERE emp_id = p_emp_id;
COMMIT;
END;
호출 예시 (익명 블록 사용):
DECLARE
v_new_salary NUMBER;
BEGIN
raise_salary(101, 10, v_new_salary);
DBMS_OUTPUT.PUT_LINE('인상된 급여: ' || v_new_salary);
END;
⸻
3. 매개변수 유형 요약
| 매개변수 유형 | 설명 |
|---|---|
| IN | 호출 시 외부에서 값을 전달 (기본값) |
| OUT | 프로시저가 값을 반환하기 위해 사용 |
| IN OUT | 전달도 받고 반환도 가능 (읽기/쓰기) |
좋습니다! 이번에는 Oracle PL/SQL의 중요한 개념 중 하나인 **트리거(Trigger)**를 먼저 소개드릴게요. 이후 원하시면 패키지, 커서도 이어서 설명해드릴 수 있습니다.
⸻
5. 활용 예시 • 데이터 감사: 변경 이력 자동 기록 • 비즈니스 룰 적용: 특정 조건에서 입력 차단 • 자동 계산: 다른 칼럼 자동 계산 (예: 합계, 포인트 등)
⸻
⸻