Sqlplus

Shell Programming and Scripting
1

Hi all,
I am new to SQLPLUS, can anyone tell me what is the following codes doing?

DECLARE
cursor c1 is select bts_int_id,
max(ave_busy_tch/res_av_denom14) maxBusyTch
from p_nbsc_res_avail
where to_char(period_start_time,'yyyymmdd')=to_char((sysdate-1),'yyyymmdd')
group by bts_int_id;
BEGIN
FOR x IN c1 LOOP
update RNP_temp_table set
bts_id = x.bts_int_id,
max_busy_tch=x.maxBusyTch
where bts_id = x.bts_int_id;

    IF SQL%NOTFOUND THEN
      insert into RNP\_temp_table
            \( bts_id,
              max\_busy_tch\)
      values
            \( x.bts\_int_id,
              x.maxBusyTch\);
    END IF;
    COMMIT;

END LOOP;

COMMIT;
END;
/

2

Hi friend,

This is a procedure which open a cursor, using select statement from p_nbsc_res_availTable where users fetch data where Period_start_time is equal to previous date.
Then using this cursor a loop update records in RNP_temp_table Table. if it found in cursor aotherwise that record inserted. Then it commit your changes. But i think you should remove first commit that is inside loop your next commit commit will fix all chages at the end of the procedure.

Thanks. :slight_smile:

3

Thanks for your reply. Now I have a better picture on how this things work. Between, is cursor must be declare every time like in the script?

declare cursor c1 is select .......

and also what if i remove the both commit command, will the script give me same result?

and is it for...loop, if...then can only be written after begin command?

Thanks again for your generous help!

:slight_smile:
Anthony Kiu

4

Hi,
see cursor only require when you have to traverse in between the records fetched from Table using select command. yeaa obviously cursor only be defined using select command only because it is something like a pointer in memry location where jump on evry row. yeaa it is a common practice to open a cursor to run a loop.

see commit will be used to fix your changes. you can commt later also it does n't have an business with your procedure and loop, but it certainly ensure security.

Thanks :slight_smile:
Mukund Ranjan.

5

Hi friend,

Thanks a lot for you help. It helps me to understand more about sqlplus.

:slight_smile:
Anthony