Oracle %ROWTYPE 在MySQL中的移植- Community

[Share Experiences] Oracle %ROWTYPE 在MySQL中的移植

Experiences and Insight 556 views · 2 replies ·

sammy-621

deepin

2021-08-03 22:43

Author

Oracle的%ROWTYPE是一种记录类型，表示数据库表中的一行记录。当使用%ROWTYPE时，表中列的数据类型变化或列数变化时，你的代码会应用最终的列数量和类型属性，而无需重写。这实现了数据表结构与程序代码的解耦，减少了应用程序代码的维护工作量。

在MySQL中没有%ROWTYPE对应的实现，所以需要为每个列定义变量。

我们需要把在Oracle中声明的%ROWTYPE类型变量，转化为与对应字段同名、同类型的一系列的变量。（有专门的软件做转换，如SQLWays）。如果Oracle的存储过程中有变量被赋值了%ROWTYPE类型的一个字段，则在MySQL中定义一个适当的变量，如果在Oracle中有一个变量被赋值为整个%ROWTYPE类型的变量，则在MySQL中需要定义一系列的变量并逐个为其赋值。举个例子对比下Oracle和MySQL中的实现：

给定表ora_rt，包括两个字段：ID NUMBER(10) ,NAME VARCHAR2(10)

以下使用%ROWTYPE但未引用记录字段

Oracle MySQL

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;/`

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;/

以下使用%ROWTYPE并引用了记录字段

Oracle MySQL

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;/`

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;/

以下将%ROWTYPE类型的值整体赋值给变量

Oracle MySQL

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;`	`CREATE 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;/`

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;

CREATE 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);