Procedural SQL

Procedural SQL is an extension to the standard SQL (Structured Query Language) that offers capabilities similar to procedural programming languages. The main goal of procedural SQL is to provide the ability to perform more complex operations beyond simple data manipulation and queries, like conditional programming, looping, error handling, etc. This is accomplished through constructs such as loops, conditional statements, and variables.

The most popular forms of procedural SQL are PL/SQL and T-SQL:

PL/SQL (Procedural Language/SQL): This is Oracle's procedural extension for SQL. PL/SQL allows you to encapsulate blocks of SQL statements within a procedural language structure. This allows developers to create more powerful applications by embedding SQL queries within procedural logic. Here's a basic example of PL/SQL code:

BEGIN
   DECLARE x NUMBER;
   BEGIN
      SELECT salary INTO x FROM employees WHERE employee_id = 100;
      IF x > 5000 THEN
         UPDATE employees SET salary = salary * 1.1 WHERE employee_id = 100;
      END IF;
   END;
END;

In this example, a variable x is declared and used to store the salary of an employee. Then, a conditional IF statement is used to check if x is greater than 5000. If it is, an UPDATE statement is executed to give the employee a 10% raise.

T-SQL (Transact-SQL): This is Microsoft's and Sybase's procedural extension for SQL. T-SQL extends the capabilities of SQL with procedural programming, local variables, various support functions for string processing, date processing, mathematics, etc. Here's a simple example:

DECLARE @x INT;
SET @x = (SELECT salary FROM employees WHERE employee_id = 100);
IF @x > 5000
BEGIN
   UPDATE employees SET salary = salary * 1.1 WHERE employee_id = 100;
END

In this T-SQL example, similar to the PL/SQL example, the salary of an employee is stored in a variable and then used in a conditional statement to possibly give the employee a raise.

Both PL/SQL and T-SQL also support the creation of stored procedures and functions, which are reusable pieces of code that can perform actions in the database. These can be called from other parts of your code or even triggered by certain events in the database.

Using procedural SQL can enhance the efficiency and execution speed of your database operations. However, keep in mind that procedural SQL syntax and capabilities can vary significantly across different database systems, unlike standard SQL, which aims to be consistent across different systems.

MySQL supports procedural SQL through its own procedural language extension, MySQL's Stored Procedure Language. This procedural language extension allows developers to write procedural code, such as loops, conditionals, and declare variables directly within MySQL.

MySQL's Stored Procedure Language can be used to create stored procedures and functions in a MySQL database. Stored procedures and functions are segments of code that are stored in the database for reuse.

Here's a simple example of a stored procedure in MySQL:

DELIMITER //
CREATE PROCEDURE IncreaseSalary(IN employee_id INT)
BEGIN
  DECLARE employee_salary DECIMAL(10, 2);
  SELECT salary INTO employee_salary FROM employees WHERE id = employee_id;
  IF employee_salary > 5000 THEN
    UPDATE employees SET salary = salary * 1.1 WHERE id = employee_id;
  END IF;
END//
DELIMITER ;

In this example, a stored procedure named IncreaseSalary is defined. It accepts one parameter, employee_id. Within the procedure, the salary of the specified employee is retrieved and stored in the employee_salary variable. If employee_salary is greater than 5000, the employee's salary is increased by 10%.

Once this procedure is created, you can call it with the CALL statement like so:

This would execute the IncreaseSalary procedure for the employee with an id of 100.

This ability to create stored procedures in MySQL enhances the power and flexibility of SQL and allows developers to encapsulate complex operations in a single, reusable component.