ORACLE _ Data Processing and oracle Data Processing

ORACLE _ Data Processing and oracle Data Processing
Zookeeper

ORACLE _ Data Processing

① Data manipulation language
DML (Data Manipulation Language-Data Manipulation Language) can be executed under the following conditions:
Insert data to a table
Modify existing data
Delete existing data
Query existing data
A transaction is composed of DML statements that complete several tasks.

② INSERT statement syntax

1. Use the INSERT statement to INSERT data into the table.
Insert into table [(column [, column...])]
VALUES (value [, value...]);
With this syntax, only one data entry can be inserted into the table at a time.

2. Copy 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 Value List in the subquery should correspond to the column name in the INSERT clause.

3. Create a script
Use & variable to specify column values in SQL statements.
& Put the variable in the VALUES clause.

③ UPDATE statement syntax
1. Use the UPDATE statement to UPDATE data.
UPDATE table
SET column = value [, column = value,...]
[WHERE condition];

Multiple data records can be updated at a time.
Use the WHERE clause to specify the data to be updated
If the WHERE clause is omitted, all data in the table will be updated.

2. Use the subquery in the UPDATE statement
Use a subquery in update to make updates based on the 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 updates
UPDATE employees
SET department_id = 55
WHERE department_id = 110;

Department_id refers to the foreign key, but 55 does not exist in the reference table. It violates the foreign key constraint.

④ DELETE statement
1. Use the DELETE statement to DELETE data from the table.
Delete from table
[WHERE condition];
Use the WHERE clause to delete a specified record.
If the WHERE clause is omitted, all data in the table will be deleted.

2. Use subquery in DELETE
Delete from emp1
WHERE department_id =
(SELECT department_id
FROM dept1
WHERE department_name LIKE '% Public % ');

3. data integrity error during Deletion
Delete from orders ments
WHERE department_id = 60;

Note: department_id = 60 cannot be deleted because it has been referenced.

⑤ Database transactions
Transaction: A group of logical operation units that convert data from one state to another.
1. database transactions are composed of the following parts:
One or more DML statements
A ddl (Data Definition Language-Data Definition Language) Statement
A dcl (Data Control Language-Data Control Language) Statement

2. start and end
Starting with the execution of the first DML statement

One of the following ends:
COMMIT or ROLLBACK statement
DDL Statement (automatically submitted)
User session ends normally
System exception termination

3. Advantages of COMMIT and ROLLBACK statements
Using the COMMIT and ROLLBACK statements, we can:
Ensure data integrity.
Preview data changes before they are submitted.
Groups logically related operations.

4. Roll Back to the reserved point
Use the SAVEPOINT statement to create a save point in the current transaction.
Use the rollback to savepoint statement TO roll back TO the created storage point.

5. Transaction Process
Automatic submission is executed in the following cases:
DDL statement.
DCL statement.
If you do not use the COMMIT or ROLLBACK statement to submit or roll back, the session ends normally.
Session ends abnormally or system exceptions cause automatic rollback.

6. Data status before submission or rollback
The data status before the change can be restored.
Users who execute DML operations can use the 'select' statement to query the previous corrections.
Other users cannot see the changes made by the current user until the current user ends the transaction.
The Rows involved in DML statements are locked and cannot be operated by other users.

7. Status of submitted data
Data changes have been saved to the database.
The data before the change has been lost.
All users can see the results.
The lock is released, and other users can operate on the data involved.
All save points are released.

Oracle Data Processing

You can use the row-to-column conversion function. The script is as follows:
Insert into othertable (zddm, cjsj, f1110101, f1110102, f1110103) select zddm, cjsj, sum (f1110101) f1110101, sum (f1110102) f1110102, sum (f1110103) f1110103 from (select zddm, cjsj, decode (xdm, '000000', xsj, 0) f1110101, decode (xdm, '000000', xsj, 0) f1110102, decode (xdm, '000000', xsj, 0) f1110103 from table_name) a group by. zddm, z. cjsj -- create table othertable asselect zddm, cjsj, sum (f1110101) values, sum (f1110102) f1110102, sum (f1110103) f1110103 from (select zddm, cjsj, decode (xdm, '000000', xsj, 0) f1110101, decode (xdm, '000000', xsj, 0) f1110102, decode (xdm, '000000', xsj, 0) f1110103 from table_name) a group by. zddm, z. cjsj and above can be converted in this way when the number of data item codes is fixed. If the number of data item codes is uncertain, dynamic SQL is required. It is best to write a stored procedure and execute it every day.
If you have any questions, please ask. I hope this will help you.


Oracle lab report

Lab report format is very standard