Oracle note (7) data update, transaction processing, and data pseudo Columns

Oracle note (7) data update, transaction processing, data pseudo column 1. Data Update operations in the DML operation syntax, in addition to queries, there are also database update operations for data, the data update operation mainly refers to adding, modifying, and deleting data. However, considering that the emp table will continue to be used later, copy the emp table and enter the following command: create table myemp as select * FROM emp; this syntax is supported in Oracle, and different databases.
1. If you want to add data now, use 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 should be processed separately: Add a number: Write a number directly, for example: 123; add a string at www.2cto.com: the string should be declared; add DATE data: First, you can write a string in the format of an existing string, for example, '17-August-80'. Second, use TO_DATE () the function changes the string to DATE type data. The third type is SYSDATE if the set time is the current system time. However, there are two operation formats for adding data: complete and simple; example: add data-complete syntax writing
Insert into myemp (empno, ename, hiredate, sal, mgr, job, comm) VALUES (8888, 'zhang san', TO_DATE ('2017-08-17 ', 'yyyy-mm-dd'), 1000, 8889, 'cleaner',); insert into myemp (empno, ename, hiredate, sal, mgr, job, deptno) VALUES, 'lily', SYSDATE, 8889, 'cleaner', 30); example: add data-simple syntax, insert into myemp VALUES (, 'lily ', 'Cleaner ', 7369, SYSDATE, 3000, null, 30); obviously, although the simple syntax code is missing, this operation is not desirable, so in actual development, no one can write simple syntax, which is not conducive to program maintenance and complete operations.
2. modify data if you want to modify the existing data in the table, you can follow 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 the employee ID to 7369, the basic salary is 5000, the bonus is 2000, and the job is changed to MANAGER, modify the employment date to www.2cto.com UPDATE myemp SET sal = 5000, comm = 2000, job = 'manager', hiredate = sysdate where empno = 7369; example: the salary of all users is changed to 7500 UPDATE myemp SET sal = 7500. If no UPDATE operation is added during the UPDATE, all data is updated, but this approach is absolutely not desirable, if the current table contains a large amount of data, this update takes a long time and significantly reduces the performance. 3. When the data is no longer needed, you can use the following syntax to DELETE the data: delete from table name [where delete condition (s)]; same as update, if no deletion conditions are written, all data is deleted. Example: DELETE all employees hired on January 1, 1987 FROM myemp WHERE TO_CHAR (hiredate, 'yyyy') = 1987; remember that if no matching data exists during deletion, the updated record is "0", and the update operation is the same. Example: DELETE all records in a table FROM myemp. Generally, you can DELETE as few records as possible, including when you are developing a system later, we recommend that you provide a confirmation box before all deletion operations to prevent accidental deletion. Ii. Transaction Processing for data table operations, it is obvious that queries are more secure than update operations, because update operations may encounter errors, as a result, the update operation is not completed correctly according to the established requirements. However, in many cases, multiple instructions may be executed together. For example, the bank transfer method is used as an example to determine whether A has 5000 million items in an account; judge whether account B exists and whether the status is normal; remove 5000 million from account A; Increase 5000 million to account B; and pay 5000 of the Commission to the Bank; the above five operations are a whole and can be understood as a complete business. What if the third of these operations fails? All operations should not be executed and return to the original state. This operation flow is the transaction operation. All transaction processing operations are performed on each session. In the Oracle database, every user connected to the database is called a session, and each session is independent of each other, there will be no communication, and each session excludes its own transaction control. In the transaction control, two commands are used: Transaction ROLLBACK: ROLLBACK, and the update operation returns to the origin. transaction commit: COMMIT: The real update operation cannot be rolled back once submitted. However, some problems also occur. For example, a session has not committed a transaction while updating the data table, other sessions cannot be updated and can only be updated after the previous session is submitted. This problem can be called deadlock in the big aspect, but there are many types of deadlocks in Oracle, these do not have much to do with developers and are the responsibility of DBAs. All data updates are subject to transaction control. 3. Data pseudo-column data pseudo columns refer to data columns maintained by Oracle rather than being processed by users. There are two data pseudo columns in Oracle: ROWNUM and ROWID; 1. ROWNUMROWNUM indicates the row number in terms of the word meaning. In fact, ROWNUM automatically generates a row number for each record displayed with the query. For example, we can observe the following two codes: select rownum, empno, ename, job, hiredate, sal FROM emp; at this time, the ROWNUM row number is not permanently fixed; www.2cto.com select rownum, empno, ename, job, hiredate, sal FROM emp WHERE deptno = 30; is generated dynamically every time. Since ROWNUM is available, partial data display can be realized below. Example: query the first five records: select rownum, empno, ename, job, hiredate, sal FRO M empWHERE ROWNUM <= 5; example: Query 6-10 records. Normally,... Select rownum, empno, ename, job, hiredate, sal FROM empWHERE rownum between 6 AND 10; at this time, no data is returned because ROWNUM is not a real column, to implement such a query, the idea is to query the first 10 records and then display the last five records, which must be completed by subqueries. SELECT * FROM (select rownum m, empno, ename, job, hiredate, sal FROM emp where rownum <= 10) tempWHERE temp. m> 5. If you follow this idea, we can provide the page-based function implementation in the future program. Example: display the current page (currentPage) of the first five records is 1; the length (lineSize) of each page is 5; Page 1: SELECT * FROM (select rownum m, empno, ename, job, hiredate, sal FROM emp where rownum <= 5) tempWHERE temp. m> 0; example: display the current page (currentPage) of the five records in the middle is 2; the length (lineSize) of each page is 5; the second page: www.2cto.com SELECT * FROM (select rownum m, empno, ename, job, hiredate, sal FROM emp where rownum <= 10) tempWHERE temp. m> 5; example: display the content 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. ROWIDROWID indicates the number of the physical address of each row of data. For example, observe the following query: select rowid, deptno, dname, loc FROM dept; at this time, many records are returned: rowid deptno dname loc records ---------- --------------------- AAAL + ACCOUNTING 10 accounting new yorkaaal + ACCOUNTING 20 research dallasaaal + ACCOUNTING 30 SALES Chicago AAAL + limit 40 operations boston each record's ROWID will not be repeated, therefore, even if the data in all columns in the table is repeated, the ROWID will not be repeated. In addition, a ROWID is used as an example to describe the composition of the ROWID. For example: AAAL + XAAEAAAAANAAA www.2cto.com data object number: AAAL + X; relative file number: AAE; data block number: AAAAAN; data line number: AAA; interview questions: please delete the duplicate records in the Table. due to poor management in the project, a lot of duplicate information occurs. To illustrate the problem, add several records in the table below. Insert into dept (deptno, dname, loc) VALUES (21, 'Research ', 'Dallas'); insert into dept (deptno, dname, loc) VALUES (31, 'sales', 'Chicago '); insert into dept (deptno, dname, loc) VALUES (32, 'sales', 'Chicago'); insert into dept (deptno, dname, loc) VALUES (41, 'operations', 'boston '); insert into dept (deptno, dname, loc) VALUES (42, 'operations', 'boston '); insert into dept (deptno, dname, loc) VALUES (43, 'operations', 'bosto N '); COMMIT; www.2cto.com at this time, the data is displayed as: SQL> select * from dept; deptno dname loc ---------- ------------ ------------- 10 accounting new york 20 research dallas 30 sales chicago 40 operations boston 21 research dallas 31 sales chicago 32 sales chicago 41 operations boston 42 operations boston 43 operations boston www.2cto.com selected 10 rows. Now it is required to delete all repeated records and retain the earliest added records. Final display: SQL> select * from dept; DEPTNO DNAME LOC ---------- ----------------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON first follow the query method, first, find all the repeated data, and the repeated data is easier. Group by department name and location, and then calculate the number. If the number is greater than 1, it indicates repeated data. SELECT dname, loc, MIN (ROWID) FROM deptGROUP BY dname, locHAVING COUNT (deptno)> 1; at this time, the three rowids returned are compared with the earliest rowids. Rowid deptno dname loc quota ---------- --------------------- AAAL + quota 10 accounting new yorkaaal + quota 20 research dallasaaal + quota 30 SALES Chicago AAAL + limit 40 operations boston SQL> SELECT dname, loc, MIN (ROWID) 2 FROM dept www.2cto.com 3 group by dname, loc 4 having count (deptno)> 1; dname loc min (ROWID )----------------------------------------- ---- Sales chicago aaal + xaaeaaaaanaacresearch dallas aaal + xaaeaaaaanaaboperations boston aaal + XAAEAAAAANAAD data is inserted early and ROWID is small. Therefore, you must first determine the ROWID that cannot be deleted, remove the previous limit (COUNT ()> 1), which indicates grouping BY department name and location to retrieve every smallest ROWID; select min (ROWID) FROM deptGROUP BY dname, loc; the preceding data is deleted, so the following DELETE statement is written: delete from deptWHERE rowid not in (select min (ROWID) FROM dept group by dname, loc); COMMIT; at this time, www.2cto.com is based on ROWID, and this question is Only visible. Among all pseudo columns, only ROWNUM is the most important part in future development and must be mastered. You just need to know the ROWID, so you don't have to have a deep understanding of it.