Oracle PL/SQL Tutorial/PL SQL Statements/bulk collect

Материал из SQL эксперт
Версия от 13:05, 26 мая 2010; Admin (обсуждение | вклад) (1 версия)
(разн.) ← Предыдущая | Текущая версия (разн.) | Следующая → (разн.)
Перейти к: навигация, поиск

Adding a limit to BULK COLLECT

   <source lang="sql">

SQL> SQL> SQL> -- create demo table SQL> create table Employee(

 2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
 3    First_Name         VARCHAR2(10 BYTE),
 4    Last_Name          VARCHAR2(10 BYTE),
 5    Start_Date         DATE,
 6    End_Date           DATE,
 7    Salary             Number(8,2),
 8    City               VARCHAR2(10 BYTE),
 9    Description        VARCHAR2(15 BYTE)
10  )
11  /

Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2               values ("01","Jason",    "Martin",  to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto",  "Programmer")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("02","Alison",   "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("03","James",    "Smith",   to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("04","Celia",    "Rice",    to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("05","Robert",   "Black",   to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("06","Linda",    "Green",   to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York",  "Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("07","David",    "Larry",   to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York",  "Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("08","James",    "Cat",     to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
 3  /

1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee

 2  /

ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION


-------------------- -------------------- --------- --------- ---------- ---------- ---------------

01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> declare

 2      type text_nt is table of VARCHAR2(256);
 3      v_ename_nt text_nt;
 4      cursor c_emp is select first_name from employee where id="02";
 5      procedure p_print_row is
 6      begin
 7          if v_eName_nt.count=2 then
 8             DBMS_OUTPUT.put_line(v_eName_nt(1)||" "||v_eName_nt(2));
 9          elsif v_eName_nt.count=1 then
10             DBMS_OUTPUT.put_line(v_eName_nt(1));
11          end if;
12      end;
13  begin
14      open c_emp;
15      loop
16          fetch c_emp bulk collect into v_eName_nt limit 2;
17          p_print_row;
18          exit when c_emp%NOTFOUND;
19      end loop;
20      close c_emp;
21  end;
22  /

Alison PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee

 2  /

Table dropped. SQL></source>


Bulk collect from cursor with LIMIT option

   <source lang="sql">

SQL> SQL> create table employee

 2          (
 3           empl_no                integer         primary key
 4          ,lastname               varchar2(20)    not null
 5          ,firstname              varchar2(15)    not null
 6          ,midinit                varchar2(1)
 7          ,street                 varchar2(30)
 8          ,city                   varchar2(20)
 9          ,state                  varchar2(2)
10          ,zip                    varchar2(5)
11          ,zip_4                  varchar2(4)
12          ,area_code              varchar2(3)
13          ,phone                  varchar2(8)
14          ,company_name           varchar2(50));

Table created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)

 2  values(1,"Jones","Joe","J","10 Ave","New York","NY","11111","1111","111", "111-1111","A Company");

1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)

 2  values(2,"Smith","Sue","J","20 Ave","New York","NY","22222","2222","222", "222-111","B Company");

1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)

 2  values(3,"Anderson","Peggy","J","500 St","New York","NY","33333","3333","333", "333-3333","C Company");

1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)

 2  values(4,"Andy","Jill", null,"930 St","New York","NY","44444","4444","212", "634-7733","D Company");

1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)

 2  values(5,"OK","Carl","L","19 Drive","New York","NY","55555","3234","212", "243-4243","E Company");

1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)

 2  values(6,"Peter","Jee","Q","38 Ave","New York","NY","66666","4598","212", "454-5443","F Inc");

1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)

 2  values(7,"Baker","Paul","V","738 St.","Queens","NY","77777","3842","718", "664-4333","G Inc");

1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)

 2  values(8,"Young","Steve","J","388 Ave","New York","NY","88888","3468","212", "456-4566","H Associates Inc");

1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)

 2  values(9,"Mona","Joe","T","9300 Ave","Kansas City","MO","99999","3658","415", "456-4563","J Inc");

1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)

 2  values(10,"Hackett","Karen","S","Kings Rd. Apt 833","Bellmore","NY","61202","3898","516", "767-5677","AA Inc");

1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)

 2  values(11,"Bob","Jack","S","12 Giant Rd.","Newark","NJ","27377","3298","908", "123-7367","Z Associates");

1 row created. SQL> SQL> declare

 2    type employee_record_type is table of employee%rowtype;
 3    cust_array  employee_record_type;
 4    cursor c is select * from employee order by 1;
 5  begin
 6    open c;
 7    loop
 8       fetch c bulk collect into cust_array LIMIT 7;
 9       for i in 1..cust_array.COUNT loop
10         dbms_output.put_line(cust_array(i).lastname );
11       end loop;
12       exit when c%notfound;
13    end loop;
14    close c;
15  end;
16  /

Jones Smith Anderson Andy OK Peter Baker Young Mona Hackett Bob PL/SQL procedure successfully completed. SQL> SQL> SQL> drop table employee; Table dropped. SQL></source>


Bulk collect with non-cursor SELECT into multiple collections

   <source lang="sql">

SQL> SQL> create table employee

 2          (
 3           empl_no                integer         primary key
 4          ,lastname               varchar2(20)    not null
 5          ,firstname              varchar2(15)    not null
 6          ,midinit                varchar2(1)
 7          ,street                 varchar2(30)
 8          ,city                   varchar2(20)
 9          ,state                  varchar2(2)
10          ,zip                    varchar2(5)
11          ,zip_4                  varchar2(4)
12          ,area_code              varchar2(3)
13          ,phone                  varchar2(8)
14          ,company_name           varchar2(50));

Table created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)

 2  values(1,"Jones","Joe","J","10 Ave","New York","NY","11111","1111","111", "111-1111","A Company");

1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)

 2  values(2,"Smith","Sue","J","20 Ave","New York","NY","22222","2222","222", "222-111","B Company");

1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)

 2  values(3,"Anderson","Peggy","J","500 St","New York","NY","33333","3333","333", "333-3333","C Company");

1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)

 2  values(4,"Andy","Jill", null,"930 St","New York","NY","44444","4444","212", "634-7733","D Company");

1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)

 2  values(5,"OK","Carl","L","19 Drive","New York","NY","55555","3234","212", "243-4243","E Company");

1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)

 2  values(6,"Peter","Jee","Q","38 Ave","New York","NY","66666","4598","212", "454-5443","F Inc");

1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)

 2  values(7,"Baker","Paul","V","738 St.","Queens","NY","77777","3842","718", "664-4333","G Inc");

1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)

 2  values(8,"Young","Steve","J","388 Ave","New York","NY","88888","3468","212", "456-4566","H Associates Inc");

1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)

 2  values(9,"Mona","Joe","T","9300 Ave","Kansas City","MO","99999","3658","415", "456-4563","J Inc");

1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)

 2  values(10,"Hackett","Karen","S","Kings Rd. Apt 833","Bellmore","NY","61202","3898","516", "767-5677","AA Inc");

1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)

 2  values(11,"Bob","Jack","S","12 Giant Rd.","Newark","NJ","27377","3298","908", "123-7367","Z Associates");

1 row created. SQL> SQL> SQL> SQL> declare

 2    type number_array is table of number;
 3    type varchar2_array is table of varchar2(20);
 4    v1 number_array;
 5    v2 varchar2_array;
 6
 7  begin
 8
 9    select empl_no, lastname
10           bulk collect into v1, v2
11    from employee;
12
13    for i in 1..v1.LAST loop    /* could use COUNT or SQL%rowcount */
14       dbms_output.put_line(v2(i) );
15    end loop;
16  end;
17  /

Jones Smith Anderson Andy OK Peter Baker Young Mona Hackett Bob PL/SQL procedure successfully completed. SQL> SQL> drop table employee; Table dropped. SQL></source>


Bulk processing with the FORALL statement

   <source lang="sql">

SQL> SQL> SQL> SQL> create table employee_history

 2          (
 3           empl_no                integer         primary key
 4          ,lastname               varchar2(20)    not null
 5          ,firstname              varchar2(15)    not null
 6          ,midinit                varchar2(1)
 7          ,street                 varchar2(30)
 8          ,city                   varchar2(20)
 9          ,state                  varchar2(2)
10          ,zip                    varchar2(5)
11          ,zip_4                  varchar2(4)
12          ,area_code              varchar2(3)
13          ,phone                  varchar2(8)
14        ,company_name         varchar2(50)
15          );

Table created. SQL> SQL> SQL> SQL> insert into employee_history(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)

 2  values(9,"OK","Joe","T","9 Ave","Kansas City","MO","65602","3658","415", "456-4563","A Inc");

1 row created. SQL> SQL> insert into employee_history(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)

 2  values(12,"Yes","Larry","T","1 Rd.","Newarkville","NJ","27377","3298","908", "123-7384","B Inc");

1 row created. SQL> SQL> insert into employee_history(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)

 2  values(13,"No","Cindy","T","13 Street","Warwick","RI","07377","3298","401", "123-7384","C Inc");

1 row created. SQL> SQL> insert into employee_history(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)

 2  values(14,"Fine","Doris","R","11th Ave","New York","NY","22299","3222","212", "123-1234","D Inc");

1 row created. SQL> SQL> SQL> SQL> create table ord(

 2           order_no               integer          primary key
 3          ,empl_no                integer
 4          ,order_date             date not null
 5          ,total_order_price      number(7,2)
 6          ,deliver_date           date
 7          ,deliver_time           varchar2(7)
 8          ,payment_method         varchar2(2)
 9          ,emp_no                 number(3,0)
10          ,deliver_name           varchar2(35)
11          ,gift_message           varchar2(100)
12  );

Table created. SQL> SQL> SQL> insert into ord(order_no,empl_no,order_date,total_order_price,deliver_date,deliver_time,payment_method,emp_no,deliver_name,gift_message)

 2           values(1,1,"14-Feb-2002", 23.00, "14-Feb-2002", "12 noon", "CA",1, null, "Gift for wife");

1 row created. SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time ,payment_method ,emp_no,deliver_name ,gift_message )

 2           values(2,1,"14-Feb-2003", 510.98, "14-feb-2003", "5 pm", "NY",7, "Rose Ted", "Happy Valentines Day to Mother");

1 row created. SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )

 2           values(3, 2,"14-Feb-2004", 315.99, "14-feb-2004", "3 pm", "VS",2, "Ani Forest", "Happy Valentines Day to Father");

1 row created. SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )

 2           values(4, 2,"14-Feb-1999", 191.95, "14-feb-1999", "2 pm", "NJ",2, "O. John", "Happy Valentines Day");

1 row created. SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )

 2           values(5, 6,"4-mar-2002", 101.95, "5-mar-2002", "2:30 pm", "MO"    , 2, "Cora", "Happy Birthday from John");

1 row created. SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )

 2           values(6, 9,"7-apr-2003", 221.95, "7-apr-2003", "3 pm", "MA", 2, "Sake Keith", "Happy Birthday from Joe" );

1 row created. SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )

 2           values(7, 9,"20-jun-2004", 315.95, "21-jun-2004", "12 noon", "BC", 2, "Jessica Li", "Happy Birthday from Jessica");

1 row created. SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )

 2           values (8, 12, "31-dec-1999", 135.95, "1-jan-2000", "12 noon", "DI",   3, "Larry", "Happy New Year from Lawrence");

1 row created. SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )

 2           values (9, 12, "26-dec-2003", 715.95, "2-jan-2004", "12 noon", "SK",7, "Did", "Happy Birthday from Nancy" );

1 row created. SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )

 2           values(10, 4, sysdate-1, 119.95, sysdate+2, "6:30 pm", "VG",2, "P. Jing", "Happy Valentines Day to Jason");

1 row created. SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )

 2           values(11, 2, sysdate, 310.00, sysdate+2, "3:30 pm", "DC",2, "C. Late", "Happy Birthday Day to Jack");

1 row created. SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)

 2           values(12, 7, sysdate-3, 121.95, sysdate-2, "1:30 pm", "AC",2, "W. Last", "Happy Birthday Day to You");

1 row created. SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)

 2           values(13, 7, sysdate, 211.95, sysdate-4, "4:30 pm", "CA",2, "J. Bond", "Thanks for hard working");

1 row created. SQL> SQL> SQL> SQL> declare

 2      type cust_array_type is table of number
 3          index by binary_integer;
 4      employee_array  cust_array_type;
 5      v_index number;
 6  begin
 7      select empl_no bulk collect
 8      into employee_array from employee_history;
 9
10      FORALL i IN employee_array.FIRST..employee_array.LAST
11         delete from ord where empl_no = employee_array(i);
12
13      v_index := employee_array.FIRST;
14      for i in employee_array.FIRST..employee_array.LAST loop
15          dbms_output.put_line("delete for employee "
16                                ||employee_array(v_index)
17                                ||" deleted "
18                                ||SQL%BULK_ROWCOUNT(v_index)
19                                ||" rows.");
20               v_index := employee_Array.NEXT(v_index);
21       end loop;
22  end;
23  /

delete for employee 9 deleted 2 rows. delete for employee 12 deleted 2 rows. delete for employee 13 deleted 0 rows. delete for employee 14 deleted 0 rows. PL/SQL procedure successfully completed. SQL> SQL> drop table ord; Table dropped. SQL> drop table employee_history; Table dropped.</source>


Demonstrate how to use DELETE with bulk processing.

   <source lang="sql">

SQL> SQL> SQL> SQL> create table employee_history

 2          (
 3           empl_no                integer         primary key
 4          ,lastname               varchar2(20)    not null
 5          ,firstname              varchar2(15)    not null
 6          ,midinit                varchar2(1)
 7          ,street                 varchar2(30)
 8          ,city                   varchar2(20)
 9          ,state                  varchar2(2)
10          ,zip                    varchar2(5)
11          ,zip_4                  varchar2(4)
12          ,area_code              varchar2(3)
13          ,phone                  varchar2(8)
14        ,company_name         varchar2(50)
15          );

Table created. SQL> SQL> SQL> SQL> insert into employee_history(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)

 2  values(9,"OK","Joe","T","9 Ave","Kansas City","MO","65602","3658","415", "456-4563","A Inc");

1 row created. SQL> SQL> insert into employee_history(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)

 2  values(12,"Yes","Larry","T","1 Rd.","Newarkville","NJ","27377","3298","908", "123-7384","B Inc");

1 row created. SQL> SQL> insert into employee_history(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)

 2  values(13,"No","Cindy","T","13 Street","Warwick","RI","07377","3298","401", "123-7384","C Inc");

1 row created. SQL> SQL> insert into employee_history(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)

 2  values(14,"Fine","Doris","R","11th Ave","New York","NY","22299","3222","212", "123-1234","D Inc");

1 row created. SQL> SQL> SQL> SQL> create table ord(

 2           order_no               integer          primary key
 3          ,empl_no                integer
 4          ,order_date             date not null
 5          ,total_order_price      number(7,2)
 6          ,deliver_date           date
 7          ,deliver_time           varchar2(7)
 8          ,payment_method         varchar2(2)
 9          ,emp_no                 number(3,0)
10          ,deliver_name           varchar2(35)
11          ,gift_message           varchar2(100)
12  );

Table created. SQL> SQL> SQL> insert into ord(order_no,empl_no,order_date,total_order_price,deliver_date,deliver_time,payment_method,emp_no,deliver_name,gift_message)

 2           values(1,1,"14-Feb-2002", 23.00, "14-Feb-2002", "12 noon", "CA",1, null, "Gift for wife");

1 row created. SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time ,payment_method ,emp_no,deliver_name ,gift_message )

 2           values(2,1,"14-Feb-2003", 510.98, "14-feb-2003", "5 pm", "NY",7, "Rose Ted", "Happy Valentines Day to Mother");

1 row created. SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )

 2           values(3, 2,"14-Feb-2004", 315.99, "14-feb-2004", "3 pm", "VS",2, "Ani Forest", "Happy Valentines Day to Father");

1 row created. SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )

 2           values(4, 2,"14-Feb-1999", 191.95, "14-feb-1999", "2 pm", "NJ",2, "O. John", "Happy Valentines Day");

1 row created. SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )

 2           values(5, 6,"4-mar-2002", 101.95, "5-mar-2002", "2:30 pm", "MO"    , 2, "Cora", "Happy Birthday from John");

1 row created. SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )

 2           values(6, 9,"7-apr-2003", 221.95, "7-apr-2003", "3 pm", "MA", 2, "Sake Keith", "Happy Birthday from Joe" );

1 row created. SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )

 2           values(7, 9,"20-jun-2004", 315.95, "21-jun-2004", "12 noon", "BC", 2, "Jessica Li", "Happy Birthday from Jessica");

1 row created. SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )

 2           values (8, 12, "31-dec-1999", 135.95, "1-jan-2000", "12 noon", "DI",   3, "Larry", "Happy New Year from Lawrence");

1 row created. SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )

 2           values (9, 12, "26-dec-2003", 715.95, "2-jan-2004", "12 noon", "SK",7, "Did", "Happy Birthday from Nancy" );

1 row created. SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )

 2           values(10, 4, sysdate-1, 119.95, sysdate+2, "6:30 pm", "VG",2, "P. Jing", "Happy Valentines Day to Jason");

1 row created. SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )

 2           values(11, 2, sysdate, 310.00, sysdate+2, "3:30 pm", "DC",2, "C. Late", "Happy Birthday Day to Jack");

1 row created. SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)

 2           values(12, 7, sysdate-3, 121.95, sysdate-2, "1:30 pm", "AC",2, "W. Last", "Happy Birthday Day to You");

1 row created. SQL> insert into ord(order_no ,empl_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)

 2           values(13, 7, sysdate, 211.95, sysdate-4, "4:30 pm", "CA",2, "J. Bond", "Thanks for hard working");

1 row created. SQL> SQL> SQL> SQL> -- First, review row-at-a-time processing: SQL> declare

 2      type cust_array_type is table of number
 3           index by binary_integer;
 4      employee_array  cust_array_type;
 5  begin
 6      select empl_no bulk collect
 7      into employee_array from employee_history;
 8
 9      FOR i IN employee_array.FIRST..employee_array.LAST LOOP
10         delete from ord where empl_no = employee_array(i);
11         dbms_output.put_line(sql%rowcount);
12      END LOOP;
13  end;
14  /

2 2 0 0 PL/SQL procedure successfully completed. SQL> SQL> SQL> drop table employee_history; Table dropped. SQL> SQL> drop table ord; Table dropped.</source>


select BULK COLLECT

   <source lang="sql">

SQL> create table cities

 2  as
 3  select username city
 4    from all_users
 5   where rownum<=37;

Table created. SQL> SQL> alter table cities

 2  add constraint
 3  cities_pk primary key(city);

Table altered. SQL> SQL> create table with_ri

 2  ( x    char(80),
 3    city references cities
 4  );

Table created. SQL> SQL> create table without_ri

 2  ( x    char(80),
 3    city varchar2(30)
 4  );

Table created. SQL> alter session set sql_trace=true; Session altered. SQL> SQL> declare

 2      type array is table of varchar2(30) index by binary_integer;
 3      l_data array;
 4  begin
 5      select * BULK COLLECT into l_data from cities;
 6      for i in 1 .. 1000
 7      loop
 8          for j in 1 .. l_data.count
 9          loop
10              insert into with_ri
11              values ("x", l_data(j) );
12              insert into without_ri
13              values ("x", l_data(j) );
14          end loop;
15      end loop;
16  end;
17  /

PL/SQL procedure successfully completed. SQL> SQL> SQL> drop table cities cascade constraint; Table dropped. SQL> drop table with_ri; Table dropped. SQL> drop table without_ri; Table dropped. SQL> SQL></source>


Speeding Up Data Collection with Bulk Operations

   <source lang="sql">

SQL> SQL> -- create demo table SQL> create table Employee(

 2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
 3    First_Name         VARCHAR2(10 BYTE),
 4    Last_Name          VARCHAR2(10 BYTE),
 5    Start_Date         DATE,
 6    End_Date           DATE,
 7    Salary             Number(8,2),
 8    City               VARCHAR2(10 BYTE),
 9    Description        VARCHAR2(15 BYTE)
10  )
11  /

Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2               values ("01","Jason",    "Martin",  to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto",  "Programmer")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("02","Alison",   "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("03","James",    "Smith",   to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("04","Celia",    "Rice",    to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("05","Robert",   "Black",   to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("06","Linda",    "Green",   to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York",  "Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("07","David",    "Larry",   to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York",  "Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("08","James",    "Cat",     to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
 3  /

1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee

 2  /

ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION


-------------------- -------------------- --------- --------- ---------- ---------- ---------------

01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> create or replace type emp2_oty is object (empNo NUMBER,eName VARCHAR2(10))

 2  /

Type created. SQL> SQL> create or replace type emp2_nt is table of emp2_oty

 2  /

Type created. SQL> SQL> SQL> create or replace function f_getEmpDept_nt (i_deptNo VARCHAR)

 2  return emp2_nt is
 3      v_emp2_nt emp2_nt:=emp2_nt();
 4  begin
 5      select emp2_oty(id, first_name) bulk collect into v_emp2_nt from employee where id=i_deptNo;
 6      return v_emp2_nt;
 7  end;
 8  /

Function created. SQL> SQL> drop type emp2_nt; Type dropped. SQL> SQL> drop type emp2_oty; Type dropped. SQL> SQL> SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee

 2  /

Table dropped.</source>


To get the same result by using explicit cursors and bulk collect

   <source lang="sql">

SQL> SQL> -- create demo table SQL> create table Employee(

 2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
 3    First_Name         VARCHAR2(10 BYTE),
 4    Last_Name          VARCHAR2(10 BYTE),
 5    Start_Date         DATE,
 6    End_Date           DATE,
 7    Salary             Number(8,2),
 8    City               VARCHAR2(10 BYTE),
 9    Description        VARCHAR2(15 BYTE)
10  )
11  /

Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2               values ("01","Jason",    "Martin",  to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto",  "Programmer")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("02","Alison",   "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("03","James",    "Smith",   to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("04","Celia",    "Rice",    to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("05","Robert",   "Black",   to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("06","Linda",    "Green",   to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York",  "Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("07","David",    "Larry",   to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York",  "Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("08","James",    "Cat",     to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
 3  /

1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee

 2  /

ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION


-------------------- -------------------- --------- --------- ---------- ---------- ---------------

01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> create or replace type emp2_oty is object (empNo NUMBER,eName VARCHAR2(10))

 2  /

Type created. SQL> SQL> create or replace type emp2_nt is table of emp2_oty

 2  /

Type created. SQL> SQL> SQL> create or replace function f_getEmpDept_nt (i_deptNo varchar)

 2  return emp2_nt is
 3
 4      v_emp2_nt emp2_nt:=emp2_nt();
 5      cursor c_emp is select emp2_oty(id, first_Name) from employee where id=i_deptNo;
 6  begin
 7      open c_emp;
 8      fetch c_emp bulk collect into v_emp2_nt;
 9      close c_emp;
10      return v_emp2_nt;
11  end;
12  /

Function created. SQL> SQL> drop type emp2_nt; Type dropped. SQL> SQL> drop type emp2_oty; Type dropped. SQL> SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee

 2  /

Table dropped.</source>


Using the RETURNING ...BULK COLLECT clause to SELECT directly into a PL/SQL array

   <source lang="sql">

SQL> SQL> create table employee(

 2           empl_no                integer         primary key
 3          ,lastname               varchar2(20)    not null
 4          ,firstname              varchar2(15)    not null
 5          ,midinit                varchar2(1)
 6          ,street                 varchar2(30)
 7          ,city                   varchar2(20)
 8          ,state                  varchar2(2)
 9          ,zip                    varchar2(5)
10          ,zip_4                  varchar2(4)
11          ,area_code              varchar2(3)
12          ,phone                  varchar2(8)
13          ,company_name           varchar2(50));

Table created. SQL> SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)

 2  values(1,"Jones","Joe","J","10 Ave","New York","NY","11111","1111","111", "111-1111","A Company");

1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)

 2  values(2,"Smith","Sue","J","20 Ave","New York","NY","22222","2222","222", "222-111","B Company");

1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)

 2  values(3,"Anderson","Peggy","J","500 St","New York","NY","33333","3333","333", "333-3333","C Company");

1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)

 2  values(4,"Andy","Jill", null,"930 St","New York","NY","44444","4444","212", "634-7733","D Company");

1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)

 2  values(5,"OK","Carl","L","19 Drive","New York","NY","55555","3234","212", "243-4243","E Company");

1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)

 2  values(6,"Peter","Jee","Q","38 Ave","New York","NY","66666","4598","212", "454-5443","F Inc");

1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)

 2  values(7,"Baker","Paul","V","738 St.","Queens","NY","77777","3842","718", "664-4333","G Inc");

1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)

 2  values(8,"Young","Steve","J","388 Ave","New York","NY","88888","3468","212", "456-4566","H Associates Inc");

1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)

 2  values(9,"Mona","Joe","T","9300 Ave","Kansas City","MO","99999","3658","415", "456-4563","J Inc");

1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)

 2  values(10,"Hackett","Karen","S","Kings Rd. Apt 833","Bellmore","NY","61202","3898","516", "767-5677","AA Inc");

1 row created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)

 2  values(11,"Bob","Jack","S","12 Giant Rd.","Newark","NJ","27377","3298","908", "123-7367","Z Associates");

1 row created. SQL> SQL> DECLARE

 2      Type cust_rec_t is RECORD
 3      (empl_no  number, lastname varchar2(25),
 4                firstname varchar2(25) );
 5      Type cust_tab_t is table of cust_rec_t
 6                   index by binary_integer;
 7      cust_tab  cust_tab_t;
 8  BEGIN
 9      UPDATE employee SET area_code = "917"
10      WHERE area_code = "212"
11      RETURNING empl_no, lastname, firstname
12      BULK COLLECT INTO cust_tab;
13      dbms_output.put_line(cust_tab(1).lastname );
14      dbms_output.put_line(cust_tab.COUNT );
15  END;
16  /

Andy 4 PL/SQL procedure successfully completed. SQL> SQL> SQL> SQL> SQL> drop table employee; Table dropped. SQL> SQL> SQL> SQL></source>