Vortrag - Hochschule für Technik und Wirtschaft Dresden

Transcrição

Fachbereich Informatik/Mathematik
Prof. Dr. Wloka
125. Datenbankstammtisch
Hochschule für Technik und Wirtschaft Dresden (FH)
Prof. Dr. Wloka
Entwicklung des SQL-Standard
vom Hoffnungsträger zum Ideengrab
Prof. Dr. Wloka
Gliederung
1 Die Anfänge: SQL:1986, SQL:1989
2 Die Entwicklung
2.1 SQL:1992
2.2 SQL:1999
2.3 SQL-Standard und DBMS-Entwicklung
3 Der Stand: SQL:2003
3.1 Überblick SQL:2003
3.2 Feature und Konformität der DBMS am Beispiel
4
Die Zukunft: SQL:200n
4.1 Überblick SQL:200n
4.2 neue Feature bei Part2, Part11, Part 14
Prof. Dr. Wloka
Hoffnungsträger
•
•
•
•
einfach verständliches Datenmodell
Deskriptive Sprache
Mengenorientierte Sprache
mächtige Sprache
Nur 30 Befehle
Sprache für Endanwender
Prof. Dr. Wloka
Entwicklung SQL:1986 bis SQL:1992
SQL 0:
1986
1987
SQL 1:
SQL 2:
SQL:1986
International edition
1989
1992
SQL:1989
SQL:1992
1995
SQL/CLI (Part 3)
1996
SQL/PSM (Part 4)
Prof. Dr. Wloka
Gliederung
2 Die Entwicklung
2.1 SQL:1992
2.2 SQL:1999
4
4.2 neue Feature bei Part2, Part11, Part14
Prof. Dr. Wloka
Niveau‘s von SQL:1992 (Beispiele)
SQL89
ENTRY SQL
DATE- und TIME-Datentypen, Varchar, nationale String-Operatoren, CAST,
CASE expressions, CREATE DOMAIN,
ALTER TABLE, DROP, CASCADE, JOIN-Typen NATURAL, OUTER und
UNION JOIN, SET TRANSACTION options for ISOLATION LEVEL
UNION, INTERSECTION, Unterabfragen in Ausdrücken und Tab.operationen
INTERMEDIATE
SQL
Verbindungsmanagement CONNECT, SET CONNECTION and DISCONNECT
BIT-Datentyp, vordefinierte Zeichentransformationen
abgeleitete Tabellen im FROM-Abschnitt
Unterabfragen in CHECK-Klauseln, ALTER DOMAIN, ASSERTION constraint
temporäre Tabellen, MATCH FULL und MATCH PARTIAL in referential constraint
character set collations and translations
UNION JOIN und CROSS JOIN
voll änderbarer Cursors, nichtsensitiver Cursor,
FULL SQL
Prof. Dr. Wloka
Entwicklung SQL:1999
SQL 3:
1999
SQL:1999
3: CLI
4: PSM
1: Framework
5: Bindings
2: Foundation
2000
Technische Berichtigungen
OLAP Erweiterungen
2001
SQL/MED (Part 9)
SQL/OLB (Part 10)
2002
SQL/JRT (Part 13)
Technische Berichtigungen
Prof. Dr. Wloka
SQL-Standard und DBMS
•
Bis 1992 enthielt der Standard minimale Anforderungen, die die gängigen DBMS
weitgehend erfüllten
•
1992 erster „kompletter“ Standard
DBMS erfüllen die Niveau‘s „intermediate“ und „full“ nur unzureichend /Melt05/
•
SQL:1999 nimmt sehr viele neuen Anforderungen auf,
nach zu langer Entwicklungszeit nicht der wahre Erfolg, /Melt05/
Standard nicht umfangreich umgesetzt
DBMS „hinken“ hinterher
Abweichungen in 2 Richtungen:
Befehle werden nur teilweise implementiert oder sind nicht in der
standardisierten Form vorhanden.
Es wird der Funktionsumfang in inkompatibler Weise erweitert,
um sich von der Konkurrenz abzugrenzen bzw. einen Marktvorteil zu erreichen.
Prof. Dr. Wloka
Funktionsexplosion beim Standard und bei DBMS
Neue
Java
Anforderungen
SQL:1999
Objektorientierung
Multimedia
Information
Retrieval
Internet
XML
Strukturen in Tabellen
Strukturen zwischen Tabellen
Contentmanagement
Data Warehouse
OLAP / Data Mining
Prof. Dr. Wloka
SQL-Standard und DBMS
Meist ist der Standard den DBMS voraus.
Aber in einzelnen Feature sind DBMS der Standardisierung
voraus (dargestellt am Beispiel von SYBASE)
- Stored Procedure
- Trigger
- Benutzerdefinierte Datentypen
- ALTER/DROP TABLE
- Replikation
- Einbindung von XQuery-Ausdrücken in SQL
(mit XMLEXTRACT)
Prof. Dr. Wloka
Gliederung
2 Die Entwicklung
2.1 SQL:1992
2.2 SQL:1999
4 Die Zukunft: SQL:200n
4.2 neue Feature bei Part2, Part11, Part14
Prof. Dr. Wloka
SQL:2003
SQL 4 (SQL:2003) endgültig 2005 mit den folgenden
9 Teilen als ISO/IEC 9075-Teile-Nr.:2003 veröffentlicht:
/Melt03a/
•
•
•
Part 1 – SQL/Framework
Part 2 – SQL/Foundation
Part 3 – SQL/CLI
•
Part 4 – SQL/PSM
•
Part 9 – SQL/MED
•
•
•
Part 10 – SQL/OLB
Part 11 – SQL/Schemata
Part 13 – SQL/JRT
•
Part 14 – SQL/XML
Rahmen
Grundlagen
Call Level Interface
Persistent Stored Modules
Management of External Data
Object Language Bindings
Schemata
Java Routines and Types
Extensible Markup Language
Prof. Dr. Wloka
SQL:2003
• Wesentliche Struktur-Änderungen
– Bindings in Foundation integriert
– Information/Definition Schema wurde zu einem separatem
Part (Schemata)
– Neuer Part: SQL/XML
Prof. Dr. Wloka
„verlorene“ Teile – „Ideengräber“?
• Part 5 – SQL/Bindings
– 2003 zurück in Foundation
• Part 6 – SQL/Transaction
– 1999 eingestellt
• Part 7 – SQL/Temporal
– 2003 zurück gezogen
• Part 8 – SQL/Objects
– 1999 zurück in Foundation
• Part 12 – SQL/Replication
– Abgebrochen wegen Mängel beim Entwicklungprozess
Prof. Dr. Wloka
Überblick SQL:2003 /Dess05/
1: Framework
3: CLI
4: PSM
9: MED
10: OLB
13: JRT
(4) PSM
11: Schemata
2: Foundation
optionale
Features
(1) Enhanced
Date/Time Fac.
verbindliche
Features
n: xxx
(n) xxx
Core SQL
... ein Part
... ein Package
(2) Enhanced Integrity
Management
(7) Enhanced
Objects
(8) Active
Databases
(6) Basic
Objects
(10) OLAP
14: XML
Prof. Dr. Wloka
SQL 2003 :Standard
SQL: 2003 Standard
Total Fetaures : 573
3%
2%
9%
0%
Framework (Packages)
Foundation (420)
CLI (8)
PSM (30)
MED (25)
OLB (9)
Schemata (11)
JRT (17)
XML (53)
2%
4%
5%
1%
74%
Prof. Dr. Wloka
Feature von Core SQL:2003
Core SQL:2003 Features
• SQL:92 Features
– Alle des SQL:92 Entry-Levels
– Einige Intermediate SQL:92 Features
– Einige Full SQL:92 Features
• SQL:1999 Features
• SQL:2003 hat keine neuen Features zum Core
hinzugefügt
Prof. Dr. Wloka
Neue Funktionalitäten von SQL-Foundation(Auswahl)
Überblick
•
SQL:2003/ Foundation enthält alle Feature von SQL:1999/ Foundation
(mit vielen Korrekturen - SQL:2003 „bugfix-release“) /Melt05/
sowie eine Reihe von neuen Features
– SQL:1999 Part 5: SQL/Bindings und Part 8 :SQL/Objects wurden integriert
•
Neue Datentypen
– BIGINT
– MULTISET
•
Erweiterungen existenter Datentypen
– unbegrenztes ARRAY
•
Löschung existenter Datentypen
– BIT
– BIT VARYING
•
Neue Schemaobjekte
– Sequenzgeneratoren (inkrementelle Erzeugung von Werten)
Prof. Dr. Wloka
Überblick
• Verbesserungen existenter Schemaobjekte
– Identity Spalten für Tabellen (Generierung von Schlüsselwerten)
– Generierte Spalten für Tabellen
• Werte werden von anderen Attributwerten abgeleitet
• Nicht konform zur 3. Normalform
–
–
–
–
–
–
–
Umfangreiche Verbesserungen bei CREATE TABLE LIKE
Basistabellendefinition mittels Anfrage (materialisierte Tabellen)
retrospektive Check Constraints
ALTER-Funktionalität für Transformierungen
SQL-invoked Routines mit Rechten des Aufrufenden
Table Functions (tabllenwertige Funktionen)
Dynamische und Schema Statements innerhalb Routinenkörper
Prof. Dr. Wloka
Überblick
•
•
Neue Funktionen (z. B. zahlreiche OLAP Funktionen“)
Ausdrücke
– Erweiterungen bei skalaren Ausdrücke und Anfrageausdrücken
– Multiset-Ausdrücke
– Anfragen auf Tabellensamples
•
•
•
•
•
Erweiterung der Routinenaufrufe
Neue Prädikate und DML Statements (z.B. Statement zur BatchVerarbeitung von INSERT und UPDATE Statements)
Geschachtelte Savepoints
Verbessertes Diagnose-Management
Verbesserungen bei PREPARE Statement
Prof. Dr. Wloka
Neue Funktionalitäten von SQL-Foundation
Sequenzgeneratoren
• Sequenzgeneratoren werden primär zur Generierung
von künstlichen Schlüsseln, beim Einfügen neuer Tupel
in Tabellen, eingesetzt
• Syntax
CREATE SEQUENCE < sequence name > AS <typename>
[START WITH <initial value>]
[INCREMENT BY <increment value >]
[NO MINVALUE|MINVALUE <minimal value>]
[NO MAXVALUE|MAXVALUE <maximal value>]
[NO CYCLE |CYCLE]
• Bei der Wertdefinition sind Restriktionen zu beachten
Prof. Dr. Wloka
Sequenzgeneratoren
•
•
Zum automatischen Generieren von sequentiellen Werten genutzt
Zwei Arten:
– Extern: Explizites Schemaobjekt mit CREATE Statement erzeugt
– Intern: Implizit mit einer Identity Spalte erzeugt
•
Generierung eines weiteren Wertes des Sequenzgenerators
INSERT INTO Mit (Mitnr,...) VALUES (NEXT VALUE FOR < sequence name >, …)
•
ALTER Statement zum Ändern der Schrittweite, Maximum-,
Minimum-Wert, Zyklus-Option oder eines neuen Basiswertes
ALTER SEQUENCE < sequence name >
RESTART WITH 500 INCREMENT BY 2
•
DROP SEQUENCE Statement löscht einen Sequenzgenerator
Prof. Dr. Wloka
Identity Spalten
• Maximal eine Spalte einer Basistabelle kann zu einer
Identity Spalte designiert werden
• Werte für eine Identity Spalte werden automatisch
zugewiesen, wenn eine Zeile in die Tabelle eingefügt wird
• Ein interner Sequenzgenerator wird mit jeder Identity
Spalte assoziiert (lediglich konzeptionell - dies muss nicht
zwingend so implementiert sein)
• Gleiche Optionen wie für Sequenzgenerator
– Datentyp, Startwert, Schrittweite, Maximum-Wert, Minimum-Wert
und Zyklus-Option
Prof. Dr. Wloka
Identity Spalten
• Beispiel
– Identity Spalte nutzt einen internen Sequenzgenerator
CREATE TABLE Mit
(
Mitnr INTEGER
GENERATED ALWAYS AS IDENTITY
START WITH 100
INCREMENT 1
MINVALUE 100
NO MAXVALUE
NO CYCLE,
Name VARCHAR(64),
...
)
Prof. Dr. Wloka
Generierte Spalten
• Beliebige Anzahl von Spalten einer Basistabelle kann
als generierte Spalte designiert werden
• Muss mit einem skalaren Ausdruck assoziert werden
– Referenzierte Spalten in solchen Ausdrücken müssen
entweder Spalten der Basistabelle oder die generierte Spalte
selbst sein
• Falls ein Typ spezifiziert wird muss dieser mit dem Typ
des Ausdrucks korrespondieren
• Die Werte der generierten Spalte werden automatisch
bei dem Einfügen einer Zeile errechnet und zugewiesen
• Widerspricht der 3. Normalform
Prof. Dr. Wloka
Generierte Spalten
• Beispiel
CREATE TABLE Mit
(
Mitnr INTEGER,
Gehalt DECIMAL(7,2),
Bonus DECIMAL(7,2),
GesamtEinkommen GENERATED ALWAYS AS ( Gehalt + Bonus ),
Sachbearbeiter GENERATED ALWAYS AS ( CURRENT_USER )
)
Prof. Dr. Wloka
Tabellendefinition mittels Anfragen
•
•
•
Tabellen können einfach erstellt werden indem das Ergebnis einer
Anfrage persistent gemacht wird
Eigenschaften der Spalten werden von den Basistabellen der
Anfrage abgeleitet
Die Definition und/oder der Inhalt einer Basistabelle kann mit
einem Anfrageausdruck generiert werden
– Falls WITH NO DATA spezifiziert ist, wird eine leere Tabelle erstellt
•
•
"Materialisierte Sicht" - Aktualisierungsmechanismus ist bislang
nicht standardisiert
Syntax CREATE TABLE <tablename> [(columnnamelist)]
AS <predicate> WITH [NO] DATA
– <predicate> meint hier den Anfrageausdruck
Prof. Dr. Wloka
Konformität der DBMS zum Standard
/Dess05/
Konformität zu SQL:2003
• SQL:92 nutzt inkrementelle Ebenen der Konformität
(Entry, Intermediate, Full)
– Kein DBMS erreichte Intermediate oder Full
/Dess05/, /sql-06/
• SQL:1999 und SQL:2003 bestehen aus einer Vielzahl von
kleinen “Features“, welche identifiziert und deren Inhalte
präzise spezifiziert sind
• jedes Feature ist entweder als Bestandteil von “Core SQL“
oder nicht als Bestandteil von Core SQL spezifiziert
• Ein “non-core feature“ kann als Bestandteil eines der
benannten und definierten “Packages“ spezifiziert sein,
welche alle auf der Konformität zu Core SQL aufbauen
Prof. Dr. Wloka
Konformität der DBMS zum Standard
Konformität zu SQL:2003
• Implementierungen können folgende Ebenen erreichen
– Minimum conformance (Core SQL)
• Konformität zu bestimmten Features des Part 2 (Foundation) und 11
(Schema)
– Conformance to a part (0 oder mehrere)
• Verbindliche Features und zusätzl. Konformitätserfordernisse eines Parts
– Conformance to a package (0 oder mehrere)
• Ein Package ist eine Gruppe optionaler Features
• Teile und Feature erfordert durch ein Package
– Conformance to optional feature (0 oder mehrere)
• Kann Konformität zu anderen Features eines Parts implizieren
• Kein DBMS erreicht Minimum conformance
/Dess05/
Prof. Dr. Wloka
Part 2 – SQL/Foundation; Data Types
Prof. Dr. Wloka
Konformität der neuen Funktionalitäten von SQL-Foundation
Feature ID/ Name
DB2
UDB v.9.1
Microsoft SQL
Server 2005
Oracle
10g R2
Sybase
ASE 15
T071 - BIGINT data type
Unterstützt
Unterstützt
-
Unterstützt
Unterstützt
(Teilmenge –
Merge joins )
F312 - MERGE statement
Unterstützt
-
Partielle Stütze
T172 - AS subquery clause in
table definition
Unterstützt
-
-
T174 - Identity columns
Unterstützt
Unterstützt
-
Unterstützt
T175 - Generated columns
Unterstützt
Unterstützt
Partielle Stütze
Unterstützt
T176 - Sequence generator
support
Unterstützt
-
(proprietäre
Syntax)
Unterstützt
T641 - Multiple column
assignment
Unterstützt
-
-
-
T272 - Enhanced savepoint
management
-
Unterstützt
-
Unterstützt
/Dess05/
Prof. Dr. Wloka
Konformität bei Core-SQL
/IBM06/, /Micr06/, /Orac05/, /Syba06/, [1]
Features(164)
Core SQL Features
180
160
140
120
100
80
60
40
20
0
98.8%
97.6%
99.4%
152
143
141
19
10
2.4%
4
DB2 V9
Full Support
Full Support
Partial Support
No Support
1.2%
2
Oracle 10g
96.9%
141
20
0.6%
18
1
3.1%
5
Sybase ASE15 MS SQL Server
2005
RDBM S
Partial Support
No Support
= Same semantic and syntax
= Semantic/Syntax support with different Syntax/Semantic
= Feature not implemented
Prof. Dr. Wloka
Konformität bei SQL-Core
Feature
ID
Feature description
Oracle
10g
E011
Numeric data types
9
9
9
9
E011-01
INTEGER and SMALLINT data types
(including all spellings)
9
9
9
9
E011-02
REAL, DOUBLE PRECISION, and FLOAT data
types
9
9
9
9
E011-03
DECIMAL and NUMERIC data types
9
9
9
9
E011-04
Arithmetic operators
9
9
9
9
E011-05
Numeric comparison
9
9
9
9
E011-06
Implicit casting among the numeric data types
9
9
9
9
Legend : 9 Fully Supported
8 Not Supported
DB2
V9
Sybase
ASE 15
MS
SQL
2005
; Partially Supported
Prof. Dr. Wloka
Feature
ID
Feature description
Oracle
10g
DB2 V9
Sybase
ASE 15
MS
SQL
2005
E021
Character string types
;
;
;
;
E021-01
CHARACTER data type (including all its spellings)
9
9
9
9
E021-02
CHARACTER VARYING data type (including all its
spellings)
;
9
;
;
E021-03
Character literals
;
9
;
9
E021-04
CHARACTER_LENGTH function
;
;
;
;
E021-05
OCTET_LENGTH function
;
;
;
;
E021-06
SUBSTRING function
;
9
;
;
E021-07
Character concatenation
9
9
;
;
E021-08
UPPER and LOWER functions
9
9
9
9
E021-09
TRIM function
9
9
9
9
E021-10
Implicit casting among the fixed and variable length
character string types
9
9
9
9
E021-11
POSITION function
;
;
;
;
E021-12
Character comparison
9
9
9
9
Prof. Dr. Wloka
Feature
ID
Feature description
Oracle
10g
DB2
V9
Sybase
ASE 15
MS SQL
2005
E031
Identifiers
9
9
9
9
E031-01
Delimited identifiers
9
9
9
9
E031-02
Lower case identifiers
9
9
9
9
E031-03
Trailing underscore
9
9
9
9
E051
Basic query specification
;
;
;
;
E051-01
SELECT DISTINCT
9
9
9
9
E051-02
GROUP BY clause
9
9
9
9
E051-04
GROUP BY can contain columns not in <select list>
9
9
9
9
E051-05
Select list items can be renamed
9
9
9
9
E051-06
HAVING clause
9
9
9
9
E051-07
Qualified * in select list
9
9
9
9
E051-08
Correlation names in the FROM clause
;
9
;
;
E051-09
Rename columns in the FROM clause
8
;
8
8
Prof. Dr. Wloka
Feature
ID
Feature description
Oracle
10g
DB2
V9
Sybase
ASE 15
MS
SQL
2005
E061
Basic predicates and search conditions
9
9
9
9
E061-01
Comparison predicate
9
9
9
9
E061-02
BETWEEN predicate
9
9
9
9
E061-03
IN predicate with list of values
9
9
9
9
E061-04
LIKE predicate
9
9
9
9
E061-05
LIKE predicate: ESCAPE clause
9
9
9
9
E061-06
NULL predicate
9
9
9
9
E061-07
Quantified comparison predicate
9
9
9
9
E061-08
EXISTS predicate
9
9
9
9
E061-09
Subqueries in comparison predicate
9
9
9
9
E061-11
Subqueries in IN predicate
9
9
9
9
E061-12
Subqueries in quantified comparison predicate
9
9
9
9
E061-13
Correlated subqueries
9
9
9
9
E061-14
Search condition
9
9
9
9
Prof. Dr. Wloka
Feature ID
Feature description
Oracle 10g
DB2 V9
Sybase
ASE 15
MS SQL
2005
E071
Basic query expressions
9
9
9
9
E071-01
UNION DISTINCT table operator
9
9
9
9
E071-02
UNION ALL table operator
9
9
9
9
E071-03
EXCEPT DISTINCT table operator
9
9
9
9
E071-05
Columns combined via table operators need not
have exactly the same data type.
9
9
9
9
E071-06
Table operators in subqueries
9
9
9
9
E081
Basic Privileges
9
9
9
9
E081-01
SELECT privilege at the table level
9
9
9
9
E081-02
DELETE privilege
9
9
9
9
E081-03
INSERT privilege at the table level
9
9
9
9
E081-04
UPDATE privilege at the table level
9
9
9
9
E081-05
UPDATE privilege at the column level
9
9
9
9
E081-06
REFERENCES privilege at the table level
9
9
9
9
E081-07
REFERENCES privilege at the column level
9
9
9
9
E081-08
WITH GRANT OPTION
9
9
9
9
E081-09
USAGE privilege
9
9
9
9
E081-10
EXECUTE privilege
9
9
9
9
Prof. Dr. Wloka
Feature
ID
Feature description
Oracle
10g
DB2
V9
Sybase
ASE 15
MS SQL
2005
E091
Set functions
9
9
9
9
E091-01
AVG
9
9
9
9
E091-02
COUNT
9
9
9
9
E091-03
MAX
9
9
9
9
E091-04
MIN
9
9
9
9
E091-05
SUM
9
9
9
9
E091-06
ALL quantifier
9
9
9
9
E091-07
DISTINCT quantifier
9
9
9
9
E101
Basic data manipulation
9
9
9
9
E101-01
INSERT statement
9
9
9
9
E101-03
Searched UPDATE statement
9
9
9
9
E101-04
Searched DELETE statement
9
9
9
9
E111
Single row SELECT statement
9
9
9
9
Prof. Dr. Wloka
Oracle 10g
DB2
V9
Sybase
ASE 15
MS
SQL
2005
Feature
ID
Feature description
E121
Basic cursor support
;
9
;
;
E121-01
DECLARE CURSOR
9
9
9
9
E121-02
ORDER BY columns need not be in select list
9
9
9
9
E121-03
Value expressions in ORDER BY clause
9
9
9
9
E121-04
OPEN statement
9
9
9
9
E121-06
Positioned UPDATE statement
9
9
9
9
E121-07
Positioned DELETE statement
9
9
9
9
E121-08
CLOSE statement
9
9
9
9
E121-10
FETCH statement: implicit NEXT
9
9
9
;
E121-17
WITH HOLD cursors
;
9
;
8
Prof. Dr. Wloka
Feature
ID
Feature description
Oracle
10g
DB2 V9
Sybase
ASE 15
MS
SQL
2005
E131
Null value support (nulls in lieu of values)
9
9
9
9
E141
Basic integrity constraints
9
9
9
9
E141-01
NOT NULL constraints
9
9
9
9
E141-02
UNIQUE constraints of NOT NULL columns
9
9
9
9
E141-03
PRIMARY KEY constraints
9
9
9
9
E141-04
Basic FOREIGN KEY constraint with the NO ACTION
default for both referential delete action and referential
update action.
9
9
9
9
E141-06
CHECK constraints
9
9
9
9
E141-07
Column defaults
9
9
9
9
E141-08
NOT NULL inferred on PRIMARY KEY
9
9
9
9
E141-10
Names in a foreign key can be specified in any order
9
9
9
9
E151
Transaction support
9
9
9
9
E151-01
COMMIT statement
9
9
9
9
E151-02
ROLLBACK statement
9
9
9
9
Prof. Dr. Wloka
Oracle 10g
DB2 V9
Sybase
ASE 15
MS SQL
2005
Feature ID
Feature description
E152
Basic SET TRANSACTION statement
9
9
9
9
E152-01
SET TRANSACTION statement:
ISOLATION LEVEL SERIALIZABLE clause
9
9
9
9
E152-02
SET TRANSACTION statement:
READ ONLY and READ WRITE clauses
9
9
9
9
E153
Updatable queries with subqueries
9
9
9
9
E161
SQL comments using leading double minus
9
8
9
8
E171
SQLSTATE support
9
9
9
9
E182
Module language
;
;
;
;
F031
Basic schema manipulation
;
;
;
;
F031-01
CREATE TABLE statement to create persistent
base tables
9
9
9
9
F031-02
CREATE VIEW statement
9
9
9
9
F031-03
GRANT statement
9
9
9
9
F031-04
ALTER TABLE statement: ADD COLUMN clause
;
9
;
;
F031-13
DROP TABLE statement: RESTRICT clause
8
9
9
9
F031-16
DROP VIEW statement: RESTRICT clause
8
9
9
9
F031-19
REVOKE statement: RESTRICT clause
8
8
;
8
Prof. Dr. Wloka
Feature ID
Feature description
Oracle
10g
DB2 V9
Sybase
ASE 15
MS
SQL
2005
F041
Basic joined table
9
9
9
9
F041-01
Inner join (but not necessarily the INNER keyword)
9
9
9
9
F041-02
INNER keyword
9
9
9
9
F041-03
LEFT OUTER JOIN
9
9
9
9
F041-04
RIGHT OUTER JOIN
9
9
9
9
F041-05
Outer joins can be nested
9
9
9
9
F041-07
The inner table in a left or right outer join can also be used in an inner join
9
9
9
9
F041-08
All comparison operators are supported (rather than just =)
9
9
9
9
F051
Basic date and time
9
9
9
9
F051-01
DATE data type (including support of DATE literal)
9
9
9
9
F051-02
TIME data type (including support of TIME literal)
9
9
9
9
F051-03
TIMESTAMP data type (including support of TIMESTAMP literal)
9
9
9
9
F051-04
Comparison predicate on DATE, TIME, and TIMESTAMP data types
9
9
9
9
F051-05
Explicit CAST between datetime types and character string types
9
9
9
9
F051-06
CURRENT_DATE
9
9
9
9
F051-07
LOCALTIME
9
9
9
9
F051-08
LOCALTIMESTAMP
9
9
9
9
Prof. Dr. Wloka
Feature ID
Feature description
Oracle
10g
DB2
V9
Sybase
ASE 15
MS SQL
2005
F081
UNION and EXCEPT in views
9
9
9
9
F131
Grouped operations
9
9
9
9
F131-01
WHERE, GROUP BY, and HAVING clauses supported in
queries with grouped views
9
9
9
9
F131-02
Multiple tables supported in queries with grouped views
9
9
9
9
F131-03
Set functions supported in queries with grouped views
9
9
9
9
F131-04
Subqueries with GROUP BY and HAVING clauses and
grouped views
9
9
9
9
F131-05
Single row SELECT with GROUP BY and HAVING
clauses and grouped views
9
9
9
9
F181
Multiple module support
9
9
9
9
F201
CAST function
9
9
9
9
F221
Explicit defaults
9
9
9
9
F261
CASE expression
9
9
9
9
F261-01
Simple CASE
9
9
9
9
F261-02
Searched CASE
9
9
9
9
F261-03
NULLIF
9
9
9
9
F261-04
COALESCE
9
9
9
9
Prof. Dr. Wloka
Feature ID
Feature description
Oracle
10g
DB2
V9
Sybase
ASE 15
MS
SQL
2005
F311
Schema definition statement
9
9
9
9
F311-01
Create Schema
9
9
9
9
F311-02
CREATE TABLE for persistent
base tables
9
9
9
9
F311-03
Create View
9
9
9
9
F311-04
Create view with option
9
9
9
9
F311-05
Grant Statement
9
9
9
9
F471
Scalar subquery values
9
9
9
9
F481
Expanded NULL predicate
9
9
9
9
F812
Basic flagging
SQL -92
;
;
;
S011
Distinct data types
;
;
;
8
Prof. Dr. Wloka
Feature
ID
Feature description
Oracle
10g
T321
Basic SQL-invoked routines
;
9
;
;
T321-01
User-defined functions with no overloading
Syntactic
9
9
;
T321-02
User-defined stored procedures with no overloading
Syntactic
9
9
;
T321-03
Function invocation
9
9
9
9
T321-04
CALL statement
9
9
;
9
T321-05
RETURN statement
9
9
9
9
T631
IN predicate with one list element
9
9
9
9
Legend
9 Fully Supported
8 Not Supported
DB2
V9
Sybase
ASE 15
MS
SQL
2005
; Partially Supported
Prof. Dr. Wloka
Part 11 – SQL/Schemata /IBM06/, /Micr06/, /Orac05/, /Syba06/
, [2]
Features(16)
Features(16)
SQL/Schemata--Mandatory
Mandatory Features
SQL/Schemata
Features
16
1416
14
1212
1010
88
66
4
4
2
20
0
93.75%
81.25%
75%
56.2%
56.2%
43.8%
1212
62.5%
11
77
8
6.25%
0
3
Oracle 10g
Oracle 10g
68.75%
10
10
99
0
62.5%
3
18.75%
3
1
1
2
DB2 V9
3
3
Sybase ASE15
DB2 V9
Sybase ASE15
RDBMS
RDBMS
Full Support
Full Support
•
•
•
Full Support
Partial Support
No Support
31.25%
Partial Support
No Support
Partial
Support No Support
= Same semantic and syntax
= Semantic/Syntax support with different Syntax/Semantic
= Feature not implemented
5
5
31
MS SQL Server
MS SQL2005
Server 2005
Prof. Dr. Wloka
SQL/Schemata – Mandatory Conformance
Feature
ID
Feature Description
Oracle
10g
DB2
V9
Sybase
ASE 15
MS SQL
2005
F021
Basic information schema
9
9
9
9
F021-01
COLUMNS view
9
9
9
9
F021-02
TABLES view
9
9
9
9
F021-03
VIEWS view
9
9
9
9
F021-04
TABLE_CONSTRAINTS view
9
9
9
9
F021-05
REFERENTIAL_CONSTRAINTS view
9
9
9
9
F021-06
CHECK_CONSTRAINTS view
9
9
9
9
F501
Features and conformance views
8
;
;
8
F501-01
SQL_FEATURES view
8
8
8
8
F501-02
SQL_SIZING view
8
9
9
8
F501-03
SQL_LANGUAGES view
8
9
8
8
S011
Distinct data types
9
9
9
;
S011-01
USER_DEFINED_TYPES view
9
9
9
;
T321
Basic SQL-invoked routines
8
;
;
;
T321-06
ROUTINES view
8
9
9
9
T321-07
PARAMETERS view
8
;
8
8
9 Full Supported
; Partial Support
8 Not Supported
Prof. Dr. Wloka
Part 11 – SQL/Schemata /IBM06/, /Micr06/, /Orac05/, /Syba06/,
[2]
SQL/Schemata - Optional Features
F e a tu r e s (1 2 )
12
91.6%
10
8
9
66.7%
6
5
4
2
3
83.3%
6
33.3%
4
8.4%
2
0
Oracle 10g
1
DB2 V9
4
83.3%
5
16.7%
2
Sybase ASE15
RDBMS
Full Support Partial Support No Support
5
16.7%
2
MS SQL Server 2005
Prof. Dr. Wloka
SQL/Schemata – Optional Features
Feature
ID
Feature description
Oracle
10g
DB2
V9
Sybase
ASE 15
MS SQL
2005
F231
Privilege tables
9
9
9
;
F341
Usage tables
9
9
9
9
F391
Long identifiers
9
;
9
;
F502
Enhanced documentation tables
8
9
;
8
S023
Basic structured types
;
9
;
;
S024
Enhanced structured types
8
8
8
8
T011
Timestamp in Information Schema
8
9
8
;
T175
Generated columns
;
9
;
9
T176
Sequence generator support
;
9
9
9
T211
Basic trigger capability
;
;
;
;
T272
Enhanced savepoint management
8
9
9
9
T331
Basic Roles
;
9
9
9
9Full Supported
; Partial Support
8 Not Supported
Prof. Dr. Wloka
SQL und Java
SQL-Java
• SQLJ Part 0: Embedded SQL in Java
• SQLJ Part 1: Routinen in Java
– Implementierung von SQL-Routinen in Java
• SQLJ Part 2: SQL-Typen in Java
– Implementierung von strukturierten Datentypen in Java
• SQL:2003 und SQL-Java
SQL:2003
SQLJ
SQL / OLB (Part 10)
SQLJ Part 0
SQL / JRT (Part 13)
SQLJ Part 1
SQLJ Part 2
Prof. Dr. Wloka
SQL/JRT (Java Routines and Types)
User-defined routines
Definierte SQL Aufrufe
(SQL:2003)
Stored procedure
User-defined function
SQL Routine
(SQL / PSM)
( - IN/OUT-Parameter
- dynamisches
ResultSet
- CALL-Statement )
Definierte Implementierung
(SQLJ Part 1)
Java Klassen
External Routine
(C, Java, ...)
( - Ausschließlich IN-Parameter
- definierte Datentypen für Ergebnis
- innerhalb beliebiger DML-Befehle)
(Java Klassen implementieren
Verhalten von SP und UDF
komp. Javacode in DB installiert)
Prof. Dr. Wloka
SQLJ Part 1: SQL-Routinen die Java nutzen
•
Statische Java Methoden um SP und UDF zu implementieren
– Konvertierung der Parametertypen, Error-/Exception Handler
– Stored Procedure: einige Spezifika
• OUT und INOUT Parameter (Return Parameter)
– Java Methode deklariert diese als Array
CREATE PROCEDURE avgGehalt(IN gruppe VARCHAR(30), OUT gehalt DECIMAL(10,2))
public static void durchschnittsGehalt(String gruppe, BigDecimal[] gehalt);
• Zurückgegebene Ergebnismenge(n)
– Java Methode deklariert Parameter für zurückgegebene Ergebnismenge
explizit als Array von ResultSet (JDBC) und Array von Iterator (SQLJ)
– Mehrere Ergebnismengen können zurückgegeben werden
CREATE PROCEDURE rang_mit(region INTEGER) DYNAMIC RESULT SETS 1 ...
public void rang_mitarbeiter(int region, ResultSet[] rs);
– Methodenkörper kann JDBC und/oder SQLJ enthalten
Prof. Dr. Wloka
SQLJ Part 1: SQL-Routinen die Java nutzen
•
Beispiel: Implementierung von user-defined routines
–
Java Klasse implementiert das Verhalten der Prozedur
public class MitProzedur{
public static void avgGehalt(String beruf, Integer gehalt) throws SQLException{
#sql {SELECT AVG(Gehalt) INTO :gehalt FROM Mit WHERE Beruf = :beruf};}}
–
Erstellen einer JAR-Datei (Java Archive)
JAVA_HOME="/usr/java/jdk1.5.0_01"
export JAVA_HOME
jar -cf /home/user/classes/Mit.jar MitProzedur.class META-INF
–
Installation der SQLJ JAR-Datei in der Datenbank
CALL SQLJ.INSTALL_JAR(‘file:/home/user/classes/Mit.jar‘, ‘Mit_jar‘);
–
Vollständige DDL zur Generierung der Prozedur
CREATE PROCEDURE avgGehalt(IN beruf VARCHAR(30), OUT gehalt NUMBER(10))
EXTERNAL NAME ‘Mit_jar:MitProzedur.avgGehalt‘
LANGUAGE JAVA
PARAMETER STYLE JAVA
Prof. Dr. Wloka
SQLJ Part 2: SQL Typen die Java nutzen
•
Nutzung von Java Klassen um SQL-Typen zu definieren
– Referenz zur strukturierten / nativen SQL-Typen (BLOBs)
– Für die Definition von Spalten und SQL typed tables (SDT) verwendet
•
Mapping des Objektstatus und -verhaltens
– Java Methode wird zur SQL Methode des SQL Typs
– Java Methoden sind innerhalb von SQL-Statements aufrufbar
•
Automatisches Mapping zum Java Objekt beim Auslesen des Cursors
und beim Methodenaufruf
– Java Serialisierung
– JDBC 2.0 SQLData Interface
•
•
Einschließlich Handhabung des USAGE Privileges des SQL Typs
Enthält die Nutzung von Prozeduren zum Installieren, Entfernen und
Ersetzen von SQLJ JAR Dateien
Prof. Dr. Wloka
5.3.3.4 SQL/JRT (Java Routines and Types)
•
Beispiel: Java Klasse
public class Adresse implements Serializable, SQLData
{
public String Nr;
public String Strasse;
public String Ort;
public String Plz;
public static String Land = “D“;
public String ausgabeAdresse() {...};
public void aendereAdresse(String adr) {... //Segmentierung & Feldänderungen …}
public void readSQL(SQLInput in, String type) {...};
public void writeSQL(SQLOutput out) {...};
}
Prof. Dr. Wloka
5.3.3.4 SQL/JRT (Java Routines and Types)
•
Beispiel: SQL DDL
CREATE TYPE Adresse EXTERNAL NAME ‘adresse_jar:Adresse‘ LANGUAGE JAVA
(
Nr VARCHAR(3) EXTERNAL NAME ‘Nr‘,
Strasse VARCHAR(30) EXTERNAL NAME ‘Strasse‘,
Stadt VARCHAR(30) EXTERNAL NAME ‘Ort‘,
PLZ VARCHAR(30) EXTERNAL NAME ‘Plz‘,
STATIC METHOD Land() RETURNS VARCHAR(30)
EXTERNAL VARIABLE NAME ‘Land‘,
METHOD ausgabeAdresse() RETURNS VARCHAR(100)
EXTERNAL NAME ‘ausgabeAdresse‘,
METHOD aendereAdresse(VARCHAR(100)) RETURNS Adresse
SELF AS RESULT EXTERNAL NAME ‘aendereAdresse‘
)
Prof. Dr. Wloka
Konformität bei SQL/JRT /Melt03b/, /IBM06/, /Micr06/, /Orac05/, /Syba06/, /Syba05/, [3]
Features(17)
JRT Features
18
16
14
12
10
8
6
4
2
0
88.3%
94.1%
15
82.3%
76.4%
13
12
9
23.6%
4
4
Oracle 10g
11.7%
17.7%
2
3
3
DB 2 V9
1
Sybase ASE15
RDBMS
Full Support
5.9%
Partial Support
1
1
MS SQL Server
2005
No Support
Prof. Dr. Wloka
Minimale Konformität
At least one of
MS
SQL
2005
Feature ID
Feature description
Oracle 10g
DB2 V9
Sybase
ASE 15
J621
External Java Routines
9
9
9
8
J541
SERIALIZABLE
9
9
9
;
J551
SQLDATA
;
;
;
8
J511
Commands
9
9
9
8
J531
Deployment
9
9
9
JDBC
At least one of
9 Supported
; Partial Support
8 Not Supported
- Unknown
Prof. Dr. Wloka
Optionale Konformität
Feature ID
Feature description
Oracle 10g
DB2 V9
Sybase
ASE 15
MS SQL
2005
J521
JDBC data types
9
9
Type 4
Type 4
J551
SQLDATA
;
;
;
8
J561
JAR privileges
9
9
9
8
J571
NEW operator
Using EJB
9
9
8
J581
Output parameters
9
9
9
8
J591
Overloading
9
9
9
8
J601
SQL-Java paths
8
8
9
8
J611
References
9
9
9
8
J622
External Java types
9
8
9
8
J631
Java signatures
8
8
8
8
J641
Static fields
9
9
9
8
J651
SQL/JRT Information Schema
8
9
9
8
J652
SQL/JRT Usage tables
8
;
;
8
9 Supported
; Partial Support
8 Not Supported
- Unknown
Prof. Dr. Wloka
Part 14 : SQL/XML - Hauptbestandeile
- Neuer Datentyp XML
- Operationen auf dem Datentyp XML, aber keine
Abfragemöglichkeiten
XQUERY (SQL:2007)
- Abbildungsverfahren SQL
XML
- Abbildung von SQL-Konzepten auf XML
- Zeichensätzen und Namen
- vordefinierten Datentypen und Namen
- benutzerdefinierten Datentypen und Namen
- SQL-Tabelle
- SQL-Schema und Katalog
Prof. Dr. Wloka
XML vs SQL /Schö07/
XML
SQL
Hierarchische Struktur
Flache Struktur
Loses Schema,
Kein Schema notwendig
Festes Schema
case-senstive Namen
case-insensitive Namen
42 Basisdatentypen
Verschiedene Typkonzepte
20 Basisdatentypen
Verschiedene Typkonzepte
Strikte Unicode-Basierung
Verschiedene Codierungen
geordnet
Ungeordnet möglich
XQuery: Suche in Bäumen von
XDM-Knoten
SQL: Suche in Mengen von Tupeln
Prof. Dr. Wloka
SQL/XML Datentyp und Operationen
XMLSERIALIZE
XMLELEMENT
XMLFOREST
XMLCOMMENT
XMLDOCUMENT
XMLAGG
XML PARSE
XMLCONCAT
(XML->XML)
Prof. Dr. Wloka
SQL/XML Operationen /Schö07/
• XMLDOCUMENT erzeugt aus einem Wert vom Typ
XML einen XML- Dokumentknoten
• XMLPARSE wandelt SQL-Werte in einen XML-Wert
• XMLELEMENT erzeugt aus SQL-Werten einen XMLElementknoten vom Typ XML
• XMLFOREST erzeugt aus einer Tabelle eine Folge von
XML-Elementknoten
• XMLCOMMENT erzeugt einen XML-Kommentarknoten
• XMLAGG aggregiert Ergebnisse über mehrere Zeilen
Prof. Dr. Wloka
SQL/XML Operationen /Schö07/
• XMLSERIALIZE wandelt einen XML-Wert in einen
SQL-String um
• XMLCONCAT verkettet Werte vom Typ XML
(XML->XML)
Prof. Dr. Wloka
Konformität bei SQL/XML /IBM06/, /Micr06/, /Orac05/, /Syba06/ , [4]
Features(53)
XML Features
54
48
42
36
30
24
18
12
6
0
71.7%
64.2%
71.7%
50.9%
49.1%
38
36
26
25
34
35.8%
28.3%
28.3%
15
2
2
Oracle 10g
DB2 V9
9
Sybase ASE15
RDBMS
Full Support
9
6
Partial Support
10
MS SQL Server
2005
No Support
Prof. Dr. Wloka
Minimale Konformität
DB2
V9
Sybas
e ASE
15
MS SQL
2005
Feature ID
Feature description
Oracle
10g
X010
XML type
XMLType
9
8
XML
X031
XMLElement
9
9
Extract
Query
X032
XMLForest
9
9
Extract
Value
X034
XMLAgg
9
9
8
Value
X070
XMLSerialize: CONTENT option
9
9
8
Query
X071
XMLSerialize: DOCUMENT option
9
8
8
Query
X100
Host language support for XML:
CONTENT option
9
9
8
8
X101
DOCUMENT option
9
9
8
8
9 Supported
; Partial Support
8 Not Supported
- Unknown
Prof. Dr. Wloka
DB2
V9
Sybase
ASE 15
MS
SQL
2005
Feature ID
Feature description
Oracle
10g
X011
Arrays of XML type
varray
8
8
8
X012
Multisets of XML type
9
8
8
8
X013
Distinct types of XML
8
;
8
8
X014
Attributes of XML type
9
9
8
8
X015
Fields of XML type
8
9
8
8
X016
Persistent XML values
9
9
Text
XML
X020
XML Concatenation
9
9
8
8
X033
XMLRoot
9
8
8
8
X035
XMLAgg: ORDER BY option
9
9
8
Value
X041
Basic table mapping: null absent
9
9
9
8
X042
Basic table mapping: null as nil
9
9
9
8
X043
Basic table mapping: table as forest
9
9
9
Value
9 Supported
; Partial Support
8 Not Supported
- Unknown
Prof. Dr. Wloka
DB2
V9
Sybase
ASE 15
MS
SQL
2005
Feature ID
Feature description
Oracle
10g
X044
Basic table mapping: table as element
9
9
9
Query
X045
Basic table mapping: with target namespace
9
9
9
9
X046
Basic table mapping: data mapping
9
9
9
8
X047
Basic table mapping: metadata mapping
9
9
-
8
X048
Basic table mapping:
base64 encoding of binary strings
8
9
9
8
X049
Basic table mapping:
hex encoding of binary strings
8
-
8
8
X051
Advanced table mapping: null absent
8
9
8
8
X052
Advanced table mapping: null as nil
8
9
8
8
X053
Advanced table mapping: table as forest
8
9
8
8
X054
Advanced table mapping: table as element
8
9
8
8
X055
Advanced table mapping: with target namespace
8
9
8
8
9 Supported
; Partial Support
8 Not Supported
- Unknown
Prof. Dr. Wloka
DB2 V9
Sybase
ASE 15
MS
SQL
2005
Feature ID
Feature description
Oracle
10g
X056
Advanced table mapping: data mapping
8
-
-
-
X057
Advanced table mapping: metadata mapping
8
-
-
-
X058
Advanced table mapping:
base64 encoding of binary strings
8
8
-
-
X059
Advanced table mapping:
hex encoding of binary strings
8
DB2 XML
8
8
X060
XMLParse: CONTENT option
9
8
;
8
X061
XMLParse: DOCUMENT option
9
9
;
8
X062
XMLParse: explicit WHITESPACE option
8
9
;
9
X080
Namespaces in XML publishing
8
9
8
9
X081
Query-level XML namespace declarations
8
9
8
9
X082
XML namespace declarations in DML
8
9
8
9
X083
XML namespace declarations in DDL
8
9
8
9
9 Supported
; Partial Support
8 Not Supported
- Unknown
Prof. Dr. Wloka
DB2
V9
Sybase
ASE 15
MS
SQL
2005
Feature ID
Feature description
Oracle
10g
X084
XML namespace declarations
in compound statements
8
8
8
9
X090
XML document predicate
Fragment
9
8
8
X110
VARCHAR mapping
8
9
8
8
X111
Host language support for XML: CLOB mapping
8
9
8
8
X120
XML parameters in SQL routines
9
9
9
9
X121
XML parameters in external routines
9
9
9
9
X131
Query-level XMLBINARY clause
-
-
-
-
X132
XMLBINARY clause in DML
-
-
-
-
X133
XMLBINARY clause in DDL
-
-
-
-
X134
XMLBINARY clause in compound statements
-
-
-
-
X135
XMLBINARY clause in subqueries
-
-
-
-
9 Supported
; Partial Support
8 Not Supported
- Unknown
Prof. Dr. Wloka
Stufen der praktischen Umsetzung von SQL/XML
• (Shreddern von XML-Dokumenten in Tabellenspalten)
• (Abspeicherung von XML-Dokumenten in LOB-Spalten)
• XML-Datentyp und XML-Operationen als erweiterte DBServer-Funktionalität
(z. B. Oracle 10g, Sybase ASE 15, MS SQL 2005)
• Hybridserver(z. B. DB2 V9)
• Native XML-Server(z. B. Tamino, Natrix, Xindice,
Infonyte)
Prof. Dr. Wloka
DB2 Version 9 - der Hybridserver
/Seub06/
Prof. Dr. Wloka
/Seub06/
Prof. Dr. Wloka
/Seub06/
Prof. Dr. Wloka
Gliederung
1 Die Anfänge: SQL:1986
2 Die Entwicklung
2.1 SQL:1992
2.2 SQL:1999
4
4.2 neue Feature bei Part2, Part11, Part 14
Prof. Dr. Wloka
Zeitliche Entwicklung des SQL-Standard
Parts
SQL 1992
Part 1 - SQL/
Framework
Part 2 - SQL/
Foundation
Completed
(includes bindings
and schema
information tables)
SQL 1999
SQL 2003
SQL 2007
Completed
Completed
Completed
Completed
Completed
(SQL/Bindings merged in
schema information tables
separated out)
Completed
(Minor
changes in
syntax)
Completed
Completed
Completed
(Revised server
module definition
& syntax change)
Processed as an amendment
to SQL/Foundation
SQL/OLAP
Part 3 –
SQL/CLI
Completed in '95
as an expansion
Completed
Part 4 –
SQL/PSM
Completed in '96
as an expansion
Completed (stored routines
and call statement moved to
SQL/Foundation)
Completed
Part 5 - SQL/
Bindings
Specification for embedding
SQL in programming
languages moved to a
separate part.
SQL/Bindings merged back into
SQL/Foundation
Part 6 - SQL/
Transaction
project canceled
Part 7 - SQL/
Temporal
withdrawn
Prof. Dr. Wloka
Zeitliche Entwicklung des SQL-Standard
Parts
SQL 1992
Part 9 SQL/MED
Part 10 SQL/OLB
completed in '98
as an ANSI only
standard
SQL 1999
SQL 2003
SQL 2007
ISO version based on
SQL'99 in process
Revision completed
Revision Completed
( with minor & Major
Technical Problems)
ISO version based on
SQL'99 completed
Revision completed
Revision Completed
Schema information tables
extracted from SQL'99
completed
Part 11 - SQL/
Schemata
Became a project in 2000,
with goal of defining syntax
and semantics to support
definition of replication
schemes and rules, including
rules for resolution of update
conflicts.
Part 12 - SQL/
Replication
Part 13 SQL/JRT
Part 14 SQL/XML
completed in '99
as an ANSI only
standard
Completed
Dropped due to lack of
progress.
Revision completed
Completed
Completed
Expansion completed
in 2006. Revision
coordinated with
other parts for 2007.
Prof. Dr. Wloka
Entwicklung des Umfanges
Part
SQL 1992 Pages
Part 1 - SQL/
Framework
SQL 1999 Pages
SQL 2003 Pages
SQL 2007 Pages
85
81
84
Part 2 - SQL
Foundation
628
1,147
1,267
1,328
Part 3 - SQL/CLI
236
421
405
390
Part 4 - SQL/PSM
256
170
184
190
Part 9 - SQL/MED
498
486
Part 10 - SQL/OLB
405
396
Part 11 - SQL/
Schemata
296
294
Part 13 - SQL/JRT
204
200
Part 14 - SQL/XML
266
428
3,606
3,796
Part 5 - SQL/
Bindings
Total
261
1,120
2,084
Prof. Dr. Wloka
SQL:200n
•
•
•
•
Struktur und wesentliche „alte“ Inhalte bleiben gleich
Umfangreiche Entwicklungen bei SQL/XML
Änderungen bei SQL/Foundation
Geringe Änerungen bei SQL/Schema
Prof. Dr. Wloka
Überblick SQL:200n
1: Framework
3: CLI
4: PSM
9: MED
10: OLB
13: JRT
(4) PSM
11: Schemata
2: Foundation
optionale
Features
(1) Enhanced
Date/Time Fac.
verbindliche
Features
n: xxx
(n) xxx
Core SQL
... ein Part
... ein Package
(2) Enhanced Integrity
Management
(7) Enhanced
Objects
(8) Active
Databases
(6) Basic
Objects
(10) OLAP
14: XML
Prof. Dr. Wloka
SQL 200n :Standard
SQL : 200n Standard
Total Features : 674
0%
Framework (Packages)
20%
Foundation (440)
3%
CLI (8)
2%
PSM (30)
1%
MED (25)
4%
OLB (9)
4%
1%
65%
Schemata (11)
JRT (17)
XML (134)
Prof. Dr. Wloka
Neue Features in SQL:200n Standard
• Änderungen in
– Part 2 SQL/Foundation
– Part 11 SQL/Schemata
– Part 14 SQL/XML
• Keine Änderungen
–
–
–
–
–
–
Part 1 SQL/Framework
Part 3 SQL/CLI
Part 4 SQL/PSM
Part 9 SQL/MED
Part 10 SQL/OLB
Part 13 SQL/JRT
Prof. Dr. Wloka
Neue Feature von SQL:200n
•
•
•
•
•
•
•
•
BINARY data type
Regular expression support
FIRST n (LIMIT TO n, TOP n, …)?
IEEE 754 “floating decimal” data type ?
Materialized views ?
Ermöglichung von Bulk inserts ?
Unterstützung der “history” (stripped-down temporal) ?
Unterstützung von streaming data ? /Melt05/
Prof. Dr. Wloka
Part 2 SQL/Foundation – neue Features(22) /Melt06a/
No
Feature Id
1
B035
Non-extended descriptor names
2
F122
Enhanced diagnostics management
3
F123
All diagnostics
4
F394
Optional normal form specification
5
F403
Partitioned join tables
6
F762
CURRENT_CATALOG
7
F690
Collation Support
8
F763
CURRENT_SCHEMA
9
F841
LIKE_REGEX predicate
10
F842
OCCURENCES_REGEX function
11
F843
POSITION_REGEX function
Feature Name
Prof. Dr. Wloka
Part 2 SQL/Foundation – neue Features(22)
No
Feature Id
Feature Name
12
F844
SUBSTRING_REGEX function
13
F845
TRANSLATE_REGEX function
14
F846
Octet support in regular expression operators
15
F847
Nonconstant regular expressions
16
T021
BINARY and VARBINARY data types
17
T022
Advanced BINARY and VARBINARY data types
18
T024
Compound binary literals
19
T024
Spaces in binary literals
20
T043
Multiplier T
21
T044
Multiplier P
22
T101
Enhanced nullability determination
Prof. Dr. Wloka
Part 2 SQL/Foundation – entfernte Features
No
Feature Id
1
F691
Collation and translation
2
F696
Additional translation documentation
Feature Name
Prof. Dr. Wloka
Part 11 SQL/Schemata
• Dropped - Mandatory features
– F501-03 Sql_languages View
• Added Features
– 12 Features are added from SQL/Foundation
– Features like
•
•
•
•
Basic Reference type
Basic array support
Basic multiset support
Collation support
Prof. Dr. Wloka
SQL/Schemata Conformance – New Features(12)
/Melt06b/
Features (12)
SQL: 200n - SQL/Schemata
10
8
6
4
2
0
66.7%
75%
8
66.7%
33.3%
0
4
Oracle 10g
5
3
7
33.3%
4
DB2 V9
2
66.7%
25%
3
Sybase ASE15
6
2
Partial Support
4
MS SQL Server
2005
DBMS
Full Support
33.3%
No Support
Prof. Dr. Wloka
SQL:200n – SQL/Schemata (New Features)
Feature ID
Feature Description
Oracle
10g
DB2
V9
Sybase
ASE15
MS SQL
Server
2005
F251
Domain support
-
;
;
-
F521
Assertions
-
8
;
;
F651
Catalog name qualifiers
-
9
9
9
F690
Collation support
;
9
9
9
F695
Translation support
;
-
;
;
F696
Additional translation
documentation
-
-
-
-
S041
Basic reference types
;
;
;
;
S081
Subtables
;
-
-
-
S091
Basic array support
;
;
-
;
S241
Transform functions
;
9
;
;
S271
Basic multiset support
;
;
;
8
T332
Basic data type attributes
;
;
;
;
Legend
9 Supported
; Partial Support
8 Not Supported
- Unspecified
Prof. Dr. Wloka
Part 14 SQL/XML
• ISO/IEC 9074-14:2003
– Mappings and Publishing Functions
• ISO/IEC 9075-14:2005 (“almost completed”)
• – Adds XQuery, including Data Model, Validation
• ISO/IEC 9075-14:2007 (planned)
• – Full-Text?
• – Update XML?
• – weiteres? /Melt05/
Prof. Dr. Wloka
Part 14 SQL/XML
• Mandatory Features
– Modified from SQL/XML optional features
• Optional Features
– 83 New features are added
– Features like XMLPI, XMLComment are updated
– 2 features are dropped
• X033 - XMLRoot
• X062 - XMLParse: explicit WHITESPACE option
Prof. Dr. Wloka
Part 14 SQL/XML SQL/XML:2003 plus
• XQuery Datenmodel zugrunde gelegt
SQL-Basisdatentyp „XML“ basiert jetzt auf dem XQuery-Datenmodell,
in Tabellen sind als XML-Werte beliebige XQuery-Sequenzen zulässig
(vorher XML-Info-Set-basiertes Datenmodell )
• Modifier zum XML-Typ
DOCUMENT
CONTENT
SEQUENCE
UNTYPED
ANY
XMLSCHEMA
Prof. Dr. Wloka
• Prädikate zum XML-Typ
IS [NOT] CONTENT evaluiert, ob ein XML-Wert [k]ein
Dokumentknoten ist
IS [NOT] DOCUMENT evaluiert, ob ein XML-Wert [k]ein
wohlgeformtes Dokument ist
XMLEXISTS prüft, ob ein XQuery-Ausdruck ein nicht
leeres Ergebnis liefert
IS [NOT] VALID prüft, ob ein XML-Wert [un]gültig in
bezug auf ein gegebenes Schema ist
Prof. Dr. Wloka
• Integration von XQuery in SQL
XQuery-Anfragen können in SQL-Anfragen eingebettet
werden(mittels SQL-Operation XMLQUERY)
XQUERY wertet einen XQuerry-Ausdruck aus
• XMLTABLE wandelt ein XQuery-Ergebnis in eine SQLTabelle um
• XMLVALIDATE validiert einen XML-Wert gegen ein
Schema
• XMLPI erzeugt eine Verarbeitungsanweisung
• XMLTEXT erzeugt einen Textknoten
• Casting zwischen XML-Typen und SQL-Typen
Prof. Dr. Wloka
SQL/XML Conformance – New Features(83) /Melt06c/
New Features(83)
SQL: 200n - SQL/XML
50
40
49.4%
50.6%
30
10
41
34
28
20
73.5%
27
57.8%
39
43
42.2%
37
35
22
9
0
DB2 V9
3
Sybase ASE15 MS SQL Server
2005
DBMS
Full Support
51.8%
26.5%
14
Oracle 10g
48.2%
Partial Support
No Support
Prof. Dr. Wloka
SQL:200n – SQL/XML (New Features)
Feature
ID
Feature Name
Oracle
10g
DB2 V9
Sybase
ASE15
MS SQL
Server
2005
X025
XMLCast
;
;
;
;
X030
XMLDocument
8
8
8
8
X036
XMLComment
9
;
8
8
X037
XMLPI
9
9
9
8
X038
XMLText
;
;
;
;
X040
Basic table mapping
;
9
;
9
X050
Advanced table mapping
8
;
;
;
X065
XMLParse: BLOB input and CONTENT option
8
;
8
8
X066
XMLParse: BLOB input and DOCUMENT option
8
;
8
8
X072
XMLSerialize: Character string serialization
9
9
9
9
X073
XMLSerialize: BLOB serialization and CONTENT option
8
;
8
8
X074
XMLSerialize: BLOB serialization and DOCUMENT option
8
;
8
8
X075
XMLSerialize: BLOB serialization
8
;
8
8
X076
XMLSerialize: VERSION
8
-
8
8
X077
XMLSerialize: explicit ENCODING option
8
-
8
8
Prof. Dr. Wloka
Feature
ID
Feature Name
Oracle
10g
DB2
V9
Sybase
ASE15
MS SQL
Server 2005
X078
XMLSerialize: explicit XML declaration
8
-
8
8
X085
Predefined namespace prefixes
8
;
;
;
X086
XML namespaces declaration in XMLTable
9
-
;
;
X091
XML content predicate
8
;
;
8
X096
XMLExists
;
;
;
8
X112
Host language support for XML: BLOB mapping
8
9
8
8
X113
Host language support for XML: STRIP
WHITESPACE option
8
9
8
8
X114
Host language support for XML: PRESERVE
WHITESPACE option
8
9
8
8
X141
IS VALID predicate: data-driven case
;
;
;
;
X142
IS VALID predicate: ACCORDING TO clause
;
;
;
;
X143
IS VALID predicate: ELEMENT clause
;
;
;
;
X144
IS VALID predicate: schema location
;
;
;
;
X145
IS VALID predicate outside check constraints
;
;
;
;
X151
IS VALID predicate with DOCUMENT option
;
;
;
;
Prof. Dr. Wloka
Feature
ID
Feature Name
Oracle
10g
DB2
V9
Sybase
ASE15
MS SQL
Server
2005
X152
IS VALID predicate with CONTENT option
8
;
;
;
X153
IS VALID predicate with SEQUENCE option
8
-
;
;
X155
IS VALID predicate: NAMESPACE without ELEMENT
clause
8
-
;
8
X157
IS VALID predicate: NO NAMESPACE with ELEMENT
clause
;
-
;
;
X160
Basic Information Schema for registered XML Schemas
;
9
9
;
X161
Advanced Information Schema for registered XML
Schemas
;
;
-
;
X170
XML null handling options
8
;
-
8
X171
NIL ON NO CONTENT option
8
-
-
8
X181
XML(DOCUMENT(UNTYPED)) type
8
-
-
;
X182
XML(DOCUMENT(ANY)) type
8
-
-
;
X190
XML(SEQUENCE) type
8
-
-
;
X191
XML(DOCUMENT(XMLSCHEMA)) type
;
-
-
-
X192
XML(CONTENT(XMLSCHEMA)) type
8
-
-
-
Prof. Dr. Wloka
Feature
ID
Feature Name
Oracle
10g
DB2
V9
Sybase
ASE15
MS SQL
Server
2005
X200
XMLQuery
;
9
9
;
X201
XMLQuery: RETURNING CONTENT
9
9
9
;
X202
XMLQuery: RETURNING SEQUENCE
8
9
9
-
X203
XMLQuery: passing a context item
9
9
8
-
X204
XMLQuery: initializing an XQuery variable
9
9
;
;
X205
XMLQuery: EMPTY ON EMPTY option
-
-
-
-
X206
XMLQuery: NULL ON EMPTY option
-
-
-
-
X211
XML 1.1 support
8
-
-
-
X221
XML passing mechanism BY VALUE
;
;
;
;
X222
XML passing mechanism BY REF
8
;
;
;
X231
XML(CONTENT(UNTYPED)) type
8
-
-
8
X232
XML(CONTENT(ANY)) type
;
-
-
8
X241
RETURNING CONTENT in XML publishing
;
;
-
8
Legend
9 Supported
; Partial Support
8 Not Supported
- Unspecified
Prof. Dr. Wloka
Feature
ID
Feature Name
Oracle
10g
DB2
V9
Sybase
ASE15
MS SQL
Server
2005
X242
RETURNING SEQUENCE in XML publishing
8
9
-
8
X251
Persistent XML values of XML(DOCUMENT(UNTYPED))
type
9
9
9
-
X252
Persistent XML values of XML(DOCUMENT(ANY)) type
9
9
9
-
X253
Persistent XML values of XML(CONTENT(UNTYPED)) type
8
9
;
-
X254
Persistent XML values of XML(CONTENT(ANY)) type
8
9
;
-
X255
Persistent XML values of XML(SEQUENCE) type
8
9
-
-
X256
Persistent XML values of
XML(DOCUMENT(XMLSCHEMA)) type
9
9
9
;
X257
Persistent XML values of XML(CONTENT(XMLSCHEMA))
type
8
9
;
8
X260
XML type: ELEMENT clause
;
;
;
;
X261
XML type: NAMESPACE without ELEMENT clause
8
;
-
8
X263
XML type: NO NAMESPACE with ELEMENT clause
;
;
-
8
X264
XML type: schema location
;
;
-
9
X271
XMLValidate: data-driven case
;
;
;
;
Prof. Dr. Wloka
Feature
ID
Feature Name
Oracle
10g
DB2
V9
Sybase
ASE15
MS SQL
Server
2005
X272
XMLValidate: ACCORDING TO clause
;
;
;
;
X273
XMLValidate: ELEMENT clause
;
;
;
;
X274
XMLValidate: schema location
;
;
;
;
X281
XMLValidate: with DOCUMENT option
;
;
;
-
X282
XMLValidate with CONTENT option
8
;
;
-
X283
XMLValidate with SEQUENCE option
8
-
;
-
X284
XMLValidate NAMESPACE without ELEMENT clause
8
8
8
-
X286
XMLValidate: NO NAMESPACE with ELEMENT clause
;
-
-
-
X300
XMLTable
;
9
;
;
Legend
9 Supported
; Partial Support
8 Not Supported
- Unspecified
Prof. Dr. Wloka
Feature
ID
Feature Name
Oracle
10g
DB2
V9
Sybase
ASE15
MS SQL
Server
2005
X301
XMLTable: derived column list option
8
9
;
;
X302
XMLTable: ordinality column option
9
9
;
;
X303
XMLTable: column default option
9
9
;
;
X304
XMLTable: passing a context item
9
9
;
;
X305
XMLTable: initializing an XQuery variable
9
9
;
;
X400
Name and identifier mapping
8
[X290]
9
;
;
Legend
9 Supported
; Partial Support
8 Not Supported
- Unspecified
Prof. Dr. Wloka
Vergleich– SQL :2003 vs SQL: 2007 Standard
SQL Standard
Parts
New
Features
Mandatory Features
Optional Features
Added
Dropped
Added
Dropped
(2007)
SQL:2003
SQL:2007
Part 1 –
SQL/Framework
-
-
-
-
-
7 Packages
7 Packages
Part 2 –
SQL/Foundation
-
-
22
2
22
420
440
Part 3 –
SQL/CLI
-
-
-
-
-
8
8
Part 4 –
SQL/PSM
-
-
-
-
-
30
30
Part 9 –
SQL/MED
-
-
-
-
-
25
25
Part 10 –
SQL/OLB
-
-
-
-
-
9
9
Part 11 –
SQL/Schemata
-
1 – from
Foundation
12 – from
Foundation
-
12 – from
Foundation
28
39
Part 13 –
SQL/JRT
-
-
-
-
-
17
17
83
2
83
53
134
105
4
117
573
674
Part 14–
SQL/XML
Total
Features modified
-
-
Total Features
Prof. Dr. Wloka
Quellen
/Dess01/
/Dess05/
/Gray04/
/IBM06/
/Melt03/
/Melt03a/
/Melt03b/
/Melt03c/
/Melt03d/
/Melt04/
/Melt05/
/Melt06a/
/Melt06b/
Deßloch, S. SQL-Norm und Java Daten. Datenbank-Spektrum 1,25-32 (2000)
SQL-Standardisierung und Umsetzung
Deutsche Informatik-Akademie Datenbank Tutorientage (DIA DBTT), Karlsruhe 2005
Proceedings ADBIS`04, Eighth East-European Conference an Advances in Databases and
Information Systems, 22-25.9, 2004, Budapest, Hungary S. 3
IBM, SQL Reference Volume1, IBM corporation, Dec 5 2006
Melton, J.: ISO ANSI WORKING DRAFT, 2003, ISO + ANSI
Melton, J.: Information Technology-Database Languages, SQL/Framework,
ISO/IEC 9075-01:2003, 2003
Jim Melton, Information Technology-Database Languages;, SQL/Foundation,
ISO/IEC 9075-2:2003, 2003
Melton, J.: Information Technology-Database Languages; SQL/JRT,
ISO/IEC 9075-13:2003, 2003
Melton, J.: Information Technology-Database Languages; SQL/XML,
ISO/IEC 9075-2:2003, 2003
Melton, J.; Eisenberg, A.; Kulkarni, K.; Michels, J.-E.; Zemke, F.:
“SQL:2003 Has Been Published”, SIGMOD Record, Vol. 33, No. 1, March 2004
Melton, J.: SQL:2007, XQuery, SQL/XML and the Semantic Web Oracle Cooperation 2005
Jim Melton: SQL/Foundation, Information technology – Database languages,
ISO/IEC 9075-2:200x (E), 2006.
Jim Melton: SQL/Schemata, Information technology – Database languages,
ISO/IEC 9075-11:200x (E), 2006.
Prof. Dr. Wloka
Quellen
/Melt06c/
/Micr06/
Jim Melton: SQL/XML, Information technology – Database languages,
ISO/IEC 9075-14:200x (E), 2006.
Microsoft Corporation, SQL Server 2005, www.microsoft.com, July 2006
/Müll06/
Sequenzcursor-basierte Verarbeitung von XML-Werten in SQL:2007-Anfrageergebnissen
Lehrstuhl für Datenbanken und Informationssysteme Friedrich-Schiller-Universität Jena
/Orac05/
Oracle corporation, Oracle Database SQL Reference, Oracle Database, December 2005
/Türk03a/ Türker, C.: SQL: 1999 & SQL: 2003, dpunkt.verlag 2003
/Türk03b/ Türker, C.:“XML und Datenbanken, Universität Zürich, 2003
http://www.dbs.ethz.ch/~xml
/Seub06/ Seubert, H.: DB2 Version 9 – the Viper Release
Vortrag 122. Datenbankstammtisch an der HTW Dresden
/Schö07/ Schöning, H.:SQL/XML-die Brücke zwischen zwei Welten
Deutsche Informatik-Akademie Datenbank Tutorientage (DIA DBTT):
Aachen 2007
/Schü05/
Schüller,G.:“SQL2003 – Neue Konzepte, Universität Bonn, 2005
/SQL-06/
/Syba06/
/Syba05/
http://www.sql-und-xml.de/sql-tutorial/sql-sprache-historische-entwicklung.html
Sybase, Adaptive Server Enterprise Reference Manual, March 2006
Sybase, Java in Adaptive Server Enterprise, Sybase Inc, July 2005
Prof. Dr. Wloka
Referenzen
[1] Implementation for SQL/Foundation
[2] Implementation for SQL/Schemata
[3] Implementation for SQL/JRT
[4] Implementation for SQL/XML
Prof. Dr. Wloka
Danksagungen
• Herrn Böhm und Mr. Kaliappan für die Materialsammlung und
–aufbereitung für den Vortrag, Frau Dilz für die Einladungen
• Frau Hamel, Frau Grosch und Frau Walther für die
organisatorische Unterstützung bei 125 Veranstaltungen
• Sybase, Microsoft, IBM, Intersystems, SQL GmbH, Robotron
Datenbanksoftware für die finanzielle Unterstützung
• Meiner Frau Erika für das langjährige Verständnis für den
Datenbankstammtisch
Prof. Dr. Wloka
Vielen Dank für Ihre Aufmerksamkeit
Prof. Dr. Wloka
Appendix
Prof. Dr. Wloka
Sample Database – Tables (Oracle 10g)
Table: Employee
Table: Taskemp
Prof. Dr. Wloka
Sample Database – Table (Oracle 10g)
Table: Task
Table: Car
Table: Department
Prof. Dr. Wloka
Sample Database – Tables (DB2 9)
Table: Employee
Table: Taskemp
Table: Task
Prof. Dr. Wloka
Sample Database – Tables (DB2 9)
Table: Car
Table: Department
Prof. Dr. Wloka
Sample Databases – Tables (MS SQL Server 2005)
Table: Employee
Table: Taskemp
Table: Task
Prof. Dr. Wloka
Sample Databases – Tables (MS SQL Server 2005)
Table: Car
Table: Department
Prof. Dr. Wloka
Sample Databases – Tables (Sybase ASE15)
Table: Employee
Table: Taskemp
Prof. Dr. Wloka
Sample Databases – Tables (Sybase ASE15)
Table: Task
Table: Car
Table: Department
Prof. Dr. Wloka
Implementation Comparison among RDBMS
Oracle 10g
DB2
MS SQL Server
Sybase ASE
Prof. Dr. Wloka
Predefined Datatypes - Feature ID E011, E021
•
Oracle 10g
create table Employee(Empno number not null primary key ,Surname char(15) not null,
Firstname char(10) not null, City char(10) not null, Age number not null ,Job char(20) not null,
Phone number not null, Departno char(5));
•
DB2
create table dbo.employee ( empno integer not null primary key, surname char (15) not null ,
firstname char (10) not null , city char (10) not null , age integer not null , job char (20) not null
, phone integer not null , departno char (5)) ;
•
MS SQL 2005
Create table Employee(Empno int not null primary key, Surname char(15) not null ,Firstname
char(10) not null,City char(10) not null,Age int not null, Job char(20) not null,Phone int not
null,Departno char(5));
•
Sybase ASE15
Create table Employee(Empno int not null primary key,Surname char(15) not null ,Firstname
char(10) not null,City char(10) not null,Age int not null, Job char(20) not null,Phone int not
null,Departno char(5));
Prof. Dr. Wloka
User Defined Data Type – Distinct Types
•
Oracle 10g
–
–
–
–
•
DB2 V9
–
–
–
–
–
•
create or replace type pc as object (name char(11));
create table pclist (pc_id number(10),pc_nam pc);
insert into pclist values(1,PC('HCL'));
insert into pclist values(2,PC('SIECOM'));
create distinct type pc as character(11) with comparisons
create table pclist(pc_id numeric,pc_nam pc)
insert into pclist values(1,PC('HCL'))
insert into pclist values(2,PC('SIECOM'))
select * from pclist
MS SQL Server 2005
– Not available in MS SQL Server 2005. Only sp_addtype is specified
•
Sybase ASE15
–
Not available in Sybase ASE15. Only sp_addtype is specified
Prof. Dr. Wloka
User Defined Data Type – Structured Type
•
Oracle 10g
create type car_detail as object
( carno varchar2(15), cartype varchar2(20), surname varchar2(15),
firstname varchar2(15), colour varchar2(10) )
•
DB 2 V9
create type car_detail as (carno varchar(15), cartype varchar(20),
surname varchar(15), firstname varchar(15), colour varchar(10)) mode db2sql
Prof. Dr. Wloka
Constructed Type – Reference Type
•
Oracle 10g
–
•
DB2 V9
–
•
create table employee_ref( empid integer, name varchar(30), car ref(car_detail))
MS SQL Server2005
–
•
create table employee_ref( empid number(5),name varchar(30),car ref car_detail)
Not available in MS SQL Server 2005.
Sybase ASE15
–
Not available in Sybase ASE15
Prof. Dr. Wloka
Constructed Type – Composite Type
•
Oracle 10g – Array Type
–
–
–
create type Car_Brand as varray(5) of varchar2(25)
create table Car_Type(Surname varchar2(25), Car_Brand_Name Car_Brand)
desc Car_type
–
–
–
insert into car_type values('Hase',Car_Brand('BMW','Ford','Mitsu'))
insert into car_type values('Grafie',Car_Brand('Ford','Trabant','Opel','Skoda'))
select C.surname,B.* from Car_type C, TABLE(C.Car_Brand_Name)
Prof. Dr. Wloka
Constructed Type – Composite Type
•
Oracle 10g – Multiset Type
Nested table-example
–
–
–
–
–
–
–
create table Car_Details(Carno
integer,Cartype varchar2(25),Colour
varchar2(15))
create type CarInfo as Table of varchar2(64)
create table Per_Details(empno
integer,surname varchar2(15),Dept
varchar2(10), Veh carinfo) nested table veh
store as car_tab
desc Per_details
insert into Per_Details
values(101,'Hase','A3',carinfo('FE-E
123','BMW', 'Schwarz'))
insert into Per_Details
values(102,'Igel','A1',carinfo('DD-DD
123','Ford','Blau'))
SELECT P.empno,P.surname,P.dept,C.*
FROM per_details P, Table(P.veh) C;
Prof. Dr. Wloka
Basic DML Functions (Feature ID E101, E111, E131, E153)
•
Oracle 10g
–
–
–
–
•
insert into Employee values(105,'Uhu','Uhu','Ast',50,'Dipl.-Ing',3419,'A3');
delete from Employee where empno=105;
update task set taskno=(select taskno from taskemp where empno=101) where
task.leader='Hase‘
select taskno, taskname, leader from task
DB2
–
–
–
insert into dbo.Employee values(105,'Uhu','Uhu','Ast',50,'Dipl.-Ing',3419,'A3');
delete from dbo.Employee where empno=105;
update dbo.task task set empno=(select empno from dbo.employee where surname='Hase') where
leader='Hase
Prof. Dr. Wloka
Basic DML Functions (Feature ID E101, E111, E131, E153)
•
•
MS SQL 2005
–
–
–
insert into Employee values(105,'Uhu','Uhu','Ast',50,'Dipl.-Ing',3419,'A3');
delete from Employee where empno=105;
update task set taskno=(select taskno from taskemp where empno=101) where task.leader='Hase'
–
select * from task
Sybase ASE15
–
–
–
insert into dbo.Employee values(105,'Uhu','Uhu','Ast',50,'Dipl.-Ing',3419,'A3');
delete from dbo.Employee where empno=105;
update dbo.task task set taskno=(select taskno from dbo.taskemp where empno='Hase') where
leader='Hase'
Prof. Dr. Wloka
Query Specifications (Feature ID E051, E071, E091)
•
Oracle 10g
–
–
•
DB2 V9
–
–
•
SELECT Empno, Taskno, Max(targeteffort)FROM dbo.Taskemp GROUP BY Empno,
Taskno;
select distinct task.empno, taskemp.taskno from dbo.task as task, dbo.taskemp as
taskemp;
MS SQL Server 2005
–
–
•
select empno, taskno, max(targeteffort)from taskemp group by empno, taskno;
select distinct taskno from taskemp intersect select distinct taskno from task;
select empno, taskno, max(targeteffort) effort from taskemp group by empno, taskno;
select distinct taskno from taskemp intersect select distinct taskno from task;
Sybase ASE15
–
–
select empno, taskno, max(targeteffort) effort from taskemp group by empno, taskno;
select distinct Task.taskno, Taskemp.Empno from dbo.task as Task, dbo.Taskemp as
Taskemp
Prof. Dr. Wloka
Basic Predicates and Privileges (Feature ID E061, T631, F481, E081)
•
Oracle 10g
–
–
–
–
–
•
select * from Employee where Surname Like '%s%'
select * from Employee where Departno is NULL
select taskno,empno from taskemp where empno in(select empno from employee where
departno = 'a3')
grant insert, delete on task to scott
revoke insert,delete on Task from scott
DB2 V9
–
–
–
–
–
select * from dbo.employee where Surname like '%s%'
select * from dbo.Employee where Departno is NULL
select taskno,empno from dbo.taskemp where empno in(select empno from dbo.employee
where departno = 'a3')
grant select,insert on table dbo.employee to user shangar
revoke select,insert on table dbo.employee from user shangar
Prof. Dr. Wloka
Basic Predicates and Privileges (Feature ID E061, T631, F481, E081)
•
MS SQL Server 2005
–
–
–
–
–
•
select * from dbo.employee where Surname like '%s%'
select taskno,empno from dbo.taskemp where empno in(select empno from dbo.employee
where departno = 'a3')
revoke select,insert on employee to guest
GRANT select,insert on employee to guest
Sybase ASE15
–
–
–
–
–
select * from dbo.Employee where Surname like '%s%'
select Taskno,Empno from dbo.Taskemp where Empno IN(SELECT Empno FROM
dbo.Employee WHERE Departno = 'A3')
revoke select,insert on employee to guest
GRANT select,insert on employee to guest
Prof. Dr. Wloka
Integrity Constraints (Feature ID E141)
•
Oracle 10g
– alter table employee add constraint uq_empno unique (empno);
– alter table employee add constraint age_ch check (age between 18 and 70)
•
DB 2 V9
–
–
•
MS SQL Server 2005
–
–
•
alter table dbo.employee add constraint uq_empno unique (empno);
alter table dbo.employee add constraint age_ch check (age between 18 and 70)
Sybase ASE15
–
–
Prof. Dr. Wloka
Transaction Support (Feature ID E151, E152)
•
•
Oracle 10g
–
–
–
commit;
insert into Employee values(105,'Uhu','Uhu','Ast',50,'Dipl.-Ing.',3419,'A3')
rollback;
–
set transaction read only name ‘db1’
DB2 V9
–
–
–
–
•
commit transaction
insert into dbo.Employee values(105,'Uhu','Uhu','Ast',50,'Dipl.-Ing.',3419,'A3')
rollback transaction
set transaction isolation level serializable
MS SQL 2005
–
–
–
Begin tran t1
insert into dbo.Employee values(105,'Uhu','Uhu','Ast',50,'Dipl.-Ing.',3419,'A3')
commit tran t1
Prof. Dr. Wloka
Transaction Support (Feature ID E151, E152)
•
Sybase ASE15
– First Possibility
• Commit
• insert into dbo.Employee values(105,'Uhu','Uhu','Ast',50,'Dipl.-Ing.',3419,'A3')
• Rollback
– Second Possibility
• Begin tran t1
• insert into dbo.Employee values(105,'Uhu','Uhu','Ast',50,'Dipl.-Ing.',3419,'A3')
• commit tran t1
Prof. Dr. Wloka
Basic Joined Table (Feature ID F041)
•
Oracle 10g
–
–
•
select Employee.empno, Employee.Surname, TaskEmp.Taskno from Employee left join
TaskEmp on Employee.empno = TaskEmp.empno
select Employee.empno, Employee.Surname, TaskEmp.Taskno from Employee right outer
join TaskEmp on Employee.empno=TaskEmp.empno
DB2
–
–
select employee.empno, employee.surname, taskemp.taskno from dbo.employee as
employee, dbo.taskemp as taskemp where employee.empno = taskemp.empno
Select Employee.Empno, Employee.Surname, Taskemp.Taskno From Dbo.Employee As
Employee Right Outer Join Dbo.Taskemp As Taskemp On Employee.Empno =
Taskemp.Empno
Prof. Dr. Wloka
Basic Joined Table (Feature ID F041)
•
MS SQL Server
–
–
•
select Employee.empno, Employee.Surname, TaskEmp.Taskno from Employee left join
TaskEmp on Employee.empno=TaskEmp.empno
select Employee.empno, Employee.Surname, TaskEmp.Taskno from Employee right outer
join TaskEmp on Employee.empno=TaskEmp.empno
Sybase ASE 15
–
–
select Employee.Empno, Employee.Surname, Taskemp.Taskno from dbo.Employee as
Employee, dbo.Taskemp as Taskemp where Employee.Empno = Taskemp.Empno
Select Employee.Empno, Employee.Surname, Taskemp.Taskno From dbo.Employee As
Employee Right Outer Join dbo.Taskemp As Taskemp On Employee.Empno =
Taskemp.Empno
Prof. Dr. Wloka
View and Grouped Operations (Feature ID F311-03, F131)
•
Oracle 10g
–
–
•
DB2 V9
–
–
•
create view distinct_view as select distinct * from dbo.employee
select empno,count(*) as Available_Entry from dbo.taskemp group by empno having
count(*)>1
MS SQL Server 2005
–
–
•
create or replace view distinct_view as select distinct * from employee
select empno,count(*) as Available_Entry from taskemp group by empno having count(*)>1
create view distinct_view as select distinct * from dbo.employee
select empno,count(*) as Available_Entry from dbo.taskemp group by empno having
count(*)>1
Sybase ASE15
–
–
create view distinct_view as select distinct * from dbo.Employee
select Empno,count(*) as Available_Entry from dbo.Taskemp group by Empno having
count(*)>1
Prof. Dr. Wloka
CAST Function (Feature ID F201, F221)
•
Oracle 10g
–
–
•
DB2 V9
–
–
•
select cast(Surname AS CHAR(5) ) from dbo.Employee
insert into dbo.car values(‘A5’,’SoftwareIII’,’Anna’,DEFAULT)
MS SQL 2005
–
–
•
select cast(Surname as varchar(5)) from Employee
insert into car values(‘A5’,’SoftwareIII’,’Anna’,DEFAULT)
select cast(Surname AS CHAR(5) ) from dbo.Employee
insert into dbo.car values(‘A5’,’SoftwareIII’,’Anna’,DEFAULT)
Sybase ASE 15
–
–
select cast(Surname AS CHAR(5) ) from Employee
insert into car values(‘A5’,’SoftwareIII’,’Anna’,DEFAULT)
Prof. Dr. Wloka
CASE expression (Feature ID F261)
•
Oracle 10g
select Empno,Taskno, (case actualeffort
when .2 then 'Min Effort'
when .55 then 'Med Effort'
when .6 then 'Max Effort'
end) EffortStatus from Taskemp
•
DB 2 V9
select Empno,Taskno, (case actualeffort
when .2 then 'Min Effort' when .55
then 'Med Effort' when .6
then 'Max Effort'
end) EffortStatus from dbo.Taskemp
Prof. Dr. Wloka
CASE expression (Feature ID F261)
•
MS SQL Server 2005
select empno, effortstatus = case targeteffort
when 0.20 then 'min effort' when 0.55
then 'med effort' when 0.60
then 'max effort' else 'no effort'
end, taskno from dbo.taskemp
•
Sybase ASE15
select Empno,Taskno, "Effort Status" =
case
when Targeteffort = 0.20 then "Min Effort"
when Targeteffort = 0.55 then "Med Effort"
when Targeteffort = 0.60 then "Max Effort"
end from Taskemp
Prof. Dr. Wloka
SQL/Information and Definition Schemas
(SCHEMATA)
Prof. Dr. Wloka
Basic Information Schema
•
Columns view
– It Identifies the privileges on columns of tables defined in the catalog that are
available to or granted by a given user or role.
•
Tables View
– It Identifies the tables defined in the catalog that are accessible to a given user
or role.
•
Views view
– It Identifies the viewed tables defined in the catalog that are accessible to a
given user or role.
•
Table_constraints view
– It Identifies the table constraints defined on tables in the catalog that are
accessible to a given user or role.
•
Referential_constraints view
– It Identifies the referential constraints defined on tables in the catalog that are
accessible to a given user or role.
•
Check_constraints view
– It Identifies the check constraints defined in the catalog that are owned by a
given user or role.
Prof. Dr. Wloka
SQL Routines and types using Java Programming
Language (SQL/JRT)
Prof. Dr. Wloka
JRT Features in Oracle 10g
– Basic manipulation
• Load java class/jar file into database
• loadjava c:/Example.jar
– Java Routines
• Supports through JDBC funtions
– JDBC
import java.sql.*;
import java.io.*;
public class QueryExample {
public static void main (String[] args) throws SQLException, IOException {
//Load and register Oracle driver
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
//Establish a connection
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:
@training:1521:Oracle", "oratest", "oratest"); // or DSN name} }
Prof. Dr. Wloka
•
Java using SQL – Example
–
Assume that the executable for the following Java class is stored in the Oracle [ODJU03]
database
Java code looks like
–
public class Fibonacci {
public static int fib (int n) {
if (n == 1 || n == 2)
return 1; else
return fib(n - 1) + fib(n - 2); }}
create or replace function fib (n number) return number as language java name
’fibonacci.fib(int) return int’;
sql> variable n number
sql> variable f number
sql> execute :n := 7;
PL/SQL procedure successfully completed.
sql> call fib(:n) into :f;
Call completed.
Output look like
SQL> PRINT f
F
---------13
Prof. Dr. Wloka
•
CALL procedures
Java code look like
import java.sql.*;
import oracle.jdbc.driver.*;
public class Deleter {
public static void main (String[] args) throws SQLException
{ Connection conn = new OracleDriver().defaultConnection();
String sql = "DELETE FROM " + args[0];if (args.length > 1) sql += " WHERE " + args[1];
try {Statement stmt = conn.createStatement();stmt.executeUpdate(sql);stmt.close();}
catch (SQLException e) {System.err.println(e.getMessage());} }}
PL/SQL looks like
create or replace package body pkg asprocedure delete_rows (table_name varchar2, condition
varchar2) as language javaname 'deleter.main(java.lang.string[])'; end;sql> call
pkg.delete_rows('emp', 'sal > 1500');
Prof. Dr. Wloka
JRT Features in DB2
•
JDBC
Import java.sql.*;
Public Class Example
{ Public static void main(String arg[])throws Exception
{ Class.forName(“com.ibm.db2.jdbc.app.DB2Driver”);
Connection conn = DriverManager.getConnection
(“jdbc:db2:test”;”db2admin”,”db2admin”); }}
•
JAR Administration
– Install
CALL sqlj.install_jar( 'file:/home/bob/bobsjar.jar', 'MYJAR' )
– Replace
CALL sqlj.replace_jar( 'file:/home/bob/bobsnewjar.jar', 'MYJAR' )
– Remove
CALL sqlj.remove_jar( 'MYJAR' )
Prof. Dr. Wloka
JRT Features in DB2
• Java Routines
Java program look like
public static double product( double in1, double in2 ) throws SQLException
{ return in1 * in2; }
SQL Procedural Language looks like
create function product( double in1, double in2 )
returns double
language java
parameter style java
no sql
fenced threadsafe
deterministic
returns null on null input
no external action
external name 'Examplejar:udfclass.product'
Prof. Dr. Wloka
JRT Features in DB2
•
Stored Procedures
public static void javastp( int inparm, int[] outparm, ResultSet[] rs ) throws SQLException
{ Connection con = DriverManager.getConnection( "jdbc:default:connection" );
PreparedStatement stmt = null;
String sql = SELECT phone FROM Employee WHERE index = ?";
//Prepare the query with the value of index
stmt = con.prepareStatement( sql ); stmt.setInt( 1, inparm );
//Execute query and set output parm
rs[0] = stmt.executeQuery(); outparm[0] = inparm + 1;
//Close open resources
if (stmt != null) stmt.close(); if (con != null) con.close();
return;
}
SQL Procedural Language looks like
create procedure javaproc( in in1 int, out out1 int )
language java
dynamic result sets 1
fenced threadsafe
external name 'myjar:stpclass.javastp'
Prof. Dr. Wloka
JRT Features in Sybase ASE15
•
Basic Manipulations
– Load java class/jar file into database [SASE05]
Query :
instjava –f “c:/Home_Address.jar”
– View the description about installed java classes in database
Query:
sp_helpjava ‘jar’ ,Home_Address, detail
– To remove the installed java package
Query:
Remove java package “Home_Address.jar”
– Table creation – java type as column
Query:
Alter table Employee add column Address Home_Address
Insertion
declare @A Address
select @A = new Address('123 Main Street', '99123')
insert into Employee (Home_Address) values(@A) where Empno=102
declare @A1 Address,
select @A1=new Address('321 Second Street', '99123')
update Employee set Address=new Address(@A1) where Empno=102
Prof. Dr. Wloka
•
Java Routines
• Pre Defined Methods in “Example.java”: getNumber(), getName(), getStreet()
• declare @street varchar(15)
• select @street=Example.getStreet(street) from Employee where Empno=102
•
Data Type Mapping
• Auto Conversion
– Select Example.getNumber(Address) from Employee where Empno=102
• Runtime Conversion
Java program looks like
Public class Example1 implements java.io.Serializable
{ public static int Empno(int I) { return I ;}
public static String Surname(String S) { return S;}
}
Transact SQL looks like
declare @I Emp
declare @S Name
select @I = Example1.Empno(@I)
select @S=Example1.Surname(@S)
Prof. Dr. Wloka
•
JDBC
import java.sql.*;
public class JDBCExamples
{{ public static void main(String args[]) {
try{ String server = args[0];
String action = args[1].toLowerCase();
Connection con = connecter(server);
} catch (Exception e) {
System.out.println("\n Exception: ");
e.printStackTrace(); } }
// JDBC
Prof. Dr. Wloka
•
Connector class
public static Connection connecter(String server)
throws Exception, SQLException, ClassNotFoundException {
String forName="", url="";
if (server=="default") {
// server connection to current server
forName = "sybase.asejdbc.ASEDriver";
url = "jdbc:default:connection";
} else if (server!="default")
{
//client connection to server
forName= "com.sybase.jdbc.SybDriver";
url = "jdbc:sybase:Tds:"+ server; }
String user = "sa", password = "";
Class.forName(forName);
// Load the driver
Connection con = DriverManager.getConnection(url,
user, password);
return con; }
// Get a connection
Prof. Dr. Wloka
•
Stored Procedures
Create proc inoutproc @id int, @name varchar(20), @job output, @phone output as
select @job=job_1,@phone=phone_1 where empno=@id
Code snippet
Callable statement cs=con.prepareCall(“{call inoutproc(?,?,?,?)}”);
cs.setInt(1,102); cs.setString(2,”Uhu”)
cs.registeroutparameter(3,java.sql.Types.varchar)
cs.registeroutparameter(4,java.sql.Types.object)
int res=cs.executeupdate();
String Job=cs.getString(3); String Phone=cs.getString(4);
System.out.println(“Job:” + Job);
System.out.println(“Phone: ” + Phone);
Prof. Dr. Wloka
•
SQLJ Functions
– SQLJ User Defined functions
Create function SQLJ_Fc()
language java
external name ‘SQLJExample.fibo’
– SQLJ Stored Procedure
Create procedure SQLJ_Pr()
Language java
Parameter style java
External name ‘SQLJExample_Proc.Fibo’
Prof. Dr. Wloka
SQL/JRT in MS SQL Server
•
JDBC
–
–
•
MS SQL Server supports Type 4 JDBC Driver.
Based on native implementation.
Other Features
–
MS SQL Server doesnot support all other features
Prof. Dr. Wloka
SQL/XML
Prof. Dr. Wloka
Implementation comparison with DBMS
•
XML Type
– Oracle 10g
• alter table employee add Resume xmltype
– DB2 V9
• alter table dbo.employee add Resume xml
– MS SQL Server 2005
• alter table dbo.employee add Resume xml
Prof. Dr. Wloka
XML Element
•
Oracle 10g
select xmlelement (name "Employee",
xmlelement (name "Empno", empno),
xmlelement (name "Empname", Surname),
xmlelement (name "Job", job) ) as Employee_Details from Employee where
empno=102
•
DB2 V9
select xmlelement (name "Employee",
xmlelement (name "Empno", empno),
xmlelement (name "Empname", Surname),
xmlelement (name "Job", job) ) as Employee_Details from dbo.Employee
where empno=102
•
MS SQL Server 2005, Sybase ASE15
Not Supported
– Results
<?xml version="1.0" encoding="UTF-16" ?>
<Employee><Empno>102</Empno> <Empname>Igel</Empname>
<Job>Industriekaufmann</Job> </Employee>
Prof. Dr. Wloka
XML Forest with Attribute and child Elements
•
Oracle 10g
select xmlelement("emp", xmlattributes(e.firstname ||' '|| e.surname as "name"),
xmlforest(e.job, e.departno as "department")) as "employee" from employee e where
Surname like ‘%e%’ where Firstname like '%e%'
•
DB2 V9
select xmlelement(name "emp", xmlattributes(firstname ||' '|| surname as "name"),
xmlforest(job, departno as "department")) as “employee” from dbo.employee where
Firstname like '%e%‘
•
Not Supported
•
Results
<emp name="Diebische Elster"><JOB>Dipl.-Math.</JOB><department/> </emp>
<emp name="Stachel Igel"><JOB>Industriekaufmann</JOB><department>A1
</department> </emp>
<emp name="Reinecke Fuchs"><JOB>EDV-Facharbeiter</JOB></emp>
Prof. Dr. Wloka
XML Aggregate
•
Oracle 10g
SELECT XMLElement("Employee Detail", XMLAgg(XMLElement("Employee",e.job||'
'||e.surname)ORDER BY e.departno)) AS "Dept_list" from employee e
•
Results
<Employee Detail>
<Employee>Industriekaufmann Igel</Employee>
<Employee>Dip.-Ing. Hase</Employee>
<Employee>Dipl.-Ing Uhu</Employee>
<Employee>EDV-Facharbeiter Fuchs</Employee>
<Employee>Dipl.-Math. Elster</Employee>
</Employee Detail>
Prof. Dr. Wloka
XML Aggregate
•
DB2 V9
SELECT XMLElement(name "EmployeeDetail", XMLAgg(XMLElement(name
"Employee",e.job||' '||e.surname)ORDER BY e.departno)) AS "Dept_list" from
dbo.employee e
•
Results
<EmployeeDetail>
<Employee>EDV-Facharbeiter Fuchs</Employee>
<Employee>Dipl.-Math. Elster</Employee>
<Employee>Industriekaufmann Igel</Employee>
<Employee>Dip.-Ing. Hase</Employee>
<Employee>Dipl.-Ing Uhu</Employee>
</EmployeeDetail>
Prof. Dr. Wloka
XML Aggregate
• MS SQL Server 2005
– Not Supported
• Sybase ASE15
– Not Supported
Prof. Dr. Wloka
XML Serialize
•
Oracle 10g
select xmlserialize(content xmlelement(name "emp_exempt",
xmlattributes(firstname,surname as "lastname")) as clob) as "result"
from employee where firstname='reinecke'
•
Results
<Emp_Exempt
firstname="Reinecke"
Lastname="Fuchs">
</Emp_Exempt>
•
DB2 V9
select xmlserialize(content xmlelement(name "emp_exempt",
xmlattributes(firstname,surname as "lastname")) as clob) as "result"
from dbo.employee where firstname='reinecke'
•
Results
<Emp_Exempt
firstname="Reinecke" Lastname="Fuchs">
</Emp_Exempt>
•
–
Not Supported
Prof. Dr. Wloka
Optional Conformance - XMLConcat
•
Oracle 10g
select xmlconcat(xmlelement("firstname", e.firstname),
xmlelement("surname", e.surname)) as "result" from employee e;
•
Results
<Firstname>Hoppel</Firstname><Surname>Hase</Surname>
<Firstname>Stachel</Firstname><Surname>Igel</Surname>
<Firstname>Reinecke</Firstname><Surname>Fuchs</Surname>
<Firstname>Diebische</Firstname><Surname>Elster</Surname>
<Firstname>Uhu</Firstname><Surname>Uhu</Surname>
•
DB 2V9
select xmlconcat(xmlelement("firstname",firstname),
xmlelement("surname", surname)) as "result" from dbo.employee
•
Results
<Firstname>Hoppel</Firstname><Surname>Hase</Surname>
<Firstname>Stachel</Firstname><Surname>Igel</Surname>
<Firstname>Reinecke</Firstname><Surname>Fuchs</Surname>
<Firstname>Diebische</Firstname><Surname>Elster</Surname>
<Firstname>Uhu</Firstname><Surname>Uhu</Surname>
Prof. Dr. Wloka
Optional Conformance - XMLConcat
•
MS SQL Server 2005
– Not Supported
•
Sybase ASE15
– Not Supported
Prof. Dr. Wloka
XML Comment
•
Oracle 10g
select xmlcomment('this is a comment') as cmnt from dual;
•
DB2 V9
XMLComment (‘This is a comment')
•
– Not Supported.
Prof. Dr. Wloka
XML Root
•
Oracle 10g
select xmlroot(xmltype('<empno>101</empno>'), version '1.0', standalone
yes) as xmlroot from dual;
•
Results
<?xml version="1.0" standalone="yes"?>
<Empno>101</Empno>
•
DB2V9, MS SQL Server 2005, Sybase ASE15
– Not Supported
Prof. Dr. Wloka
XML Parse
•
Oracle 10g
select xmlparse(content ' <task no="32"> <surname>igel</surname>
<actualeffort>0.10</actualeffort> <targeteffort>0.20</targeteffort>
</task>' wellformed) as task from dual;
•
Results
<Task No="32">
<Surname>Igel</Surname> <Actualeffort>0.10</Actualeffort>
<Targeteffort>0.20</Targeteffort>
</Task>
•
DB2 V9
select xmlparse ( document '<task no="32"> <surname>igel</surname>
<actualeffort>0.10</actualeffort> <targeteffort>0.20</targeteffort>
</task>') as task from sysibm.sysdummy1
•
Results
<Task No="32">
<Surname>Igel</Surname>
<Actualeffort>0.10</Actualeffort>
<Targeteffort>0.20</Targeteffort>
</Task>
Prof. Dr. Wloka
XML Parse
•
MS SQL Server 2005
– Not Supported
•
Sybase ASE15
update Employee set Image_type = xmlparse(Resume) where Empno=106
Prof. Dr. Wloka
XML Namespaces
•
Oracle 10g
Not Supported
•
DB2 V9
select (xmlelement (name "name", xmlnamespaces (default
'http://sample.default.nspace.com'),xmlattributes ('male' as "gender"),
xmlforest ('steve' as "firstname",'ferrington' as "lastname"))) from sysibm.sysdummy1
– Results
<Name xmlns="http://sample.default.nspace.com" gender="MALE">
<FirstName>Steve</FirstName>
<LastName>Ferrington</LastName>
</Name>
•
MS SQL Server 2005
Supports through external program interface(Visual Basic, Visual C++) by means of
“XmlSchemaCollection” class.
•
Sybase ASE15
Not Supported
Prof. Dr. Wloka
Sybase ASE15 : XML Special Features
•
XML Insert
insert into Employee values(106,'Martin','Luther','FRT',36,'Dip.Ing',5354,'A6',"<?xml version='1.0' standalone = 'no'?> <resume
title='software'> <student name='Prabhu'>
– Results
<Experience>Two Years in Software Programing</Experience>
<Area> <First>RDBMS</First><Second>Java</Second></Area>
</student> <student name='Garten'>
<Experience>Two Years in RDBMS</Experience>
<Area><First>Sybase</First>
<Second>Oracle</Second> </Area></student></resume>")
•
XML Extract
select xmlextract('/resume/student/Experience',Resume)from Employee where
empno=106
– Results
<Experience>Two Years in Software Programing</Experience>
<Experience>Two Years in RDBMS</Experience>
Prof. Dr. Wloka
Sybase ASE15 : XML Special Features
•
XML Test
select Empno, Surname from Employee where '//Area[First="RDBMS"]' xmltest
Resume;
•
XML Parse
alter table Employee add Image_type image null;
update Employee set Image_type = xmlparse(Resume) where Empno=106
•
XML Representation
select Empno, xmlrepresentation(Image_type) as status from Employee where
empno=106
•
XML Validate
create table xmltype(xml_t text null);
insert into xmltype values (xmlvalidate( '<employee_name>John Doe
</employee_name> ', option 'dtdvalidate=yes'))
Prof. Dr. Wloka
MS SQL Server 2005: XML Special Features
•
XML DML Support – Insert
insert into Employee values(107,'Peter','Silie','FRT',45,'Dipl-Ing',5343,'A5','<Root>
<JobDescription JobID="31" JobName="Report Generator"> <Features>
</Features> </JobDescription> </Root>');
select Empno,Surname,Firstname,Resume from Employee where Empno=107
•
Insert Using Modify()
UPDATE Employee SET Resume.modify('insert <Experience>3 years in
Teaching</Experience> as first into (/Root/JobDescription/Features)[1]') where
Empno=107;
Select Empno,Surname,Resume from Employee where Empno=107
Prof. Dr. Wloka
MS SQL Server 2005: XML Special Features
•
Delete
UPDATE Employee SET Resume.modify('delete /Root/JobDescription/ Features/*[1]')
where empno=107;
Select Empno,Resume from Employee where Empno=107;
•
Replace Value of (XML DML)
UPDATE Employee SET Resume.modify(' replace value of
(/Root/JobDescription/@JobName)[1] with "Statistik" ') where Empno=107;
Select Empno,Resume from Employee where Empno=107;
Prof. Dr. Wloka
MS SQL Server 2005 - XML Data Type Methods
•
Query() Method
select Resume.query('/Root/JobDescription/Features') from Employee as Label where
empno=107;
•
Value() Method
select Resume.value('(/Root/JobDescription/@JobID)[1]','int')as JOBID from Employee
where empno=107
•
Exist() Method
select Resume.exist('/Root/JobDescription/Features/Experience') as Exist_Check from
Employee where empno=107 or empno=103
•
Nodes() Method
DECLARE @emp xml
SET @emp='<Root>
<emp id="109"><name>Nass</name></emp>
<emp id="110"><name>Uhr</name></emp>
<emp id="111"><name>Dorant</name></emp>
<empl ids="121"></empl>
</Root>'
select c.query('.') as result from @emp.nodes('/root/emp') employee(c)
Prof. Dr. Wloka
MS SQL Server 2005 - XML Schema
create xml schema collection employee_schema as N'<?xml version="1.0" encoding="UTF-16"?>
<xsd:schema targetNamespace=
"http://schemas.microsoft.com/
sqlserver/2004/07/adventure-works/ProductModelManuInstructions"
xmlns ="http://schemas.microsoft.com/sqlserver/2004/07/adventureworks/ProductModelManuInstructions" elementFormDefault="qualified"
attributeFormDefault="unqualified"
xmlns:xsd="http://www.w3.org/2001/XMLSchema" >
<xsd:complexType name="Employee" mixed="true" >
<xsd:sequence minOccurs="0" maxOccurs="unbounded" >
<xsd:element name="Empid" type="xsd:integer"/>
<xsd:element name="Surname" type="xsd:string" />
<xsd:element name="Firstname" type="xsd:string" />
<xsd:element name="Department" type="xsd:string" />
<xsd:element name="Job" type="xsd:string" />
</xsd:sequence> </xsd:complexType>
<xsd:element name="root"> <xsd:complexType mixed="true">
<xsd:sequence>
<xsd:element name="Car" minOccurs="1" maxOccurs="unbounded">
<xsd:complexType mixed="true"> <xsd:sequence>
<xsd:element name="Empl" type="Employee" minOccurs="1"
maxOccurs="unbounded" /> </xsd:sequence>
<xsd:attribute name="Empid" type="xsd:integer" use="required"/>
<xsd:attribute name="Surname" type="xsd:string" use="optional"/>
<xsd:attribute name="City" type="xsd:string" use="optional"/>
</xsd:complexType> </xsd:element>
</xsd:sequence> </xsd:complexType>
</xsd:element> </xsd:schema>' ;
Prof. Dr. Wloka
MS SQL Server 2005 - XML Schema
• select * from
sys.xml_schema_collections
• select name from
sys.xml_schema_namespaces

Vortrag - Hochschule für Technik und Wirtschaft Dresden

Transcrição

Documentos relacionados

Überblick zu den Editionen von SQL Server 2008 R2

Antrag auf Wiederherstellung meines HTW

MATRIX Vollversion Systemvoraussetzungen

Offene Ports der Firewall bei Server/Client Installation

Erfahrungsbericht Nadja Donauer

Girls`Day 2007

Überwachung problematischer Abfragen im Data Warehouse

Visio-Organigramm 11_2011.vsd

XSL, XSLT, FO - Einstieg für XML

Neue Mitarbeiter, Datenblatt / New Employee Form valid