Live ORACLE

DECLARE

TYPE employeeset IS TABLE OF employees%ROWTYPE;

underpaid   EMPLOYEESET; -- Holds set of rows from EMPLOYEES table.

CURSOR c1 IS

  SELECT first_name,

         last_name

  FROM   employees;

TYPE nameset IS TABLE OF c1%ROWTYPE;

some_names  NAMESET; -- Holds set of partial rows from EMPLOYEES table.



BEGIN

-- With one query, we bring all the relevant data into the collection of records.

SELECT *

BULK COLLECT INTO underpaid

FROM     employees

WHERE    salary < 5000

ORDER BY salary DESC;



-- Now we can process the data by examining the collection, or passing it to

-- a separate procedure, instead of writing a loop to FETCH each row.

dbms_output.Put_line(underpaid.COUNT

                    ||' people make less than 5000.');



FOR i IN underpaid.FIRST.. underpaid.LAST LOOP

 dbms_output.Put_line(Underpaid(i).last_name

                      ||' makes '

                      ||Underpaid(i).salary);

END LOOP;



-- We can also bring in just some of the table columns.

-- Here we get the first and last names of 10 arbitrary employees.

SELECT first_name,

      last_name

BULK COLLECT INTO some_names

FROM   employees

WHERE  ROWNUM < 11;



FOR i IN some_names.FIRST.. some_names.LAST LOOP

 dbms_output.Put_line('Employee = '

                      ||Some_names(i).first_name

                      ||' '

                      ||Some_names(i).last_name);

END LOOP;

END;

/

49 people make less than 5000.
Austin makes 4800
Pataballa makes 4800
Whalen makes 4400
Sarchand makes 4200
Lorentz makes 4200
Bull makes 4100
Bell makes 4000
Everett makes 3900
Chung makes 3800
Ladwig makes 3600
Dilly makes 3600
Rajs makes 3500
Dellinger makes 3400
Mallin makes 3300
Bissot makes 3300
Stiles makes 3200
Nayer makes 3200
Taylor makes 3200
McCain makes 3200
Khoo makes 3100
Walsh makes 3100
Fleaur makes 3100
Davies makes 3100
Feeney makes 3000
Cabrio makes 3000
Gates makes 2900
Rogers makes 2900
Baida makes 2900
Jones makes 2800
Atkinson makes 2800
Geoni makes 2800
Tobias makes 2800
Seo makes 2700
Mikkilineni makes 2700
OConnell makes 2600
Himuro makes 2600
Grant makes 2600
Matos makes 2600
Perkins makes 2500
Colmenares makes 2500
Sullivan makes 2500
Vargas makes 2500
Patel makes 2500
Marlow makes 2500
Landry makes 2400
Gee makes 2400
Philtanker makes 2200
Markle makes 2200
Olson makes 2100
Employee = Ellen Abel
Employee = Sundar Ande
Employee = Mozhe Atkinson
Employee = David Austin
Employee = Hermann Baer
Employee = Shelli Baida
Employee = Amit Banda
Employee = Elizabeth Bates
Employee = Sarah Bell
Employee = David Bernstein

PL/SQL procedure successfully completed.

Using BULK COLLECT With a SELECT INTO Statement