저장 프로시저와 함수

SQL Stored Procedure 와 Fuction

Byleader.bettermind@gmail.com

저장 프로시저와 함수는 SQL에서 코드 블록을 재사용하는 방법입니다. 저장 프로시저는 주로 작업 수행에 사용되며, 함수는 값을 반환합니다. 각각의 생성, 수정, 삭제 방법과 IN, OUT, INOUT 파라미터 사용법을 배우고, 두 개념의 차이점을 이해해 보세요.

저장 프로시저와 함수

저장 프로시저 (Procedure)와 함수 (Function) 개념

SQL에서 저장 프로시저와 함수는 반복적인 작업을 효율적으로 처리하기 위해 사용되는 코드 블록입니다. 이 두 가지 개념은 비슷하게 보일 수 있지만, 그 목적과 사용 방식에서 차이가 있습니다.

저장 프로시저 (Procedure)

저장 프로시저는 특정 작업을 수행하기 위해 미리 정의된 SQL 명령어의 집합입니다. 주로 데이터베이스에서 여러 SQL 문을 실행하거나 복잡한 로직을 처리하는 데 사용됩니다. 저장 프로시저는 호출 시 결과를 반환하지 않으며, 주로 데이터 조작 작업에 사용됩니다.

저장 프로시저 생성 예제:

CREATE OR REPLACE PROCEDURE increase_salary (
    emp_id IN NUMBER,
    increment IN NUMBER
) AS
BEGIN
    UPDATE emp
    SET sal = sal + increment
    WHERE empno = emp_id;
END;

위 예제는 특정 직원의 급여를 증가시키는 저장 프로시저입니다. emp_id와 increment라는 두 개의 입력 파라미터를 받아서, 해당 직원의 급여를 증가시킵니다.

함수 (Function)

함수는 특정 값을 계산하거나 변환하여 결과를 반환하는 코드 블록입니다. 함수는 호출 시 항상 값을 반환하며, 주로 계산이나 데이터 변환 작업에 사용됩니다. SQL 쿼리 내에서 직접 호출할 수 있으며, SELECT 문과 함께 사용될 수 있습니다.

함수 생성 예제:

CREATE OR REPLACE FUNCTION get_salary (
    emp_id IN NUMBER
) RETURN NUMBER AS
    emp_salary NUMBER;
BEGIN
    SELECT sal INTO emp_salary
    FROM emp
    WHERE empno = emp_id;

    RETURN emp_salary;
END;

위 예제는 특정 직원의 급여를 반환하는 함수입니다. emp_id라는 입력 파라미터를 받아 해당 직원의 급여를 조회한 후, 그 값을 반환합니다.

저장 프로시저와 함수의 차이점

  1. 목적: 저장 프로시저는 주로 작업을 수행하는 데 사용되며, 함수는 값을 반환하는 데 중점을 둡니다.
  2. 반환값: 저장 프로시저는 결과를 반환하지 않지만, 함수는 항상 값을 반환합니다.
  3. 사용 위치: 함수는 SQL 쿼리 내에서 호출할 수 있지만, 저장 프로시저는 일반적으로 CALL 문을 사용하여 호출합니다.

이러한 개념을 이해하면 SQL에서 저장 프로시저와 함수를 효과적으로 활용할 수 있습니다.

생성, 수정, 삭제 방법

SQL에서 저장 프로시저와 함수를 생성, 수정, 삭제하는 방법은 다음과 같습니다. 각각의 과정에 대해 자세히 설명하겠습니다.

1. 저장 프로시저 생성

저장 프로시저는 CREATE OR REPLACE PROCEDURE 구문을 사용하여 생성합니다. 기본적인 문법은 다음과 같습니다.

CREATE OR REPLACE PROCEDURE 프로시저명 (
    파라미터명1 IN 데이터타입,
    파라미터명2 OUT 데이터타입
) AS
BEGIN
    -- SQL 명령어
END;

예제:

CREATE OR REPLACE PROCEDURE update_employee_name (
    emp_id IN NUMBER,
    new_name IN VARCHAR2
) AS
BEGIN
    UPDATE emp
    SET ename = new_name
    WHERE empno = emp_id;
END;

위 예제는 직원의 이름을 업데이트하는 저장 프로시저입니다.

2. 저장 프로시저 수정

저장 프로시저를 수정하려면 CREATE OR REPLACE PROCEDURE 구문을 사용하여 기존 프로시저를 다시 정의합니다. 기존 프로시저를 삭제할 필요 없이 새로운 정의로 덮어씌울 수 있습니다.

예제:

CREATE OR REPLACE PROCEDURE update_employee_name (
    emp_id IN NUMBER,
    new_name IN VARCHAR2,
    new_job IN VARCHAR2
) AS
BEGIN
    UPDATE emp
    SET ename = new_name, job = new_job
    WHERE empno = emp_id;
END;

위 예제는 직원의 이름과 직무를 동시에 업데이트하는 프로시저로 수정된 모습입니다.

3. 저장 프로시저 삭제

저장 프로시저를 삭제할 때는 DROP PROCEDURE 구문을 사용합니다. 삭제할 프로시저의 이름을 지정하면 됩니다.

예제:

DROP PROCEDURE update_employee_name;

위 예제는 update_employee_name이라는 저장 프로시저를 삭제하는 명령입니다.

4. 함수 생성

함수는 CREATE OR REPLACE FUNCTION 구문을 사용하여 생성합니다. 기본적인 문법은 다음과 같습니다.

CREATE OR REPLACE FUNCTION 함수명 (
    파라미터명 IN 데이터타입
) RETURN 반환데이터타입 AS
BEGIN
    -- SQL 명령어
    RETURN 결과값;
END;

예제:

CREATE OR REPLACE FUNCTION calculate_bonus (
    emp_id IN NUMBER
) RETURN NUMBER AS
    bonus_amount NUMBER;
BEGIN
    SELECT sal * 0.1 INTO bonus_amount
    FROM emp
    WHERE empno = emp_id;

    RETURN bonus_amount;
END;

위 예제는 특정 직원의 보너스를 계산하여 반환하는 함수입니다.

5. 함수 수정

함수를 수정하는 방법도 저장 프로시저와 마찬가지로 CREATE OR REPLACE FUNCTION 구문을 사용하여 기존 함수를 다시 정의합니다.

예제:

CREATE OR REPLACE FUNCTION calculate_bonus (
    emp_id IN NUMBER,
    bonus_rate IN NUMBER
) RETURN NUMBER AS
    bonus_amount NUMBER;
BEGIN
    SELECT sal * bonus_rate INTO bonus_amount
    FROM emp
    WHERE empno = emp_id;

    RETURN bonus_amount;
END;

위 예제는 보너스 비율을 파라미터로 받아 계산하는 함수로 수정된 모습입니다.

6. 함수 삭제

함수를 삭제할 때는 DROP FUNCTION 구문을 사용합니다. 삭제할 함수의 이름을 지정하면 됩니다.

예제:

DROP FUNCTION calculate_bonus;

위 예제는 calculate_bonus라는 함수를 삭제하는 명령입니다.

이러한 방법들을 통해 SQL에서 저장 프로시저와 함수를 효율적으로 관리할 수 있습니다.

IN, OUT, INOUT 파라미터 사용법

SQL에서 저장 프로시저와 함수는 파라미터를 사용하여 외부에서 값을 전달받거나 반환할 수 있습니다. 이때 사용되는 파라미터의 종류에는 IN, OUT, INOUT이 있습니다. 각 파라미터의 사용법을 자세히 살펴보겠습니다.

1. IN 파라미터

IN 파라미터는 프로시저나 함수에 값을 전달할 때 사용됩니다. 이 파라미터는 호출 시 외부에서 값을 받아들이며, 프로시저나 함수 내에서만 사용됩니다. IN 파라미터는 읽기 전용이며, 값을 변경할 수 없습니다.

예제:

CREATE OR REPLACE PROCEDURE display_employee (
    emp_id IN NUMBER
) AS
    emp_name VARCHAR2(50);
BEGIN
    SELECT ename INTO emp_name
    FROM emp
    WHERE empno = emp_id;

    DBMS_OUTPUT.PUT_LINE('Employee Name: ' || emp_name);
END;

위 예제는 직원 ID를 받아 해당 직원의 이름을 출력하는 저장 프로시저입니다. emp_id는 IN 파라미터로, 호출 시 외부에서 값을 전달받습니다.

2. OUT 파라미터

OUT 파라미터는 프로시저나 함수에서 값을 호출한 쪽으로 반환할 때 사용됩니다. 이 파라미터는 호출 시 초기화되지 않으며, 프로시저나 함수 내에서 값을 설정한 후 호출한 쪽으로 전달합니다. OUT 파라미터는 주로 결과 값을 반환하는 데 사용됩니다.

예제:

CREATE OR REPLACE PROCEDURE get_employee_salary (
    emp_id IN NUMBER,
    emp_salary OUT NUMBER
) AS
BEGIN
    SELECT sal INTO emp_salary
    FROM emp
    WHERE empno = emp_id;
END;

위 예제는 직원 ID를 받아 해당 직원의 급여를 OUT 파라미터로 반환하는 저장 프로시저입니다. emp_salary는 OUT 파라미터로, 프로시저 실행 후 호출한 쪽에서 값을 사용할 수 있습니다.

3. INOUT 파라미터

INOUT 파라미터는 프로시저나 함수에 값을 전달하고, 동시에 프로시저나 함수 내에서 값을 변경하여 호출한 쪽으로 반환할 때 사용됩니다. 즉, INOUT 파라미터는 입력과 출력을 모두 수행할 수 있습니다.

예제:

CREATE OR REPLACE PROCEDURE adjust_salary (
    emp_id IN NUMBER,
    adjustment INOUT NUMBER
) AS
BEGIN
    UPDATE emp
    SET sal = sal + adjustment
    WHERE empno = emp_id;

    adjustment := adjustment + 100; -- 조정 후 값을 변경
END;

위 예제는 직원 ID와 급여 조정 값을 받아 해당 직원의 급여를 조정하는 저장 프로시저입니다. adjustment는 INOUT 파라미터로, 프로시저 실행 후 조정된 값을 호출한 쪽으로 반환합니다.

요약

  • IN 파라미터: 외부에서 값을 전달받아 읽기 전용으로 사용.
  • OUT 파라미터: 프로시저나 함수에서 값을 호출한 쪽으로 반환.
  • INOUT 파라미터: 값을 전달받고, 변경된 값을 호출한 쪽으로 반환.

이러한 파라미터를 적절히 활용하면 저장 프로시저와 함수를 더욱 유용하게 사용할 수 있습니다.

프로시저와 함수의 차이점

SQL에서 저장 프로시저와 함수는 모두 코드 블록을 재사용하기 위한 방법이지만, 그 목적과 사용 방식에서 여러 가지 차이점이 있습니다. 아래에서 주요 차이점을 자세히 살펴보겠습니다.

1. 반환 값

  • 저장 프로시저: 저장 프로시저는 값을 반환하지 않습니다. 대신, 데이터베이스에서 작업을 수행하고 결과를 출력하는 데 중점을 둡니다. 저장 프로시저는 DBMS_OUTPUT.PUT_LINE과 같은 방법으로 결과를 출력할 수 있습니다.

예제:

CREATE OR REPLACE PROCEDURE display_message AS
BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello, World!');
END;

위 예제는 메시지를 출력하는 저장 프로시저입니다. 반환 값이 없습니다.

  • 함수: 함수는 항상 값을 반환합니다. 호출 시 결과를 반환하여 다른 SQL 쿼리에서 사용할 수 있습니다.

예제:

CREATE OR REPLACE FUNCTION get_greeting RETURN VARCHAR2 AS
BEGIN
    RETURN 'Hello, World!';
END;

위 예제는 인사 메시지를 반환하는 함수입니다.

2. 사용 위치

  • 저장 프로시저: 저장 프로시저는 일반적으로 CALL 문을 사용하여 호출됩니다. SQL 쿼리 내에서 직접 사용할 수는 없습니다.

예제:

CALL display_message;

위 예제는 display_message 저장 프로시저를 호출하는 방법입니다.

  • 함수: 함수는 SQL 쿼리 내에서 직접 사용할 수 있습니다. SELECT 문과 함께 호출하여 값을 반환받을 수 있습니다.

예제:

SELECT get_greeting FROM dual;

위 예제는 get_greeting 함수를 호출하여 결과를 SELECT 문으로 가져오는 방법입니다.

3. 목적

  • 저장 프로시저: 저장 프로시저는 주로 데이터베이스에서 여러 작업을 수행하거나 복잡한 로직을 처리하는 데 사용됩니다. 데이터 조작, 트랜잭션 처리 등 다양한 작업을 수행할 수 있습니다.

  • 함수: 함수는 특정 값을 계산하거나 변환하는 데 중점을 두며, 주로 데이터 변환, 계산 등의 용도로 사용됩니다.

4. 파라미터 사용

  • 저장 프로시저: 저장 프로시저는 IN, OUT, INOUT 파라미터를 모두 지원합니다. 이를 통해 입력 값을 받고, 결과 값을 반환하거나, 입력 값을 변경할 수 있습니다.

  • 함수: 함수는 IN 파라미터만 지원합니다. 값을 입력받아 계산 후 결과를 반환하지만, 입력 값을 변경할 수는 없습니다.

요약

  • 저장 프로시저: 반환 값 없음, CALL 문으로 호출, 데이터 조작 및 복잡한 로직 수행.
  • 함수: 항상 값 반환, SQL 쿼리 내에서 호출 가능, 특정 값 계산 및 변환.

이러한 차이점을 이해하면 상황에 맞게 저장 프로시저와 함수를 적절히 활용할 수 있습니다.

Similar Posts

DB 모델링 기초

DB 모델링 기초: ERD 작성법과 정규화 이해하기

Byleader.bettermind@gmail.com

DB 모델링 기초에서는 ERD(Entity-Relationship Diagram) 작성법을 다룹니다. 엔터티, 속성, 관계의 개념을 설명하며, 1:1 및 1:N 관계를 이해합니다. 또한, 데이터베이스 설계에서 중요한 정규화와 비정규화의 원리를 소개하여 효율적인 데이터 구조를 구축하는 방법을 배웁니다.

DDL (데이터 정의어)

DDL을 활용한 데이터베이스 객체 정의 및 관리 방법

Byleader.bettermind@gmail.com

DDL(데이터 정의어)은 데이터베이스 객체를 정의하고 관리하는 SQL 명령어입니다. 주요 명령어로는 CREATE(테이블 생성), ALTER(테이블 수정), DROP(테이블 삭제)가 있습니다. 또한, PRIMARY KEY와 FOREIGN KEY 설정, INDEX 생성 및 활용, 스키마 이해를 통해 데이터 구조를 효율적으로 관리할 수 있습니다.

CASE 문과 조건부 로직

SQL CASE 문과 DECODE의 차이점 및 조건부 로직 구현 방법

Byleader.bettermind@gmail.com

CASE 문은 SQL에서 조건부 로직을 구현하는 데 사용되며, DECODE와는 다르게 다중 조건을 처리할 수 있습니다. 이를 통해 특정 조건에 따라 데이터를 변환하거나 값을 할당할 수 있으며, IF-THEN-ELSE 구조를 SQL 쿼리 내에서 쉽게 구현할 수 있습니다.

트랜잭션 관리 (TCL)

트랜잭션 관리와 ACID 특성: 데이터베이스 일관성 유지하기

Byleader.bettermind@gmail.com

트랜잭션 관리(TCL)는 데이터베이스에서 데이터의 일관성을 유지하는 중요한 개념입니다. COMMIT으로 변경 사항을 확정하고, ROLLBACK으로 취소할 수 있습니다. SAVEPOINT를 사용해 중간 지점을 설정할 수 있으며, 트랜잭션의 ACID 특성과 격리 수준, 잠금 메커니즘도 이해해야 합니다.

문자열 함수

문자열 함수 완벽 가이드: SUBSTR, LENGTH, REPLACE, CONCAT 사용법 및 주의사항

Byleader.bettermind@gmail.com

문자열 함수에 대한 이 글에서는 SUBSTR, LENGTH, REPLACE, CONCAT의 사용법을 설명합니다. 문자열 검색과 변환 방법을 다루며, 문자열 처리 시 주의해야 할 사항도 포함됩니다. 초급자들이 쉽게 이해하고 실습할 수 있도록 구성되었습니다.