Oracle PL/SQL is a powerful database programming language used for creating stored procedures, functions, and triggers. One of the key features of PL/SQL is its ability to return multiple rows from a stored procedure. In this article, we will explore how to use this feature to retrieve data from an Oracle database.
To begin with, let's understand what a stored procedure is. A stored procedure is a set of SQL statements that are stored in the database and can be executed as a single unit. It can accept input parameters and return values, making it a useful tool for data manipulation and retrieval.
Now, let's assume we have a table called "employees" in our Oracle database, which stores information about the employees of a company. Our task is to create a stored procedure that will return the details of all the employees whose salary is above a certain threshold.
First, we need to create a type that will hold the employee's details. We will name it "emp_details" and define its structure as follows:
CREATE OR REPLACE TYPE emp_details AS OBJECT (
employee_id NUMBER,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
salary NUMBER
);
Next, we will create a table type that will hold multiple rows of the "emp_details" type. We will name it "emp_details_table" and define its structure as follows:
CREATE OR REPLACE TYPE emp_details_table AS TABLE OF emp_details;
Now, it's time to create the stored procedure. We will name it "get_employees" and define its parameters and return type as follows:
CREATE OR REPLACE PROCEDURE get_employees (
p_min_salary IN NUMBER,
p_emp_details OUT emp_details_table
) AS
BEGIN
SELECT emp_details(
employee_id,
first_name,
last_name,
salary
)
BULK COLLECT INTO p_emp_details
FROM employees
WHERE salary > p_min_salary;
END;
Let's break down the above code. The "get_employees" procedure takes in a minimum salary as input and returns a table of "emp_details" type as output. We use the "BULK COLLECT" clause to retrieve multiple rows from the query into the "p_emp_details" parameter.
Now, let's execute the stored procedure and see the results:
DECLARE
l_emp_details emp_details_table;
BEGIN
get_employees(50000, l_emp_details);
FOR i IN 1..l_emp_details.count LOOP
DBMS_OUTPUT.PUT_LINE(l_emp_details(i).employee_id || ' ' ||
l_emp_details(i).first_name || ' ' ||
l_emp_details(i).last_name || ' ' ||
l_emp_details(i).salary);
END LOOP;
END;
In the above code, we declare a variable "l_emp_details" of type "emp_details_table" and call the "get_employees" procedure with a minimum salary of 50000. Then, we use a loop to print the details of the employees returned by the stored procedure.
This is just a basic example of how to return multiple rows from a stored procedure in Oracle PL/SQL. You can modify the procedure according to your requirements and use it to retrieve data from your database.
In conclusion, Oracle PL/SQL's ability to return multiple rows from a stored procedure is a useful feature that can simplify the process of data retrieval. With a little bit of knowledge and practice, you can leverage this feature to create efficient and powerful applications.
