TransactSQL

Transact SQL
(
Einordnung
TransactSQL
MS-Erweiterung (bei anderen Systemen ähnlich)
SQL
normierter Standard
Datentypen
numerische
int
smallint
tinyint
bigint
decimal(p[,q])
numeric(p[,q])
real
float(p)
money
smallmoney
4 Bytes
2 Bytes
1 Byte
8 Bytes
2-17 Bytes
(s.o.)
alphanumerische
char[(n)]
varchar[(n)]
nchar[(n)]
nvarchar[(n)]
text(n)]
ntext(n)]
1*n Byte, Länge genau n
1*n Byte, mit Längenfeld, max n
2*n Byte, Unicode, Länge genau n
2*n Byte, max n, Unicode
binäre
binary([n])
varbinary([n])
image([n])
bit
Bitkette, Länge genau n
Bitkette, Länge max n
Bitkette, für Bilder
Datum/Zeit
datetime
smalldatetime
je 4 Bytes
je 2 Bytes
abgeleitete
timestamp
sysname
weitere
cursor
uniqueidentifier
sql_variant
table
xml
user defined type (UDT)
4 oder 8 Bytes
= decimal mit 8 Bytes
= decimal mit 4 Bytes
Steuerungen
BEGIN-Block
begin
Anweisung1
Anweisung2
…
end
IF-Anweisung if (select count(*) from person) > 5
print 'grösser 5'
else
print 'kleiner gleih 5'
WHILEAnweisung
while (select sum(mittel) from projekt) < 500000
update projekt set mittel=mittel*1.1
Lokale
Variablen
set @x = 15000
select @durchschnitt = avg(gehalt) from person
Weitere
Anweisungen
return
goto marke
raiserror
waitfor
Transaktion
begin transaction
insert ...
-- geht gut
insert ...
-- Verletzung irgendeiner Bedingung
commit transaction
AusnahmeBehandlung
try
begin transaction
insert ...
-- geht gut
insert ...
-- Verletzung irgendeiner Bedingung
commit transaction
print 'erfolgreiche Transaktionsausführung'
catch
rollback transaction
print 'Transaktion zurückgesetzt'
end
3 Fälle 1) guter Fall:
alle inserts gehen durch
create table ... primary key
begin transaction
insert ...
-- geht gut
insert ...
-- geht gut
commit transaction
drop table
2) schlechter Fall1:
nur mit Transaktion (wird zurückgesetzt)
create table ... primary key
begin transaction
insert ...
-- geht gut
insert ...
-- geht nicht gut, Verletzung des Primärschlüssels
commit transaction
drop table
3) schlechter Fall2:
mit Transaktion und try-catch (zurückgesetzt, Zusatzbehandlung)
create table ... primary key
Ausnahmetry
Behandlung
begin transaction
insert ...
-- geht gut
insert ...
-- geht nicht gut, Verletzung des Primärschlüssels
commit transaction
print 'erfolgreiche Transaktionsausführung'
catch
rollback transaction
print 'Transaktion zurückgesetzt'
end
drop table
Prozeduralität in der Datenbank
klassisch
select
jetzt
execute
update1
update2
ok/not ok
insert
Vorteile:
Performance wg. Übersetzung
Kommunikationsreduzierung
Mehrfachverwendung
Implementierung einer Anwendungs-Schnittstelle über SQL
gespeicherte Prozeduren
Syntax: create procedure pname (@parm1 typ, parm2 typ, …) as …
Beispiele:
SQL-Abkürzung
create procedure p1 as
select … from … where …
create procedure p2 as
create table e …
Ablaufsteuerung
create procedure p3 as
if (select count(*) from person) = 5
print ‘gleich 5'
else
print 'ungleich 5'
mit Parametern
create procedure p4 (@x int) as
select … from … where spname = @x
Definition und Aufruf
create procedure pp
execute pp
Es gibr sonst noch
drop procedure pp
alter procedure pp
benutzerdefinierte Funktionen
Syntax: create function fname (@parm1 typ, parm2 typ, …) returns typ
as begin ... return … end
1) Standardtyp: Kontodurchschnitt create function xxx …
Aufruf anstelle eines Wertes
s. Kontostand als Standardfunktion
2) Tabelle
Projektmitarbeiter create function xxx …
Aufruf anstelle einer Tabelle
s. Kontostand als Tabellenfunktion
Beispiele
-- Vorbesetzung
create procedure p3 as
begin
create table artikel2
(artikelnr int primary key, artikelname char(10), menge int, nettopreis decimal(7,2))
insert into artikel2 values (11,'Fahrrad',20,400)
insert into artikel2 values (12,'Lenker',30,70)
insert into artikel2 values (13,'Rahmen',25,120)
insert into artikel2 values (14,'Lufrad',45,50.50)
select * from artikel2
end
-- Prgrammierung
if (select count(*) from artikel) > 5
print 'mehr als 5'
else
print 'weniger oder gleich 5'
if (select sum(nettopreis) from artikel2) < 1000
update artikel2 set nettopreis=nettopreis*1.1
while (select sum(nettopreis) from artikel2) < 1000
update artikel2 set nettopreis=nettopreis*1.1
declare @x int
set @x = 15000
print @x
declare @durchschnitt decimal (7,2)
select @durchschnitt = avg(nettopreis) from artikel2
print @durchschnitt
--Gute Transaktion: alle inserts gehen durch
create table artikel2
(artikelnr int primary key, artikelname char(10), menge int, nettopreis decimal(5,2))
begin transaction
insert into artikel2 values (11,'Fahrrad',20,400) -- geht gut
insert into artikel2 values (12,'Lenker',30,70)
-- geht gut
commit transaction
select * from artikel2
drop table artikel2
--Alternative: Transaktion wird abgeschlossen oder zurückgesetzt
create table artikel2
(artikelnr int primary key, artikelname char(10), menge int, nettopreis decimal(5,2))
begin transaction
insert into artikel2 values (11,'Fahrrad',20,400) -- geht gut
insert into artikel2 values (11,'Lenker',30,70)
-- geht nicht gut
if (@@error <> 0) rollback transaction
else
commit transaction
select * from artikel2
drop table artikel2
--andere Alternative: Transaktion wird evtl. zurückgesetzt mit Zusatzbehandlung
create table artikel2
(artikelnr int primary key, artikelname char(10), menge int, nettopreis decimal(5,2))
begin try
begin transaction
insert into artikel2 values (11,'Fahrrad',20,400) -- geht gut
insert into artikel2 values (12,'Lenker',30,70)
-- geht nicht gut
commit transaction
print 'erfolgreiche Transaktionsausführung'
end try
begin catch
rollback transaction
print 'Transaktion zurückgesetzt'
end catch
select * from artikel2
--praktische Verwendung
SQL-Abkürzung
create procedure p1 as
Select , nettopreis*1.19 from artikel2
create procedure p2 as
create table artikel2
(artikelnr int primary key, artikelname char(10), menge int, nettopreis decimal(5,2))
create procedure p3 as
Ablaufsteuerung
if (select count(*) from artikel) = 5
print ‘gleich 5'
else
print 'ungleich 5'
mit Parametern
create procedure p3 (@x int) as
select * from artikel2 where nettopreis <= @x
Definition und Aufruf
create procedure pp
execute pp
--Standardfunktionen
create function c()
returns int
as begin
return 99
end
print dbo.c()
create function pd()
returns decimal(7,2)
as begin
declare @du decimal(7,2)
select @du = avg(nettopreis) from artikel
return @du
end
print dbo.pd()
select * from artikel where nettopreis < dbo.pd()
--kontostand als Standardfunktion
create function kontostand1(@kundenname char(12), @pin int)
returns decimal(9,2)
as begin
declare @ko decimal(9,2)
select @ko = kontostand from kunde2 where kundenname = @kundenname and pin = @pin
return @ko
end
print dbo.kontostand1('Meier',12345)
-- Tabellenfunktionen
create function bi()
returns table
as return select * from artikel2
select * from dbo.bi()
create function bi2()
returns table
as return select * from artikel2 where nettopreis < 100
select * from dbo.bi2()
create function preis(@anr int)
returns decimal(7,2)
as begin
declare @pr decimal(7,2)
select @pr = nettopreis from artikel2 where artikelnr = @anr
return @pr
end
print dbo.preis(11)
-- kontostand als Tabellenfunktion
create function kontostand2(@kundenname char(12),@pin int)
returns table
as return select * from kunde2 where kundenname = @kundenname and pin = @pin
select * from dbo.kontostand2('Meier',12345)
-- im Java-Programm
import java.sql.*;
public class Kontostand2SQLServer
{ public static void main(String[] args) throws Exception
{ Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection c = DriverManager.getConnection("jdbc:odbc:sqlserverdb","","");
Statement s = c.createStatement();
// als gespeicherte Tabellenfunktion
ResultSet rs = s.executeQuery("select * from dbo.kontostand2('Meier',12345)");
while (rs.next())
{ System.out.println(rs.getInt("kontostand"));
}
c.close();
}
}
--compute-Klausel
select * from artikel2
compute min(nettopreis)
Einige Systemprozeduren
Bereits fertige gespeicherte Prozeduren
auflisten aller eingeloggten Benutzer
auflisten aller Objekte der Datenbank
hinzufügen von Objekten
konfigurieren des Servers
anzeigen von Hilfen zu Datenbankobjekten
verschicken von e-mails
verwalten der Sicherheit
z.B.
sp_configure
sp_help
sp_help ‘tabellenname’
sp_helpdb
sp_spaceused
sp_who
xp_cmdshell