Release 3 (8.1.7)
Part Number A85397-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i SQL Reference Release 3 (8.1.7) Part Number A85397-01 |
|
SQL Statements:
DROP SEQUENCE to UPDATE, 21 of 27
Use a SELECT statement or subquery to retrieve data from one or more tables, object tables, views, object views, or materialized views.
|
See Also:
|
For you to select data from a table or materialized view, the table or materialized view must be in your own schema or you must have the SELECT privilege on the table or materialized view.
For you to select rows from the base tables of a view,
SELECT privilege on the view, and
SELECT privilege on the base tables.
The SELECT ANY TABLE system privilege also allows you to select data from any table or any materialized view or any view's base table.
subquery::=
query_table_expression_clause::=
table_collection_expression::=
outer_join::=
hint
Specify a comment that passes instructions to the optimizer on choosing an execution plan for the statement.
|
See Also: "Hints" and Oracle8i Performance Guide and Reference for the syntax and description of hints |
DISTINCT | UNIQUE
Specify DISTINCT or UNIQUE if you want Oracle to return only one copy of each set of duplicate rows selected (these two keywords are synonymous). Duplicate rows are those with matching values for each expression in the select list.
Restrictions:
DISTINCT or UNIQUE, the total number of bytes in all select list expressions is limited to the size of a data block minus some overhead. This size is specified by the initialization parameter DB_BLOCK_SIZE.
DISTINCT if the FROM clause contains LOB columns.
ALL
Specify ALL if you want Oracle to return all rows selected, including all copies of duplicates. The default is ALL.
*
Specify the asterisk to select all columns from all tables, views, or materialized views listed in the FROM clause.
schema
Specify the schema containing the selected table, view, or materialized view. If you omit schema, Oracle assumes the table, view, or materialized view is in your own schema.
table.* |view.* | snapshot.*
Specify the object name followed by a period and the asterisk to select all columns from the specified table, view, or materialized view. You can use the schema qualifier to select from a table, view, or materialized view in a schema other than your own. A query that selects rows from two or more tables, views, or materialized views is a join.
expr
Specify an expression representing the information you want to select. A column name in this list can be qualified with schema only if the table, view, or materialized view containing the column is qualified with schema in the FROM clause.
Restrictions:
FROM Clause
|
|
The |
|
|
|
|
Specify the partition or subpartition from which you want to retrieve data. The |
|
|
|
Specify the complete or partial name for a database link to a remote database where the table, view, or materialized view is located. This database need not be an Oracle database.
|
|
|
|
If you omit
Restriction: You cannot query a user-defined type or an object |
|
|
|
Specify the name of a table, view, or materialized view from which data is selected. "Materialized view" is synonymous with "snapshot". |
sample_clause
The sample_clause lets you instruct Oracle to select from a random sample of rows from the table, rather than from the entire table.
|
|
|
|
|
|
|
|
Restrictions on the sample_clause:
SAMPLE only in a query that selects from a single table. Joins are not supported. However, you can achieve the same results by using a CREATE TABLE ... AS SELECT query to materialize a sample of an underlying table and then rewrite the original query to refer to the newly created table sample. If you wish, you can write additional queries to materialize samples for other tables.
SAMPLE, Oracle automatically uses cost-based optimization. Rule-based optimization is not supported with this clause.
Caution: The use of statistically incorrect assumptions when using this feature can lead to incorrect or undesirable results. Refer to Oracle8i Concepts for more information on using the
sample_clause.
with_clause
The with_clause lets you restrict the subquery in one of the following ways:
|
|
Specify |
|
|
|
Specify
|
|
table_collection_expression
The table_collection_expression lets you inform Oracle that the collection value expression should be treated as a table for purposes of query and DML operations. The collection_expression can be a subquery, a column, a CAST or DECODE expression, a function, or a collection constructor. Regardless of its form, it must return a collection value (that is, a value whose type is nested table or varray). This process of extracting the elements of a collection is called collection unnesting.
The collection_expression can reference columns of tables defined to its left in the FROM clause. This is called left correlation. Left correlation can occur only in table_collection_expression. Other subqueries cannot contains references to columns defined outside the subquery.
The optional "(+)" lets you specify that table_collection_expression should return a row with all fields set to NULL if the collection is null or empty. The "(+)" is valid only if collection_expression uses left correlation. The result is similar to that of an outer join.
t_alias
Specify a correlation name (alias) for the table, view, materialized view, or subquery for evaluating the query. Correlation names are most often used in a correlated query. Other references to the table, view, or materialized view throughout the query must refer to this alias.
where_clause
The where_clause lets you restrict the rows selected to those that satisfy one or more conditions.
condition can be any valid SQL condition.
outer_join applies only if the query_table_expression_clause specifies more than one table. This special form of condition requires Oracle to return all the rows that satisfy the condition, as well as all the rows from one of the tables for which no rows of the other table satisfy the condition.
If one of the elements in the query_table_expression_clause is actually a nested table or some other form of collection, you specify the outer-join syntax in the table_collection_expression rather than in the where_clause.
|
See Also: "Outer Joins" for more information, including rules and restrictions that apply to outer joins |
If you omit this clause, Oracle returns all rows from the tables, views, or materialized views in the FROM clause.
|
Note: If this clause refers to a See Also: the "PARTITION Example" |
hierarchical_query_clause
The hierarchical_query_clause lets you select rows in a hierarchical order. For a discussion of hierarchical queries, see "Hierarchical Queries".
The preceding where_clause, if specified, restricts the rows returned by the query without affecting other rows of the hierarchy.
SELECT statements that contain hierarchical queries can contain the LEVEL pseudocolumn. LEVEL returns the value 1 for a root node, 2 for a child node of a root node, 3 for a grandchild, etc. The number of levels returned by a hierarchical query may be limited by available user memory.
|
See Also:
|
Restrictions: If you specify a hierarchical query:
order_by_clause, it takes precedence over any ordering specified by the hierarchical query.
|
|
Specify a condition that identifies the row(s) to be used as the root(s) of a hierarchical query. Oracle uses as root(s) all rows that satisfy this condition. If you omit this clause, Oracle uses all rows in the table as root rows. The |
|
|
|
Specify a condition that identifies the relationship between parent rows and child rows of the hierarchy. |
|
|
|
Restriction: The |
|
group_by_clause
Use the group_by_clause to group the selected rows based on the value of expr(s) for each row, and returns a single row of summary information for each group. If this clause contains CUBE or ROLLUP extensions, then superaggregate groupings are produced in addition to the regular groupings.
Expressions in the group_by_clause can contain any columns in the tables, views, and materialized views in the FROM clause, regardless of whether the columns appear in the select list.
Restrictions:
group_by_clause can contain no more than 255 expressions.
group_by_clause is limited to the size of a data block (specified by the initialization parameter DB_BLOCK_SIZE) minus some overhead.
group_by_clause references any object columns, the query will not be parallelized.
|
|
|
|
|
|
For example, given three expressions in the |
|
|
|
Rows based on the values of the first 'n' expressions are called regular rows, and the others are called superaggregate rows.
|
|
|
|
|
|
|
|
For example, given three expressions in the |
|
|
|
|
|
|
|
Use the
Specify |
|
|
||
|
|
These set operators combine the rows returned by two If you combine more than two queries with set operators, Oracle evaluates adjacent queries from left to right. You can use parentheses to specify a different order of evaluation.
|
|
Restrictions:
BLOB, CLOB, BFILE, varray, or nested table.
UNION, INTERSECT, and MINUS operators are not valid on LONG columns.
order_by_clause in the subquery of these operators.
SELECT statements containing TABLE collection expressions.
DB_BLOCK_SIZE) minus some overhead.
order_by_clause
Use the order_by_clause to order rows returned by the statement. Without an order_by_clause, no guarantee exists that the same query executed more than once will retrieve rows in the same order.
expr orders rows based on their value for expr. The expression is based on columns in the select list or columns in the tables, views, or materialized views in the FROM clause.
position orders rows based on their value for the expression in this position of the select list; position must be an integer.
You can specify multiple expressions in the order_by_clause. Oracle first sorts rows based on their values for the first expression. Rows with the same value for the first expression are then sorted based on their values for the second expression, and so on. Oracle sorts nulls following all others in ascending order and preceding all others in descending order.
Restrictions:
DISTINCT operator in this statement, this clause cannot refer to columns unless they appear in the select list.
order_by_clause can contain no more than 255 expressions.
If you specify a group_by_clause in the same statement, this order_by_clause is restricted to the following expressions:
USER, UID, and SYSDATE
group_by_clause
for_update_clause
The for_update_clause lets you lock the selected rows so that other users cannot lock or update the rows until you end your transaction. You can specify this clause only in a top-level SELECT statement (not in subqueries).
SELECT ... FOR UPDATE statement.
Restrictions:
DISTINCT or CURSOR operator, set operators, group_by_clause, or aggregate functions.
LONG columns and sequences referenced in the same statement.
The following statement selects rows from the emp table with the department number of 30:
SELECT * FROM emp WHERE deptno = 30;
The following statement selects the name, job, salary and department number of all employees except sales people from department number 30:
SELECT ename, job, sal, deptno FROM emp WHERE NOT (job = 'SALESMAN' AND deptno = 30);
The following statement selects from subqueries in the FROM clause and gives departments' total employees and salaries as a decimal value of all the departments:
SELECT a.deptno "Department", a.num_emp/b.total_count "%Employees", a.sal_sum/b.total_sal "%Salary" FROM (SELECT deptno, COUNT(*) num_emp, SUM(SAL) sal_sum FROM scott.emp GROUP BY deptno) a, (SELECT COUNT(*) total_count, SUM(sal) total_sal FROM scott.emp) b ;
PARTITION Example
You can select rows from a single partition of a partitioned table by specifying the keyword PARTITION in the FROM clause. This SQL statement assigns an alias for and retrieves rows from the nov98 partition of the sales table:
SELECT * FROM sales PARTITION (nov98) s WHERE s.amount_of_sale > 1000;
The following example selects rows from the sales table for sales earlier than a specified date:
SELECT * FROM sales WHERE sale_date < TO_DATE('1998-06-15', 'YYYY-MM-DD');
SAMPLE Examples
The following query estimates the number of employees in the emp table:
SELECT COUNT(*) * 100 FROM emp SAMPLE BLOCK (1);
The following example creates a sampled subset of the emp table and then joins the resulting sampled table with dept. This operation circumvents the restriction that you cannot specify the sample_clause in join queries:
CREATE TABLE sample_emp AS SELECT empno, deptno FROM emp SAMPLE(10); SELECT e.empno FROM sample_emp e, dept d WHERE e.deptno = d.deptno AND d.name = 'DEV';
GROUP BY Examples
To return the minimum and maximum salaries for each department in the employee table, issue the following statement:
SELECT deptno, MIN(sal), MAX (sal) FROM emp GROUP BY deptno; DEPTNO MIN(SAL) MAX(SAL) ---------- ---------- ---------- 10 1300 5000 20 800 3000 30 950 2850
To return the minimum and maximum salaries for the clerks in each department, issue the following statement:
SELECT deptno, MIN(sal), MAX (sal) FROM emp WHERE job = 'CLERK' GROUP BY deptno; DEPTNO MIN(SAL) MAX(SAL) ---------- ---------- ---------- 10 1300 1300 20 800 1100 30 950 950
CUBE Example
To return the number of employees and their average yearly salary across all possible combinations of department and job category, issue the following query:
SELECT DECODE(GROUPING(dname), 1, 'All Departments', dname) AS dname, DECODE(GROUPING(job), 1, 'All Jobs', job) AS job, COUNT(*) "Total Empl", AVG(sal) * 12 "Average Sal" FROM emp, dept WHERE dept.deptno = emp.deptno GROUP BY CUBE (dname, job); DNAME JOB Total Empl Average Sa --------------- --------- ---------- ---------- ACCOUNTING CLERK 1 15600 ACCOUNTING MANAGER 1 29400 ACCOUNTING PRESIDENT 1 60000 ACCOUNTING All Jobs 3 35000 RESEARCH ANALYST 2 36000 RESEARCH CLERK 2 11400 RESEARCH MANAGER 1 35700 RESEARCH All Jobs 5 26100 SALES CLERK 1 11400 SALES MANAGER 1 34200 SALES SALESMAN 4 16800 SALES All Jobs 6 18800 All Departments ANALYST 2 36000 All Departments CLERK 4 12450 All Departments MANAGER 3 33100 All Departments PRESIDENT 1 60000 All Departments SALESMAN 4 16800 All Departments All Jobs 14 24878.5714
The following CONNECT BY clause defines a hierarchical relationship in which the empno value of the parent row is equal to the mgr value of the child row:
CONNECT BY PRIOR empno = mgr;
In the following CONNECT BY clause, the PRIOR operator applies only to the empno value. To evaluate this condition, Oracle evaluates empno values for the parent row and mgr, sal, and comm values for the child row:
CONNECT BY PRIOR empno = mgr AND sal > comm;
To qualify as a child row, a row must have a mgr value equal to the empno value of the parent row and it must have a sal value greater than its comm value.
HAVING Example
To return the minimum and maximum salaries for the clerks in each department whose lowest salary is below $1,000, issue the next statement:
SELECT deptno, MIN(sal), MAX (sal) FROM emp WHERE job = 'CLERK' GROUP BY deptno HAVING MIN(sal) < 1000; DEPTNO MIN(SAL) MAX(SAL) ---------- ---------- ---------- 20 800 1100 30 950 950
ORDER BY Examples
To select all salesmen's records from emp, and order the results by commission in descending order, issue the following statement:
SELECT * FROM emp WHERE job = 'SALESMAN' ORDER BY comm DESC;
To select the employees from emp ordered first by ascending department number and then by descending salary, issue the following statement:
SELECT ename, deptno, sal FROM emp ORDER BY deptno ASC, sal DESC;
To select the same information as the previous SELECT and use the positional ORDER BY notation, issue the following statement:
SELECT ename, deptno, sal FROM emp ORDER BY 2 ASC, 3 DESC;
FOR UPDATE Examples
The following statement locks rows in the emp table with clerks located in New York and locks rows in the dept table with departments in New York that have clerks:
SELECT empno, sal, comm FROM emp, dept WHERE job = 'CLERK' AND emp.deptno = dept.deptno AND loc = 'NEW YORK' FOR UPDATE;
The following statement locks only those rows in the emp table with clerks located in New York. No rows are locked in the dept table:
SELECT empno, sal, comm FROM emp, dept WHERE job = 'CLERK' AND emp.deptno = dept.deptno AND loc = 'NEW YORK' FOR UPDATE OF emp.sal;
The following example uses a SELECT ... FOR UPDATE statement to lock a row containing a LOB prior to updating the LOB value.
INSERT INTO t_table VALUES (1, 'abcd'); COMMIT; DECLARE num_var NUMBER; clob_var CLOB; clob_locked CLOB; write_amount NUMBER; write_offset NUMBER; buffer VARCHAR2(20) := 'efg'; BEGIN SELECT clob_col INTO clob_locked FROM t_table WHERE num_col = 1 FOR UPDATE; write_amount := 3; dbms_lob.write(clob_locked, write_amount, write_offset, buffer); END;
WITH CHECK OPTION Example
The following statement is legal even though the second value violates the condition of the subquery where_clause:
INSERT INTO(SELECT ename, deptno FROM emp WHERE deptno < 10)VALUES ('Taylor', 20);
However, the following statement is illegal because of the WITH CHECK OPTION clause:
INSERT INTO(SELECT ename, deptno FROM empWHERE deptno < 10WITH CHECK OPTION)VALUES ('Taylor', 20);
This equijoin returns the name and job of each employee and the number and name of the department in which the employee works:
SELECT ename, job, dept.deptno, dname FROM emp, dept WHERE emp.deptno = dept.deptno; ENAME JOB DEPTNO DNAME ---------- --------- ---------- -------------- CLARK MANAGER 10 ACCOUNTING KING PRESIDENT 10 ACCOUNTING MILLER CLERK 10 ACCOUNTING SMITH CLERK 20 RESEARCH ADAMS CLERK 20 RESEARCH FORD ANALYST 20 RESEARCH SCOTT ANALYST 20 RESEARCH JONES MANAGER 20 RESEARCH ALLEN SALESMAN 30 SALES BLAKE MANAGER 30 SALES MARTIN SALESMAN 30 SALES JAMES CLERK 30 SALES TURNER SALESMAN 30 SALES WARD SALESMAN 30 SALES
You must use a join to return this data because employee names and jobs are stored in a different table than department names. Oracle combines rows of the two tables according to this join condition:
emp.deptno = dept.deptno
The following equijoin returns the name, job, department number, and department name of all clerks:
SELECT ename, job, dept.deptno, dname FROM emp, dept WHERE emp.deptno = dept.deptno AND job = 'CLERK'; ENAME JOB DEPTNO DNAME ---------- --------- ---------- -------------- MILLER CLERK 10 ACCOUNTING SMITH CLERK 20 RESEARCH ADAMS CLERK 20 RESEARCH JAMES CLERK 30 SALES
This query is identical to the preceding example, except that it uses an additional where_clause condition to return only rows with a job value of 'CLERK'.
To determine who works in Taylor's department, issue the following statement:
SELECT ename, deptno FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE ename = 'TAYLOR');
To give all employees in the emp table a 10% raise if they have not already been issued a bonus (if they do not appear in the bonus table), issue the following statement:
UPDATE emp SET sal = sal * 1.1 WHERE empno NOT IN (SELECT empno FROM bonus);
To create a duplicate of the dept table named newdept, issue the following statement:
CREATE TABLE newdept (deptno, dname, loc) AS SELECT deptno, dname, loc FROM dept;
The following query uses a self join to return the name of each employee along with the name of the employee's manager:
SELECT e1.ename||' works for '||e2.ename "Employees and their Managers" FROM emp e1, emp e2 WHERE e1.mgr = e2.empno; Employees and their Managers ------------------------------- BLAKE works for KING CLARK works for KING JONES works for KING FORD works for JONES SMITH works for FORD ALLEN works for BLAKE WARD works for BLAKE MARTIN works for BLAKE SCOTT works for JONES TURNER works for BLAKE ADAMS works for SCOTT JAMES works for BLAKE MILLER works for CLARK
The join condition for this query uses the aliases e1 and e2 for the emp table:
e1.mgr = e2.empno
This query uses an outer join to extend the results of the Equijoin example above:
SELECT ename, job, dept.deptno, dname FROM emp, dept WHERE emp.deptno (+) = dept.deptno; ENAME JOB DEPTNO DNAME ---------- --------- ---------- -------------- CLARK MANAGER 10 ACCOUNTING KING PRESIDENT 10 ACCOUNTING MILLER CLERK 10 ACCOUNTING SMITH CLERK 20 RESEARCH ADAMS CLERK 20 RESEARCH FORD ANALYST 20 RESEARCH SCOTT ANALYST 20 RESEARCH JONES MANAGER 20 RESEARCH ALLEN SALESMAN 30 SALES BLAKE MANAGER 30 SALES MARTIN SALESMAN 30 SALES JAMES CLERK 30 SALES TURNER SALESMAN 30 SALES WARD SALESMAN 30 SALES 40 OPERATIONS
In this outer join, Oracle returns a row containing the operations department even though no employees work in this department. Oracle returns NULL in the ename and job columns for this row. The join query in this example selects only departments that have employees.
The following query uses an outer join to extend the results of the preceding example:
SELECT ename, job, dept.deptno, dname FROM emp, dept WHERE emp.deptno (+) = dept.deptno AND job (+) = 'CLERK'; ENAME JOB DEPTNO DNAME ---------- --------- ---------- -------------- MILLER CLERK 10 ACCOUNTING SMITH CLERK 20 RESEARCH ADAMS CLERK 20 RESEARCH JAMES CLERK 30 SALES 40 OPERATIONS
In this outer join, Oracle returns a row containing the operations department even though no clerks work in this department. The (+) operator on the job column ensures that rows for which the job column is NULL are also returned. If this (+) were omitted, the row containing the operations department would not be returned because its job value is not 'CLERK'.
This example shows four outer join queries on the customers, orders, lineitems, and parts tables. These tables are shown here:
SELECT custno, custname FROM customers; CUSTNO CUSTNAME ---------- -------------------- 1 Angelic Co. 2 Believable Co. 3 Cables R Us SELECT orderno, custno, TO_CHAR(orderdate, 'MON-DD-YYYY') "ORDERDATE" FROM orders; ORDERNO CUSTNO ORDERDATE ---------- ---------- ----------- 9001 1 OCT-13-1998 9002 2 OCT-13-1998 9003 1 OCT-20-1998 9004 1 OCT-27-1998 9005 2 OCT-31-1998 SELECT orderno, lineno, partno, quantity FROM lineitems; ORDERNO LINENO PARTNO QUANTITY ---------- ---------- ---------- ---------- 9001 1 101 15 9001 2 102 10 9002 1 101 25 9002 2 103 50 9003 1 101 15 9004 1 102 10 9004 2 103 20 SELECT partno, partname FROM parts; PARTNO PARTNAME ------ -------- 101 X-Ray Screen 102 Yellow Bag 103 Zoot Suit
The customer Cables R Us has placed no orders, and order number 9005 has no line items.
The following outer join returns all customers and the dates they placed orders. The (+) operator ensures that customers who placed no orders are also returned:
SELECT custname, TO_CHAR(orderdate, 'MON-DD-YYYY') "ORDERDATE" FROM customers, orders WHERE customers.custno = orders.custno (+); CUSTNAME ORDERDATE -------------------- -------------- Angelic Co. OCT-13-1993 Angelic Co. OCT-20-1993 Angelic Co. OCT-27-1993 Believable Co. OCT-13-1993 Believable Co. OCT-31-1993 Cables R Us
The following outer join builds on the result of the previous one by adding the lineitems table to the FROM clause, columns from this table to the select list, and a join condition joining this table to the orders table to the where_clause. This query joins the results of the previous query to the lineitems table and returns all customers, the dates they placed orders, and the part number and quantity of each part they ordered. The first (+) operator serves the same purpose as in the previous query. The second (+) operator ensures that orders with no line items are also returned:
SELECT custname, TO_CHAR(orderdate, 'MON-DD-YYYY') "ORDERDATE", partno, quantity FROM customers, orders, lineitems WHERE customers.custno = orders.custno (+) AND orders.orderno = lineitems.orderno (+); CUSTNAME ORDERDATE PARTNO QUANTITY -------------------- -------------- ---------- ---------- Angelic Co. OCT-13-1993 101 15 Angelic Co. OCT-13-1993 102 10 Angelic Co. OCT-20-1993 101 15 Angelic Co. OCT-27-1993 102 10 Angelic Co. OCT-27-1993 103 20 Believable Co. OCT-13-1993 101 25 Believable Co. OCT-13-1993 103 50 Believable Co. OCT-31-1993 Cables R Us
The following outer join builds on the result of the previous one by adding the parts table to the FROM clause, the partname column from this table to the select list, and a join condition joining this table to the lineitems table to the where_clause. This query joins the results of the previous query to the parts table to return all customers, the dates they placed orders, and the quantity and name of each part they ordered. The first two (+) operators serve the same purposes as in the previous query. The third (+) operator ensures that rows with NULL part numbers are also returned:
SELECT custname, TO_CHAR(orderdate, 'MON-DD-YYYY') "ORDERDATE", quantity, partname FROM customers, orders, lineitems, parts WHERE customers.custno = orders.custno (+) AND orders.orderno = lineitems.orderno (+) AND lineitems.partno = parts.partno (+); CUSTNAME ORDERDATE QUANTITY PARTNAME -------------------- -------------- ---------- ------------ Angelic Co. OCT-13-1993 15 X-Ray Screen Angelic Co. OCT-13-1993 10 Yellow Bag Angelic Co. OCT-20-1993 15 X-Ray Screen Angelic Co. OCT-27-1993 10 Yellow Bag Angelic Co. OCT-27-1993 20 Zoot Suit Believable Co. OCT-13-1993 25 X-Ray Screen Believable Co. OCT-13-1993 50 Zoot Suit Believable Co. OCT-31-1993 Cables R Us
You can perform DML operations on nested tables only if they are defined as columns of a table. Therefore, when the query_table_expression_clause of an INSERT, DELETE, or UPDATE statement is a table_collection_expression, the collection expression must be a subquery that selects the table's nested table column. The examples that follow are based on this scenario:
CREATE TYPE ProjectType AS OBJECT( pno NUMBER, pname CHAR(31), budget NUMBER); CREATE TYPE ProjectSet AS TABLE OF ProjectType; CREATE TABLE Dept (dno NUMBER, dname CHAR(31), projs ProjectSet) NESTED TABLE projs STORE AS ProjectSetTable ((Primary Key(Nested_Table_Id, pno)) ORGANIZATION INDEX COMPRESS 1); INSERT INTO Dept VALUES (1, 'Engineering', ProjectSet());
This example inserts into the 'Engineering' department's 'projs' nested table:
INSERT INTO TABLE(SELECT d.projs FROM Dept d WHERE d.dno = 1) VALUES (1, 'Collection Enhancements', 10000);
This example updates the 'Engineering' department's 'projs' nested table:
UPDATE TABLE(SELECT d.projs FROM Dept d WHERE d.dno = 1) p SET p.budget = p.budget + 1000;
This example deletes from the 'Engineering' department's 'projs' nested table
DELETE TABLE(SELECT d.projs FROM Dept d WHERE d.dno = 1) p WHERE p.budget > 100000;
Suppose the database contains a table hr_info with columns dept, location, and mgr, and a column of nested table type people which has name, dept, and sal columns. You could get all the rows from hr_info and all the rows from people using the following statement:
SELECT t1.dept, t2.* FROM hr_info t1, TABLE(t1.people) t2 WHERE t2.dept = t1.dept;
Now suppose that people is not a nested table column of hr_info, but is instead a separate table with columns name, dept, address, hiredate, and sal. You can extract the same rows as in the preceding example with this statement:
SELECT t1.department, t2.* FROM hr_info t1, TABLE(CAST(MULTISET( SELECT t3.name, t3.dept, t3.sal FROM people t3 WHERE t3.dept = t1.dept) AS NESTED_PEOPLE)) t2;
Finally, suppose that people is neither a nested table column of table hr_info nor a table itself. Instead, you have created a function people_func that extracts from various sources the name, department, and salary of all employees. You can get the same information as in the preceding examples with the following query:
SELECT t1.dept, t2.* FROM HY_INFO t1, TABLE(CAST (people_func( ... ) AS NESTED_PEOPLE)) t2;
|
See Also:
Oracle8i Application Developer's Guide - Fundamentals for more examples of collection unnesting. |
LEVEL Examples
The following statement returns all employees in hierarchical order. The root row is defined to be the employee whose job is 'PRESIDENT'. The child rows of a parent row are defined to be those who have the employee number of the parent row as their manager number.
SELECT LPAD(' ',2*(LEVEL-1)) || ename org_chart, empno, mgr, job FROM emp START WITH job = 'PRESIDENT' CONNECT BY PRIOR empno = mgr; ORG_CHART EMPNO MGR JOB ------------ ---------- ---------- --------- KING 7839 PRESIDENT JONES 7566 7839 MANAGER SCOTT 7788 7566 ANALYST ADAMS 7876 7788 CLERK FORD 7902 7566 ANALYST SMITH 7369 7902 CLERK BLAKE 7698 7839 MANAGER ALLEN 7499 7698 SALESMAN WARD 7521 7698 SALESMAN MARTIN 7654 7698 SALESMAN TURNER 7844 7698 SALESMAN JAMES 7900 7698 CLERK CLARK 7782 7839 MANAGER MILLER 7934 7782 CLERK
The following statement is similar to the previous one, except that it does not select employees with the job 'ANALYST'.
SELECT LPAD(' ', 2*(LEVEL-1)) || ename org_chart, empno, mgr, job FROM emp WHERE job != 'ANALYST' START WITH job = 'PRESIDENT' CONNECT BY PRIOR empno = mgr; ORG_CHART EMPNO MGR JOB -------------------- ---------- ---------- --------- KING 7839 PRESIDENT JONES 7566 7839 MANAGER ADAMS 7876 7788 CLERK SMITH 7369 7902 CLERK BLAKE 7698 7839 MANAGER ALLEN 7499 7698 SALESMAN WARD 7521 7698 SALESMAN MARTIN 7654 7698 SALESMAN TURNER 7844 7698 SALESMAN JAMES 7900 7698 CLERK CLARK 7782 7839 MANAGER MILLER 7934 7782 CLERK
Oracle does not return the analysts scott and ford, although it does return employees who are managed by scott and ford.
The following statement is similar to the first one, except that it uses the LEVEL pseudocolumn to select only the first two levels of the management hierarchy:
SELECT LPAD(' ',2*(LEVEL-1)) || ename org_chart, empno, mgr, job FROM emp START WITH job = 'PRESIDENT' CONNECT BY PRIOR empno = mgr AND LEVEL <= 2; ORG_CHART EMPNO MGR JOB ------------ ---------- ---------- --------- KING 7839 PRESIDENT JONES 7566 7839 MANAGER BLAKE 7698 7839 MANAGER CLARK 7782 7839 MANAGER
This example shows a query that joins the dept table on the local database with the emp table on the houston database:
SELECT ename, dname FROM emp@houston, dept WHERE emp.deptno = dept.deptno;
The following examples show the general syntax of a correlated subquery:
SELECT select_list FROM table1 t_alias1 WHERE expr operator (SELECT column_list FROM table2 t_alias2 WHERE t_alias1.column operator t_alias2.column); UPDATE table1 t_alias1 SET column = (SELECT expr FROM table2 t_alias2 WHERE t_alias1.column = t_alias2.column); DELETE FROM table1 t_alias1 WHERE column operator (SELECT expr FROM table2 t_alias2 WHERE t_alias1.column = t_alias2.column);
The following statement returns data about employees whose salaries exceed their department average. The following statement assigns an alias to emp, the table containing the salary information, and then uses the alias in a correlated subquery:
SELECT deptno, ename, sal FROM emp x WHERE sal > (SELECT AVG(sal) FROM emp WHERE x.deptno = deptno) ORDER BY deptno;
For each row of the emp table, the parent query uses the correlated subquery to compute the average salary for members of the same department. The correlated subquery performs the following steps for each row of the emp table:
deptno of the row is determined.
deptno is then used to evaluate the parent query.
The subquery is evaluated once for each row of the emp table.
The following statement returns the current date:
SELECT SYSDATE FROM DUAL;
You could select SYSDATE from the emp table, but Oracle would return 14 rows of the same SYSDATE, one for every row of the emp table. Selecting from DUAL is more convenient.
The following statement increments the zseq sequence and returns the new value:
SELECT zseq.nextval FROM dual;
The following statement selects the current value of zseq:
SELECT zseq.currval FROM dual;
|
 Copyright © 1996-2000, Oracle Corporation. All Rights Reserved. |
|