PL/SQL – Interazioni col DB

PL/SQL è integrato completamente con Oracle, quindi è possibile eseguire tutte le operazioni sui dati e sugli oggetti del database.

TransazioniCOMMIT: salva tutti i cambiamenti dall’ultimo COMMIT o ROLLBACKROLLBACK: annulla tutti i cambiamenti dall’ultimo COMMIT o ROLLBACKROLLBACK TO SAVEPOINT name: annulla tutti i cambiamenti dal SavepointSAVEPOINT name: Crea un Savepoint SET TRANSACTION type: permette di inizre una sessione READ ONLY, READ WRITE

o con impostazioni avanzateLOCK TABLE name IN mode: permette di blocare una tabella con una certa modalità

CURSORIUn cursore è un riferimento all’area di lavoro associata con un’istruzione

SQL.I cursori possono essere impliciti o espliciti, ma nel seguito considereremo

solo quelli espliciti. CURSOR employee_cur IS SELECT * FROM employee;Il cursore poi può essere aperto:

OPEN employee_cur;Quindi si possono recuperare dei dati con un FETCH:

FETCH employee_cur INTO employee_rec;Infine il cursore si può chiudere

CLOSE employee_cur;

Cursori con e senza parametri

Senza parametriCURSOR company_cur ISSELECT company_id FROM company;

Con parametri (permette di filtrare i dati estratti)CURSOR name_cur (company_id_in IN NUMBER)ISSELECT name FROM companyWHERE company_id = company_id_in;

Con tipo di ritornoCURSOR emp_cur RETURN employee%ROWTYPEISSELECT * FROM employeeWHERE department_id = 10;

Variabili nei cursoriDECLARE

/* A local PL/SQL variable */projected_bonus NUMBER := 1000;/*Cursor adds $1000 to the salary of each employee hired more than 3 years ago.*/CURSOR employee_curIS

SELECT employee_id, salary + projected_bonus new_salary, /* Column alias */ :review.evaluation /* Bind variable */FROM employeeWHERE hiredate < ADD_MONTHS (SYSDATE, −36);

(Le variabili non possono avere lo stesso nome delle colonne delle tabelle)

Apertura dei cursoriL’apertura di un cursore avviene semplicemente con l’istruzione OPEN, eventualmente passandogli dei parametri, tuttavia è opportuno testare che il cursore non sia stato già aperto:IF NOT company_cur%ISOPENTHEN

OPEN company_cur;END IF;

Fetching dei cursoriIl cursore è un indice su una tabella virtuale recuperata da una SELECT. Per recuperare i dati nelle righe selezionate si deve operareb un FETCH.

FETCH company_cur INTO company_rec;Il fetch può avvenire verso record, verso variabili ‘scalari’ o anche verso variabili di tipo %ROWTYPE.

L’operazione di fetch è possibile fino a che non si supera l’ultima riga dopo di che l’attributo %NOTFOUND avrà il valore TRUE

Alias per le colonneDECLARE

CURSOR comp_cur ISSELECT company_name, SUM (inv_amt) total_salesFROM company C, invoice IWHERE C.company_id = I.company_idAND I.invoice_date BETWEEN '01−JAN−1994' AND '31−DEC−1994';comp_rec comp_cur%ROWTYPE;

BEGINOPEN comp_cur;FETCH comp_cur INTO comp_rec;

...

IF comp_rec.total_sales > 5000THENDBMS_OUTPUT.PUT_LINE(' You have exceeded your credit limit of $5000 by ' ||TO_CHAR (5000−company_rec.total_sales, '$9999'));END IF;

END;

Attributi dei cursori%FOUND TRUE se la riga viene recuperata correttamente%NOTFOUND TRUE se la riga non viene recuperata%ROWCOUNT Numero di righe recuperate%ISOPEN TRUE se il cursore è aperto

Esempio di uso di FOUNDOPEN caller_cur;LOOP

FETCH caller_cur INTO caller_rec;EXIT WHEN NOT caller_cur%FOUND;

UPDATE call SET caller_id = caller_rec.caller_idWHERE call_timestamp < SYSDATE;

END LOOP;CLOSE call_cur;

SELECT FOR UPDATEL’operazione di SELECT non pone alcun lock sulle righe selezionate, ma il alcune case si vuole apportare modifiche su tali righe e un blocco su esse diviene inevitabile: per ottenere questo si aggiunge la clausola FOR UPDATE.CURSOR toys_cur IS

SELECT name, manufacturer, preference_level, sell_at_yardsale_flag

FROM my_sons_collectionWHERE hours_used = 0FOR UPDATE;

CURSOR fall_jobs_cur ISSELECT task, expected_hours, tools_required, do_it_yourself_flagFROM winterizeWHERE year = TO_CHAR (SYSDATE, 'YYYY')FOR UPDATE OF task;

Il rilascio del blocco su una riga avviene all’atto dell’esecuzione del COMMIT sul UPDATE che riguarda la riga stessa.

FOR per i CURSORIIl ciclo for può essere usato in modo molto utili per scorrere i cursori all’interno dei loro limiti:DECLARE

CURSOR occupancy_cur ISSELECT pet_id, room_numberFROM occupancy WHERE occupied_dt = SYSDATE;

BEGINFOR occupancy_rec IN occupancy_curLOOP

update_bill (occupancy_rec.pet_id, occupancy_rec.room_number);

END LOOP;END;

Exception HandlingLa gestione delle Eccezioni in PLSQL è molto efficiente e simile a quella realizzata in linguaggi come java, e .NET.La struttura di un programma è:DECLARE... declarations ...BEGIN... executable statements ...[ EXCEPTION... exception handlers ... ]END;Il blocco EXCEPTION ha il seguente formato:EXCEPTION

WHEN NO_DATA_FOUNDTHEN

executable_statements1;WHEN payment_overdueTHEN

executable_statements2;WHEN OTHERSTHEN

executable_statements3;END;

Eccezioni standardOracle mette a disposizione numerosi tipi di eccezioni predefinite, di sistema.CURSOR_ALREADY_OPENDUP_VAL_ON_INDEXINVALID_CURSORINVALID_NUMBERLOGIN_DENIEDNO_DATA_FOUNDNOT_LOGGED_ON……

L’utente può comunque definire delle eccezioni personalizzate

Eccezioni personalizzateE’ possibile dichiarare una variabile di tipo EXCEPTION

overdue_balance EXCEPTION;BEGIN

... executable statements ...LOOP...

IF ... THENRAISE overdue_balance;

END IF;END LOOP;

EXCEPTIONWHEN overdue_balance THEN ...

END;