DBMS_SQL Package - Oracle to MySQL Migration

In Oracle, the DBMS_SQL built-in package allows you to execute dynamic SQL statements.

In MySQL, you can use the PREPARE and EXECUTE statements.

Oracle:

  -- Sample table
  CREATE TABLE colors (name VARCHAR(30), category CHAR(1));
 
  -- Sample procedure
  CREATE OR REPLACE PROCEDURE sp1(name VARCHAR2, category CHAR)
  AS
    cur INTEGER;
    processed INTEGER;
  BEGIN
    cur := DBMS_SQL.OPEN_CURSOR;
 
    DBMS_SQL.PARSE(cur, 'INSERT INTO colors VALUES (:name, :category)', DBMS_SQL.NATIVE);
 
    DBMS_SQL.BIND_VARIABLE(cur, ':name', name);
    DBMS_SQL.BIND_VARIABLE(cur, ':category', SUBSTR(category, 1, 1));
 
    processed := DBMS_SQL.EXECUTE(cur);
    DBMS_SQL.CLOSE_CURSOR(cur);
 
    DBMS_OUTPUT.PUT_LINE(processed);
  END;
  /

MySQL:

  -- Sample table
  CREATE TABLE colors (name VARCHAR2(30), category CHAR(1));
 
  DELIMITER //
 
  -- Sample procedure
  CREATE PROCEDURE sp1(name VARCHAR(4000), category CHAR)
  BEGIN
    DECLARE processed INTEGER;
 
    PREPARE cur FROM 'INSERT INTO colors VALUES (?, ?)';  
    SET @cur_name = name;
    SET @cur_category = SUBSTR(category, 1, 1);
 
    EXECUTE cur USING @cur_name, @cur_category;  
    SET processed = ROW_COUNT();
 
    DEALLOCATE PREPARE cur;
 
    SELECT processed AS '';
  END;
  //
 
  DELIMITER ;

For more information, see Oracle to MySQL Migration.