Programmierung von Datenbank Anwendungen
Werbung
Programmierung von
Datenbank Anwendungen
ESQL, ODBC, JDBC und co
IS: Datenbanken, © Till Hänisch 2000
Methoden
bisher interaktive Verwendung von SQL
Terminal
Skripte
Ausführen von SQL aus
Programmiersprache heraus
proprietäre APIs
standardisierte Schnittstellen
statisch (embedded SQL)
dynamisch (ODBC, JDBC)
IS: Datenbanken, © Till Hänisch 2000
Embedded SQL
Einbettung von SQL-Statements in Wirts-Sprache
i.w. gleiche Syntax wie bei interaktivem SQL,
zusätzlich Konstrukte für
C,COBOL, PL/1, FORTRAN, PASCAL,...
Vor-Übesetzung des Programms in Wirts-Sprache
(precompile)
Einbettung der SQL-Befehle
Fehlerbehandlung
Übergabe von Variableninhalten
Übergabe von Query-Ergebnissen
Einfache, sprachunabhängige Syntax für Precompiler
EXEC SQL Präfixc für SQL-Kommandos
":" als Kennzeichner für Variablen
IS: Datenbanken, © Till Hänisch 2000
Vorgehen
example.cp
C Source mit eingebettetem SQL
Precompiler für C
example.c
C Source, SQl durch DBMS-spezifische
Funktionsaufrufe ersetzt
C Compiler
example.o
Linker
example[.exe]
Object Code
DBMSLibrary
ausführbares Programm
IS: Datenbanken, © Till Hänisch 2000
Tupelvariablen
SQL liefert Tupelmenge, Darstellung in C, PASCAL,...
"Impedence mismatch"
Typkonzept des RDBMS und der Wirtssprache passen nicht zusammen
Lösung: Cursor
Iterator, Tupel-Zeiger für satzweise Verarbeitung
EXEC SQL DECLARE name CURSOR FOR select statm.
Operationen:
OPEN
führt Abfrage aus
CLOSE
FETCH name INTO :var1, :var2,...
überträgt Werte der Attribute des aktuellen Datensatzes in
Variablen und setzt Zeiger eins weiter
IS: Datenbanken, © Till Hänisch 2000
prinzipieller Aufbau
EXEC SQL BEGIN DECLARE SECTION;
Deklaration der Übergabevariablen
EXEC SQL END DECLARE SECTION;
EXEC SQL INCLUDE SQLCA;
EXEC SQL CONNECT :userid IDENTIFIED BY :password
EXEC SQL DECLARE c CURSOR FOR SELECT * FROM EMP;
EXEC SQL OPEN c;
for(;;) {
}
EXEC SQL FETCH ...
EXEC SQL CLOSE c;
EXEC SQL DISCONNECT;
IS: Datenbanken, © Till Hänisch 2000
Anmerkungen
statisches SQL
tupelweise Verarbeitung u.U. nicht effizient
wird im Programm fest definiert und kann vom Precompiler
ausgewertet werden
SQL muß vorher bekannt sein !
Wie ist isql implementiert ?
dynamisches SQL (in ESQL nicht möglich)
ein Funktionsaufruf pro Tupel - > Array Fetch,...
ESQL ist standardisiert
wie SQL selbst
passende Umgebung muß zum Programm gelinkt werden
Geht nicht, wenn Auswahl des DB-Systems erst zur Laufzeit
erfolgen soll ! -> ODBC
IS: Datenbanken, © Till Hänisch 2000
Native API, Beispiel OCI
Oracle Call Interface (CLI)
kompliziert, mächtig, Oracle spezifisch
bestimmter Funktionen nur mit OCI
mehrere Transaktionen
BLOBs
static char cmd[] = "INSERT INTO MESSAGE(SEVERITY,CODE) VALUES (:Severity,:Code)";
if (!olog(&lda, hda, (unsigned char *)pszUserid, -1,
(unsigned char *)pszPassword, -1, (unsigned char *)pszNetAlias, -1,
(ub4)OCI_LM_DEF))
if (!oopen(&cda, &lda, (text *) 0, -1, -1, (text *) 0, -1))
if (!oparse(&cda,(unsigned char *) cmd,-1,0,2))
ProcessMessage(&msg); /* normaler C-Code */
if ((!obndrv(&cda,(unsigned char *)":Severity",-1,(unsigned char *) Severity,
strlen(Severity), VARCHAR2_TYPE,-1,0,0,-1,-1))
|| (obndrv(&cda,(unsigned char *)":Code",-1,(unsigned char *)Code,
strlen(Code),VARCHAR2_TYPE,-1,0,0,-1,-1)))
if (!oexec(&cda))
IS: Datenbanken, © Till Hänisch 2000
Embedded SQL
SQL wird in Standard C (COBOL,...) eingebettet
Quellcode datenbankunabhängig
Precompiler, der OCI erzeugt
ausführbares Programm ist datenbankabhängig
EXEC SQL BEGIN DECLARE SECTION;
VARCHAR pszUserid[20];
VARCHAR pszPassword[20];
VARCHAR Severity[5];
VARCHAR Code[10];
EXEC SQL END DECLARE SECTION;
EXEC SQL CONNECT :username IDENTIFIED BY :password;
ProcessMessage(&msg); /* normaler C-Code */
EXEC SQL INSERT INTO MESSAGE(Severity, Code)
VALUES (:Severity, :Code);
IS: Datenbanken, © Till Hänisch 2000
Warum ODBC ?
Am Anfang waren die Daten, sie waren unformatiert, und Dunkelheit herrschte auf der
Erde. Und Codd sagte: „Es werde ein relationales Datenmodell“. Und so geschah es.
Und Codd sagte: „Die Daten sollen von den Datenstrukturen der Applikationsprogramme
getrennt werden, so daß eine Datenunabhängigkeit entstehe“. Und es war gut.
Und die DBMS-Hersteller sagten: „Wir wollen fruchtbar sein und uns mehren“. Und so
geschah es.
Und die Benutzer sagten: „Wir wollen Applikationen einsetzen, um auf die Daten von allen
DBMS-Herstellern zuzugreifen“
Und die Applikationsentwickler senkten die Häupter und sagten: „Wir müssen durch das
finstere Tal mit den Precompilern oder CLI‘s, Kommunikationsstacks und Protokollen aller
Hersteller wandern“. Und es war nicht gut
...
Und so entstand ODBC
(Kyle Geiger, Inside ODBC)
IS: Datenbanken, © Till Hänisch 2000
ODBC-Architektur
Anwendung
ODBC
Treibermanager
ODBC
Treiber
ODBC
Treiber
ODBC
Treiber
Datenbank
Datenbank
Datenbank
IS: Datenbanken, © Till Hänisch 2000
ODBC
Open Database Connectivity
Industriestandard (Microsoft, IBM,...)
datenbankunabhängig
static char cmd[] = "INSERT INTO MESSAGE(SEVERITY,CODE) VALUES (?,?)";
rc=SQLAllocEnv(&henv);
rc=SQLAllocConnect(henv,&hdbc);
rc=SQLConnect(hdbc,“Kurs",SQL_NTS,ODBC_USERNAME,SQL_NTS,ODBC_PASSWORD,SQL_NTS);
rc=SQLAllocStmt(hdbc,&hstmt);
rc = SQLPrepare(hstmt,cmd,SQL_NTS);
rc = SQLBindParameter(hstmt,1,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_VARCHAR,
strlen(Severity),0,Severity,0,NULL);
rc = SQLBindParameter(hstmt,2,SQL_PARAM_INPUT,SQL_C_CHAR,SQL_VARCHAR,
strlen(Code),0,Code,0,NULL);
rc = SQLExecute(hstmt);
IS: Datenbanken, © Till Hänisch 2000
JDBC
Java Database Connectivity
import java.sql.*;
class Employee
{
public static void main (String args [])
throws SQLException
{
DriverManager.registerDriver(new com.sybase.jdbc.SybDriver());
Connection conn =
DriverManager.getConnection ("jdbc:sybase:Tds:vaio:9898",
"ba", "isdb00");
Statement stmt = conn.createStatement ();
ResultSet rset = stmt.executeQuery ("select empno,ename from emp");
// Iterate through the result and print the employee names
while (rset.next ())
System.out.println (rset.getInt(1) + " " + rset.getString (2));
}
}
IS: Datenbanken, © Till Hänisch 2000
JDBC - Statement
Statement stmt = conn.createStatement ();
Stmt.executeQuery(“SELECT * FROM EMP“);
PreparedStatement ps = conn.prepareStatement(
„SELECT * FROM EMP WHERE EMPNO = ?“);
Kann SQL-Anweisungen ausführen
Spezialfall: PreparedStatement:
Bei mehrfacher Ausführung bleibt SQL-Text gleich,
muß nicht bei jeder Ausführung analysiert werden
Bietet u.U. bessere Performance
IS: Datenbanken, © Till Hänisch 2000
JDBC - ResultSet
ResultSet rset = stmt.executeQuery ("select empno,ename from emp");
while (rset.next ())
System.out.println (rset.getInt(1) + " " + rset.getString (2));
}
executeQuery liefert ResultSet-Objekt zurück
Kapselt Cursor, kann Ergebnis zeilenweise
durchgehen
Steht nach executeQuery vor dem ersten Datensatz
next() geht einen Datensatz weiter, liefert true
zurück, solange aktueller Datensatz gültig
Zugriff auf Spalten mit getXXX (getInt, getString,...)
IS: Datenbanken, © Till Hänisch 2000
JDBC - PreparedStatement
PreparedStatement ps = conn.prepareStatement(
„SELECT * FROM EMP WHERE EMPNO = ?“);
for(...) {
ps.setInt(1,4711);
ResultSet rset = ps.execute();
...
}
Platzhalter werden mit setXXX mit Werten belegt
Erster Parameter ist Index des Parameters
IS: Datenbanken, © Till Hänisch 2000
JDBC - Transaktionskontrolle
conn.setAutoCommit(false);
Conn.commit();
Conn.rollback();
Conn.setTransactionIsolation(
Connection.TRANSACTION_SERIALIZABLE);
AutoCommit führt nach jedem execute ein commit durch (oft
Standard, sinnvoll ?)
Manuelle Commit-Steuerung oft sinnvoller
Nicht alle Isolationlevel werden von allen Datenbanken
unterstützt (siehe Datenbanken II)
IS: Datenbanken, © Till Hänisch 2000
stored procedures mit mysql
seit Version 5 unterstützt mysql stored
procedures/functions
Kapselung von Zugriffen
eigene Funktionen für SQL (reporting)
Dinge, die in SQL schwierig sind ....
Trigger
Implementierung von Business Logik
Sicherung von Konsistenz
Syntax:
http://www.mysqltutorial.org/mysql-stored-procedure-tutorial.aspx
IS: Datenbanken, © Till Hänisch 2000
Kapselung von Zugriffen
Beispiel emp: Gehalt ändern
-- create table emp_history(wann datetime not null,
-- von int not null, nach int not null, wer varchar(255) not null);
delimiter '$$';
drop procedure if exists change_salary$$
create procedure change_salary (l_empno int, l_new_sal int)
begin
declare l_sal int;
select sal into l_sal from emp where empno=l_empno;
update emp set sal = l_new_sal where empno=l_empno;
insert into emp_history (wann,von, nach, wer)
values (now(),l_sal,l_new_sal,user());
end$$
delimiter ;
IS: Datenbanken, © Till Hänisch 2000
Ergebnis
mysql> call change_salary(7369,1000);
Query OK, 1 row affected (0.07 sec)
mysql> select * from emp;
+-------+--------+-----------+------+------------+------+------+--------+
| empno | ename | job
| mgr | hiredate
| sal | comm | deptno |
+-------+--------+-----------+------+------------+------+------+--------+
| 7369 | smith | clerk
| 7902 | 1980-12-17 | 1000 | NULL |
20 |
14 rows in set (0.00 sec)
mysql> select * from emp_history;
+---------------------+-----+------+----------------+
| wann
| von | nach | wer
|
+---------------------+-----+------+----------------+
| 2011-11-27 16:15:14 | 800 | 1000 | root@localhost |
+---------------------+-----+------+----------------+
1 row in set (0.00 sec)
IS: Datenbanken, © Till Hänisch 2000
Zugriffsrechte
-- alice hat zunächst keine Rechte auf emp
grant select on test.emp to 'alice'@'localhost';
grant execute on procedure test.change_salary to 'alice'@'localhost';
-- als Benutzer alice
mysql> update test.emp set sal=3000 where empno=7369;
ERROR 1142 (42000): UPDATE command denied to user 'alice'@'localhost' for table '
mysql> call test.change_salary(7369,5000);
Query OK, 1 row affected (0.03 sec)
mysql> select * from test.emp;
+-------+--------+-----------+------+------------+------+------+--------+
| empno | ename | job
| mgr | hiredate
| sal | comm | deptno |
+-------+--------+-----------+------+------------+------+------+--------+
| 7369 | smith | clerk
| 7902 | 1980-12-17 | 5000 | NULL |
20 |
mysql> select * from emp_history;
+---------------------+------+------+-----------------+
| wann
| von | nach | wer
|
+---------------------+------+------+-----------------+
| 2011-11-27 16:36:23 | 3000 | 5000 | alice@localhost |
IS: Datenbanken, © Till Hänisch 2000
Eigene Funktionen
Beispiel emp: Gehalt
delimiter '$$';
drop function if exists gehalt$$
create function gehalt (l_empno int) returns int
begin
declare l_sal int;
declare l_comm int;
declare retval int;
select sal,comm into l_sal, l_comm from emp where empno=l_empno;
if (l_comm is null) then
set l_comm = 0;
end if;
set retval = l_sal + l_comm;
return retval;
end$$
delimiter ;
IS: Datenbanken, © Till Hänisch 2000
Ergebnis:
mysql> select ename,gehalt(empno) as Gehalt from emp;
+--------+--------+
| ename | Gehalt |
+--------+--------+
| smith |
1000 |
| allen |
1900 |
| ward
|
1750 |
| jones |
2975 |
| martin |
2650 |
| blake |
2850 |
| clark |
2450 |
| scott |
3000 |
| king
|
5000 |
| turner |
1500 |
| adams |
1100 |
| james |
950 |
| ford
|
3000 |
| miller |
1300 |
+--------+--------+
14 rows in set (0.00 sec)
IS: Datenbanken, © Till Hänisch 2000
Erzeugung von Nummern
Beispiel: Rechnungsnummern (2011/21)
mysql> select make_rno();
+------------+
| make_rno() |
+------------+
| 2011/3
|
+------------+
1 row in set (0.00 sec)
mysql> select make_rno();
+------------+
| make_rno() |
+------------+
| 2011/4
|
+------------+
1 row in set (0.00 sec)
mysql> select * from numbers;
+----------+---------+
| tab
| current |
+----------+---------+
| rechnung |
4 |
+----------+---------+
1 row in set (0.00 sec)
mysql>
IS: Datenbanken, © Till Hänisch 2000
Aufgabe:
Erstellen Sie eine Funktion make_rno, die eine Nummer
der Form YYYY/nnnn erzeugt. YYYY ist das aktuelle Jahr
( mysql: YEAR(NOW()) ), nnnn eine laufende Nummer, die
Sie aus der Tabelle NUMBERS erzeugen.
Nachteil: Am Jahresbeginn muß die Nummer in der
Tabelle zurückgesetzt werden
Erweitern Sie die Funktion so, dass automatisch im neuen
Jahr wieder bei 1 begonnen wird. Erweitern Sie dazu die
Tabelle numbers um ein Attribut Jahr, in dem Sie das Jahr
zur letzten erzeugten Nummer speichern. Prüfen Sie dann
in make_rno, ob Sie sich noch im gleichen Jahr befinden,
falls nicht, setzen Sie current auf 1.
IS: Datenbanken, © Till Hänisch 2000
schwierige Dinge ...
Beispiel: Hierarchie ausgeben
delimiter '$$';
drop function if exists emp_level$$
create function emp_level (l_empno int) returns int
begin
declare l int;
declare mgr_id int;
set l = 0;
select mgr into mgr_id from emp where empno=l_empno;
while (mgr_id is not null) do
set l = l + 1;
set l_empno = mgr_id;
select mgr into mgr_id from emp where empno=l_empno;
end while;
return l;
end$$
IS: Datenbanken, © Till Hänisch 2000
delimiter ;
Ergebnis:
Beispiel: Hierarchie ausgeben
mysql> select ename,empno,mgr,emp_level(empno) from emp;
+--------+-------+------+------------------+
| ename | empno | mgr | emp_level(empno) |
+--------+-------+------+------------------+
| smith | 7369 | 7902 |
3 |
| allen | 7499 | 7698 |
2 |
| ward
| 7521 | 7698 |
2 |
| jones | 7566 | 7839 |
1 |
| martin | 7654 | 7698 |
2 |
| blake | 7698 | 7839 |
1 |
| clark | 7782 | 7839 |
1 |
| scott | 7788 | 7566 |
2 |
| king
| 7839 | NULL |
0 |
| turner | 7844 | 7698 |
2 |
| adams | 7876 | 7788 |
3 |
| james | 7900 | 7698 |
2 |
| ford
| 7902 | 7566 |
2 |
| miller | 7934 | 7782 |
2 |
+--------+-------+------+------------------+
IS: Datenbanken, © Till Hänisch 2000
14 rows in set (0.00 sec)
schöner:
delimiter '$$';
drop function if exists emp_pos$$
create function emp_pos (l_empno int) returns char(30)
begin
declare l varchar(255);
declare mgr_id int;
set l = '';
select mgr into mgr_id from emp where empno=l_empno;
while (mgr_id is not null) do
set l = concat(l,'-');
set l_empno = mgr_id;
select mgr into mgr_id from emp where empno=l_empno;
end while;
return l;
end$$
delimiter ;
IS: Datenbanken, © Till Hänisch 2000
Ergebnis:
mysql> select concat(emp_pos(empno),ename)
order by length(emp_pos(empno)) asc;
+------------------------------+
| concat(emp_pos(empno),ename) |
+------------------------------+
| king
|
| -blake
|
| -clark
|
| -jones
|
| --allen
|
| --james
|
| --ward
|
| --scott
|
| --ford
|
| --miller
|
| --martin
|
| --turner
|
| ---smith
|
| ---adams
|
+------------------------------+
14 rows in set (0.00 sec)
from emp
IS: Datenbanken, © Till Hänisch 2000
mit Oracle:
SELECT LPAD(' ',2*(LEVEL-1)) || empno
empno,ename,job,mgr,sal,deptno,LEVEL
FROM emp
CONNECT BY PRIOR empno = mgr
START WITH job = 'PRESIDENT';
EMPNO
-----------7839
7566
7788
7876
7902
7369
7698
7499
7521
7654
7844
7900
7782
7934
ENAME
---------KING
JONES
SCOTT
ADAMS
FORD
SMITH
BLAKE
ALLEN
WARD
MARTIN
TURNER
JAMES
CLARK
MILLER
JOB
MGR
SAL
DEPTNO LEVEL
--------- ---------- ---------- ------ ----PRESIDENT
5000
10
1
MANAGER
7839
2975
20
2
ANALYST
7566
3000
20
3
CLERK
7788
1100
20
4
ANALYST
7566
3000
20
3
CLERK
7902
800
20
4
MANAGER
7839
2850
30
2
SALESMAN
7698
1600
30
3
SALESMAN
7698
1250
30
3
SALESMAN
7698
1250
30
3
SALESMAN
7698
1500
30
3
CLERK
7698
950
30
3
MANAGER
7839
2450
10
2
IS: Datenbanken, © Till Hänisch 2000
CLERK
7782
1300
10
3
Trigger
Beispiel emp: Änderung protokollieren
delimiter '$$';
drop trigger if exists t_emp$$
create trigger t_emp before update on emp for each row
begin
insert into emp_history(wann,von,nach,wer)
values (now(),old.sal,new.sal,user());
end$$
delimiter ;
IS: Datenbanken, © Till Hänisch 2000
Ergebnis:
mysql> source emptrig.sql
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> update emp set sal = 1000 where empno=7369;
Query OK, 1 row affected (0.37 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from emp;
+-------+--------+-----------+------+------------+------+------+--------+
| empno | ename | job
| mgr | hiredate
| sal | comm | deptno |
+-------+--------+-----------+------+------------+------+------+--------+
| 7369 | smith | clerk
| 7902 | 1980-12-17 | 1000 | NULL |
20 |
mysql> select * from emp_history;
+---------------------+------+------+-----------------+
| wann
| von | nach | wer
|
+---------------------+------+------+-----------------+
| 2011-11-27 16:45:21 | 5000 | 1000 | root@localhost |
+---------------------+------+------+-----------------+
3 rows in set (0.00 sec)
IS: Datenbanken, © Till Hänisch 2000
Trigger: Konsistenz
Manchmal ist Integrität komplexer, als durch
constraints abbildbar, z.B:
depts, die nur inaktive Mitarbeiter haben, dürfen
gelöscht werden.
mysql> select * from emp order by deptno;
+-------+--------+-----------+------+------------+------+------+--------+--------+
| empno | ename | job
| mgr | hiredate
| sal | comm | deptno | active |
+-------+--------+-----------+------+------------+------+------+--------+--------+
| 7934 | miller | clerk
| 7782 | 1982-01-23 | 1300 | NULL |
10 | N
|
| 7839 | king
| president | NULL | 1981-11-17 | 5000 | NULL |
10 | N
|
| 7782 | clark | manager
| 7839 | 1981-06-09 | 2450 | NULL |
10 | N
|
| 7902 | ford
| analyst
| 7566 | 1981-12-03 | 3000 | NULL |
20 | J
|
| 7876 | adams | clerk
| 7788 | 1983-01-12 | 1100 | NULL |
20 | J
|
| 7788 | scott | analyst
| 7566 | 1982-12-09 | 3000 | NULL |
20 | J
|
| 7566 | jones | manager
| 7839 | 1981-04-02 | 2975 | NULL |
20 | J
|
| 7369 | smith | clerk
| 7902 | 1980-12-17 | 1000 | NULL |
20 | J
|
| 7698 | blake | manager
| 7839 | 1981-05-01 | 2850 | NULL |
30 | J
|
IS: Datenbanken, © Till Hänisch 2000
Trigger: Konsistenz
delimiter '$$';
drop trigger if exists t_dept$$
create trigger t_dept before delete on dept for each row
begin
declare l_numemps int;
declare msg varchar(255);
select count(*) into l_numemps from emp
where deptno = old.deptno and active = 'J';
if (l_numemps > 0) then
set msg = concat('Unable to delete dept ',old.deptno);
signal sqlstate '45000' set MESSAGE_TEXT=msg;
end if;
end$$
mysql> delete from dept where deptno=20;
ERROR 1644 (45000): Unable to delete dept 20
mysql> delete from dept where deptno=10;
ERROR 1451 (23000): Cannot delete
or ©
update
a parent row: a foreign key constrain
IS: Datenbanken,
Till Hänisch 2000
m
Beispiel: Anwendungslogik
CREATE OR REPLACE TRIGGER TUpdAuftrag
BEFORE UPDATE ON Auftrag FOR EACH ROW
BEGIN
IF ((:old.Status = Globvar.Stat_Auftrag_abgerechnet)
AND (USER <> '&1')) THEN
Error.raise_error(Error.en_Abgerechnet);
END IF;
END;
CREATE OR REPLACE TRIGGER TDelAuftrag
BEFORE DELETE ON Auftrag FOR EACH ROW
BEGIN
IF (:old.Status = Globvar.Stat_Auftrag_abgerechnet) THEN
Error.raise_error(Error.en_Abgerechnet);
IS: Datenbanken, © Till Hänisch 2000
END IF;
END;
Aktive Datenbanken
„normale“ Datenbanken speichern
Daten
Aktive Datenbanken führen Aktionen
aus (ECA-Modell)
Event
Condition
Z.B. Änderung von Daten, Zeitpunkt,...
Bedingung, unter der Action ausgeführt wird
Action
IS: Datenbanken, © Till Hänisch 2000
Anwendungen von Triggern
Denormalisierte Relationen
Protokollierung
Replikation
Materialized views
Einfache Integritätsbedingungen durch
constraints, komplexere ?
Business rules
Z.B. „Fakturierte Aufträge dürfen nicht
geändert werden“
IS: Datenbanken, © Till Hänisch 2000
Events bei mysql
Events werden regelmäßig (cron)
ausgelöst.
Wartungsarbeiten
Statistik
set GLOBAL event_scheduler=ON;
create EVENT fill_t ON SCHEDULE
EVERY 1 minute DO
insert into t values(77,42);
IS: Datenbanken, © Till Hänisch 2000
Performance
SQL> describe lagerbewegung
Name
----------------------------------------NUMMER
ART
VORGANGSNUMMER
BESTELLNUMMER
TEILENUMMER
MENGE
LAGERORT
DATUM
Null?
-------NOT NULL
NOT NULL
NOT
NOT
NOT
NOT
SQL> select count(*) from lagerbewegung;
COUNT(*)
---------159804
IS: Datenbanken, © Till Hänisch 2000
NULL
NULL
NULL
NULL
Type
----------------NUMBER
NUMBER(2)
VARCHAR2(10)
NUMBER
VARCHAR2(15)
NUMBER(10,3)
VARCHAR2(12)
DATE
Szenario
Lesen aller Records aus einer Tabelle
und schreiben in andere, zunächst intern
durch DB
Dann verschiedene Programmiertechniken
Idee: Bearbeitung der Records nötig, z.B.
ergänzen von Werten ...
In Programmen commit alle 1000 records,
sonst ziemlich langsam ....
SQL> create table fastest as select * from lagerbewegung;
Table created.
Elapsed: 00:00:03.91
IS: Datenbanken, © Till Hänisch 2000
Stored procedure
create or replace procedure perf_proc as
cursor c is select * from lagerbewegung;
c_rec c%rowtype;
i integer;
begin
open c;
i := 0;
fetch c into c_rec;
while c%found loop
insert into perf(nummer,art,vorgangsnummer,...)
values (c_rec.nummer,c_rec.art...);
i := i + 1;
if ((i mod 1000) = 0) then
commit;
end if;
fetch c into c_rec;
end loop;
close c;
SQL> execute perf_proc
end;
PL/SQL procedure successfully completed.
/
Elapsed: 00:01:56.90
IS: Datenbanken, © Till Hänisch 2000
Java, dynamisches SQL
ResultSet rset = stmt.executeQuery ("select nummer, ...
where rownum < 10000"); // Zunächst nur 10.000 records ....
while (rset.next ()) {
sql = "insert into perf(nummer,art...)"
+ “values (" + rset.getInt(1) + "," + ...)";
ins.execute(sql);
NumberOfRecords++;
if ((NumberOfRecords % CommitInterval) == 0)
conn.commit();
}
oracle$ java Perftest
elapsed time: 38.146 seconds für 10.000 records,
Entspricht insgesamt (extrapoliert) ca. 11 Minuten
IS: Datenbanken, © Till Hänisch 2000
Prepared statement
PreparedStatement ps = conn.prepareStatement("insert into perf(...,
values (?,?,?,?,?,?,?,to_date(?,'yyyy-mm-dd hh24:mi:ss'))");
ResultSet rset = stmt.executeQuery ("select nummer ... ");
while (rset.next ()) {
ps.setInt(1,rset.getInt(1));
...
ps.execute();
NumberOfRecords++;
if ((NumberOfRecords % CommitInterval) == 0)
conn.commit();
}
oracle$ java PerftestPrepared
elapsed time: 17.573 seconds für 10.000 records,
Entspricht insgesamt (extrapoliert) ca. 5 Minuten !
IS: Datenbanken, © Till Hänisch 2000
Vergleich
Technik
Dauer
Table Copy ohne
Transaktion
Stored procedure
4 Sekunden
Java mit
dynamischem
Statement
Java mit prepared
statement
11 Minuten
2 Minuten
5 Minuten
IS: Datenbanken, © Till Hänisch 2000
Besser ... (DB-spezifisch)
create or replace procedure perf_bulk as
type recs is table of lagerbewegung%rowtype;
data recs;
cursor c is select * from lagerbewegung;
i integer;
begin
open c;
loop
fetch c bulk collect into data limit 10000;
forall i in 1..data.count
insert into perf values data(i);
commit;
exit when c%notfound;
end loop;
close c;
end;
SQL> execute perf_bulk
PL/SQL procedure successfully completed.
Elapsed: 00:00:13.36
IS: Datenbanken, © Till Hänisch 2000
NB: Commit-Frequenz
Commit alle n records
Dauer (sec)
1
1132
10
384
100
285
1000
280
10000
275
Eine Transaktion
259
(*)
(*): möglicherweise phys. Speicher zu klein
IS: Datenbanken, © Till Hänisch 2000
