Conversion of Oracle %ROWTYPE to MySQL

The Oracle %ROWTYPE attribute provides a record type that represents a row in a database table. If you use %ROWTYPE and any column's type changes or the number of columns in the table is changed, your application code remains correct and uses the latest column and type information. This provides data independence from applications, and allows reducing application maintenance code.

MySQL has no an equivalent of Oracle %ROWTYPE. In MySQL it is necessary to declare a variable for every column.

SQLWays changes Oracle variable declaration with the %ROWTYPE attribute to the MySQL variable declaration list with the same names and data types as column's names and data types in the database table.

If there is variable with reference to a field in the record in the procedure body, SQLWays changes it to appropriate variable. If there is an assignment statement with variable using %ROWTYPE in the procedure body, SQLWays changes the assignment statement to the list of assignment statements for each variable.

Given the table ora_rt contains two columns: ID of NUMBER(10) and Name of VARCHAR2(10).

TABLE 22. Using %ROWTYPE without references to the record fields
Oracle	MySQL
CREATE PROCEDURE ORA_SP_ROWTYPE (ROW1 OUT ora_rt%ROWTYPE) IS BEGIN SELECT ID, Name INTO ROW1 FROM ora_rt WHERE ID = 1; END;	CREATE PROCEDURE ORA_SP_ROWTYPE (OUT SWV_ROW1_ID INT, OUT SWV_ROW1_NAME VARCHAR(10)) BEGIN SELECT ID,Name INTO SWV_ROW1_ID,SWV_ROW1_NAME FROM ora_rt WHERE ID = 1; END;/

TABLE 22. Using %ROWTYPE without references to the record fields

Oracle

MySQL

CREATE PROCEDURE ORA_SP_ROWTYPE (ROW1 
OUT ora_rt%ROWTYPE) IS 
BEGIN 
   SELECT ID, Name 
      INTO ROW1 
      FROM ora_rt 
      WHERE ID = 1; 
END;

CREATE PROCEDURE ORA_SP_ROWTYPE (OUT 
SWV_ROW1_ID INT, OUT SWV_ROW1_NAME VARCHAR(10))  
BEGIN 
	SELECT ID,Name 
INTO SWV_ROW1_ID,SWV_ROW1_NAME 
	FROM ora_rt 	WHERE ID = 1; 
END;/

Remarks: in the table ora_rt there are two columns: ID of NUMBER(10) and Name of VARCHAR2(10).

TABLE 23. Using %ROWTYPE with reference to a field in the record.
Oracle	MySQL
CREATE PROCEDURE ora_sp_rowtype3 IS ROW1 ora_rt%ROWTYPE; BEGIN ROW1.ID := 5; ROW1.Name := 'Tenth'; INSERT INTO ora_rt values (ROW1.ID, ROW1.Name); END;	CREATE PROCEDURE ORA_SP_ROWTYPE3() BEGIN declare SWV_ROW1_ID INT; declare SWV_ROW1_NAME VARCHAR(10); SET SWV_ROW1_ID = 5; SET SWV_ROW1_Name = 'Tenth'; INSERT INTO ora_rt values(SWV_ROW1_ID,SWV_ROW1_Name); END;/

TABLE 23. Using %ROWTYPE with reference to a field in the record.

Oracle

MySQL

CREATE PROCEDURE ora_sp_rowtype3  IS 
ROW1 ora_rt%ROWTYPE; 
BEGIN 
   ROW1.ID := 5; 
   ROW1.Name := 'Tenth'; 
INSERT INTO ora_rt values (ROW1.ID, ROW1.Name); 
END;

CREATE PROCEDURE ORA_SP_ROWTYPE3() 
BEGIN 
   declare SWV_ROW1_ID INT; 
   declare SWV_ROW1_NAME VARCHAR(10); 
   SET SWV_ROW1_ID = 5; 
   SET SWV_ROW1_Name = 'Tenth'; 
   INSERT INTO ora_rt 
values(SWV_ROW1_ID,SWV_ROW1_Name); 
END;/

TABLE 24. Using %ROWTYPE with the record assignment.
Oracle	MySQL
CREATE PROCEDURE ora_sp_rowtype2 IS ROW1 ora_rt%ROWTYPE; ROW2 ora_rt %ROWTYPE; BEGIN SELECT ID, Name INTO ROW1 FROM ora_rt WHERE ID = 1; ROW2 := ROW1; END;	REATE PROCEDURE ORA_SP_ROWTYPE2() BEGIN declare SWV_ROW1_ID INT; declare SWV_ROW1_NAME VARCHAR(10); declare SWV_ROW2_ID INT; declare SWV_ROW2_NAME VARCHAR(10); SELECT ID,Name INTO SWV_ROW1_ID,SWV_ROW1_NAME FROM ora_rt WHERE ID = 1; SET SWV_ROW2_ID = SWV_ROW1_ID; SET SWV_ROW2_NAME = SWV_ROW1_NAME; END;/

TABLE 24. Using %ROWTYPE with the record assignment.

Oracle

MySQL

CREATE PROCEDURE ora_sp_rowtype2  IS 
ROW1 ora_rt%ROWTYPE; 
ROW2 ora_rt %ROWTYPE; 
BEGIN 
   SELECT ID, Name 
      INTO ROW1 
      FROM ora_rt 
      WHERE ID = 1; 
ROW2 := ROW1; 
END;

REATE PROCEDURE ORA_SP_ROWTYPE2() 
BEGIN 
   declare SWV_ROW1_ID INT; 
   declare SWV_ROW1_NAME VARCHAR(10); 
   declare SWV_ROW2_ID INT; 
   declare SWV_ROW2_NAME VARCHAR(10); 
   SELECT ID,Name 
   INTO SWV_ROW1_ID,SWV_ROW1_NAME 
   FROM ora_rt WHERE ID = 1; 
   SET SWV_ROW2_ID = SWV_ROW1_ID; 
 SET SWV_ROW2_NAME = SWV_ROW1_NAME; 
END;/

Ispirer SQLWays Database Migration Software

Conversion of Oracle %ROWTYPE to MySQL