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

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

Thêm vào BST

Báo xấu

142
lượt xem 51
download

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

Quy tắc khi tham chiếu đến Object : Khi tham chiếu đến một Object trong câu lệnh SQl, Oracle phân tích câu lệnh và xác định các object trong không gian tên . Sau khi xác định các object , Oracle thực hiện các thao tác mà cáu lệnh quy định trên Object...............

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 7

C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL 11.3 C¸c quy t¾c khi tham chiÕu ®Õn object Có ph¸p chung khi tham chiÕu ®Õn c¸c object S¬ ®å chung khi tham chiÕu c¸c object hoÆc thµnh phÇn cña c¸c object Schema.Object.Part.@dblink Trong ®ã object Tªn object schema Schema chøa object part Thµnh phÇn cña object dblink Tªn CSDL chøa object ORACLE gi¶i quyÕt viÖc tham chiÕu c¸c object Khi tham chiÕu ®Õn mét object trong c©u lÖnh SQL, ORACLE ph©n tÝch c©u lÖnh vµ vµ x¸c ®Þnh c¸c object trong kh«ng gian tªn. Sau khi x¸c ®Þnh c¸c object, ORACLE thùc hiÖn c¸c thao t¸c mµ c©u lÖnh quy ®Þnh trªn object. NÕu tªn object truy cËp kh«ng thuéc kh«ng gian tªn th× c©u lÖnh kh«ng ®−îc thùc hiÖn vµ cã th«ng b¸o lçi. C©u lÖnh sau thªm mét mÈu tin vµo b¶ng DEPT INSERT INTO Dept VALUES (50, 'SUPPOR', 'PARIS') Theo ng÷ c¶nh cña c©u lÖnh, ORACLE x¸c ®Þnh b¶ng Dept cã thÓ lµ • Mét table trong schema cña b¹n • Mét view trong schema cña b¹n • §ång nghÜa riªng cho table hoÆc view • §ång nghÜa chung cho table hoÆc view Tham chiÕu ®Õn c¸c object kh«ng thuéc quyÒn së h÷u §Ó tham chiÕu ®Õn c¸c object kh«ng thuéc schema hiÖn thêi, ph¶i chØ ra tªn cña schema chøa object muèn truy cËp schema.object VÝ dô ®Ó xãa table EMP trong schema SCOTT DROP TABLE scott.emp Tham chiÕu c¸c object tõ xa §Ó truy cËp ®Õn mét CSDL ë xa, sau tªn object ph¶i chØ ra tªn liªn kÕt CSDL (database link) cña CSDL chøa object muèn truy cËp. Database link lµ mét schema object, ORACLE dïng ®Ó th©m nhËp vµ truy xuÊt CSDL tõ xa. 11.4 KiÓu d÷ liÖu vµ ®iÒu kiÖn 11.4.1 CHAR KiÓu CHAR dïng ®Ó khai b¸o mét chuçi cã chiÒu dµi cè ®Þnh, khi khai b¸o biÕn hoÆc cét kiÓu CHAR víi chiÒu dµi chØ ®Þnh th× tÊt c¶ c¸c môc tin cña biÕn hay cét nµy ®Òu cã cïng chiÒu dµi ®−îc chØ ®Þnh. C¸c môc tin ng¾n h¬n ORACLE sÏ tù ®éng thªm vµo c¸c kho¶ng trèng cho ®ñ chiÒu dµi. ORACLE kh«ng cho phÐp §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 47
C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL g¸n môc tin dµi h¬n chiÒu dµi chØ ®Þnh ®èi víi kiÓu CHAR. ChiÒu dµi tèi ®a cho phÐp cña kiÓu CHAR lµ 255 byte 11.4.2 VARCHAR2 KiÓu VARCHAR2 dïng ®Ó khai b¸o chuçi ký tù víi chiÒu dµi thay ®æi. Khi khai b¸o mét biÕn hoÆc cét kiÓu VARCHAR2 ph¶i chØ ra chiÒu dµi tèi ®a, c¸c môc tin chøa trong biÕn hay cét kiÓu VARCHAR2 cã chiÒu dµi thùc sù lµ chiÒu dµi cña môc tin. ORACLE kh«ng cho phÐp g¸n môc tin dµi h¬n chiÒu dµi tèi ®a chØ ®Þnh ®èi víi kiÓu VARCHAR2. ChiÒu dµi tèi ®a kiÓu VARCHAR2 lµ 2000 byte 11.4.3 VARCHAR HiÖn t¹i ORACLE xem kiÓu VARCHAR2 vµ VARCHAR lµ nh− nhau, tuy nhiªn ORACLE khuyªn nªn dïng VARCHAR2. ORACLE dù ®Þnh trong t−¬ng lai dïng kiÓu VARCHAR ®Ó chøa c¸c chuçi víi chiÒu dµi biÕn ®æi, nh−ng trong phÐp so s¸nh sÏ ®−îc chØ ®Þnh theo nhiÒu ng÷ nghÜa kh¸c nhau. 11.4.4 NUMBER KiÓu sè cña ORACLE dïng ®Ó chøa c¸c môc tin d¹ng sè d−¬ng, sè ©m, sè víi dÊu chÊm ®éng. NUMBER(p, s) trong ®ã p: sè ch÷ sè tr−íc dÊu chÊm thËp ph©n (precision), p tõ 1 ®Õn 38 ch÷ sè s: sè c¸c ch÷ sè tÝnh tõ dÊu chÊm thËp ph©n vÒ bªn ph¶i (scale), s tõ -84 ®Õn 127 NUMBER(p) sè cã dÊu chÊm thËp ph©n cè ®Þnh víi precision b»ng p vµ scale b»ng 0 NUMBER sè víi dÊu chÊm ®éng víi precision b»ng 38. Nhí r»ng scale kh«ng ®−îc ¸p dông cho sè víi dÊu chÊm ®éng. VÝ dô sau cho thÊy c¸ch thøc ORACLE l−u tr÷ d÷ liÖu kiÓu sè tïy theo c¸ch ®Þnh precision vµ scale kh¸c nhau D÷ liÖu thùc KiÓu L−u tr÷ 7456123.89 NUMBER 7456123.89 7456123.89 NUMBER(9) 7456123 7456123.89 NUMBER(9,2) 7456123.89 7456123.89 NUMBER(9,1) 7456123.8 7456123.89 NUMBER(6) Kh«ng hîp lÖ 7456123.8 NUMBER(15,1) 7456123.8 7456123.89 NUMBER(7,-2) 7456100 7456123.89 NUMBER(-7,2) Kh«ng hîp lÖ 11.4.5 FLOAT Dïng ®Ó khai b¸o kiÓu sè dÊu chÊm ®éng, víi ®é chÝnh x¸c thËp ph©n 38 hay ®é chÝnh x¸c nhÞ ph©n lµ 126. FLOAT(b) Khai b¸o kiÓu dÊu chÊm ®éng víi ®é chÝnh x¸c nhÞ ph©n lµ b, b tõ 1 ®Õn 126. Cã thÓ chuyÓn tõ ®é chÝnh x¸c nhÞ phËn sang ®é chÝnh x¸c thËp ph©n b»ng c¸ch nh©n ®é chÝnh x¸c nhÞ ph©n víi 0.30103 §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 48
C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL 11.4.6 LONG Dïng ®Ó khai b¸o kiÓu chuçi ký tù víi ®é dµi biÕn ®æi, chiÒu dµi tèi ®a cña kiÓu LONG lµ 2 gigabyte. KiÓu LONG th−êng ®−îc dïng ®Ó chøa c¸c v¨n b¶n. Cã mét sè h¹n chÕ khi dïng kiÓu LONG • Mét table kh«ng thÓ chøa nhiÒu h¬n mét cét kiÓu LONG • D÷ liÖu kiÓu LONG kh«ng thÓ tham gia vµo c¸c rµng buéc toµn vÑn, ngo¹i trõ kiÓm tra NULL vµ kh¸c NULL • Kh«ng thÓ index mét cét kiÓu LONG • Kh«ng thÓ truyÒn tham sè kiÓu LONG cho hµm hoÆc thñ tôc • C¸c hµm kh«ng thÓ tr¶ vÒ d÷ liÖu kiÓu LONG • Trong c©u lÖnh SQL cã truy cËp c¸c cét kiÓu LONG, th× viÖc cËp nhËt hoÆc khãa c¸c b¶ng chØ cho phÐp trong cïng mét CSDL Ngoµi ra, c¸c cét kiÓu LONG kh«ng ®−îc tham gia trong c¸c thµnh phÇn sau cña c©u lÖnh SQL • C¸c mÖnh ®Ò WHERE, GROUP BY, ORDER BY, CONNECT BY hoÆc víi t¸c tö DISTINCT trong c©u lÖnh SELECT • C¸c hµm sö dông trong c©u lÖnh SQL nh− SUBSTR, INSTR • Trong danh s¸ch lùa chän cña c©u lÖnh SELECT cã sö dông mÖnh ®Ò GROUP BY • Trong danh s¸ch lùa chän cña c©u hái con, c©u hái cã sö dông c¸c to¸n tö tËp hîp • Trong danh s¸ch lùa chän cña c©u lÖnh CREATE TABLE AS SELECT 11.4.7 DATE Dïng ®Ó chøa d÷ liÖu ngµy vµ thêi gian. MÆc dï kiÓu ngµy vµ thêi gian cã thÓ ®−îc chøa trong kiÓu CHAR vµ NUMBER. Víi gi¸ trÞ kiÓu DATE, nh÷ng th«ng tin ®−îc l−u tr÷ gåm thÕ kû, n¨m, th¸ng, ngµy, giê, phót, gi©y. ORACLE kh«ng cho phÐp g¸n gi¸ trÞ kiÓu ngµy trùc tiÕp, ®Ó g¸n gi¸ trÞ kiÓu ngµy, b¹n ph¶i dïng TO_DATE ®Ó chuyÓn gi¸ trÞ kiÓu chuçi ký tù hoÆc kiÓu sè. NÕu g¸n mét gi¸ trÞ kiÓu ngµy mµ kh«ng chØ thêi gian th× thêi gian mÆc ®Þnh lµ 12 giê ®ªm, NÕu g¸n gi¸ trÞ kiÓu ngµy mµ kh«ng chØ ra ngµy, th× ngµy mÆc ®Þnh lµ ngµy ®Çu cña th¸ng. Hµm SYSDATE cho biÕt ngµy vµ thêi gian hÖ thèng. TÝnh to¸n ®èi víi kiÓu ngµy §èi víi d÷ liÖu kiÓu ngµy, b¹n cã thÓ thùc hiÖn c¸c phÐp to¸n céng vµ trõ. VÝ dô SYSDATE+1 ngµy h«m sau SYSDATE-7 c¸ch ®©y mét tuÇn SYSDATE+(10/1440) m−êi phót sau Ngµy Julian: Lµ gi¸ trÞ sè cho biÕt sè ngµy kÓ tõ ngµy 1 th¸ng giªng n¨m 4712 tr−íc c«ng nguyªn. VÝ dô §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 49
C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL SELECT TO_CHAR (TO_DATE('01-01-1992', 'MM-DD-YYYY'), 'J') JULIAN FROM DUAL Cho kÕt qu¶ JULIAN ----------------------------------------------- 2448623 11.4.8 RAW vµ LONG RAW KiÓu RAW vµ LONG RAW dïng ®Ó chøa c¸c chuçi byte, c¸c d÷ liÖu nhÞ ph©n nh− h×nh ¶nh, ©m thanh. C¸c d÷ liÖu kiÓu RAW chØ cã thÓ g¸n hoÆc truy cËp chø kh«ng ®−îc thùc hiÖn c¸c thao t¸c nh− ®èi víi chuçi ký tù. KiÓu RAW gièng nh− kiÓu VARCHAR2 vµ kiÓu LONG RAW gièng kiÓu LONG, chØ kh¸c nhau ë chæ ORACLE tù ®éng chuyÓn ®æi c¸c gi¸ trÞ kiÓu CHAR, VARCHAR2 vµ LONG gi÷a tËp hîp ký tù cña CSDL vµ tËp ký tù cña c¸c øng dông. 11.4.9 ROWID Mçi mÉu tin trong CSDL cã mét ®Þa chØ cã kiÓu ROWID. ROWID gåm block.row.file, trong ®ã block : chuçi hÖ hexa cho biÕt block chøa row row : chuçi hÖ hexa cho biÕt row trong block file : chuçi hÖ hexa cho biÕt database file chøa block VÝ dô 0000000F.0000.0002 Row ®Çu tiªn trong block 15 cña data file thø hai. 11.4.10 MLSLABEL KiÓu MLSLABEL dïng ®Ó chøa label d¹ng nhÞ ph©n mµ ORACLE dïng ®Ó ®¶m b¶o ho¹t ®éng cña b¶n th©n hÖ thèng. 11.4.11 ChuyÓn ®æi kiÓu Nãi chung mét biÓu thøc kh«ng thÓ gåm c¸c gi¸ trÞ thuéc nhiÒu kiÓu kh¸c nhau, tuy nhiªn ORACLE cho phÐp chuyÓn ®æi gi−· c¸c kiÓu d÷ liÖu. ORACLE tù ®éng chuyÓn kiÓu cña d÷ liÖu trong mét sè tr−êng hîp sau • Khi INSERT hoÆc UPDATE g¸n gi¸ trÞ cho cét cã kiÓu kh¸c, ORACLE sÏ tù ®éng chuyÓn gi¸ trÞ sang kiÓu cña cét. • Khi sö dông c¸c hµm hoÆc c¸c to¸n tö mµ c¸c tham sè cã kiÓu kh«ng t−¬ng thÝch th× ORACLE sÏ tù ®éng chuyÓn kiÓu. • Khi sö dông to¸n tö so s¸nh mµ c¸c gi¸ trÞ cã c¸c kiÓu kh¸c nhau, ORACLE sÏ tù ®éng chuyÓn kiÓu. VÝ dô 1 SELECT ename FROM emp WHERE hiredate = '12-MAR-1993' ORACLE ®· tù ®éng chuyÓn chuçi '12-MAR-1993' sang kiÓu DATE trong phÐp so s¸nh VÝ dô 2 SELECT ename FROM emp WHERE ROWID = '00002514.0001.0001' §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 50
C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL ORACLE ®· tù ®éng chuyÓn chuçi '00002514.0001.0001' sang kiÓu ROWID trong phÐp so s¸nh Ng−êi sö dông tù chuyÓn ®æi ORACLE cung cÊp c¸c hµm ®Ó chuyÓn ®æi kiÓu, vÝ dô • TO_NUMBER ChuyÓn sang kiÓu sè • TO_CHAR ChuyÓn sang kiÓu ký tù • TO_DATE ChuyÓn sang kiÓu ngµy (xem phÇn tra cøu c¸c hµm vµ thñ tôc) 11.5 Constraint C¸c d¹ng constraint gåm: - NULL/NOT NULL - UNIQUE - PRIMARY KEY - FOREIGN KEY ( Referential ) - CHECK NULL/NOT NULL: rµng buéc column trèng hoÆc kh«ng trèng, trong vÝ dô mÖnh ®Ò rµng buéc: CREATE TABLE DEPT ( DEPTNO NUMBER(2) NOT NULL, DNAME CHAR(14), LOC CHAR(13), CONSTRAINT DEPT_PRIMARY_KEY PRIMARY KEY (DEPTNO)); UNIQUE: ChØ ra rµng buéc duy nhÊt, c¸c gi¸ trÞ cña column chØ trong mÖnh ®Ò UNIQUE trong c¸c row cña table ph¶i cã gi¸ trÞ kh¸c biÖt. Gi¸ trÞ null lµ cho phÐp nªu UNIQUE dùa trªn mét cét. Vd: CREATE TABLE DEPT ( DEPTNO NUMBER(2), DNAME CHAR(14), LOC CHAR(13), CONSTRAINT UNQ_DEPT_LOC UNIQUE(DNAME, LOC)); PRIMARY KEY: ChØ ra rµng buéc duy nhÊt (gièng UNIQUE), tuy nhiªn kho¸ lµ d¹ng kho¸ UNIQUE cÊp cao nhÊt. Mét table chØ cã thÓ cã mét PRIMARY KEY. C¸c gi¸ trÞ trong PRIMARY KEY ph¶i NOTT NULL. Có ph¸p khi ®Æt CONSTRAINT ë møc TABLE [CONSTRAINT constraint_name] PRIMARY KEY (column, Column..) Có ph¸p khi ®Æt CONSTRAINT ë møc COLUMN [CONSTRAINT constraint_name] PRIMARY KEY FOREIGN KEY ( Referential ): ChØ ra mèi liªn hÖ rµng buéc tham chiÕu gi÷a table nµy víi table kh¸c, hoÆc trong chÝnh 1 table. Nã chØ ra mèi liªn hÖ cha-con vµ chØ rµng buéc gi÷a FOREIGN KEY b¶ng nµy víi PRIMARY KEY hoÆc UNIQUE Key cña b¶ng kh¸c. VÝ dô quan hÖ gi÷a DEPT vµ EMP th«ng qua tr−êng DEPTNO. Tõ kho¸ ON DELETE CASCADE ®−îc hØ ®Þnh trong d¹ng kho¸ nµy ®Ó chØ khi d÷ liÖu cha bÞ xo¸ (trong b¶ng DEPT) th× d÷ liÖu con còng tù ®éng bÞ xo¸ theo (trong b¶ng EMP). CHECK: Rµng buéc kiÓm tra gi¸ trÞ VÝ dô: CREATE TABLE EMP (EMPNO NUMBER NOT NULL CONSTRAINT PK_EMP PRIMARY KEY, ENAME VARCHAR2(10) CONSTRAINT NN_ENAME NOT NULL CONSTRAINT UPPER_ENAME CHECK (ENAME = UPPER(ENAME)), §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 51
C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL JOB VARCHAR2(9), MGR NUMBER CONSTRAINT FK_MGR REFERENCES SCOTT.EMP(EMPNO), HIREDATE DATE DEFAULT SYSDATE, SAL NUMBER(10,2) CONSTRAINT CK_SAL CHECK(SAL>500), COMM NUMBER(9,0) DEFAULT NULL, DEPTNO NUMBER(2) CONSTRAINT NN_DEPTNO NOT NULL CONSTRAINT FK_DEPTNO REFERENCES SCOTT.DEPT(DEPTNO); 11.6 Bµi tËp 1. T¹o b¶ng PROJECT víi c¸c column ®−îc chØ ra d−íi ®©y, PROJID lµ promary key, vµ P_END_DATE > P_START_DATE. Column name Data Type Size. ------------------------------------ PROJID NUMBER 4 P_DESC VARCHAR2 20 P_START_DATE DATE P_END_DATE DATE BUDGET_AMOUNT NUMBER 7,2 MAX_NO_STAFF NUMBER 2 2.. T¹o b¶ng ASSIGNMENTS víi c¸c column ®−îc chØ ra d−íi ®©y, ®ång thêi cét PROJID lµ foreign key tíi b¶ng PROJECT, cét EMPNO lµ foreign key tíi b¶ng EMP. Column name Data Type Size. ------------------------------------------------ PROJID NUMBER 4 NOT NULL EMPNO NUMBER 4 NOT NULL A_START_DATE DATE A_END_DATE DATE BILL_AMOUNT NUMBER 4,2 ASSIGN_TYPE VARCHAR2 2 12 c¸c lÖnh DDL kh¸c vµ d÷ liÖu trong tõ ®iÓn d÷ liÖu 12.1 ChØnh söa cÊu tróc table Dïng lÖnh ALTER TABLE ®Ó chØnh söa cÊu tróc b¶ng. Có ph¸p nh− sau: ALTER TABLE tablename [ADD/MODIFY/DROP options ([column [column constraint) [ENABLE clause] [DISABLE clause] Trong ®ã: ADD: thªm column hay constraint. MODIFY: söa ®æi kiÓu c¸c column DROP: bá constraint. ENABLE/DISABLE: Che khuÊt hoÆc ®−a vµo sö dông c¸c CONSTRAINT mµ kh«ng xãa h¼n Chó ý: • KhÝ dïng mÖnh ®Ò MODIFY kh«ng thÓ chuyÓn tÝnh chÊt cña COLUMN cã néi dung lµ NULL chuyÓn thµnh NOT NULL; • Kh«ng thÓ ®−a thªm mét cét NOT NUL nÕu table ®· cã sè liÖu. Ph¶i thªm cét NULL, ®iÒn ®Çy sè liÖu, sau ®ã chuyÓn thµnh NOT NULL. • Kh«ng thÓ chuyÓn ®æi kiÓu kh¸c nhau nÕu column ®· chøa sè liÖu • Kh«ng thÓ dïng mÖnh ®Ò MODIFY ®Ó ®Þnh nghÜa c¸c CONSTRAINT trõ rµng buéc NULL/NOT NULL. Muèn söa CONSTRAINT cÇn xo¸ chóng sau ®ã ADD thªm vµo. §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 52
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 ALTER TABLE emp ADD (spouse_name CHAR(10); VÝ dô 2 ALTER TABLE emp MODIFY (ename CHAR(25); VÝ dô 3 ALTER TABLE emp DROP CONSTRAINT emp_mgr; ALTER TABLE DROP PRIMARY KEY; VÝ dô 4 ALTER TABLE dept DISABLE CONSTRAINT dept_prim; 12.2 C¸c lÖnh DDL kh¸c 12.2.1 Xãa table Dïng lÖnh DROP TABLE ®Ó xo¸ b¶ng. Có ph¸p nh− sau: DROP TABLE table_name [CASCADE CONSTRAINTS] Trong ®ã: Option CASCADE ®Ó xãa tÊt c¶ c¸c rµng buéc toµn vÑn liªn quan ®Õn table bÞ xãa. VÝ dô: DROP TALE emp Khi drop table th×: • Xãa tÊt c¶ d÷ liÖu • View vµ synonymliªn quan vÉn cßn nh−ng kh«ng cã gi¸ trÞ • C¸c giao dÞch ch−a gi¶i quyÕt xong sÏ ®−îc commit • ChØ ng−êi t¹o ra table hay DBA míi cã thÓ xãa table 12.2.2 Gi¶i thÝch b¶ng Dïng lÖnh COMMENT ®Ó chó thÝch. VÝ dô COMMENT ON TABLE EMP IS ‘ THONG TIN NHAN VIEN’; COMMENT ON COLUMN EMP.EMPNO IS ‘ MA SO NHAN VIEN’; 12.2.3 Thay ®æi tªn object Dïng lÖnh RENAME ®Ó thay ®æi tªn object. Có ph¸p nh− sau: RENAME old TO new Trong ®ã: Old: Tªn cò New: tªn míi VÝ dô RENAME emp TO employee 12.2.4 Xãa d÷ liÖu cña table Dïng lÖnh TRUNCATE TABLE ®Ó xãa d÷ liÖu cña table, xãa tÊt c¶ c¸c row trong table. Có ph¸p nh− sau: TRUNCATE TABLE table_name [REUSE STORAGE] Trong ®ã: §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 53