Functions, Procedures, Cursors, and Triggers in SQL

Full Guide: Functions, Procedures, Cursors, and Triggers in SQL

In relational database management systems (RDBMS), various components such as functions, procedures, cursors, and triggers play essential roles in enhancing the flexibility and functionality of database systems. They allow developers to implement custom business logic, automate repetitive tasks, and manage data more effectively.

This guide will provide a comprehensive explanation of these components, along with example code snippets for each one.

---

1. Functions in SQL

A function in SQL is a stored program that can accept inputs, perform operations, and return a value. It is similar to a procedure, but a function must return a value, and it can be used within queries like any other expression.

Key Points:

• Functions can be invoked from queries.
• Functions return a single value.
• They can take input parameters.
• They are typically used for computations, transformations, and data retrieval.

Example of Function (SQL Server Syntax)

Let’s write a simple function that calculates the square of a number.

CREATE FUNCTION dbo.SquareNumber (@Number INT)
RETURNS INT
AS
BEGIN
    RETURN @Number * @Number
END

Usage:

SELECT dbo.SquareNumber(4); -- Output: 16

This function takes an integer as input, calculates its square, and returns the result.

---

2. Procedures in SQL

A procedure (also called a stored procedure) is a set of SQL statements that can be executed as a unit. Procedures can take parameters, perform operations like insert, update, delete, and select, and return multiple results (but not directly a single value like functions).

Key Points:

• Procedures do not necessarily return a value, but they may return multiple result sets.
• They can perform multiple operations.
• Procedures can be invoked explicitly using the EXEC command.

Example of Procedure (SQL Server Syntax)

Let’s write a procedure to update the salary of an employee.

CREATE PROCEDURE dbo.UpdateSalary 
    @EmployeeID INT, 
    @NewSalary DECIMAL
AS
BEGIN
    UPDATE Employees
    SET Salary = @NewSalary
    WHERE EmployeeID = @EmployeeID;
END

Usage:

EXEC dbo.UpdateSalary @EmployeeID = 101, @NewSalary = 75000;

This procedure takes an EmployeeID and a NewSalary as inputs, updates the employee's salary, and does not return any value.

---

3. Cursors in SQL

A cursor in SQL is a database object that allows you to retrieve and process each row returned by a query one at a time. This is particularly useful when you need to perform row-by-row operations, such as updates or deletes, that are not easily handled in a single set-based operation.

Key Points:

• Cursors can be used to iterate over query result sets.
• They are typically used when set-based operations are not sufficient.
• Cursors can be classified into different types (static, dynamic, forward-only, etc.).

Example of Cursor (SQL Server Syntax)

Let’s write an example using a cursor to update the salary of all employees by 10%.

CREATE FUNCTION dbo.SquareNumber (@Number INT)
RETURNS INT
AS
BEGIN
    RETURN @Number * @Number
END

Explanation:

1. We declare a cursor SalaryCursor that selects the EmployeeID and Salary from the Employees table.
2. We open the cursor and fetch the first row into variables.
3. Inside the WHILE loop, we update the salary for each employee by multiplying it by 1.1 (10% increase).
4. After processing all rows, we close and deallocate the cursor.

---

4. Triggers in SQL

A trigger is a special kind of stored procedure that automatically executes (or "fires") when specific database events occur, such as INSERT, UPDATE, or DELETE on a table. Triggers are useful for enforcing business rules, maintaining data integrity, or automatically updating related tables when changes occur.

Key Points:

• Triggers can be BEFORE or AFTER the event (insert, update, delete).
• Triggers can fire once per statement or once per row (depending on the type).
• They are often used to enforce integrity rules or track changes.

Example of Trigger (SQL Server Syntax)

Let’s create a trigger that automatically updates the LastModified column whenever an employee’s salary is updated.

SELECT dbo.SquareNumber(4); -- Output: 16

Explanation:

1. The trigger trg_UpdateSalary fires after an UPDATE operation on the Employees table.
2. Inside the trigger, we check if the Salary column was updated using the UPDATE() function.
3. If the Salary was updated, we modify the LastModified column with the current date and time (GETDATE()).
4. The inserted table is a special table that contains the new values after the update operation, and we use it to update the LastModified field for the modified employee(s).

---

Summary of SQL Components

Component	Description	Example Use Case
Function	A stored program that returns a single value and can be used in queries.	Calculate the square of a number.
Procedure	A stored program that can perform multiple actions (insert, update, delete) but does not return a value.	Update an employee’s salary.
Cursor	A mechanism for iterating over a result set row-by-row, used for operations that cannot be easily expressed in set-based SQL.	Update all employees’ salaries by a fixed percentage.
Trigger	A stored program that automatically executes when specific database events (INSERT, UPDATE, DELETE) occur.	Automatically update a timestamp column when a record is modified.

---

Conclusion

• Functions and procedures are essential for modularizing business logic and reusable operations in the database. Functions are more focused on returning a value, whereas procedures can handle multiple tasks but do not return values directly.
• Cursors are used when you need to process data row-by-row, though set-based operations are generally more efficient.
• Triggers allow automatic responses to database events, ensuring data integrity and enforcing rules without requiring manual intervention.

Each of these components serves a unique purpose in making your database more flexible, maintainable, and efficient, especially in complex database environments.

The above is the detailed content of Functions, Procedures, Cursors, and Triggers in SQL. For more information, please follow other related articles on the PHP Chinese website!