Difference Between Stored Procedure and View

1. Overview

Stored procedures and views are essential components for database management and optimization in SQL. While they serve different purposes, they’re important for efficient data handling and retrieval.  Furthermore, understanding their differences, types, and use cases can significantly enhance database performance and usability.

In this tutorial, we’ll explore the definitions, types, and examples of stored procedures and views using the Baeldung University database schema. Finally, we’ll conclude with a comparative analysis to highlight their distinct features.

Notably, we perform all operations in the Postgres database management system (DBMS).

2. Stored Procedure

A stored procedure is a precompiled collection of one or more SQL statements stored in the database. In practice, these SQL statements can be executed multiple times. One unique characteristic of a stored procedure is that it can accept parameters, perform complex operations, and return results. Stored procedures are used to encapsulate business logic, automate repetitive tasks, and enhance performance. Therefore, they reduce the need for multiple round trips between the application and the database.

2.1. Types

There are different types of stored procedures:

• User-Defined Stored Procedure: this stored procedure is created by users to encapsulate business logic
• System Stored Procedure: it’s provided by the database system for administrative tasks
• Extended Stored Procedure: it allows external programs to interact with the database system

2.2. Example

To illustrate the use of a stored procedure, let’s use the Student table from the Baeldung University database schema. In particular, let’s create a stored procedure to update the GPA of a student based on their ID:

CREATE OR REPLACE PROCEDURE UpdateStudentGPA(
    IN student_id INT,
    IN new_gpa REAL
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE Student
    SET gpa = new_gpa
    WHERE id = student_id;
END;
$$;

In this query, the stored procedure performs several tasks. First, it accepts two input parameters (student_id and new_gpa). Also, it specifies the procedural language which is plpgsql. Then, it contains a block of code that updates the gpa field in the Student table where the id matches the provided student_id.

Next, we execute the stored procedure:

CALL UpdateStudentGPA(1001, 4.5);

This command updates the GPA of the student with ID 1001 to 4.5.

To verify that the stored procedure has been executed successfully, we query the Student table to check the updated GPA for the student with the ID 1001:

SELECT id, name, gpa 
FROM Student 
WHERE id = 1001;
+------+----------+-----+
| id   | name     | gpa |
|------+----------+-----|
| 1001 | John Liu | 4.5 |
+------+----------+-----+
SELECT 1
Time: 0.014s

The output confirms that the stored procedure has successfully updated the GPA of the student with ID 1001.

3. View

A view is a virtual table based on the result set of an SQL query. It doesn’t store data physically but provides a way to simplify complex queries and enhance security by restricting access to specific data. Furthermore, views are used to present data in a specific format, aggregate data, and enforce security policies.

3.1. Types

There are different types of views:

• Simple Views: they’re derived from a single table without any using functions or groupings
• Complex Views: they’re derived from multiple tables and can include functions and groupings
• Materialized Views: unlike regular views, they store the result set of a query and periodically refresh the data

3.2. Example

For illustration, let’s use the Student table in the Baeldung University database schema. In particular, let’s create a view that lists students with their graduation dates and GPAs:

CREATE VIEW StudentGraduationGPA AS
SELECT id, name, graduation_date, gpa
FROM Student
WHERE graduation_date IS NOT NULL;

The query creates a view called StudentGraduationGPA that selects the id, name, graduation_date, and gpa columns from the Student table where graduation_date is not null. Basically, this view retrieves information about students who have a specified graduation date along with their GPAs.

To verify its creation, we query the StudentGraduationGPA view:

SELECT * FROM StudentGraduationGPA;
+------+-----------------+-----------------+--------+
| id   | name            | graduation_date | gpa    |
|------+-----------------+-----------------+--------|
| 1003 | Rita Ora        | 2024-06-15      | 4.2    |
| 1007 | Philip Lose     | 2024-06-15      | 3.8    |
| 1010 | Samantha Prabhu | 2024-06-15      | 4.9    |
| 1610 | Ritu Raj        | 2025-06-15      | <null> |
...
SELECT 18
Time: 0.007s

The output confirms that the StudentGraduationGPA view has been created successfully and returns data correctly.

4. Differences

The key differences between a stored procedure and a view can be summarized as follows:

5. Conclusion

In this article, we’ve explored stored procedures and views. While stored procedures encapsulate business logic and automate repetitive tasks, views simplify complex queries and enhance security. Additionally, we’ve understood their differences and use cases. In particular, we’ve looked at examples of how to create and use stored procedures and views, showcasing their distinct functionalities and benefits.