Shulou(Shulou.com)06/01 Report--

Use subqueries to process data

You can use data manipulation language (DML) statements in subqueries:

Retrieve data using embedded views

Copy data from one table to another

Update the data in the table based on the values of another table

Delete rows in a table based on the value of another table

Use subqueries as data sources to retrieve data

Select department_name, city from departments

Natural join (select l.location_id, l.city, l.country_id

From loc l

Join countries c

On (l.country_id = c.country_id)

Join regions using (region_id) where region_name = 'europe')

Insert data using a subquery as a target

Insert into (select l.location_id, l.city, l.country_id from locations l

Join countries c

On (l.country_id = c.country_id)

Join regions using (region_id)

Where region_name = 'europe')

Values (3300, 'Cardiff',' UK')

Using WITH CHECK OPTION in DML statements

WITH CHECK OPTION keyword that forbids changes to rows in a subquery.

An overview of the default features displayed

Use the DEFAULT keyword to set the field default value.

Allows users to control when default values are applied to data

You can explicitly use default values in INSERT and UPDATE statements

Use explicit default values

INSERT and DEFAULT:

Insert into deptm3 (department_id, department_name, manager_id) values (300, 'engineering', default)

UPDATE and DEFAULT:

Update deptm3 set manager_id = default where department_id = 10

Copy rows from another table

Subquery of the INSERT statement:

Insert into sales_reps (id, name, salary, commission_pct)

Select employee_id, last_name, salary, commission_pct

From employees

Where job_id like'% REP%'

Do not use the VALUES clause

The INSERT clause matches the number and type of columns of the subquery

Use the following types to complete multiple table inserts:

-unconditional INSERT

-rotate INSERT

-conditional INSERT ALL

-conditional INSERT FIRST

Insert all

Into target_a values (...)

Into target_b values (...)

Into target_c values (...)

Select...

From sourcetab

Where...

Multi-table query schematic diagram:

The function of multi-table insertion is as follows:

Use INSERT... The SELECT statement inserts rows into multiple tables as a single DML statement.

Multi-table INSERT statements used in data warehouse systems write the source data of one or more operations to a set of target tables.

They provide significant performance improvements:

-single DML statement and multi-table INSERT. SELECT statement

-A single DML statement versus multiple table inserts using IF...THEN syntax

Types of multi-table INSERT statements

The following are different types of multi-table INSERT statements:

Unconditional INSERT

Rotate INSERT

Conditional INSERT ALL

Conditional INSERT FIRST

Multi-table INSERT syntax

Insert [conditional_insert_clause]

[insert_into_clause values_clause] (subquery)

Conditional INSERT clause:

[ALL | FIRST]

[WHEN condition THEN] [insert_into_clause values_clause]

[ELSE] [insert_into_clause values_clause]

Unconditional INSERT ALL

Insert all

Into sal_history values (empid,hiredate,sal)

Into mgr_history values (empid,mgr,sal)

Select employee_id empid, hire_date hiredate

Salary sal, manager_id mgr

From employees

Where employee_id > 200

Conditional INSERT ALL: exampl

Conditional INSERT ALL

Insert all

When hiredate

< ' 01-JAN-95 ' then into emp_history values(EMPID,HIREDATE,SAL) when comm is not null then into emp_sales values(EMPID,COMM,SAL) select employee_id empid, hire_date hiredate, salary sal, commission_pct comm from employees 有条件INSERT FIRST insert first when salary < 5000 then into sal_low values (employee_id, last_name, salary) when salary between 5000 and 10000 then into sal_mid values (employee_id, last_name, salary) else into sal_high values (employee_id, last_name, salary) select employee_id, last_name, salary from employees 旋转INSERT 将销售记录从非关系型数据库表中设置为关系格式

Insert all

Into sales_info values (employee_id,week_id,sales_MON)

Into sales_info values (employee_id,week_id,sales_TUE)

Into sales_info values (employee_id,week_id,sales_WED)

Into sales_info values (employee_id,week_id,sales_THUR)

Into sales_info values (employee_id,week_id, sales_FRI)

Select employee_id, week_id, sales_MON, sales_TUE

Sales_WED, sales_THUR,sales_FRI

From sales_source_data

Restriction condition

Multiple table insert statements can only be executed on tables, not on views or materialized views

Cannot execute multiple table insert statements on remote tables

Table collection expressions cannot be used

Cannot exceed 999 target columns

In a RAC environment or when the target table is an index organization table or there is an BITMAP index on the target table, multiple table insert statements cannot be executed in parallel

Multiple table insert statements do not support execution plan stability

Subqueries in multiple table insert statements cannot use sequences.

MERGE statement

Provide the function of updating, inserting and deleting data according to conditions

If the data exists, execute UPDATE, if it does not exist, then INSERT:

-avoid individual updates

-improved performance and ease of use

-very suitable for data warehouse

MERGE statement syntax

Using the merge statement, you can insert, update, or delete rows in a table based on conditions.

Merge into table_name table_alias

Using (table | view | sub_query) alias

On (join condition)

When matched then

Update set

Col1 = col1_val

Col2 = col2_val

When not matched then

Insert (column_list)

Values (column_values)

Concurrency: exampl

Insert or update rows in the COPY_ EMP3 table that match the EMPLOYEES.

Merge into copy_emp3 c

Using (select * from employees) e

On (c.employee_id = e.employee_id)

When matched then

Update set

C.first_name = e.first_name

C.last_name = e.last_name

...

Delete where (e.commission_pct is not null)

When not matched then

Insert values (e.employee_id, e.first_name, e.last_name

E.email, e.phone_number, e.hire_date, e.job_id

E.salary, e.commission_pct, e.manager_id

E.department_id)

Merge row exampl

Truncate table copy_emp3

Select * from copy_emp3

Merge into copy_emp3 c

Using (select * from employees) e

On (c.employee_id = e.employee_id)

When matched then

Update set

C.first_name = e.first_name

C.last_name = e.last_name

...

Delete where (e.commission_pct is not null)

When not matched then

Insert values (e.employee_id, e.first_name,...

Track changes in data

Flashback version query example

Select salary from employees3 where employee_id = 107

Update employees3 set salary = salary * 1.30 where employee_id = 107

Commit

Select salary from employees3 versions between scn minvalue and maxvalue where employee_id = 107

VERSIONS BETWEEN clause

Select versions_starttime "start_date"

Versions_endtime "end_date"

Salary

From employees

Versions between scn minvalue

And maxvalue

Where last_name = 'Lorentz'

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.