SQL Procedure

What is a Procedure?

• A Procedure is a collection of SQL statements stored in the database. It performs one or more database operations whenever it is executed.

Why Do We Need a Stored Procedure?

Suppose every day you need to:

• Insert new students

• Update marks

• Display reports

Instead of writing many SQL statements every day, you can save them in one stored procedure and execute it whenever needed.

How Does a Procedure Work?

User
  |
CALL Procedure
  |
Stored Procedure
  |
Runs Multiple SQL Statements
  |
Returns Result

Syntax (PostgreSQL)

CREATE PROCEDURE procedure_name()
LANGUAGE SQL
AS $$
SQL Statements;
$$;

Example

CREATE PROCEDURE ShowStudents()
LANGUAGE SQL
AS $$
SELECT * FROM Student;
$$;

Execute

CALL ShowStudents();

Why Use a Procedure?

• Execute multiple SQL statements together

• Reduce repeated SQL code

• Improve performance

• Increase security

• Automate database tasks

Advantages

• Saves SQL code

• Faster execution

• Reusable

• Easy to maintain

• Improves security