Oracle Stored Procedure, 꼭 알아야 할 데이터베이스의 필수 개념과 활용법

1. Oracle Stored Procedure란?

Oracle에서 **Stored Procedure(저장 프로시저)**는 데이터베이스에 미리 저장된 SQL과 PL/SQL 블록을 말합니다. 이는 반복적으로 사용되는 SQL 쿼리와 비즈니스 로직을 하나의 프로시저로 작성하고, 필요할 때마다 호출할 수 있는 구조입니다. 마치 프로그래밍 언어의 함수처럼 동작하지만, 데이터베이스에서 동작하는 특성을 가지고 있습니다.

간단히 말해, Stored Procedure는 사전에 정의된 SQL 명령어들이 모여 하나의 작업 단위로 실행되는 프로그램입니다. 이를 통해 특정한 작업을 효율적이고 일관되게 수행할 수 있습니다.

---

2. Stored Procedure의 장점

Stored Procedure를 도입할 때의 장점은 여러 가지가 있습니다:

• 성능 최적화: 한 번 저장된 프로시저는 데이터베이스에서 컴파일되고 캐싱되므로 반복적으로 실행될 때 성능을 최적화할 수 있습니다.
• 코드 재사용성: 프로시저는 여러 애플리케이션에서 재사용할 수 있으므로 코드 중복을 줄이고 유지보수가 용이해집니다.
• 보안 강화: 데이터베이스 관리자는 프로시저 내에서만 특정 데이터에 접근을 허용할 수 있으므로, 데이터 보안이 강화됩니다.
• 비즈니스 로직 캡슐화: 복잡한 비즈니스 로직을 데이터베이스 내에 캡슐화하여, 애플리케이션의 복잡도를 줄이고 로직의 일관성을 유지할 수 있습니다.

---

반응형

3. 언제 Stored Procedure를 사용하면 좋을까?

Stored Procedure는 다음과 같은 경우에 유용합니다:

• 복잡한 비즈니스 로직이 필요한 경우: 여러 SQL 쿼리들을 조합해 하나의 작업으로 처리하고 싶을 때.
• 대량의 데이터를 처리하는 경우: 많은 데이터를 처리하는 SQL 로직을 애플리케이션 레이어가 아닌 데이터베이스 레벨에서 직접 처리함으로써 성능을 향상시킬 수 있습니다.
• 트랜잭션 관리가 필요한 경우: 여러 SQL 작업을 하나의 트랜잭션으로 묶어 일괄 처리하고, 실패 시 롤백하는 등의 작업을 효율적으로 관리할 수 있습니다.
• 반복 작업이 자주 발생하는 경우: 주기적으로 실행해야 하는 작업들을 프로시저로 작성해 자동화할 수 있습니다.

---

4. Oracle Stored Procedure의 기본 문법

Stored Procedure의 기본 구조는 다음과 같습니다

CREATE OR REPLACE PROCEDURE 프로시저명 (파라미터 목록)
IS
  -- 변수 선언
BEGIN
  -- SQL 문 실행
EXCEPTION
  -- 예외 처리
END 프로시저명;

---

5. 예제: 간단한 저장 프로시저 생성 및 실행

고객의 ID를 받아 고객 정보를 출력하는 간단한 프로시저를 만들어 보겠습니다.

-- 프로시저 생성
CREATE OR REPLACE PROCEDURE get_customer_info (p_customer_id IN NUMBER) 
IS
  v_customer_name VARCHAR2(100);
BEGIN
  -- 고객의 이름을 조회하는 SQL 문
  SELECT customer_name INTO v_customer_name
  FROM customers
  WHERE customer_id = p_customer_id;
  
  -- 고객 이름을 출력
  DBMS_OUTPUT.PUT_LINE('Customer Name: ' || v_customer_name);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Customer not found.');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END get_customer_info;

---

6. 프로시저 실행하기

이제 프로시저를 실행하는 방법은 다음과 같습니다

-- 프로시저 실행
BEGIN
  get_customer_info(101); -- 고객 ID 101의 정보를 가져옵니다.
END;

위 코드에서는 DBMS_OUTPUT.PUT_LINE을 사용하여 고객의 이름을 출력하도록 설정하였으며, 고객 정보가 없는 경우 예외 처리도 포함하였습니다.

---

728x90

Stored Procedure는 데이터베이스의 성능을 향상시키고, 복잡한 비즈니스 로직을 캡슐화하며, 코드 재사용성과 보안성을 높여주는 강력한 도구입니다. 특히 대규모 데이터 처리가 빈번하게 발생하는 환경에서는 필수적인 기능이라고 할 수 있습니다. 이번 포스팅을 통해 Oracle에서 Stored Procedure를 어떻게 정의하고 사용하는지 간단히 알아보았으니, 실제로 다양한 시나리오에 적용해 보시길 바랍니다!