Procedural SQL Reigns Supreme: Why It's the go-to Language for MariaDB Stored Procedures

• Portability: Procedural SQL is the standard language for stored procedures across many database systems, including MariaDB and MySQL. This makes your code more portable if you need to switch platforms in the future.
• SQL statements: The core of a stored procedure is made up of SQL statements like SELECT, INSERT, UPDATE, and DELETE. These statements manipulate data within the database.
• Control flow structures: Procedural SQL allows for control flow structures like IF, ELSE, WHILE, and LOOP statements. These help define the logic and execution order of your procedure.
• Variables: You can declare and use variables to store temporary data within the procedure.

While Procedural SQL is the main language, there's an additional point to consider:

• Limited PL/SQL compatibility: MariaDB, with the SQL_MODE=ORACLE setting, offers some compatibility with Oracle's PL/SQL language. This allows you to use a subset of PL/SQL features within your stored procedures. However, for broader compatibility and portability, sticking to Procedural SQL is recommended.

This procedure finds all customers from California:

CREATE PROCEDURE GetCaliforniaCustomers()
BEGIN
  SELECT * FROM Customers WHERE State = 'CA';
END;

Procedure with Input Parameter:

This procedure takes a customer ID as input and displays their details:

CREATE PROCEDURE GetCustomerDetails(IN customerID INT)
BEGIN
  SELECT * FROM Customers WHERE CustomerID = customerID;
END;

This procedure calculates the total number of customers and stores it in an output parameter:

CREATE PROCEDURE GetCustomerCount(OUT total INT)
BEGIN
  SET total = (SELECT COUNT(*) FROM Customers);
END;

Procedure with Conditional Logic:

This procedure updates a customer's discount based on their total purchase amount:

CREATE PROCEDURE UpdateCustomerDiscount(IN customerID INT, IN purchaseAmount DECIMAL(10,2))
BEGIN
  DECLARE newDiscount DECIMAL(5,2);
  IF purchaseAmount > 1000 THEN
    SET newDiscount = 0.10;
  ELSE
    SET newDiscount = 0.05;
  END IF;
  
  UPDATE Customers SET Discount = newDiscount WHERE CustomerID = customerID;
END;

Remember to call these procedures using the CALL statement followed by the procedure name and any required parameters.

1. Prepared Statements:

-- Define prepared statement
PREPARE GetCustomerByName (SQL, IN name VARCHAR(255));

SET @name = 'John Doe';

-- Execute with parameter
EXECUTE GetCustomerByName USING 'SELECT * FROM Customers WHERE Name = ?', @name;

DEALLOCATE GetCustomerByName;

1. User-Defined Functions (UDFs):

Choosing the Right Method:

• Procedural SQL is generally the most straightforward option for most database operations.
• Prepared statements offer performance and security benefits for frequently executed queries with dynamic data.
• UDFs provide more flexibility for complex logic but require additional development effort and potential platform dependencies.