Oracle_ Data Processing



Oracle_ Data Processing

① Data Manipulation language
DML (data manipulation language–) can run under the following conditions:
Inserting data into a table
Change existing data
Delete existing data
Querying existing data
A transaction is made up of DML statements that have completed several tasks

②insert statement syntax

1. Insert the data into the table using the INSERT statement.
INSERT into table [(column [, Column ...])]
VALUES (value [, value ...]);
Using such a syntax can only insert a single piece of data into a table at a time.

2. Copying data from other tables
Add a subquery in the INSERT statement.

INSERT into table1 [(column [, Column ...])]
select [(Column [, Column ...])]
From table2
Where condition

You do not have to write the VALUES clause.
The list of values in the subquery should correspond to the column names in the INSERT clause

3. Create a script
Use the & variable in the SQL statement to specify the column values.
The & variable is placed in the values clause.

③update statement syntax
1. Update the data using the UPDATE statement.
UPDATE table
SET column = value [, column = value, ...]
[WHERE condition];

Ability to update more than one data at a time.

Use the WHERE clause to specify the data that needs to be updated
Assuming that the WHERE clause is omitted, all data in the table will be updated

2. Using subqueries in UPDATE statements
Use subqueries in update to make updates based on data in one table.

UPDATE copy_emp
SET department_id = (SELECT department_id
From Employees
WHERE employee_id = 100)
WHERE job_id = (SELECT job_id
From Employees
WHERE employee_id = 200);
3. Data integrity errors in the update
UPDATE Employees
SET department_id = 55
WHERE department_id = 110;

DEPARTMENT_ID is a foreign key, but 55 does not exist in the reference table and violates the FOREIGN KEY constraint

④delete statements
1. Delete the data from the table using the DELETE statement.
DELETE from table
[WHERE condition];
Use the WHERE clause to delete the specified record.

Assuming that the WHERE clause is omitted, all data in the table will be deleted

2. Using subqueries in DELETE
DELETE from EMP1
WHERE department_id =
(SELECT department_id
From DEPT1
WHERE department_name like '%public% ');

3. Data integrity errors in the Delete
DELETE from Departments
WHERE department_id = 60;

Note: department_id = 60 is already referenced and cannot be deleted

⑤ Database Transactions
Transactions: A set of logical operations units that transform data from one state to another.
1. 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

2. Start and end of a transaction
Start with the operation of the first DML statement

End with one of the following:
COMMIT or ROLLBACK statement
DDL statements (self-commit)
User session ends normally
System Abnormal termination

Advantages of 3.COMMIT and rollback statements
Using the Commit and rollback statements, we can:
ensure data integrity.
The data changes are previewed before being submitted.

Groups logically related operations.

4. Rollback to a retention point
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.

5. Transaction processes
Submit your own initiative to run in the following scenario:
DDL statements.
DCL statement.
The session ends normally without committing or rolling back with a commit or ROLLBACK statement.
An unexpected end of session or a system exception can cause you to roll back on your own initiative.

6. Data status before committing or rolling back
The state of the data before the change can be restored.
Users running DML operations can query for previous corrections through the SELECT statement
Other users cannot see the 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.

7. 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 are able to manipulate the data involved.
All save points are released.

Oracle_ Data Processing