Giáo trình SQL và PL/SQL
Nội dung: 1. Giới thiệu 2. Lệnh truy vấn cơ bản 3. Truy vấn dữ liệu có điều kiện 4. Các hàm áp dụng cho 1 dòng dữ liệu 5. Biến RUNTIME 6. Các hàm nhóm áp dụng cho lớn hơn hoặc bằng 1 dòng dữ liệu 7. Hiển thị nội dung dữ liệu từ nhiều bảng 8. Các lệnh truy vấn lồng nhau 9. Cấu trúc hình cây 10. Tổng kết về lệnh SELECT 11. Tạo TABLE 12. Các lệnh DLL khác và dữ liệu trong từ điển dữ liệu 13. Các lệnh thao tác dữ liệu khác 14. SEQUENCE và INDEX 15. Tạo VIEW 16. Quyền bảo mật 17. Tổng quan về PL/SQL và PROCEDURE BUILDER 18. Cú pháp lập trình 19. CURSOR 20. PROCEDURE và FUNCTION 21. PACKAGE 22. DATABASE TRIGGER 23. ERROR HANDING
Bạn đang xem trước 20 trang tài liệu Giáo trình SQL và PL/SQL, để xem tài liệu hoàn chỉnh bạn click vào nút DOWNLOAD ở trên
www.nhipsongcongnghe.net
C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT
Gi¸o tr×nh SQL vµ PL/SQL
§µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 2
C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - Fpt
Hµ Néi, th¸ng 11 n¨m 2002.
§µo t¹o Oracle c¬ b¶n
Gi¸o tr×nh SQL vµ PL/SQL
C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT
Gi¸o tr×nh SQL vµ PL/SQL
§µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 3
Môc lôc
Môc lôc .................................................................................................................................................3
1 Giíi thiÖu.......................................................................................................................................6
1.1 Môc tiªu kho¸ häc ..............................................................................................................................6
1.2 Khëi ®éng vµ tho¸t khái Oracle.........................................................................................................6
1.2.1 T¹i Server (Window NT) .........................................................................................6
1.2.2 T¹i Client (Window 9x)............................................................................................6
1.3 Giíi thiÖu ng«n ng÷ SQL ...................................................................................................................7
1.3.1 LÞch sö ph¸t triÓn cña ng«n ng÷ SQL .....................................................................7
1.3.2 ChuÈn SQL .............................................................................................................7
1.4 C¸c kh¸i niÖm trong CSDL.................................................................................................................7
1.5 Danh s¸ch rót gän c¸c ®èi t−îng CSDL ...........................................................................................8
1.6 C¸c lÖnh SQL ......................................................................................................................................8
1.7 Giíi thiÖu vÒ vÝ dô thùc hµnh.............................................................................................................9
1.7.1 M« h×nh quan hÖ d÷ liÖu..........................................................................................9
1.7.2 M« t¶ d÷ liÖu............................................................................................................9
2 LÖnh truy vÊn c¬ b¶n ...........................................................................................................10
2.1 LÖnh truy vÊn c¬ b¶n........................................................................................................................10
2.2 C¸c thµnh phÇn kh¸c cña mÖnh ®Ò SELECT..................................................................................10
2.3 Gi¸ trÞ Null..........................................................................................................................................11
2.4 Läc d÷ liÖu tõ c¸c row cã cïng gi¸ trÞ.............................................................................................11
2.5 HiÓn thÞ cÊu tróc b¶ng ......................................................................................................................12
2.6 C¸c lÖnh cña c«ng cô SQL*Plus......................................................................................................12
2.6.1 C¸c lÖnh so¹n th¶o ...............................................................................................12
2.6.2 C¸c lÖnh vÒ file......................................................................................................13
2.6.3 C¸c lÖnh vÒ column...............................................................................................13
2.7 Bµi tËp................................................................................................................................................14
3 Truy vÊn d÷ liÖu cã ®iÒu kiÖn ............................................................................................16
3.1 MÖnh ®Ò ORDER BY .........................................................................................................................16
3.2 MÖnh ®Ò WHERE...............................................................................................................................16
3.3 C¸c to¸n tö ........................................................................................................................................17
3.4 Bµi tËp................................................................................................................................................19
4 C¸c hµm ¸p dông cho 1 dßng d÷ liÖu..............................................................................20
4.1 C¸c hµm sè........................................................................................................................................20
4.2 C¸c hµm ký tù ...................................................................................................................................22
4.3 C¸c hµm ngµy ...................................................................................................................................26
4.4 C¸c hµm chuyÓn ®æi kiÓu.................................................................................................................28
4.5 Bµi tËp................................................................................................................................................29
5 BiÕn runtime..............................................................................................................................31
5.1 Bµi tËp................................................................................................................................................32
6 C¸c hµm nhãm ¸p dông cho lín h¬n hoÆc b»ng 1 dßng d÷ liÖu ........................32
6.1 C¸c hµm t¸c ®éng trªn nhãm ..........................................................................................................32
6.2 MÖnh ®Ò GROUP BY .........................................................................................................................34
6.3 Bµi tËp................................................................................................................................................35
7 HiÓn thÞ néi dung d÷ liÖu tõ nhiÒu b¶ng........................................................................35
7.1 Mèi liªn kÕt t−¬ng ®−¬ng .................................................................................................................35
C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT
Gi¸o tr×nh SQL vµ PL/SQL
§µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 4
7.2 Mèi liªn kÕt kh«ng t−¬ng ®−¬ng......................................................................................................35
7.3 Mèi liªn kÕt céng...............................................................................................................................36
7.4 Liªn kÕt cña b¶ng víi chÝnh nã .......................................................................................................36
7.5 C¸c to¸n tö tËp hîp ..........................................................................................................................36
7.6 Bµi tËp................................................................................................................................................37
8 C¸c lÖnh truy vÊn lång nhau...........................................................................................39
8.1 C©u lÖnh SELECT lång nhau. ..........................................................................................................39
8.2 Bµi tËp................................................................................................................................................40
9 CÊu tróc h×nh c©y...................................................................................................................40
9.1 CÊu tróc h×nh c©y trong 1 table .......................................................................................................40
9.2 Kü thuËt thùc hiÖn ............................................................................................................................41
9.3 Bµi tËp................................................................................................................................................42
10 Tæng kÕt vÒ lÖnh select .....................................................................................................44
11 T¹o table ...................................................................................................................................44
11.1 LÖnh t¹o b¶ng ...................................................................................................................................44
11.2 C¸c quy t¾c ®Æt tªn object ...............................................................................................................46
11.3 C¸c quy t¾c khi tham chiÕu ®Õn object...........................................................................................47
11.4 KiÓu d÷ liÖu vµ ®iÒu kiÖn..................................................................................................................47
11.4.1 CHAR ....................................................................................................................47
11.4.2 VARCHAR2...........................................................................................................48
11.4.3 VARCHAR.............................................................................................................48
11.4.4 NUMBER...............................................................................................................48
11.4.5 FLOAT...................................................................................................................48
11.4.6 LONG ....................................................................................................................49
11.4.7 DATE.....................................................................................................................49
11.4.8 RAW vµ LONG RAW ............................................................................................50
11.4.9 ROWID..................................................................................................................50
11.4.10 MLSLABEL............................................................................................................50
11.4.11 ChuyÓn ®æi kiÓu ....................................................................................................50
11.5 Constraint..........................................................................................................................................51
11.6 Bµi tËp................................................................................................................................................52
12 c¸c lÖnh DDL kh¸c vµ d÷ liÖu trong tõ ®iÓn d÷ liÖu...............................................52
12.1 ChØnh söa cÊu tróc table ..................................................................................................................52
12.2 C¸c lÖnh DDL kh¸c ...........................................................................................................................53
12.2.1 Xãa table ...............................................................................................................53
12.2.2 Gi¶i thÝch b¶ng ......................................................................................................53
12.2.3 Thay ®æi tªn object................................................................................................53
12.2.4 Xãa d÷ liÖu cña table.............................................................................................53
12.3 D÷ liÖu trong tõ ®iÓn d÷ liÖu ............................................................................................................54
12.4 Bµi tËp................................................................................................................................................54
13 C¸c lÖnh Thao t¸c d÷ liÖu kh¸c ......................................................................................55
13.1 ChÌn mét row vµo table ...................................................................................................................55
13.2 ChØnh söa d÷ liÖu..............................................................................................................................55
13.3 Xãa dßng ...........................................................................................................................................55
13.4 Lçi rµng buéc d÷ liÖu .......................................................................................................................56
13.5 LÖnh ®iÒu khiÓn giao dÞch................................................................................................................56
13.6 Bµi tËp................................................................................................................................................57
14 Sequence vµ index..................................................................................................................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
§µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 5
14.1 Sequence...........................................................................................................................................57
14.1.1 T¹o Sequence.......................................................................................................57
14.1.2 Xo¸ vµ söa sequence ...........................................................................................58
14.2 Index ..................................................................................................................................................58
14.3 Bµi tËp................................................................................................................................................59
15 T¹o view ......................................................................................................................................59
15.1 View....................................................................................................................................................59
15.2 Bµi tËp................................................................................................................................................61
16 QuyÒn vµ b¶o mËt ...................................................................................................................61
16.1 QuyÒn - PRIVILEGE ..........................................................................................................................61
16.2 ROLE..................................................................................................................................................62
16.3 Synonym............................................................................................................................................63
17 tæng quan vÒ pl/sql vµ procedure builder...............................................................63
17.1 Có ph¸p lÖnh PL/SQL .......................................................................................................................63
17.2 PL/SQL block ....................................................................................................................................63
17.3 Giíi thiÖu Procedure builder............................................................................................................64
18 có ph¸p lËp tr×nh ...................................................................................................................66
18.1 IF ........................................................................................................................................................66
18.2 LOOP vµ EXIT....................................................................................................................................66
18.3 FOR ....................................................................................................................................................67
18.4 WHILE ................................................................................................................................................67
18.5 GOTO .................................................................................................................................................67
19 cursor ........................................................................................................................................68
19.1 §Þnh nghÜa .........................................................................................................................................68
19.2 KiÓu d÷ liÖu Table vµ Record...........................................................................................................69
19.3 Sao kiÓu d÷ liÖu ................................................................................................................................70
19.4 C©u lÖnh SELECT... INTO... trong PL/SQL......................................................................................70
19.5 Bµi tËp................................................................................................................................................70
20 procedure vµ funtion..........................................................................................................71
20.1 Procedure ..........................................................................................................................................71
20.2 Function.............................................................................................................................................72
20.3 Bµi tËp................................................................................................................................................73
21 pakage.........................................................................................................................................73
21.1 Package .............................................................................................................................................73
22 database trigger ..................................................................................................................74
22.1 Database Trigger...............................................................................................................................74
22.2 Bµi tËp................................................................................................................................................75
23 error handing.........................................................................................................................76
23.1 Bµi tËp................................................................................................................................................78
C«ng ty cæ phÇn ®Çu t− ph¸t triÓn c«ng nghÖ - FPT
Gi¸o tr×nh SQL vµ PL/SQL
§µo t¹o c¬ b¶n: SQL vµ PL/SQL Trang 6
1 Giíi thiÖu
1.1 Môc tiªu kho¸ häc
KÕt thóc kho¸ häc häc viªn ph¶i n¾m ®−îc
• HiÓu ®−îc ph−¬ng ph¸p, c¸c thµnh phÇn, thuËt ng÷ vµ thao t¸c trong CSDL quan hÖ
• T¹o ®−îc c¸c cÊu tróc
