Conversion of Oracle RECORD Variable to Microsoft SQL Server

Since Microsoft SQL Server doesn't support records as type, SQLWays emulates such functionality through use set of distinct variables. For full substitution record variables SQLWays changes definition of record data type, variable declaration with record type, use record variables and use of internal element of record variables.

At first SQLWays converts declaration of variable with record type to set of distinct declaration with corresponding data types. Number and data types of declaring variables are equal with internal element of sources record variable.

After that SQLWays looks for use of record variables and converts them as follow:

If record variable uses without references to internal element then SQLWays converts it's to list of variables which generate by SQLWays for this record variable.

If record variable uses with references to internal element then SQLWays converts its to corresponding variable which generates by SQLWays.

TABLE 28. Conversion of Oracle RECORD Variable to Microsoft SQL Server
Oracle	Microsoft SQL Server
CREATE OR REPLACE PROCEDURE ora_sp_record_type as begin DECLARE TYPE RecType IS RECORD( a number, b varchar2(15)); RecVar RecType; a number; b varchar2(15); CURSOR cursoitem IS SELECT a, b FROM tab1; BEGIN OPEN cursoitem; LOOP FETCH cursoitem INTO RecVar; EXIT WHEN cursoitem%NOTFOUND; BEGIN SELECT a, b INTO a,b FROM tabl WHERE c = RecVar.a; END; END LOOP; CLOSE cursoitem; COMMIT; END; end;	CREATE PROCEDURE ORA_SP_RECORD_TYPE AS begin DECLARE @a FLOAT DECLARE @b VARCHAR(15) DECLARE cursoitem CURSOR FOR SELECT @a, @b FROM tab1 DECLARE @SWV_RecVar_a FLOAT DECLARE @SWV_RecVar_b VARCHAR(15) OPEN cursoitem while 1 = 1 begin FETCH cursoitem INTO @SWV_RecVar_a, @SWV_RecVar_b if @@FETCH_STATUS <> 0 break BEGIN select @a = a, @b = b FROM tabl WHERE a = @SWV_RecVar_a END end CLOSE cursoitem DEALLOCATE cursoitem COMMIT end

TABLE 28. Conversion of Oracle RECORD Variable to Microsoft SQL Server

Oracle

Microsoft SQL Server

CREATE OR REPLACE PROCEDURE 
ora_sp_record_type as 
begin 
  DECLARE 
    TYPE RecType IS RECORD( 
      a     number, 
      b      varchar2(15)); 
    RecVar    RecType; 
a number; 
b varchar2(15); 
CURSOR cursoitem IS SELECT a, b  
FROM tab1; 
BEGIN 
    OPEN cursoitem; 
     LOOP 
      FETCH cursoitem  INTO RecVar; 
      EXIT WHEN cursoitem%NOTFOUND; 
      BEGIN 
         SELECT a, b INTO a,b FROM tabl 
         WHERE c = RecVar.a; 
      END; 
    END LOOP; 
    CLOSE cursoitem; 
    COMMIT; 
  END; 
end;

CREATE PROCEDURE ORA_SP_RECORD_TYPE  AS 
begin 
   DECLARE @a FLOAT 
   DECLARE @b VARCHAR(15) 
   DECLARE cursoitem CURSOR FOR SELECT @a, @b 
   FROM tab1 
   DECLARE @SWV_RecVar_a 
   FLOAT 
   DECLARE @SWV_RecVar_b 
   VARCHAR(15) 
   OPEN cursoitem 
   while 1 = 1 
   begin 
      FETCH cursoitem INTO @SWV_RecVar_a, @SWV_RecVar_b 
      if @@FETCH_STATUS <> 0 
      break 
      BEGIN 
         select  @a = a, @b = b FROM tabl WHERE a = 
@SWV_RecVar_a 
     
      END 
   end 
   CLOSE cursoitem 
   DEALLOCATE cursoitem 
   COMMIT 
end

Ispirer SQLWays Database Migration Software

Conversion of Oracle RECORD Variable to Microsoft SQL Server