ORACLE advanced subqueries

2011-07-13  来源:本站原创  分类:Database  人气:164 

:: Write multiple-column subquery
:: Return null values ​​describe and explain the behavior of subqueries
:: Write a subquery in the FROM clause
:: SQL subquery used in classification
:: Description can be used to solve problems related to the type of subquery
:: Write correlated subqueries
:: With correlated subqueries Update and delete rows
:: Using the EXISTS and NOT EXISTS operator
:: Using the WITH clause

Lesson Aim
In this lesson, you learn how to write multiple-column subqueries and subqueries in the FROM clause of a SELECT statement. You also learn how to solve problems by using scalar, correlated subqueries and the WITH clause.

What is subquery?

A sub-query is a SELECT statement embedded in another clause of the SQL statement

select ...
The main query ----> from ...
where ...
(Select ...
from ... <----- subquery
where ...)

What Is a Subquery?

A subquery is a SELECT statement that is embedded in a clause of another SQL statement, called the parent statement.

Query within the query results to the outer query
The subquery (inner query) returns a value that is used by the parent statement. Using a nested subquery is equivalent to performing two sequential queries and using the result of the inner query as the search value in the outer query (main query).

Subqueries can be used for the following purposes:
:: To provide values ​​for conditions in WHERE, HAVING, and START WITH clauses of SELECT statements
:: To define the set of rows to be inserted into the target table of an INSERT or CREATE TABLE statement /././././
:: To define the set of rows to be included in a view or snapshot in a CREATE VIEW or CREATE SNAPSHOT statement /./././ ibid.
:: To define one or more values ​​to be assigned to existing rows in an UPDATE statement / / / / ditto
:: To define a table to be operated on by a containing query. (You do this by placing the subquery in the FROM clause. This can be done in INSERT, UPDATE, and DELETE statements as well.) /,.,. FROM subquery

Note: A subquery is evaluated once for the entire parent statement.
First execution of the query, the returned value to the outer query, then execute the main query.

Subquery

SELECT select_list
FROM table
WHERE expr operator (SELECT select_list
FROM table);

:: Subquery (inline queries) in the main query execution time
:: Sub-query results are for the main query (outer query)

Subqueries

You can build powerful statements out of simple ones by using subqueries. Subqueries can be very useful when you need to select rows from a table with a condition that depends on the data in the table itself or some other table. Subqueries are very useful for writing SQL statements that need values ​​based on one or more unknown conditional values.

In the syntax: ././././.
operator includes a comparison operator such as>, =, or IN

Note: Comparison operators fall into two classes: (comparison operation)
single-row operators (>, =,> =, <, <>, <=)
multiple-row operators (IN, ANY, ALL).

The subquery is often referred to as a nested SELECT, sub-SELECT, or inner SELECT statement. The inner and outer queries can retrieve data from either the same table or different tables. (Called embedded select, sub-select, internal select)

Using Subqueries

SELECT last_name
FROM employees 10500
WHERE salary> <-------------|
(SELECT salary |
FROM employees
WHERE employee_id = 149);

Using a Subquery
/ / Explanation of the above
In the example in the slide, the inner query returns the salary of the employee with employee number 149. The outer query uses the result of the inner query to display the names of all the employees who earn more than this amount.

Example:

Display the names of all employees who earn less than the average salary in the company.

SELECT last_name, job_id, salary
FROM employees
WHERE salary <(SELECT AVG (salary)
FROM employees);

Multiple-column subquery

Each line of the main query with a multi-line values ​​of more multiple-column subquery

Main query <----------|
WHERE (manager_id, department_id) IN <-------| |
| |
<-----|| |
| | |
Subquery | | |
100 90 _______|| |
102 60 ________| |
12450 ____________|

Multiple-Column Subqueries

So far you have written single-row subqueries and multiple-row subqueries where only one column is returned by the inner SELECT statement and this is used to evaluate the expression in the parent select statement. If you want to compare two or more columns, you must write a compound WHERE clause using logical operators (if you want to compare two or more rows you want to use logical operators to write the WHERE clause of a hybrid). Using multiple-column subqueries, you can combine duplicate WHERE conditions into a single WHERE clause. (mixed subquery with multiple WHERE conditions can be combined into a WHERE clause.)

Syntax
SELECT column, column, ...
FROM table
WHERE (column, column, ...) IN
(SELECT column, column, ...
FROM table
WHERE condition);

The graphic in the slide illustrates that the values ​​of the MANAGER_ID and DEPARTMENT_ID from the main query are being compared with the MANAGER_ID and DEPARTMENT_ID values ​​retrieved by the subquery. Since the number of columns that are being compared are more than one, the example qualifies as a multiple-column subquery.

Out more

In a multiple-column subquery can be compared in a column:
:: Paired comparison
:: Unpaired comparison

Pairwise versus Nonpairwise Comparisons in pairs, non-paired comparison

Column comparisons in a multiple-column subquery can be pairwise comparisons or nonpairwise comparisons.
/././././. In the select statement for each row have the same column conditions.
In the example on the next slide, a pairwise comparison was executed in the WHERE clause. Each candidate row in the SELECT statement must have both the same MANAGER_ID column and the DEPARTMENT_ID as the employee with the EMPLOYEE_ID 178 or 174.

A multiple-column subquery can also be a nonpairwise comparison. In a nonpairwise comparison, each of the columns from the WHERE clause of the parent SELECT statement are individually compared to multiple values ​​retrieved by the inner select statement. The individual columns can match any of the values ​​retrieved by the inner select statement. But collectively, all the multiple conditions of the main SELECT statement must be satisfied for the row to be displayed. The example on the next page illustrates a nonpairwise comparison.

Pairwise comparison subquery

Shows the details of employees, these employees were the same manager and work in the same department, with EMPLOYEE_ID 178 or 174
SELECT employee_id, manager_id, department_id
FROM employees
WHERE (manager_id, department_id) IN
(SELECT manager_id, department_id
FROM employees
WHERE employee_id IN (178,174))
AND employee_id NOT IN (178,174);

SQL> select manager_id, department_id
2 from employees
3 where employee_id in (178,174);

MANAGER_ID DEPARTMENT_ID
---------- -------------
14980
149

SQL> SELECT employee_id, manager_id, department_id
2 FROM employees
3 WHERE (manager_id, department_id) IN
4 (SELECT manager_id, department_id
5 FROM employees
6 WHERE employee_id IN (178,174))
7 AND employee_id NOT IN (178,174);

EMPLOYEE_ID MANAGER_ID DEPARTMENT_ID
----------- ---------- -------------
17,914,980
17,714,980
17,614,980
17,514,980

SQL> SELECT employee_id, manager_id, department_id
2 FROM employees
3 WHERE (manager_id, department_id) IN
4 (SELECT manager_id, department_id
5 FROM employees
6 WHERE employee_id IN (178,174))
7;

EMPLOYEE_ID MANAGER_ID DEPARTMENT_ID
----------- ---------- -------------
17,914,980
17,714,980
17,614,980
17,514,980
17,414,980 /./././.

Pairwise Comparison Subquery
/ /
The example in the slide is that of a multiple-column subquery because the subquery returns more than one column (row subquery returns more than the value). It compares the values ​​in the MANAGER_ID column and the DEPARTMENT_ID column of each row in the EMPLOYEES table with the values ​​in the MANAGER_ID column and the DEPARTMENT_ID column for the employees with the EMPLOYEE_ID 178 or 174.

First, the subquery to retrieve the MANAGER_ID and DEPARTMENT_ID values ​​for the employees with the EMPLOYEE_ID 178 or 174 is executed. These values ​​are compared with the MANAGER_ID column and the DEPARTMENT_ID column of each row in the EMPLOYEES table. If the values ​​match, the row is displayed. In the output, the records of the employees with the EMPLOYEE_ID 178 or 174 will not be displayed. The output of the query in the slide follows.

Non-paired comparison subquery

Show by the same manager, with EMPLOYEE_ID 174 or 141 employees; and, working in the same department, with EMPLOYEE_ID 174 or 141 of the employee's details
SELECT employee_id, manager_id, department_id 3 /
FROM employees
WHERE manager_id IN
(SELECT manager_id 1 /
FROM employees
WHERE employee_id IN (174,141))
AND department_id IN
(SELECT department_id 2 /
FROM employees
WHERE employee_id IN (174,141))
AND employee_id NOT IN (174,141);

Manager_id return department_id value and the value of the table with departments in each row for comparison.
To satisfy only two values ​​at the same time display.

Nonpairwise Comparison Subquery

The example shows a nonpairwise comparison of the columns. It displays the EMPLOYEE_ID, MANAGER_ID, and DEPARTMENT_ID of any employee whose manager ID matches any of the manager IDs of employees whose employee IDs are either 174 or 141 and DEPARTMENT_ID match any of the department IDs of employees whose employee IDs are either 174 or 141.

First, the subquery to retrieve the MANAGER_ID values ​​for the employees with the EMPLOYEE_ID 174 or 141 is executed. Similarly, the second subquery to retrieve the DEPARTMENT_ID values ​​for the employees with the EMPLOYEE_ID 174 or 141 is executed. The retrived values ​​of the MANAGER_ID and DEPARTMENT_ID columns are compared with the MANAGER_ID and DEPARTMENT_ID column for each row in the EMPLOYEES table. If the MANAGER_ID column of the row in the EMPLOYEES table matches with any of the values ​​of the MANAGER_ID retrieved by the inner subquery and if the DEPARTMENT_ID column of the row in the EMPLOYEES table matches with any of the values ​​of the DEPARTMENT_ID retrieved by the second subquery, the record is displayed. The output of the query in the slide follows.

EMPLOYEE_ID MANAGER_ID DEPARTMENT_ID
14,212,450
14,312,450
14,412,450
17,614,980

In the FROM clause subquery

SELECT a.last_name, a.salary,
a.department_id, b.salavg / / must be the b table 'column'
FROM employees a, (SELECT department_id,
AVG (salary) salavg
FROM employees
GROUP BY department_id) b
WHERE a.department_id = b.department_id
AND a.salary> b.salavg;

Using a Subquery in the FROM Clause

You can use a subquery in the FROM clause of a SELECT statement, which is very similar to how views are used. A subquery in the FROM clause of a SELECT statement is also called an inline view (internal view). A subquery in the FROM clause of a SELECT statement defines a data source for that particular SELECT statement, and only that SELECT statement. The example on the slide displays employee last names, salaries, department numbers, and average salaries for all the employees who earn more than the average salary in their department. The subquery in the FROM clause is named b, and the outer query references the SALAVG column using this alias.
/ / Note the table alias.

Subquery expression levels

:: A hierarchical subquery expression is a return line from the exact value of a column subquery
:: In Oracle8i, subqueries grading only limited circumstances in some cases supported, for example:
- SELECT statement (FROM and WHERE clauses)
- In an INSERT statement in the VALUES table
:: In Oracle9i, the grading sub-query can be used:
- DECODE and CASE expression is part of the conditions and
- In addition to all other than the SELECT GROUP BY clause

Scalar Subqueries in SQL

A subquery that returns exactly one column value from one row is also referred to as a scalar subquery (a subquery expression is a hierarchical row from a column to return the exact value of the subquery.) Multiple-column subqueries written to compare two or more columns, using a compound WHERE clause and logical operators, do not qualify as scalar subqueries.

././././././ If the subquery returns zero rows, grading subquery expression is NULL, if the subquery returns multiple rows, Oracle Server returns ERROR.
If the subquery returns 0 rows, the value of the scalar subquery expression is NULL. If the subquery returns more than one row, the Oracle Server returns an error.

The value of the scalar subquery expression is the value of the select list item of the subquery. If the subquery returns 0 rows, the value of the scalar subquery expression is NULL. If the subquery returns more than one row, the Oracle Server returns an error. The Oracle Server has always supported the usage of a scalar subquery in a SELECT statement. The usage of scalar subqueries has been enhanced in Oracle9i. You can now use scalar subqueries in:
- Condition and expression part of DECODE and CASE
- All clauses of SELECT except GROUP BY
- In the left-hand side of the operator in the SET clause and WHERE clause of UPDATE statement

However, scalar subqueries are not valid expressions in the following places:
- As default values ​​for columns and hash expressions for clusters
- In the RETURNING clause of DML statements
- As the basis of a function-based index index of the basic functions
- In GROUP BY clauses, CHECK constraints, WHEN conditions ///./././
- HAVING clauses ./././
- In START WITH and CONNECT BY clauses
- In statements that are unrelated to queries, such as CREATE PROFILE

Classification sub-query: an example

CASE expressions in the classification of sub-queries.

SELECT employee_id, last_name,
(CASE 20 <----|
WHEN department_id = |
(SELECT deaprtment_id FROM deaprtments
WHERE location_id = 1800)
THEN 'Canada' ELSE 'USA' END) location
FROM employees;

...
...
EMPLOYEE_ID LAST_NAME LOCATI
----------- ------------------------- ------
199 Grant USA
200 Whalen USA
201 Hartstein Canada
202 Fay Canada
203 Mavris USA
204 Baer USA
...

ORDER BY in a subquery in the classification of sub-queries
SELECT employee_id, last_name
FROM employees e / / two tables
ORDER BY (SELECT department_name / / use deaprtments sort this table department_name
FROM departments d
WHERE e.department_id = d.department_id);

Scalar Subqueries: Examples

The first example in the slide demonstrates (certification) that scalar subqueries can be used in CASE expressions. The inner query returns the value 20, which is the department ID of the department whose location ID is 1800. The CASE expression in the outer query uses the result of the inner query to display the employee ID, last names, and a value of Canada or USA, depending on whether the department ID of the record retrieved by the outer query is 20 or not. / / is the USA, or Canada depends in the outer query to return records department_id is not 20 /./././
/ / Inner join is 20, if 20 is the outer join Canada, if not 20, then return to USA

The result of the preceding example follows:
EMPLOYEE_ID LAST_NAME LOCATI
100 King USA
101 Kochhar USA
102 De Haan USA
103 Hunold USA
...
201 Hartstein Canada
202 Fay Canada
206 Higgins USA
206 Gietz USA

Scalar Subqueries: Examples (continued)

The second example in the slide demonstrates that scalar subqueries can be used in the ORDER BY clause. The example orders the output based on the DEPARTMENT_NAME by matching the DEPARTMENT_ID from the EMPLOYEES table with the DEPARTMENT_ID from the DEPARTMENTS table. This comparison in done in a scalar subquery in the ORDER BY clause. The result of the the second example follows:

The second example uses a correlated subquery. In a correlated subquery, the subquery references a column from a table referred to in the parent statement. Correlated subqueries are explained later in this lesson.

Related sub-query

Related sub-query is used to row-by-row processing. Each line of external queries, each sub-query is executed once ./././.

GET
|----> Candidate row from outer query / / get query from outside the candidate line.
| |
| EXECUTE
| Inner query using candidate row value / / get the query from within the candidate line.
| |
| USE
|---- Values ​​from inner query to qualify / /
or disqualify candidate row

Correlated Subqueries

The Oracle Server performs a correlated subquery when the subquery references a column from a table referred to in the parent statement. A correlated subquery is evaluated once for each row processed by the parent statement. The parent statement can be a SELECT, UPDATE, or DELETE statement. /././././

Nested Subqueries Versus Correlated Subqueries
With a normal nested subquery, the inner SELECT query runs first and executes once, returning values ​​to be used by the main query (which is for general inquiries). A correlated subquery, however, executes once for each candidate row considered by the outer query. In other words, the inner query is driven by the outer query.

Nested Subquery Execution /././.
- The inner query executes first and finds a value. Within the first query
- The outer query executes once, using the value from the inner query. / / And then the outer query

Correlated Subquery Execution .,./././././
- Get a candidate row (fetched by the outer query). / / Get the line from the outer query
- Execute the inner query using the value of the candidate row.
Outside the lines obtained with the implementation of the query within the query.
- Use the values ​​resulting from the inner query to qualify or disqualify the candidate. From the inside using the value returned by the query limit or limit line.
- Repeat until no candidate row remains. / / Repeat until no rows remaining.

Correlated subquery

SELECT column1, column2, ...
FROM table1 outer
WHERE column1 operator
(SELECT column1, column2
FROM table2
WHERE expr1 = / / must have an associated
outer.expr2);

In the parent query subquery references a column in the table

Correlated Subqueries (continued)

A correlated subquery is one way of reading every row in a table and comparing values ​​in each row against related data. It is used whenever a subquery must return a different result or set of results for each candidate row considered by the main query. In other words, you use a correlated subquery to answer a multipart question whose answer depends on the value in each row processed by the parent statement.

The Oracle Server performs a correlated subquery when the subquery references a column from a table in the parent query. (When a reference to the parent subquery columns returned by the query table.)

Note: You can use the ANY and ALL operators in a correlated subquery.

Use correlated subqueries

Identify all employees who earn salaries higher than the average salary in the sector

SELECT last_name, salary, department_id
FROM employees outer
WHERE salary>
|---> (SELECT AVG (salary)
FROM employees
WHERE department_id =
outer.department_id);

Outer query is processed once for each row, once in the query evaluation

Using Correlated Subqueries

The example in the slide determines which employees earn more than the average salary of their department. In this case, the correlated subquery specifically computes the average salary for each department.

Because both the outer query and inner query use the EMPLOYEES table in the FROM clause, an alias is given to EMPLOYEES in the outer SELECT statement, for clarity. Not only does the alias make the entire SELECT statement more readable, but without the alias the query would not work properly, because the inner statement would not be able to distinguish the inner table column from the outer table column.

Use correlated subqueries

Show the employee's details, these employees work at least twice transform

SELECT e.employee_id, last_name, e.job_id
FROM employees e
WHERE 2 <= (SELECT COUNT (*)
FROM job_history
WHERE employee_id = e.employee_id);

Using Correlated Subqueries

/ / Example of the above analysis
The example in the slide displays the details of those employees who have switched jobs at least twice. The Oracle Server evaluates a correlated subquery as follows:
1. Select a row from the table specified in the outer query. This will be the current candidate row.
2. Store the value of the column referenced in the subquery from this candidate row. (In the example in the slide, the column referenced in the subquery is E. EMPLOYEE_ID.) / / Stored in the column from the candidates in the reference column subquery value, the subquery references a column value: E. EMPLOYEE_ID

3. Perform the subquery with its condition referencing the value from the outer query's candidate row / / calculation of the inquiry, will meet the conditions of the count (*) to find out. (In the example in the slide, group function COUNT (*) is evaluated based on the value of the E. EMPLOYEE_ID column obtained in step 2.) e.employee_id value derived from step 2.

4. Evaluate the WHERE clause of the outer query on the basis of results of the subquery performed in step 3. This is determines if the candidate row is selected for output. (In the example, the number of times an employee has switched jobs, evaluated by the subquery, is compared with 2 in the WHERE clause of the outer query. If the condition is satisfied, that employee record is displayed.)
/ / Will be elected by the count (*) compared with 2, if> = is displayed, otherwise not shown.
5. Repeat the procedure for the next candidate row of the table, and so on until all the rows in the table have been processed.

The correlation is established by using an element from the outer query in the subquery. In this example, the correlation is established by the statement EMPLOYEE_ID = E. EMPLOYEE_ID in which you compare EMPLOYEE_ID from the table in the subquery with the EMPLOYEE_ID from the table in the outer query.

Use EXISTS operator

:: EXISTS subquery operations in the result set to test the existence of the line
:: If a subquery row value is found:
- Including the search query will not continue ././././.
- The condition is flagged TRUE
:: If a subquery row value is not found:
- The condition is flagged FALSE
- Including the search query to continue

The EXISTS Operator

With nesting SELECT statements, all logical operators are valid. In addition, you can use the EXISTS operator. This operator is frequently used with correlated subqueries to test whether a value retrieved by the outer query exists in the results set of the values ​​retrieved by the inner query. If the subquery returns at least one row, the operator returns TRUE. If the value does not exist, it returns FALSE.
If the subquery returns at least one line, the operation returns TRUE, if it did not return, it returns FALSE
Accordingly, NOT EXISTS tests whether a value retrieved by the outer query is not a part of the results set of the values ​​retrieved by the inner query.

Use EXISTS operator

Find at least one employee's manager

SELECT employee_id, last_name, job_id, department_id
FROM employees outer
WHERE EXISTS (SELECT 'X' / / If the return X, is TRUE, otherwise FALSE. Finally see is not TRUE, the return X
FROM employees of the conditions is met.
WHERE manager_id =
outer.employee_id);
EMPLOYEE_ID LAST_NAME JOB_ID DEPARTMENT_ID
----------- ------------------------- ---------- ---- ---------
100 King AD_PRES 90
101 Kochhar AD_VP 90
102 De Haan AD_VP 90
103 Hunold IT_PROG 60
108 Greenberg FI_MGR 100
114 Raphaely PU_MAN 30
120 Weiss ST_MAN 50
121 Fripp ST_MAN 50
122 Kaufling ST_MAN 50
123 Vollman ST_MAN 50
124 Mourgos ST_MAN 50
...

/ / Parse it::
In the employee_id in the long manager_id on display, that is, its manager.

SQL> select distinct manager_id
2 FROM employees
3 WHERE manager_id IS NOT NULL;

MANAGER_ID
----------
100
101
102
103
108
114
120
121
122
123
124
145
146
147
148
149
201
205

SQL> select 'X' from dual;

'
-
X

SELECT employee_id, last_name, job_id, department_id
FROM employees
WHERE employee_id IN (SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL);

Using the EXISTS Operator

Use the following conditions, if at least find a manager and employee number matches the number of records, EXISTS operator to ensure that the search query, including not continue:
WHERE manager_id = outer.employee_id.

Note that the inner SELECT query does not need to return a specific value (within the query does not find the exact value), so a constant (constant can also choose to) can be selected. From a performance standpoint, it is faster to select a constant than a column.

Note: Having EMPLOYEE_ID in the SELECT clause of the inner query causes a table scan for that column. Replacing it with the literal X, or any constant, improves performance. This is more efficient than using the IN operator.

A IN construct can be used as an alternative for a EXISTS operator, as shown in the following example:

SELECT employee_id, last_name, job_id, department_id
FROM employees
WHERE employee_id IN (SELECT manager_id
FROM employees
WHERE manager_id IS NOT NULL);

Use NOT EXISTS operator

Find all the departments without any employees

SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS (SELECT 'X'
FROM employees
WHERE department_id / / have a joint United
= D.department_id);

DEPARTMENT_ID DEPARTMENT_NAME
------------- ------------------------------
120 Treasury
130 Corporate Tax
140 Control And Credit
...

Can draw the following is not selected.
SQL> select * from employees
2 where department_id = 120;

No rows selected

SQL> select * from employees
2 where department_id = 130;

No rows selected
...

Using the NOT EXISTS Operator

Alternative Solution

A NOT IN construct can be used as an alternative for a NOT EXISTS operator, as shown in the following example.
SELECT department_id, department_name
FROM departments
WHERE department_id NOT IN (SELECT department_id
FROM employees);

However, NOT IN evaluates to FALSE if any member of the set is a NULL value.
If any member of the collection is the NULL value, NOT IN value is FALSE. Therefore, even if the departments table rows that satisfy the WHERE condition, your query will not return any rows.
Therefore, your query will not return any rows even if there are rows in the departments table that satisfy the WHERE condition.

Related UPDATE

UPDATE table1 alias1
SET column = (SELECT expression
FROM table2 alias2
WHERE alias1.column =
alias2.column);

With a correlated subquery to update rows in a table, the table rows based on another table

Correlated UPDATE
In the case of the UPDATE statement, you can use a correlated subquery to update rows in one table based on rows from another table.

Related UPDATE

:: With an additional column to store the department name, denormalized EMPLOYEES table
:: Fill the table with relevant updates

ALTER TABLE employees
ADD (department_name VARCHAR2 (30));

UPDATE employees e
SET department_name =
(SELECT department_name
FROM departments d
WHERE e.department_id = d.department_id); / / to have an associated

Correlated UPDATE (continued)

The example in the slide denormalizes the EMPLOYEES table by adding a column to store the department_name and then populates the table by using a correlated update.

Here is another example for a correlated update.

Problem Statement
Use a correlated subquery to update rows in the EMPLOYEES table based on rows from the REWARDS table:

././././././
UPDATE employees
SET salary = (SELECT employees.salary + rewards.pay_raise
FROM rewards
WHERE employee_id = employees.employee_id
AND payraise_date =
(SELECT MAX (payraise_date)
FROM rewards
WHERE employee_id = employees.employee_id))
WHERE employees.employee_id IN
(SELECT employee_id
FROM rewards);

This example uses the REWARDS table. The REWARDS table has the columns EMPLOYEE_ID, PAY_RAISE, and PAYRAISE_DATE. Every time an employee gets a pay raise, a record with the details of the employee ID, the amount of the pay raise, and the date of receipt of the pay raise is inserted into the REWARDS table. The REWARDS table can contain more than one record for an employee. The PAYRAISE _DATE column is used to identify the most recent pay raise received by an employee.

In the example, the SALARY column in the EMPLOYEES table is updated to reflect the latest pay raise received by the employee. This is done by adding the current salary of the employee with the corresponding pay raise from the REWARDS table.

Related DELETE

DELETE FROM table1 alias1
WHERE column operator
(SELECT expression
FROM table2 alias2
WHERE alias1.column = alias2.column);
With a correlated subquery to delete rows from a table, the table rows based on another table

Correlated DELETE

In the case of a DELETE statement, you can use a correlated subquery to delete only those rows that also exist in another table. If you decide that you will maintain only the last four job history records in the JOB_HISTORY table, then when an employee transfers to a fifth job, you delete the oldest JOB_HISTORY row by looking up the JOB_HISTORY table for the MIN (START_DATE) for the employee. The following code illustrates how the preceding operation can be performed using a correlated DELETE:

DELETE FROM job_history JH
WHERE employee_id =
(SELECT employee_id
FROM employees E
WHERE JH.employee_id = E.employee_id ././././ have an associated
AND start_date =
(SELECT MIN (start_date)
FROM job_history JH
WHERE JH.employee_id = E.employee_id) / / association
AND 5> (SELECT COUNT (*)
FROM job_history JH
WHERE JH.employee_id = E.employee_id / / association
GROUP BY employee_id
HAVING COUNT (*)> = 4));

Related delete DELETE

Remove with a correlated subquery which in the EMPLOYEES table and EMP_HISTORY table the employee_id column values ​​the same line

DELETE FROM employees E
WHERE employee_id =
(SELECT employee_id
FROM emp_history
WHERE employee_id = E.employee_id);

Correlated DELETE (continued)

Example
Two tables are used in this example. They are:
- The EMPLOYEES table, which gives details of all the current employees
- The EMP_HISTORY table, which gives details of previous employees

EMP_HISTORY contains data regarding previous employees, so it would be erroneous if the same employee's record existed in both the EMPLOYEES and EMP_HISTORY tables. You can delete such erroneous records by using the correlated subquery shown in the slide.

WITH clause

:: When a query block in a complex query occurs multiple times, use the WITH clause to SELECT statement is used multiple times in the same query block
:: WITH clause to retrieve the results of the query block, and it exists in the user's temporary tablespace
:: WITH clause can improve performance

The WITH clause

Using the WITH clause, you can define a query block before using it in a query. The WITH clause (formally known as subquery_factoring_clause) enables you to reuse the same query block in a SELECT statement when it occurs more than once within a complex query. This is particularly useful when a query has many references to the same query block and there are joins and aggregations.

Using the WITH clause, you can reuse the same query when it is high cost to evaluate the query block and it occurs more than once within a complex query. Using the WITH clause, the Oracle Server retrieves the results of a query block and stores it in the user's temporary tablespace. This can improve performance.

WITH Clause Benefits
- Makes the query easy to read
- Evaluates a clause only once, even if it appears multiple times in the query, thereby enhancing performance

WITH Clause: Example

Using the WITH clause, write a query to display the name of the department and the department's total salary, total salary higher than what's the average salary for all departments

WITH
dept_costs AS (
SELECT d.department_name, SUM (e.salary) AS dept_total
FROM employees e, departments d
WHERE e.department_id = d.department_id
GROUP BY d.department_name),
avg_cost AS (
SELECT SUM (dept_total) / COUNT (*) AS dept_avg
FROM dept_costs)
SELECT *
FROM dept_costs
WHERE dept_total>
(SELECT dept_avg
FROM avg_cost)
ORDER BY department_name;

The problem in the slide would require the following intermediate calculations:
1. Calculate the total salary for every department, and store the result using a WITH clause.
2. Calculate the average salary across departments, and store the result using a WITH clause.
3. Compare the total salary calculated in the first step with the average salary calculated in the second step. If the total salary for a particular department is greater than the average salary across departments, display the department name and the total salary for that department.

WITH Clause: Example (continued)

The SQL code in the slide is an example of a situation in which you can improve performance and write SQL more simply by using the WITH clause. The query creates the query names DEPT_COSTS and AVG_COST and then uses them in the body of the main query. Internally, the WITH clause is resolved either as an in-line view or a temporary table. The optimizer chooses the appropriate resolution depending on the cost or benefit of temporarily storing the results of the WITH clause.

Note: A subquery in the FROM clause of a SELECT statement is also called an in-line view. (Internal view)

The output generated by the SQL code on the slide will be as follows:

DEPARTMENT_NAME DEPT_TOTAL
------------------------------ ----------
Sales 304500
Shipping 156400

The WITH Clause Usage Notes
- It is used only with SELECT statements
- A query name is visible to all WITH element query blocks (including their subquery blocks) defined after it and the main query block itself (including its subquery blocks).
- When the query name is the same as an existing table name, the parser searches from the inside out, the query block name takes precedence over the table name.
- The WITH clause can hold more than one query. Each query is then separated by a comma.

SUMMARY

In this lesson, you should have learned how to:
:: Return more than one of the multiple-column subquery
:: Multi-column comparison to be carried out in pairs or in pairs
:: A multiple-column subquery can also be used in a SELECT statement's FROM clause
:: Hierarchical subqueries have been enhanced in Oracle9i
Summary

You can use multiple-column subqueries to combine multiple WHERE conditions into a single WHERE clause. Column comparisons in a multiple-column subquery can be pairwise comparisons or non-pairwise comparisons.

You can use a subquery to define a table to be operated on by a containing query.

Oracle 9i enhances the the uses of scalar subqueries. Scalar subqueries can now be used in:
: Condition and expression part of DECODE and CASE
: All clauses of SELECT except GROUP BY
: SET clause and WHERE clause of UPDATE statement

Summary
:: Whenever a sub-query must be a candidate for each row returned different results, then, correlated subqueries are useful
:: EXISTS operator to test the existence of the value of the Boolean operation
:: A correlated subquery can be used in SELECT, UPDATE, and DELETE statements
:: In a SELECT statement using the WITH clause, you can repeatedly use the same query block
Summary (continued)

The Oracle Server performs a correlated subquery when the subquery references a column from a table referred to in the parent statement. A correlated subquery is evaluated once for each row processed by the parent statement. The parent statement can be a SELECT, UPDATE, or DELETE statement. Using the WITH clause, you can reuse the same query when it is costly to reevaluate the query block and it occurs more than once within a complex query.

************ ************** Classification retrieve data

Target

After completing this lesson, you should be able to do the following:
:: Explain the concept of hierarchical query
:: Create a tree structure of the report
:: Hierarchical data format
:: Remove branches from the tree structure

Lesson Aim
In this lesson, you learn how to use hierarchical queries to create tree-structured reports. (Tree-structured report)

Examples of data in the EMPLOYEES table

Sample Data from the EMPLOYEES Table

Using hierarchical queries, you can retrieve data based on a natural hierarchical relationship between rows in a table. A relational database does not store records in a hierarchical way relational databases can not be stored in a hierarchical way. However, where a hierarchical relationship exists between the rows of a single table, a process called tree walking enables the hierarchy to be constructed. A hierarchical query is a method of reporting, in order, the branches of a tree.

Imagine a family tree with the eldest members of the family found close to the base or trunk of the tree and the youngest members representing branches of the tree. Branches can have their own branches, and so on.

A hierarchical query is possible when a relationship exists between rows in a table.
When there are trekking in a table relationship between the hierarchical query is possible. For example, in the slide, you see that employees with the job IDs of AD_VP, ST_MAN, SA_MAN, and MK_MAN report directly to the president of the company. We know this because the MANAGER_ID column of these records contain the employee ID 100, which belongs to the president (AD_PRES).

Note: Hierarchical trees are used in various fields such as human genealogy (family trees), livestock (breeding purposes), corporate management (management hierarchies), manufacturing (product assembly), evolutionary research (species development), and scientific research.

Natural tree structure

Natural Tree Structure

The EMPLOYEES table has a tree structure representing the management reporting line. The hierarchy can be created by looking at the relationship between equivalent values ​​in the EMPLOYEE_ID and MANAGER_ID columns. This relationship can be exploited by joining the table to itself (self-connections of these tables can be used.). The MANAGER_ID column contains the employee number of the employee's manager.

The parent-child relationship of a tree structure enables you to control:
- The direction in which the hierarchy is walked
- The starting point inside the hierarchy

Note: The slide displays an inverted tree structure of the management hierarchy of the employees in the EMPLOYEES table.

Hierarchical queries

SELECT [LEVEL], column, expr ...
FROM table
[WHERE condition (s)]
[START WITH condition (s)]
[CONNECT BY PRIOR condition (s)];

WHERE condition:
expr comparison_operator (comparison) expr

Keywords and Clauses (clauses and keywords)

Hierarchical queries can be identified by the presence of the CONNECT BY and START WITH clauses.

In the syntax:

SELECT Is the standard SELECT clause.

LEVEL For each row returned by a hierarchical query, the LEVEL
For each row returned hierarchical queries, pseudocolumn (column) returns 1 for a root row, 2 for a child of a root, and so on.

/././././
FROM table Specifies the table, view, or snapshot containing the columns. You can select from only one table. (Only one table)

WHERE Restricts the rows returned by the query without affecting other rows of the hierarchy. (Back row does not affect other hierarchical queries)

condition Is a comparison with expressions. (compare expression conditions)

START WITH Specifies the root rows of the hierarchy (where to start). This clause is required for a tree hierarchical query.
Specified level of the root line (where to start), this clause for classification of the query tree is a must.

CONNECT BY Specifies the columns in which the relationship between parent and child
Parent-child relationship specified column

PRIOR rows exist. This clause is required for a hierarchical query.
Lines exist for hierarchical queries is necessary.
The SELECT statement cannot contain a join or query from a view that contains a join.
SELECT operation can not include the connection or from a query contains a join view

Traverse the tree

Starting point

:: Specify the conditions must be met
:: To accept the conditions of validity

START WITH column1 = value

Use the EMPLOYEES table, the name is Kochhar employees from the beginning
... START WITH last_name = 'Kochhar'

Walking the Tree (traverse tree)

The row or rows to be used as the root of the tree are determined by the START WITH clause (lines or line S as determined in the START WITH clause roots). The START WITH clause can be used in conjunction with any valid condition.
START WITH clause in connection with any valid condition

Examples

Using the EMPLOYEES table, start with King, the president of the company.
... START WITH manager_id IS NULL

Using the EMPLOYEES table, start with employee Kochhar. A START WITH condition can contain a subquery.
... START WITH employee_id = (SELECT employee_id
FROM employees
WHERE last_name = 'Kochhar')

If the START WITH clause is omitted (ignored), the tree walk is started with all of the rows in the table as root rows (tree traversal all the rows from the table, as the root of the traversal). If a WHERE clause is used, the walk is started with all the rows that satisfy the WHERE condition. This no longer reflects a true hierarchy.
If you use a WHERE clause, all rows from the beginning through, and satisfy the WHERE condition, which will not return to the tree level.

Note: The clauses CONNECT BY PRIOR and START WITH are not ANSI SQL standard.

Instructor Note
You may wish to add that multiple hierarchical outputs are generated if more than one row satisfies the START WITH condition.

Traverse the tree (with the direction of the query)

CONNECT BY PRIOR column1 = column2

From the top-down traversal, the parent with the employees table, column employee_id, sub-column is manager_id
... CONNECT BY PRIOR employee_id = manager_id

Direction from the top down ----> Column1 = Parent Key
Column2 = Child Key

From the bottom up ----> Column1 = Child Key
Column2 = Parent Key

Walking the Tree (continued)

The direction of the query, whether it is from parent to child or from child to parent, is determined by the CONNECT BY PRIOR column placement. The PRIOR operator refers to the parent row (PRIOR operations related to the parent line). To find the children of a parent row, the Oracle Server evaluates the PRIOR expression for the parent row and the other expressions for each row in the table (the parent line in order to find the child rows, ORACLE SERVER PRIOR role in the parent line, and other expressions operation will be used to form the other line, the line condition is true then the sub-line parent line). Rows for which the condition is true are the children of the parent. The Oracle Server always selects children by evaluating the CONNECT BY condition with respect to a current parent row.

Examples

Walk from the top down (top-down) using the EMPLOYEES table. Define a hierarchical relationship in which the EMPLOYEE_ID value of the parent row is equal to the MANAGER_ID value of the child row.
... CONNECT BY PRIOR employee_id = manager_id

Walk from the bottom up using the EMPLOYEES table.
... CONNECT BY PRIOR manager_id = employee_id

The PRIOR operator does not necessarily need to be coded immediately following the CONNECT BY (PRIOR operator does not necessarily follow directly after the CONNECT BY). Thus, the following CONNECT BY PRIOR clause gives the same result (the same as the previous examples results) as the one in the preceding example.
... CONNECT BY employee_id = PRIOR manager_id

Note: The CONNECT BY clause cannot contain a subquery.. /./././. Can not contain subqueries

Traverse the tree: from the bottom up

SELECT employee_id, last_name, job_id, manager_id
FROM employees
START WITH employee_id = 101
CONNECT BY PRIOR manager_id = employee_id; / / from the bottom up

EMPLOYEE_ID LAST_NAME JOB_ID MANAGER_ID
----------- ------------------------- ---------- ---- ------
101 Kochhar AD_VP 100
100 King AD_PRES

Walking the Tree: From the Bottom Up

The example in the slide displays a list of managers starting with the employee whose employee ID is 101.

Example

In the following example, EMPLOYEE_ID values ​​are evaluated for the parent row and MANAGER_ID, and SALARY values ​​are evaluated for the child rows. The PRIOR operator applies only to the EMPLOYEE_ID value.

... CONNECT BY PRIOR employee_id = manager_id
AND salary> 15000;

SQL> SELECT employee_id, last_name, job_id, manager_id
2 FROM employees
3 START WITH employee_id = 101
4 CONNECT BY PRIOR manager_id = employee_id
5 and salary> 15000;

EMPLOYEE_ID LAST_NAME JOB_ID MANAGER_ID
----------- ------------------------- ---------- ---- ------
101 Kochhar AD_VP 100
100 King AD_PRES

To qualify as a child row, a row must have a MANAGER_ID value equal to the EMPLOYEE_ID value of the parent row and must have a SALARY value greater than $ 15,000.
In order to get the child rows, columns have a value = MANAGER_ID employee_id value of the parent line ../././

Instructor Note
:: In the context of the second paragraph, you may wish to include that additional conditions added to the CONNECT BY PRIOR clause potentially eliminated the whole of the branch (branch tree of the potential limitations), hence the EMPLOYEE_ID AND SALARY are evaluated for the parent row to determine if it is to be part of the output.

SQL> SELECT last_name | | 'reports to' | | PRIOR last_name "Walk Top Down"
2 FROM employees
3 start with employee_id = 101
4 connect by prior manager_id = employee_id;

Walk Top Down
--------------------------------// Inserted about
Kochhar reports to
King reports to Kochhar

SQL> SELECT last_name | | 'reports to' | | last_name "Walk Top Down"
2 FROM employees
3 start with employee_id = 101
4 connect by prior manager_id = employee_id;

Walk Top Down
-------------------------------------------------- ------------
Kochhar reports to Kochhar
King reports to King

Traverse the tree: from the top down

SELECT last_name | | 'reports to' | | PRIOR last_name "Walk Top Down"
FROM employees
START WITH last_name = 'King'
CONNECT BY PRIOR employee_id = manager_id;

Walking the Tree: From the Top Down

Walking from the top down, display the names of the employees and their manager. Use employee King as the starting point. Print only one column.

Walk Top Down
--------------------------------
King reports to
King reports to
Kochhar reports to King
Greenberg reports to Kochhar
Faviet reports to Greenberg
Chen reports to Greenberg
Sciarra reports to Greenberg
Urman reports to Greenberg
Popp reports to Greenberg
Whalen reports to Kochhar
Mavris reports to Kochhar

SELECT last_name | | 'reports to' | | last_name "Walk Top Down"
FROM employees
START WITH last_name = 'King'
CONNECT BY PRIOR employee_id = manager_id;

Walk Top Down
------------------------------------------
King reports to King
King reports to King
Kochhar reports to Kochhar
Greenberg reports to Greenberg
Faviet reports to Faviet
Chen reports to Chen
Sciarra reports to Sciarra
Urman reports to Urman
Popp reports to Popp
Whalen reports to Whalen
Mavris reports to Mavris
...

Line with the LEVEL pseudo column grading

Ranking Rows with the LEVEL Pseudocolumn

You can explicitly show the rank or level of a row in the hierarchy by using the LEVEL pseudocolumn (pseudo-column). This will make your report more readable (this will make your report easier to read). The forks where one or more branches split away from a larger branch are called nodes, and the very end of a branch is called a leaf, or leaf node. The diagram in the slide shows the nodes of the inverted tree with their LEVEL values. For example, employee Higgens is a parent and a child, while employee Davies is a child and a leaf.

The LEVEL Pseudocolumn

Value Level
1 A root node (the root)
2 A child of a root node (the root of the child)
3 A child of a child, and so on (the root of the children's children ...)

Note: A root node is the highest node within an inverted tree. A child node is any nonroot node. A parent node is any node that has children. A leaf node is any node without children. The number of levels returned by a hierarchical query may be limited by available user memory.

In the slide, King is the root or parent (LEVEL = 1). Kochhar, De Hann, Mourgos, Zlotkey, Hartstein, Higgens, and Hunold are children and also parents (LEVEL = 2). Whalen, Rajs, Davies, Matos, Vargas, Gietz, Ernst, Lorentz, Abel, Taylor, Grant, and Fay are children and leaves.
(LEVEL = 3 and LEVEL = 4)

Formatted with the LEVEL and LPAD grading report

Create a report showed the company's management, starting from the highest level, indented below the level of follow

COLUMN org_chart FORMAT A18
SELECT LPAD (last_name, LENGTH (last_name) + (LEVEL * 2) -2 ,'_')
AS org_chart
FROM employees
START WITH last_name = 'King'
CONNECT BY PRIOR employee_id = manager_id;

Formatting Hierarchical Reports Using LEVEL

The nodes in a tree are assigned level numbers from the root. Use the LPAD function in conjunction with the pseudocolumn LEVEL to display a hierarchical report as an indented tree. (Staggered tree)

In the example on the slide:

: LPAD (char1, n [, char2]) returns char1, left-padded to length n with the sequence of characters in char2. The argument n is the total length of the return value as it is displayed on your terminal screen.
: LPAD (last_name, LENGTH (last_name) + (LEVEL * 2) -2 ,'_') defines the display format.
: Char1 is the LAST_NAME, n the total length of the return value, is length of the LAST_NAME + (LEVEL * 2) -2, and char2 is '_'.

In other words, this tells SQL to take the LAST_NAME and left-pad it with the '_' character till the length of the resultant string is equal to the value determined by LENGTH (last_name) + (LEVEL * 2) -2.

For King, LEVEL = 1. Hence, (2 * 1) - 2 = 2 - 2 = 0. So King does not get padded with any '_' character and is displayed in column 1.

For Kochhar, LEVEL = 2. Hence, (2 * 2) - 2 = 4 - 2 = 2. So Kochhar gets padded with 2 '_' characters and is displayed indented.

The rest of the records in the EMPLOYEES table are displayed similarly.

Formatting Hierarchical Reports Using LEVEL (continued)

King
__Kochhar
____Greenberg
______Faviet
______Chen
______Sciarra
______Urman
______Popp
____Whalen
____Mavris

ORG_CHART
--------------------
____Baer
____Higgins
______Gietz
__De Haan
____Hunold
______Ernst
______Austin
______Pataballa
______Lorentz

Pruning branches

WHERE clause with the CONNECT BY clause to remove a node (node) leaves also remove a branch (node, the leaves are not a)

Where last_name! = 'Higgins' CONNECT BY PRIOR
employee_id = manager_id
AND last_name! = 'Higgins'
Range: Small: Big

Pruning Branches
You can use the WHERE and CONNECT BY clauses to prune the tree; that is, to control which nodes or rows are displayed (the control node or line S which is not shown). The predicate you use acts as a Boolean condition.

Examples

Starting at the root, walk from the top down, and eliminate employee Higgins in the result, but process (keep) the child rows.
SELECT department_id, employee_id, last_name, job_id, salary
FROM employees
WHERE last_name! = 'Higgins'
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id;

Starting at the root, walk from the top down, and eliminate employee Higgins and all child rows. (Remove the entire branch)
SELECT department_id, employee_id, last_name, job_id, salary
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
AND last_name! = 'Higgins';

相关文章
  • ORACLE advanced subqueries 2011-07-13

    :: Write multiple-column subquery :: Return null values ​​describe and explain the behavior of subqueries :: Write a subquery in the FROM clause :: SQL subquery used in classification :: Description can be used to solve problems related to the type o

  • Research Report on Oracle Advanced Replication of data 2010-04-14

    Research Report on Oracle replication data Kwan Chan Time :2010-2-9 Using the scene description: Have a database server running, in order not to affect the usage of the database, a station needs a new database server Kai, the original database data a

  • Replication Oracle Advanced Replication configuration steps that 2011-05-28

    The following article mainly is about Oracle Advanced Replication replication, if you need a few in the country related to the relevant place to set up the server (Oracle), the requirements are all related data in the same DB, the following is a desc

  • Oracle Advanced Replication 2011-03-21

    Before you begin, you need to briefly present the company has several projects using Oracle as the database platform, and some projects to use Oracle's database replication technology, which also encountered some problems, so here I am on Oracle repl

  • Oracle Advanced Replication to copy the configuration steps that 2011-05-28

    The following article focuses on the Oracle Advanced Replication Replication, if you need several related areas in the country to set up the relevant server (Oracle), the requirements are all related DB data consistency, the following is a descriptio

  • oracle advanced query 2011-04-17

    Oracle's advanced query simple join query (join) And the dept table in the emp table that exists between the many to one association (in reality there are other associations), often we want to check out more information at this time we have to use jo

  • Oracle Advanced scheduling options (original) 2011-04-19

    Overview of scheduling tasks in Oracle Scheduling tasks in Oracle not only provides a program, job and schedul three basic components, but also provides many advanced components. As job class: used to carry out tasks associated with resource planning

  • oracle advanced query instances, improve efficiency 2010-09-30

    Using the Oracle-specific query syntax, you can achieve a multiplier effect. As follows: 1. Tree query create table tree ( id number (10) not null primary key, name varchar2 (100) not null, super number (10) not null / / 0 is root ); - From child to

  • oracle notes (xiv) Oracle Advanced Query 2 2011-04-14

    1, cross-connection Cross-connect that is the Cartesian product , refers to two relationships in any combination of all records. Under normal circumstances, cross-check is no practical significance. SQL> select * from emp e cross join dept d; Note: Y

  • ORACLE Advanced HINT 2011-08-17

    Here are just a hint applies under these five main situations: 1) use_nl mainly used in multi-table join, when, nl mean nest loop, that is, nested queries; Rules, according to the parameters specified in the order table, according to the query where

  • [Change] oracle advanced queue application in communications design 2010-07-23

    http://blog.csdn.net/aozhi/archive/2009/06/05/4244592.aspx

  • Oracle Advanced Search 2011-08-30

    1. Row_number () over (order by column name) select empno, ename, sal, row_number () over (order by ename) from emp; Usage: query the emp table, then sort of ename, row_number () is the role, ename sorted position, starting at 1. If two such inquirie

  • Advanced Search in oracle 2011-04-19

    Advanced Search in oracle A collection of operations set operators a) equal to or conditions of the joint union sal> 2500 or job = manager select ename, sal, job from emp where sal> 2500 union select ename, sal, job from emp where job = 'MANAGER'; s

  • Oracle database migration to MySQL Migration Notes combat 2011-03-31

    Oracle database migration to MySQL Migration Notes combat (2010-10-20 14:32:18) Reproduced Tags: Transplant trigger mysql oracle it Category: Database I. Introduction The original project is based on the company's Oracle database, Oracle is powerful,

  • Spring calls the results of a small set of Oracle stored procedure 2010-03-29

    oracle advanced features for the congregation is always different (I strongly dislike this point, if you use his product is necessary for this product-specific programming, which is like I have never been bullish weblogic platform reasons), keep larg

  • Oracle database upgrade in silent mode 2010-02-26

    Reasons can not be started because of X window graphical interface, use silent mode to upgrade the database, that is, 10.2.0.1 upgrade to 10.2.0.4. Operating system version Quote $ Oslevel-r 5300-06 Upgrade steps as follows: 1, edit the file to upgra

  • (Transfer) Oracle Database 11g version of the introduction 2010-03-29

    http://www.oracle.com/lang/cn/database/product_editions.html Oracle Database 11 g version of the introduction Meet the needs of different enterprises world-class database Whether you are independent developers, small and medium enterprises or large e

  • oracle permission role 2010-04-24

    First, rights Categories: System privileges: system requires the consumer to use the database permissions. (The system privileges is the users). Entity permissions: a user permissions to other users of the table or view access. (Is for the purposes o

  • Oracle users. Role. Permissions 2010-05-05

    Oracle User Management First, create a user SQL> Create user username identified by password Example: SQL> Create user zzh identified by zzh / / If the password is numbers, enclosed in double quotes SQL> grant connect, resource to zzh; Queries th

  • Oracle user. Permissions. Role Management 2010-07-14

    Oracle permission to set one, permissions Category: System privileges: system requires the consumer to use the database permissions. (The system privileges is the users). Entity permissions: a user permissions to other users of the table or view perm