oracle stored procedure returns result set

In Oracle database, stored procedure is a widely used data processing method. Although stored procedures can perform a variety of complex data operations, their most common use is to return query result sets. In this article, we will explore the method and implementation of Oracle stored procedures to return result sets.

1. Introduction to Oracle stored procedures

The stored procedure in Oracle database is a database object, which is a set of precompiled SQL statements written in PL/SQL language. Stored procedures can be regarded as programmed SQL statements, which are usually used to perform complex data operations, such as data calculations, data filtering, and logical processing.

Stored procedures can be used to process a variety of data, including querying result sets, modifying data, creating and deleting objects, and performing other data operations. The most commonly used stored procedure is used to query data, which can return a specific result set based on customized query conditions.

2. Methods for Oracle stored procedures to return result sets

Oracle database provides a variety of methods to return the result sets of stored procedures. The following are some of the more commonly used ones.

1. Use a cursor to return the result set

The cursor is a data structure used to process the query result set. It can save the query result set in memory so that the program can You can manipulate and process data in the result set. In a stored procedure, you use a cursor to save the query result set into the cursor and then return it to the cursor.

In Oracle stored procedures, the basic process of using a cursor to return a result set is: first define a cursor variable, then save the query result set to the cursor, and return the cursor variable as the stored procedure return value.

The following is an example stored procedure that uses a cursor to return a result set:

CREATE OR REPLACE PROCEDURE my_proc(p_recordset OUT SYS_REFCURSOR)
AS
BEGIN
  OPEN p_recordset FOR
    SELECT * FROM my_table;
END;

In the above example, we define a stored procedure named my_proc, and its return value is a cursor p_recordset . When the stored procedure is executed, the data in the my_table table is queried and the result set is saved in the cursor variable p_recordset.

2. Using table types to return result sets

Oracle database also provides another way to return result sets, that is, using table types. The table type is an Oracle object type used to define the table data structure. It can define multiple types of columns and data types, including strings, integers, dates, etc.

In the stored procedure, the query result set can be encapsulated into the table type using the table type, and then the table type is used as the return value of the stored procedure.

The following is an example stored procedure that returns a result set using a table type:

CREATE OR REPLACE TYPE my_type AS OBJECT (
  c1 INT,
  c2 VARCHAR2(10)
);

CREATE OR REPLACE TYPE my_table_type AS TABLE OF my_type;

CREATE OR REPLACE PROCEDURE my_proc(p_recordset OUT my_table_type)
AS
BEGIN
  SELECT my_type(my_table.col1, my_table.col2)
    BULK COLLECT INTO p_recordset
    FROM my_table;
END;

In the above example, we define two object types my_type and my_table_type, where my_type represents a row of data and my_table_type Represents the complete query result set. In the my_proc stored procedure, we encapsulate the query result set into a my_table_type table type object and use it as the return value of the stored procedure.

3. Implementation of Oracle stored procedure returning result set

Let's gradually implement a stored procedure using cursor and table type to return data in the my_table table. This stored procedure defines two parameters, namely the input parameter date_field and the output parameter p_recordset.

First, we need to create a my_table table and insert some test data to use as our example. The SQL statements to create and insert data are as follows:

CREATE TABLE my_table (
  col1 NUMBER,
  col2 VARCHAR2(10),
  date_field DATE
);

INSERT INTO my_table VALUES (1, 'A', TO_DATE('2022-01-01', 'YYYY-MM-DD'));
INSERT INTO my_table VALUES (2, 'B', TO_DATE('2022-02-01', 'YYYY-MM-DD'));
INSERT INTO my_table VALUES (3, 'C', TO_DATE('2022-03-01', 'YYYY-MM-DD'));

Next, we create a stored procedure named my_proc to query the data in the my_table table and save the query results to the cursor variable. In the stored procedure, we define a parameter date_field to limit the date range of the query result set.

The sample code of my_proc stored procedure that uses a cursor to return a result set is as follows:

CREATE OR REPLACE PROCEDURE my_proc(
  date_field IN DATE DEFAULT NULL,
  p_recordset OUT SYS_REFCURSOR
)
AS
  v_query_str VARCHAR2(1000);
BEGIN
  v_query_str := 'SELECT * FROM my_table WHERE 1=1';

  IF date_field IS NOT NULL THEN
    v_query_str := v_query_str || ' AND date_field >= :date_field ';
  END IF;

  OPEN p_recordset FOR v_query_str USING date_field;
END;

In the above code, we use a dynamic SQL statement v_query_str to dynamically construct a SQL query statement. This SQL statement contains a date range restriction, so we need to use a dynamic SQL statement to dynamically add date conditions in the WHERE clause.

The first line defines the basic framework of the query statement, including the query table name and the default WHERE clause. Line 4 determines whether the input parameter date_field is NULL. If it is not NULL, add date restrictions to the query statement.

The last line executes the query statement and uses the OPEN statement to save the result set into a cursor variable, and uses the cursor variable as the return value of the stored procedure.

Finally, we create a stored procedure that uses the table type to return the result set, which is also used to query the data in the my_table table and save the query results to the table.

The sample code of my_proc2 stored procedure that uses table type to return a result set is as follows:

CREATE OR REPLACE TYPE my_type AS OBJECT (
  col1 NUMBER,
  col2 VARCHAR2(10),
  date_field DATE
);

CREATE OR REPLACE TYPE my_table_type AS TABLE OF my_type;

CREATE OR REPLACE PROCEDURE my_proc2(
  date_field IN DATE DEFAULT NULL,
  p_recordset OUT my_table_type
)
AS
BEGIN
  SELECT my_type(col1, col2, date_field)
    BULK COLLECT INTO p_recordset
    FROM my_table
    WHERE date_field >= NVL(date_field, date_field);
END;

In the above code, we define two object types: the my_type object represents a data row, and the my_table_type type represents Query result set. In the my_proc2 stored procedure, we encapsulate the query data into the table type object of my_table_type, and use the BULK COLLECT INTO statement to save the query result set into the p_recordset parameter.

4. Summary

This article introduces the method of using stored procedures to return result sets in Oracle database, mainly including methods of using cursors and table types. Through practical examples, we learned how to write stored procedures that can return query result sets, and learned about the use of dynamic SQL statements.

In actual development, stored procedures are very useful data processing tools, with many advantages such as high efficiency, flexibility, and security. Learning how to write and use stored procedures is crucial to improving the data processing capabilities of Oracle database.

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!