Data update, transaction processing, data pseudo columns

Update operation of data

DML operation syntax, in addition to the query also has the data Library update operations, the data update operations mainly refers to: Add, modify, delete data, but in view of the EMP table will continue to use, so the following first copy the EMP table, enter the following instructions:

CREATE TABLE myemp as SELECT * from EMP;

This syntax is supported in Oracle and is not the same in other databases.

1. Increase in data

If you want to implement the increase in data now, you can do this by using the following syntax:

INSERT into table name [(Field 1, Field 2,...)] Values (value 1, value 2,...);

If you want to add data, the following types of data are handled separately: add numbers: Write numbers directly, for example: 123; add strings: Strings should use "'" declaration; Add date data: First: You can write strings in the format of an existing string, for example, "' 1 July-December-80 ' "; the second: Using the To_date () function to convert a string to DATE data; Third: If the time set is the current system time, use sysdate;

But for the increase in data there are two types of operation format: complete, simple-type;

Example: Adding data--complete grammar writing

INSERT into Myemp (empno,ename,hiredate,sal,mgr,job,comm)
VALUES (8888, ' John ', To_date ('1960- (a), ' Yyyy-mm-dd '),8000,7369, ' cleaners ',1000);

INSERT into Myemp (empno,ename,hiredate,sal,mgr,job,deptno)
VALUES (8889, ' Dick ', Sysdate,3000 ,7369, ' cleaners ';

Example: adding data--simple syntax, adding column names without writing

INSERT into Myemp VALUES (8889, ' Dick ', ' cleaners ',7369, sysdate,3000, NULL,);

Obviously, although the simple syntax code is less, but this kind of operation is not desirable, so in the actual development, no one will write simple syntax, so it is not conducive to the maintenance of the program, all write a complete operation.

2. Data modification

If you want to modify the data already in the table, you can do so by using the following syntax:

Update table name SET Update Field 1 = Update value 1, update Field 2 = Update Value 2,... [WHERE Update condition (s)];

Example: Update employee number is 7369 base salary 5000, bonus 2000, position modified as manager, hire date modified to today

UPDATE myemp SET sal=5000, comm=, job= ' MANAGER ', hiredate=sysdate WHERE empno= 7369;

Example: Everyone's salary is changed to 7500

UPDATE myemp SET sal=7500;

If updates are not added, it means updating all of the data, but this is absolutely not possible, and if the amount of data in the table now is large, the time spent on this update is quite long and performance is significantly reduced.

3. Data deletion

After the data is no longer needed, you can delete the data using the following syntax:

Delete from table name [WHERE Delete condition (s)];

As with updates, all data is deleted if the deletion condition is not written;

Example: Delete all employees hired in 1987

DELETE from Myemp WHERE to_char (hiredate, ' yyyy ') =1987;

It is important to remember that if there are no matching criteria when the data is deleted, the updated record is "0", including the update operation.

Example: deleting all records in a table

DELETE from Myemp;

Generally for the deletion of operations, as little as possible to use, including later in the development of the system, for all the delete operation before the actual recommendation that everyone should first give a confirmation of the box to prevent the user mistakenly deleted.

II. Transaction Processing

For the operation of the datasheet, it is obvious that the query is more secure than the update operation, because the update operation is likely to have an error, resulting in the failure to complete the update operation exactly as required.

But in many cases it is possible to update a number of instructions together, such as: In the form of bank transfers: to determine whether A's account is 5000W, to determine if B accounts exist and the status is normal, to remove 5000W from the account of a and to increase 5000W in the account B; Payment of fees to banks 5000;

The above five operations is a whole, can be understood as a complete business, if this 3rd error, then the other operation. All operations should no longer execute, and revert to the original state, and this process is the operation of the transaction.

All transaction operations are for each session, in the Oracle database, each of the users connected to the database is called a sessions, each is independent of each other, there will be no communication, Each session has its own transaction control, while the transaction control mainly uses two commands: Rollback of the transaction: ROLLBACK, update operation back to Origin; transaction commit: Commit, real update action, cannot be rolled back once committed;

However, there will also be some problems, such as: A session in the Update data table has not committed the transaction, other sessions are not updated, must wait before the session before the session can be submitted;

This problem can be described in large terms as deadlocks, but there are many types of deadlocks among Oracle, which are not too much of a relationship with the developer and are the responsibility of the DBA.

All data updates must be controlled by the transaction.

Iii. Data Pseudo-columns

A data pseudo column is a column that the user does not need to process, but a data column maintained by Oracle, with two data pseudo columns in Oracle: RowNum, ROWID;

1, RowNum

RowNum from the meaning of the word should represent the line number, in fact rownum for each of the records will automatically generate line numbers with the query, for example, the following two code observation:

SELECT rownum,empno,ename,job,hiredate,sal from EMP;

At this time the RowNum line number is not permanent fixed;

SELECT rownum,empno,ename,job,hiredate,sal from emp WHERE deptno=;

is a dynamic regeneration every time, now that you have the rownum, the following can be achieved part of the display of data;

Example: Query first 5 records

SELECT rownum,empno,ename,job,hiredate,sal from emp
WHERE rownum<=5;

Example: Query 6-10 Records

According to normal thinking must be directly to the between ... the judgment of and;

SELECT rownum,empno,ename,job,hiredate,sal from emp
6;

This time did not return any data, because rownum is not the real column, and to really realize this query, the idea is: First query the first 10 records, and then show the next 5 records, to rely on subquery completed.

SELECT * FROM (
      select RowNum m,empno,ename,job,hiredate,sal from emp
      WHERE rownum<=) Temp
WHERE temp.m>5;

If you follow this line of thought now, the following will give you the ability to implement the paging in your future program.

Example: Show First 5 records

The current page (currentpage) is 1;

The record length (linesize) shown on each page is 5;

First page:

SELECT * FROM (
      select RowNum m,empno,ename,job,hiredate,sal from emp
      WHERE rownum<=5) Temp
WHERE temp.m>0;

Example: displaying 5 records in the middle

The current page (currentpage) is 2;

The record length (linesize) shown on each page is 5;

Second page: