How to Write a Simple SELECT Stored Procedure in PL/SQL?

In PL/SQL, stored procedures are powerful tools for encapsulating logic and executing complex queries. This article will guide you through the process of creating a simple SELECT stored procedure in PL/SQL.

In this article, we will learn about how to store procedures with the help of various methods and examples and so on.

How to Write a Simple SELECT Stored Procedure in PL/SQL?

When working with databases, it’s common to need to retrieve data from tables based on certain criteria. In PL/SQL, we can create a stored procedure to handle this task efficiently. By using a stored procedure, we can encapsulate the query logic and reuse it whenever needed. Below are the methods which help us to CREATE PROCEDURE or CREATE FUNCTION statements are as follows:

1. Using Basic SELECT Stored Procedure
2. Using OUT Parameters

To understand How to Write a simple SELECT Stored Procedure in PL/SQL we need a table on which we will perform various operations and queries. Here we will consider a table called Students which contains student_id, first_name, last_name, department, and age as Columns.

Query:

CREATE TABLE students (
    student_id INT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department VARCHAR(50),
    age INT
);
INSERT INTO students (student_id, first_name, last_name, department, age)
VALUES 
    (1, 'John', 'Doe', 'Computer Science', 20),
    (2, 'Jane', 'Smith', 'Mathematics', 22),
    (3, 'Michael', 'Johnson', 'Biology', 21),
    (4, 'Emily', 'Brown', 'Physics', 23),
    (5, 'David', 'Wilson', 'Chemistry', 20),
    (6, 'Sarah', 'Lee', 'Computer Science', 22);

After inserting data into the students table, The table looks:

Students Table

1. Using Basic SELECT Stored Procedure

A simple SELECT stored procedure in PL/SQL allows us to retrieve data from a database table. The basic syntax for creating a simple SELECT stored procedure is as follows:

Syntax:

CREATE OR REPLACE PROCEDURE procedure_name
AS
BEGIN
   SELECT column1, column2, ...
   FROM table_name;
END;
/

Explanation:

• CREATE OR REPLACE PROCEDURE : It is the statement used to create or replace a stored procedure.
• procedure_name : It is the unique name assigned to the stored procedure.
• AS : It marks the beginning of the procedural code block.
• BEGIN and END : It enclose the set of SQL and PL/SQL statements that define the procedure’s functionality.
• SELECT : This statement retrieves specific columns or all columns from the specified table_name.

To execute a simple stored procedure in SQL, we will follow the following syntax:

Syntax:

CALL procedure_name(parameter1, parameter2, ...);

Explanation:

• CALL: It is a keyword used to execute the stored procedure.
• procedure_name: Name of the stored procedure to be called.
• argument1, argument2, …: Arguments (if any) to be passed to the stored procedure. These can be values or variables required by the procedure.

In this example We want to create a simple select stored procedure that will select and display student records based on a specified department:

CREATE OR REPLACE PROCEDURE get_students_by_department
AS
BEGIN
    SELECT * 
    FROM students
    WHERE department = p_department;
END;
/
-- To call the above stored procedure 
CALL get_students_by_department('Computer Science');

Output:

Example 1 Output

Explanation: This stored procedure retrieves all columns (*) from the students table and displays student records based on a specified department. We can execute this stored procedure by calling this to see all the records in the students table.

When we call this stored procedure, it will fetch all records from the students table and display student records for those who are from the ‘Computer Science’ department. We can observe that we get the all records for those who are from the ‘Computer Science’ department from the students table .

2. Using OUT Parameters

Sometimes, we need to customize our queries to fetch specific data based on certain conditions. This is where OUT parameters come in handy. They allow us to tailor our stored procedures to return desired results. OUT parameters in PL/SQL stored procedures serve as conduits for returning data to the caller.

While IN parameters provide inputs to the procedure, OUT parameters facilitate the output of computed or retrieved data. This mechanism enhances the flexibility of stored procedures, empowering them to not only execute tasks but also share results with the invoking code.

Example 1: We’ll define the get_student_details stored procedure with appropriate IN and OUT parameters (for Students table):

CREATE OR REPLACE PROCEDURE get_student_details (
    student_id_in IN INT,
    first_name_out OUT VARCHAR2,
    last_name_out OUT VARCHAR2,
    department_out OUT VARCHAR2
)
AS
BEGIN
    SELECT first_name, last_name, department
    INTO first_name_out, last_name_out, department_out
    FROM students
    WHERE student_id = student_id_in;
END;
/

Executing the Procedure and Retrieving the Result Set:

Now, let’s execute the get_student_details stored procedure to retrieve the details of a specific student:

DECLARE
    student_id_input INT := 3; -- Provide the student ID for which details are needed
    first_name_output VARCHAR2(50);
    last_name_output VARCHAR2(50);
    department_output VARCHAR2(50);
BEGIN
    get_student_details(student_id_input, first_name_output, last_name_output, department_output);
    DBMS_OUTPUT.PUT_LINE('Student Details:');
    DBMS_OUTPUT.PUT_LINE('First Name: ' || first_name_output);
    DBMS_OUTPUT.PUT_LINE('Last Name: ' || last_name_output);
    DBMS_OUTPUT.PUT_LINE('Department: ' || department_output);
END;
/

Output:

Example 2 output

Explanation: The output of executing the PL/SQL block for a specific student ID, which is 3 in our example . The output includes the student’s details, such as first name, last name, and department.

Conclusion

Overall, the simple select stored procedure are used to efficiently retrieves specific data, ensuring targeted information access and enhancing workflow efficiency. we should also note that stored procedures offer a convenient way to encapsulate and execute SQL queries within the PL/SQL environment. By following the above examples and syntax, we can effectively retrieve data from database tables using stored procedures.