Giáo trình SQL và PL/SQL

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 d÷ liÖu nh− table, view dïng SQL

• Ghi, ®äc, vµ cËp nhËt d÷ liÖu trong CSDL

• X©y dùng c¸c PL/SQL block dïng Procedure Builder

1.2 Khëi ®éng vµ tho¸t khái Oracle

1.2.1 T¹i Server (Window NT)

SQLDBA cung cÊp c¸c dÞch vô qu¶n trÞ hÖ thèng, nh−: t¹o lËp CSDL, më - ®ãng CSDL, t¹o vµ qu¶n lý c¸c

USER ... C¸c b−íc ®Ó khëi ®éng t¹i Server nh− sau:

• Khëi ®éng m¸y chñ

• BËt dÞch vô OracleServiceXXX (trong ®ã XXX lµ tªn cña CSDL) b»ng c¸ch nhÊn vµo Start -> Program -

> Service -> OracleServiceXXX -> NhÊn chuét ph¶i -> NhÊn Start. Chó ý chØ bËt dÞch vô nµy khi ng−êi

cµi ®Æt kh«ng ®Ó chÕ ®é tù ®éng hay khi dÞch vô nµy ch−a ®−îc bËt.

• BËt dÞch vô OracleXXXTNSLÝstener (trong ®ã XXX lµ tªn cña Database Home) b»ng c¸ch nhÊn vµo

Start -> Program -> Service -> OracleXXXTNSLÝstener -> NhÊn chuét ph¶i -> NhÊn Start. Chó ý chØ bËt

dÞch vô nµy khi ng−êi cµi ®Æt kh«ng ®Ó chÕ ®é tù ®éng hay khi dÞch vô nµy ch−a ®−îc bËt.

• Khi bËt xong CSDL ®· s½n sµng ®Ó lµm viÖc

§Ó ®ãng CSDL cÇn lµm theo c¸c b−íc ng−îc l¹i:

• T¾t dÞch vô OracleXXXTNSLÝstener (trong ®ã XXX lµ tªn cña Database Home) b»ng c¸ch nhÊn vµo

Start -> Program -> Service -> OracleXXXTNSLÝstener -> NhÊn chuét ph¶i -> NhÊn Stop.

• T¾t dÞch vô OracleServiceXXX (trong ®ã XXX lµ tªn cña CSDL) b»ng c¸ch nhÊn vµo Start -> Program -

> Service -> OracleServiceXXX -> NhÊn chuét ph¶i -> NhÊn Stop.

• Shutdown m¸y chñ.

1.2.2 T¹i Client (Window 9x)

C¸c øng dông cña oracle ch¹y trong m«i tr−êng Windows víi giao diÖn graphic, c¸c øng dông th−êng dïng

cã SQL*Plus, Oracle Form, Oracle Report, Oracle Designer ... ViÖc ch¹y c¸c øng dông nµy hoµn toµn gièng

nh− viÖc ch¹y c¸c øng dông th«ng th−êng trong m«i tr−êng windows.

§Ó lµm viÖc víi c¸c øng dông truy cËp CSDL Oracle, ng−êi sö dông (NSD) ph¶i connect vµo CSDL. Cã hai

c¸ch ®Ó connect.

Connect NSD/password, vÝ dô NSD tªn Scott cã password lµ tiger th×

Connect Scott/tiger

Ph¸t lÖnh connect víi tªn NSD, khi ®ã Oracle sÏ hái password

Connect Scott

Enter password: *****

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 7

NSD cã thÓ lµm viÖc trong ph¹m vi cho phÐp cña m×nh mµ Oracle gäi lµ "khung c¶nh" (Schema) cña NSD.

Mçi khung c¶nh chøa nhiÒu ®èi t−îng c¸c lo¹i, NSD chØ cã thÓ t¸c ®éng lªn c¸c ®èi t−îng trong khung c¶nh

cña m×nh.

Trong c¸c øng dông ®Òu cã chøc n¨ng tho¸t vµ tù ®éng disconnect.

§Ó thùc hµnh phÇn SQL vµ PL/SQL gäi øng dông SQL* Plus.

1.3 Giíi thiÖu ng«n ng÷ SQL

1.3.1 LÞch sö ph¸t triÓn cña ng«n ng÷ SQL

M« h×nh c¬ së d÷ liÖu (CSDL) quan hÖ do E.F Codd ®−a ra vµo ®Çu thËp kû 70, tõ ®ã ®Õn nay nã liªn tôc

ph¸t triÓn trë thµnh m« h×nh CSDL phæ biÕn bËc nhÊt (RDBMS). M« h×nh quan hÖ gåm c¸c thµnh phÇn sau:

• TËp hîp c¸c ®èi t−îng vµ/hoÆc c¸c mèi quan hÖ

• TËp hîp c¸c xö lý t¸c ®éng tíi c¸c quan hÖ

• Rµng buéc d÷ liÖu ®¶m b¶o tÝnh chÝnh x¸c vµ nhÊt qu¸n.

SQL (Structured Query Language, ®äc lµ "sequel") lµ tËp lÖnh truy xuÊt CSDL quan hÖ. Ng«n ng÷ SQL ®−îc

IBM sö dông ®Çu tiªn trong hÖ qu¶n trÞ CSDL System R vµo gi÷a nh÷ng n¨m 70, hÖ ng«n ng÷ SQL ®Çu tiªn

(SEQUEL2) ®−îc IBM c«ng bè vµo th¸ng 11 n¨m 1976. N¨m 1979, tËp ®oµn ORACLE giíi thiÖu th−¬ng

phÈm ®Çu tiªn cña SQL, SQL còng ®−îc cµi ®Æt trong c¸c hÖ qu¶n trÞ CSDL nh− DB2 cña IBM vµ SQL/DS.

Ngµy nay, SQL ®−îc sö dông réng r·i vµ ®uîc xem lµ ng«n ng÷ chuÈn ®Ó truy cËp CSDL quan hÖ.

1.3.2 ChuÈn SQL

N¨m 1989, viÖn tiªu chuÈn quèc gia Hoa kú (ANSI) c«ng nhËn SQL lµ ng«n ng÷ chuÈn ®Ó truy cËp CSDL

quan hÖ trong v¨n b¶n ANSI SQL89.

N¨m 1989, tæ chøc tiªu chuÈn quèc tÕ (ISO) c«ng nhËn SQL ng«n ng÷ chuÈn ®Ó truy cËp CSDL quan hÖ

trong v¨n b¶n ISO 9075-1989.

TÊt c¶ c¸c hÖ qu¶n trÞ CSDL lín trªn thÕ giíi cho phÐp truy cËp b»ng SQL vµ hÇu hÕt theo chuÈn ANSI.

1.4 C¸c kh¸i niÖm trong CSDL

Table lµ cÊu tróc l−u tr÷ c¬ b¶n nhÊt trong CSDL quan hÖ (RDBMS), nã bao gåm 1 hoÆc nhiÒu

column vµ 0 hoÆc nhiÒu row.

Row lµ tæ hîp nh÷ng gi¸ trÞ cña Column trong b¶ng. Mét row cßn cã thÓ ®−îc gäi lµ 1 record.

Column hiÓn thÞ mét lo¹i d÷ liÖu trong b¶ng, vÝ dô tªn phßng ban trong b¶ng phßng ban. Ng−êi ta thÓ

hiÖn nã th«ng qua tªn column vµ gi÷ sè liÖu d−íi c¸c kiÓu vµ kÝch cì nhÊt ®Þnh.

Field lµ giao cña column vµ row. Field chÝnh lµ n¬i chøa d÷ liÖu. NÕu kh«ng cã d÷ liÖu trong field

ng−êi ta nãi field cã gia trÞ lµ null.

Primary Key lµ mét column hoÆc mét tËp c¸c column x¸c ®Þnh tÝnh duy nhÊt cña c¸c row ë trong b¶ng. VÝ dô

m· phßng ban. Primary Key nhÊt thiÕt ph¶i cã sè liÖu.

Foreign Key lµ mét column hoÆc mét tËp c¸c column tham chiÕu tíi chÝnh b¶ng ®ã hoÆc mét b¶ng kh¸c.

Foreign Key x¸c ®Þnh mèi quan hÖ gi÷a c¸c b¶ng.

Constraint lµ c¸c rµng buéc d÷ liÖu, vÝ dô Foreign Key, Primary Key...