Stored programs are SQL statements that are stored in the server and can be executed by name. MySQL (and MariaDB) supports five types of stored programs:
Stored Procedures
A Stored Procedure is a routine that can accept multiple input and output parameters and can return result sets. A Stored Procedure can perform transactions, raise errors, or call other stored programs. Stored procedures are created with CREATE PROCEDURE and invoked with the CALL statement. See Create and Run Stored Procedures.
Stored Functions
A Stored Function is similar to the stored procedure, but it can only accept input parameters and return a single scalar value. A stored function can perform transactions, raise errors, or return result sets. Stored functions are created with CREATE FUNCTION and invoked by using their name like any other MySQL/MariaDB function in an expression.
Triggers
A Trigger invoked automatically when an event occurs on a table, such as an INSERT, UPDATE, or DELETE statement. A Trigger can access the old and new values of the affected row and can modify the new values before they are stored. Triggers cannot accept parameters or return values, but they can call other stored programs. Triggers are created with the CREATE TRIGGER statement.
Events
An event is scheduled to run at a specific time or at regular intervals. Events can perform any SQL statement, such as deleting old data or optimizing tables. Events are created with the CREATE EVENT statement.
Views
A view acts as a virtual table that is defined by a query and can be used like a regular table. Views can simplify complex queries, provide data security, or hide the underlying table structure. Views are created with the CREATE VIEW statement.
Advantages of Using Stored Programs
Stored programs offer several advantages:
- Enhanced Database Security: Stored programs can contribute to a more secure database environment by managing and restricting access to sensitive data.
- Abstraction of Data Access Routines: They provide a mechanism to abstract data access routines, enhancing code maintainability as underlying data structures evolve over time.
- Reduced Network Traffic: Stored programs operate on data within the server itself, reducing the need to transfer data across the network, thereby improving efficiency.
- Reusability Across Applications: Stored programs can be used to implement common routines accessible from multiple applications, even if those applications use different frameworks or technologies. This promotes code reuse and consistency.
- Isolation of Database-Centric Logic: They enable the isolation of database-centric logic, allowing programmers with specialized database experience to manage and optimize these critical components separately.
- Improved Application Portability: In certain scenarios, the use of stored programs can enhance the portability of your application, making it more adaptable across different database systems.
`