5.2 DML Command Syntax

 < Day Day Up > 



The first thing to be done is to make some changes and expand the Employees schema as shown in Figure 5.2. The original is shown in Figure 2.10 (p. 73).

click to expand
Figure 5.2: The Employees Schema

Now I am going to describe the syntax for all of the Oracle SQL DML statements with explanatory examples of each using the schema in Figure 5.2.

5.2.1 The SELECT Statement

Here is a simplistic form of the Oracle SQL syntax for the SELECT statement.

SELECT { [alias.]column | expression | [alias.]* [ , ¼ ] } FROM [schema.]table [alias] [ WHERE [ [schema.]table.|alias.] { column | expression }    comparison { ¼ }        [ { AND | OR } [ NOT ] ¼ ] ] [ GROUP BY      { expression | rollup-cube | grouping-sets }       [, { expression | rollup-cube | grouping-sets } ¼ ]    [ HAVING condition ] ] [ ORDER BY    { { column | expression | position }       [ ASC | DESC ] [ NULLS { FIRST | LAST } ]    [, ¼ ] } ] ;     rollup-cube: ROLLUP | CUBE ( expression [, expression ¼ ] ) GROUPING SETS       ( rollup-cube | expression [, expression ¼ ])

The WHERE clause is used to filter out or remove unwanted rows. The GROUP BY clause is used to generate summary rows of sets of rows and the ORDER BY clause is used to sort the returned rows. ROLLUP, CUBE, and GROUPING SETS clauses are extensions to the GROUP BY clause.

Logical Operators

Logical operators allow for Boolean logic in WHERE clause filtering and various other places. Mathematically the sequence of precedence is NOT, followed by AND, and finally OR. Precedence can be altered using parentheses.

This first query finds the CEO and the second employee. The CEO has no manager and thus his Employee.MANAGER_ID is NULL.

SELECT * FROM employee WHERE employee_id = 2 OR manager_id    IS NULL;

This query returns no rows because EMPLOYEE_ID is unique and the MANAGER_ID of that employee is not NULL, that person is not the CEO.

SELECT * FROM employee WHERE employee_id = 2 AND manager_id    IS NULL;

This query returns a spurious result because precedence of executing AND in conjunction with OR is not implemented using parentheses.

SELECT employee_id, manager_id FROM employee WHERE manager_id IS NULL AND employee_id = 1 OR employee_id = 2;

This query enforces precedence using parentheses.

SELECT employee_id, manager_id FROM employee WHERE manager_id IS NULL AND (employee_id = 1 OR    employee_id = 2);

Comparison Conditions

Comparison conditions allow for different methods of comparison between expressions.

  • expression { [!] = | > | < | >= | <= } expression

    SELECT * FROM projecttype WHERE projecttype_id = 1; SELECT * FROM projecttype WHERE projecttype_id != 1; SELECT * FROM projecttype WHERE projecttype_id < 5; SELECT * FROM projecttype WHERE projecttype_id >= 8;
  • expression [ NOT ] LIKE expression LIKE is a pattern matcher.

    SELECT name FROM projecttype WHERE name LIKE '%a%'; SELECT name FROM projecttype WHERE name LIKE 'R%'; SELECT name FROM projecttype WHERE name LIKE 'R%' OR    name LIKE 'S%'; SELECT name FROM projecttype WHERE name LIKE 'R%' AND    name LIKE '%a%';
  • expression [ NOT ] IN expression IN is used to check for set inclusion.

    SELECT projecttype_id FROM projecttype    WHERE projecttype_id IN (1,2,3,4,5); SELECT projecttype_id FROM projecttype    WHERE projecttype_id NOT IN (1,2,3,4,5);
  • [ NOT ] EXISTS (subquery) EXISTS checks for "existence of" giving a Boolean, TRUE or FALSE result.

    SELECT projecttype_id FROM projecttype       WHERE EXISTS (SELECT * FROM DUAL); SELECT p.projecttype_id FROM projecttype p       WHERE EXISTS          (SELECT projecttype_id FROM projecttype             WHERE projecttype_id < 10                AND projecttype_id = p.projecttype_id);     SELECT p.projecttype_id FROM projecttype p       WHERE EXISTS          (SELECT project_id FROM project             WHERE projecttype_id = p.projecttype_id);
  • expression [ NOT ] BETWEEN expression AND expression BETWEEN does a range check much the same as x >= 1 and x <= 10.

    SELECT projecttype_id FROM projecttype WHERE projecttype_id BETWEEN 1 AND 5;
  • expression [ = | != | > | < | >= | <= ] [ ANY | SOME | ALL ] expression

ANY and SOME verify if any items match and ALL verifies if all items match.

SELECT name FROM projecttype WHERE projecttype_id = ANY (SELECT projecttype_id FROM    project); SELECT name FROM projecttype WHERE projecttype_id = SOME (SELECT projecttype_id FROM    project);

The two queries following will produce no rows since every item must match or not match, respectively.

SELECT name FROM projecttype WHERE projecttype_id = ALL (SELECT projecttype_id FROM    project); SELECT name FROM projecttype WHERE projecttype_id != ALL (SELECT projecttype_id FROM    project);

Types of SELECT Statements

There are numerous ways in which the SELECT statement can be used. These different methods are best shown by example.

Simple query

SELECT * FROM projecttype;

Filtering Queries using the WHERE Clause

SELECT * FROM projecttype WHERE name LIKE 'R%';

Sorting Queries using the ORDER BY Clause

SELECT * FROM projecttype ORDER BY name;

Joining Tables

The ANSI format has been introduced in order to comply with ANSI standards. Tuning ANSI standard format SQL join statements is an issue that will be covered in Chapter 6.

The syntax of the Oracle SQL proprietary join format is as shown.

SELECT { [ [schema.]table. | [alias.] ]    { column | expression [, ... ] } | * }  FROM [schema.]table [alias] [, …] [ WHERE     [ [schema.]table.|alias.] { column | expression [(+)] }       comparison condition    [ [schema.]table.|alias.] { column | expression [(+)] }    [ { AND | OR } [ NOT ] … ] ] [ GROUP BY … ] [ ORDER BY … ];

Note in the syntax diagram above that the (+) or outer join operator is always placed on the side deficient in information.

SELECT di.name, de.name FROM division di, department de WHERE di.division_id = de.division_id(+);

The Department table is the table deficient in information and thus rows in the Divisions table without entries in the Department table will be included in the returned result as a left outer join. The term left outer join implies that if the table on the left (the first table in the FROM clause above) contains rows not in the table on the right (the second table in the FROM clause) then rows from the first table will be returned in addition to the intersection.

This next syntax diagram shows the syntax for the ANSI standard join format.

SELECT {{[[schema.]table.|alias.] {column|expression} [, ¼    ]}|*} FROM [schema.]table [alias] [       CROSS JOIN [schema.]table [alias]    | NATURAL [INNER | [ LEFT | RIGHT | FULL] OUTER]          JOIN [schema.]table [alias]    |{       [INNER | [LEFT | RIGHT | FULL] OUTER] JOIN          [schema.]table [alias]       {              ON (column = column [{AND | OR} [NOT] column =                 column … ])          | USING (column [, column … ])        }    } ] [ WHERE … ] [ GROUP BY … ] [ ORDER BY … ];

The query following is the equivalent ANSI standard syntax for the Oracle SQL proprietary format of the left outer join query shown above.

SELECT di.name, de.name FROM division di LEFT OUTER JOIN department de USING    (division_id);

Types of Joins

  • Cross Join. A cross join is a merge of all rows in both tables where each row in one table is matched with every other row in the second table. A cross join is a Cartesian product.

    SELECT * FROM division, managerof;
  • Inner or Natural Join. An inner join is an intersection between two tables, joining based on a column or column names.

    SELECT * FROM division NATURAL JOIN managerof;
  • Outer Join. An outer join joins rows from two tables. Rows joined are those both in the intersection plus rows in either or both tables, and not in the other table. It is important to remember that for an outer join rows found in one table and not in the other have their columns replaced with NULL values in the joined rows.

    • Left Outer Join. A left outer join joins all intersecting rows plus rows only in the left table.

      SELECT * FROM division NATURAL LEFT OUTER JOIN managerof;
    • Right Outer Join. A right outer join is the opposite of the left outer join: the intersection plus all rows in the right table only.

      SELECT * FROM division NATURAL RIGHT OUTER JOIN    managerof;
    • Full Outer Join. A full outer join retrieves all rows from both tables.

      SELECT * FROM division NATURAL FULL OUTER JOIN managerof;
  • Self Join. A self join joins a table to itself.

    SELECT manager.name, employee.name FROM employee manager JOIN employee employee    ON (employee.manager_id = manager.employee_id);
  • Equi/Anti/Range Joins. These joins use the appropriate comparison conditions to join rows in tables.

    SELECT * FROM division di JOIN department de    ON (di.division_id = de.division_id);
  • Mutable and Complex Joins. A mutable join is a join of two or more tables and a complex join is a mutable join with extra filtering.

    SELECT di.name, de.name, prj.name FROM division di    JOIN department de ON(di.division_id =        de.division_id)    JOIN project prj ON(de.department_id =       prj.department_id) WHERE di.division_id = 5;

Subqueries

There are specific types of subqueries:

  • Single Row Subquery. A single row subquery returns a single row. Some comparison conditions require a single row with a single column.

    SELECT * FROM project WHERE projecttype_id =    (SELECT projecttype_id FROM projecttype       WHERE projecttype_id = 1);
  • Multiple Row Subquery. A multiple row subquery returns one or more rows.

    SELECT project_id FROM project WHERE projecttype_id IN    (SELECT projecttype_id FROM projecttype);
  • Multiple Column Subquery. A multiple column subquery returns many columns.

    SELECT COUNT(*) FROM(SELECT * FROM project);
  • Regular Subquery. A regular subquery executes a subquery in its entirety with no communication with the calling query.

    SELECT * FROM department WHERE division_id IN    (SELECT division_id FROM division);
  • Correlated Subquery. A correlated subquery can use a value passed from a calling query as a parameter to filter specific rows in the subquery. Values can only be passed from calling to subquery and not the other way around.

    SELECT * FROM division WHERE EXISTS    (SELECT division_id FROM department       WHERE division_id = division.division_id);

There are numerous places where subqueries can be used:

  • SELECT clause

    SELECT p.project_id,    (SELECT projecttype_id FROM projecttype    WHERE projecttype_id = p.projecttype_id) FROM project p;
  • WHERE clause (filtering)

    SELECT project_id FROM project WHERE projecttype_id IN    (SELECT projecttype_id FROM projecttype); SELECT division_id FROM division WHERE division_id IN    (SELECT division_id FROM department   WHERE department_id IN    (SELECT department_id FROM department));
  • ORDER BY clause (sorting)

    SELECT name FROM projecttype ORDER BY (SELECT name FROM DUAL);
  • HAVING clause (filtering groups)

    SELECT name, COUNT(name) FROM projecttype GROUP BY name HAVING name = ANY (SELECT name FROM projecttype);
  • FROM clause

    SELECT COUNT(*) FROM (    SELECT di.name, de.name, pr.name    FROM division di    JOIN department de ON (di.division_id =        de.division_id)    JOIN project pr ON (de.department_id =        pr.department_id)); 
  • INSERT statement VALUES clause

    INSERT INTO projecttype(projecttype_id, name) VALUES((SELECT MAX(projecttype_id)+1 FROM projecttype)    ,'A new project type');
  • UPDATE statement SET = (subquery)

    UPDATE department SET name = (SELECT name FROM    division       WHERE division_id = department.division_id);
  • CASE statement expression

This is PL/SQL and PL/SQL syntax is not part of tuning Oracle SQL code.

  • A function parameter

    SELECT LPAD('xxx',10,(SELECT * FROM DUAL)) FROM DUAL;

Table and View Creation

This example uses a subquery to create a new table.

CREATE TABLE namesTable AS    SELECT di.name AS Division, de.name AS Department,       pr.name AS Project    FROM division di    JOIN department de ON (di.division_id = de.division_id)       JOIN project pr ON (de.department_id =           pr.department_id);

This example uses a subquery to create a new view.

CREATE VIEW namesView AS    SELECT di.name AS Division, de.name AS Department,          pr.name AS Project    FROM division di    JOIN department de ON (di.division_id = de.division_id)          JOIN project pr ON (de.department_id =              pr.department_id);

Hierarchical Query

A hierarchical query allows display of hierarchical data in a single table.

SELECT name, employee_id, manager_id FROM employee    CONNECT BY PRIOR employee_id = manager_id; 

Set Operators and Composite Queries

Set operators are used to combine two separate queries into a composite query. Both queries must have the same data types for each column.

  • UNION ALL retrieves all rows from both queries including duplicates. Duplicate rows are rows returned by both queries.

  • UNION is as UNION ALL but duplicate rows are only returned once.

  • INTERSECT returns distinct rows from both queries. An intersection is a little like an inner join.

  • MINUS returns one query less than the other, a little like a left outer join where only distinct rows in the first query are returned.

In order to demonstrate a sensible use of composite queries Figure 5.3 shows a split of the Employees schema Employee table into two tables.

click to expand
Figure 5.3: Separating the Employee Table into Manager and Employee

Using the two Manager and Employee tables we can use a set operator to join the two tables together into a composite query.

SELECT manager_id, name FROM manager UNION ALL SELECT employee_id, name FROM employee;

This query will not work because the sequence of column data types is different between the two queries.

SELECT name, manager_id FROM manager UNION ALL SELECT employee_id, name FROM employee;

Flashback

Flashback queries are not particularly relevant to tuning but important enough to include. Flashback simply returns rows at a particular point in time using a system change number (SCN) or a timestamp.

SELECT ¼ AS OF { SCN | TIMESTAMP } expression

Flashback Versions Queries

 Oracle Database 10 Grid   Flashback queries are improved in Oracle Database 10g such that they can be used to return multiple versions of a query. A flashback versions query can return multiple iterations of the same rows within a period of time.

SELECT ¼ [ VERSIONS BETWEEN { SCN | TIMESTAMP }    { expression | MINVALUE } AND { expression | MAXVALUE } AS OF { SCN | TIMESTAMP } expression

Flashback Database

 Oracle Database 10 Grid   This has little to do with tuning apart from the fact that it might make restoration of a previous database state much easier and faster. The FLASHBACK DATABASE command allows database recovery by allowing the undoing of all changes back to an SCN or timestamp.

Using DISTINCT

DISTINCT retrieves the first value from a repeating group. When there are multiple repeating groups DISTINCT will retrieve the first row from each repeating group. DISTINCT can operate on single or multiple columns.

SELECT COUNT ( [ DISTINCT | ALL ] expression ) ... SELECT DISTINCT column [, column ... ] ... SELECT DISTINCT (column [, column ... ]) ...

The DUAL Table

Every DML statement creates an implicit cursor. A cursor is an area in memory allocated for the results of an SQL statement. SELECT statements require a source table for the implicit cursor to operate on. Some SELECT statements do not retrieve from any particular table and thus the DUAL table is a repository for an expression result applied to a single value. The DUAL table acts as a temporary repository for the result of an expression. The result of the expression is selected from the DUAL table.

SELECT * FROM DUAL; SELECT a.*, b.* FROM DUAL a, projecttype b; SELECT 'This is a string' FROM DUAL;

The DUAL table also can be used to retrieve Oracle Database constants in a SELECT statement where a constant value is applied to every row.

SELECT SYSDATE FROM DUAL; SELECT USER FROM DUAL;

NULLs

There are a number of things to remember about NULL:

  • NULL represents nothing.

  • Not even a space character is NULL.

  • NULL is not the same as zero.

  • NULL values are not indexed.

  • Most functions return NULL when passed NULL.

  • Test for NULL using IS [ NOT ] NULL.

  • An expression containing a NULL returns NULL.

  • Use NVL (value, replace) to account for NULL values in expressions.

  • NULL values sort as the highest value.

Pseudocolumns

A pseudocolumn is a virtual column producing a specific value when used in DML. In actuality a pseudocolumn is an expression calculator or a window into some part of the Oracle database.

  • Sequences.

    • CURRVAL (sequence.CURRVAL).

    • NEXTVAL (sequence.NEXTVAL).

  • ROWID provides the fastest access method but can change since it is a relative address. A ROWID is unique across a table and used as the pointer in an index, pointing to a table row. A ROWID is made up of:

    • A tablespace identifier.

    • A datafile block.

    • A data block row.

    • A tablespace datafile number.

  • ROWNUM is an order sequence of number of rows returned by a SELECT statement.

  • LEVEL provides a hierarchical level number in a hierarchical query.

  • XMLDATA allows access to XMLTYPE datatypes.

Using Functions

Functions can be used in most parts of different DML statements. Using functions can affect performance. Functions can be divided into the following general categories:

  • Single row functions

  • Datatype conversion functions

  • Group functions

  • Object reference functions

  • User defined functions

5.2.2 The INSERT Statement

There are two forms of the INSERT statement, which can be used to add rows to a single table or multiple tables. There are various syntax options.

INSERT INTO [schema.]table [ ( column [, column …] ) ] VALUES ( { expression | DEFAULT | (subquery) }          [, { expression | DEFAULT | (subquery) } ... ] ) [ RETURNING expression [, expression ] INTO variable    [, variable ] ];

The specified columns are optional if the VALUES clause specifies columns in existing physical column order.

INSERT INTO projecttype       VALUES (projecttype_seq.NEXTVAL, 'A new project type');

The RETURNING clause is PL/SQL and will be covered later on in this book.

Multiple Table INSERT Statements

INSERT { FIRST | ALL } [ WHEN condition THEN ] INTO table [ VALUES (¼) ] [ WHEN condition THEN ] INTO table [ VALUES (¼) ] ELSE INTO table [ VALUES (…) ]subquery;

All WHEN conditions are executed on every row of the table. The WHEN condition determines if an INTO table VALUES clause is executed. The ELSE option is executed if all WHEN conditions have failed. Processing is moved to the next row retrieved after processing a passed WHEN condition.

FIRST will cause only the first WHEN condition to add rows. ALL will cause all passing WHEN conditions to be executed thus potentially adding a row to more than a single table, thus a multiple table insertion.

5.2.3 The UPDATE Statement

UPDATE [schema.]table SET column = expression | (subquery) | DEFAULT    | ( column [, column … ] ) = (subquery) [ WHERE ... ] [ RETURNING expression [, expression ] INTO variable    [, variable ] ) ];

5.2.4 The DELETE and TRUNCATE Statements

DELETE [ FROM ] [schema.]table [ WHERE ... ] [ RETURNING expression [, expression ] INTO variable    [, variable ] ) ];

The TRUNCATE statement is a DDL command, executes an automatic COMMIT command, and is therefore not undoable.

TRUNCATE [schema.]table table;

5.2.5 The MERGE Statement

The MERGE statement is used to add new rows and update existing rows between two tables as shown in Figure 5.4.

click to expand
Figure 5.4: The MERGE Command

MERGE [ hint ] INTO [schema.]table [alias] USING { [schema.] table | view | (subquery) } [ alias ] ON ( column = column [, column = column … ] ) WHEN MATCHED THEN     UPDATE SET { column = { column | expression | DEFAULT }       [ , column = … ]} WHEN NOT MATCHED THEN    INSERT ( column [, column … ] )       VALUES ( { column | expression | DEFAULT }          [ , column … ] );

The MERGE Statement in Oracle Database 10g

 Oracle Database 10 Grid   New syntax for the MERGE statement in Oracle Database 10g is highlighted in the following syntax diagram.

MERGE [ hint ] INTO [schema.]table [alias] USING { [schema.] table | view | (subquery) } [ alias ] ON ( column = column [, column = column ... ] ) { WHEN MATCHED THEN        UPDATE SET { column = { column | expression | DEFAULT }          [ , column = ... ]}       [ WHERE filter ]       [ DELETE WHERE filter ] |    WHEN NOT MATCHED THEN       INSERT ( column [, column ... ] )          VALUES ( { column | expression | DEFAULT }             [ , column ... ] )       [ WHERE filter ]  |    both UPDATE and INSERT };

Three changes are apparent:

  1. The addition of WHERE filtering clauses.

  2. Updated rows can be optionally deleted after updating of each row is complete.

  3. The Oracle9i Database syntax required that both INSERT and UPDATE clauses must exist. Both INSERT and UPDATE are now optional where at least one or both must be specified.

    Note 

     Oracle Database 10 Grid   The MERGE statement is now much more flexible.



 < Day Day Up > 



Oracle High Performance Tuning for 9i and 10g
Oracle High Performance Tuning for 9i and 10g
ISBN: 1555583059
EAN: 2147483647
Year: 2003
Pages: 164

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net