Background

Let us create and populate a numbers table.

Code

Outline

Creating a numbers table is a bit more complicated.

And, it’s complexity is dependent on Version of Oracle.

Steps

1. Create Table
   • Identity Column
     • To me identity column is a bit easier that Sequence
2. Run Statement N Times
   • In Transact SQL, we use go
   • In Oracle
     • We can use the Hierarchy function “connect by level”
     • For Statement

SQL

SQL – Identity – Assign Default

create table "number"
    (
        "id" INTEGER
            GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1)
            NOT NULL

        , constraint "PK_Lab_Number"
            primary key
            (
                "id"
            )
    )

;
/

truncate table "number";
/

DECLARE
    l_time PLS_INTEGER;
    l_cpu PLS_INTEGER;

begin

    l_time := DBMS_UTILITY.get_time;
    l_cpu := DBMS_UTILITY.get_cpu_time;

    FOR iLoop IN 1..5000
    LOOP

        insert into "number"
        (
	        "id"
        )
        values
        (
	        default
        );

	END LOOP;

   DBMS_OUTPUT.put_line
      (
        'Time = ' || TO_CHAR(DBMS_UTILITY.get_time - l_time) || ' hsecs '
      );

   DBMS_OUTPUT.put_line
        (
          'CPU Time = ' || (DBMS_UTILITY.get_cpu_time - l_cpu) || ' hsecs '
        );

end;

/

drop table "number";
/

SQL – Identity – Assign Explicit

create table "number"
    (
        "id" INTEGER
            -- GENERATED ALWAYS AS IDENTITY (START WITH 1 INCREMENT BY 1) 
            GENERATED BY DEFAULT AS IDENTITY (START WITH 1 INCREMENT BY 1) 
            NOT NULL
        
        , constraint "PK_Lab_Number"
            primary key
            (
                "id"
            )
    )
    
;
/


truncate table "number";
/

DECLARE 
        l_time     PLS_INTEGER;
        l_cpu      PLS_INTEGER;
        lMaxNumber integer;

begin

    l_time := DBMS_UTILITY.get_time; 
    l_cpu := DBMS_UTILITY.get_cpu_time;
 

    FOR iLoop IN 1..1
    LOOP

		insert into "number"
        (
	        "id"
        )
        with cte
		(
		    "vail"
		)
		as
		(
		    select rownum
            FROM   dual
            CONNECT BY LEVEL <= 5000
		)
        select 
           cte."vail"
           
        from cte
		;
		
        
	END LOOP;
	
    DBMS_OUTPUT.put_line
      (
        'Time = ' || TO_CHAR(DBMS_UTILITY.get_time - l_time) || ' hsecs '
      );
    
       
   DBMS_OUTPUT.put_line      
        ( 
          'CPU Time = ' || (DBMS_UTILITY.get_cpu_time - l_cpu) || ' hsecs '
        );	
	

    select max("id") 
    into   lMaxNumber
    from   "number"
    ;
    
       
   DBMS_OUTPUT.put_line      
        ( 
          'lMaxNumber = ' || TO_CHAR(lMaxNumber)
        );	    
    
end;

/


drop table "number";
/