Demo record-based DML and Bulk Collect : ROW « Query Select « Oracle PL/SQL Tutorial






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>
SQL>
SQL>
SQL> -- Demo record-based DML and Bulk Collect:
SQL> DECLARE
  2      Type cust_rec_t is RECORD
  3           (empl_no  number,
  4                lastname varchar2(25),
  5                firstname varchar2(25) );
  6      Type cust_tab_t is table of cust_rec_t;
  7      cust_tab  cust_tab_t;
  8  BEGIN
  9      UPDATE employee
 10      SET area_code = '917'
 11      WHERE area_code = '212'
 12      RETURNING empl_no, lastname, firstname
 13      BULK COLLECT INTO cust_tab;
 14      dbms_output.put_line(cust_tab(1).lastname );
 15  END;
 16  /
Andy

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>
SQL>
SQL> drop table employee;

Table dropped.








2.33.ROW
2.33.1.Demonstrate the 9i R2 record-based DML support
2.33.2.Demo record-based DML using PL/SQL table and FORALL
2.33.3.Demo record-based DML and Bulk Collect