On Jul 12, 1:03 pm, Rose <[email protected]> wrote: > Need to compare the name within two different tables and I am not sure > how to begin. > > I need to get the 1st record from a driver table > Get the 1st record from the owner table then > > Read the 1st character of the driver.driver_name and see if it matches > the 1st character of the owner.owner_name and continue comparing each > character for a match. If it matches the entire name, it inserts into > a new table. If it does not match, move on to the next record in the > owner table and begin again. > > Thank you.
Why on earth would you want to do that? Look how long it takes versus a simple inner join: SQL> create table driver( 2 driver_name varchar2(40), 3 other_stuff varchar2(100), 4 recnum number 5 ); Table created. Elapsed: 00:00:00.02 SQL> SQL> create table owner( 2 owner_name varchar2(40), 3 other_stuff varchar2(100), 4 recnum number 5 ); Table created. Elapsed: 00:00:00.00 SQL> SQL> create table new_table( 2 matched_name varchar2(40), 3 other_stuff varchar2(100), 4 recnum number 5 ); Table created. Elapsed: 00:00:00.00 SQL> SQL> begin 2 for i in 1..1000 loop 3 insert into driver 4 values (TO_CHAR ( TO_DATE ( TO_CHAR ( i, '99999999999') , 'J'), 'JSP'), 'blather and junk '||i, i); 5 insert into owner 6 values (TO_CHAR ( TO_DATE ( TO_CHAR ( i, '99999999999') , 'J'), 'JSP'), 'blather and junk '||i, i); 7 end loop; 8 9 commit; 10 11 end; 12 / PL/SQL procedure successfully completed. Elapsed: 00:00:00.07 SQL> SQL> delete from owner where mod(recnum,33) = 0; 30 rows deleted. Elapsed: 00:00:00.00 SQL> SQL> commit; Commit complete. Elapsed: 00:00:00.00 SQL> SQL> insert into new_table (matched_name) 2 select o.owner_name 3 from driver d join owner o on d.driver_name = o.owner_name; 970 rows created. Elapsed: 00:00:00.00 SQL> SQL> commit; Commit complete. Elapsed: 00:00:00.00 SQL> SQL> select count(*) from new_table; COUNT(*) ---------- 970 Elapsed: 00:00:00.00 SQL> SQL> truncate table new_table; Table truncated. Elapsed: 00:00:00.01 SQL> SQL> declare 2 match varchar2(1):='N'; 3 4 curr_ct number; 5 cursor get_drvr is 6 select driver_name, length(driver_name) dlen from driver; 7 8 cursor get_ownr is 9 select owner_name , length(owner_name) olen from owner; 10 11 begin 12 for drec in get_drvr loop 13 for orec in get_ownr loop 14 if drec.dlen = orec.olen then 15 for l in 1..orec.olen loop 16 if substr(drec.driver_name, l,1) = substr(orec.owner_name, l,1) then 17 match:='Y'; 18 else 19 match:='N'; 20 end if; 21 end loop; 22 end if; 23 24 select count(*) into curr_ct from new_table where matched_name = orec.owner_name; 25 if match = 'Y' and curr_ct = 0 then 26 insert into new_table (matched_name) values (orec.owner_name); 27 end if; 28 29 end loop; 30 31 end loop; 32 33 commit; 34 35 end; 36 / PL/SQL procedure successfully completed. Elapsed: 00:00:47.08 SQL> SQL> select count(*) from new_table; COUNT(*) ---------- 970 Elapsed: 00:00:00.00 SQL> David Fitzjarrell -- You received this message because you are subscribed to the Google Groups "Oracle PL/SQL" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/Oracle-PLSQL?hl=en
