Mega Code Archive

 
Categories / Oracle PLSQL Tutorial / Query Select
 

Demo record-based DML using PLSQL table and FORALL

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> select empl_no, lastname, firstname   2  from employee   3  where empl_no = 234; no rows selected SQL> SQL> -- Demo record-based DML using PL/SQL table and FORALL SQL> DECLARE   2      Type cust_tab_t is table of employee%rowtype;   3      cust_tab  cust_tab_t  := cust_tab_t();   4  BEGIN   5      cust_tab.extend(2);   6   7      cust_tab(1).empl_no := 1234;   8      cust_tab(1).lastname := 'Anderson';   9      cust_tab(1).firstname := 'Dave';  10  11      cust_tab(2).empl_no := 1235;  12      cust_tab(2).lastname := 'Wiland';  13      cust_tab(2).firstname := 'Geoff';  14  15      FORALL idx IN cust_tab.first..cust_tab.last  16             INSERT INTO employee VALUES cust_tab(idx);  17  END;  18  / PL/SQL procedure successfully completed. SQL> SQL> drop table employee; Table dropped.