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

LÖnh thao t¸c d÷ liÖu Néi dung M« t¶ c©u lÖnh DML ChÌn c¸c hµng vµo b¶ng CËp nhËt c¸c hµng trong b¶ng Xãa c¸c hµng trong b¶ng §iÒu khiÓn c¸c transaction Ng«n ng÷ thao t¸c d÷ liÖu (DML) Mét c©u lÖnh DML thùc thi khi: Thªm mét hµng vµo b¶ng Söa ch÷a c¸c hµng trong b¶ng Xãa hµng trong b¶ng Mét transaction bao gåm mét tËp c©u lÖnh DML t¹o thµnh mét ®¬n vÞ c«ng viÖc Logic. 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 trong b¶ng nhê c©u lÖnh INSERT ChØ mét hµng ®­îc INSERT vµo b¶ng t¹i mét thêi ®IÓm INSERT INTO table [(column [, column...])] VALUES (value [, value...]); ChÌn mét hµng míi ChÌn mét hµng míi chøa gi¸ trÞ cho mçi cét trong b¶ng Danh s¸ch gi¸ trÞ theo trËt tù mÆc ®Þnh cña b¶ng Danh s¸ch c¸c cét lµ tïy chän C¸c d÷ liÖu kiÓu character vµ date cÇn ®Æt trong dÊu nh¸y ®¬n SQL> INSERT INTO dept (deptno, dname, loc) 2 VALUES (50, 'DEVELOPMENT', 'DETROIT'); 1 row created. ChÌn mét hµng víi gi¸ trÞ NULL Bá qua cét cã gi¸ trÞ null trong danh s¸ch cét SQL> INSERT INTO dept (deptno, dname ) 2 VALUES (60, 'MIS'); 1 row created. ChØ ®Þnh gi¸ trÞ null SQL> INSERT INTO dept 2 VALUES (70, 'FINANCE', NULL); 1 row created. ChÌn gi¸ trÞ ®Æc biÖt Hµm sysdate tr¶ vÒ ngµy vµ thêi gian hÖ thèng 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. ChÌn gi¸ trÞ ngµy chØ ®Þnh Thªm mét nh©n viªn míi. 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 b¶n ghi. EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ------- -------- ---- --------- ---- ---- ------ 2296 AROMANO SALESMAN 7782 03-FEB-97 1300 10 ChÌn gi¸ trÞ nhê biÕn thay thÕ T¹o script t­¬ng t¸c nhê tham sè thay thÕ 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 script víi lêi nh¾c cña ng­êi dïng ACCEPT L­u gi¸ trÞ trong biÕn PROMPT hiÓn thÞ lêi nh¾c 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'); LÊy hµng tõ b¶ng kh¸c ViÕt c©u lÖnh INSERT b»ng c©u lÖnh subquery Kh«ng sö dông mÖnh ®Ò VALUES Sè cét trong mÖnh ®Ò SELECT ph¶i b»ng sè cét trong mªnh ®Ò insert 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 Söa c¸c hµng ®· tån t¹i b»ng c©u lÖnh update  Cã thÓ update nhiÒu h¬n mét hµng trong mét thêi ®iÓm UPDATE table SET column = value [, column = value, ...] [WHERE condition]; CËp nhËt hµng trong b¶ng ChØ ®Þnh c¸c hµng ®­îc cËp nhËt nhê mÖnh ®Ò WHERE TÊt c¶ c¸c hµng trong b¶ng ®­îc cËp nhËt nÕu bá qua 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. CËp nhËt víi mÖnh ®Ò subquery trªn 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. CËp nhËt c¸c tr­êng job vµ department cña nh©n viªn 7698 vµ víi gi¸ trÞ cña t­¬ng øng cña nh©n viªn 7499. CËp nhËt b¶ng dùa vµo gi¸ trÞ cña b¶ng kh¸c Sö dông c¸c subquery ®Ó cËp nhËt gi¸ trÞ cña b¶ng dùa vµo gi¸ trÞ b¶ng kh¸c 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; CËp nhËt lçi do constraint Department number 55 does not exist ChÌn gi¸ trÞ víi Default Oracle 9i cho phÐp sö dông tõ kho¸ DEFAULT trong c©u lÖnh INSERT hay UPDATE statements: INSERT INTO EMP (EMPNO, ENAME, DEPTNO) VALUES (8000,’MIKE’,DEFAULT); UPDATE EMP SET COMM = DEFAULT; LÖnh MERGE – Võa cã kh¶ n¨ng Insert võa cã kh¶ n¨ng Update. MERGE INTO T1 USING T2 ON (T1.C9=T2.C9) WHEN MATCHED THEN UPDATE SET T1.C1=T2.C2, T1.C2=T2.C2 ... WHEN NOT MATCHED THEN INSERT (C1,C2, ...) VALUES (C1,C2, ...); Insert nhiÒu table LÖnh Insert trªn nhiÒu table: Sö dông mét c©u lÖnh DML ®Ó insert d÷ liÖu lªn nhiªu b¶ng. UNCONDITIONAL (Kh«ng ®iÒu kiÖn) INSERT ALL INTO T1 (C1, C2, ...) VALUES (C1, C2, ...) INTO T2 (C1, C2, ...) VALUES (C1, C2, ...) ... SELECT C1, C2, ... FROM T9; CONDITIONAL (Cã ®iÒu kiÖn) INSERT [ALL|FIRST] WHEN c1 = 1 THEN INTO T1 (C1, C2, ...) VALUES (C1, C2, ...) WHEN c1 = 2 THEN INTO T2 (C1, C2, ...) VALUES (C1, C2, ...) WHEN c2 = 3 THEN INTO T3 (C1, C2, ...) VALUES (C1, C2, ...) ... SELECT C1, C2, ... FROM T9; – FIRST: chØ insert ®èi víi c©u lÖnh ®©u tiªn tho¶ m·n ®iÒu kiÖn; ALL: insert cho tÊt c¶ c¸c c©u lÖnh tho¶ m·n ®iÒu kiÖn. Lo¹i bá hµng 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 ... C©u lÖnh DELETE Cã thÓ xãa bá hµng trong b¶ng nhê lÖnh DELETE [FROM] table [WHERE condition]; ChØ ®Þnh c¸c hµng ®­îc xãa nhê mÖnh ®Ò where  xãa toµn bé c¸c b¶n nghi khi bá qua mÖnh ®Ò where Xãa hµng tõ b¶ng SQL> DELETE FROM department 2 WHERE dname = 'DEVELOPMENT'; 1 row deleted. SQL> DELETE FROM department; 4 rows deleted. Xãa d÷ liÖu trong b¶ng Sö dông subquery ®Ó xãa hµng trong b¶ng SQL> DELETE FROM employee 2 WHERE deptno = 3 (SELECT deptno 4 FROM dept 5 WHERE dname ='SALES'); 6 rows deleted. Lçi xãa hµng d÷ liÖu cã constraint 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 that contains a primary key You cannot delete a row that is used as a foreign key in another table. Database Transaction(giao dÞch c¬ së d÷ liÖu) Bao gåm mét trong c©u lÖnh sau: Mét c©u lÖnh DML Mét c©u lÖnh DDL Mét c©u lÖnh DCL Database Transaction B¾t ®Çu khi c©u lÖnh SQL thùc thi KÕt thóc khi ®iÒu kiÖn sau x¶y ra: C©u lÖnh COMMIT hay ROLLBACK x¶y ra C¸c c©u lªnh DDL, DCL thùc thi(tù ®éng commit) User tho¸t ra khái sql plus System ®ãng Nh÷ng lîi ®iÓm cña c©u lÖnh commit vµ rollback §¶m b¶o tÝnh nhÊt qu¸n cña d÷ liÖu Xem d÷ liÖu thay ®æi tr­íc khi chuyÓn thay ®æi ®ã thËt sù Nhãm logic nhãm to¸n h¹ng §iÒu khiÓn giao dÞch Transaction Savepoint A Savepoint B COMMIT UPDATE INSERT Qu¸ tr×nh tù ®éng commit x¶y ra khi Ch¹y c©u lÖnh DDL Ch¹y c©u lÖnh DCL Tho¸t khái SQL*Plus b×nh th­êng Mét c©u lÖnh Rollback tù ®éng ®­a ra khi ®ãng SQL*Plus kh«ng b×nh th­êng, hay hÖ thèng bÞ háng . Xö lý c¸c giao dÞch b¾t buéc Tr¹ng th¸i cña d÷ liÖu tr­íc khi Rollback hay Commit Tr¹ng th¸i tr­íc cña d÷ liÖu cã thÓ ®­îc phôc håi. User hiÖn thêi cã thÓ xem kÕt qu¶ cña c©u lÖnh DML Nh÷ng user kh¸c kh«ng thÓ xem kÕt qu¶ cña user hiÖn thêi C¸c hµng bÞ t¸c ®éng sÏ bÞ khãa(locked), c¸c user kh¸c kh«ng thÓ thay ®æi d÷ liÖu cña c¸c hµng bÞ khãa . Tr¹ng th¸i cña d÷ liÖu sau COMMIT D÷ liÖu thay ®æi lµ permanent trong database TÊt c¶ c¸c user cã thÓ xem d÷ liÖu C¸c khãa trªn c¸c hµng ®­îc më . Toµn bé c¸c savepoint ®­îc xãa. Commit d÷ liÖu SQL> UPDATE emp 2 SET deptno = 10 3 WHERE empno = 7782; 1 row updated. T¹o ra sù thay ®æi T¹o ra sù thay ®æi SQL> COMMIT; Commit complete. Tr¹ng th¸i sau khi ROLLBACK Lo¹i bá toµn bé thay ®æi b»ng lÖnh rollback D÷ liÖu thay ®æi lµ cã thÓ kh«i phôc Tr¹ng th¸i tr­íc cña d÷ liÖu ®­îc kh«i phôc. Khãa trªn c¸c hµng ®­îc më. SQL> DELETE FROM employee; 14 rows deleted. SQL> ROLLBACK; Rollback complete. Rollback Back tíi mét thêi ®iÓm ®¸nh dÊu  T¹o mét thêi ®iÓm rollback trong transaction b»ng lÖnh savepoint. Roll back tíi thêi ®iÓm ®¸nh dÊu bëi lÖnh ROLLBACK TO SAVEPOINT SQL> UPDATE... SQL> SAVEPOINT update_done; Savepoint created. SQL> INSERT... SQL> ROLLBACK TO update_done; Rollback complete. Møc cña c©u lÖnh Rollback NÕu mét c©u lÖnh DML háng trong qu¸ tr×nh thùc thi th× chØ c©u lÖnh ®ã ®­îc rollback Oracle server thiÕt lËp mét savepoint kh«ng b¾t buéc. C¸c user cÇn kÕt thóc c¸c giao dÞch b¾t buéc b»ng c©u lÖnh COMMIT hay ROLLBACK §äc nhÊt qu¸n §äc nhÊt qu¸n ®¶m b¶o c¸ch nh×n nhÊt qu¸n cña d÷ liÖu trong toµn bé thêi gian. Nh÷ng thay ®æi bëi mét user sÏ kh«ng ¶nh h­ëng tíi thay ®æi cña user kh¸c §äc nhÊt qu¸n ®¶m b¶o trªn cïng mét d÷ liÖu : Ng­êi ®äc d÷ liÖu kh«ng ®îi ng­êi viÕt d÷ liÖu Ng­êi viÕt d÷ liÖu kh«ng ®îi ng­êi ®äc d÷ liÖu ThiÕt lËp ®äc nhÊt qu¸n UPDATE empSET sal = 2000 WHERE ename = 'SCOTT'; Datablocks Rollbacksegments changedand unchanged data before change“old” data User A User B Readconsistentimage SELECT *FROM emp; Tæng kÕt Description Adds a new row to the table Modifies existing rows in the table Removes existing rows from the table Makes all pending changes permanent Allows a rollback to the savepoint marker Discards all pending data changes Statement INSERT UPDATE DELETE COMMIT SAVEPOINT ROLLBACK