PL/SQL Tables - TYPE name IS TABLE OF - Oracle to SQL Server Migration

In Oracle you can use PL/SQL Tables to store and process collections of data in a PL/SQL program (procedure, function i.e.). In SQL Server you can use temporary tables to implement such functionality.

You can declare, fill in and iterate a PL/SQL table of records as follows:

Oracle:

  SET serveroutput ON
 
  DECLARE
     TYPE t_emp IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;
     v_emp t_emp;
     idx BINARY_INTEGER := 0;
  BEGIN
      -- Fill in the PL/SQL table with a query result
      FOR rec IN  (SELECT empno, ename, job FROM emp) LOOP
         idx := idx+1;
         v_emp(idx).empno := rec.empno;
         v_emp(idx).ename := rec.ename;
         v_emp(idx).job := rec.job;
      END LOOP;
 
      -- Iterate the collection and output its data
      FOR i IN 1..idx LOOP
         DBMS_OUTPUT.PUT_LINE('Employee No: ' || v_emp(i).empno ||
            ', Name: ' || v_emp(i).ename ||
            ', Job: ' || v_emp(i).job);
      END LOOP;
  END;
  /

When migrating to SQL Server you can use temporary tables to implement PL/SQL tables functionality:

SQL Server:

  -- Create temporary table and insert data
  SELECT * INTO #v_emp 
  FROM emp;

Iterate the temporary table records:

SQL Server:

  DECLARE @empno CHAR(5);
  DECLARE @ename VARCHAR(30);
  DECLARE @job VARCHAR(30);
 
  DECLARE cur CURSOR FOR SELECT * FROM #v_emp;
  OPEN cur;
 
  FETCH NEXT FROM cur INTO @empno, @ename, @job;
  WHILE @@FETCH_STATUS = 0  
  BEGIN 
     PRINT('Employee No: ' + @empno +
            ', Name: ' + @ename +
            ', Job: ' + @job);
     FETCH NEXT FROM cur INTO @empno, @ename, @job;
  END
 
  CLOSE cur;
  DEALLOCATE cur;

For more information, see Oracle to SQL Server Migration.