Il tipo Record in PL/SQL

martedì 7 aprile 2009 alle 07:19 | Pubblicato su PL/SQL | 1 commento

Alcuni giorni fa, scrivendo una procedura in PL/SQL mi sono inbattuto in un problema particolare con i record in PL/SQL. In realtà non mi sono subito reso conto che il problema era nel tipo record ed ho fatto molta fatica a capire bene che cosa sbagliavo io e che queli sono in confini del PL/SQL.

Come ho gia detto ieri il PL/SQL è un linguaggio potente, ma nasconde un sacco di insidie per chi non lo conosce bene e non neconosce i confini, in particola nella interazione con il motore SQL.

Finalmente oggi sono riuscito a fare una serie di test per capire bene cosa si può fare e cosa non si può fare con i record, la versione Oracle alla quale mi riferisco è la 10.2.0.4, in 11g non ho fatto dei test ma ho visto che le cose sono diverse.

Cerco di dare in modo molto sintetico la specifica di ciò che voglio (o volevo) fare:

ho due tabelle A e B, A ha una chiave esterna verso B. Devo travare il contenuto della join tra A e B su una tabella C, a intervalli regolari. Le tabelle A e B vengono modifica in modi diversi, nella tabella A vengono solo inseriti nuovi record, nella tabella B vengono inseriti e aggiornati, in momenti diversi. A è una tabella molto grande, B è più piccola.

La mia idea, sulla cui legittimità per ora sorvoliamo, era questa:

1) apro un cursore sulla A che prende solo i record inseriti dall’ultima elaborazione

2) in un ciclo  faccio un bulk collect in una nested table con il “LIMIT“, nello stesso ciclo con una forall vorrei inserire i record di A in C.

Riporto un test di questa procedura (non fedelissimo):

conn scott/tiger
create table emp2 as select * From emp where 1=0;

create or replace procedure test1 is
cursor emp_cur is
select
EMPNO     ,
ENAME     ,
JOB       ,
MGR       ,
HIREDATE  ,
SAL       — ,
— COMM      ,
— DEPTNO
from emp;
type nt_emp_rec_type is table of emp_cur%rowtype;
nt_emp_var nt_emp_rec_type;
begin
open emp_cur;
loop
nt_emp_var := nt_emp_rec_type();
fetch emp_cur bulk collect into nt_emp_var limit 1000;
forall i IN nt_emp_var.first..nt_emp_var.last
insert into EMP2
(
EMPNO     ,
ENAME     ,
JOB       ,
MGR       ,
HIREDATE  ,
SAL       –,
— COMM      ,
— DEPTNO
)
values nt_emp_var(i);
EXIT WHEN emp_cur%NOTFOUND;
END LOOP;
close emp_cur;
end;
/

Questa procedura da il seguente errore di compilazione:

SCOTT@perseo10 > show errors
Errori in PROCEDURE TEST1:

LINE/COL ERROR
——– —————————————————
21/5     PL/SQL: SQL Statement ignored
32/2     PL/SQL: ORA-00947: non ci sono abbastanza valori

Ora, nella procedura ci sono un po’ di “variabili” in gioco, prinpipalmente il fatto che adopero i costrutti bulk, per cercare di migliorare le prestazioni, però stando alla documentazione, che dice:

If you issue the INSERT through the FORALL statement, you can insert values from an entire collection of records. The number of fields in the record must equal the number of columns listed in the INTO clause, and corresponding fields and columns must have compatible datatypes. To make sure the record is compatible with the table, you might find it most convenient to declare the variable as the type table_name%ROWTYPE.

sembrerebbe che ciò che ho scritto sia lecito. In realtà non lo è, infatti il secondo esempio che si trova su questa pagina sul sito di Oracle, c’è scritto:

YOU CAN'T BIND AN EXPLICIT COLUMN LIST TO A RECDORD

Con un po’ di ricerche ho scoperto che c’è un modo per aggirare questo limite, non so il perché e non ne ho trovata spiegazione, però ecco due link dove c’è la soluzione:

1) oraclequirks (Gary Mayers nei commenti da la soluzione)

2) Oracle Forums

Ed ecco il test:

create or replace procedure test2 is
cursor emp_cur is
select
EMPNO     ,
ENAME     ,
JOB       ,
MGR       ,
HIREDATE  ,
SAL       — ,
— COMM      ,
— DEPTNO
from emp;
type nt_emp_rec_type is table of emp_cur%rowtype;
nt_emp_var nt_emp_rec_type;
begin
open emp_cur;
loop
nt_emp_var := nt_emp_rec_type();
fetch emp_cur bulk collect into nt_emp_var limit 1000;
forall i IN nt_emp_var.first..nt_emp_var.last
insert into
(select
EMPNO     ,
ENAME     ,
JOB       ,
MGR       ,
HIREDATE  ,
SAL       –,
— COMM      ,
— DEPTNO
from emp2 where 1=0)
values nt_emp_var(i);
EXIT WHEN emp_cur%NOTFOUND;
END LOOP;
close emp_cur;
end;
/
SCOTT@perseo10 > show errors
Nessun errore.

sostituendo a <nome tabella> (<elenco campi>) la query (select <elenco campi> from <nome tabella>) è possibile legare una lista esplicita di colonne a un record.

Superato il problema degli insert arriva il secondo problema, la fase due della mia procedura. L’idea adesso è quella di aprire un cursore sulla tabella B con i record modificati dall’ultima elaborazione ed andare a fare un update sulla tabella C. Anche qui vorrei sfruttare l’efficenza dei costrutti bulk, ma qui neppure la documentazione da scampo (ma anche qui ho dovuto sbattere il naso, a causa dell’errore:

PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records

per cui questa implementazione non funziona:

create or replace procedure test3 is
cursor emp_cur is
select
EMPNO     ,
— ENAME     ,
— JOB       ,
— MGR       ,
— HIREDATE  ,
— SAL       — ,
COMM      ,
DEPTNO
from emp;
type nt_emp_rec_type is table of emp_cur%rowtype;
nt_emp_var nt_emp_rec_type;
begin
open emp_cur;
loop
nt_emp_var := nt_emp_rec_type();
fetch emp_cur bulk collect into nt_emp_var limit 1000;
forall i IN nt_emp_var.first..nt_emp_var.last
update emp2
set
comm = nt_emp_var(i).comm,
deptno = nt_emp_var(i).deptno;
where empno = nt_emp_var(i).empno;
EXIT WHEN emp_cur%NOTFOUND;
END LOOP;
close emp_cur;
end;
/

Fra l’altro mi sembra che qui ci sia anche la violazione della restrizione:

Within a FORALL loop, you cannot refer to the same collection in both the SET clause and the WHERE clause of an UPDATE statement. You might need to make a second copy of the collection and refer to the new name in the WHERE clause.

Qui subentra un trucco interessante, basato sull’utilizzo della funzione SQL TREAT, che ho trovato su AskTom:

create type emp_obj_type is object (
empno    number(4),
comm        number(7,2),
deptno    number(2));
/
create type nt_emp_obj_type is table of emp_obj_type;
/

create or replace procedure test7 is
cursor emp_cur is
select
EMPNO     ,
— ENAME     ,
— JOB       ,
— MGR       ,
— HIREDATE  ,
— SAL       — ,
COMM      ,
DEPTNO
from emp;
nt_emp_var nt_emp_obj_type;
begin
select emp_obj_type(EMPNO     ,
COMM      ,
DEPTNO )
bulk collect into nt_emp_var
from emp;
forall i IN nt_emp_var.first..nt_emp_var.last
update emp2
set
comm  = treat(nt_emp_var(i) as emp_obj_type).comm,
deptno = treat(nt_emp_var(i) as emp_obj_type).deptno
where
empno = treat(nt_emp_var(i) as emp_obj_type).empno;
end;
/

Procedura creata.

SCOTT@perseo10 > show errors
Nessun errore.

Qui c’è un primo grosso problema: non riesco (e non sembra possibile) ad usare un cursore esplicito con un bulk collect in una nested table con il “LIMIT“. Non mi piace il fatto di usare tipi esterni (SQL), non so se rimanga più efficente. Se la tabella è molto grande (non è il mio caso secondo le mie specifiche iniziali) mi carico una nested table molto grande, cosa che non va molto bene. Trovo però affascinante come questo trucco riesca a permettere di superare uno dei limiti della FORALL.

E’ chiaro che devo imparare ancora qualcosa sul PL/SQL. Anche l’ultimo esempio che ho riportato mostra come l’interazione tra motore PL/SQL e motore SQL nasconde molte insidie. Questo post, nel pieno spirito di questo blog ha più lo scopo di appunto mio che spiegazione, in quanto in effetti non è che spieghi molto.

1 commento »

RSS feed for comments on this post. TrackBack URI

  1. Vediamo se ho capito:

    Prima cuoi calcolare il join tra le tabelle A e B (il risultato è JF – Join First): JF = Join A,B

    Poi il risultato deve essere rimesso in Join con C (vuoi le righe di C che fanno match con JF (il risultato è JS – Join Second): JS = Join JF,C

    Se è questo potresti fare qualcosa del tipo

    with JF as (select col1 jf_col1, col2 jf_col2 from A, B where a.colA1=bcolB1 and a.colA2=b.colA2)
    select C.colC1, C.colC2
    from JF, C
    where jf_col1=C.colCx and jf_col2=C.colCy

    credo debba funzionare.


Lascia un commento

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...

Blog su WordPress.com.
Entries e commenti feeds.

%d blogger cliccano Mi Piace per questo: