Explain the oracle database DML statements and explain the oracledml statements

Explain the oracle database DML statements and explain the oracledml statements

INSERT

The INSERT command can INSERT a row or a set of rows created by the SELECT statement.

If you do not specify the value to insert to a column, the position representation is used.

A good programming habit is that location notation is not used.

UPDATE

Used to change existing rows.

Affects one or more rows.

The range of the affected sets is determined by the WHERE clause.

A single update command cannot affect rows in multiple tables.

The UPTDAE command follows all the constraints defined in the table.

DELETE

Delete a row or a group of rows from the table based on the WHERE clause.

All rows in the table are deleted without the WHERE clause.

TRUNCATE

DDL, not a user transaction.

The table contains any active DML commands, and the DDL command fails.

Reset the high watermark line.

The physical location of a table is part of the table definition stored in the data field. When a table is created, the data file allocates a fixed space to the table. The data dictionary uses a high waterline to track how much space is used. The following is used and the above is not used. Reset the high waterline to the beginning of the initial space to delete all rows.

MERGE

If the row in the source data (table, view, and subquery) already exists in the target table, you can UPDATE the target row or replace it, or keep the target unchanged. If the row in the source data is not in the target table, you can INSERT it.

The source data is transferred once for three operations.

The UPDATE or INSERT clause is optional.

The UPDATE clause can be followed by the DELETE clause to remove unnecessary rows.

You can add a WHERE clause to the UPDATE and INSERT clauses.

INSTER ALL

Insert multiple rows into multiple tables using one statement.

Fill multiple tables with the source data at a time to reduce the burden on the database.

WITH

It can be considered that a temporary table TT is constructed in advance before the query is performed, and then it can be used for further analysis and processing multiple times.

The ease of use of SQL is added. If multiple subqueries are constructed, the structure is clearer. More importantly, the structure is "one analysis and multiple use ", this is why the performance is improved to achieve the goal of "less read.

DML statement failed

Syntax Error

Reference nonexistent objects or columns

Access permission

Constraint Violation

Space Problems