집 > 기사 > 데이터 베이스 > oracle 저장 프로시저는 결과 세트를 반환합니다.

oracle 저장 프로시저는 결과 세트를 반환합니다.

王林원래의: 2023-05-08 11:19:073165검색

Oracle 데이터베이스에서 저장 프로시저는 입력 매개변수를 받아들이고 일련의 작업을 수행하여 최종적으로 결과를 반환하는 컴파일되고 재사용 가능한 코드 블록입니다. 결과는 스칼라 값, 임시 테이블이나 커서에 저장된 결과 집합 또는 OUT 매개 변수를 통해 호출자에게 전달된 값일 수 있습니다.

일상 작업에서는 일부 일괄 작업, 장기 실행 작업 또는 복잡한 데이터 처리 논리를 완료하기 위해 저장 프로시저를 작성해야 하는 경우가 많습니다. 그러나 저장 프로시저에서 결과 집합을 반환해야 할 때 종종 몇 가지 문제에 직면하게 됩니다. 결과 집합을 어떻게 출력합니까? 출력 결과 세트의 형식은 무엇입니까? 여러 결과 세트를 처리하는 방법은 무엇입니까?

이러한 문제에 대응하여 이 기사에서는 Oracle 저장 프로시저에서 결과 집합을 반환하는 방법을 소개하고 독자의 이해를 돕기 위해 몇 가지 샘플 코드를 제공합니다.

커서를 사용하여 결과 집합 반환

Oracle 저장 프로시저에서는 커서를 사용하여 결과 집합을 반환할 수 있습니다. 구체적으로 REF CURSOR 유형의 변수를 정의한 다음 OPEN-FETCH-CLOSE 작업을 통해 커서에 데이터를 채우고 마지막으로 OUT 매개 변수로 호출자에게 커서를 반환해야 합니다.

다음은 커서를 사용하여 직원 테이블의 모든 레코드를 반환하는 방법을 보여주는 간단한 샘플 코드입니다.

CREATE OR REPLACE PROCEDURE get_all_employees(cur OUT SYS_REFCURSOR)
AS
BEGIN
  OPEN cur FOR
    SELECT * FROM employees;
END;

위 코드에서는 get_all_employees라는 저장 프로시저를 정의했습니다. 반환된 결과 집합을 나타내는 SYS_REFCURSOR 유형의 OUT 매개변수 cur가 있습니다. 저장 프로시저에서는 OPEN cur FOR를 사용하여 SELECT 문 실행 결과로 커서를 채웁니다. 마지막으로 저장 프로시저가 끝나면 커서가 자동으로 닫힙니다. get_all_employees 的存储过程，它有一个 OUT 参数 cur，类型为 SYS_REFCURSOR，表示返回的结果集。在存储过程中，我们通过 OPEN cur FOR 来将 SELECT 语句执行结果填充到游标中。最后，在存储过程结束时，游标会自动关闭。

在调用存储过程时，我们需要先声明一个与游标类型相同的变量，并将它作为参数传递给存储过程，然后使用 FETCH 语句来从游标中读取数据行：

DECLARE
  emp_cur SYS_REFCURSOR;
  emp_rec employees%ROWTYPE;
BEGIN
  get_all_employees(emp_cur);
  LOOP
    FETCH emp_cur INTO emp_rec;
    EXIT WHEN emp_cur%NOTFOUND;
    -- 处理数据行
  END LOOP;
  CLOSE emp_cur;
END;

在上面的代码中，我们先声明了一个名为 emp_cur 的游标变量，然后调用 get_all_employees 存储过程，并将 emp_cur 作为参数传递进去。接下来，我们使用 LOOP 和 FETCH 语句来从游标中逐行读取数据，并在每个循环迭代中用 emp_rec 变量来存储当前行的数据。在读取完所有数据后，我们需要手动关闭游标，以释放资源。

需要注意的是，在以上的代码中，我们使用了 %ROWTYPE 来定义了一个类型为 employees 表的行类型变量 emp_rec。这样，在 FETCH 语句中，就无需手动为每个字段指定变量，而是可以将整个数据行读取到 emp_rec 变量中。这种方式可以使代码更加简洁和易读。

值得一提的是，在 Oracle 12c 中，我们还可以使用 FETCH BULK COLLECT INTO 语句来一次性将多行数据读取到 PL/SQL 表或者数组变量中，以提高代码效率。由于 BULK COLLECT 的使用较为复杂，本文不再赘述，读者可以自行搜索相关资料深入学习。

使用临时表返回结果集

除了游标外，我们还可以使用临时表来返回一个结果集。具体来说，我们可以在存储过程中创建一个临时表，并将数据填充到表中，最后将表名作为 OUT 参数返回给调用者。

以下是一个简单的示例代码，演示如何使用临时表返回员工表中的所有记录：

CREATE OR REPLACE PROCEDURE get_all_employees(tbl_name OUT VARCHAR2)
AS
BEGIN
  CREATE GLOBAL TEMPORARY TABLE temp_employees
  AS SELECT * FROM employees;
  
  tbl_name := 'temp_employees';
END;

在上面的代码中，我们先创建了一个名为 temp_employees 的全局临时表，在创建表的同时，将 employees 表中的所有记录填充到表中。接下来，我们将表名 "temp_employees" 通过 OUT 参数 tbl_name 返回给调用者。

在调用存储过程时，我们可以通过表名来访问临时表中的数据：

DECLARE
  tbl_name VARCHAR2(30);
BEGIN
  get_all_employees(tbl_name);
  SELECT * FROM TABLE(tbl_name);
END;

在上面的代码中，我们声明了一个名为 tbl_name 的变量，用来存储存储过程返回的表名。在调用存储过程 get_all_employees 时，tbl_name 将会被更新为 "temp_employees"。之后，我们可以通过 SELECT * FROM TABLE(tbl_name)

저장 프로시저를 호출할 때 먼저 커서와 동일한 유형의 변수를 선언하고 이를 저장 프로시저에 매개 변수로 전달한 다음 FETCH 문을 사용하여 커서에서 데이터 행을 읽어야 합니다.

CREATE OR REPLACE PROCEDURE get_employees_and_stats(cur OUT SYS_REFCURSOR, total_salary OUT NUMBER)
AS
BEGIN
  OPEN cur FOR SELECT * FROM employees;
  SELECT SUM(salary) INTO total_salary FROM employees;
END;

위 코드에서는 먼저 emp_cur라는 커서 변수를 선언한 다음 get_all_employees 저장 프로시저를 호출하고 emp_cur를 매개변수로 전달합니다. 다음으로 LOOP 및 FETCH 문을 사용하여 커서에서 행별로 데이터를 읽고 emp_rec 변수를 사용하여 각 루프 반복에서 현재 행의 데이터를 저장합니다. 모든 데이터를 읽은 후 리소스를 해제하려면 커서를 수동으로 닫아야 합니다.

위 코드에서는 %ROWTYPE을 사용하여 employees 테이블 코드>에 대한 행 유형 변수 emp_rec를 정의했다는 점에 유의하세요. 이렇게 하면 FETCH 문에서 각 필드에 대한 변수를 수동으로 지정하는 대신 전체 데이터 행을 <code>emp_rec 변수로 읽을 수 있습니다. 이 접근 방식을 사용하면 코드가 더 간결해지고 읽기 쉬워집니다.

Oracle 12c에서는 FETCH BULK COLLECT INTO 문을 사용하여 한 번에 여러 데이터 행을 PL/SQL 테이블 또는 배열 변수로 읽어 코드 효율성을 향상시킬 수 있다는 점을 언급할 가치가 있습니다. BULK COLLECT의 사용은 상대적으로 복잡하므로 이 기사에서는 여기서 자세히 다루지 않습니다. 독자는 심층 연구를 위해 관련 정보를 검색할 수 있습니다.

DECLARE
  emp_cur SYS_REFCURSOR;
  emp_rec employees%ROWTYPE;
  total_salary NUMBER;
BEGIN
  get_employees_and_stats(emp_cur, total_salary);
  LOOP
    FETCH emp_cur INTO emp_rec;
    EXIT WHEN emp_cur%NOTFOUND;
    -- 处理员工数据行
  END LOOP;
  -- 处理工资汇总数据（total_salary）
  CLOSE emp_cur;
END;

temp_employees

employees

tbl_name

"temp_employees"

tbl_name

get_all_employees

tbl_name

"temp_employees"

SELECT * FROM TABLE(tbl_name)

CREATE OR REPLACE PROCEDURE get_employees_and_stats(cur OUT SYS_REFCURSOR, total_salary OUT NUMBER)
AS
BEGIN
  OPEN cur FOR SELECT * FROM employees;
  SELECT SUM(salary) INTO total_salary FROM employees;
END;

在上面的代码中，我们定义了一个名为 get_employees_and_stats 的存储过程，它有两个 OUT 参数，分别是一个游标变量 cur 和一个标量变量 total_salary。在存储过程中，我们先通过 OPEN cur FOR 来填充游标变量 cur，并将其返回给调用者。接着，我们通过 SELECT SUM(salary) INTO total_salary FROM employees; 语句计算出员工表中工资的总和，并将结果设置为标量变量 total_salary，同样也将其返回给调用者。

在调用存储过程时，我们需要将两个 OUT 参数作为参数传递给存储过程，并用游标变量来访问查询结果：

DECLARE
  emp_cur SYS_REFCURSOR;
  emp_rec employees%ROWTYPE;
  total_salary NUMBER;
BEGIN
  get_employees_and_stats(emp_cur, total_salary);
  LOOP
    FETCH emp_cur INTO emp_rec;
    EXIT WHEN emp_cur%NOTFOUND;
    -- 处理员工数据行
  END LOOP;
  -- 处理工资汇总数据（total_salary）
  CLOSE emp_cur;
END;

在上面的代码中，我们声明了一个游标变量 emp_cur 和一个标量变量 total_salary，用来接收存储过程的返回值。在调用存储过程时，我们将这两个变量作为参数传递给存储过程，并通过 FETCH emp_cur INTO emp_rec 逐行读取结果集中的数据。在读取所有数据之后，我们通过标量变量 total_salary 处理工资汇总数据。最后，我们需要手动关闭游标 emp_cur 以释放资源。

需要注意的是，在返回多个结果集时，我们需要保证每个结果集在调用存储过程之前都执行完毕，否则可能会导致输出数据不完整或者部分数据丢失。因此，我们需要仔细设计存储过程的逻辑，保证数据的完整性和准确性。

总结

在 Oracle 数据库中，存储过程是一个强大的编程工具，可以帮助我们完成一些复杂的数据处理任务。在存储过程中，返回结果集是一个常见的需求，我们可以使用游标或者临时表来实现结果集的输出，也可以通过 OUT 参数来返回多个结果集。在编写存储过程时，我们需要深入理解数据库的工作方式和 PL/SQL 语法，以保证代码的正确性和性能。

위 내용은 oracle 저장 프로시저는 결과 세트를 반환합니다.의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!

성명：

이전 기사：oracle 저장 프로시저는 결과 세트를 반환합니다.다음 기사：oracle 저장 프로시저는 결과 세트를 반환합니다.