sql3 sql 2008
play

SQL3 / SQL:2008 The SQL3 standard is big and is therefore divided - PowerPoint PPT Presentation

Jan 13, 2024 •10 likes •1.56k views

SQL3 / SQL:2008 The SQL3 standard is big and is therefore divided into parts: DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 1 / 44 SQL3 / SQL:2008 The SQL3 standard is big and is therefore divided into parts:

  1. SQL3:2008 – UDT . . . CREATE TYPE PersonType AS ( ssn SocialSecurityNumber CHECK (alive(ssn)), firstname VARCHAR(15), lastname VARCHAR(15)) INSTANTIABLE NOT FINAL REF IS SYSTEM GENERATED INSTANCE METHOD age() RETURNS INTEGER, INSTANCE METHOD age(ssn SocialSecurityNumber) RETURNS PersonType; CREATE INSTANCE METHOD age() RETURNS INTEGER; FOR PersonType BEGIN RETURN /* code to calculate age */ END; CREATE INSTANCE METHOD age(ssn SocialSecurityNumber) RETURNS PersonType FOR PersonType BEGIN SELF.ssn = ssn RETURN SELF END; • DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 5 / 44

  2. SQL3:2008 – observers and mutators For each attribute in a UDT an observer function and a mutator function are automatically created. Both may be (should be??) redefined by the user. DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 6 / 44

  3. SQL3:2008 – observers and mutators For each attribute in a UDT an observer function and a mutator function are automatically created. Both may be (should be??) redefined by the user. SQL3 uses total encapsulation (as in Smalltalk), meaning that attributes are only accessible by the observer and manipulable by the mutator. DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 6 / 44

  4. SQL3:2008 – observers and mutators For each attribute in a UDT an observer function and a mutator function are automatically created. Both may be (should be??) redefined by the user. SQL3 uses total encapsulation (as in Smalltalk), meaning that attributes are only accessible by the observer and manipulable by the mutator. The observer for firstname in the PersonType is: FUNCTION firstname (p PersonType) RETURNS VARCHAR(15) RETURN p.firstname; DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 6 / 44

  5. SQL3:2008 – observers and mutators For each attribute in a UDT an observer function and a mutator function are automatically created. Both may be (should be??) redefined by the user. SQL3 uses total encapsulation (as in Smalltalk), meaning that attributes are only accessible by the observer and manipulable by the mutator. The observer for firstname in the PersonType is: FUNCTION firstname (p PersonType) RETURNS VARCHAR(15) RETURN p.firstname; and the corresponding mutator: FUNCTION firstname (p PersonType RESULT, newname VARCHAR(15)) RETURNS PersonType BEGIN p.firstname = newname; RETURN p; END; • DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 6 / 44

  6. SQL3:2008 – constructors A constructor is created as well. The default constructor takes no arguments and sets all attributes to their defaults (which almost always is a bad idea). DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 7 / 44

  7. SQL3:2008 – constructors A constructor is created as well. The default constructor takes no arguments and sets all attributes to their defaults (which almost always is a bad idea). The user may (must??) redefine the default constructor and a reasonable constructor for PersonType might be: CREATE CONSTRUCTOR METHOD PersonType ( ssn SocialSecurityNumber, firstname VARCHAR(15), lastname VARCHAR(15)) RETURNS PersonType BEGIN SET SELF.ssn = ssn; SET SELF.firstname = firstname; SET SELF.lastname = lastname; RETURN SELF; END; • DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 7 / 44

  8. SQL3:2008 – User Defined Routine (UDR) The call for generality makes it important to be able to include functions and procedures that are impossible to construct with the built-in facilities DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 8 / 44

  9. SQL3:2008 – User Defined Routine (UDR) The call for generality makes it important to be able to include functions and procedures that are impossible to construct with the built-in facilities E.g. if you have stored images in the database and want to present thumbnail images for an overview it might be better to be able to generate the thumbnail image rather than to store it in the database DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 8 / 44

  10. SQL3:2008 – User Defined Routine (UDR) The call for generality makes it important to be able to include functions and procedures that are impossible to construct with the built-in facilities E.g. if you have stored images in the database and want to present thumbnail images for an overview it might be better to be able to generate the thumbnail image rather than to store it in the database A UDR can be defined as part of a UDT or as part of a schema. I may be written in almost any language, even directly in SQL(3) DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 8 / 44

  11. SQL3:2008 – User Defined Routine (UDR) The call for generality makes it important to be able to include functions and procedures that are impossible to construct with the built-in facilities E.g. if you have stored images in the database and want to present thumbnail images for an overview it might be better to be able to generate the thumbnail image rather than to store it in the database A UDR can be defined as part of a UDT or as part of a schema. I may be written in almost any language, even directly in SQL(3) Procedures are invoked by the command CALL and can have parameters of type IN, OUT or INOUT as in ADA • DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 8 / 44

  12. SQL3:2008 – UDR . . . CREATE FUNCTION thumbNail (IN im ImageType) RETURNS BOOLEAN EXTERNAL NAME /usr/local/bin/thumbnail LANGUAGE C PARAMETER STYLE GENERAL DETERMINISTIC NO SQL; DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 9 / 44

  13. SQL3:2008 – UDR . . . CREATE FUNCTION thumbNail (IN im ImageType) RETURNS BOOLEAN EXTERNAL NAME /usr/local/bin/thumbnail LANGUAGE C PARAMETER STYLE GENERAL DETERMINISTIC NO SQL; the external executable ’ thumbnail ’ has to be provided by the user. The ORDBMS will link to it, store it in the database and invoke it when necessary. DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 9 / 44

  14. SQL3:2008 – UDR . . . CREATE FUNCTION thumbNail (IN im ImageType) RETURNS BOOLEAN EXTERNAL NAME /usr/local/bin/thumbnail LANGUAGE C PARAMETER STYLE GENERAL DETERMINISTIC NO SQL; the external executable ’ thumbnail ’ has to be provided by the user. The ORDBMS will link to it, store it in the database and invoke it when necessary. ’ DETERMINISTIC ’ means the the function always gives the same result for the same input (= no side effects) DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 9 / 44

  15. SQL3:2008 – UDR . . . CREATE FUNCTION thumbNail (IN im ImageType) RETURNS BOOLEAN EXTERNAL NAME /usr/local/bin/thumbnail LANGUAGE C PARAMETER STYLE GENERAL DETERMINISTIC NO SQL; the external executable ’ thumbnail ’ has to be provided by the user. The ORDBMS will link to it, store it in the database and invoke it when necessary. ’ DETERMINISTIC ’ means the the function always gives the same result for the same input (= no side effects) ’ NO SQL ’ means the the function does not contain any SQL statements (= no embedded SQL in this case) DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 9 / 44

  16. SQL3:2008 – UDR . . . CREATE FUNCTION thumbNail (IN im ImageType) RETURNS BOOLEAN EXTERNAL NAME /usr/local/bin/thumbnail LANGUAGE C PARAMETER STYLE GENERAL DETERMINISTIC NO SQL; the external executable ’ thumbnail ’ has to be provided by the user. The ORDBMS will link to it, store it in the database and invoke it when necessary. ’ DETERMINISTIC ’ means the the function always gives the same result for the same input (= no side effects) ’ NO SQL ’ means the the function does not contain any SQL statements (= no embedded SQL in this case) Other options: ’ CONTAINS SQL ’, ’ READS SQL DATA ’ and ’ MODIFIES SQL DATA ’ • DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 9 / 44

  17. SQL3:2008 – UDR, polymorphism Polymorphism in the shape of overloading exists DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 10 / 44

  18. SQL3:2008 – UDR, polymorphism Polymorphism in the shape of overloading exists but with restrictions: • methods may redefine the content of superclass methods with the same name and the same signature, DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 10 / 44

  19. SQL3:2008 – UDR, polymorphism Polymorphism in the shape of overloading exists but with restrictions: • methods may redefine the content of superclass methods with the same name and the same signature, • two methods in the same schema may carry the same name as long as they differ in signature, DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 10 / 44

  20. SQL3:2008 – UDR, polymorphism Polymorphism in the shape of overloading exists but with restrictions: • methods may redefine the content of superclass methods with the same name and the same signature, • two methods in the same schema may carry the same name as long as they differ in signature, • Interesting: If no method is found that has the exact signature of the call (that can be inferred from the call) SQL tries to find a “closest match” and attempts to invoke that method if one is found. • DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 10 / 44

  21. SQL3:2008 – reference types and object identity Just as in Postgres there is an implicit ’ oid ’ assigned to every row in every table. The objective is never to reuse an oid the ensure reference integrity. They are stored in the database, may be shared among databases, and constitute a direct reference to a specific row in a specific table in a database (or, if shared, a specific database in the actual server). • DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 11 / 44

  22. SQL3:2008 – sub- and supertypes CREATE TYPE empType UNDER PersonType AS ( empNo employeeNumber, position VARCHAR(10) DEFAULT ’Assistant’, salary DECIMAL(8,2), department VARCHAR(10), INSTANCE METHOD isBoss () RETURNS BOOLEAN INSTANTIABLE NOT FINAL; CREATE INSTANCE METHOD isBoss () RETURNS BOOLEAN FOR empType BEGIN IF SELF.position=’Boss’ THEN RETURN True; ELSE RETURN False; ENDIF END; DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 12 / 44

  23. SQL3:2008 – sub- and supertypes CREATE TYPE empType UNDER PersonType AS ( empNo employeeNumber, position VARCHAR(10) DEFAULT ’Assistant’, salary DECIMAL(8,2), department VARCHAR(10), INSTANCE METHOD isBoss () RETURNS BOOLEAN INSTANTIABLE NOT FINAL; CREATE INSTANCE METHOD isBoss () RETURNS BOOLEAN FOR empType BEGIN IF SELF.position=’Boss’ THEN RETURN True; ELSE RETURN False; ENDIF END; A type has the type of all its supertypes and, thus, the type system corresponds closely to classes in OOP languages. • DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 12 / 44

  24. SQL3:2008 – sub- and supertypes . . . A special restriction requires all types to have exactly one most specific type which makes it impossible to have multiple inheritance unless the types share a common supertype DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 13 / 44

  25. SQL3:2008 – sub- and supertypes . . . A special restriction requires all types to have exactly one most specific type which makes it impossible to have multiple inheritance unless the types share a common supertype Sometimes it takes a rather strained approach to organizing types to make it work. Not so OOP DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 13 / 44

  26. SQL3:2008 – sub- and supertypes . . . A special restriction requires all types to have exactly one most specific type which makes it impossible to have multiple inheritance unless the types share a common supertype Sometimes it takes a rather strained approach to organizing types to make it work. Not so OOP You may have to create a number of “glue” types. DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 13 / 44

  27. SQL3:2008 – sub- and supertypes . . . A special restriction requires all types to have exactly one most specific type which makes it impossible to have multiple inheritance unless the types share a common supertype Sometimes it takes a rather strained approach to organizing types to make it work. Not so OOP You may have to create a number of “glue” types. You may have to create a mix of table inheritance and OO-inheritance. DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 13 / 44

  28. SQL3:2008 – sub- and supertypes . . . A special restriction requires all types to have exactly one most specific type which makes it impossible to have multiple inheritance unless the types share a common supertype Sometimes it takes a rather strained approach to organizing types to make it work. Not so OOP You may have to create a number of “glue” types. You may have to create a mix of table inheritance and OO-inheritance. The privileges to create types and subtypes are standard database privileges. • DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 13 / 44

  29. SQL3:2008 – tables To guarantee backwards compatibility with earlier SQL standards you must still use ’ CREATE TABLE ’ even if the table consists of just a UDT. DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 14 / 44

  30. SQL3:2008 – tables To guarantee backwards compatibility with earlier SQL standards you must still use ’ CREATE TABLE ’ even if the table consists of just a UDT. Tables are still the only means for persistence DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 14 / 44

  31. SQL3:2008 – tables To guarantee backwards compatibility with earlier SQL standards you must still use ’ CREATE TABLE ’ even if the table consists of just a UDT. Tables are still the only means for persistence Due to a complex syntax and a likewise complex semantics the variations are infinite, e.g DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 14 / 44

  32. SQL3:2008 – tables To guarantee backwards compatibility with earlier SQL standards you must still use ’ CREATE TABLE ’ even if the table consists of just a UDT. Tables are still the only means for persistence Due to a complex syntax and a likewise complex semantics the variations are infinite, e.g CREATE TABLE employee ( info empType, PRIMARY KEY (empNo)); DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 14 / 44

  33. SQL3:2008 – tables To guarantee backwards compatibility with earlier SQL standards you must still use ’ CREATE TABLE ’ even if the table consists of just a UDT. Tables are still the only means for persistence Due to a complex syntax and a likewise complex semantics the variations are infinite, e.g CREATE TABLE employee ( info empType, PRIMARY KEY (empNo)); or CREATE TABLE employee OF empType ( REF IS empID SYSTEM GENERATED, PRIMARY KEY (empNo)); • DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 14 / 44

  34. SQL3:2008 – tables with references CREATE TABLE order ( orderNo orderNumber, item itemNo NOT NULL, quantity INTEGER NOT NULL, unit VARCHAR(5) NOT NULL, client SocialSecurityNumber NOT NULL, representative REF(empType) SCOPE employee REFERENCES ARE CHECKED ON DELETE CASCADE, PRIMARY KEY (orderNo)); • DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 15 / 44

  35. SQL3:2008 – problems with table references If we create a table ’ client ’ as CREATE TABLE client ( info PersonType, deductionrate SMALLINT, latestPurchase DATE, PRIMARY KEY (ssn)); DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 16 / 44

  36. SQL3:2008 – problems with table references If we create a table ’ client ’ as CREATE TABLE client ( info PersonType, deductionrate SMALLINT, latestPurchase DATE, PRIMARY KEY (ssn)); we will spread information about persons over two tables (’ empType ’ is a subtype to ’ PersonType ’). If we use the method regularly then we are in troubles. . . DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 16 / 44

  37. SQL3:2008 – problems with table references If we create a table ’ client ’ as CREATE TABLE client ( info PersonType, deductionrate SMALLINT, latestPurchase DATE, PRIMARY KEY (ssn)); we will spread information about persons over two tables (’ empType ’ is a subtype to ’ PersonType ’). If we use the method regularly then we are in troubles. . . It might be better to create the table differently: CREATE TABLE client UNDER person( deductionrate SMALLINT, latestPurchase DATE); • DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 16 / 44

  38. SQL3:2008 – problems with table references . . . Then the part of client that belongs to the person type will be stored in the person table with the extra feature that if information about a client is deleted from the client table then the corresponding info will automatically be erased from the person table. DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 17 / 44

  39. SQL3:2008 – problems with table references . . . Then the part of client that belongs to the person type will be stored in the person table with the extra feature that if information about a client is deleted from the client table then the corresponding info will automatically be erased from the person table. Rules are quite intuitive: • An insert is automatically distributed over tables and “supertables”. DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 17 / 44

  40. SQL3:2008 – problems with table references . . . Then the part of client that belongs to the person type will be stored in the person table with the extra feature that if information about a client is deleted from the client table then the corresponding info will automatically be erased from the person table. Rules are quite intuitive: • An insert is automatically distributed over tables and “supertables”. • An update is automatically propagated to all involved rows in all involved tables. DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 17 / 44

  41. SQL3:2008 – problems with table references . . . Then the part of client that belongs to the person type will be stored in the person table with the extra feature that if information about a client is deleted from the client table then the corresponding info will automatically be erased from the person table. Rules are quite intuitive: • An insert is automatically distributed over tables and “supertables”. • An update is automatically propagated to all involved rows in all involved tables. • A delete is also automatically proagated to all involved tables. • DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 17 / 44

  42. SQL3:2008 – collection types, ’ ARRAY ’ So far (and it seems to stop at that), two collection types have been implemented, ’ ARRAY ’ and ’ MULTISET ’. DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 18 / 44

  43. SQL3:2008 – collection types, ’ ARRAY ’ So far (and it seems to stop at that), two collection types have been implemented, ’ ARRAY ’ and ’ MULTISET ’. ARRAY works approximately as in programming languages: Suppose that we add relative PersonType ARRAY to the ’ employee ’-table. DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 18 / 44

  44. SQL3:2008 – collection types, ’ ARRAY ’ So far (and it seems to stop at that), two collection types have been implemented, ’ ARRAY ’ and ’ MULTISET ’. ARRAY works approximately as in programming languages: Suppose that we add relative PersonType ARRAY to the ’ employee ’-table. Then we can find the ssn of “closest” and “most distant” realtive by SELECT relative[1].ssn as closestRelSsn, relative[CARDINALITY(relative)].ssn as mostDistantRelSsn FROM employee e WHERE e.ssn=’451112-0356’; DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 18 / 44

  45. SQL3:2008 – collection types, ’ ARRAY ’ So far (and it seems to stop at that), two collection types have been implemented, ’ ARRAY ’ and ’ MULTISET ’. ARRAY works approximately as in programming languages: Suppose that we add relative PersonType ARRAY to the ’ employee ’-table. Then we can find the ssn of “closest” and “most distant” realtive by SELECT relative[1].ssn as closestRelSsn, relative[CARDINALITY(relative)].ssn as mostDistantRelSsn FROM employee e WHERE e.ssn=’451112-0356’; All collection types have the ’ CARDINALITY ’ method that returns the actual number of elements in a collection. OBS that prior to SQL3 you had to indicate the max number of elements that your collection could contain. • DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 18 / 44

  46. SQL3:2008 – collection types, ’ MULTISET ’ You may use relative MULTISET(PersonType) instead of ’ ARRAY ’ and ask for information about the relatives by SELECT n.ssn, n.firstname FROM employee e, UNNEST (e.relative) AS n(ssn,firstname, lastname) WHERE e.ssn = ’451112-0356’; • DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 19 / 44

  47. SQL3:2008 – collection types, ’ MULTISET ’ . . . It would be possible to represent the nested relation from lecture 1 Document Title Author Date Search-words Lang code Lang DBTheory Lindqvist 940322 database 46 swedish Dahl relation normalform ODBMS Johnson 940312 persistent 0 english Peterson transient DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 20 / 44

  48. SQL3:2008 – collection types, ’ MULTISET ’ . . . It would be possible to represent the nested relation from lecture 1 Document Title Author Date Search-words Lang code Lang DBTheory Lindqvist 940322 database 46 swedish Dahl relation normalform ODBMS Johnson 940312 persistent 0 english Peterson transient with (according to MS): CREATE TABLE document ( title VARCHAR(50), author VARCHAR(50) MULTISET, date DATE, search VARCHAR(50) MULTISET, language ROW (code integer, language VARCHAR(20)) ); • DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 20 / 44

  49. SQL3:2008 – Persistent Stored Modules (PSM) PSM is an addition to SQL3 to ensure computational completeness instead of, as in SQL(1) and SQL2, merely relational completeness. DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 21 / 44

  50. SQL3:2008 – Persistent Stored Modules (PSM) PSM is an addition to SQL3 to ensure computational completeness instead of, as in SQL(1) and SQL2, merely relational completeness. Some of these additions: • Declarations, e.g.: DECLARE b BOOLEAN; DECLARE a empType; b = a.isBoss(); DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 21 / 44

  51. SQL3:2008 – Persistent Stored Modules (PSM) PSM is an addition to SQL3 to ensure computational completeness instead of, as in SQL(1) and SQL2, merely relational completeness. Some of these additions: • Declarations, e.g.: DECLARE b BOOLEAN; DECLARE a empType; b = a.isBoss(); • IF ... THEN ... ELSE ... END IF DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 21 / 44

  52. SQL3:2008 – Persistent Stored Modules (PSM) PSM is an addition to SQL3 to ensure computational completeness instead of, as in SQL(1) and SQL2, merely relational completeness. Some of these additions: • Declarations, e.g.: DECLARE b BOOLEAN; DECLARE a empType; b = a.isBoss(); • IF ... THEN ... ELSE ... END IF CASE lowercase(x) • WHEN ’a’ THEN SET a = 1; WHEN ’b’ THEN SET a = 2; SET b = 1; WHEN ’default THEN set b = 2; END CASE; • DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 21 / 44

  53. SQL3:2008 – Persistent Stored Modules (PSM) . . . • Repetition statements, where blocks of SQL statements can be executed and you may loop over the result tables DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 22 / 44

  54. SQL3:2008 – Persistent Stored Modules (PSM) . . . • Repetition statements, where blocks of SQL statements can be executed and you may loop over the result tables • FOR x,y AS SELECT a,b FROM tab1 WHERE cond DO ... END FOR; DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 22 / 44

  55. SQL3:2008 – Persistent Stored Modules (PSM) . . . • Repetition statements, where blocks of SQL statements can be executed and you may loop over the result tables • FOR x,y AS SELECT a,b FROM tab1 WHERE cond DO ... END FOR; • WHILE NOT b DO ... END WHILE; DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 22 / 44

  56. SQL3:2008 – Persistent Stored Modules (PSM) . . . • Repetition statements, where blocks of SQL statements can be executed and you may loop over the result tables • FOR x,y AS SELECT a,b FROM tab1 WHERE cond DO ... END FOR; • WHILE NOT b DO ... END WHILE; • REPEAT ... UNTIL NOT b END REPEAT; DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 22 / 44

  57. SQL3:2008 – Persistent Stored Modules (PSM) . . . • Repetition statements, where blocks of SQL statements can be executed and you may loop over the result tables • FOR x,y AS SELECT a,b FROM tab1 WHERE cond DO ... END FOR; • WHILE NOT b DO ... END WHILE; • REPEAT ... UNTIL NOT b END REPEAT; • A ’ CALL ’ statement to execute procedures and a ’ RETURN ’ statement which allows returning the result of executing an SQL statement from a function invocation. • DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 22 / 44

  58. SQL3:2008 – exceptions SQL3 includes exception management. It is verbose . . . . You declare an exception and describe the action to take if an error occurs. Then you can choose what to do after the action has been executed. You can choose to be satisfied and do nothing more or send the signal on to enclosing environments. DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 23 / 44

  59. SQL3:2008 – exceptions SQL3 includes exception management. It is verbose . . . . You declare an exception and describe the action to take if an error occurs. Then you can choose what to do after the action has been executed. You can choose to be satisfied and do nothing more or send the signal on to enclosing environments. DECLARE { CONTINUE | EXIT | UNDO } HANDLER FOR SQLSTATE { sqlStatus | conditionName | SQLEXCEPTION | SQLWARNING | NOT FOUND } action; DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 23 / 44

  60. SQL3:2008 – exceptions SQL3 includes exception management. It is verbose . . . . You declare an exception and describe the action to take if an error occurs. Then you can choose what to do after the action has been executed. You can choose to be satisfied and do nothing more or send the signal on to enclosing environments. DECLARE { CONTINUE | EXIT | UNDO } HANDLER FOR SQLSTATE { sqlStatus | conditionName | SQLEXCEPTION | SQLWARNING | NOT FOUND } action; DECLARE cond CONDITION [ FOR SQLSTATE sqlStatus ] DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 23 / 44

  61. SQL3:2008 – exceptions SQL3 includes exception management. It is verbose . . . . You declare an exception and describe the action to take if an error occurs. Then you can choose what to do after the action has been executed. You can choose to be satisfied and do nothing more or send the signal on to enclosing environments. DECLARE { CONTINUE | EXIT | UNDO } HANDLER FOR SQLSTATE { sqlStatus | conditionName | SQLEXCEPTION | SQLWARNING | NOT FOUND } action; DECLARE cond CONDITION [ FOR SQLSTATE sqlStatus ] An error (exception) signal may be explicitly sent or resent. SIGNAL sqlStatus; RESIGNAL sqlStatus; • DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 23 / 44

  62. SQL3:2008 – triggers A trigger is a (compound) SQL statement that the DBMS execute automatically as a side effect to some event. DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 24 / 44

  63. SQL3:2008 – triggers A trigger is a (compound) SQL statement that the DBMS execute automatically as a side effect to some event. They are set to execute prior to or after letting the event have is effect on the data and are used to • check input data DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 24 / 44

  64. SQL3:2008 – triggers A trigger is a (compound) SQL statement that the DBMS execute automatically as a side effect to some event. They are set to execute prior to or after letting the event have is effect on the data and are used to • check input data • enforce complex integrity requirements DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 24 / 44

  65. SQL3:2008 – triggers A trigger is a (compound) SQL statement that the DBMS execute automatically as a side effect to some event. They are set to execute prior to or after letting the event have is effect on the data and are used to • check input data • enforce complex integrity requirements • message-sending DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 24 / 44

  66. SQL3:2008 – triggers A trigger is a (compound) SQL statement that the DBMS execute automatically as a side effect to some event. They are set to execute prior to or after letting the event have is effect on the data and are used to • check input data • enforce complex integrity requirements • message-sending • uphold transaction logs DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 24 / 44

  67. SQL3:2008 – triggers A trigger is a (compound) SQL statement that the DBMS execute automatically as a side effect to some event. They are set to execute prior to or after letting the event have is effect on the data and are used to • check input data • enforce complex integrity requirements • message-sending • uphold transaction logs • replication in distributed DBMS DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 24 / 44

  68. SQL3:2008 – triggers A trigger is a (compound) SQL statement that the DBMS execute automatically as a side effect to some event. They are set to execute prior to or after letting the event have is effect on the data and are used to • check input data • enforce complex integrity requirements • message-sending • uphold transaction logs • replication in distributed DBMS CREATE TRIGGER triggername BEFORE | AFTER event ON table [ REFERENCING aliaslist ] [ FOR EACH { ROW | STATEMENT } ] [ WHEN triggercondition ] triggercode • DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 24 / 44

  69. SQL3:2008 – triggers, example CREATE TRIGGER setnull-trigger BEFORE UPDATE ON employee REFERENCING NEW ROW AS newrow FOR EACH ROW WHEN newrow.phone = ’’ SET newrow.phone = NULL DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 25 / 44

  70. SQL3:2008 – triggers, example CREATE TRIGGER setnull-trigger BEFORE UPDATE ON employee REFERENCING NEW ROW AS newrow FOR EACH ROW WHEN newrow.phone = ’’ SET newrow.phone = NULL CREATE TRIGGER check-department BEFORE INSERT ON employee REFERENCING NEW ROW AS newrow WHEN newrow.dept NOT IN (SELECT name FROM department) SIGNAL avd-not-found(newrow.dept) • DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 25 / 44

  71. SQL3:2008 – triggers, example . . . CREATE TRIGGER update-supply AFTER INSERT ON order REFERENCING NEW ROW AS newrow FOR EACH ROW UPDATE supply SET volume = volume - newrow.quantity WHERE item = newrow.item • DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 26 / 44

  72. ORDBMS – some systems Three of the major DBMS providers – IBM, Informix and Oracle – have extended their RDBMS to ORDBMS or universal servers. DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 27 / 44

  73. ORDBMS – some systems Three of the major DBMS providers – IBM, Informix and Oracle – have extended their RDBMS to ORDBMS or universal servers. They have – each with their own technique – extended their DBMS to handle pluggable modules that extends both its data storage possibilities, its type system, its optimizer and its functionality. Let us look att each of these three. • DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 27 / 44

  74. ORDBMS, – DB2 Relational Extenders The first IBM attempt to implement SQL3 was in DB2 version 5, where UDTs, UDFs, LOBs, triggers and stored procedures were introduced, some of them with IBM specific notation. DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 28 / 44

  75. ORDBMS, – DB2 Relational Extenders The first IBM attempt to implement SQL3 was in DB2 version 5, where UDTs, UDFs, LOBs, triggers and stored procedures were introduced, some of them with IBM specific notation. In version 6 they introduced abstract data types and extendibility as described on the pervious slide. With the system you get four Relational extenders that can be used to create new extenders. • DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 28 / 44

  76. ORDBMS, – DB2 Relational Extenders . . . Using the attached relstonal extenders, UDTs, UDFs, et.c. the DBA can create new extenders that cover any data storage need. Third party relational extender developers use exactly the same tools. DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 29 / 44

  77. ORDBMS, – DB2 Relational Extenders . . . Using the attached relstonal extenders, UDTs, UDFs, et.c. the DBA can create new extenders that cover any data storage need. Third party relational extender developers use exactly the same tools. All collected knowledge about RDBMS is still relevant. DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 29 / 44

  78. ORDBMS, – DB2 Relational Extenders . . . Using the attached relstonal extenders, UDTs, UDFs, et.c. the DBA can create new extenders that cover any data storage need. Third party relational extender developers use exactly the same tools. All collected knowledge about RDBMS is still relevant. Nowadays many third party relational extenders are available. DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 29 / 44

  79. ORDBMS, – DB2 Relational Extenders . . . Using the attached relstonal extenders, UDTs, UDFs, et.c. the DBA can create new extenders that cover any data storage need. Third party relational extender developers use exactly the same tools. All collected knowledge about RDBMS is still relevant. Nowadays many third party relational extenders are available. They are simple to install and activate. DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 29 / 44

  80. ORDBMS, – DB2 Relational Extenders . . . Using the attached relstonal extenders, UDTs, UDFs, et.c. the DBA can create new extenders that cover any data storage need. Third party relational extender developers use exactly the same tools. All collected knowledge about RDBMS is still relevant. Nowadays many third party relational extenders are available. They are simple to install and activate. Once plugged in and activated they act as a fully integral part of the DBMS • DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 29 / 44

  81. ORDBMS, – DB2 Relational Extenders . . . • DD2471 (Lecture 08) Modern database systems & their applications Spring 2012 30 / 44

Download Presentation
Download Policy: The content available on the website is offered to you 'AS IS' for your personal information and use only. It cannot be commercialized, licensed, or distributed on other websites without prior consent from the author. To download a presentation, simply click this link. If you encounter any difficulties during the download process, it's possible that the publisher has removed the file from their server.

Recommend

SQL3 / SQL:2008 The SQL3 standard is big and is therefore divided into parts: 1. SQL/Framework,

SQL3 / SQL:2008 The SQL3 standard is big and is therefore divided into parts: 1. SQL/Framework,

SQL3 / SQL:2008 The SQL3 standard is big and is therefore divided into parts: 1. SQL/Framework, contains information common to all parts of the standard and describes the parts. 2. SQL/Foundation, Data definition and data maniputlation syntax and

624 views • 44 slides
Semantic Web 2008 Se a t c eb 008 Semantic Web ca. 2008 S ti W b 2008 Semantic Web

Semantic Web 2008 Se a t c eb 008 Semantic Web ca. 2008 S ti W b 2008 Semantic Web

Semantic Web 2008 Se a t c eb 008 Semantic Web ca. 2008 S ti W b 2008 Semantic Web companies starting & growing Siderean, SandPiper, SiberLogic, Ontology Works, Intellidimension, Intellisophic, TopQuadrant, Data Grid, Mondeca,

481 views • 23 slides
2008 SNA at the global level 2009 Adoption of the 2008 SNA by the UN Statistical Commission 2010

2008 SNA at the global level 2009 Adoption of the 2008 SNA by the UN Statistical Commission 2010

2008 SNA & R e gional Pr ogr amme on E c onomic Statistic s Zeynep Orhun Girard ESCAP Statistics Division Sub regional training workshop on changes in the 2008 SNA affecting GDP compilation 30 June 2014 Astana, Kazakhstan 2008 SNA at

141 views • 12 slides
LHC Status and Introduction to the HL-LHC O. Brning LHC Timeline 1/3 2008 2008

LHC Status and Introduction to the HL-LHC O. Brning LHC Timeline 1/3 2008 2008

LHC Status and Introduction to the HL-LHC O. Brning LHC Timeline 1/3 2008 2008 Accelerator complete Ring cold and under vacuum September 10 th 2008 First beams around September 19 th 2008 The incident 2008

711 views • 60 slides
HPEC 2008 HPEC 2008 September 23-25, 2008 Background RC Taxonomy

HPEC 2008 HPEC 2008 September 23-25, 2008 Background RC Taxonomy

HPEC 2008 HPEC 2008 September 23-25, 2008 Background RC Taxonomy Reconfigurability Factors Computational Density Metrics Internal Memory Bandwidth Metric Results & Analysis Future Work

425 views • 25 slides
Investor Community Conference Call Q1 2008 2008 2008 2008 Risk Review Risk Review Risk

Investor Community Conference Call Q1 2008 2008 2008 2008 Risk Review Risk Review Risk

Investor Community Conference Call Q1 2008 2008 2008 2008 Risk Review Risk Review Risk Review Risk Review Bob McGlashan Executive Vice President and Chief Risk Officer, Enterprise Risk and Portfolio Management March 4 2008 Forward

488 views • 10 slides
2008 2008 2008 2008 Investor Community Conference Call Risk Review Risk Review Risk

2008 2008 2008 2008 Investor Community Conference Call Risk Review Risk Review Risk

2008 2008 2008 2008 Investor Community Conference Call Risk Review Risk Review Risk Review Risk Review Bob McGlashan Executive Vice President and Chief Risk Officer, Enterprise Risk and Portfolio Management March 4 2008 Forward

469 views • 19 slides
2008 Half Year Results Presentation 6 months to 30 June 2008 20 August 2008 1 2008 Half Year

2008 Half Year Results Presentation 6 months to 30 June 2008 20 August 2008 1 2008 Half Year

2008 Half Year Results Presentation 6 months to 30 June 2008 20 August 2008 1 2008 Half Year Results Presentation Terry Davis Group Managing Director 20 August 2008 2 1 HY08 Highlights 1. Double digit EBIT, NPAT and EPS growth

276 views • 15 slides
2008 2008 2008 2008 Investor Community Conference Call Risk Review Tom Flynn Executive

2008 2008 2008 2008 Investor Community Conference Call Risk Review Tom Flynn Executive

2008 2008 2008 2008 Investor Community Conference Call Risk Review Tom Flynn Executive Vice President and Chief Risk Officer August 26 2008 Forward Looking Statements Caution Regarding Forward-Looking Statements Bank of Montreals

666 views • 11 slides
AIS Space RIN 2008 Very Nearly There! 29th October 2008 RIN 2008 - 29th October 2008 Benefits

AIS Space RIN 2008 Very Nearly There! 29th October 2008 RIN 2008 - 29th October 2008 Benefits

AIS Space RIN 2008 Very Nearly There! 29th October 2008 RIN 2008 - 29th October 2008 Benefits of AIS Space Global Coverage = GLOBAL Ability to validate position information Course and speed within AIS message Window of 15

310 views • 17 slides
2008 2008 2008 2008 Investor Community Conference Call Risk Review Tom Flynn Executive

2008 2008 2008 2008 Investor Community Conference Call Risk Review Tom Flynn Executive

2008 2008 2008 2008 Investor Community Conference Call Risk Review Tom Flynn Executive Vice President and Chief Risk Officer November 25 2008 Forward Looking Statements Caution Regarding Forward-Looking Statements Bank of

351 views • 12 slides
2008 2008 2008 2008 Investor Community Conference Call Risk Review Tom Flynn Executive

2008 2008 2008 2008 Investor Community Conference Call Risk Review Tom Flynn Executive

2008 2008 2008 2008 Investor Community Conference Call Risk Review Tom Flynn Executive Vice President and Chief Risk Officer May 27 2008 Forward Looking Statements Caution Regarding Forward-Looking Statements Bank of Montreals

711 views • 18 slides
Investor Community Conference Call Q2 2008 2008 2008 2008 Risk Review Tom Flynn Executive

Investor Community Conference Call Q2 2008 2008 2008 2008 Risk Review Tom Flynn Executive

Investor Community Conference Call Q2 2008 2008 2008 2008 Risk Review Tom Flynn Executive Vice President and Chief Risk Officer May 27 2008 Forward Looking Statements Caution Regarding Forward-Looking Statements Bank of Montreals

270 views • 9 slides
Investor Community Conference Call Q4 2008 2008 2008 2008 Risk Review Tom Flynn Executive

Investor Community Conference Call Q4 2008 2008 2008 2008 Risk Review Tom Flynn Executive

Investor Community Conference Call Q4 2008 2008 2008 2008 Risk Review Tom Flynn Executive Vice President and Chief Risk Officer November 25 2008 Forward Looking Statements Caution Regarding Forward-Looking Statements Bank of

492 views • 6 slides
2008 2008 2008 2008 Investor Community Conference Call Financial Results RUSS ROBERTSON

2008 2008 2008 2008 Investor Community Conference Call Financial Results RUSS ROBERTSON

2008 2008 2008 2008 Investor Community Conference Call Financial Results RUSS ROBERTSON Chief Financial Officer November 25 2008 Forward Looking Statements Caution Regarding Forward-Looking Statements Bank of Montreals public

620 views • 33 slides
Future Direction of LCS study 2008 AIM Training Workshop, T Tsukuba, Oct 27, 2008 k b O t 27

Future Direction of LCS study 2008 AIM Training Workshop, T Tsukuba, Oct 27, 2008 k b O t 27

Future Direction of LCS study 2008 AIM Training Workshop, T Tsukuba, Oct 27, 2008 k b O t 27 2008 Japan Low Carbon Society Scenarios toward 2050 (S-3) [FY2004-2008, Global Environmental Research Program, MOE] [FY2004 2008, Global

217 views • 20 slides
Testing at 43,000 Feet: Reporting Risk That Matters

Testing at 43,000 Feet: Reporting Risk That Matters

W15 People & Teams Wednesday, October 17th, 2018 3:00 PM Testing at 43,000 Feet: Reporting Risk That Matters Presented by:

572 views • 18 slides
Business Combination of Petroamerica Oil Corp. and PetroNova Inc. June 15, 2015 0 Complementary

Business Combination of Petroamerica Oil Corp. and PetroNova Inc. June 15, 2015 0 Complementary

Business Combination of Petroamerica Oil Corp. and PetroNova Inc. June 15, 2015 0 Complementary Asset Base PetroNovas portfolio is highly complementary to Petroamericas existing asset base, bringing reserves, near-term reserve growth

577 views • 20 slides
Renaissance Overview of the KraneShares MSCI China Environment ETF (Ticker: KGRN) KGRN

Renaissance Overview of the KraneShares MSCI China Environment ETF (Ticker: KGRN) KGRN

Opportunities from China's Environmental Renaissance Overview of the KraneShares MSCI China Environment ETF (Ticker: KGRN) KGRN 6/30/2020 1 info @ kraneshares.com Introduction to KraneShares About KraneShares Krane Funds Advisors, LLC is

340 views • 21 slides
2017 Results 19 March 2018 Disclaimer This presentation and its contents are This

2017 Results 19 March 2018 Disclaimer This presentation and its contents are This

2017 Results 19 March 2018 Disclaimer This presentation and its contents are This presentation is directed solely at persons To the extent available, any industry and without limitation, any statements preceded by, followed by or

494 views • 27 slides
POWER CONTROL SYSTEM FOR THE AGILE SATELLITE G. Ebale, A. Lamantia, M. La Bella Blu Electronic

POWER CONTROL SYSTEM FOR THE AGILE SATELLITE G. Ebale, A. Lamantia, M. La Bella Blu Electronic

POWER CONTROL SYSTEM FOR THE AGILE SATELLITE G. Ebale, A. Lamantia, M. La Bella Blu Electronic s.r.l. via Gallarate,150. 20151 Milano (Italy) 7th European Space Power Conference May 9- -13, 2005 13, 2005 Stresa Stresa, Italy , Italy

370 views • 24 slides
Company Presenta.on October 2011 Disclaimer The informa,on

Company Presenta.on October 2011 Disclaimer The informa,on

Company Presenta.on October 2011 Disclaimer The informa,on contained in this presenta,on ( Presenta.on ) has been prepared by Orca Energy Limited ( the

218 views • 21 slides
Investor Presentation May 2017 Information contained herein is as of March 31, 2017 unless

Investor Presentation May 2017 Information contained herein is as of March 31, 2017 unless

Investor Presentation May 2017 Information contained herein is as of March 31, 2017 unless otherwise noted. Not for distribution in whole or in part without the express written consent of Apollo Global Management, LLC. It should not be assumed

484 views • 32 slides
Full Year Results for the year ended 31 May 2019 25 July 2019 Agenda Adam Palser Overview CEO

Full Year Results for the year ended 31 May 2019 25 July 2019 Agenda Adam Palser Overview CEO

Full Year Results for the year ended 31 May 2019 25 July 2019 Agenda Adam Palser Overview CEO Tim Kowalski Financial review CFO Adam Palser Business review CEO Q&A Appendix 2 Overview Profits : H2 EBIT up 11% to 18.9m (H2

1.01k views • 37 slides

More recommend