Oracle_ Data Processing



Oracle_ Data Processing

① Data Manipulation language
DML (data manipulation language–) can be executed under the following conditions:
Inserting data into a table
Modifying existing data
Delete existing data
Querying existing data
A transaction is made up of DML statements that do several things

②insert statement syntax

1. Insert the data into the table using the INSERT statement.
INSERT into table [(column [, Column ...])]
VALUES (value [, value ...]);
Use this syntax to insert only one piece of data into a table at a time.

2. Copying data from other tables
Add a subquery to 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];

You can update more than one data at a time.
Use the WHERE clause to specify the data that needs to be updated
If you omit the WHERE clause, 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 another 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.
If you omit the WHERE clause, 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. The database transaction consists of the following components:
One or more DML statements
A DDL (data definition language– language) statement
A DCL (Data Control language– language) statement

2. Start and end
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

Advantages of the 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
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.

6. 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.

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 can manipulate the data involved.
All save points are freed.

Oracle_ Data Processing