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 문을 하나의 프로시저에 묶어서 복잡한 로직을 구현 • 에러 처리: 예외 처리 구문을 프로시저 내에서 정의하여 안정성 증가
⸻ 좋습니다! 아래에 Oracle PL/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)**를 먼저 소개드릴게요. 이후 원하시면 패키지, 커서도 이어서 설명해드릴 수 있습니다.
⸻
Oracle PL/SQL 트리거(Trigger) 설명
1. 개념 • **트리거(Trigger)**는 특정 **이벤트(DML 이벤트)**가 발생했을 때 자동으로 실행되는 PL/SQL 블록입니다. • 이벤트에는 INSERT, UPDATE, DELETE 등이 있으며, 주로 데이터 무결성 유지 또는 자동 감사 처리 등에 사용됩니다.
⸻
2. 트리거 기본 구조
CREATE [OR REPLACE] TRIGGER 트리거명 {BEFORE | AFTER} {INSERT | UPDATE | DELETE} ON 테이블명 [FOR EACH ROW] BEGIN
-- 실행될 PL/SQL 코드
END;
• BEFORE: 트리거가 DML 동작 전에 실행됨 • AFTER: 트리거가 DML 동작 후에 실행됨 • FOR EACH ROW: 행 단위 트리거 (각 행마다 실행)
⸻
3. 트리거 예시
예제: 직원 테이블에서 급여 변경 시 변경 전/후 급여 로그 저장
CREATE OR REPLACE TRIGGER trg_salary_audit BEFORE UPDATE OF salary ON employees FOR EACH ROW BEGIN
INSERT INTO salary_log (emp_id, old_salary, new_salary, changed_on) VALUES (:OLD.emp_id, :OLD.salary, :NEW.salary, SYSDATE);
END;
• :OLD: 변경 전 값 • :NEW: 변경 후 값
⸻
4. 미디어위키 표로 정리: 트리거 주요 요소
| 항목 | 설명 |
|---|---|
| 트리거(Trigger) | 특정 DML 이벤트 발생 시 자동 실행되는 PL/SQL 블록 |
| BEFORE / AFTER | DML 전후 시점 지정 |
| INSERT / UPDATE / DELETE | 트리거를 작동시키는 이벤트 종류 |
| FOR EACH ROW | 각 행에 대해 실행되는 트리거 지정 |
| :OLD / :NEW | 변경 전/후 값을 참조할 때 사용 |
⸻
5. 활용 예시 • 데이터 감사: 변경 이력 자동 기록 • 비즈니스 룰 적용: 특정 조건에서 입력 차단 • 자동 계산: 다른 칼럼 자동 계산 (예: 합계, 포인트 등)
⸻
⸻