Oracle data Update, transaction processing, data pseudo-column

First, update the operation of the data

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

CREATE TABLE myemp as SELECT * from EMP;

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

1. Data increase

If you want to implement data additions now, you can do so 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 data types are processed separately:

• Add numbers: Write numbers directly, for example: 123;
• Add string: String should use "'" declaration;
• Add Date data:
  • The first: You can write a string in the format of an existing string, such as "' 1 July-December-80 '";
  • The second type: Use the To_date () function to change the string to DATE data;
  • The third type: If the time set is the current system time, then use sysdate;

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

Example: adding data--complete syntax writing

INSERT into Myemp (empno,ename,hiredate,sal,mgr,job,comm) VALUES (8888, ' Zhang San ', To_date (' 1960-08-17 ', ' yyyy-mm-dd '), 8000,7369, ' cleaner ', 1000);

INSERT into Myemp (Empno,ename,hiredate,sal,mgr,job,deptno) VALUES (8889, ' John Doe ', sysdate,3000,7369, ' cleaners ', 30);

Example: adding data--simple syntax, not writing added column names

INSERT into Myemp VALUES (8889, ' John Doe ', ' cleaners ', 7369,sysdate,3000,null,30);

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 grammar, 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 in the following syntax:

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

Example: Update employee number is 7369 base pay of 5000, bonus 2000, job change to manager, hire date changed to today

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

Example: wages for all people changed to 7500

UPDATE myemp SET sal=7500;

If the update does not add to the update operation, it means that all the data is updated, but this is absolutely not possible, if the current table of large amounts of data, this update will take a long time, and performance can be 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 the update, if there is no write delete condition, it means to delete all the data;

Example: Delete all employees hired in 1987

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

Be sure to keep in mind 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 the operation, as little as possible to use, including in the future in the development of the system, for all the deletion before the actual proposed to give you a confirmation of the prompt box, to prevent users from mistakenly deleted.

II. Transaction Processing

For data table operations, it is clear that the query is more secure than the update operation, because the update operation may have errors that cause the update operation to be completed incorrectly as required.

However, in many cases the update may have multiple instructions to complete, for example: Bank transfer:

1. Determine if there is 5000W on the account of A;
2. Determine if the B account exists and the status is normal;
3. Remove 5000W from the account of A;
4. Add 5000W to account B;
5. 5000 of the fees paid to the bank;

The above five operations are a whole, can be understood as a complete business, if this 3rd error, then other operations? All operations should no longer be performed, and return to the most primitive state, and this process is the operation of the transaction.

All transaction operations are for each session, in the Oracle database, every user connected to the database is called a session, each session is independent of each other, there is no communication, Each session has its own transaction control, while the main use of the transaction control is two commands:

• Rollback of a transaction: ROLLBACK, update the operation back to the original point;
• commit a transaction: commit, the actual issue of the update, once submitted cannot be rolled back;

However, there will be some problems, such as: a session in the updating of the data table when the transaction has not been committed, the other session is not updated, you must wait for the previous session to submit before you can;

This kind of problem can be called a deadlock in a big way, but there are many types of deadlocks in Oracle, which are not much related to the developer and are the responsibility of the DBA.

All data updates must be subject to transaction control.

Third, data pseudo-column

Data pseudo-Columns refer to columns that the user does not need to process, but data columns that are maintained by Oracle, with two data pseudo-columns in Oracle: ROWNUM, ROWID;

1, ROWNUM

RowNum from the meaning of the word should be expressed is the line number, in fact rownum for each displayed record automatically as the query generates line numbers, for example, the following two code observation:

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

The rownum line number is not permanently fixed at this time;

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

Is every time the dynamic regeneration, then since the rownum, the following can be realized in the data part of the display;

Example: query Top 5 Records

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

Example: query 6-10 Records

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

SELECT rownum,empno,ename,job,hiredate,sal from Empwhere ROWNUM between 6 and 10;

This time does not return any data, because rownum is not the real column, but to really realize this query, the idea is: First query 10 records, then display the last 5 records, to rely on sub-query completion.

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

If you follow this idea now, the following can give you the function of paging in the future program.

Example: show Top 5 records

The current page (currentpage) is 1;

The length of records displayed per page (linesize) is 5;

First page:

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

Example: displaying 5 records in the middle

The current page (currentpage) is 2;

The length of records displayed per page (linesize) is 5;

Second page:

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

Example: display the contents of the third page, currentpage=3,linesize=10;

SELECT * FROM (      select ROWNUM m,empno,ename,job,hiredate,sal from emp      WHERE rownum<=15) tempwhere temp.m> 10;

The above program is the core code of the paging display operation.

2, ROWID

ROWID represents the number of physical addresses that each row of data holds, for example, to observe the following query:

SELECT Rowid,deptno,dname,loc from Dept;

Many records are returned at this time:

ROWID                  DEPTNO dname          LOC-------------------------------------------------------aaal+xaaeaaaaanaaa         10 ACCOUNTING     NEW Yorkaaal+xaaeaaaaanaab         dallasaaal+xaaeaaaaanaac,         SALES          Chicagoaaal+xaaeaaaaanaad         OPERATIONS     BOSTON

The rowid of each record is not duplicated, so even if the data contents of all columns in the table are duplicated, ROWID is not duplicated, and a rowid is an example of rowid composition, for example: AAAL+XAAEAAAAANAAA

• Data Object number: Aaal+x;
• Relative file number: AAE;
• Data block number: Aaaaan;
• Data line number: AAA;

Interview questions: delete duplicate records from a table

There is a lot of duplication of information in the project due to poor management, and in order to illustrate the problem, there are several records added to the table below.

INSERT INTO Dept (DEPTNO,DNAME,LOC) VALUES (+, ' "", ' DALLAS '); INSERT into dept (Deptno,dname,loc) VALUES (31, ' Sales ', ' CHICAGO '); INSERT into dept (Deptno,dname,loc) VALUES (+, ' SALES ', ' CHICAGO '); INSERT INTO dept (deptno,dname,loc VALUES (' OPERATIONS ', ' BOSTON '), insert INTO dept (DEPTNO,DNAME,LOC) VALUES ("OPERATIONS", ' BOSTON '); INSERT into Dept (Deptno,dname,loc) VALUES (+, ' OPERATIONS ', ' BOSTON '); COMMIT;

The data displayed at this point is:

Sql> select * from dept;    DEPTNO dname          LOC-------------------------------------        ten ACCOUNTING     NEW YORK       DALLAS        Sales          CHICAGO        OPERATIONS     BOSTON        DALLAS          CHICAGO        SALES          CHICAGO        OPERATIONS     BOSTON        OPERATIONS     BOSTON        43 OPERATIONS     Boston has selected 10 rows.

It is now required to delete all duplicate records, preserving the oldest added record content.

Final Display effect:

Sql> select * from dept;    DEPTNO dname          LOC-------------------------------------        ten ACCOUNTING     NEW YORK       DALLAS        SALES          CHICAGO        OPERATIONS     BOSTON

First, according to the way the query, the first to find all the duplicate data, duplicate data is easier, according to the department name and location group, then the number of statistics, if the number is greater than 1, it is repeated.

SELECT dname,loc,min (ROWID) from Deptgroup by Dname,lochaving COUNT (DEPTNO) >1;

The three rowid returned at this time are compared with the earliest rowid.

ROWID                  DEPTNO dname          LOC-------------------------------------------------------aaal+xaaeaaaaanaaa         ACCOUNTING     NEW Yorkaaal+xaaeaaaaanaab         dallasaaal+xaaeaaaaanaac,         SALES          Chicagoaaal+xaaeaaaaanaad         OPERATIONS     BOSTON

Sql> SELECT dname,loc,min (ROWID)  2 from  dept  3  GROUP by Dname,loc  4 have  COUNT (DEPTNO) >1;dname          LOC           MIN (ROWID)---------------------------------------------SALES          CHICAGO       aaal+ Xaaeaaaaanaacresearch       DALLAS        aaal+xaaeaaaaanaaboperations     BOSTON        Aaal+xaaeaaaaanaad

The data is inserted early, ROWID is small, so the next step must first determine the ROWID can not be deleted, remove the previous limit (COUNT () >1), the department name and location of the group, take out each of the smallest rowid;

SELECT MIN (ROWID) from Deptgroup by Dname,loc;

The above data is deleted, so write the DELETE statement below:

DELETE from Deptwhere ROWID No in (      SELECT MIN (ROWID) from      dept      GROUP by Dname,loc); COMMIT;

At this point is done according to ROWID, and this topic is ROWID now the only can see the role.

Among all the pseudo-columns, only rownum is the most important part of later development, must master, and for rowID know on the line, do not have to do too deep understanding.

Oracle data Update, transaction processing, data pseudo-column