AWS Schema Conversion Tool
User Guide (Version 1.0)

Converting Oracle to Amazon RDS for MySQL or Amazon Aurora (MySQL)

The following are some things to consider regarding GOTO statements:

  • A GOTO statement and a label can be used to change the order that statements are run in. Any PL/SQL statements that follow a GOTO statement are skipped and processing continues at the label. GOTO statements and labels can be used anywhere within a procedure, batch, or statement block. GOTO statements can also be nested.

    MySQL doesn’t use GOTO statements. When AWS SCT converts code that contains a GOTO statement, it converts the statement to use a BEGIN…END or LOOP…END LOOP statement. You can find examples of how AWS SCT converts GOTO statements in the table following.

    Oracle GOTO statements and the converted MySQL statements

    Oracle statement MySQL statement
    BEGIN .... statement1; .... GOTO label1; statement2; .... label1: Statement3; .... END
    BEGIN label1: BEGIN .... statement1; .... LEAVE label1; statement2; .... END; Statement3; .... END
    BEGIN .... statement1; .... label1: statement2; .... GOTO label1; statement3; .... statement4; .... END
    BEGIN .... statement1; .... label1: LOOP statement2; .... ITERATE label1; LEAVE label1; END LOOP; statement3; .... statement4; .... END
    BEGIN .... statement1; .... label1: statement2; .... statement3; .... statement4; .... END
    BEGIN .... statement1; .... label1: BEGIN statement2; .... statement3; .... statement4; .... END; END

Converting the WITH Statement in Oracle to Amazon RDS for MySQL or Amazon Aurora (MySQL)

You use the WITH clause (subquery_factoring) in Oracle to assign a name (query_name) to a subquery block. You can then reference the subquery block multiple places in the query by specifying query_name. If a subquery block doesn't contain links or parameters (local, procedure, function, package), then AWS SCT converts the clause to a view or a temporary table.

The advantage of converting the clause to a temporary table is that repeated references to the subquery may be more efficient as the data is easily retrieved from the temporary table rather than being required by each reference. This can be emulated by using additional views or a temporary table.The view name uses the format <procedure_name>$<subselect_alias>.

Oracle WITH statements and the converted MySQL statements

Oracle statement MySQL statement
CREATE PROCEDURE TEST_ORA_PG.P_WITH_SELECT_VARIABLE_01 (p_state IN NUMBER) AS l_dept_id NUMBER := 1; BEGIN FOR cur IN (WITH dept_empl(id, name, surname, lastname, state, dept_id) AS ( SELECT id, name, surname, lastname, state, dept_id FROM test_ora_pg.dept_employees WHERE state = p_state AND dept_id = l_dept_id) SELECT id,state FROM dept_empl ORDER BY id) LOOP NULL; END LOOP;
CREATE PROCEDURE test_ora_pg.P_WITH_SELECT_VARIABLE_01(IN par_P_STATE DOUBLE) BEGIN DECLARE var_l_dept_id DOUBLE DEFAULT 1; DECLARE var$id VARCHAR (8000); DECLARE var$state VARCHAR (8000); DECLARE done INT DEFAULT FALSE; DECLARE cur CURSOR FOR SELECT ID, STATE FROM (SELECT ID, NAME, SURNAME, LASTNAME, STATE, DEPT_ID FROM TEST_ORA_PG.DEPT_EMPLOYEES WHERE STATE = par_p_state AND DEPT_ID = var_l_dept_id) AS dept_empl ORDER BY ID; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done := TRUE; OPEN cur; read_label: LOOP FETCH cur INTO var$id, var$state; IF done THEN LEAVE read_label; END IF; BEGIN END; END LOOP; CLOSE cur; END;
CREATE PROCEDURE TEST_ORA_PG.P_WITH_SELECT_REGULAR_MULT_01 AS BEGIN FOR cur IN ( WITH dept_empl AS ( SELECT id, name, surname, lastname, state, dept_id FROM test_ora_pg.dept_employees WHERE state = 1), dept AS (SELECT id deptid, parent_id, name deptname FROM test_ora_pg.department ) SELECT dept_empl.*,dept.* FROM dept_empl, dept WHERE dept_empl.dept_id = dept.deptid ) LOOP NULL; END LOOP;
CREATE VIEW TEST_ORA_PG.`P_WITH_SELECT_REGULAR_MULT_01$dept_empl `(id, name, surname, lastname, state, dept_id) AS (SELECT id, name, surname, lastname, state, dept_id FROM test_ora_pg.dept_employees WHERE state = 1); CREATE VIEW TEST_ORA_PG.`P_WITH_SELECT_REGULAR_MULT_01$dept `(deptid, parent_id,deptname) AS (SELECT id deptid, parent_id, name deptname FROM test_ora_pg.department); CREATE PROCEDURE test_ora_pg.P_WITH_SELECT_REGULAR_MULT_01() BEGIN DECLARE var$ID DOUBLE; DECLARE var$NAME VARCHAR (30); DECLARE var$SURNAME VARCHAR (30); DECLARE var$LASTNAME VARCHAR (30); DECLARE var$STATE DOUBLE; DECLARE var$DEPT_ID DOUBLE; DECLARE var$deptid DOUBLE; DECLARE var$PARENT_ID DOUBLE; DECLARE var$deptname VARCHAR (200); DECLARE done INT DEFAULT FALSE; DECLARE cur CURSOR FOR SELECT dept_empl.*, dept.* FROM TEST_ORA_PG.`P_WITH_SELECT_REGULAR_MULT_01$dept_empl ` AS dept_empl, TEST_ORA_PG.`P_WITH_SELECT_REGULAR_MULT_01$dept ` AS dept WHERE dept_empl.DEPT_ID = dept.DEPTID; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done := TRUE; OPEN cur; read_label: LOOP FETCH cur INTO var$ID, var$NAME, var$SURNAME, var$LASTNAME, var$STATE, var$DEPT_ID, var$deptid, var$PARENT_ID, var$deptname; IF done THEN LEAVE read_label; END IF; BEGIN END; END LOOP; CLOSE cur; END; call test_ora_pg.P_WITH_SELECT_REGULAR_MULT_01()
CREATE PROCEDURE TEST_ORA_PG.P_WITH_SELECT_VAR_CROSS_02(p_state IN NUMBER) AS l_dept_id NUMBER := 10; BEGIN FOR cur IN ( WITH emp AS (SELECT id, name, surname, lastname, state, dept_id FROM test_ora_pg.dept_employees WHERE dept_id > 10 ), active_emp AS ( SELECT id FROM emp WHERE emp.state = p_state ) SELECT * FROM active_emp ) LOOP NULL; END LOOP; END;
CREATE VIEW TEST_ORA_PG.`P_WITH_SELECT_VAR_CROSS_01$emp `(id, name, surname, lastname, state, dept_id) AS (SELECT id, name, surname, lastname, state, dept_id FROM TEST_ORA_PG.DEPT_EMPLOYEES WHERE DEPT_ID > 10); CREATE PROCEDURE test_ora_pg.P_WITH_SELECT_VAR_CROSS_02(IN par_P_STATE DOUBLE) BEGIN DECLARE var_l_dept_id DOUBLE DEFAULT 10; DECLARE var$ID DOUBLE; DECLARE done INT DEFAULT FALSE; DECLARE cur CURSOR FOR SELECT * FROM (SELECT ID FROM TEST_ORA_PG. `P_WITH_SELECT_VAR_CROSS_01$emp` AS emp WHERE emp.STATE = par_p_state) AS active_emp; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done := TRUE; OPEN cur; read_label: LOOP FETCH cur INTO var$ID; IF done THEN LEAVE read_label; END IF; BEGIN END; END LOOP; CLOSE cur; END;

On this page: