Cache Table data and performe search on it

0
votes

I am writing a SQL procedure to perform following task.

  1. Read data from a table A. Which have columns are Col1, Col2, Col3, Col4, Col5, Col6.
  2. I have to search primary key (number) in table B and filed Col1,Col2,Col3.
  3. Insert the primary key,COL4,COL5,COL6 in table 6.

Table A can contain any number of combination of COL1, COL2 and COL3.

Here I am reading all data from table A but I don’t want to fire a search query on table B every time to find out the key in table B. But I like to store/fetch data of table B in somewhere (map etc) and then want to search there and insert in the table C. Is it possible in PL/SQL?

In short, I want to cache table B data and perform the search and get there data.

1
oracleplsqlplsqldeveloper
Why aren't you just joining the tables together in your query - letting the database do what it's good at? Do you really need any PL/SQL here - sounds like you could insert ... select in one simple step? – Alex Poole
Thanks Alex. I tried it but issue was if reference is missing in the TABLE B then I wouldn't able to figure out. – CrazyC
Isn't that what outer joins are for? I'm sure caching and RobertK's answer will work for you but you might want to look into alternatives for the future. – Alex Poole

1 Answers

1
votes

We do this quite a lot using arrays to cache the data for lookup values, eg we have a t_sites table and we want to cache the entire data set, the primary key is varchar2(10) so we create an array indexed by varchar2(10) ... here is an example of the code :

declare
-- type and array to hold all the data from the query
type sites_rec_t is record (
    addr_id             t_sites.addr_id%type,
    code                t_sites.code%type,
    description         t_sites.description%type,
    pm_telephone        t_sites.pm_telephone%type
);

-- array for the lookups
type sites_t is table of sites_rec_t index by varchar2(10);
a_sites sites_t;
v_site_arr_key  varchar2(10);

begin

-- populate the arrays
for r in ( select addr_id, code, description, pm_telephone from t_sites ) loop
    a_sites(r.code).addr_id := r.addr_id;
    a_sites(r.code).code := r.code;
    a_sites(r.code).description := r.description;
    a_sites(r.code).pm_telephone := r.pm_telephone;
end loop;

-- example of how to loop through the varchar indexed array as you cant do for i in 1 .. array.count loop
v_site_arr_key := a_sites.first;
while v_site_arr_key is not null loop
    dbms_output.put_line(v_site_arr_key || '=' || a_sites(v_site_arr_key).description || ', ' || a_sites(v_site_arr_key).addr_id);
    v_site_arr_key := a_sites.next(v_site_arr_key);
end loop;

end;