Oracle에서 저장 프로시저를 생성하고 실행하는 방법의 예

Oracle은 저장 프로시저를 비롯한 다양한 고급 기능을 갖춘 매우 강력한 데이터베이스 관리 시스템입니다. 저장 프로시저는 나중에 호출할 때 사용할 수 있도록 데이터베이스에 저장할 수 있는 데이터베이스 작업을 위해 미리 정의된 SQL 문 집합입니다.

Oracle에서는 저장 프로시저가 SQL과 프로그래밍을 결합한 언어인 PL/SQL로 작성됩니다. PL/SQL은 강력한 데이터 조작 기능과 프로세스 제어 기능을 갖추고 있으며 효율적인 저장 프로시저를 쉽게 작성할 수 있습니다.

저장 프로시저의 이점

저장 프로시저의 주요 이점은 데이터베이스의 실행 효율성을 높이고 네트워크 통신 오버헤드를 줄일 수 있다는 것입니다. 저장 프로시저가 미리 컴파일되고 최적화되었기 때문에 실행 중에 반복적으로 구문 분석하고 최적화할 필요가 없으며 실행을 위해 직접 호출할 수 있습니다. 또한 저장 프로시저에서는 매개변수를 통해 동적 작업을 구현할 수도 있으므로 코드가 단순화될 뿐만 아니라 SQL 삽입과 같은 위험도 방지됩니다.

저장 프로시저 생성 및 실행

다음은 Oracle에서 저장 프로시저를 생성하고 실행하는 방법을 설명합니다.

저장 프로시저 만들기

Oracle에서 저장 프로시저를 만들려면 CREATE PROCEDURE 문을 사용해야 합니다. 구문은 다음과 같습니다.

CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name [IN | OUT | IN OUT] parameter_type [, ...])]
[IS | AS]
BEGIN
      pl/sql_code_block;
END [procedure_name];

그 중:

• CREATE PROCEDURE: 저장 프로시저를 만드는 문입니다.
• OR REPLACE: 선택적 매개변수입니다. 이 매개변수를 지정하면 생성된 저장 프로시저가 이미 존재하는 경우 교체된다는 의미입니다.
• procedure_name: 저장 프로시저의 이름입니다.
• parameter_name: 저장 프로시저의 입력 및 출력을 지정하는 데 사용되는 선택적 입력 및/또는 출력 매개 변수입니다.
• parameter_type: 매개변수 유형으로, VARCHAR2, NUMBER와 같은 데이터 유형 또는 SYS_REFCURSOR와 같은 커서 유형일 수 있습니다.
• IS | AS: 저장 프로시저의 언어 유형을 지정하는 데 사용되는 선택적 매개변수입니다. IS는 시작(PL/SQL 블록)을 나타내고 AS는 끝(PL/SQL 블록)을 나타냅니다.
• pl/sql_code_block: 저장 프로시저의 특정 논리 구현이 포함된 PL/SQL 코드 블록입니다.

다음 예제 코드는 두 개의 매개 변수를 허용하고 해당 합계를 출력하는 간단한 저장 프로시저를 만드는 방법을 보여줍니다.

CREATE OR REPLACE PROCEDURE add_nums(
    num1 IN NUMBER,
    num2 IN NUMBER,
    sum OUT NUMBER
)
IS
BEGIN
    sum := num1 + num2;
END add_nums;

저장 프로시저 실행

Oracle에서 저장 프로시저를 실행하려면 EXECUTE 또는 EXECUTE IMMEDIATE 문을 사용해야 합니다. . 예를 들어 위의 샘플 프로그램을 실행하려면 다음 명령문을 사용할 수 있습니다.

DECLARE
    result NUMBER;
BEGIN
    add_nums(10, 20, result);
    DBMS_OUTPUT.PUT_LINE('The sum is: ' || result);
END;

여기서는 DECLARE 문을 사용하여 사용해야 할 변수 결과를 선언하고 add_nums 저장 프로시저를 호출하여 그 결과를 화면에 출력합니다. .

매개변수 유형

저장 프로시저에서 매개변수는 입력 매개변수, 출력 매개변수 또는 양방향 매개변수일 수 있습니다.

• 입력 매개변수: 저장 프로시저의 입력을 지정합니다.
• 출력 매개변수: 저장 프로시저의 출력을 지정합니다.
• 양방향 매개변수: 입력 또는 출력이 가능합니다.

매개변수 유형 선언 방법은 다음과 같습니다.

(param_name [IN | OUT | IN OUT] param_type [, ...])

이 선언에서 [IN | OUT | IN OUT]은 매개변수 유형을 지정하는 데 사용되는 선택적 매개변수입니다. 매개변수 유형을 지정하지 않으면 기본적으로 IN 유형, 즉 입력 매개변수가 사용됩니다.

샘플 코드:

CREATE OR REPLACE PROCEDURE my_proc (
    num IN NUMBER,
    str IN OUT VARCHAR2,
    cur OUT SYS_REFCURSOR
)
IS
BEGIN
    -- 逻辑实现
END my_proc;

위 코드에서는 세 개의 매개변수를 사용하여 저장 프로시저 my_proc를 선언했습니다. 첫 번째 매개변수 num은 입력 매개변수이고 두 번째 매개변수 str은 양방향 매개변수이며 세 번째 매개변수 cur는 출력 매개변수입니다.

레코드 세트 처리

저장 프로시저를 사용하여 데이터를 연산할 때 쿼리 결과 목록을 반환해야 하는 경우가 종종 있습니다. Oracle은 커서와 PL/SQL 테이블이라는 두 가지 유형의 레코드 세트를 제공합니다.

Cursor

커서는 쿼리 결과를 탐색할 수 있는 결과 집합을 반환하는 데이터 구조입니다. 커서는 명시적이거나 암시적일 수 있습니다. 명시적 커서는 커서 변수를 선언해야 하며 코드에서 이를 열고 닫는 것은 Oracle에서 자동으로 생성되고 관리됩니다.

다음은 커서 사용 방법을 보여주는 저장 프로시저입니다.

CREATE OR REPLACE PROCEDURE get_employee(
    id_list IN VARCHAR2,
    emp_cur OUT SYS_REFCURSOR
)
IS
BEGIN
    OPEN emp_cur FOR 'SELECT * FROM employees WHERE id IN (' || id_list || ')';
END get_employee;

이 예에서는 쉼표로 구분된 직원 ID 목록을 입력 매개변수로 받아들이고 커서 emp_cur를 반환하는 두 개의 매개변수를 사용하여 저장 프로시저 get_employee를 선언합니다. 선택한 직원 정보가 포함되어 있습니다.

PL/SQL 테이블

PL/SQL 테이블은 일련의 값을 저장할 수 있는 배열과 유사한 데이터 구조입니다. PL/SQL 테이블에는 일련의 데이터를 저장 프로시저에 전달하는 등 저장 프로시저에 많은 실제 응용 프로그램이 있습니다.

Oracle에서는 다음 코드와 같은 저장 프로시저에서 PL/SQL 테이블을 선언하고 사용할 수 있습니다.

CREATE OR REPLACE PACKAGE my_package
IS
    TYPE num_list IS TABLE OF NUMBER INDEX BY PLS_INTEGER;

    PROCEDURE sum_nums(nums IN num_list, sum OUT NUMBER);
END my_package;

CREATE OR REPLACE PACKAGE BODY my_package
IS
    PROCEDURE sum_nums(nums IN num_list, sum OUT NUMBER)
    IS
        total NUMBER := 0;
    BEGIN
        FOR indx IN 1 .. nums.COUNT LOOP
            total := total + nums(indx);
        END LOOP;
        sum := total;
    END sum_nums;
END my_package;

여기에서는 num_list라는 PL/SQL을 선언하는 my_package라는 패키지를 만들었습니다. 해당 유형을 사용하는 저장 프로시저 sum_nums입니다. sum_nums는 num_list 유형의 인수를 받아들이고 해당 합계를 계산합니다.

결론

오라클에서 저장 프로시저는 효율적인 실행 기능과 역동성을 갖춘 중요한 도구 중 하나입니다. 또한 저장 프로시저를 사용하여 단일 SQL 문을 실행하는 대신 일부 비즈니스 논리를 실행할 수 있으므로 재사용성과 유지 관리성이 향상됩니다. 데이터베이스에 저장되고 여러 애플리케이션이나 프로세스에서 공유 및 액세스될 수 있기 때문입니다. 저장 프로시저를 사용하면 많은 이점이 있어 짧은 글로 모두 다루기는 어렵지만, 깊이 있게 이해하고 적용한다면 실제 업무에서 많은 이점을 얻을 수 있을 것이라 믿습니다.

위 내용은 Oracle에서 저장 프로시저를 생성하고 실행하는 방법의 예의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!