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

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

Thêm vào BST

Báo xấu

132
lượt xem 44
download

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

Quyền và bảo mật Privileges là các quyền hạn được thực hiện các thao tác hoặc thực hiện truy cập đến các đối tượng dữ liệu. trong Oracle bạn sẽ không thể thực hiện các thao tác mà không có quyền tương ứng . các quyền hạn này được gắn cho user để có thể thực hiện các thao tác tren đối chỉ định.......

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 9

C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL • Kh«ng thÓ nµo insert trªn view, nÕu view nµy cã dïng biÓu thøc decode. • Nh÷ng query cña view kh«ng thÓ nµo tham kh¶o vµo 2 column gi¶ nextval, currval (nextval, currval dïng cho sequence). 15.2 Bµi tËp 1. T¹o view cã hiÓn thÞ nh− sau: select * from aggredates; DEPTNO AVERAGE MAXIMUN MINIMUN SUM NO_SALS NO_COMMS ------ ---------- ------- ---------- ---------- ---------- ---------- 10 2916.66667 5000 1300 8750 3 0 20 2235 3300 800 11175 5 0 30 1566.66667 2850 950 9400 6 4 2. T¹o view ®Ó nhËp sè liÖu vµo b¶ng ASIGNMENT víi c¸c ®iÒu kiÖn sau: PROJID
C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL • SYSTEM: Cã quyÒn thÊp h¬n SYS. MËt khÈu lµ MANAGER QuyÒn hÖ thèng Trong c¸c quyÒn hÖ thèng quyÒn DBA lµ lín nhÊt. DBA cã quyÒn • CREATE USER : T¹o user míi • DROP USER :Xo¸ user • DROP ANY TABLE :Xo¸ table • BACKUP ANY TABLE :T¹o c¸c backup table. LÖnh t¹o user cña ng−êi cã quyÒn DBA nh− sau: CREATE USER user_name IDENTIFY BY password; QuyÒn trªn ®èi t−îng: • CREATE SESION: Truy nhËp vµo CSDL • CREATE TABLE: t¹o b¶ng trong user ®ã • CREATE SEQUENCE: T¹o sequence • CREATE VIEW: T¹o view • CREATE PROCEDURE: T¹o procedure • ... G¸n quyÒn GRANT privilege[,privilege...] TO user [,user...] Xo¸ quyÒn REVOKE privilege[,privilege...] FROM user [,user...] 16.2 ROLE Role lµ tªn cña mét nhãm c¸c quyÒn h¹n. Nã ®−îc t¹o ®Ó qu¶n lý quyÒn h¹n cho c¸c øng dông hoÆc nhãm c¸c User. ViÖc dïng role cho phÐp qu¶n lý thèng nhÊt trªn c¸c ®èi t−îng, t¨ng tÝnh mÒm dÎo trong qu¶n trÞ, dÔ dµng thay ®æi. VÝ dô hai ®èi t−îng X, Y cã quyÒn trªn role A tøc lµ role A cã quyÒn g× th× X, Y cã quyÒn t−¬ng øng khi role A bÞ thay ®æi quyÒn h¹n th× X, Y còng bÞ thay ®æi quyÒn h¹n theo. LÖnh t¹o Role: CREATE ROLE role [IDENTIFY BY password]; G¸n privilege cho Role G¸n Role cã c¸c ®èi t−îng Mét sè Role hay dïng: • CONNECT • RESOURCE LÖnh g¸n vµ xo¸ Role gièng nh− lÖnh g¸n vµ xo¸ Privilege. Chi tiÕt xem trong phÇn qu¶n trÞ ORACLE. §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 62
C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL 16.3 Synonym Synonyms lµ bÝ danh cho mäi ®èi t−îng cña Oracle. C¸c ®èi t−îng cña Oracle lµ table, view, snapshot, sequence, procedure, function, package vµ c¸c synonym kh¸c. Có ph¸p CREATE PUBLIC SYNONYM synonym_name FROM [OWNER.]object_name; Dïng Synonyms cã nh÷ng lîi ®iÓm sau: • Kh«ng tèn thªm n¬i l−u tr÷ kh¸c bëi v× nã ®· ®−îc cÊt trªn tõ ®iÓn d÷ liÖu. • Lµm ®¬n gi¶n ®o¹n ch−¬ng tr×nh SQL. • T¨ng tÝnh b¶o mËt cho database. • Cã thÓ cho phÐp mäi ng−êi (public) truy xuÊt c¸c ®èi t−îng cña Oracle. VÝ dô: Chóng ta cã mét table EMPLY trong schema emp_01 Khi lËp tr×nh th× ph¶i truy xuÊt theo emp_01. EMPLY, tªn dµi nh− vËy th× ®o¹n ch−¬ng tr×nh sÏ dµi sÏ dÔ lÇm lÉn. Nªn chóng ta ph¶i dïng synonym CREATE SYNONYM EMP FOR EMP_01.EMPLY; Cã thÓ t¹o mét synonym cho phÐp mäi ng−êi cã thÓ tham kh¶o tíi CREATE PUBLIC EMP FOR EMP_01.EMPLY; TÝnh b¶o mËt lµ v× synonym lµ bÝ danh, nªn ng−êi sö dông dïng bÝ danh nµy sÏ kh«ng ®o¸n ®−îc thªm th«ng tin g×. 17 tæng quan vÒ pl/sql vµ procedure builder 17.1 Có ph¸p lÖnh PL/SQL • Mçi lÖnh SQL kÒt thóc b»ng dÊu (;) • LÖnh ®Þnh nghÜa CSDL (DDL) kh«ng ®−îc sö dông trong PL/SQL • LÖnh SELECT tr¶ vÒ nhiÒu dßng cã thÓ g©y exception • LÖnh DML cã thÓ t¸c ®éng trªn nhiÒu dßng 17.2 PL/SQL block Khèi lÖnh PL/SQL gåm c¸c thµnh phÇn DECLARE /Kh«ng b¾t buéc/ §Þnh nghÜa c¸c biÕn BEGIN §o¹n lÖnh; EXCEPTION /Kh«ng b¾t buéc/ Hµnh ®ång nÕu lçi xuÊt hiÖn; END; VÝ dô 1 DECLARE empno NUMBER(4):=7788; §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 63
C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL BEGIN UPDATE emp SET sal = 9000 WHERE empno = 0001; .... END; VÝ dô 2 DECLARE v_deptno NUMBER(2); v_loc VARCHAR2(15); BEGIN SELECT deptno, loc INTO v_deptno, v_loc FROM dept WHERE dname = 'SALES'; END; 17.3 Giíi thiÖu Procedure builder Trong Procedure Builder cã thÓ x©y dùng c¸c ®o¹n ch−¬ng tr×nh PL/SQL nh− program units, libraries, vµ database triggers ë c¶ client-side vµ server-side. Procedure Builder cã mét sè thµnh phÇn sau: • Object Navigator lµ phÇn hiÓn thÞ mäi ®èi tîng trong Procedure Builder's • Program Unit editor • PL/SQL Interpreter • Wizard Object Navigator. §Æc tÝnh • §ãng (+), më (-) c¸c node ®Ó xem th«ng tin • Cã thÓ connect vµo CSDL ®Ó xem th«ng tin vÒ c¸c ®èi t−îng trong CSDL • KÐo th¶ ®Ó copy ®èi t−îng • T×m kiÕm ®èi t−îng Program Unit editor • T¸c dông: Dïng ®Ó so¹n th¶o ®o¹n ch−¬ng tr×nh PL/SQL dÔ dµng • C¸ch gäi: NhÊn ®óp vµo icon bªn tr¸i cña program unit. HoÆc NhÊn ®óp vµo nót (+) ®Ó t¹o Program unit míi • TiÖn Ých Compile: DÞch §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 64
C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL New: T¹o míi Name: T×m theo c¸c program unit Delete: Xo¸ Close: §ãng Help: Trî gióp Apply Revert PL/SQL Interpreter • Khi chän mét program unit nµo ®ã. Néi dung cña program unit sÏ hiÖn lªn cöa sè Interpreter ®Ó debug. • Cöa sè interpreter cßn cã phÇn ®¸nh lÖnh PL/SQL sau dÊu nh¾c PL/SQL> Wizard • Cöa sæ Wizard hiÓn thÞ khi t¹o mét program unit míi b»ng c«ng cô Wizard. • C«ng cô nµy gióp dÔ dµng h¬n trong viÖc x©y dùng c¸c program unit. T¹o mét program unit • T¹o míi b»ng c¸ch nhÊn vµo nót (+) trªn thanh toolbar. • So¹n th¶o Trî gióp so¹n th¶o b»ng menu edit. Cã thÓ dïng import vµ export trªn menu file ®Ó ®−a thªm/lo¹i bá ®o¹n text Chän Syntax palete trong menu program ®Ó trî gióp vÒ có ph¸p Compile ®Ó t×m lçi, th«ng b¸o lçi hiÖn lªn t¹i dßng cuèi cña cöa sè Database Trigger • T¹o míi: NhÊn vµo nót (+), chän lo¹i database Trigger • So¹n th¶o: Gièng nh− víi Program unit Thªm c¸c lùa chän thuéc tÝnh cña trigger. T×m vÕt söa lçi c¸c Program Unit • T×m vÕt, söa lçi c¸c Program Unit trong PL/SQL interpreter gåm cã: Toolbar Command line • T¹o c¸c breakpoint (nhÊn ®óp vµo sè dßng lÖnh) ®Ó dõng ®o¹n ch−¬ng tr×nh, kiÓm tra c¸c biÕn runtime, d÷ liÖu... • C¸c c«ng cô trong interpreter Step into: Thùc hiÖn tiÕp ®Õn dßng lÖnh tiÕp theo (cã thÓ ngoµi program unit) Step over: Thùc hiÖn tiÕp ®Õn dßng lÖnh tiÕp theo nh−ng chØ trong Program unit ®ã. Step out: Thùc hiÖn phÇn cßn l¹i cña ch−¬ng tr×nh Go: Thùc hiÖn ®Õn cuèi ch−¬ng tr×nh vµ dõng l¹i khi cã breakpoint §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 65
C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL Reset: Bá c¸c breakpoint. Tæ chøc c¸c PL/SQL Program Unit • C¸c PL/SQL Program Unit th−êng ®−îc tæ chøc l¹i trong c¸c library (.PLL/.PPL). Create: T¹o mét library míi Open: Më library Save: Ghi l¹i thay ®æi • Attached library: Sö dông c¸c program unit trong c¸c Attached library nh− c¸c hµm mÆc ®Þnh. • Stored Program Unit: CÊt Program Unit thµnh c¸c Stored Program Unit trong CSDL 18 có ph¸p lËp tr×nh 18.1 IF IF condition THEN actions [ELSIF condition THEN actions] [ELSE actions] END IF VÝ dô 1 IF ename := 'SCOTT' THEN beam_me_up := 'YES'; COMMIT; ELSE beam_me_up := 'NO'; ROLLBACK; END IF; VÝ dô 2 IF choice= 1 THEN action := 'Run payroll'; ELSIF choice=2 THEN action:='Run'; ELSIF choice=3 THEN action:='Backup'; ELSE action:='Invalid'; END IF; 18.2 LOOP vµ EXIT LOOP actions; [EXIT loop_label [WHEN condition]] END LOOP VÝ dô 1: LOOP counter:=counter-1 INSERT INTO numbered_rows VALUES (counter); ..... IF counter = 10 THEN §µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 66
C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT Gi¸o tr×nh SQL vµ PL/SQL COMMIT; EXIT; END IF; END LOOP; VÝ dô 2: LOOP ..... EXIT WHEN total_sals = 60000; .... END LOOP; 18.3 FOR FOR control_variable IN [REVERSE] low_value .. high_value VÝ dô FOR I IN 1..2000 LOOP INSERT INTO numbered_rows VALUES (i); preserve_i:=i; .... END LOOP; 18.4 WHILE WHILE condition VÝ dô WHILE Bill