Oracle:SQL Variations and Extensions

By -

SQL Variations and Extensions

Oracle9i supports all core SQL:1999 features fully or partially, with some minor exceptions such as distinct data types. In addition, Oracle supports a large number of other language constructs, some of which conform with SQL:1999, while others are Oracle-specific in syntax or functionality. For example, Oracle supports the OLAP operations described in Section 22.2, including ranking, moving aggregation, cube, and rollup.

A few examples of Oracle SQL extensions are:

• connect by, which is a form of tree traversal that allows transitive closure- style calculations in a single SQL statement. It is an Oracle-specific syntax for a feature that Oracle has had since the 1980s.

• Upsert and multitable inserts. The upsert operation combines update and insert, and is useful for merging new data with old data in data warehousing applications. If a new row has the same key value as an old row, the old row is updated (for example by adding the measure values from the new row), otherwise the new row is inserted into the table. Multitable inserts allow multiple tables to be updated based on a single scan of new data.

• with clause, which is described in Section 4.8.2.

Object-Relational Features

Oracle has extensive support for object-relational constructs, including:

• Object types. A single-inheritance model is supported for type hierarchies.

• Collection types. Oracle supports varrays which are variable length arrays, and nested tables.

• Object tables. These are used to store objects while providing a relational view of the attributes of the objects.

• Table functions. These are functions that produce sets of rows as output, and can be used in the from clause of a query. Table functions in Oracle can be nested. If a table function is used to express some form of data transformation, nesting multiple functions allows multiple transformations to be expressed in a single statement.

• Object views. These provide a virtual object table view of data stored in a regular relational table. They allow data to be accessed or viewed in an object- oriented style even if the data are really stored in a traditional relational for- mat.

• Methods. These can be written in PL/SQL, Java, or C.

• User-defined aggregate functions. These can be used in SQL statements in the same way as built-in functions such as sum and count.

• XML data types. These can be used to store and index XML documents.

Oracle has two main procedural languages, PL/SQL and Java. PL/SQL was Oracle’s original language for stored procedures and it has syntax similar to that used in the Ada language. Java is supported through a Java virtual machine inside the database engine. Oracle provides a package to encapsulate related procedures, functions, and variables into single units. Oracle supports SQLJ (SQL embedded in Java) and JDBC, and provides a tool to generate Java class definitions corresponding to user-defined database types.

Triggers

Oracle provides several types of triggers and several options for when and how they are invoked. (See Section 6.4 for an introduction to triggers in SQL.) Triggers can be written in PL/SQL or Java or as C callouts.

For triggers that execute on DML statements such as insert, update, and delete, Oracle supports row triggers and statement triggers. Row triggers execute once for every row that is affected (updated or deleted, for example) by the DML operation. A statement trigger is executed just once per statement. In each case, the trigger can be defined as either a before or after trigger, depending on whether it is to be invoked before or after the DML operation is carried out.

Oracle allows the creation of instead of triggers for views that cannot be subject to DML operations. Depending on the view definition, it may not be possible for Oracle to translate a DML statement on a view to modifications of the underlying base tables unambiguously. Hence, DML operations on views are subject to numerous restrictions. A user can create an instead of trigger on a view to specify manually what operations on the base tables are to occur in response to the DML operation on the view. Oracle executes the trigger instead of the DML operation and therefore pro- vides a mechanism to circumvent the restrictions on DML operations against views.

Oracle also has triggers that execute on a variety of other events, like database startup or shutdown, server error messages, user logon or logoff, and DDL statements such as create, alter and drop statements.

Comments

Post a Comment

Popular posts from this blog

Database System Architectures:Parallel Systems.

By -
Image
Parallel Systems Parallel systems improve processing and I/O speeds by using multiple CPUs and disks in parallel. Parallel machines are becoming increasingly common, making the study of parallel database systems correspondingly more important. The driving force behind parallel database systems is the demands of applications that have to query extremely large databases (of the order of terabytes — that is, 1012 bytes) or that have to process an extremely large number of transactions per second (of the or- der of thousands of transactions per second). Centralized and client – server database systems are not powerful enough to handle such applications. In parallel processing, many operations are performed simultaneously, as opposed to serial processing, in which the computational steps are performed sequentially. A coarse-grai n parallel machine consists of a small number of powerful processors; a massivel y parallel or fine-grai n parallel machine uses thousands of smaller processors....
Read more

DATABASE DESIGN -2 part2

By -
Image
Multi-valued Dependencies and Fourth Normal Form (a) The EMP relation with two MVDs: ENAME —>> PNAME and ENAME —>> DNAME. (b) Decomposing the EMP relation into two 4NF relations EMP_PROJECTS and EMP_DEPENDENTS. (c) The relation SUPPLY with no MVDs is in 4NF but not in 5NF if it has the JD(R1, R2, R3). (d) Decomposing the relation SUPPLY into the 5NF relations R1, R2, and R3.   Multi-valued Dependencies and Fourth Normal Form Definition: A multi-valued dependency (MVD) X —>> Y specified on relation schema R, where X and Y are both subsets of R, specifies the following constraint on any relation state r of R: If two tuples t1 and t2 exist in r such that t1[X] = t2[X], then two tuples t3 and t4 should also exist in r with the following properties, where we use Z to denote (R - (X υ Y)): t3[X] = t4[X] = t1[X] = t2[X]. t3[Y] = t1[Y] and t4[Y] = t2[Y]. t3[Z] = t2[Z] and t4[Z] = t1[Z]. An MVD X —>> Y in R is called a trivial MVD if (a) Y is a subset...
Read more

Database System Architectures:Network Types

By -
Image
Network Types Distributed databases and client – server systems are built around communication networks. There are basically two types of networks: local-are a networks and wide- are a networks . The main difference between the two is the way in which they are distributed geographically. In local-area networks, processors are distributed over small geographical areas, such as a single building or a number of adjacent buildings. In wide-area networks, on the other hand, a number of autonomous processors are distributed over a large geographical area (such as the United States or the en- tire world). These differences imply major variations in the speed and reliability of the communication network, and are reflected in the distributed operating-system design. Local-Area Networks Local-are a network s ( LAN s) (Figure 18.10) emerged in the early 1970s as a way for computers to communicate and to share data with one another. People recognized that, for many enterprises, numerous small...
Read more