PL/SQL vs MySQL Stored Programs

PL/SQL and MySQL Stored Programs Are Not the Same Language

PL/SQL is Oracle Database's procedural extension to SQL. MySQL has a different stored-program language used in stored procedures, stored functions, triggers and scheduled events. Both support variables, conditions, loops, cursors and error handling, but the grammar, database objects and runtime behavior differ.

This page compares Oracle PL/SQL with MySQL 8.4 stored programs. It does not imply that PL/SQL is a generic name for every database's procedural SQL.

PL/SQL vs MySQL: Practical Difference Matrix

Types must be mapped by range, precision, character semantics and nullability—not by similar-looking names alone.

Blocks, Declarations and Variables

An Oracle PL/SQL block has an optional declarative part, a required executable part and an optional exception-handling part:

DECLARE
  v_total NUMBER(10,2) := 0;
BEGIN
  SELECT COALESCE(SUM(total_amount), 0)
  INTO v_total
  FROM orders
  WHERE customer_id = 101;
  DBMS_OUTPUT.PUT_LINE(v_total);
EXCEPTION
  WHEN OTHERS THEN
    RAISE;
END;
/

In MySQL, declarations belong at the start of a BEGIN ... END compound block. The declaration order matters: local variables and conditions come before cursors, and cursors come before handlers.

DELIMITER //
CREATE PROCEDURE show_customer_total(IN p_customer_id INT)
BEGIN
  DECLARE v_total DECIMAL(12,2) DEFAULT 0;

  SELECT COALESCE(SUM(total_amount), 0)
    INTO v_total
  FROM orders
  WHERE customer_id = p_customer_id;

  SELECT v_total AS paid_total;
END //
DELIMITER ;

Procedures, Functions and Parameters

Both systems provide named procedures and functions, but their details differ. MySQL procedure parameters place the mode before the name: IN p_id INT, OUT p_total DECIMAL(12,2). MySQL function parameters are inputs and the function declares RETURNS plus a RETURN statement.

-- MySQL procedure call
CALL customer_summary(101, @paid_total);
SELECT @paid_total;

-- MySQL stored function call
SELECT service_fee(1500.00);

Oracle PL/SQL supports richer package-level overloading and package state. Do not assume every overloaded Oracle subprogram can retain the same name in MySQL. Choose explicit routine names and signatures, and move session state to transaction-scoped tables or the application where appropriate.

Security also needs a fresh decision. MySQL routines support SQL SECURITY DEFINER or INVOKER; Oracle uses definer's-rights or invoker's-rights behavior through AUTHID. Recreate least privilege rather than copying owners blindly.

Exception Handling, Cursors and Diagnostics

Oracle routes an exception to an EXCEPTION section. MySQL declares handlers inside the block:

DECLARE duplicate_key CONDITION FOR 1062;
DECLARE EXIT HANDLER FOR duplicate_key
BEGIN
  ROLLBACK;
  SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Order already exists';
END;

A MySQL CONTINUE handler continues execution; an EXIT handler leaves the compound block where it was declared. UNDO is not supported. Use GET DIAGNOSTICS when code needs structured condition details, and use RESIGNAL to preserve or augment the current error.

Both languages support explicit cursors, but syntax and attributes differ. MySQL cursors are asensitive, read-only and nonscrollable. A typical loop declares a CONTINUE HANDLER FOR NOT FOUND, then performs OPEN, FETCH and CLOSE. Set-based SQL should remain the first choice in both databases.

Packages and Oracle-Specific Features Need Redesign

Oracle packages group public declarations and private implementation, and can hold package variables and overloaded subprograms. MySQL 8.4 has no direct package specification/body object. A migration may use:

• consistent routine prefixes such as billing_create_invoice;
• a dedicated database schema only when its ownership and deployment model fit;
• application modules for orchestration, external services and state;
• tables for durable state, with explicit keys, locking and retention;
• views and routines for narrow, permission-controlled database interfaces.

Review Oracle features one by one: sequences, collections, records, %TYPE, %ROWTYPE, bulk collect, FORALL, autonomous transactions, ref cursors, database links, synonyms and Oracle built-ins do not have guaranteed one-line MySQL equivalents.

Worked Migration: Customer Spend Procedure

Suppose the requirement is: accept a customer ID, return paid-order count and total, and reject a missing ID. A MySQL implementation is:

DELIMITER //
CREATE PROCEDURE customer_spend(
  IN  p_customer_id INT,
  OUT p_order_count INT,
  OUT p_paid_total DECIMAL(12,2)
)
SQL SECURITY INVOKER
READS SQL DATA
BEGIN
  IF p_customer_id IS NULL THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'customer_id is required';
  END IF;

  SELECT COUNT(*), COALESCE(SUM(total_amount), 0)
    INTO p_order_count, p_paid_total
  FROM orders
  WHERE customer_id = p_customer_id
    AND status = 'PAID';
END //
DELIMITER ;

CALL customer_spend(101, @orders, @total);
SELECT @orders AS paid_orders, @total AS paid_total;

The migrated routine preserves the business contract, not Oracle punctuation. The query is set-based, NULL input is explicit, output types have defined precision, and the caller's security context is intentional.

Production Migration Checklist

1. Inventory every routine, package, trigger, job, dependency and caller.
2. Record inputs, outputs, side effects, errors and transaction ownership.
3. Map data types by range, precision, timezone, charset and collation.
4. Replace Oracle-only SQL and built-ins with tested MySQL equivalents.
5. Redesign packages, session state, autonomous work and bulk operations.
6. Convert exception paths to named conditions, handlers and SIGNAL.
7. Prefer set-based SQL; benchmark any cursor or per-row function.
8. Rebuild privileges with a durable least-privileged definer or invoker.
9. Test normal, boundary, NULL, concurrency, rollback and retry cases.
10. Compare result sets and row changes against a controlled Oracle baseline.

Study MySQL stored procedures, stored functions, cursors and transactions before a production conversion.

Official References

• Oracle Database 19c: Main Features of PL/SQL
• MySQL 8.4: Compound Statement Syntax
• MySQL 8.4: Restrictions on Stored Programs

Language structure and migration cautions were checked against the official Oracle PL/SQL and MySQL 8.4 manuals.