Hotsos Symposium ANSI/ISO SQL: Writing Portable SQL
Dallas, March 2005 Lex de Haan (lex.de.haan@naturaljoin.nl)
Who Am I Lex de Haan lex.de.haan@naturaljoin.nl
Applied Maths, TU Delft Oracle employee 1990-2004 – Teacher, delivery manager, product manager seminars, curriculum developer/manager, ST development
ISO SQL Standardization March 2004: Natural Join B.V. http://www.naturaljoin.nl
2
©2004, Natural Join B.V.
Writing Portable SQL - 1
Topics
Introduction ANSI/ISO data types and literals Data definition (DDL) Query and table expressions Predicates and search conditions Expressions and functions Analytical functions Recommendations Q&A
3
Advantages of ANSI/ISO SQL Portability? Readability? Maintainability? If you mastered the SQL language some years ago (as most of use did, right?) you might be surprised ... – to see how the SQL language developed – to learn about some great new SQL features implemented in recent Oracle releases
4
©2004, Natural Join B.V.
Writing Portable SQL - 2
Introduction: Standards Background ISO: Membership by National Body – ISO/IEC/JTC1/SC32/WG3: Database languages
ANSI: Membership by organization or individual
5
Introduction: Standards History SQL-86, SQL-87, SQL-89 (referential integrity added) SQL-92 – One standard with multiple conformance levels: Entry, Transitional, Intermediate, Full
SQL:1999 – Introduced multiple parts and named features: the main part is "Core SQL" – Some parts were backported to SQL-92
SQL:2003 "bug fixing" release (minor enhancements) SQL:2007 ??? 6
©2004, Natural Join B.V.
Writing Portable SQL - 3
SQL:1999 and SQL:2003 Parts 1. 2. 3. 4. 5. 9. 10. 11. 13. 14.
Framework Foundation Call Level Interface (CLI) Persistent Stored Modules (PSM) Host Language Bindings (gone in SQL:2003) Management of External Data (MED) Object Language Bindings (OLB) Information and Definition Schemata Java Routines and Types (JRT) XML Related Specifications (XML)
7
Oracle and the ANSI/ISO Standard B-1
Fully supported Core SQL:2003 features
B-2
Partially supported Core SQL:2003 features
B-3
Equivalent functionality for Core SQL:2003 features
B-4
Unsupported Core SQL:2003 features
B-5
Fully supported optional features of SQL/Foundation:2003
B-6
Partially supported optional features of SQL/Foundation:2003
B-7
Equivalent functionality for optional features of SQL/Foundation:2003
B-8
Fully supported features of SQL/XML:2003
B-9
Equivalent functionality for SQL/XML:2003 features
B-10
Unsupported SQL/XML:2003 features
B-11
Sizing for database constructs SQL Reference, Appendix B: Oracle and Standard SQL 8
Š2004, Natural Join B.V.
Writing Portable SQL - 4
Data Types and Literals Character data CHARACTER [VARYING][(<len>)] [CHARACTER SET <cs-name>] CLOB Shorthands: CHAR(x), NCHAR(x), VARCHAR(x)
Binary data BLOB BIT [VARYING][(<len>)] Literals: B or X prefix Oracle equivalent: RAW
9
Data Types and Literals Exact numeric data NUMERIC[(p[,s])] DECIMAL[(p[,s])] INTEGER, SMALLINT, BIGINT Shorthands: INT, DEC(p,s)
Approximate numeric data FLOAT[(p)] REAL DOUBLE PRECISION (These three types map to NUMBER in Oracle) Oracle equivalents: BINARY_FLOAT, BINARY_DOUBLE Literals: Using E between mantissa and exponent Oracle: Postfix numbers with F or D 10
©2004, Natural Join B.V.
Writing Portable SQL - 5
Data Types and Literals Datetime and intervals DATE TIME[(p)][WITH TIME ZONE] (Oracle: set event 10407) TIMESTAMP[(p)][WITH TIME ZONE] INTERVAL {<single-fld>|<start-fld> TO <end-fld>}
Datetime and interval fields YEAR MONTH DAY HOUR MINUTE SECOND TIMEZONE_{HOUR|MINUTE}
Datetime and interval literals DATE 'yyyy-mm-dd' TIME 'hh:mi:ss [tz-interval]' TIMESTAMP 'yyyy-mm-dd hh:mi:ss [tz-interval]' INTERVAL [+|-]'value' <qualifier> 11
Data Types and Literals Miscellaneous other data types BOOLEAN ROW ARRAY, MULTISET User-defined types, REF types
– I think it is really a pity Oracle doesn't support the BOOLEAN data type...
12
©2004, Natural Join B.V.
Writing Portable SQL - 6
Data Definition Language Examples of some nice/powerful DDL features missing in Oracle: – Local temporary tables – Recursive view definitions – Views with cascading CHECK option CREATE [{GLOBAL|LOCAL} TEMPORARY] TABLE ... [ON COMMIT {PRESERVE|DELETE} ROWS] CREATE [RECURSIVE] VIEW ... AS <query> [WITH [LOCAL|CASCADED] CHECK OPTION]
13
Query and Table Expressions <q1> UNION [ALL][CORRESPONDING[BY(<col-list>)]] <q2> <q1> EXCEPT [ALL][CORRESPONDING[BY(<col-list>)]] <q2> <q1> INTERSECT[ALL][CORRESPONDING[BY(<col-list>)]] <q2> <t1> CROSS JOIN <t2> <t1> [NATURAL] [<join-type>] JOIN <t2> [{ON|USING} ...] <join-type>:== INNER (default) , {LEFT|RIGHT|FULL} [OUTER], UNION WITH [RECURSIVE] ... AS (<query>) ...
14
©2004, Natural Join B.V.
Writing Portable SQL - 7
Predicates and Search Conditions ... IS [NOT] {TRUE|FALSE|UNKNOWN} [NOT] BETWEEN ... AND ... [NOT] LIKE ... [ESCAPE ...] SIMILAR (regular expressions) IS [NOT] NULL [NOT] IN {<subquery>|<in-list>} <op> {ALL|{SOME|ANY}} (<subquery>) (a,b) OVERLAPS (c,d) MATCH [UNIQUE] [SIMPLE|PARTIAL|FULL] (<subquery>) EXISTS (<subquery>) UNIQUE (<subquery>) IS [NOT] NORMALIZED <r1> IS DISTINCT FROM <r2> <r> [NOT] MEMBER [OF] <ms> <ms1> [NOT] SUBMULTISET [OF] <ms2> <c> IS [NOT] A SET 15
Expressions and Functions Scalar functions USER, CURRENT_{TIMESTAMP[(p)]|DATE|TIME[(p)]} EXTRACT(<component> FROM <value>) SUBSTRING(... FROM <start-pos> [FOR <len>]) {CHARACTER|OCTET|BIT}_LENGTH(...) POSITION(... IN ...) TRIM([[LEADING|TRAILING|BOTH]... FROM] <source>) UPPER, LOWER TRANSLATE(... USING...) CONVERT(... USING...) CAST({<value>|NULL} AS...)
16
©2004, Natural Join B.V.
Writing Portable SQL - 8
Expressions and Functions Conditional value expressions CASE <expr> WHEN <v1> THEN <r1>... [ELSE <rn>] END CASE WHEN <c1> THEN <r1>... [ELSE <rn>] END
CASE abbreviations NULLIF(v1,v2)
<=> CASE WHEN THEN ELSE END COALESCE(v1,v2,...) <=> CASE WHEN THEN ELSE END
v1=v2 NULL v1 v1 IS NOT NULL v1 COALESCE(v2,...)
17
Window Functions Window function subtypes: – – – –
Rank functions (RANK, DENSE_RANK) Distribution functions (PERCENT_RANK, CUME_DIST) Row number function (ROW_NUMBER) Window aggregate functions (COUNT, SUM, AVG, ...) table
window
current row
<window-function> OVER ( [ PARTITION BY (...) ] [ ORDER BY (...) ] [{RANGE|ROWS} {BETWEEN ... AND ... | ... PRECEDING } ] ) 18
©2004, Natural Join B.V.
Writing Portable SQL - 9
SQL Standards Compliance Checker FIPS does not certify SQL implementations anymore; today, there is no formal way to test/certify SQL. Check out the Mimer website in Sweden: – – – – –
Go to www.mimer.com Click "Mimer SQL Developer" Navigate to "SQL Validator" Enter your SQL statements in the box Click the "Test SQL" button SQL:2003 Validator
19
Some SQL Recommendations In Oracle SQL, use:
Instead of:
JOIN … {USING|ON} …
Comma in FROM clause
{LEFT|FULL|RIGHT} OUTER JOIN
(+) syntax
CASE, NULLIF
DECODE, NVL2
COALESCE
NVL
EXTRACT, CAST
TO_{CHAR|DATE|NUMBER}
TRIM
LTRIM, RTRIM
{DATE|INTERVAL|TIMESTAMP} '…'
TO_DATE('…','…')
20
©2004, Natural Join B.V.
Writing Portable SQL - 10
That's All...
21
Š2004, Natural Join B.V.
Writing Portable SQL - 11