Diapositiva 1

Basi di Dati e Sistemi Informativi
Sviluppo di applicazioni per Basi di dati
Giuseppe Loseto
Corso di Laurea in Ing. Informatica – Ing. Gestionale Magistrale
Uso di SQL nei linguaggi
di programmazione
 Interazione con basi di dati utilizzando SQL non è diretta, ma attraverso
l’uso di programmi applicativi
 Linguaggi di quarta generazione (4GL) come Informix4GL che
permettono di sviluppare complete applicazioni per la gestione di basi di
dati
 SQL da solo non è Touring-Completo
 Impedence mismatch (“disaccoppiamento di impedenza”) fra base di
dati e linguaggio di programmazione
 I linguaggi operano tipicamente su singole variabili o oggetti mentre SQL
opera su relazioni (insiemi di tuple)
 In alcuni approcci la soluzione ai problemi di interazione è rappresentata
dall’uso dei cursori
Basi di Dati e
Sistemi Informativi
Sviluppo di applicazioni per Basi di dati
2 di 31
Approcci Principali
 SQL embedded (SQL “integrato”)
 SQL Statico
 SQL Dinamico
 Call Level Interface (CLI)
 Stored Procedures
Basi di Dati e
Sistemi Informativi
Sviluppo di applicazioni per Basi di dati
3 di 31
SQL Embedded






Tecnica sviluppata sin dagli anni ‘70
SQL è “ospitato” in un linguaggio di programmazione, chiamato
linguaggio ospite (Java, Pascal, Cobol, C, C++, …), in quanto questi
standard hanno definito l’interfaccia con SQL
Il codice sorgente contiene sia codice nel linguaggio di programmazione
che codice SQL
Un preprocessore, dipendente sia dal linguaggio ospite che dalla
piattaforma del DBMS, viene usato per analizzare il codice
Le istruzioni SQL sono tradotte con chiamate alle funzioni di una API del
DBMS prima della compilazione vera e propria
Ogni programma che usa Embedded SQL deve:
1. Definire quale database usare
2. Connettersi al database
3. Effettuare le operazioni sul database (usando cursori e statement SQL)
4. Disconnettersi dal database
Basi di Dati e
Sistemi Informativi
Sviluppo di applicazioni per Basi di dati
4 di 31
Esempio in C di SQL statico (1/2)
#include<stdlib.h>
#include<stdio.h>
#include<sqlenv.h>
main()
{
exec sql begin declare section; /*dichiarazione variabili*/
char *NomeDip = “DEE";
int Id_Dip = 3;
exec sql end declare section; /*fine della dichiarazione variabili*/
exec sql connect to esempio@azienda_esempio;
if (sqlca.sqlcode != 0) {
/*sqlca (SQL Communication Area) è una struttura di dati predefinita che mantiene le informazioni sulla comunicazione tra SQL e
programma, sqlcode=0 no errore, altrimenti codice dell’errore*/
printf(“Errore di connessione al DB\n"); }
else {
exec sql insert into Dipartimento
values(:NomeDip,:Id_Dip);
/* notare l’uso del : per utilizzare le variabili del programma ospite in SQL*/
exec sql disconnect all;
}
}
Basi di Dati e
Sistemi Informativi
Sviluppo di applicazioni per Basi di dati
5 di 31
Esempio in C di SQL statico (2/2)
main()
{
exec sql connect to universita
user pguser identified by pguser;
exec sql create table studente (matricola integer primary key,
nome varchar(20),
annodicorso integer);
exec sql disconnect;
}
/* These include files are added by the preprocessor */
#include <ecpgtype.h>
#include <ecpglib.h>
#include <ecpgerrno.h>
#include <sqlca.h>
main()
{
ECPGconnect(__LINE__, "universita" , "pguser" , "pguser" , NULL, 0);
ECPGdo(__LINE__, NULL, "create table studente ( matricola integer primary key , nome varchar ( 20 ) , annodicorso
integer )", ECPGt_EOIT, ECPGt_EORT);
ECPGdisconnect(__LINE__, "CURRENT");
}
Basi di Dati e
Sistemi Informativi
Sviluppo di applicazioni per Basi di dati
6 di 31
Cursori
 Quando una query restituisce più tuple è necessario passarle al
programma ospite una per volta
 Si utilizza un cursore per accedere a tutte le tuple di una interrogazione
in modo globale (tutte insieme o a blocchi – è il DBMS che sceglie la
strategia efficiente)
 Il cursore passa poi una tupla per volta al programma
 Un cursore viene definito su una generica interrogazione mediante la
seguente sintassi
declare Cursor_Name [scroll ] cursor for
SelectSQL [for <read only| update [of attribute,{attribute}]>]
Dove, scroll indica che il cursore può muoversi liberamente sul risultato
della query, mentre for update indica che il cursore può essere utilizzato in
operazioni di aggiornamento permettendo di specificare eventualmente
gli attributi oggetto del comando.
Si osservi che per riga corrente si considera l’ultima riga letta.
Basi di Dati e
Sistemi Informativi
Sviluppo di applicazioni per Basi di dati
7 di 31
Utilizzo di un cursore (1/2)
 Apertura cursore ed esecuzione della query:
open Cursor_Name
 Utilizzo dei risultati (una ennupla alla volta):
fetch [position from] Cursor_Name into Variables
accede alla tupla corrente o a quella indicata da position e copia i valori
nelle variabili del programma ospite
 Position (utilizzabili se l’opzione scroll è stata impostata, altrimenti è
disponibile solo next):
 next (con riferimento a current indica la prossima tupla);
 prior (tupla precedente);
 first (prima tupla);
 last (ultima tupla);
 absolute integer_value (tupla posizionata al valore ordinale espresso)
 relative integer_value (tupla posizionata al valore ordinale espresso, rispetto alla
posizione corrente)
Basi di Dati e
Sistemi Informativi
Sviluppo di applicazioni per Basi di dati
8 di 31
Utilizzo di un cursore (2/2)
 Accedere alla tupla corrente di un cursore per effettuare un update o un
delete
Delete from NomeTabella where current of Cursor_Name
 Chiusura del cursore
close cursor Cursor_Name
Basi di Dati e
Sistemi Informativi
Sviluppo di applicazioni per Basi di dati
9 di 31
Esempio di cursore in C
……
printf(“nome della citta‘?”);
scanf(“%s”,&citta[0]);
/*creo il cursore*/
EXEC SQL DECLARE P CURSOR FOR SELECT NOME, REDDITO
FROM Impiegato
WHERE CITTA = :citta for UPDATE of REDDITO;
EXEC SQL OPEN P ; /* apro il cursore -> eseguo la query*/
EXEC SQL FETCH P INTO :nome, :reddito ; /*prelevo i dati*/
while (sqlca.sqlcode == 0)
{
printf(“Qual è l’aumento per %s? ”, nome);
scanf(“%d”,&aumento);
EXEC SQL UPDATE Impiegato SET REDDITO = REDDITO + :aumento
WHERE CURRENT OF P; /*eseguo l’update su current*/
EXEC SQL FETCH P INTO :nome, :reddito; ; /*prelevo i dati per la tupla immediatamente successiva*/
}
EXEC SQL CLOSE CURSOR P;
} …..
Basi di Dati e
Sistemi Informativi
Sviluppo di applicazioni per Basi di dati
10 di 31
SQL Dinamico
 Non sempre le istruzioni SQL sono note quando si scrive il
codice ospite
 SQL statico
 i nomi delle relazioni e degli attributi coinvolte nelle istruzioni SQL sono fissate
a priori
 l’unica parte delle istruzioni che può rimanere non nota a tempo di
compilazione è costituita dagli specifici valori da ricercare o da aggiornare
 SQL dinamico
 le istruzioni SQL sono generate a tempo di esecuzione
 non è quindi necessario specificare relazioni e attributi coinvolti in una
istruzione SQL prima della sua esecuzione
 degrado delle prestazioni
 operazioni eseguite immediatamente oppure preparate e poi eseguite (anche
più volte)
Basi di Dati e
Sistemi Informativi
Sviluppo di applicazioni per Basi di dati
11 di 31
Modalità di esecuzione
Le operazioni SQL possono essere

Con esecuzione immediata (in assenza di parametri)
execute immediate SQLStatement
Esempio:
Char *SQL_string=“delete from Impiegato where CF=27”;
exec sql execute immediate :SQL_string;
Con esecuzione differita e ripetibile (anche con parametri):
prepare CommandName from SQLStatement
execute CommandName [into TargetList][using ParameterList]
Esempio:
Prepare :trova_nome From “Select nome From Impiegato Where CF =?”
Execute :trova_nome into :nome_imp using :cf_imp
Deallocate prepare :trova_nome
Basi di Dati e
Sistemi Informativi
Sviluppo di applicazioni per Basi di dati
12 di 31
Call Level Interface

Application Programming Interface (API) per accedere ai database

Insieme di funzioni chiamabili direttamente dal linguaggio di programmazione
usato

Ogni DBMS mette a disposizione una libreria di funzioni, che seguono lo standard
CLI

Generalmente vengono usate per applicazioni basate su SQL dinamico
API
Oracle
Oracle
Prog
C
API
Interbase
Interbase
Basi di Dati e
Sistemi Informativi
Sviluppo di applicazioni per Basi di dati
13 di 31
Evoluzione di CLI

Utilizzare uno strato software che si interpone tra l’applicazione e i driver specifici
del database

Consente ai programmi applicativi di usare query standard SQL, che accederanno
al database, senza necessità di conoscere la particolare interfaccia proprietaria

Interfacciare qualsiasi database tramite un driver specifico (es. ODBC, JDBC)
Driver
Oracle
Prog
JAVA
API
Comune
Driver
Interbase
Basi di Dati e
Sistemi Informativi
Oracle
Sviluppo di applicazioni per Basi di dati
Interbase
14 di 31
Implementazioni delle CLI
ODBC (Open Database Connectivity)
 implementazione proprietaria (Microsoft) di SQL/CLI (altra CLI parte di
SQL:1999)
 accesso a basi di dati relazionali in un contesto eterogeneo e distribuito
 originariamente rilasciata nel 1992, consente di accedere a database di
numerosissimi costruttori, inclusi Microsoft (jet, Access, SQLServer); Oracle,
IBM, Informix e numerosi altri
JDBC (Java Database Connectivity)
 API Java sviluppata da JavaSoft per eseguire istruzioni SQL
 consente ai programmi Java di interagire con qualunque database
 JDBC rende possibile scrivere una singola applicazione di database che può girare
indifferentemente su piattaforme diverse e interagire con diversi database
 simile a ODBC ma è progettato per Java mentre ODBC è indipendente dal
linguaggio di programmazione.
Basi di Dati e
Sistemi Informativi
Sviluppo di applicazioni per Basi di dati
15 di 31
Accesso con JDBC
In generale le operazioni richieste sono:
 caricamento del driver
 apertura della connessione con la base di dati
 richiesta di esecuzione di istruzioni SQL
 elaborazione dei risultati delle istruzioni SQL
Le funzioni di accesso al DBMS possono essere realizzate tramite:
 JDBC/ODBC Bridge
 Driver Nativo
 Middleware-server
 Driver Java
Basi di Dati e
Sistemi Informativi
Sviluppo di applicazioni per Basi di dati
16 di 31
ODBC e ADO
 ODBC richiede una certa familiarità di programmazione con le API ed è
sostanzialmente procedurale (Scritta in C).
 Successivamente Microsoft ha introdotto altre due API, questa volta orientate agli
oggetti
 DAO (Data Access Object)
 RDO (Remote Data Object)
 Definiscono interfacce ActiveX (originariamente COM Component Object Model)
 ADO (Active Data Object), basata su una nuova tecnologia di programmazione delle
interfacce per DB nota come OLE DB (Object Linking and Embedding for
DataBases)
 In ODBC ogni tipo di DB deve avere una DLL (Dynamic Link Library) e un driver
ODBC, che viene utilizzata dal motore ODBC per accedere allo specifico DB.
 In OLE DB is hanno ancora dei driver, ma questi sono implementazioni ActiveX, cioè
definizioni di classi che implementano delle interfacce, eliminando così i livelli
interposti tra il programma e lo specifico DBMS che si sta utilizzando
Basi di Dati e
Sistemi Informativi
Sviluppo di applicazioni per Basi di dati
17 di 31
Basi di ADO
ADO è composto essenzialmente da tre oggetti primari
 Connection Object: responsabile di raccogliere tutte le informazioni necessarie
alla creazione del RecordSet
 RecordSet: fornisce la struttura atta a contenere i dati e i metodi necessari per
accedere ai dati. Tutte le funzionalità dei cursori sono rappresentati nell’interfaccia
del RecordSet
 Command Object: fornisce un secondo metodo per creare un RecordSet, ed è
stato esplicitamente progettato per passare i parametri alle stored procedure
Basi di Dati e
Sistemi Informativi
Sviluppo di applicazioni per Basi di dati
18 di 31
SQL Embedded vs CLI
SQL embedded
 pemette precompilazione (e quindi efficienza e portabilità del sorgente)
 uso di SQL completo
 Molto usato in applicazioni legacy
CLI
 indipendente dal DBMS, uso di funzioni di interazione con i DBMS
attraverso Application Programming Interfaces (API)
 permette di accedere a più basi di dati, anche eterogenee (senza
ricompilazione: portabilità dell’eseguibile)
 richiede un ulteriore strato di comunicazione
 esistono drivers specifici per i vari DBMS
Basi di Dati e
Sistemi Informativi
Sviluppo di applicazioni per Basi di dati
19 di 31
Stored Procedure
 A partire da SQL-2 è possibile definire procedure dette stored procedures per il fatto che
vengono memorizzate all’interno della base di dati come parti dello schema
 Le procedure permettono di associare un nome ad una o più istruzioni SQL, con la possibilità
di specificare dei parametri da utilizzare per lo scambio di informazioni con la procedura
 Caratteristiche:
 minor quantità di dati trasferita tra client e server
 più facile manutenibiltà
 riutilizzo della logica dell’applicazione da parte di utenti diversi
 sensibile incremento delle prestazioni
 logica di business in gran parte inglobata nella base di dati
 Una volta che la procedura è definita, essa è utilizzabile come se facesse parte dell’insieme
dei comandi SQL predefiniti
 La procedura può essere invocata avendo cura di associare un valore ai parametri.
procedure Update_address(:cod_dip int, :Addr varchar(60))
Begin;
update Department set Address =:Addr where ID_dip =:cod_dip
End;
exec sql Update_address(:codice,:indirizzo);
Basi di Dati e
Sistemi Informativi
Sviluppo di applicazioni per Basi di dati
20 di 31
PL/SQL (Procedural Language/SQL)
Oracle PL/SQL: estensione più ricca e più complessa (ad es. costrutti if – else, cicli
while) che rendono SQL un linguaggio computazionalmente completo ossia con lo
stesso potere espressivo di un normale linguaggio di programmazione.
DECLARE
/* Declarative section: variables, types, and local subprograms. */
BEGIN
/* Executable section: procedural and SQL statements go here. */
/* This is the only section of the block that is required. */
EXCEPTION
/* Exception handling section: error handling statements go here.
*/
END;
Per eseguire un programma PL/SQL al testo bisogna far seguire:
Una riga con .
Una riga con run
Basi di Dati e
Sistemi Informativi
Sviluppo di applicazioni per Basi di dati
21 di 31
Variabili e Tipi
Una variabile unbound ha sempre valore NULL. Esistono i tipi di SQL, ma anche tipi nuovi, quali Number
DECLARE
price
NUMBER;
myBeer VARCHAR(20);
Usare %TYPE:
DECLARE
myBeer Beers.name%TYPE;
/* Assegna direttamente a myBeer il tipo di name */
Usare %ROWTYPE per definire record strutturati:
DECLARE
beerTuple Beers%ROWTYPE;
Assegnare valori
DECLARE
a NUMBER := 3;
BEGIN
a := a + 1;
END;
.
run;
Basi di Dati e
Sistemi Informativi
Sviluppo di applicazioni per Basi di dati
22 di 31
Esempio Base
CREATE TABLE T1(e INTEGER, f INTEGER );
INSERT INTO T1 VALUES(1, 3);
INSERT INTO T1 VALUES(2, 4);
/* Above is plain SQL; below is the PL/SQL program. */
DECLARE
a NUMBER;
b NUMBER;
BEGIN
SELECT e,f INTO a,b FROM T1 WHERE e>1;
INSERT INTO T1 VALUES(b,a);
END;
.
run;
Si noti l’obbligo della keyword INTO
Basi di Dati e
Sistemi Informativi
Sviluppo di applicazioni per Basi di dati
23 di 31
Flusso di controllo: IF
IF <condition> THEN <statement_list>
ELSE <statement_list>
END IF; la parte ELSE è opzionale.
Si può fare multiway branch:
IF <condition_1> THEN ... ELSIF <condition_2> THEN ... ... ... ELSIF
<condition_n> THEN ... ELSE ... END IF;
Esempio:
DECLARE
a NUMBER;
b NUMBER;
BEGIN
SELECT e,f INTO a,b FROM T1 WHERE e>1;
IF b=1 THEN
INSERT INTO T1 VALUES(b,a);
ELSE
INSERT INTO T1 VALUES(b+10,a+10);
END IF;
END;
Basi di Dati e
.
Sistemi Informativi
Sviluppo di applicazioni per Basi di dati
24 di 31
Flusso di controllo: Cicli
LOOP <loop_body> /* A list of statements. Inclusivi di un EXIT WHEN
<condition>;*/
END LOOP;
Esempio:
DECLARE
i NUMBER := 1;
BEGIN
LOOP
INSERT INTO T1 VALUES(i,i);
i := i+1;
EXIT WHEN i>100;
END LOOP;
END;
Esistono anche:
WHILE <condition> LOOP
<loop_body>
FOR <var> IN <start>..<finish> LOOP
Basi di Dati e
Sistemi Informativi
END LOOP;
<loop_body>
Sviluppo di applicazioni per Basi di dati
END LOOP;
25 di 31
Basic I/O
DECLARE
emp_count
NUMBER;
BEGIN
SELECT count(*)
INTO emp_count
FROM employees
WHERE department_id = &department_id;
END;
Produrrà in esecuzione:
Enter value for department_id: 13
Stampare a video:
DBMS_OUTPUT.PUT_LINE(‘hello world’ || emp_count);
Basi di Dati e
Sistemi Informativi
Sviluppo di applicazioni per Basi di dati
26 di 31
Usare i cursori
1) DECLARE
/* Declare output variables to hold the result of the query: */
2)
a T1.e%TYPE;
3)
b T1.f%TYPE;
4)
CURSOR T1Cursor AS
5)
SELECT e, f
6)
FROM T1
7)
WHERE e < f
8)
FOR UPDATE;
/* Cursor declaration: */
9) BEGIN
10)
OPEN T1Cursor;
11)
LOOP
/*Retrieve each row of the result of the above query into PL/SQL variables:*/
12)
FETCH T1Cursor INTO a, b;/* If there are no more rows to fetch, exit the loop*/
13)
EXIT WHEN T1Cursor%NOTFOUND;
14)
DELETE FROM T1 WHERE CURRENT OF T1Cursor;
15)
INSERT INTO T1 VALUES(b, a);
16)
END LOOP;
17)
CLOSE T1Cursor;
/* Delete the current tuple: */
/* Insert the reverse tuple: */
/* Free cursor used by the query. */
18) END;
19) .
20) run;
Basi di Dati e
Sistemi Informativi
Sviluppo di applicazioni per Basi di dati
27 di 31
Stored procedures in PL/SQL
CREATE TABLE T2 ( a INTEGER, b CHAR(10));
Dichiarazione di una stored procedure:
CREATE|REPLACE PROCEDURE proc_name(var_name IN|OUT|INOUT Data_type) AS|IS
Esempio:
CREATE PROCEDURE addtuple1(i IN NUMBER) AS
BEGIN
INSERT INTO T2 VALUES(i, 'xxx');
END addtuple1;
.
run;
Esecuzione:
BEGIN addtuple1(99); END;
.
run;
Eliminazione:
DROP PROCEDURE <proc_name>
Basi di Dati e
Sistemi Informativi
Sviluppo di applicazioni per Basi di dati
28 di 31
Stored Procedures in PL/SQL
CREATE PROCEDURE addtuple2(x IN T2.a%TYPE, y IN T2.b%TYPE)
AS
BEGIN
INSERT INTO T2(a, b)
VALUES(x, y);
END addtuple2;
.
run;
Esecuzione:
BEGIN
addtuple2(10, 'abc');
END;
.
run;
Basi di Dati e
Sistemi Informativi
Sviluppo di applicazioni per Basi di dati
29 di 31
Stored Procedures in PL/SQL
CREATE TABLE T3 ( a INTEGER, b INTEGER);
CREATE PROCEDURE addtuple3(a IN NUMBER, b OUT NUMBER) AS
BEGIN
b := 4;
INSERT INTO T3 VALUES(a, b);
END;
.
run;
Eseguiamo la procedura:
DECLARE v NUMBER;
BEGIN
addtuple3(10, v);
END;
.
run;
Basi di Dati e
Sistemi Informativi
Sviluppo di applicazioni per Basi di dati
30 di 31
Funzioni
Le funzioni vengono trattate analogamente alle procedure:
CREATE FUNCTION <func_name>(<param_list>)
RETURN <return_type> AS ...
..
RETURN <expression>
Basi di Dati e
Sistemi Informativi
Sviluppo di applicazioni per Basi di dati
31 di 31