Oracle study notes 7 data operations

1. append data
Insert into table [(column [, column...])]
Values (value [, value...]);

Example 1: insert into dept (deptno, dname, Loc)
Values (50, 'Development ', 'detroit ');

Example 2: insert into Dept
Values (50, 'Development ', 'detroit ');

Example 3: insert into dept (deptno, dname)
Values (50, 'development ');

1) insert a record containing null values
-Implicit method: Ignore this column in the column name list.
Example: insert into dept (deptno, dname)
Values (60, 'mis ');

-Display Method: Specify the null keyword.
Example: insert into Dept
Values (50, 'development', null );

2) Insert special values
-The sysdate function records the current date and time.
Example: insert into EMP (empno, ename, job, Mgr, hiredate, Sal, comm, deptno)
Values (7196, 'green', 'salesman ', 7782, sysdate, 2000, null, 10 );

3) Substitution variable insert value
Example: insert into dept (deptno, dname, Loc)
Values (& department_id, '& department_name', '& location ');

Result:
Enter the value of the replacement variable in the script to be executed:

Variable value
Department_id: 80
Department_name: Education
Location: Atlanta

4) copy data from another table
Inser into table [column (, column)]
Subquery;

Example: insert into test
Select *
From Dept
Where loc = 'New York ';

2. Modify Table Data
Update table
Set column = value [, column = value]
[Where condition];

Example 1: Use the WHERE clause to specify the record to be updated
Update EMP
Set deptno = 20
Where e-mapreduce = 7782;
Example 2: If you want to update all records, the where clause can be ignored.
Update employee
Set deptno = 20;
Example 3: update the information corresponding to an employee numbered 7499 with the employee's name and department number.
Method 1:
Update EMP
Set (job, deptno) =
(Select job, deptno
From EMP
Where e-mapreduce = 7499)
Where e-mapreduce = 7698;
Method 2:

Update EMP
Set deptno =
(Select deptno
From EMP
Where e-mapreduce = 7499)
Job =
(Select job
From EMP
Where e-mapreduce = 7499)
Where e-mapreduce = 7698;

3. Delete table records
Delete [from] table_name
[Where condition];

Example 1: specifying the records to be deleted in the WHERE clause

Delete from EMP
Where job = 'cler ';

Example 2: If the WHERE clause is omitted, all records in the table will be deleted.
Delete from EMP;

Example 3: Use a subquery in a Delete statement to delete a table record from another table
Delete from EMP
Where deptno =
(Select deptno
From Dept
Where dname = 'New York ');

Example 4: integrity constraints error when deleting a record
Delete from Dept
Where deptno = 10;
Result:
Delete from Dept
*

Error is located in row 1st:
ORA-02292: full constraints violated (Scott. fk_deptno)-subrecord log found

4. Merge statement
-Update or insert data to a table based on conditions
-If a row exists, update it. If a new row exists, insert it.
Merge into table_name table_alias
Using (talble | View | sub_query) alias
On (join condition)
When matched then
Update set
Col1 = col1_val,
Col2 = col2_val
When not matched then
Insert (column_list)
Values (column_values)

Example:
Merge into copy_dept C
Using DEPT E
On (C. deptno = E. deptno)
When matched then
Update set
C. dname = E. dname,
C. Loc = E. Loc
When not matched then
Insert values (E. deptno, E. dname, E. LOC );

5. database transactions
-Started with the execution of the first executable SQL statement
-End in the following cases:
-Commit or rollback
-DDL or DCL statements are executed.
-The user exits.
-System Crash
1) Advantages of commit and rollback
-Ensure data consistency
-Data changes can be previewed before permanent changes are generated.
-Aggregate a series of logic-related operations

2) Implicit Transaction Processing
-It is automatically submitted in the following cases:
-Execute DDL statements
-Execute the DCl statement.
-Exit SQL * Plus normally without explicit execution of commit or rollback
-Automatic rollback will occur when an SQL * Plus exception ends or the system fails.

3) data status before the execution of commit or rollback
-The status before data changes can be restored.
-The current user can use the SELECT statement to verify the DML operation results.
-Other users cannot view DML operation results of the current user
-The affected records are locked, that is, other users cannot change the data in the affected records.

4) status after the execution of COMMIT
-Data changes in the Database become permanent
-The previous data status permanently disappears.
-All users can query the submitted results.
-The locked records are released and can be effectively operated by other users.
-All storage nodes are cleared.

Example: submit data
-Data changes
Update EMP
Set deptno = 10
Where e-mapreduce = 7782;
-Submit data changes
Commit;

5) roll back to a flag status
-Use the savepoint statement to create a tag status in the current transaction.
-Use the rollback to savepoint statement to roll back to the specified tag status.

For example, SQL> Update...
SQL> savapoint update_done;
Savepoint created.
SQL> insert...
SQL> rollback to update_done;
Rollback complete.

6) Statement-level rollback
-If a DML statement fails during execution, only the operations in this statement are rolled back.
-The Oracle Server completes an implicit node storage operation.
-All other changes remain
-The user should have a commit or rollback statement to explicitly terminate the transaction.

7) read consistency
-Read consistency ensures data query consistency at any time
-Data changes produced by one user do not conflict with those produced by another user.
-Ensure that the same data is:
-The Reader does not have to wait for the writer.
-The writer does not have to wait for the reader.

6. Lock
-Locking is a mechanism used to prevent multiple transaction colleagues from accessing the same resource and causing destructive interaction.
-Oracle locking
-Prevent the destructive interaction between concurrent transactions
-No user intervention required
-Minimum automatic usage limit
-The lock time is the same as the transaction duration.
-There are two basic modes
-Row-Level Lock)
-Share (Table-Level Lock)

Exercise
1. Add the following information to the EMP table:
Empno ename job Mgr hiredate Sal comm deptno
10 mirror manager 7839 3000 20

Insert into EMP
Values (10, 'mirerror', 'manager', 7839,
To_date ('1970-12-10 ', 'yyyy-mm-dd'), 1980, null, 20)

 

2. Add the following information to the dept table:

Deptno dname Loc
50 leader Shenyang

Insert into Dept
Values (50, 'Leader ', 'shenyang ');

3. the employee's salary updated to 10 is 3500, and the employment date is

Update EMP
Set sal = 3500
Where empno = 10;

4. Update the dept table department ID to Beijing.

Update Dept
Set loc = 'beijing'
Where deptno = 50;

6. Update the salaries of employees numbered 10 with employees numbered 7900

Update EMP
Set sal =
(Select Sal
From EMP
Where e-mapreduce = 7900)
Where empno = 10;

7. Delete employees working in Beijing

Delete
From Dept
Where deptno =
(Select deptno
From Dept
Where loc = 'beijing ');

8. Delete the Department Information numbered 50.

Delete
From Dept
Where deptno = 50;

9. Add the following information to the EMP table:
Empno ename job Mgr hiredate Sal comm deptno
10 mirror manager 7839 3000.00 20

Insert into EMP
Values (10, 'mirerror', 'manager', 7839,
To_date ('1970-12-10 ', 'yyyy-mm-dd'), 1980, null, 20 );

10. Submit the changes.

Commit;

11. Add the following information to the EMP table.
Empno ename job Mgr hiredate Sal comm deptno
11 word clerk 7782 1500.00 30

Insert into EMP
Value (11, 'word', 'manager', 7782,
To_date ('1970-01-12 ', 'yyyy-mm-dd'), 2001, null, 30 );

12. Set savepoint mark1

Savepoint mark1;

13. Change the Job Value of "empno" to "10" from "manager" to "clerk", and change the salary from 3000.00 to 1500.00.

Update EMP
Set job = 'cler', sal = 1500.00
Where empno = 10;

14. Set savepoint mark2

Savepoint mark2;

15. Delete all employees whose salaries are less than 1000 RMB

Delete
From EMP
Whree Sal <1, 1000;

16. Roll Back To savepoint mark2

Rollback to mark2;