Retrieve multiple records with pl/sql tables 2

[tokerago13]

Hi,
I have never used plsql tables before. I am trying to retrieve the last two rows from a cursor. What I intend to do is use the individual variables for each of the rows to carry out some calculations between the two rows. The set of record being retrieved is a mix of old and new data. By adding these, I can get a total number which is what I require.

What I have done so far is thus:
dfine the variables

holiday_rec c1%rowtype;

TYPE t_holidayrec IS TABLE OF holiday_rec index by binary_integer;

v_holidaytab t_holidayrec;

last_hol_rec holiday_rec;
prev_hol_rec holiday_rec;

Hre I'm tryng to fetch the records... What next

for holiday_rec in c1 loop

v_holidaytab := 'x';

v_holidaytab(x).choice := x.col1;
v_holidaytab(x).enrt_cvg_strt_dt := x.col2;
v_holidaytab(x).enrt_cvg_thru_dt := x.col3;
v_holidaytab(x).prtt_enrt_rslt_stat_cd := x.col4;
v_holidaytab(x).effective_start_date := x.col5;
v_holidaytab(x).effective_end_date := x.col6;
v_holidaytab(x).lf_evt_ocrd_dt := x.col7;
v_holidaytab(x).asg_effective_start_date := x.col8;
v_holidaytab(x).person_id := x.col9;

v_count := v_count + 1;



end loop;

Any help will be much appreciated.

Tny

 

[carp]

Since I don't know your table structure or the query that populates your C1 cursor, I will give you an example and you can adjust it to fit your needs.
If you are loading a PL/SQL table and need to get the last two records, the following should do it for you:

DECLARE
   TYPE h_tab_type IS TABLE OF my_table%ROWTYPE INDEX BY BINARY_INTEGER;
   h_tab H_TAB_TYPE;
   last_hol_rec my_table%ROWTYPE;
   prev_hol_rec my_table%ROWTYPE;
BEGIN
  SELECT * BULK COLLECT INTO h_tab 
    FROM  my_table
   ORDER BY x;
  prev_hol_rec := h_tab(h_tab.count - 1);
  last_hol_rec := h_tab(h_tab.count);
END;

The BULK COLLECT clause will automatically load the results of the query into your PL/SQL table. Then you can pick off the last two rows as shown.

 

[SimonSellick]

Do you mean the last two, or for each row, the previous two? If you really want the last two, change the ORDER BY to DESC and take the first two instead - might save a bit of time! For the previous two, adapt carp's code to have two stored intermediate rows, or use his array with bulk binding and use indexes i, i-1 and i-2 to get at each row.

HTH

 

[tokerago13]

Hi,
Thanks for this. One question, given that I'm using mutiple tables how do I use the bulk collect. It seems the syntax given is based on one table.
Thanks again

 

[carp]

Hmmm. Well, I've never tried this before, but it seems to me that you could define your cursor (as you currently do), define the PL/SQL table and records as c1%ROWTYPE (as you currently do), and then use the same query defined in your cursor to BULK COLLECT the data into the table. In other words, you never open the cursor - it's just there to simplify defining your record structure. The alternative would be to explicitly define the structure of your record - which probably involves more typing and is less flexible if you need to change your query later on.

 

[tokerago13]

Thanks everyone for this. The records being retrieved are the last two records.
New issue, given that I have not used pl/sql tables, the requirements have now changed.
I need to capture all the data from the cursor and use them cummulatively. That is, I need to add the value of a column in the first row to the same value in the second row and so on. I will then use the final data cummulatively.
Any takers? If more expalnation is required I can provide the code...
Thanks

 

[SantaMufasa]

Toker...

Must you display intermediate cummulations or are you interested just in the final cummulative results? If the latter, then a SELECT with the SUM() function works just fine, but for the former, you can just use individual counters of the intermediate values.

How do these ideas sound to you? Do you understand how to implement them?

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[tokerago13]

Hi,
May be I did not explain very well what I intend to do. I actually need to process the individual columns of every row individually. After this has been done a final variable, for instance a number is generated for each row. If I do have 4 rows then the cummulative of the 4 rows is what is needed which I'll use for further processing... Does any of this make sense?
T

 

[SantaMufasa]

Toker,

It absolutely makes sense...I just could not presume that's what you wanted without your confirmation.

The most efficient method to achieve what you want is to include "incremental additions" of values while you process individual records. Here sample code that does the type of thing you want:

set serveroutput on format wrap
declare
   sal_total number := 0;
begin
   for r in (select last_name, salary
               from s_emp
              where salary > 1500
              order by salary desc) loop
      sal_total := sal_total+r.salary;
      dbms_output.put_line(to_char(r.salary,'$99,999')||': '||r.last_name);
   end loop;
   dbms_output.put_line('-------');
   dbms_output.put_line(to_char(sal_total,'$99,999')||': Total');
end;
/

  $3,328: Velasquez
  $1,550: Ropeburn
  $1,525: Nguyen
  $1,515: Sedeghi
-------
  $7,918: Total

Let us know if this resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[tokerago13]

Thanks a lot everyone for your input. I've got the whole process working as it should. Going through all the rows.
Thanks