Integration, Migration und Evolution

User Defined Data Types in DB2
User Defined Functions in DB2
Integration, Migration und Evolution
Dipl.Inf. Frank Kramer
28. Mai 2013
Dipl.Inf. Frank Kramer
Integration, Migration und Evolution
User Defined Data Types in DB2
User Defined Functions in DB2
Quelle
Das Material stammt aus dem Buch SQL in a nutshell von Kevin
E. Kline, Brand Hunt und Daniel Kline und der offiziellen DB2
Dokumentation
Dipl.Inf. Frank Kramer
Integration, Migration und Evolution
User Defined Data Types in DB2
User Defined Functions in DB2
Programm für heute
1
User Defined Data Types in DB2
2
User Defined Functions in DB2
Dipl.Inf. Frank Kramer
Integration, Migration und Evolution
User Defined Data Types in DB2
User Defined Functions in DB2
DB2 Syntax
Es gibt fünf verschiedenen Arten von CREATE TYPE Statements
in DB2
Dipl.Inf. Frank Kramer
Integration, Migration und Evolution
User Defined Data Types in DB2
User Defined Functions in DB2
DB2 Syntax
Es gibt fünf verschiedenen Arten von CREATE TYPE Statements
in DB2
array Statement
cursor Statement
distinct Statement
row Statement
structured Statement
Dipl.Inf. Frank Kramer
Integration, Migration und Evolution
User Defined Data Types in DB2
User Defined Functions in DB2
DB2 Syntax
CREATE TYPE [DISTINCT] type name
[UNDER supertype name]
[AS [new udt name] datatype
[WITH COMPARISONS]
[[NOT] INSTANTIABLE] [INLINE LENGTH int] [WITHOUT COMPARISONS]
[NOT FINAL] MODE DB2SQL
[WITH FUNCTION ACCESS]
[REF USING datatype] [CAST {(SOURCE AS REF ) | (REF AS SOURCE)}
WITH identifier]
[[OVERRIDING] METHOD method definition [,...]]
Dipl.Inf. Frank Kramer
Integration, Migration und Evolution
User Defined Data Types in DB2
User Defined Functions in DB2
Beispiele
CREATE DISTINCT TYPE Bezahlung AS DECIMAL(10,2) WITH
COMPARISONS
Dipl.Inf. Frank Kramer
Integration, Migration und Evolution
User Defined Data Types in DB2
User Defined Functions in DB2
Beispiele
CREATE DISTINCT TYPE Bezahlung AS DECIMAL(10,2) WITH
COMPARISONS
CREATE TYPE Person AS (
Vorname varchar(30),
Nachname varchar(30)
) NOT FINAL
MODE DB2SQL
Dipl.Inf. Frank Kramer
Integration, Migration und Evolution
User Defined Data Types in DB2
User Defined Functions in DB2
Beispiele
CREATE DISTINCT TYPE Bezahlung AS DECIMAL(10,2) WITH
COMPARISONS
CREATE TYPE Person AS (
Vorname varchar(30),
Nachname varchar(30)
) NOT FINAL
MODE DB2SQL
CREATE TYPE Institut AS (
Name varchar(20),
max mitarbeiter varchar(30)
) REF USING INT
MODE DB2SQL
Dipl.Inf. Frank Kramer
Integration, Migration und Evolution
User Defined Data Types in DB2
User Defined Functions in DB2
Beispiele
CREATE TYPE Mitarbeiter UNDER Person AS (
Mitarbeiternummer integer
Institut REF(Institut)
Gehalt Bezahlung
) MODE DB2SQL
Dipl.Inf. Frank Kramer
Integration, Migration und Evolution
User Defined Data Types in DB2
User Defined Functions in DB2
DB2 Syntax
Es gibt fünf verschiedenen Arten von CREATE FUNCTION
Statements in DB2
Dipl.Inf. Frank Kramer
Integration, Migration und Evolution
User Defined Data Types in DB2
User Defined Functions in DB2
DB2 Syntax
Es gibt fünf verschiedenen Arten von CREATE FUNCTION
Statements in DB2
external scalar Statement
external table Statement
OLE DB external table Statement
source or template Statement
SQL scalar, table or row Statement
Dipl.Inf. Frank Kramer
Integration, Migration und Evolution
User Defined Data Types in DB2
User Defined Functions in DB2
DB2 Syntax
CREATE [OR REPLACE] FUNCTION function name
parameter declaration
RETURNS data type|ROW [column list]|TABLE [column list]
[SQL FUNCTION BODY]
Dipl.Inf. Frank Kramer
Integration, Migration und Evolution
User Defined Data Types in DB2
User Defined Functions in DB2
Beispiele
CREATE FUNCTION availableDutchAuctions (auctionDate date )
RETURNS TABLE (auctionId integer, owner varchar(50), title varchar(100),
startPrice DECIMAL(10,2), reservePrice DECIMAL(10,2) )
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
DETERMINISTIC
RETURN
SELECT a.id AS auctionId, a.owner AS owner, a.title AS title, d.start price as
startPrice, a.reserve price AS reservePrice
FROM Auction a
JOIN Dutch d on a.id = d.id
WHERE CURRENT TIME BETWEEN a.start time AND s.end time
AND NOT EXISTS (
SELECT b.auction id FROM Bid b WHERE a.id = b.auction id);
Dipl.Inf. Frank Kramer
Integration, Migration und Evolution
User Defined Data Types in DB2
User Defined Functions in DB2
Beispiele
CREATE FUNCTION proofProduct( productName varchar(50), manufacturer
varchar(50) )
RETURNS INT
LANGUAGE SQL
NO EXTERNAL ACTION
DETERMINISTIC
BEGIN ATOMIC
DECLARE uid INT;
DECLARE pid INT;
SET pid = 1;
SET uid = (SELECT COUNT(id) FROM Product WHERE name =
productName AND Product.manufacuter = manufacturer);
IF uid == 0 THEN
SET pid = 0;
END IF;
RETURN VALUES pid;
END
Dipl.Inf. Frank Kramer
Integration, Migration und Evolution