Oracle Foundation Note Eight

Chapter Eighth Data processing

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

A transaction is made up of DML statements that do several things

2.INSERT statement syntax

Use the INSERT statement to insert data into the table.

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

INSERT intotable [(column [, Column ...])]

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

2.1 Inserting data

Add a new value for each column.

Lists the values of individual columns in the default order of the columns.

Arbitrarily list column names and their values in the INSERT clause.

Character and date data should be enclosed in single quotes.

For example:

INSERT into departments (department_id, Department_name,

manager_id, location_id)

VALUES (+, ' public relations ', 100, 1700);

INSERT into Employees (EMPLOYEE_ID,LAST_NAME,EMAIL,HIRE_DATE,JOB_ID)

VALUES (' Tom ', ' [email protected] ', to_date (' 2012-3-21 ', ' yyyy-mm-dd '), ' sa_rap ');

1 row created.

2.2 Inserting null values into the table

Implicit: Omit the value of the column in the Column name table.

INSERT intodepartments (department_id,

Department_name)

VALUES(the ' Purchasing ');

Display mode: Specify a null value in the VALUES clause.

INSERT intodepartments

VALUES(+, ' Finance ', null, NULL);

2.3 Inserting the specified value

INSERT into employees (employee_id,

First_Name, last_name,

email, Phone_number,

Hire_date, job_id, salary,

commission_pct, manager_id,

DEPARTMENT_ID)

VALUES (113,

' Louis ', ' Popp ',

' Lpopp ', ' 515.124.4567 ',

Sysdate, ' Ac_account ', 6900,

NULL, 205, 100);

2.4 Copy the data from other tables and add a subquery to the INSERT statement.

INSERT into EMP2

SELECT *

From Employees

WHERE department_id = 90;

INSERT into Sales_reps (ID, name, salary, commission_pct)

SELECT employee_id, last_name, salary, commission_pct

From Employees

WHERE job_id like '%rep% ';

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. Update the data using the UPDATE statement.

UPDATETable

SETcolumn = value [, column = value, ...]

[WHERE condition];

3.1 Use the WHERE clause to specify the data that needs to be updated.

UPDATE Employees

SET department_id = 70

WHERE employee_id = 113;

If you omit the WHERE clause, all data in the table will be updated

UPDATE copy_emp

SET department_id = 110;

Rows updated.

3.2 Using subqueries in UPDATE statements

Title: The work and salary of the updated employee 114th is the same as that of employee number NO. 205.

UPDATE Employees

SET job_id = (SELECT job_id

From Employees

where employee_id = 205),

Salary = (SELECT salary

From Employees

WHERE employee_id = 205)

WHERE employee_id = 114;

Title: Adjust the department_id of employees who job_id the same staff as employee_id 200 to department_id for employee_id 100 of employees.

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);

Note Data integrity errors in updates

4.DELETE statement, delete the data from the table using the DELETE statement.

DELETE from table

[WHERE condition];

Use the WHERE clause to delete the specified record.

DELETE from Departments

WHERE department_name = ' Finance ';

If you omit the WHERE clause, all data in the table will be deleted

DELETE from Copy_emp;

Rows deleted.

4.2 Use a subquery in Delete to make the deletion based on data in another table.

Title: Remove the Department ID from the EMP1 table with the public character in the DEPT1 department name

DELETE from EMP1

WHERE department_id =

(SELECT department_id

From DEPT1

WHERE department_name like '%public% ');

5. Database transactions

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

The benefits of 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.

Roll back to the 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.

UPDATE ...

SavePoint Update_done;

SavePoint created.

INSERT ...

ROLLBACK to Update_done;

Rollback complete.

This article from the "Ah Cheng Blog" blog, reproduced please contact the author!

Oracle Foundation Note Eight