Transcription
8. More SQL features:Views, PL/SQL, Functions,Triggers8.18.28.38.48.58.6Views and view updatesApplication architecturesPL/SQL – PL/pgSQLFunctions and ProceduresTriggersAbstract Data Typessee Kemper/Eickler chap. 14, Elmasri chap. 6.8, O'Neill: Chap. 4,Melton: SQL99, Postgres and Oracle Manuals (PL/PGSQL,PL/SQL)
8.1 ViewsDef.: A view is a named SQL-query, whichbecomes part of the schema as a virtual tableIntention Casting the database schema for differentapplications Access protection Privacy Structuring of SQL programs The RDM concept for external schemas("3-schema-architecture") HS-201008-PLSQLetc-2
Materialized ViewsDef.: A materialized view is a temporary Table , whichcontains the result set of an SQL query Not in all DBMS Often used in replication scenarios No way to insert / delete data But refreshing of the view makes sense Sometimes called snapshot Different from temporary tablesCREATE TEMPORARY TABLE Temp AS ( Query ) Insertion / Deletion allowed Dropped at the end of a session HS-201008-PLSQLetc-3
SQL ViewsMay be defined on base tables (ordinary tables)or on views (or both)CREATE VIEW LargeCities(name,population,country, code,fraction)AS(SELECT ci.name, ci.population, co.name, co.code,ci.population/co.populationFROM City ci JOIN Country co ON ci.country co.codeWHERE ci.population 1000000)CREATE VIEW VeryLargeCitiesAS(SELECT name, population, countryFROM LargeCities lWHERE l.population 3000000) HS-2010implicitecolumnnames08-PLSQLetc-4
Views and privacyVery large American cities:JOIN with encompasses(continent,country.)CREATE OR REPLACE VIEW VLAmeriCities AS(SELECT c.name, c.population, c.countryFROM LargeCities c JOIN Encompasses eON c.code e.countryWHERE e.continent 'America'AND c.population 3000000)Views may be used like ordinary table in queries.Privacy: column access may be granted even if accessto base table is not allowed ! HS-201008-PLSQLetc-5
Views and code readability. simplify SQL queriesCountries having more inhabitants than all american big citiesSELECT c.name, c.populationFROM country cWHERE c.population ALL(SELECT populationFROM VLAmeriCities)Operator tree of query more complicated. HS-201008-PLSQLetc-6
Query planJoint optimization ofviews and query HS-201008-PLSQLetc-7
Evaluation of viewsSteps:[1. Transform query on view using its definition]2. Construct operator tree including view definitionsand query3. Optimize plan4. Execute query on base tables HS-201008-PLSQLetc-8
Views in PostgresMore general substitution concept in PostgresRules are "first class objects": CREATE RULE.CREATE VIEW myview AS SELECT * FROM mytab;equivalent toCREATE TABLE myview ( same column list as mytab );CREATE RULE " RETURN" AS ON SELECT TO myview DOINSTEAD SELECT * FROM mytab;Kind of dynamic view evaluation compared tostatic rewrite of query or query tree HS-201008-PLSQLetc-9
8.2 Updatable viewsView updatesMany views are not updatable. Obviously:CREATE OR REPLACE VIEW PopulInCities (country,cityPop)AS(SELECT co.name, sum(ci.population)FROM City ci JOIN Country co ONci.country co.codeGROUP BY co.name)View not updatable if defined using: Aggregation Arithmetic in Projection DISTINCT HS-201008-PLSQLetc-10
Semantic characterization of updatable viewsDef: A view V is updatable if for every update u (*)there exist one or more updates cu which applied tothe base relations and the subsequent application of theview definition result in the same result:u (V(D)) V (cu (D) ) Semantic characterization, Wanted: syntactic criteria for updatability HS-2010(*) as if it were materialized08-PLSQLetc-11
Syntactic criteriaRead only views may be arbitrarily defined,Update is rejected, if view not updatable.Syntactic criteriaNot updatable (SQL 92) HS-2010if grouped (GROUP BY), HAVING or aggregatedDISTINCT in SELECT clauseset operators (INTERSECT, EXCEPT, UNION)more than one table in FROM clauseNo updates on join views (restrictive!)08-PLSQLetc-12
Views and joinsCREATE VIEW CCP AS(SELECT c.name, c.capital, ci.populationFROM Country c JOIN City ciON c.capital ci.name and c.code ci.countryWHERE ci.population 1000000ORDER BY c.name)Base tables: Country, City,Join on key: row insertion in one table (Country) maygenerate one new row in in the other (City), if notalready present. HS-201008-PLSQLetc-13
Syntactic criteria (2)SQL 1999Columns (of views) are potentially updatable if .no DISTINCT operatorno GROUP BY, HAVING clauseno derived columns (e.g. arithmetic expressions)(1) Column is updatable if potentially updatableand one table in FROM clause (!) HS-201008-PLSQLetc-14
Key preserved tables SQL 1999: more than one table in FROM clause(2) Column c is updatable if potentially updatableand- c belongs to exactly one table- the key of the table is preserved, i.e. the update of cmay be traced back to exactly one row.Table is key preserved if every key of the table can alsobe a key of the join result table.A key-preserved table has its keys preservedthrough a join. HS-201008-PLSQLetc-15
Find updatable columnsFind updatable columns by querying thecatalogueSELECT column name, updatableFROM user updatable columnsWHERE table name 'LARGECITIES'-- OracleCOLUMN NTRYCODEFRACTION HS-2010This is a (system) viewmust be upper caseUPDATABLE--------YESYESNONONO08-PLSQLetc-16
Views WITH CHECK OPTIONIssue: side effects on base table rows, no effect on viewCREATE VIEW CCLarge(ctryName, capital, population) AS(SELECT c.name as ctryName, c.capital, ci.populationFROM Country c JOIN City ciON c.capital ci.name and c.code ci.countryand c.province ci.provinceWHERE ci.population 1000000)WITH CHECK OPTIONUPDATE TABLE CC LargeSET population population - 20000WHERE capital 'Amsterdam' --has 1011000 inhabitantsWhat happens? HS-201008-PLSQLetc-17
CHECK OPTIONUpdate may result in insertion and deletion (!) of rowsCHECK OPTION: update and insert must result in rows theview can select , otherwise exception raisedExample above: update has to be performed on base table HS-201008-PLSQLetc-18
View update by triggersTriggers: Event – Condition – Action rulesEvent:Update, insert, delete (basically)Condition: WHEN some conditon on table Action:some operation ( expressed as DML, DBScript language expression, even Java)INSTEAD OF Triggers (Postgres: rules)- defined on views- specify what to do in case of an updateof the viewdetails on triggers: see below HS-201008-PLSQLetc-19
Summary views Views: important mechanism foraccess protection / privacysimplyfy SQL application programming The mechanism for defining external schemas in the RDM Useful for modeling generalization hierarchies Disadvantage: updates (inserts, deletes) not alwayspossible Criteria for updatable views complex INSTEAD OF triggers are a convenient work around HS-201008-PLSQLetc-20
8.2 Application Architectures SQL is an interactive language, but. Main usage: access database from application programMeans basically: SQL-statements staticallyknown, but parameterized:SELECT name INTO :ctryNameFROM Country JOIN Economy ON.WHERE gdp :threshold"Impedance mismatch": tuple sets vs records or objects Typical database usage:independent applications concurrently access DB Web based user interface is standard today Big differences of (application) systemarchitectures HS-201008-PLSQLetc-21
Business logicBig question: where sits the "business logic" ? Business logic: the steps which have to bemade in order to process a user query.e.g. "go to check out" in an Internet shop is implementedby several steps, most of them access the DB:User logged in? if not., perform stock keeping operations, prepareinvoice, charge client, . Two tier or Three tier: business logic separatedfrom user interaction as well as data access? HS-201008-PLSQLetc-22
ArchitecturesClient server model– Business logic sits in application program– Runs on a machine different from database server– Interaction by means of SQL queries, inserts, Result setsUser interaction: web browser or integrated (e.g. Swing) HS-201008-PLSQLetc-23
Client server exampleclass JdbcTest {public static void main (String args []) throws SQLException {// Load driverDriverManager.registerDriver (new oracle.jdbc.OracleDriver());// Connect to the local databaseConnection conn DriverManager.getConnection ("jdbc:oracle:thin:@myhost:1521:orcl","hr", "hr");// Query the employee namesStatement stmt conn.createStatement ();ResultSet rset stmt.executeQuery ("SELECT last name FROMemployees");// Print the name outwhile (rset.next ())System.out.println (rset.getString (1));// Close the result set, statement, and the } HS-201008-PLSQLetc-24
Persistence abstraction mechanismsObject oriented programming model with persistenceabstraction hides SQL database accessuser interactionGeneratedby ORmappingtool HS-2010"Business Logic"DB tc-25
Server side application logic Business logic in stored procedures"Thin"app. code."Thin"app. codeCall procedure,Result setsDB-ServerApplication code(Stored Procedures)Thin clients Stored procedures written in DB specific host languagee.g. PL/SQL, PL/pgSQL based on SQL/PSM standard Programming language like C, C , Java, HS-201008-PLSQLetc-26
Multi tier architectureGUI clientWebbrowserMiddleware layer/ Web HS-2010Middle tierWeb ServerApplicationServerDB ApplicationDB-ServerbrowserDB-ServerDB clientDB ApplicationFile SystemDB-Server08-PLSQLetc-27
Server side architecturesrequest handlingin web server HS-2010Basicallystoredproceduresrequest handlingin DB server08-PLSQLetc-28
Pros and ConsServer based code: performance communication efficiency Database servers provide (most of) the functionalityMulti tier architecture scalability interoperability of autonomous systems secure and reliable transport of request / reply messages Better workflow supportBut base technologies are basically the samein both architectures. HS-201008-PLSQLetc-29
Base technologies. to come: Database script languages (like PL/pgSQL)also used for trigger programming Stored procedures using Java, C or alike Embedding SQL into programming languagescall level interface e.g. JDBCintegration in PL e.g. Embedded SQL ESQL/C,java integration: SQLJ Object relational mapping: hiding data access andpersistence from application code. HS-201008-PLSQLetc-30
8.3 Stored proceduresServer extension by user defined functionsSQL based: PL/SQL (Oracle), PL/pgSQL adds control structures to SQL easy way to define complex functions on the DBProgramming language basedC, Java, .,Perl, Python, Tcl for PostgresAny Programming language suitable in principle HS-201008-PLSQLetc-31
SQL standardsDB-Script languagesBased on SQL/PSM ("persistent stored modules") standardOnly propriatary implementations: PL/SQL (Oracle),PL/pgSQL (Postgres), Transact-SQL (Micorsoft), SQL procedurelanguage (IBM)But conceptually similarProgramming language basedSQL/OLB (object language binding)SQL/JRT (SQL routines and types using the Java language)SQL/CLI (SQL call level interface): How to call SQL fromProgramming language. HS-201008-PLSQLetc-32
DB script languages basics: BlocksSyntax[DECLARE/* Declarative section: variables, types, and local subprograms. */ ]BEGIN/* Executable section: procedural and SQL statements go here. *//* This is the only section of the block that is required. */[EXCEPTION/* Exception handling section: error handling statements go here. */ ]END;Block: Scope as in programming languages,nesting allowed. HS-201008-PLSQLetc-33
Usage Blocks used for direct excecution (e.g. SQL )(only for testing and some administrative tasks) Used within programs. e.g. CEXEC SQL EXECUTE Block Definition of independent functions / functionsCREATE PROCDURE ( ) IS For definition of triggers Inside object / type declarationsCREATE TYPE BODY HS-2010Type definitions: seebelow08-PLSQLetc-34
DeclarationsStandard ;Use table typesAll variables haveto be declared,all SQL typesallowed.tableDECLAREprodName Product.name%TYPE;Use row typeDECLAREcolumnproductTuple Product%ROWTYPE;This is a record type HS-201008-PLSQLetc-35
Record typesExamplePL/SQL syntaxDECLARE countryRec Country%ROWTYPE;BEGINSELECT * INTO countryRec FROM Country WHERE CODE 'D';dbms output.PUT LINE('Name: ' countryRec.name);END;Library function (Oracle) May be executed from the command line Works only with exactly one result row How to iterate over result sets? HS-201008-PLSQLetc-36
PL/SQL Control flowCREATE TABLE TNumb(x NUMBER, y NUMBER);DECLAREi NUMBER : 1;BEGINLOOPINSERT INTO T1 VALUES(i,i 1);i : i 1;EXIT WHEN i 100;END LOOP;END;Only SQL/DMLwithin blockSimilar : WHILE ( condition ) LOOP . END LOOPFOR var IN start . finish LOOP.END LOOPsee Manual HS-201008-PLSQLetc-37
PL/SQL Insertion in FOR loopCREATE TABLE TestNormal (empno number(10), enamevarchar2(30), sal number(10));BEGINFOR i in 1.1000000LibraryLOOPINSERT INTO Test normalVALUES (i, dbms random.string('U',80),dbms random.value(1000,7000));IF mod(i, 10000) 0 THENCOMMIT;Transaction commit: insertedEND IF;data stored in DB now.END LOOP;All or nothing semantics.END; HS-2010function08-PLSQLetc-38
Result setsProblem: how to process result set of unkown cardinality?DECLARE countryRec Country%ROWTYPE;BEGINSELECT * INTO countryRec FROM Country WHERE CODE 'D%';dbms output.PUT LINE('Name: ' countryRec.name);END;.does not work – more than one result record expected.Needed: a kind of pointer to result set records, which allowsto iterate through the result set. HS-201008-PLSQLetc-39
Result set: exampleCursor, internal object,DECLAREnot a variableCURSOR ctry ISSELECT * FROM Country WHERE CODE LIKE 'D%';countryRec Country%ROWTYPE;has few operations:BEGINOPEN, CLOSE, FETCHOPEN ctry;LOOPand attributes:%NOTFOUND,FETCH ctry INTO countryRec;%OPEN,EXIT WHEN ctry%NOTFOUND;%ROWCOUNT et aldbms output.PUT LINE('Name: ' countryRec.name ', Popul: ' countryRec.population);END LOOP;CLOSE ctry;END; HS-201008-PLSQLetc-40
Cursor (*)Def: A cursor is an abstraction of a result set for a particularSQL statement with operations: OPEN, FETCH, CLOSEand attributes %ROWCOUNT, %FOUND, %NOTFOUND Explicit cursors have to be defined for SQL statementswith more than one result record Implicit cursors are defined for every SQL statementBEGINDELETE FROM TNUMB WHERE x 50;DBMS OUTPUT.PUT LINE('Deleted rows: ' SQL%ROWCOUNT);END;(*) Important concept for embedding SQL in host (programming) languages,typically more operations, see JDBC below HS-201008-PLSQLetc-41
Cursors and FOR loopsDECLARECURSOR ctry ISSELECT * FROM Country WHERE CODE LIKE 'C%';row# int;LOOP is part ofBEGINFOR loop onFOR resRecord IN ctry LOOPresult set of implicitrow# : ctry%ROWCOUNT;cursor.dbms output.PUT LINE('Name: ' resRecord.name ', Popul: ' resRecord.population);END LOOP;dbms output.PUT LINE('Number of countries: ' row#);END; Implicit: open, close, record variable of result record. Cursor closed at END LOOP, no attributes definedafter that point. HS-201008-PLSQLetc-42
Collection variablesDECLARETABLE variables allowTYPE largeCtry IS RECORD (for manipulationname country.name%TYPE,of sets within a blockcapital country.capital%TYPE);TYPE largeCtryTab IS TABLE OF largeCtry;lTab largeCtryTab;Bulk load from DBi int;or individualBEGINassignementSELECT name, capital BULK COLLECT INTO lTabFROM country WHERE population 100000000;Set operations in DBFOR i IN 1.lTab.LAST LOOPusually preferrabledbms output.PUT LINE('Name: ' lTab(i).name ', capital: ' lTab(i).capital);END LOOP;END; HS-201008-PLSQLetc-43
8.4 Functions and proceduresRecall.BrowserWebserver:- interpret request- call stored procedure- return htmlDatabasewith businesslogic asstored proceduresNeeded: procedures and functions , not justanonymous blocks Major syntactic (and some semantic) differencesbetween PL/SQL and PL/pgSQL e.g. no procedure in PL/pgSQL but FUNCTION RETURNS VOID HS-201008-PLSQLetc-44
PL/SQL proceduresCREATE PROCEDURE addtuple2 ( x IN T2.a%TYPE,y IN T2.b%TYPE)ASNo DECLARE (!)i NUMBER dbms random.value(1000,7000)-- here go declarationsBEGININSERT INTO T2(k NUMBER,a, b)VALUES(i, x, y);END addtuple2;Parameter passing like in ADA: call by value (IN), call by result (OUT), call by value-result (INOUT)Why no call by reference? HS-201008-PLSQLetc-45
Functions in PL/SQLCREATE FUNCTION CountryCity(cname IN VARCHAR)RETURNS intISCURSOR ctry ISSELECT * FROM Country WHERE CODE LIKE cname '%';row# int;BEGINFOR resRecord IN ctry LOOProw# : ctry%ROWCOUNT;dbms output.PUT LINE('Name: ' resRecord.name ', Capital: ' resRecord.capital);END LOOP;RETURN (row#);END; HS-201008-PLSQLetc-46
Calling functions / procedures Embedded in host language like C, Javasimilar to execution of plain SQL below Big difference: no result set, but usage of INOUT, OUTparameters and function values Inside PL/SQL blockBEGINdbms output.Put Line('Number of countries: ' TO CHAR(CountryCity('G')));END; Postgres: Server Programming interface (SPI) HS-201008-PLSQLetc-47
PackagesPL/SQL packages:define API and its implementation for relatedfunctions and proceduresThe API forCREATE PACKAGE MyMondial ASthis packageTYPE myCity City%ROWTYPE;Cursor myC RETURNS myCity;FUNCTION BigCites(countryName VARCHAR) RETURN NUMBER;PROCEDURE NewCityInsert(newC myCity);END MyMondial;CREATE PACKAGE BODY MyMondial ASImplementationmyVar NUMBER; -- local to package!CURSOR myC AS SELECT * FROM City WHERE. –-full def.FUNCTION BigCities(.)AS . –- full definitionPROCEDURE NewCityInsert(newC myCity) AS.; --full def.BEGIN .-- initializationsEND MyMondial HS-201008-PLSQLetc-48
PL/SQL: etcException handlingEXCEPTIONWHEN exceptionname [OR ]THEN SQL / PL/SQL – statement sequence ;WHEN OTHERSTHEN SQL /PL/SQL – statement sequence Flexible concept comparable with Java exceptions.Different semantics for special situations.(see manual) HS-201008-PLSQLetc-49
Realistic PL/SQL (Oracle) example-- very simple purchase transactionCREATE PROCEDURE Purchase() ASqty on hand NUMBER(5);BEGINSELECT quantity INTO qty on hand FROM inventoryWHERE product 'TENNIS RACKET' -FOR UPDATE OF quantity;IF qty on hand 0 THEN -- check quantityUPDATE inventory SET quantity quantity - 1WHERE product 'TENNIS RACKET';INSERT INTO purchase recordVALUES ('Tennis racket purchased', SYSDATE);ELSEINSERT INTO purchase recordVALUES ('Out of tennis rackets', SYSDATE);END IF;COMMIT;END;/ HS-201008-PLSQLetc-50
PL/pgSQL in a nutshellExampleCREATE FUNCTION foo (acc integer, amount numeric) RETURNSnumeric AS B UPDATE bank SET balance balance - amountWHERE accountno acc;SELECT balance FROM bank WHERE accountno acc; B LANGUAGE SQL; quoting of PG- Many SQL-statements in one call: performance gain- value returned: first row of last query result- Compound result type and table valued functions allowed Table valued function in FROM clause HS-201008-PLSQLetc-51
SQL based functionsTable result typesCREATE FUNCTION getfoo(integer) RETURNS SETOF movie AS SELECT * FROM movieWHERE m id 1; LANGUAGE SQL;placeholder for parametersSELECT title, director FROM getfoo(93) AS m1;Alias for returned table value HS-201008-PLSQLetc-52
PL/pgSQL in a nutshellExampleCREATE OR REPLACE FUNCTION rand (hi integer,low int4)RETURNS integer AS BODY -- no DECLAREHere go the variableBEGINdeclarationsRETURN low ceil((hi-low) * random());END; BODY LANGUAGE 'plpgsql' VOLATILE;Standard functions: -quote, useful forstring literals HS-2010random() returnsuniformly distributedvalues 0 v 1.0Function may not return the samevalue for same argument:hint for optimization08-PLSQLetc-53
PL/pgSQL in a nutshellCREATE OR REPLACE FUNCTION video.randtab(count integer,low integer, hi integer)RETURNS integer AS BODY variable declarationsDECLARE c INTEGER : 0;r INTEGER;BEGINCREATE TABLE randomTable (numb integer, randValinteger);FOR i IN 1.countLOOPside effects!INSERT INTO randomTable VALUES(i, rand(low,hi));END LOOP;RETURN (SELECT MAX(numb) FROM randomTable);END; BODY LANGUAGE 'plpgsql' VOLATILE; HS-201008-PLSQLetc-54
PL/pgSQL in a nutshellEvaluation of functionsWithin a select statement:SELECT randtab(100,0,9)Without result valuePERFORM my function(args)EXECUTE query planEXECUTE PROCEDURE emp stamp();Note: Functions may have side effects!No (pretty) PRINT facilitiesworkarounds: SELECT 'This is my heading'- put PLSQL-call into shell script- use Programming language for I/O HS-201008-PLSQLetc-56
8.5 TriggersTriggers: Event – Condition – Action rulesEvent:Update, insert, delete (basically)Condition: WHEN some conditon on table Action:some operation ( expressed as DML, DB- Script languageexpression, C, Java, )Triggers make data base systems pro-activecompared to re-active (and interactive) HS-201008-PLSQLetc-57
Triggers: simple exampleBasic FunctionalityCREATE TRIGGER myTriggerBEFORE [AFTER] eventON TABLE myTable FOR EACH ROW{ STATEMENT}EXECUTE PROCEDURE myFunction(myArgs);event: UPDATE, INSERT, DELETESemanticsExecute the function after each eventonce for each row changed or once per statemente.g. per statement: write log-recordper row: write new time-stamp HS-201008-PLSQLetc-58
Anatomy of a trigger (Oracle)CREATE OR REPLACE TRIGGER movie DVD TriggerINSTEAD OF INSERT ON T MFOR EACH ROWSemantics:Action(here:PL/SQL) HS-2010DECLARE m row NUMBER;-- local variableBEGINSELECT COUNT(*) INTO m rowFROM MovieWHERE m id :NEW.mid;trigger foreach row affected(not only once perexcecuted statement)IF m row 0THEN RAISE APPLICATION ERROR(-20300, 'Movie does not exist');ELSE INSERT INTO DVD (DVD id, m id) VALUES (:NEW.DVD id,:NEW.mid);END IF;End;CREATE view T MAS SELECT m.m Id AS mid, DVD id, title.08-PLSQLetc-59
Using an INSTEAD OF TRIGGERWithout the trigger:Insert into T M (mid, DVD id) VALUES(93,14);*FEHLER in Zeile 1:ORA-01779: Kann keine Spalte, die einer Basistabelle zugeordnetwird, verändernUsing the INSTEAD OF TRIGGERInsert into T M (mid, DVD id) VALUES(93,14)1 Zeile eingefügtInsert into T M (mid, DVD id) VALUES(99,14)*FEHLER in Zeile 1:ORA-20300: Movie does not existORA-06512: in "VIDEODB.MOVIE DVD TRIGGER", Zeile 8ORA-04088: Fehler bei der Ausführung von Trigger'VIDEODB.MOVIE DVD TRIGGER' HS-201008-PLSQLetc-60
Triggers. are a powerful DB programming conceptAllow complex integrity constraintsUsed in most real-life database applicationsSometimes dangerous:CREATE TRIGGER myTrigger1BEFORE INSERTON TABLE myTable1 EXCECUTE myfct (.)-- inserts some record into myTable2CREATE TRIGGER myTrigger2BEFORE INSERTON TABLE myTable2 EXCECUTE myfct (.)-- inserts some record into myTable1Cycle! HS-201008-PLSQLetc-61
8.6 SQL3: Abstract data types"ADT is a data type defined by the operations allowed on itsvalues"CREATE TYPE name ( list of component attributes declaration of EQUAL, LESS declaration of more methods )supported only by a few DBSADT equivalent to 'object type' (Oracle). or functions may be defined stand-alone (PG) HS-201008-PLSQLetc-62
Functions, methods, proceduresMethod interface in an object type definition(Oracle flavor)CREATE TYPE LineType AS OBJECT( end1 PointType,end2 PointType,MEMBER FUNCTION length(scale IN NUMBER) RETURNNUMBER,PRAGMA RESTRICT REFERENCES(length, WNDS));CREATE TABLE Lines ( lineID INT, line LineType );Predicates defined over functionsSELECT lineID, k.length (1.0) FROM Lines kWHERE k.length(1.0) 8.0 HS-201008-PLSQLetc-63
Defining methods (Oracle)Implementation of a method signature*CREATE TYPE BODY LineType ASMEMBER FUNCTION length(scale NUMBER) RETURN NUMBER ISBEGINRETURN scale * d2.x) (SELF.end1.y-SELF.end2.y)*(SELF.end1.ySELF.end2.y) );END;END;Methods may be defined in Java or PL/SQL (Oracle)Functions: independent of types, no SELF attribute*compare: java interface vs. classsee: Ullman, J.: Object-Relational Features of Oraclehttp://www-db.stanford.edu/ ullman/fcdb/oracle/or-objects.html HS-201008-PLSQLetc-64
Summary Extensions of relational model popular SQL 3 keeps extensions under control – somehow Object-relational extensions more important thanobject oriented database systems Extensions basically are:structured types and set typesfunctions, written in a db script language orsome programming languageactive elements: triggers (SQL 3) , rules (only PGres) HS-201008-PLSQLetc-70
Only propriatary implementations: PL/SQL (Oracle), PL/pgSQL (Postgres), Transact-SQL (Micorsoft), SQL procedure language (IBM) But conceptually similar Programming language based SQL/OLB (object language binding) SQL/JRT (SQL routines and types using the Java language) SQL/CLI (SQL call level interface): How to call SQL from Programming language.
