Datenbanksysteme am Beispiel von MICROSOFT SQL Server

1. Datenbanksysteme am Beispiel von MICROSOFT SQL Server Design, Konzepte, Applikationen und neue M�glichkeiten

2. MS SQL Server: Funktionen Funktionen sind benannte Befehlsfolgen, die einen R�ckgabewert liefern Sie stellen immer eine logische Einheit dar Parameter�bergabe in Form einer Parameterliste Liegen in kompilierter Form in der Datenbank? schnellere Ausf�hrung als einzelne SQL Anweisungen In ANSI SQL sind lediglich die Aggregatfunktionen AVG SUM MIN MAX COUNT definiert

3. MS SQL Server: Funktionen Drei Gruppen von Funktionen Aggregatfunktionen (siehe vorheriges Kapitel)Zusammenfassung mehrerer Werte zu einem R�ckgabewertDeterministische Funktionen Skalare FunktionenVerarbeitung eines WertesDeterministisch und Nicht deterministischz.B.DATEDIFF deterministischGETDATE nicht deterministisch Rowset FunktionenTabellenverweise

4. MS SQL Server: Allgemeine Funktionen

5. MS SQL Server: Allgemeine Funktionen Allgemeiner Aufbau einer Funktion: R�ckgabewert = Fkt ([Parameter[,Parameter]])

6. MS SQL Server: Allgemeine Funktionen Wie rufe ich eine Funktion (fkt) auf?Select fkt(parameter1, parameter2)GO Select fkt(parameter1, parameter2) from tabelleGOSelect fkt(attribut) from tabelleGOSelect attribut from tabelle where attribut1=fkt(parameter1)GOSelect attribut from tabelle where attribut1=fkt(attribut1)GO

7. MS SQL Server: einige wichtige Funktionen System Funktionen IsNull(value, return_value) System_User User_Name() bzw. Current_User String Funktionen + Left(string, anzahl_char) Right(string, anzahl_char) Substr(string, start_position, anzahl_char) Upper(string) Lower(string) Date/Time Funktionen GetDate() Day(date) Month(date) Year(date)

8. Aufgaben

9. ??? Fragen ???

10. MS SQL Server: Benutzerdefinierte Funktionen �hnlich einer Standard Funktion ProblemeKeine komfortable Entwicklungsumgebung Drei Typen Skalarfunktionen Inlinefunktionen mit Tabellenr�ckgabe Komplexe Funktionen mit Tabellenr�ckgabe

11. Variablen Ziel: Speichern von Werten DeklarationDECLARE @VariablenName Datentyp [, @Var2 �] Werte zuweisenSET @Var1=Ausdruck (Empfehlung!)SELECT @Var2 = Ausdruck G�ltigkeitvon der Deklaration bis zum- Ende des Batches (Go)- oder Ende der Funktion/Procedur

12. Verwendung von Variablen Select @Var1 Select @Var1, @Var2 Select @Var2=ma_vorname from mitarbeiter Select @Var2 Set @Var2=�kein eintrag� Select @Var2=ma_vorname from mitarbeiter where ma_nr < 0 Select @Var2

13. Beispiel 1 DECLARE @Var1 int, @Var2 varchar(20), @Var3 datetime set @Var1=67 set @Var2='Das ist ein Test' set @Var3='1.7.2005' select @Var1, @Var2, @Var3 select @Var3=@Var3+@Var1 select @Var1, @Var2, @Var3 Go

14. Beispiel 1: Im Querystudio

15. Beispiel 2 DECLARE @Var2 varchar(20) Select @Var2=ma_vorname from mitarbeiter Select @Var2 Go DECLARE @Var2 varchar(20) Set @Var2='kein eintrag' Select @Var2=ma_vorname from mitarbeiter where ma_nr < 0 Select @Var2 Go

16. Beispiel 2: Im Querystudio

17. Kontrollstrukturen Anweisungsbl�ckeBEGIN�END Bedingte Ausf�hrungIF Ausdruck Anweisung1[ELSE Anweisung2]

18. Beispiele if db_name() <> 'egroiss' use egroiss Go declare @zaehler int set zaehler=0 begin set @zaehler=@zaehler+1 print @zaehler end Go

19. ??? Fragen ???

20. Kontrollstrukturen SchleifenWHILE Ausdruck BEGIN � � END

21. Beispiel declare @zaehler int set @zaehler=0 while @zaehler < 10 begin set @zaehler=@zaehler+1 print @zaehler end print 'Schleife beendet' go

22. Beispiel: Im Querystudio

23. Kontrollstrukturen Schleifen verlassenWHILE Ausdruck BEGIN � BREAK � END

24. Beispiel declare @zaehler int set @zaehler=0 while @zaehler < 10 begin set @zaehler=@zaehler+1 print @zaehler break end print 'Schleife beendet' go

25. Beispiel: Im Querystudio

26. Kontrollstrukturen Schleifen am Beginn fortsetzenWHILE Ausdruck BEGIN � CONTINUE � END

27. Beispiel declare @zaehler int set @zaehler=0 while @zaehler < 7 begin set @zaehler=@zaehler+1 if @zaehler=5 continue else print @zaehler end print 'Schleife beendet' go

28. Beispiel: Im Querystudio

29. Kontrollstrukturen R�cksprung aus der Abfrage oder ProzedurRETURN [Ausdruck]

30. Beispiel � declare @zaehler int set @zaehler=0 while @zaehler < 7 begin set @zaehler=@zaehler+1 if @zaehler=5 continue else print @zaehler end return @zaehler

31. Skalarfunktionen Funktionen CREATE/ALTER FUNCTION funktionsname ([@Var1 datentyp [,�n]]) RETURNS datentyp BEGIN � RETURN END DROP FUNCTION funktionsname

32. Im Enterprisemanager

33. Aufruf von Skalarfunktionen

34. ??? Fragen ???

35. Aufgaben

36. Inlinefunktionen mit Tabellenr�ckgabe CREATE/ALTER FUNCTION funktionsname ([@Var1 datentyp [,�n]]) RETURNS TABLE RETURN ( SQLAnweisung)

37. Beispiel CREATE FUNCTION fktGehalt (@Var1 decimal) RETURNS TABLE RETURN ( select ma_nr, ma_vorname from mitarbeiter where ma_gehalt < @Var1)

38. Beispiel: Im Querystudio

39. Beispiel: Im Querystudio

40. Komplexe Funktionen mit Tabellenr�ckgabe Benutzerdefinierte Funktionen, die einen table-Wert zur�ckgeben, k�nnen leistungsf�hige Alternativen zu Sichten sein. Eine benutzerdefinierte Funktion, die einen table-Wert zur�ckgibt, kann �berall dort verwendet werden, wo Tabellen- oder Sichtausdr�cke in Transact-SQL-Abfragen zul�ssig sind. Sichten sind auf eine einzelne SELECT-Anweisung beschr�nkt, w�hrend benutzerdefinierte Funktionen zus�tzliche Anweisungen enthalten k�nnen, die eine leistungsf�higere Logik als Sichten erm�glichen.

41. Komplexe Funktionen mit Tabellenr�ckgabe CREATE/ALTER FUNCTION funktionsname ([@Var1 datentyp [,�n]]) RETURNS @r_var TABLE <Tabellendefinition> BEGIN � RETURN END

42. Beispiel CREATE FUNCTION fkt_USD_Gehalt () RETURNS @MA_USD_GEHALT TABLE (ma_nachname varchar(80), Gehalt_USD money) BEGIN declare @tmptab table (var1 varchar(80), var2 money) insert @tmptab select ma_nachname,ma_gehalt/1.22 from mitarbeiter insert @MA_USD_GEHALT select var1, var2 from @tmptab RETURN END

43. Beispiel: Im Querystudio

44. Beispiel: Im Querystudio

45. Aufgaben

46. ??? Fragen ???

47. MS SQL Server: Stored Procedure Gespeicherte Prozeduren Wenn Sie eine Anwendung mit Microsoft� SQL Server� 2000 erstellen, stellt die Programmiersprache Transact-SQL die prim�re Programmierschnittstelle zwischen den Anwendungen und der SQL Server-Datenbank dar. Wenn Sie Transact-SQL-Programme verwenden, stehen Ihnen zwei Methoden zur Verf�gung, um Programme zu speichern und auszuf�hren: Sie k�nnen die Programme lokal speichern und Anwendungen erstellen, die Befehle an SQL Server senden und die Ergebnisse verarbeiten, oder Sie k�nnen die Programme in SQL Server als gespeicherte Prozeduren speichern und Anwendungen erstellen, die diese gespeicherten Prozeduren ausf�hren und die Ergebnisse verarbeiten.

48. MS SQL Server: Stored Procedure Gespeicherte Prozeduren in SQL Server gleichen den Prozeduren in anderen Programmiersprachen bez�glich der folgenden Merkmale und F�higkeiten: Annehmen von Eingabeparametern und Zur�ckgeben mehrerer Werte in Form von Ausgabeparametern an die aufrufende Prozedur oder den aufrufenden Batch. Aufnehmen von Programmierungsanweisungen, die Operationen in der Datenbank ausf�hren, einschlie�lich des Aufrufens anderer Prozeduren.  Zur�ckgeben eines Statuswertes an eine aufrufende Prozedur oder einen aufrufenden Batch, der Erfolg oder Fehlschlagen (sowie die Ursache) anzeigt. Sie k�nnen die EXECUTE-Anweisung von Transact-SQL verwenden, um eine gespeicherte Prozedur auszuf�hren. Gespeicherte Prozeduren unterscheiden sich insofern von Funktionen, als sie keine Werte anstelle ihrer Namen zur�ckgeben und nicht direkt in einem Ausdruck verwendet werden k�nnen.

49. MS SQL Server: Stored Procedure CREATE PROC [ EDURE ] procedure_name [ ; number ] ����[ { @parameter data_type } ��������[ VARYING ] [ = default ] [ OUTPUT ] ����] [ ,...n ] [ WITH ����{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] [ FOR REPLICATION ] AS sql_statement [ ...n ]

50. MS SQL Server: Stored Procedure ;number Eine optionale ganze Zahl zum Gruppieren von Prozeduren mit dem gleichen Namen, so dass sie alle mit einer einzigen DROP PROCEDURE-Anweisung gel�scht werden k�nnen. So k�nnen beispielsweise die in einer Anwendung namens orders verwendeten Prozeduren mit orderproc;1, orderproc;2 usw. benannt sein. Die DROP PROCEDURE orderproc-Anweisung l�scht dann die gesamte Gruppe. Wenn der Name begrenzte Bezeichner enth�lt, sollte die Nummer nicht als Teil des Bezeichners eingeschlossen sein. Verwenden Sie die entsprechenden Trennzeichen nur f�r procedure_name.

51. MS SQL Server: Stored Procedure @parameter Ein Parameter in der Prozedur. Sie k�nnen einen oder mehrere Parameter in einer CREATE PROCEDURE-Anweisung deklarieren. Der Benutzer muss beim Ausf�hren der Prozedur den Wert jedes deklarierten Parameters angeben (sofern kein Standardwert f�r den entsprechenden Parameter definiert ist). Eine gespeicherte Prozedur kann maximal 2.100 Parameter haben. Geben Sie einen Parameternamen an, der mit dem Zeichen @ beginnt. Der Parametername muss den Regeln f�r Bezeichner entsprechen. Parameter gelten lokal in der jeweiligen Prozedur, d. h., dass Sie die gleichen Parameternamen in anderen Prozeduren verwenden k�nnen. Parameter k�nnen standardm��ig nur den Platz von Konstanten einnehmen. Sie k�nnen nicht anstelle von Tabellennamen, Spaltennamen oder Namen anderer Datenbankobjekte verwendet werden.

52. MS SQL Server: Stored Procedure data_type Der Parameterdatentyp. Alle Datentypen, einschlie�lich text, ntext und image, k�nnen als Parameter einer gespeicherten Prozedur verwendet werden. Der cursor-Datentyp kann jedoch nur f�r OUTPUT-Parameter verwendet werden. Bei Angabe des cursor-Datentyps m�ssen die Schl�sselw�rter VARYING und OUTPUT ebenfalls angegeben werden.

53. MS SQL Server: Stored Procedure ; VARYING Gibt das als Ausgabeparameter unterst�tzte Resultset an (das dynamisch durch die gespeicherte Prozedur erstellt wird und dessen Inhalt variieren kann). Gilt nur f�r cursor-Parameter. default Ein Standardwert f�r den Parameter. Falls ein Standardwert definiert ist, kann die Prozedur ausgef�hrt werden, ohne dass ein Wert f�r den entsprechenden Parameter angegeben wird. Der Standardwert muss eine Konstante oder NULL sein. Er kann Platzhalterzeichen enthalten, falls die Prozedur den Parameter mit dem LIKE-Schl�sselwort verwendet.

54. MS SQL Server: Stored Procedure OUTPUT Zeigt an, dass es sich bei dem Parameter um einen R�ckgabeparameter handelt. Der Wert dieser Option kann an EXEC[UTE] zur�ckgegeben werden. Verwenden Sie OUTPUT-Parameter, um Informationen an die aufrufende Prozedur zur�ckzugeben. text-, ntext und image-Parameter k�nnen als OUTPUT-Parameter verwendet werden. Ein Ausgabeparameter, der das OUTPUT-Schl�sselwort verwendet, kann ein Cursorplatzhalter sein. n Ein Platzhalter, der anzeigt, dass bis zu 2.100 Parameter angegeben werden k�nnen. {RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION} RECOMPILE zeigt an, dass SQL Server den Plan f�r diese Prozedur nicht zwischenspeichert; die Prozedur wird zur Laufzeit neu kompiliert.

55. MS SQL Server: Stored Procedure ENCRYPTION zeigt an, dass SQL Server den syscomments-Tabelleneintrag verschl�sselt, der den Text der CREATE PROCEDURE-Anweisung enth�lt. Durch die Verwendung von ENCRYPTION wird die Prozedur nicht als Teil der SQL Server-Replikation publiziert. Anmerkung��W�hrend einer Aktualisierung verwendet SQL Server die verschl�sselten Kommentare, die in syscomments gespeichert sind, um verschl�sselte Prozeduren neu zu erstellen. FOR REPLICATION Gibt an, dass f�r die Replikation erstellte gespeicherte Prozeduren nicht auf dem Abonnenten ausgef�hrt werden k�nnen. Eine gespeicherte Prozedur, die mit der Option FOR REPLICATION erstellt wurde, wird als Filter f�r gespeicherte Prozeduren verwendet und nur w�hrend der Replikation ausgef�hrt. Diese Option kann nicht zusammen mit der Option WITH RECOMPILE verwendet werden.

56. MS SQL Server: Stored Procedure AS Gibt die Aktionen an, die die Prozedur ausf�hren soll. sql_statement Transact-SQL-Anweisungen beliebiger Anzahl und beliebigen Typs, die in die Prozedur eingeschlossen werden sollen. Es gelten einige Beschr�nkungen. n Ein Platzhalter, der anzeigt, dass mehrere Transact-SQL-Anweisungen in diese Prozedur eingeschlossen werden k�nnen.

57. MS SQL Server: Stored Procedure Erstellen einer gespeicherten Prozedur mit dem Assistenten zur Erstellung gespeicherter Prozeduren (Enterprise Manager) Erweitern Sie eine Servergruppe und dann den Server, in dem Sie die Sicht erstellen m�chten. Klicken Sie im Men� Extras auf Assistenten. Erweitern Sie Datenbank. Doppelklicken Sie auf Assistent zur Erstellung gespeicherter Prozeduren. F�hren Sie die Schritte des Assistenten vollst�ndig aus.

58. Cursor

59. Cursor-Operationen

60. Beispiel f�r eine Prozedur Aufgabe: 1. Anf�gen der Spalte PRAEMIE an die Tabelle Mitarbeiter 2. Den Mitarbeitern unterschiedliche Einstellungsjahre zuordnen (�ffnen und Bearbeiten im Enterprise Manager) 3. Verteilen von Pr�mien anhand dieser Tabelle ANZAHLJAHRE PRAEMIE 5 5.000 10 10.000 15 15.000 Bei gr�sseren Datenbest�nden ist diese Aufgabe sehr arbeitsintensiv, kann diese Aufgabe nicht automatisiert werden? Doch, durch eine Prozedur!

61. Beispiel f�r eine Prozedur 1/2 CREATE procedure verteile_praemie as declare @praemie15 money; declare @praemie10 money; declare @praemie5 money; declare @praemie money; declare @ma_nr int,@anzahljahre int declare CUR_MITARBEITER CURSOR KEYSET FOR SELECT MA_NR,datediff(year,MA_EINTRITTSDATUM,'31.12.2005')as jahre_im_betrieb FROM MITARBEITER; set @praemie15 = 15000 set @praemie10 = 10000 set @praemie5 = 5000 open cur_mitarbeiter FETCH NEXT FROM CUR_MITARBEITER INTO @ma_nr, @anzahljahre WHILE @@FETCH_STATUS = 0 BEGIN

62. Beispiel f�r eine Prozedur 2/2 if @anzahljahre >=15 set @praemie = @praemie15 else if @anzahljahre >=10 set @praemie = @praemie10 else if @anzahljahre >=5 set @praemie = @praemie5 else set @praemie = null update mitarbeiter set ma_praemie = @praemie where current of CUR_MITARBEITER FETCH NEXT FROM CUR_MITARBEITER INTO @ma_nr, @anzahljahre END CLOSE CUR_MITARBEITER DEALLOCATE CUR_MITARBEITER GO

63. Aufgaben

64. Datenbank - Trigger

65. Erstellt einen Trigger, einen besonderen Typ einer gespeicherten Prozedur, der automatisch ausgef�hrt wird, wenn ein Benutzer versucht, die angegebene Daten�nderungsanweisung f�r die angegebene Tabelle auszuf�hren. Microsoft� SQL�Server� erm�glicht das Erstellen mehrerer Trigger f�r eine angegebene INSERT-, UPDATE- oder DELETE-Anweisung. Syntax CREATE TRIGGER trigger_name ON { table | view } [ WITH ENCRYPTION ] { ����{ { FOR | AFTER | INSTEAD OF } {[DELETE] [,] [INSERT] [,] [UPDATE] } ��������[WITH APPEND] ��������[NOT FOR REPLICATION] ��������AS ��������[ { IF UPDATE ( column ) ������������[ { AND | OR } UPDATE ( column ) ] ����������������[ ...n ] ��������| IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask ) ����������������{ comparison_operator } column_bitmask [ ...n ] ��������} ] ��������sql_statement [ ...n ] ����} } CREATE TRIGGER

66. AFTER Gibt an, dass der Trigger nur dann ausgel�st wird, wenn alle Operationen, die in der den Trigger ausl�senden SQL-Anweisung festgelegt sind, erfolgreich ausgef�hrt wurden. Alle referenziellen CASCADE-Aktionen und Einschr�nkungs�berpr�fungen m�ssen ebenfalls erfolgreich ausgef�hrt worden sein, bevor dieser Trigger ausgef�hrt wird. AFTER ist die Standardeinstellung, wenn FOR als einziges Schl�sselwort angegeben ist. AFTER-Trigger k�nnen f�r Sichten nicht definiert werden. CREATE TRIGGER

67. INSTEAD OF Gibt an, dass der Trigger anstelle der triggerausl�senden SQL-Anweisung ausgef�hrt wird, wodurch die Aktionen der triggerausl�senden Anweisung au�er Kraft gesetzt werden. Es kann nur maximal ein INSTEAD OF-Trigger pro INSERT-, UPDATE- oder DELETE-Anweisung f�r eine Tabelle oder Sicht definiert werden. Es ist jedoch m�glich, Sichten f�r Sichten zu definieren, wobei jede Sicht �ber einen eigenen INSTEAD OF-Trigger verf�gt. CREATE TRIGGER

68. INSTEAD OF-Trigger sind in aktualisierbaren Sichten, f�r die WITH CHECK OPTION festgelegt ist, nicht zul�ssig. SQL�Server l�st einen Fehler aus, falls ein INSTEAD OF-Trigger zu einer aktualisierbaren Sicht hinzugef�gt wird, die mit WITH CHECK OPTION angegeben wurde. Der Benutzer muss die Option mithilfe von ALTER VIEW entfernen, bevor der INSTEAD OF-Trigger definiert wird. { [DELETE] [,] [INSERT] [,] [UPDATE] } Schl�sselw�rter, die angeben, welche Daten�nderungsanweisungen den Trigger aktivieren, wenn sie auf diese Tabelle oder Sicht angewendet werden. Es muss mindestens eine Option angegeben werden. Die Optionen k�nnen in beliebiger Kombination und Reihenfolge in der Triggerdefinition angegeben werden. Wenn Sie mehrere Optionen angeben, trennen Sie diese durch Kommas. F�r INSTEAD OF-Trigger ist die Option DELETE nicht f�r Tabellen mit einer referenziellen Beziehung untereinander zul�ssig, wenn f�r ON DELETE die Option CASCADE angegeben ist. Ebenso ist die Option UPDATE nicht f�r Tabellen mit einer referenziellen Beziehung untereinander zul�ssig, wenn f�r ON UPDATE die Option CASCADE angegeben ist. CREATE TRIGGER

69. Enterprise Manager CREATE TRIGGER 

70. Query Analyzer CREATE TRIGGER 

71. TRIGGER-Beispiel 

72. Aufgaben