SQL Views and Stored Procedures

Introduction

SQL Views and Stored Procedures are powerful features in relational databases that help in organizing and optimizing database queries and operations. Views provide a way to save complex queries as virtual tables, while stored procedures allow users to encapsulate SQL logic into reusable blocks of code.

SQL Views

A View is a virtual table based on a SELECT query. It does not store data itself but provides an abstraction layer over database tables. Views are useful for simplifying complex queries, enhancing security, and ensuring data consistency.

Creating a View

A view is created using the CREATE VIEW statement.

CREATE VIEW EmployeeView AS
SELECT id, name, department, salary
FROM Employees
WHERE department = 'IT';

Selecting Data from a View

Once a view is created, it can be used like a table:

SELECT * FROM EmployeeView;

Updating a View

Views can be modified using CREATE OR REPLACE VIEW:

CREATE OR REPLACE VIEW EmployeeView AS
SELECT id, name, department, salary
FROM Employees
WHERE department IN ('IT', 'HR');

Dropping a View

To delete a view:

DROP VIEW EmployeeView;

Stored Procedures

A Stored Procedure is a precompiled collection of SQL statements that can be executed as a unit. Stored procedures improve performance, reduce network traffic, and enhance security.

Creating a Stored Procedure

A stored procedure is created using the CREATE PROCEDURE statement.

DELIMITER //
CREATE PROCEDURE GetEmployeesByDept(IN dept_name VARCHAR(50))
BEGIN
    SELECT * FROM Employees WHERE department = dept_name;
END //
DELIMITER ;

Executing a Stored Procedure

To call a stored procedure:

CALL GetEmployeesByDept('IT');

Stored Procedure with Output Parameter

Stored procedures can also return values using OUT parameters.

DELIMITER //
CREATE PROCEDURE GetTotalEmployees(OUT total INT)
BEGIN
    SELECT COUNT(*) INTO total FROM Employees;
END //
DELIMITER ;

To call it and get the output:

CALL GetTotalEmployees(@employee_count);
SELECT @employee_count;

Dropping a Stored Procedure

To delete a stored procedure:

DROP PROCEDURE GetEmployeesByDept;

Conclusion

SQL Views and Stored Procedures help in improving efficiency, security, and maintainability of databases. Views provide an abstraction over tables, making complex queries easier, while stored procedures allow executing predefined SQL logic efficiently. These features are widely used in large-scale applications to streamline database operations.