Oracle Database (ix)--Data processing

(a) Insert data mode one

Insert statement syntax

INSERT into table [(column [, Column ...])]

VALUES (value [, value ...]);

Use this syntax to insert only one piece of data into a table at a time

If you assign a value in the order of the columns, you can not write the column name

Otherwise, to write the column name, for non-empty columns must be copied

Mode two: Copy data from other tables

Method Three: Create a script (learn)

• Use the & variable in the SQL statement to specify the column value 0.
• The & variable is placed in the values clause.

Using a script, after running the SQL statement, a bullet box appears, and after all the values of the box are filled, the data is saved in the database.

(ii) updating of data

UPDATE table

SET column = value [, column = value, ...]

[WHERE condition];

--Update the work and salary of employee No. 114th to make it the same as employee number NO. 205

(1) Check whether the work and wages of employee No. 114th and No. 205 are the same

(2) Update information

(3) Re-check whether the work and wages of staff number 114th and No. 205 are the same

--Adjust the same as the EMPLOYEE_ID 200 employee job_id, the employee's department_id is employee_id 100 of the Employees department_id

Data integrity errors in the update

Department 55 does not exist, so it cannot be modified and violates the integrity constraints

(iii) Deletion of data

DELETE from table

[WHERE condition];

• To delete a specified record by using the WHERE clause
• If you omit the WHERE clause, all data in the table will be deleted

Using subqueries in Delete

--Delete the department ID with the public character in the DEPT1 department name

Data integrity errors in Delete

If there are other tables that use the data to be deleted, this data cannot be deleted

(iv) Database services

Transactions: A set of logical operations units that transform data from one state to another

Database transactions are made up of the following sections:

• One or more DML statements
• A DDL (data definition language– language) statement
• A DCL (Data Control language– language) statement

Start with execution of the first DML statement

End with one of the following:

• COMMIT or ROLLBACK statement
• DDL statements (auto-commit)
• User session ends normally
• System Abnormal termination

1. Commit and rollback

Advantages

• ensure data integrity.
• The data changes are previewed before being submitted.
• Groups logically related operations.

2. Reserved points

• Use the savepoint statement to create a savepoint in the current transaction.
• Use the ROLLBACK to savepoint statement to roll back to the saved point that was created.

3. Transaction process

Autocommit is performed in the following cases:

• DDL statements.
• DCL statement.
• The session ends normally without committing or rolling back with a commit or ROLLBACK statement.

An automatic rollback occurs because of an unexpected session end or a system exception.

4. Data status before committing or rolling back

• The state of the data before the change can be restored.
• Users performing DML operations can query for previous corrections through the SELECT statement
• Other users cannot see changes made by the current user until the current user ends the transaction.
• The rows involved in the DML statement are locked and cannot be manipulated by other users.

5. Status of data after submission

• Changes to the data have been saved in the database.
• The data before the change has been lost.
• All users can see the results.
• The lock is released and other users can manipulate the data involved.
• All save points are freed.

6. Data state after rollback

• Data changes are canceled.
• The state of the data before the modification is restored.
• The lock is released.

(v) Summary

Using DML statements to change data and transaction control

Oracle Database (ix)--Data processing