Mega Code Archive

 

Categories / Oracle PLSQL Tutorial / Query Select

 

Demonstrate the 9i R2 record-based DML support

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   3      cust_rec    employee%rowtype;   4   5  BEGIN   6   7      cust_rec.empl_no := 234;   8      cust_rec.lastname := 'Anderson';   9      cust_rec.firstname := 'Dave';  10  11      INSERT INTO employee VALUES cust_rec;  12  13  EXCEPTION  14      WHEN dup_val_on_index THEN  15          UPDATE employee  16          SET ROW = cust_rec  17          WHERE empl_no = 234;  18  END;  19  / PL/SQL procedure successfully completed. SQL> SQL> drop table employee; Table dropped. SQL>