Học Oracle cơ bản (Bài 09-manipulate data)

§iÒu khiÓn d÷ liÖu Môc ®Ých Sau bµi nµy, b¹n cã thÓ: Miªu t¶ tõng lÖnh DML ChÌn (Insert) hµng vµo b¶ng CËp nhËt (Update) hµng trong b¶ng Xo¸ (Delete) hµng tõ b¶ng Qu¶n lý (Control) transactions Ng«n ngò ®iÒu khiÓn d÷ liÖu (DML) Mçi lÖnh DML ®­îc thùc hiÖn khi: ChÌn hµng míi vµo mét b¶ng Thay ®æi hµng trong mét b¶ng Xo¸ bá hµng ®ang cã khái b¶ng Mét transaction bao gåm mét tËp c¸c lÖnh DML t¹o nªn mét khèi logic khi lµm viÖc. Thªm mét hµng vµo b¶ng DEPT DEPTNO DNAME LOC ------ ---------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON DEPT DEPTNO DNAME LOC ------ ---------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON C©u lÖnh INSERT Thªm mét hµng vµo mét b¶ng nhê sö dông c©u lÖnh INSERT. ChØ mét hµng ®­îc thªm vµo t¹i mét thêi ®iÓm víi c©u lÖnh trªn. INSERT INTO table [(column [, column...])] VALUES (value [, value...]); Thªm hµng míi Danh s¸ch c¸c gi¸ trÞ ë trËt tù ngÇm ®Þnh cña c¸c cét trong b¶ng. Mét c¸ch tïy chän liÖt kª c¸c cét trong mÖnh ®Ò INSERT.  Bao hµm c¸c gi¸ trÞ ký tù vµ ngµy th¸ng trong c¸c dÊu ngoÆc ®¬n cao. SQL> INSERT INTO dept (deptno, dname, loc) 2 VALUES (50, 'DEVELOPMENT', 'DETROIT'); 1 row created. Thªm hµng víi c¸c gi¸ trÞ Null NgÇm ®Þnh: kh«ng hiÖn tªn cét trong danh s¸ch cét. SQL> INSERT INTO dept (deptno, dname ) 2 VALUES (60, 'MIS'); 1 row created. C¸ch chØ râ: ChØ râ tõ NULL. SQL> INSERT INTO dept 2 VALUES (70, 'FINANCE', NULL); 1 row created. Thªm nh÷ng gi¸ trÞ ®Æc biÖt Hµm SYSDATE ghi l¹i ngµy th¸ng vµ thêi gian hiÖn t¹i. SQL> INSERT INTO emp (empno, ename, job, 2 mgr, hiredate, sal, comm, 3 deptno) 4 VALUES (7196, 'GREEN', 'SALESMAN', 5 7782, SYSDATE, 2000, NULL, 6 10); 1 row created. Thªm c¸c gi¸ trÞ ngµy th¸ng riªng Thªm mét hµng trong b¶ng emp SQL> INSERT INTO emp 2 VALUES (2296,'AROMANO','SALESMAN',7782, 3 TO_DATE('FEB 3, 97', 'MON DD, YY'), 4 1300, NULL, 10); 1 row created. KiÓm tra l¹i sau khi thªm. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ------- -------- ---- --------- ---- ---- ------ 2296 AROMANO SALESMAN 7782 03-FEB-97 1300 10 Thªm c¸c gi¸ trÞ nhê biÕn thay thÕ T¹o mét giao diÖn t­¬ng t¸c cã sö dông c¸c tham sè thay thÕ trong SQL*PLUS SQL> INSERT INTO dept (deptno, dname, loc) 2 VALUES (&department_id, 3 '&department_name', '&location'); Enter value for department_id: 80 Enter value for department_name: EDUCATION Enter value for location: ATLANTA 1 row created. T¹o mét Script víi c¸c c©u nh¾c cña m×nh ACCEPT l­u gi¸ trÞ trong biÕn PROMPT hiÖn c©u nh¾c cña ng­êi dïng ACCEPT department_id PROMPT 'Please enter the - department number:' ACCEPT department_name PROMPT 'Please enter - the department name:' ACCEPT location PROMPT 'Please enter the - location:' INSERT INTO dept (deptno, dname, loc) VALUES (&department_id, '&department_name', '&location'); Sao c¸c hµng tõ mét b¶ng kh¸c ViÕt lÖnh INSERT víi Subquery. Kh«ng sö dông tõ VALUES. C¸c cét trong Insert ph¶i phï hîp víi c¸c cét trong Subquery. SQL> INSERT INTO managers(id, name, salary, hiredate) 2 SELECT empno, ename, sal, hiredate 3 FROM emp 4 WHERE job = 'MANAGER'; 3 rows created. Thay ®æi d÷ liÖu trong b¶ng EMP EMP EMPNO ENAME JOB ... DEPTNO 7839 KING PRESIDENT 10 7698 BLAKE MANAGER 30 7782 CLARK MANAGER 10 7566 JONES MANAGER 20 ... EMPNO ENAME JOB ... DEPTNO 7839 KING PRESIDENT 10 7698 BLAKE MANAGER 30 7782 CLARK MANAGER 10 7566 JONES MANAGER 20 ... C©u lÖnh UPDATE Thay ®æi hµng ®ang cã víi c©u lÖnh UPDATE. Thay ®æi h¬n mét hµng t¹i mét thêi ®iÓm, nÕu yªu cÇu. UPDATE table SET column = value [, column = value, ...] [WHERE condition]; Thay ®æi hµng trong b¶ng ChØ ra hµng hay c¸c hµng cÇn thay ®æi trong mÖnh ®Ò WHERE. TÊt c¶ c¸c hµng trong b¶ng ®­îc thay ®æi nÕu b¹n kh«ng cã mÖnh ®Ò WHERE SQL> UPDATE emp 2 SET deptno = 20 3 WHERE empno = 7782; 1 row updated. SQL> UPDATE employee 2 SET deptno = 20; 14 rows updated. Thay ®æi nhiÒu hµng nhê SUBQUERY nhiÒu cét SQL> UPDATE emp 2 SET (job, deptno) = 3 (SELECT job, deptno 4 FROM emp 5 WHERE empno = 7499) 6 WHERE empno = 7698; 1 row updated. Thay ®æi Job vµ deptno cña ng­êi cã empno lµ 7698 nh­ cña ng­êi cã empno lµ 7499. Thay ®æi c¸c hµng dùa trªn b¶ng kh¸c Sö dông c¸c subquery trong mÖnh ®Ò UPDATE ®Ó thay ®æi c¸c hµng SQL> UPDATE employee 2 SET deptno = (SELECT deptno 3 FROM emp 4 WHERE empno = 7788) 5 WHERE job = (SELECT job 6 FROM emp 7 WHERE empno = 7788); 2 rows updated. UPDATE emp * ERROR at line 1: ORA-02291: integrity constraint (USR.EMP_DEPTNO_FK) violated - parent key not found SQL> UPDATE emp 2 SET deptno = 55 3 WHERE deptno = 10; Thay ®æi hµng: lçi rµng buéc Kh«ng cã phßng 55 Xãa hµng tõ mét b¶ng DEPT DEPTNO DNAME LOC ------ ---------- -------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON 50 DEVELOPMENT DETROIT 60 MIS ... LÖnh DELETE B¹n cã thÓ xo¸ c¸c hµng ®ang cã tõ mét b¶ng nhê sö dông lÖnh DELETE DELETE [FROM] table [WHERE condition]; ChØ ra c¸c hµng cÇn xo¸ trong mÖnh ®Ò WHERE. TÊt c¶ c¸c hµng trong b¶ng bÞ xo¸ nÕu kh«ng cã mÖnh ®Ò WHERE. Deleting Rows from a Table SQL> DELETE FROM department 2 WHERE dname = 'DEVELOPMENT'; 1 row deleted. SQL> DELETE FROM department; 4 rows deleted. Xo¸ hµng dùa trªn b¶ng kh¸c Sö dông c¸c subquery SQL> DELETE FROM employee 2 WHERE deptno = 3 (SELECT deptno 4 FROM dept 5 WHERE dname ='SALES'); 6 rows deleted. Xo¸ c¸c hµng: Lçi rµng buéc SQL> DELETE FROM dept 2 WHERE deptno = 10; DELETE FROM dept * ERROR at line 1: ORA-02292: integrity constraint (USR.EMP_DEPTNO_FK) violated - child record found B¹n kh«ng xo¸ ®­îc hµng cã chøa kho¸ chÝnh (primary key) ®­îc sö dông nh­ kho¸ ngo¹i (foreign key) trong b¶ng kh¸c. Database Transactions Bao gåm c¸c lÖnh sau: C¸c lÖnh DML t¹o lªn sù thay ®æi d÷ liÖu Mét lÖnh DDL Mét lÖnh DCL Database Transactions B¾t ®Çu khi lÖnh SQL ®Çu tiªn ®­îc thùc hiÖn. KÕt thóc khi mét trong c¸c sù kiÖn sau x¶y ra: COMMIT hoÆc ROLLBACK ®­îc thùc hiÖn. LÖnh DDL hoÆc DCL ®­îc thùc hiÖn (commit tù ®éng). Ng­êi dïng ra khái ch­¬ng tr×nh HÖ thèng bÞ háng ­u ®iÓm cña c¸c lÖnh COMMIT vµ ROLLBACK §¶m b¶o d÷ liÖu æn ®Þnh Xem tr­íc sù thay ®æi tr­íc khi thay ®æi thËt T¹o c¸c ho¹t ®éng liªn quan thµnh mét nhãm. Qu¶n lý Transactions Transaction Savepoint A Savepoint B COMMIT UPDATE INSERT Commit tù ®éng x¶y ra sau khi: Mét lÖnh DDL ®­îc ph¸t ra. Mét lÖnh DCL ®­îc ph¸t ra. Tho¸t khái SQL*Plus b×nh th­êng. Rollback tù ®éng x¶y ra khi: Tho¸t khái SQL*Plus kh«ng b×nh th­êng. HÖ thèng bÞ háng Xö lý ngÇm ®Þnh Transaction Tr¹ng th¸i cña d÷ liÖu tr­íc khi COMMIT hoÆc ROLLBACK Tr¹ng th¸i lóc tr­íc cña d÷ liÖu cã thÓ ®­îc kh«i phôc. ChØ Ng­êi dïng hiÖn t¹i cã thÓ xem kÕt qu¶ cña ho¹t ®éng DML nhê lÖnh SELECT. C¸c hµng ®ang ¶nh h­ëng bÞ kho¸. Ng­êi kh¸c kh«ng thÓ thay ®æi d÷ liÖu nh÷ng hµng ®anh bÞ ¶nh h­ëng. Tr¹ng th¸i cña D÷ liÖu sau khi COMMIT Thay ®æi cña d÷ liÖu ®­îc thùc hiÖn cè ®Þnh trong CSDL. Tr¹ng th¸i lóc tr­íc cña d÷ liÖu bÞ mÊt vÜnh viÔn. Mäi ng­êi cã thÓ xem kÕt qu¶. C¸c hµng bÞ ¶nh h­ëng kh«ng bÞ kho¸. TÊt c¶ c¸c ®iÓm l­u (savepoint) bÞ xo¸. Thùc hiÖn Commiting d÷ liÖu SQL> UPDATE emp 2 SET deptno = 10 3 WHERE empno = 7782; 1 row updated. Thùc hiÖn thay ®æi d÷ liÖu. Thùc hiÖn Commit nh÷ng thay ®æi. SQL> COMMIT; Commit complete. Tr¹ng th¸i cña d÷ liÖu sau ROLLBACK Bá tÊt c¶ nh÷ng thay ®æi ®ang ®îi nhê lÖnh ROLLBACK. Nh÷ng thay ®æi d÷ liÖu bÞ xo¸. Tr¹ng th¸i lóc tr­íc cña d÷ liÖu ®­îc kh«i phôc. Xo¸ bá c¸c kho¸ trªn c¸c hµng ®ang bÞ ¶nh h­ëng. SQL> DELETE FROM employee; 14 rows deleted. SQL> ROLLBACK; Rollback complete. Bá nh÷ng thay ®æi cho tíi ®iÓm ®¸nh dÊu T¹o ®iÓm ®¸nh dÊu trong transaction hiÖn t¹i nhê lÖnh SAVEPOINT. Bá nh÷ng thay ®æi ®Õn ®iÓm ®¸nh dÊu nhê lÖnh ROLLBACK TO SAVEPOINT. SQL> UPDATE... SQL> SAVEPOINT update_done; Savepoint created. SQL> INSERT... SQL> ROLLBACK TO update_done; Rollback complete. Møc lÖnh cña Rollback NÕu mét lÖnh DML háng, th× chØ lÖnh ®ã ®­îc kh«i phôc (rolled back). Oracle Server ¸p dông ®iÓm l­u ngÇm ®Þnh (DDL). Mäi thay ®æi kh¸c vÉn gi÷ nguyªn. Nªn sö dông COMMIT hoÆc ROLLBACK râ rµng. Sù æn ®Þnh khi ®äc Sù æn ®Þnh khi ®äc ®¶m b¶o c¸ch nh×n æn ®Þnh vÒ d÷ liÖu t¹i mäi thêi ®iÓm. Sù thay ®æi cña ng­êi dïng nµy kh«ng m©u thuÉn víi sù thay ®æi ng­êi dïng kh¸c.. Sù æn ®Þnh khi ®äc ®¶m b¶o trªn cïng d÷ liÖu: Ng­êi ®äc kh«ng ®îi ng­êi viÕt Ng­êi viÕt kh«ng ®îi ng­êi ®äc. Thùc hiÖn sù æn ®Þnh khi ®äc UPDATE empSET sal = 2000 WHERE ename = 'SCOTT'; Datablocks Rollbacksegments changedand unchanged data before change“old” data User A User B Readconsistentimage SELECT *FROM emp; Kho¸ (giíi thiÖu) Kho¸ cña Oracle: Ng¨n chÆn sù ¶nh h­ëng ph¸ háng cÊu tróc gi÷a c¸c transaction cïng lóc. Kh«ng yªu cÇu ng­êi dïng can thiÖp. Møc thÊp nhÊt cña c¸c h¹n chÕ ®­îc sö dông tù ®éng. §­îc l­u gi÷ trong suèt transaction. Tãm t¾t Miªu t¶ Thªm mét hµng vµo b¶ng Thay ®æi hµng trong b¶ng Xo¸ hµng trong b¶ng Lµm cho thay ®æi ®ang ®îi ®­îc thùc hiÖn. Cho phÐp kh«i phôc ®Õn ®iÓm l­u Bá nh÷ng thay ®æi ®ang ®îi LÖnh INSERT UPDATE DELETE COMMIT SAVEPOINT ROLLBACK Thùc hµnh ChÌn hµng vµo b¶ng Thay ®æi vµ xo¸ hµng trong b¶ng §iÒu khiÓn Transaction