oracle stored procedure returns result set

In Oracle Database, a stored procedure is a compiled, reusable block of code that accepts input parameters and performs a series of operations, ultimately returning a result. The result may be a scalar value, a result set stored in a temporary table or cursor, or a value passed to the caller via an OUT parameter.

In our daily work, we often need to write stored procedures to complete some batch operations, long-running tasks or complex data processing logic. However, when we need to return a result set in a stored procedure, we often encounter some problems: How to output the result set? What is the format of the output result set? How to handle multiple result sets?

In response to these problems, this article will introduce how to return result sets in Oracle stored procedures, and provide some sample code to help readers better understand.

1. Use a cursor to return a result set

In Oracle stored procedures, we can use a cursor to return a result set. Specifically, we need to define a variable of type REF CURSOR, then fill the data into the cursor through the OPEN-FETCH-CLOSE operation, and finally return the cursor to the caller as an OUT parameter.

The following is a simple sample code that demonstrates how to use a cursor to return all records in the employees table:

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

In the above code, we define a file called get_all_employees's stored procedure has an OUT parameter cur, of type SYS_REFCURSOR, which represents the returned result set. In the stored procedure, we use OPEN cur FOR to fill the cursor with the SELECT statement execution results. Finally, at the end of the stored procedure, the cursor is automatically closed.

When calling a stored procedure, we need to first declare a variable of the same type as the cursor, pass it as a parameter to the stored procedure, and then use the FETCH statement to read data rows from the cursor:

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;

In the above code, we first declare a cursor variable named emp_cur, then call the get_all_employees stored procedure, and use emp_cur as Parameters are passed in. Next, we use LOOP and FETCH statements to read data from the cursor row by row, and use the emp_rec variable to store the current row's data in each loop iteration. After reading all the data, we need to manually close the cursor to release resources.

It should be noted that in the above code, we use %ROWTYPE to define a row type variable emp_rec of the employees table . This way, in the FETCH statement, instead of manually specifying variables for each field, the entire row of data can be read into the emp_rec variable. This approach makes the code more concise and readable.

It is worth mentioning that in Oracle 12c, we can also use the FETCH BULK COLLECT INTO statement to read multiple rows of data into a PL/SQL table or array variable at one time to improve code efficiency. Since the use of BULK COLLECT is relatively complex, this article will not go into details here. Readers can search for relevant information for in-depth study.

2. Use a temporary table to return a result set

In addition to cursors, we can also use a temporary table to return a result set. Specifically, we can create a temporary table in the stored procedure, populate the table with data, and finally return the table name to the caller as the OUT parameter.

The following is a simple sample code that demonstrates how to use a temporary table to return all records in the employee table:

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;

In the above code, we first create a file named The global temporary table of temp_employees will populate all the records in the employees table into the table while creating the table. Next, we return the table name "temp_employees" to the caller via the OUT parameter tbl_name.

When calling a stored procedure, we can access the data in the temporary table through the table name:

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

In the above code, we declare a file named tbl_name variable is used to store the table name returned by the stored procedure. When the stored procedure get_all_employees is called, tbl_name will be updated to "temp_employees". After that, we can access the data in the temporary table through the SELECT * FROM TABLE(tbl_name) statement and display it in the client.

It should be noted that the life cycle of the global temporary table is session level, that is, the data in the table will be automatically deleted when the database session ends. This ensures that each session has its own temporary table and avoids data conflicts between different sessions.

3. Return multiple result sets

In some cases, we need to return multiple result sets in one stored procedure. For example, in a complex query, we need to return both the query results and some summary statistics. In Oracle stored procedures, we can use OUT parameters and cursors to achieve multiple result set output.

The following is a simple sample code that demonstrates how to return two result sets in a stored procedure:

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 语法，以保证代码的正确性和性能。

The above is the detailed content of oracle stored procedure returns result set. For more information, please follow other related articles on the PHP Chinese website!