Some tips for working with PLSQL

I decided to write a cycle of small notes thanks to the post Good habits in PL / SQL , which tips and consider.

In this part we will discuss:

1. Nuances of% TYPE /% ROWTYPE

Let's make a test case - create a label with an Integer field and enter the test value into 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 value 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 error
 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) is a floating-point number, which has 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 integer:

 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 table% 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 a type in a package and then use it in parameters or fields of schema-level objects, it automatically creates a type at the schema level with “multi-valued” type names: SYS_PLSQL_2906526_17_1, in 11g these types are just have hidden.

2. Select for update

It’s no secret that if we want to update something in the sample, then we need to use select for update to block the necessary whole lines 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 (the salary change is not for those workers, if a department changes in a parallel session, the salary change loss in case of a parallel salary change by another session depending on the order of 1- 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 fields will be blocked (this is not possible), all rows of the table with alias e will be blocked:

Where are the tables or view rows are locked.

Note here e.salary means only to whom 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 changeable fields are listed in the select list, you can not specify “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:
one:

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 parameters for for update: nowait or skip locked. By the way, it should be noted that if skip locked is used, window functions and rownum will work on the whole set, not excluding blocked ones. Example:
Session 1:

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

Session 2:

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 also process them entirely. About "FOR LOOP" and the optimal amount for bulk operations Tom is covered here and find out the number you need directly in your particular situation.
In the case of FORALL, several points need to be clarified at once:

FORALL is not atomic - that is, if the data it will see will not be at the moment when FORALL starts to be executed, but at the time of the execution of a particular cycle of receiving a set, it will be updated later when it receives a ready modified set (this is for read committed, for serializable we get ora-08177).

Let's run the example again with test_ab, but 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 one will be blocked, and the second transaction will show the values of the second one, although we launched it last. You can, of course, first block the test_ab table, for example, through the same select for update, but is it convenient and always necessary?
Managing execution plans in FORALL is complicated.
In the case of using different SQL variants, we can relatively easily adjust the plan for mass update, 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 is an ambiguous moment and categorically it is impossible to advise one thing in all cases.

Ps. To be continued…

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

Source: https://habr.com/ru/post/125893/

All Articles

Some tips for working with PLSQL

1. Nuances of% TYPE /% ROWTYPE

2. Select for update

3. Work with collections

More articles: