1 / 18

SQL:2003

SQL:2003. An Introduction to Some New Features. New Data Types. BIT and BIT VARYING removed BIGINT, MULTISET and XML introduced

aziza
Download Presentation

SQL:2003

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. SQL:2003 An Introduction to Some New Features

  2. New Data Types • BIT and BIT VARYING removed • BIGINT, MULTISET and XML introduced • First class data types: can be used in all the contexts that any other (existing) SQL data type can be used; e.g. as column types, parameter and return types of SQL-revoked routines, etc

  3. MULTISET(1) • MULTISET: unordered collection of elements, all of the same element type, with duplicates permitted. • SQL:1999 only has ARRAY • Element type: any SQL data type, including another collection type, e.g. INTEGER MULTISET • Unbounded

  4. MULTISET(2) • Value creation for MULTISET • Enumeration: MULTISET [1,2,3,4] • Query: MULTISET (SELECT grade FROM courses) • A multiset value can be used as a table reference in FROM using UNNEST operator, e.g. SELECT T.A, T.A*2 AS B FROM UNNEST(MULTISET [1,2,3,4]) AS T(A)

  5. MULTISET(3) • Operations supported by MULTISET • Casting a multiset into an array or another multiset with a compatible element type • Removing duplicates from a multiset • Returning the number of elements in a given multiset • Returning the only element of a multiset that has exactly one element • Union, intersection, difference • Three new aggregate functions • Predicates: test =, , , , etc

  6. New multiset aggregate functions • COLLECT: create a multiset from the value of the argument in each row of a group • FUSION: create a multiset union of a multiset value in all rows of a group • INTERSECTION: create a multiset intersection of a multiset value in all rows of a group

  7. Examples SELECT COLLECT(A) AS all_A, FUSION(B) AS all_B, INTERSECTION(B) AS common_B FROM R; R: A B a multiset[1,2] b multiset[1] c multiset[1,3] Result: all_A all_B common_B multiset[a,b,c] multiset[1,1,1,2,3] multiset[1]

  8. Table Functions • SQL-invoked function that returns a “table” • Not a real table, but a MULTISET type with ROW type element • Can be queried like a table

  9. Table Functions – Example (1) CREATE FUNCTION func( ) RETURNS TABLE (A CHAR(10), B INT) LANGUAGE SQL //body written in SQL READS SQL DATA //read-only access to DB DETERMINISTIC //same input/DB state -> same result RETURN TABLE( SELECT name, age FROM student); To invoke func( ): SELECT T.A FROM TABLE(func( )) AS T WHERE T.B>20;

  10. Table Functions – Example(2) CREATE FUNCTION external_func( ) RETURNS TABLE (A VARCHAR(10), B INT) NOT DETERMINISTIC //same input, different results NO SQL //not call back to SQL engine to exec SQL LANGUAGE C EXTERNAL PARAMETER STYLE SQL; //associated with null indicator How is external_func( ) invoked by the SQL engine? • Open call: once. Set up data structures • Fetch call: multiple invocations. One row transmitted per invocation. • Close call: once.

  11. CREATE TABLE LIKE (1) • SQL:1999 allows copying table structure into new table – but restricted to column name and type CREATE TABLE T1 ( C1 INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 2), C2 VARCHAR(100) NOT NULL DEFAULT ‘test’); CREATE TABLE T2 ( LIKE T1, C3 CHAR(50)); CREATE TABLE T3 ( C1 INTEGER, C2 VARCHAR(100), C3 CHAR(50)); • T2 is equivalent to T3

  12. CREATE TABLE LIKE (2) • SQL:2003 introduced additional (optional) options for the LIKE clause for copying more information CREATE TABLE T1 ( C1 INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 2), C2 VARCHAR(100) NOT NULL DEFAULT ‘test’); CREATE TABLE T4 ( LIKE T1 INCLUDING COLUMN DEFAULTS INCLUDING INDENTITY); • T4 is equivalent to T1

  13. CREATE TABLE AS • Create a table from a query CREATE TABLE T5 (D1, D2, D3, D4) AS ( SELECT T1.C1, T1.C2, T2.C3 FROM T1, T2 WHERE T1.C2 = T2.C2) WITH DATA; • Similar to materialized query tables(MQT) or materialized views? • No dependency

  14. MERGE • Transfer data from “transaction table” to a “master table” • TT contains • Updates to the existing rows in the MT • New rows that should be inserted into the MT • MERGE = UPDATE + INSERT

  15. MERGE -- Example Inventory(before) Shipment Inventory(after) PART DESC QTY PART DESC QTY PART DESC QTY 1 AAA 10 2 BBB 5 1 AAA 20 2 BBB 15 4 DDD 15 2 BBB 20 3 CCC 20 1 AAA 10 3 CCC 20 4 DDD 15 MERGE INTO Inventory AS I USING (SELECT PART,DESC,QTY FROM Shipment) AS S ON (I.PART = S.PART) WHEN MATCHED THEN UPDATE SET QTY=I.QTY+S.QTY WHEN NOT MATCHED THEN INSERT (PART,DESC,QTY) VALUES (S.PART, S.DESC, S.QTY)

  16. Sequence Generator • Used for generating unique values automatically • New kind of DB object • Example CREATE SEQUENCE PartSeq AS INTEGER START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 10000 NO CYCLE INSERT INTO Shipment VALUES (NEXT VALUE FOR PartSeq, ‘EEE’, 20);

  17. Identity Columns • Similar to sequence generator, but value is automatically generated • Example CREATE TABLE Parts ( PART INTEGER GENERATED ALWAYS AS INDENTITY (START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 10000 NO CYCLE), DESC VARCHAR(100), QTY INTEGER); INSERT INTO Parts(DESC,QTY) VALUES(‘FFF’,30);

  18. Generated Columns • A generated column is associated with a scalar expression that evaluate the value for that column • Example CREATE TABLE Employees ( EMPID INTEGER, SALARY DECIMAL(7,2), BONUS DECIMAL(7,2), TOTAL_COMP GENERATED ALWAYS AS (SALARY + BONUS)) INSERT INTO Employees(EMPID,SALARY,BONUS) VALUES(501, 65000.00, 5000.00);

More Related