Giáo trình SQL và PL SQL part 8

Chia sẻ: Mr Yukogaru | Ngày: | Loại File: PDF | Số trang:7

Thêm vào BST

Báo xấu

159
lượt xem 51
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Truyền dữ liệu trong từ điển dữ liệu: trung tâm của cơ sở dữ liệu Oracle la data dictionary . Data dictionary tự động được tạo ra khi cơ sở dữ liệu Oracle được tạo . Oracle cập nhật lên Data Dictionaary bằng các kệnh DDL (Data define language ).........

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Giáo trình SQL và PL SQL part 8

C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL Option REUSE STORAGE gi÷ l¹i khung ®Ó chøa, chØ xãa d÷ liÖu 12.3 D÷ liÖu trong tõ ®iÓn d÷ liÖu Trung t©m cña c¬ së d÷ liÖu ORACLE lµ data dictionary. Data dictionary tù ®éng ®−îc t¹o ra khi c¬ së d÷ liÖu ORACLE ®−îc t¹o. ORACLE cËp nhËt lªn data dictionary b»ng c¸c lÖnh DDL (Data Define Language). C¸c table cña tõ ®iÓn d÷ liÖu ®−îc t¹o ra b»ng lÖnh CREATE DATABASE vµ chØ ®−îc t¹o tõ user SYS. C¸c view trong tõ ®iÓn d÷ liÖu chøc c¸c th«ng tin d−íi d¹ng dÔ nh×n h¬n b¶ng. Cã c¸c d¹ng view lµ: • USER_xxx: lµ nh÷ng ®èi t−îng thuéc user , vÝ dô c¸c b¶ng ®−îc t¹o bëi user • ALL_xxx: lµ tÊt c¶ c¸c ®èi t−îng mµ user cã quyÒn truy nhËp • DBA_xxx: tÊt c¶ c¸c ®èi t−îng trong database • V$: C¸c thùc thi cña Server. Ngoµi ra cßn cã c¸c view quan träng kh¸c lµ: • DICTIONARY: Th«ng tin vÒ toµn bé c¸c table, view, snapshot trong tõ ®iÓn d÷ liÖu • TABLE_PRIVILEGES: Th«ng tin vÒ viÖc g¸n quyÒn trªn c¸c ®èi t−îng • IND: ®ång nghÜa cña USER_INDEX. Muèn hiÓn thÞ toµn bé th«ng tin vÒ c¸c table, view, snapshot trong tõ ®iÓn d÷ liÖu dïng lÖnh SELECT * FROM DICTIONARY; HiÓn thÞ cÊu cña USER_OBJECT DESCRIBE USER_OBJECT; HiÓn thÞ tÊt c¶ c¸c b¶ng m· user ®ã së h÷u: SELECT OBJECT_NAME FROM USER_OBJECT WHERE OBJECT_TYPE = ‘TABLE’; SELECT * FROM TAB; SELECT TABLE_NAME FROM USER_TABLE; HiÓn thÞ tÊt c¶ c¸c lo¹i ®èi t−îng trong tõ ®iÓn d÷ liÖu: SELECT DISTINCT OBJECT_TYPE FROM USER_OBJECTS; 12.4 Bµi tËp 1. Thªm column COMMENTS kiÓu LONG vµo b¶ng PROJECTS. Thªm column HOURS kiÓu NUMBER vµo b¶ng ASSIGNMENTS. 2. Sö dông view USER_OBJECTS hiÓn thÞ tÊt c¶ c¸c ®èi t−îng user së h÷u. 3. Thªm rµng buéc duy nhÊt (UNIQUE) cho 2 column PROJECT_ID vµ EMPNO cña b¶ng ASSIGNMENTS. 4. Xem c¸c th«ng tin vÒ c¸c rµng buéc trong USER_CONSTRAINTS. 5. Xem trong USER hiÖn t¹i cã tÊt c¶ bao nhiªu b¶ng. §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 54
C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL 13 C¸c lÖnh Thao t¸c d÷ liÖu kh¸c 13.1 ChÌn mét row vµo table §Ó chÌn mét row vµo table dïng lÖnh INSERT. Có ph¸p nh− sau: INSERT INTO tablename ([column, column, ...]) VALUES (value, value ...); VÝ dô INSERT INTO dept (depno, dname, loc) VALUES (50, 'MARKETING', 'SAN JOSE') ChÐp d÷ liÖu tõ table kh¸c INSERT INTO table [(column, column...)] SELECT select_list FROM table(s) VÝ dô INSERT INTO emp_tmp (ename, sal) SELECT ename, sal FROM emp WHERE sal > 1000 13.2 ChØnh söa d÷ liÖu §Ó chØnh söa d÷ liÖu dïng lÖnh UPDATE. Có ph¸p nh− sau : UPDATE table [alias] SET column [,column...] = [expr, subquery] [WHERE condition] VÝ dô 1 UPDATE emp SET job = 'SALEMAN', hiredate = sysdate, sal = sal * 1.1 WHERE ename = 'SCOTT'; VÝ dô 2 UPDATE emp SET comm = (SELECT comm FROM commission C WHERE C.empno = emp.empno) WHERE empno IN (SELECT empno FROM commission); VÝ dô 3 UPDATE emp a SET deptno = (SELECT deptno FROM dept WHERE loc = 'BOSTON'), (sal, comm) = (SELECT 1.1*AVG(sal),1.5*AVG(comm) FROM emp b WHERE a.deptno = b.deptno) WHERE deptno IN (SELECT deptno FROM dept WHERE loc = 'DALLAS' OR loc = 'DETROIT'); Chó thÝch: - CËp nhËt c¸c nh©n viªn ë Dallas hoÆc Detroit - Thay DEPTNO cña c¸c nh©n viªn nµy b»ng DEPTNO cña Boston - Thay l−¬ng cña mçi nh©n viªn b»ng l−¬ng trung b×nh cña bé phËn * 1.1 - Thay commission cña mçi nh©n viªn b»ng commission trung b×nh cña bé phËn * 1.5 13.3 Xãa dßng §Ó xãa dßng dïng lÖnh DELETE. Có ph¸p nh− sau: §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 55
C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL DELETE FROM table [WHERE condition] VÝ dô DELETE FROM emp WHERE deptno = 10; 13.4 Lçi rµng buéc d÷ liÖu Th«ng th−êng khi thùc hiÖn c¸c lÖnh thao t¸c d÷ liÖu hay gÆp ph¶i c¸c lçi rµng buéc toµn vÑn d÷ liÖu. C¸c lçi nµy xuÊt hiÖn khi cã c¸c rµng buéc tr−íc ®ã mµ d÷ liÖu nhËp vµo, chØnh söa hay khi xo¸ ®i kh«ng ®¶m b¶o c¸c ®iÒu kiÖn toµn vÑn. M· lçi: ORA_02292: INTEGRITY CONSTRAINT Sau ®ã b¸o tªn cña Constraint bÞ lçi. 13.5 LÖnh ®iÒu khiÓn giao dÞch Mét c©u lÖnh SQL cã thÓ gåm • LÖnh DML thao t¸c d÷ liÖu • LÖnh DDL ®Þnh nghÜa d÷ liÖu • LÖnh DCL ®iÒu khiÓn truy nhËp d÷ liÖu Mét giao dÞch b¾t ®Çu khi mét lÖnh SQL ®−îc thùc hiÖn Mét giao dÞch kÕt thóc mét trong c¸c tr−êng hîp sau: • COMMIT hoÆc ROLLBACK • C¸c lÖnh DDL vµ DCL thùc hiÖn (tù ®éng commit) • Lçi, tho¸t khái SQL*Plus, hÖ thèng bÞ down. Có ph¸p c¸c lÖnh ®iÒu khiÓn giao dÞch: COMMIT KÕt thóc giao dÞch hiÖn t¹i, thùc hiÖn c¸c chuyÓn ®æi d÷ liÖu SAVEPOINT name X¸c ®Þnh ®iÓm savepoint cña giao dÞch ROLLBACK [TO SAVEPOINT name] Quay l¹i d÷ liÖu ë ®iÓm SAVEPOINT hoÆc toµn bé giao dÞch. SET AUTO[COMMIT] ON/OFF Tù ®éng COMMIt khi thùc hiÖn c¸c lÖnh Insert, update, delete. VÝ dô: INSERT INTO DEPT VALUES (50,’TESTING’,’LAS VEGAS’); SAVEPOINT INSERT_DONE; UPDATE DEPT SET DNAME = ‘MARKETING’; ROLLBACK TO INSERT_DONE ; UPDATE DEPT SET DNAME = ‘MARKETING’ WHERE DNAME =’SALES’; COMMIT; §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 56
C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL 13.6 Bµi tËp 1. Thªm d÷ liÖu vµo b¶ng PROJECTS. PROJID 1 2 P_DESC WRITE C030 COURSE PROOF READ NOTES P_START_DATE 02-JAN-88 01-JAN-89 P_END_DATE 07-JAN-88 10-JAN-89 BUDGET_AMOUNT 500 600 MAX_NO_STAFF 1 1 2. Thªm d÷ liÖu vµo b¶ng ASSIGNMENTS. PROJID 1 1 2 EMPNO 7369 7902 7844 A_START_DATE 01-JAN-88 04-JAN-88 01-JAN-89 A_END_DATE 03-JAN-88 07-JAN-88 10-JAN-89 BILL_RATE 50.00 55.00 45.50 ASSIGN_TYPE WR WR PF HOURS 15 20 30 3. CËp nhËt tr−êng ASIGNMENT_TYPE tõ WT thµnh WR. 4. NhËp thªm sè liÖu vµo b¶ng ASSIGNMENTS. 14 Sequence vµ index 14.1 Sequence 14.1.1 T¹o Sequence Sequence lµ danh s¸ch tuÇn tù cña con sè, vµ ®−îc t¹o bëi Oracle sever. Sequence dïng ®Ó t¹o khãa chÝnh mét c¸ch tù ®éng cho d÷ lÖu. Sequence th−êng dïng ®Ó t¹o khãa chÝnh trong sinh m· tù ®éng. Cã thÓ dïng chung cho nhiÒu ®èi t−îng. Con sè sequence nµy cã chiÒu dµi tèi ®a lµ 38 sè. §Ó t¹o sequence, dïng lÖnh create nh− sau CREATE SEQUENCE sequence_name INCREMENT BY integer START WITH integer [MAXVALUE integer] [MINVALUE integer] [CYCLE/NO CYCLE]; Trong ®ã: INCREMENT BY : chØ ®Þnh kho¶ng c¸ch cña d·y sè tuÇn tù START WITH : ChØ ®Þnh sè ®Çu tiªn cña d·y sè tuÇn tù MAXVALUE : Gi¸ trÞ lín nhÊt cña d·y tuÇn tù MINVALUE : Gi¸ trÞ nhá nhÊt cña d·y tuÇn tù CYCLE/NO CYCLE: D·y tuÇn tù cã quay vßng khi ®Õn ®iÓm cuèi. MÆc ®Þnh lµ NO CYCLE VÝ dô: CREATE SEQUENCE sample_sequence INCREMENT 1 STRAT WITH 2 MAXVALUE 100; §Ó lµm viÖc víi c¸c sequence, dïng lÖnh SQL víi c¸c cét gi¶ sau CURRVAL Cho gi¸ tri hiÖn thêi cña sequence NEXTVAL T¨ng gi¸ tri hiÖn thêi cña sequence vµ cho gi¸ trÞ sau khi t¨ng ph¶i x¸c ®Þnh tªn sequence tr−íc currval vµ nextval §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 57
C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL sequence.CURRVAL sequence.NEXTVAL §Ó truy cËp c¸c sequence kh«ng thuéc schema hiÖn thêi, th× ph¶i chØ ra tªn schema schema.sequence.CURRVAL schema.sequence.NEXTVAL §Ó truy cËp c¸c sequence tõ xa, th× cßn ph¶i chØ ra datalink schema.sequence.CURRVAL@dblink schema.sequence.NEXTVAL@dblink Sö dông sequence CURRVAL vµ NEXTVAL cã thÓ ®−îc sö dông trong c¸c tr−êng hîp sau: • Trong danh s¸ch lùa chän cña c©u lÖnh SELECT • Trong mÖnh ®Ò VALUES cña c©u lÖnh INSERT • Trong mÖnh ®Ò SET cña c©u lÖnh UPDATE • Kh«ng ®−îc sö dông CURRVAL vµ NEXTVAL trong c¸c tr−êng hîp sau • Trong c©u hái con • Trong c¸c view vµ snapshot • Trong c©u lÖnh SELECT cã t¸c tö DISTINCT • Trong c©u lÖnh SELECT cã sö dông GROUP BY hay ORDER BY • Trong c©u lÖnh SELECT cã sö dông c¸c phÐp to¸n tËp hîp nh− UNION, INTERSET, MINUS • Trong mÖnh ®Ò WHERE cña c©u lÖnh SELECT • GÝa trÞ DEFAULT cña cét trong c©u lÖnh CREATE TABLE hay ALTER TABLE • Trong ®iÒu kiÖn cña rµng buéc CHECK 14.1.2 Xo¸ vµ söa sequence Söa b»ng lÖnh: ALTER SEQUENCE sequence_name INCREMENT BY integer START WITH integer [MAXVALUE integer] [MINVALUE integer] [CYCLE/NO CYCLE]; Xo¸ b»ng lÖnh: DROP SEQUENCE sequence_name ; 14.2 Index Index lµ mét cÊu tróc c¬ së d÷ liÖu, ®−îc sever sö dông ®Ó t×m mét row trong b¶ng mét c¸ch nhanh chãng. Index bao gåm mét key value ( mét cét (column) trong hµng (row) ) vµ rowid. Có ph¸p: CREATE [UNIQUE]] INDEX index_name ON TABLE ( column [,column...]); §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 58
C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL • Dïng index ®Ó query cho nhanh. • Dïng Index khi mµ viÖc lÊy d÷ liÖu
C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL VÝ dô 1 CREATE VIEW emp_view AS SELECT empno, ename, sal FROM emp WHERE deptno = 10; VÝ dô 2 CREATE VIEW dept_summary (name, minsal, maxsal, avsal) AS SELECT dname, min(sal), max(sal), avg(sal) FROM emp, dept FROM emp, dept WHERE emp.deptno = dept.deptno GROUP BY dname; VÝ dô 3 CREATE VIEW dept_view AS SELECT eame, sal*12 Annsal FROM emp WHERE deptno = 20 WITH CHECK OPTIION CONSTRAINT dept_check; Xãa c¸c view ChØ nh÷ng ng−êi t¹o view míi cã quyÒn DROP DROP VIEW dept_view; View cã thÓ thùc hiÖn c¸c lÖnh SQL sau • Select • Insert (insert trªn view còng ¶nh h−ëng lªn table) • Update (¶nh h−ëng lªn table) • Comment Tuy nhiªn cã nh÷ng rµng buéc sau: • Kh«ng thÓ insert, update trªn view, khi query cña view chøa c¸c to¸n tö join, set, distinct, group by, group. • Kh«ng thÓ nµo insert, update trªn view, nÕu nh− trong view cã dïng with check option. • Kh«ng thÓ nµo insert trªn view, trªn table cã nh÷ng cét not Null mµ kh«ng dïng default value ( bëi v× trong tr−êng hîp nµy view sÏ cã Ýt colunm h¬n table table. Nªn insert 1 row vµo view, thùc chÊt lµ insert row ®ã vµo table sÏ kh«ng hîp lÖ). §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 60