Some tips when working with PLSQL

I decided to write a series of small notes thanks to the post Good habits in PL / SQL , whose tips I will discuss.

In this part we will discuss:

1. Nuances of% TYPE /% ROWTYPE
2. Select for update
3. Work with collections

1. Nuances of% TYPE /% ROWTYPE

Make a test case - create a plate with a field of type Integer and add a test value to it:

create table test_integer(a integer);
create type integer_table as table of integer;
/
insert into test_integer values (1E125);
commit;
select * from test_integer;

As you can see, the meaning is perfectly inserted. And now let's try to get it in the pl / sql block:

declare
 l_a test_integer.a%type;
begin
 select a into l_a from test_integer;
 dbms_output.put_line('Все ок, значение=' || l_a);
exception
 when others then
    dbms_output.put_line('Все плохо! '||sqlerrm);
end;
/

Everything is bad! ORA-06502: PL / SQL: numeric or value error: number precision too large
PL / SQL procedure successfully completed.
Elapsed: 00: 00: 00.02

The reason for the error lies in the fact that INTEGER in SQL is number (*, 0) - a floating-point number with scale = 0, and INTEGER in PL / SQL is “SUBTYPE INTEGER IS NUMBER (38,0);”.

What about the type from that article " CREATE TYPE id_list IS TABLE OF INTEGER; "?
Naturally, this is a collection of pl / sql integers:

DB1> declare
  2 l_int_tab integer_table;
  3 begin
  4 select a
  5 bulk collect into l_int_tab
  6 from test_integer;
  7 dbms_output.put_line ('Everything is OK!');
  8 exception when others then
  9 dbms_output.put_line ('Everything is bad! Exception:' || sqlerrm);
 10 end;
 eleven /
Everything is bad! Exception: ORA-06502: PL / SQL: numeric or value error: Bulk bind: Error in define
PL / SQL procedure successfully completed.
Elapsed: 00: 00: 00.00

I created the id_list type as integer_table - it's just my whim to create collection types with the _TABLE postfix. In addition, I usually do not create a type for each table of table of table_name% rowtype, but only for frequently used ones or if these types are used in the parameters of procedures and functions. By the way, if you have oracle <11g and you create the type in the package and then use it in the parameters or fields of the circuit level objects, then this automatically creates the type at the circuit level with "multi-valued" type names: SYS_PLSQL_2906526_17_1, in 11g these types are simply have hidden.

2. Select for update

It is no secret that if we want to update something in the selection, then we must use select for update to block the entire rows or fields in them. For example, in that procedure from the article under discussion there is no for update clause and the results can be enchanting (salary changes not for those employees, if the department changed in the parallel session, loss of salary changes in case of parallel changes in salary by another session depending on the sequence of the commit in 1- 1st or 2nd session), so select should be rewritten like this:

SELECT e.employee_id,e.salary,e.hire_date
FROM employee e
WHERE department_id = dept_in
FOR UPDATE OF e.salary;

Note that “OF e.salary ” does not mean that only the e.salary field will be blocked (this is not possible), all rows of the table with e alias will be blocked:

The columns in the OF clause only indicate which table or view rows are locked.

Specifying e.salary here only means to whom to additionally apply the statement restart mechanism, and therefore, in this case, as in all cases when you need to lock the rows of all tables from FROM, and the variable fields are listed in the select list, you can omit “OF ... "
By the way, with for update you can run into ORA-00060: deadlock detected.
Consider an example:

create table test_integer
pctfree 99
pctused 1
as
select level                        id,
      trunc(10*dbms_random.value) val,
      rpad('x',100)                padding
from dual connect by level <=100
/
create index test_indeger_idx on test_integer(id)
/
exec dbms_stats.gather_table_stats(ownname => user,tabname => 'TEST_INTEGER',cascade => true);

Emulate the delay will be a function:

create or replace function integer_delay(p_i in integer,p_interval in integer)
return integer
is
begin
 dbms_lock.sleep(seconds => p_interval);
 return p_i;
end;


And run in parallel sessions:
1:

begin
 for rec in (
             select --+ index(test_integer_idx)
              id,val
             from test_integer
             where integer_delay(id,1) in (100,30,1)
             order by id desc
             for update
 )
 loop
    dbms_output.put_line(rec.id);
 end loop;
 commit;
end;

2.

begin
 for rec in (
             select --+ index(test_integer_idx)
              id,val
             from test_integer
             where integer_delay(id,1) in (100,30,1)
             order by id desc
             for update
 )
 loop
    dbms_output.put_line(rec.id);
 end loop;
 commit;
end;

Sometimes when speed is important, you can use the options for for update: nowait or skip locked. By the way, it should be noted that in the case of using skip locked, window functions and rownum will work on a whole set, not excluding blocked ones. Example:
1st session:

select * from test_integer where id in (1,3,4,8) for update;

2nd session:

select id
     ,row_number()over (order by id) rn
     ,rownum
from test_integer t
where id < 10
order by id
for update skip locked

3. Work with collections

It was very funny to read in that post that you always need to work with collections, and even process them whole. About “FOR LOOP” and the optimal amount for bulk operations Tom covers here and find out the number you need directly in your specific situation.
In the case of FORALL, it is necessary to clarify several points at once:

1. FORALL is not atomic - that is, if the data that it will see is not at the time the FORALL starts, but at the time it runs its particular set collection cycle, and it will update later when it receives the ready modified set (this is for read committed, for serializable get ora-08177).
   
   Let's run the example again with test_ab, only first set b = 1 again for all columns.
   
   
   • We start the first session:
     

     declare
 type number_table is table of number;
 
 procedure bulk_update(p_nt in number_table) is
 begin
    forall n in p_nt.first..p_nt.last
     update test_ab
             set b=b*10
             where a=p_nt(n)
             and a=integer_delay(a,10);
 end bulk_update;
begin
 bulk_update(number_table(1,3,7,10));
end;

     
     
   • And then after a couple of seconds we start the second session and quietly change any b from the same set:
     

     update test_ab set b=10 where a=7;

     
     As you can see, until we complete the second transaction, the first will be blocked, and in this case the values ​​of the second will be visible in the first transaction, although we launched it last. You can, of course, first lock the table test_ab, for example, through the same select for update, but is it so convenient and is it always necessary?
     
   
   
2. Management of execution plans in FORALL is complicated.
   In the case of using various SQL variants, we can relatively easily fine-tune the plan for mass updates, which is difficult in the case of forall, on the other hand, it is possible that nothing needs to be improved (for example, in the case of index range scan).
   

In general, this moment is ambiguous and categorically it is impossible to advise one thing in all cases.

PS. To be continued ...

The code in the article is designed using Source Code Highlighter .