Giáo trình SQL server 2005: Phần 2

Chia sẻ: Trần Ngọc Lâm | Ngày: | Loại File: PDF | Số trang:45

Thêm vào BST

Báo xấu

99
lượt xem 15
download

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

Tiếp nối phần 1 giáo trình SQL server 2005 phần 2 trình bày các nội dung chính xoay quanh các ngôn ngữ định nghĩa dữ liệu, các thủ tục lưu trữ hàm và trigger, cách sao lưu và phục hồi dữ liệu. Các bạn học sinh và quý thầy cô hãy tham khảo để có thêm tư liệu trong việc giảng dạy và học tập.

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 server 2005: Phần 2

4 Ngôn ng đ nh nghĩa d li u – DDL Trong chương này s đ c p đ n nhóm các câu l nh đư c s d ng đ đ nh nghĩa v à qu n lý các đ i tư ng CSDL như b ng, khung nhìn, ch m c,... và đư c g i là ngôn ng đ nh nghĩa d li u (DDL). V cơ b n, ngôn ng đ nh nghĩa d li u bao g m các l nh: CREATE: đ nh nghĩa và t o m i đ i tư ng CSDL. ALTER: thay đ i đ nh nghĩa c a đ i tư ng CSDL. DROP: Xoá đ i tư ng CSDL đã có. 4.1 T o b ng Câu l nh CREATE TABLE đ ư c s d ng đ đ nh nghĩa m t b ng d li u m i trong CSDL. Khi đ nh nghĩa m t b ng d li u m i, ta c n ph i xác đ nh đ ư c các yêu c u sau đây: B ng m i đư c t o ra s d ng v i m c đích g ì và có vai trò như th nào trong cơ s d li u. C u trúc c a b ng bao g m nh ng tr ư ng (c t) nào, m i m t trư ng có ý nghĩa như th nào trong vi c bi u di n d li u, ki u d li u c a m i trư ng là gì và trư ng đó có cho phép nh n giá tr NULL hay không. Nh ng trư ng nào s tham gia vào khóa chính c a b ng. B ng có quan h v i nh ng b ng khác hay không và n u có thì quan h như th nào. Trên các trư ng c a b ng có t n t i nh n g ràng bu c v khuôn d ng, đi u ki n h p l c a d li u hay không; n u có th ì s d ng đâu và như th nào. Câu l nh CREATE TABLE có cú pháp nh ư sau CREATE TABLE tên_b ng ( tên_c t thu c_tính_c t các_ràng_bu c [,... ,tên_c t_n thu c_tính_c t_n các_ràng_bu c_c t_n] [,các_ràng_bu c_trên_b ng] ) Tên_b ng: tuân theo quy t c đ nh danh, không vư t quá 128 ký t Tên_c t: các c t trong b ng, m i b ng có ít nh t m t c t. Thu c_tính_c t: bao g m ki u d li u c a c t, giá tr m c đ nh c a c t, c t có đ ư c thi t l p thu c tính identity, c t có ch p nh n giá tr NULL hay không. Trong đó ki u d li u là thu c tính b t bu c. 56
Các_ràng_bu c: g m các ràng bu c v khuôn d ng d li u ( r àng bu c CHECK) hay các ràng bu c v bào toàn d li u (PRIMARY KEY, FOREIGN KEY, UNIQUE) Ví d : Ví d dư i đây t o m t b ng có tên CUSTOMERS create table customers ( customerid int identity (1,1) primary key, customername nvarchar(50) not null, address nvarchar(100 ) null , birthday datetime null, gender bit default('true') not null ) C t customerid có ki u d li u int, đư c ch đ nh thu c tính identity(1,1) nghĩa là d li u c t này đư c thêm t đ ng b t đ u t 1 và m i l n có dòng m i thêm vào, giá tr c t này đư c tăng lên 1. C t này cũng đư c ch đ nh làm khóa chính c a b ng thông qua thu c tính primary key Thu c tính NULL/ NOT NULL ch ra r ng c t đó có ch p nh n/ không ch p nh n giá tr NULL. C t gender đư c ch đ nh giá tr m c đ nh l à true nghĩa là n u không ch đ nh giá tr cho c t này thì c t này có giá tr là true Ví d : Thêm dòng m i vào b ng customers v i giá tr truy n v ào đ y đ cho các c t insert into customers values('Nguyen Van An', '22 Nguyen Thien Thuat', '5/5/1988', 'True') Thêm dòng m i vào b ng customers s d ng giá tr m c đ nh insert into customers (customername, addr ess, birthday) values('Nguyen Van An', '22 Nguyen Thien Thuat', '5/5/1988') Thêm dòng m i vào b ng customers và không truy n giá tr cho các c t cho phép giá tr NULL insert into customers (customername ) values('Nguyen Van An') 57
4.2 Các lo i ràng bu c 4.2.1 Ràng bu c CHECK Ràng bu c CHECK đư c s d ng nh m ch đ nh đi u ki n h p l đ i v i d li u. M i khi có s thay đ i d li u trên b ng (INSERT, UPDATE), nh ng r àng bu c này s đư c s d ng nh m ki m tra xem d li u m i có h p l hay không. Ràng bu c CHECK đư c khai báo theo cú pháp như sau: [CONSTRAINT tên_ràng_bu c] CHECK (đi u_ki n) Ví d : create table students ( studentid int identity(1,1) primary key, studentname nvarchar(50) not null, address nvarchar(100) not null, score1 tinyint not null constraint chk_score1 CHECK (score1 >= 0 and score1
score3 tinyint not null, constraint chk_score CHECK( (score1>= 0 and score1
address nvarchar(100) not null, gender bit not null ) Ví d 2: Đ nh nghĩa b ng có hai khóa chính: create table orderdetail ( customerid int, orderid int, itemid int not null, quantity decimal(8,2) not null, constraint chk_primarykey primary key (customerid, orderid) ) 4.2.3 Ràng bu c FOREIGN KEY FOREIGN KEY là m t c t hay m t s k t h p c a nhi u c t đ ư c s d ng đ áp đ t m i liên k t d li u gi a hai table. FOREIGN KEY c a m t b ng s gi giá tr c a PRIMARY KEY c a m t b ng khác và chúng ta có th t o ra nhi u FOREIGN KEY trong m t table. FOREIGN KEY có th tham chi u vào PRIMARY KEY hay c t có ràng bu c duy nh t. FOREIGN KEY có th ch a giá tr NULL. M c dù m c đích chính c a ràng bu c FOREIGN KEY là đ ki m soát d li u ch a trong b ng có FOREIGN KEY (t c table con) nhưng th c ch t nó cũng ki m soát luôn c d li u trong b ng ch a PRIMARY KEY (t c table cha). Ví d n u ta xóa d li u trong b ng cha thì d li u trong b ng con tr nên "m côi" (orphan) vì không th tham chi u ngư c v b ng cha. Do đó ràng bu c FOREIGN KEY s đ m b o đi u đó không x y ra. N u b n mu n xóa d li u trong b ng cha thì trư c h t b n ph i xóa hay vô hi u hóa ràng bu c FOREIGN KEY trong b ng con trư c. Ràng bu c FOREIGN KEY đư c đ nh nghĩa theo cú pháp d ư i đây: [CONSTRAINT tên_ràng_bu c] FOREIGN KEY [(danh_sách_c t)] REFERENCES tên_b ng_tham_chi u(danh_sách_c t_tham_chi u) [ON DELETE CASCADE | NO ACTION | SET NULL | SET DEFAULT] [ON UPDATE CASCADE | NO ACTION | SET NULL | SET DEFAULT] Vi c đ nh nghĩa m t ràng bu c FOREIGN KEY bao g m các y u t sau: Tên c t ho c danh sách c t c a b ng đư c đ nh nghĩa tham gia vào khoá ngoài. Tên c a b ng đư c tham chi u b i khoá ngo ài và danh sách các c t đư c tham chi u đ n trong b ng tham chi u. 60
Cách th c x lý đ i v i các b n ghi trong b ng đ ư c đ nh nghĩa trong trư ng h p các b n ghi đư c tham chi u trong b ng tham chi u b xoá (ON DELETE) hay c p nh t (ON UPDATE). SQL chu n đưa ra 4 cách x lý CASCADE: T đ ng xoá (c p nh t) n u b n ghi đ ư c tham chi u b xoá (c p nh t). NO ACTION: (M c đ nh) N u b n ghi trong b ng tham chi u đang đư c tham chi u b i m t b n ghi b t kỳ trong b ng đ ư c đ nh nghĩa thì bàn ghi đó không đư c phép xoá ho c c p nh t (đ i v i c t đ ư c tham chi u). SET NULL: C p nh t l i khoá ngoài c a b n ghi thành giá tr NULL (n u c t cho phép nh n giá tr NULL). SET DEFAULT: C p nh t l i khoá ngoài c a b n ghi nh n giá tr m c đ nh (n u c t có qui đ nh giá tr m c đ nh). Ví d : drop table orderdetail create table orderdetail ( orderid int constraint fk_orderdetail_orders foreign k ey references orders(orderid) on delete cascade on update cascade, customerid int constraint fk_orderdetail_customer foreign key references customers(customerid) on delete cascade on update cascade, itemid int constraint fk_orderdetail_items fore ign key references items(itemid) on delete cascade on update cascade, quantity decimal(18,2) not null, ) 4.3 S a đ i đ nh nghĩa b ng M t b ng sau khi đã đư c đ nh nghĩa b ng câu l nh CREATE TABLE có th đ ư c s a đ i thông qua câu l nh ALTER TABLE. Câu l nh này cho phép th c hi n đư c các thao tác sau: B sung m t c t vào b ng. 61
Xoá m t c t kh i b ng. Thay đ i đ nh nghĩa c a m t c t trong b ng. Xoá b ho c b sung các ràng bu c cho b ng Cú pháp c a câu l nh ALTER TABLE nh ư sau: ALTER TABLE tên_b ng ADD đ nh_nghĩa_c t | ALTER COLUMN tên_c t ki u_d _li u [NULL | NOT NULL] DROP COLUMN tên_c t | ADD CONSTRAINT tên_ràng_bu c đ nh_nghĩa_ràng_bu c DROP CONSTRAINT tên_ràng_bu c Ví d 1: Thêm m t c t m i vào b ng ORDERS alter table orders add description nvarchar(100) not null Ví d 2: Thay đ i đ nh nghĩa c t desciption alter table orders alter column description nvarchar(200) null Ví d 3: Thêm ràng bu c CHECK vào c t decription alter table orders add constraint chk_descriptionlength CHECK (len(description) > 10) Ví d 4: Xóa ràng bu c CHECK alter table orders drop chk_descriptionlength Ví d 5: Xóa c t description alter table orders drop column description Ví d 6: Thêm m t c t m i vào b ng orders và thêm ràng bu c cho c t này alter table orders add description nvarchar(100) null, constraint chk_descriptionlength CHECK (len(description) > 0) N u b sung thêm m t c t vào b ng và trong b ng đã có ít nh t m t b n ghi thì c t m i c n b sung ph i cho phép ch p nh n giá tr NULL ho c ph i có giá tr m c đ nh. Mu n xoá m t c t đang đư c ràng bu c b i m t ràng bu c ho c đang đư c tham chi u b i m t khoá ngoài, ta ph i xoá ràng bu c ho c khoá ngoài trư c sao cho trên c t không còn b t kỳ m t ràng bu c và không còn đư c tham chi u b i b t kỳ khoá ngo ài nào. 62
N u b sung thêm ràng bu c cho m t b ng đã có d li u và ràng bu c c n b sung không đư c tho mãn b i các b n ghi đã có trong b ng thì câu l nh ALTER TABLE không th c hi n đư c. 4.4 Xóa b ng Khi m t b ng không còn c n thi t , ta có th xoá nó ra kh i c ơ s d li u b ng câu l nh DROP TABLE. Câu l nh này cũng đ ng th i xoá t t c nh ng r àng bu c, ch m c, trigger liên quan đ n b ng đó. Câu l nh có cú pháp như sau: DROP TABLE tên_b ng Trong các h qu n tr cơ s d li u, khi đã xoá m t b ng b ng l nh DROP TABLE, ta không th khôi ph c l i b ng cũng nh ư d li u c a nó. Do đó, c n ph i c n th n khi s d ng câu l nh này. Câu l nh DROP TABLE không th th c hi n đ ư c n u b ng c n xoá đang đ ư c tham chi u b i m t ràng bu c FOREIGN KEY. Trong tr ư ng h p này, ràng bu c FOREIGN KEY đang tham chi u ho c b ng đang tham chi u đ n b ng c n xoá ph i đ ư c xoá trư c. Khi m t b ng b xoá, t t c các r àng bu c, ch m c và trigger liên quan đ n b ng cũng đ ng th i b xóa theo. Do đó, n u ta t o l i b ng thì cũng ph i t o l i các đ i t ư ng này. Ví d : Đ xóa b ng ORDERS trư c tiên ta ph i xóa ràng bu c FOREIGN KEY t b ng ORDERDETAIL alter table orderdetail drop constraint fk_orderdetail_orders Sau đó xóa b ng ORDERS drop table orders 4.5 Khung nhìn - VIEW Khung nhìn là m t b ng t m th i, có c u trúc nh ư m t b ng, khung nhìn không lưu tr d li u mà nó đư c t o ra khi s d ng, khung nh ìn là đ i tư ng thu c CSDL. Khung nhìn đư c t o ra t câu l nh truy v n d li u (l nh SELECT), truy v n t m t ho c nhi u b ng d li u. Khung nhìn đư c s d ng khai thác d li u nh ư m t b ng d li u, chia s nhi u ng ư i dùng, an toàn trong khai thác, không nh hư ng d li u g c. Có th th c hi n truy v n d li u tr ên c u trúc c a khung nhìn. 63
Như v y, m t khung nhìn trông gi ng như m t b ng v i m t tên khung nhìn và là m t t p bao g m các dòng và các c t. Đi m khác bi t gi a khung nh ìn và b ng là khung nhìn không đư c xem là m t c u trúc lưu tr d li u t n t i trong cơ s d li u. Th c ch t d li u quan sát đư c trong khung nhìn đư c l y t các b ng thông qua câu l nh truy v n d li u. Câu l nh CREATE VIEW đư c s d ng đ t o ra khung nh ìn và có cú pháp như sau: CREATE VIEW tên_khung_nhìn[(danh_sách_tên_c t)] AS câu_l nh_SELECT Ví d : create view CUSTOMERINFO as select CUSTOMERNAME, (year(getdate()) - year(BIRTHDAY)) as AGE, ADDRESS from customers Th c hi n câu truy v n trên khung nhìn v a t o ra: select * from customerinfo N u trong câu l nh CREATE VIEW, ta không ch đ nh danh sách các t ên c t cho khung nhìn, tên các c t trong khung nhìn s chính là tiêu đ các c t trong k t qu c a câu l nh SELECT. Trong trư ng h p tên các c t c a khung nhìn đươc ch đ nh, chúng ph i có cùng s lư ng v i s lư ng c t trong k t qu c a câu truy v n. Ví d : create view CUSTOMERINFO (CUSTOMERNAME, AGE, ADDRESS) as select CUSTOMERNAME, year(getdate()) - year(BIRTHDAY), ADDRESS from customers Lưu ý: Ph i đ t tên cho các c t c a khung nhìn trong các trư ng h p sau đây: 64
Trong k t qu c a câu l nh SELECT có ít nh t m t c t đ ư c sinh ra b i m t bi u th c (t c là không ph i là m t tên c t trong b ng cơ s ) và c t đó không đư c đ t tiêu đ . T n t i hai c t trong k t qu c a câu l nh SELECT có c ùng tiêu đ c t. 4.6 Thêm, c p nh t, xóa d li u trong VIEW Đ i v i m t s khung nhìn, ta có th ti n hành th c hi n các thao tác c p nh p, thêm và xoá d li u. Th c ch t, nh ng thao tác n ày s đư c chuy n thành nh ng thao tác trên các b ng cơ s và có tác đ ng đ n nh ng b ng cơ s . V m t lý thuy t, đ có th th c hi n thao tác b sung, c p nh t v à xoá, m t khung nhìn trư c tiên ph i tho mãn các đi u ki n sau đây: Trong câu l nh SELECT đ nh nghĩa khung nh ìn không đư c s d ng t khoá DISTINCT, TOP, GROUP BY và UNION. Các thành ph n xu t hi n trong danh sách ch n c a câu l nh SELECT ph i l à các c t trong các b ng cơ s . Trong danh sách ch n không đ ư c ch a các bi u th c tính toán, các h àm g p. Ngoài nh ng đi u ki n trên, các thao tác thay đ i đ n d li u thông qua khung nh ìn còn ph i đ m b o tho mãn các ràng bu c trên các b ng cơ s , t c là v n đ m b o tính toàn v n d li u. M c dù thông qua khung nhìn có th th c hi n đư c thao tác b sung và c p nh t d li u cho b ng cơ s nhưng ch h n ch đ i v i nh ng khung nh ìn đơn gi n. Đ i v i nh ng khung nhìn ph c t p thì thư ng không th c hi n đư c; hay nói cách khác là d li u trong khung nhìn là ch đ c. 4.7 Thay đ i đ nh nghĩa khung nh ìn Câu l nh ALTER VIEW dùng đ đ nh nghĩa l i khung nh ìn có c u trúc như sau: ALTER VIEW tên_khung_nhìn [(danh_sách_tên_c t)] AS Câu_l nh_SELECT Ví d : Ví d dư i đây đ nh nghĩa l i khung nhìn CUSTOMERINFO alter view customerinfo as select CUSTOMERNAME, (year(getdate()) - year(birthday)) as AGE, ADDRESS, GENDER from customers Lưu ý: l nh CREATE VIEW không làm thay đ i các quy n đã đư c c p phát cho ngư i s d ng trư c đó. 65
4.8 Xóa khung nhìn Câu l nh DROP VIEW dùng đ xóa khung nhìn có c u trúc như sau: DROP VIEW tên_khung_nhìn Ví d : drop view customerinfo Lưu ý: N u m t khung nhìn b xoá, toàn b nh ng quy n đã c p phát cho ngư i s d ng trên khung nhìn cũng đ ng th i b xoá. Do đó, n u ta t o l i khung nhìn thì ph i ti n hành c p phát l i quy n cho ngư i s d ng. 66
5 Th t c lưu tr , hàm và trigger 5.1 Th t c lưu tr (Stored procedure) Th t c lưu tr là m t đ i tư ng trong CSDL, bao g m nhi u câu l nh T-SQL đư c t p h p l i v i nhau thành m t nhóm, và t t c các l nh này s đư c th c thi khi th t c l ưu tr đư c th c thi. V i th t c lưu tr , m t ph n nào đó kh năng c a ngôn ng l p tr ình đư c đưa vào trong ngôn ng SQL. Th t c lưu tr có th có các thành ph n sau: Các c u trúc đi u khi n (IF, WHILE, FOR ) có th đư c s d ng trong th t c. Bên trong th t c lưu tr có th s d ng các bi n nh ư trong ngôn ng l p trình nh m lưu gi các giá tr tính toán đ ư c, các giá tr đư c truy xu t đư c t cơ s d li u. M t t p các câu l nh SQL đư c k t h p l i v i nhau th ành m t kh i l nh bên trong m t th t c. M t th t c có th nh n các tham s truy n v ào cũng như có th tr v các giá tr thông qua các tham s (như trong các ngôn ng l p trình). Khi m t th t c lưu tr đã đư c đ nh nghĩa, nó có th đư c g i thông qua tên th t c, nh n các tham s truy n v ào, th c thi các câu l nh SQL bên trong th t c và có th tr v các giá tr sau khi th c hi n xong. L i ích c a vi c s d ng th t c l ưu tr : SQL Server ch biên d ch các th t c lưu tr m t l n và s d ng l i k t qu biên d ch này trong các l n ti p theo tr khi ngư i dùng có nh ng thi t l p khác. Vi c s d ng l i k t qu biên d ch không làm nh hư ng đ n hi u su t h th ng khi th t c l ưu tr đư c g i liên t c nhi u l n. Th t c lưu tr đư c phân tích, t i ưu khi t o ra nên vi c th c thi chúng nhanh h ơn nhi u so v i vi c ph i th c hi n m t t p r i r c các câu l nh SQL t ương đương theo cách thông thư ng. Th t c lưu tr cho phép chúng ta th c hi n c ùng m t yêu c u b ng m t câu l nh đơn gi n thay vì ph i s d ng nhi u dòng l nh SQL. Đi u này s làm gi m thi u s lưu thông trên m ng. Thay vì c p phát quy n tr c ti p cho ng ư i s d ng trên các câu l nh SQL và trên các đ i tư ng cơ s d li u, ta có th c p phát quy n c ho ngư i s d ng thông qua các th t c l ưu tr , nh đó tăng kh năng b o m t đ i v i h th ng. Các th t c lưu tr tr v k t qu theo 4 cách: S d ng các tham s output S d ng các l nh tr v giá tr , các l nh n ày luôn tr v giá tr s nguyên. 67
T p các giá tr tr v c a m i câu l nh SELECT có trong th t c l ưu tr ho c c a quá trình g i m t th t c lưu tr khác trong m t th t c l ưu tr . M t bi n con tr toàn c c có th tham chi u t b ên ngoài th t c. 5.1.1 T o th t c lưu tr Th t c lưu tr đư c t o thông qua câ.u l nh CREATE PROCEDURE. CREATE PROCEDURE tên_th _t c [(danh_sách_tham_s )] [WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION] AS Các_câu_l nh_c a_th _t c Trong đó: WITH RECOMPILE: yêu c u SQL Server biên d ch l i th t c lưu tr m i khi đư c g i. WITH ENCRYPTION: yêu c u SQL Server mã hóa th t c lưu tr . Các_câu_l nh_c a_th _t c: Các l nh T-SQL. Các l nh này có th n m trong c p BEGIN…END ho c không. Ví d : Gi s c n th c hi n các công vi c theo th t nh ư sau: Nh p m t đơn đ t hàng m i c a khách hàng có mã khách hàng là 3 Nh p các chi ti t đơn đ t hàng cho đơn đ t hàng trên. Đ th c hi n các công vi c tr ên chúng ta c n các câu l nh như sau: Trư c tiên nh p đơn đ t hàng cho khách hàng có mã khách hàng là 3 insert into orders values(3, '7/22/2008') Ti p theo thêm các chi ti t đơn đ t hàng cho hóa đơn này. Gi s r ng đơn đ t hàng có mã là 4 và khách hàng đ t m t m t hàng có mã là 1. insert into orderdetail values(4, 1, 10) Cách vi t như trên có h n ch là: trong quá trình làm vi c s có r t nhi u đơn đ t hàng m i, do đó ngư i dùng s ph i vi t đi vi t l i nh ng câu l nh t ương t nhau cho các khách hàng khác nhau. M t cách gi i quy t v n đ n ày là dùng th t c lưu tr và dùng tham s đ nh n các thông tin thay đ i. create procedure sp_InsertOrderAndOrderD etail @customerid int, @orderdate datetime, @orderid int, @itemid int, 68
@quantity decimal, as begin insert into orders values(@customerid, @orderdate) insert into orderdetail values(@orderid, @itemid, @quantity) end Th c hi n th t c lưu tr này như sau: sp_InsertOrderAndOrderDetail ‘3’, ‘22/7/2008’, ‘4’, ‘1’, ‘10’) 5.1.2 L i g i th t c Th t c lưu tr đư c g i theo c u trúc Tên_th _t c_lưu _tr [danh_sách_tham_s ] C n lưu ý là danh sách tham s truy n vào trong l i g i ph i theo đúng th t khai báo các tham s trong th t c lưu tr . N u th t c đư c g i t m t th t c khác, th c hi n b ên trong m t trigger hay ph i h p v i câu l nh SELECT, c u trúc nh ư sau; Exec Tên_th _t c_lưu _tr [danh_sách_tham_s ] 5.1.3 Bi n trong th t c lưu tr Trong th t c lưu trũ có th có các bi n nh m lưu các k t qu tính toán hay truy xu t t CSDL. Các bi n trong th t c đư c khai báo b ng t khóa DECLARE theo c u trúc nh ư sau: DECLARE @tên_bi n ki u_d _li u Ví d : create procedure sp_SelectCustomerWithMaxAge as begin declare @maxAge int select @maxAge = max(year(getdate()) -year(BIRTHDAY)) from customers select CUSTOMERNAME, BIRTHDAY from customers where year(getdate())-year(BIRTHDAY)=@maxAge 69
end 5.1.4 Giá tr tr v trong th t c lưu tr Trong các ví d trư c, n u đ i s truy n c ho th t c khi có l i g i đ n th t c l à bi n, nh ng thay đ i giá tr c a bi n trong th t c s không đ ư c gi l i khi k t thúc quá tr ình th c hi n th t c. Ví d : Có th t c lưu tr như sau create procedure sp_TestOutput @a int, @b int, @c int as select @c = @a + @b Th c thi th t c: Declare @tong int set @tong = 0 sp_TestOutput 100, 200, @tong select @tong K t qu là 0. S d ng tham s OUTPUT Trong trư ng h p c n ph i gi l i giá tr c a đ i s sau khi k t thúc th t c, ta ph i khai báo tham s c a th t c theo cú pháp như sau: @tên_tham_s ki u_d _li u OUTPUT Ví d trên đư c vi t l i như sau: create procedure sp_TestOutput @a int, @b int, @c int output as select @c = @a + @b Th c thi th t c: Declare @tong int set @tong = 0 sp_TestOutput 100, 100, @tong output select @tong 70
K t qu là 200. S d ng l nh RETURN Tương nh như vi c s d ng tham s OUTPUT, câu l nh RETURN tr v giá tr cho đ i tư ng th c thi stored procedure. Ví d : create procedure sp_TestReturn as begin declare @out int select @out = count(*) from customers return @out end Th c thi th t c lưu tr declare @a int exec @a = sp_TestReturn select @a 5.1.5 Tham s v i giá tr m c đ nh Các tham s đư c khai báo trong th t c có th nh n các giá tr m c đ nh. Giá tr m c đ nh s đư c gán cho tham s trong trư ng h p không truy n đ i s cho tham s khi có l i g i đ n th t c. Tham s v i giá tr m c đ nh đ ư c khai báo theo cú pháp nh ư sau: @tên_tham_s ki u_d _li u = giá_tr _m c_đ nh Ví d : create procedure sp_TestDefault @customerid int = 3 as begin select * from customers where customerid = @customerid end Th c thi th t c lưu tr theo giá tr m c đ nh c a tham s . sp_TestDefault 71
Th c thi th t c và truy n giá tr cho tham s : sp_TestDefault 4 5.1.6 S a đ i th t c Khi m t th t c đã đư c t o ra, ta có th ti n hành đ nh nghĩa l i th t c đó b ng câu l nh ALTER PROCEDURE có cú pháp như sau: ALTER PROCEDURE tên_th _t c [(danh_sách_tham_s )] [WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION] AS Các_câu_l nh_c a_th _t c Câu l nh này s d ng tương t như câu l nh CREATE PROCEDURE. Vi c s a đ i l i m t th t c đã có không làm thay đ i đ n các quy n đã c p phát trên th t c cũng như không tác đ ng đ n các th t c khác hay trigger ph thu c v ào th t c này. 5.1.7 Xóa th t c Đ xoá m t th t c đã có, ta s d ng câu l nh DROP PROCEDURE v i cú pháp như sau: DROP PROCEDURE tên_th _t c Khi xoá m t th t c, t t c các quy n đ ã c p cho ngư i s d ng trên th t c đó cũng đ ng th i b xoá b . Do đó, n u t o l i th t c, ta ph i ti n h ành c p phát l i các quy n trên th t c đó. 5.2 Hàm do ngư i dùng đ nh nghĩa (User Defined Function-UDF) Hàm do ngư i dùng đ nh nghĩa đư c chia làm 3 lo i: (1) scalar (hàm vô hư ng), (2) inline table-valued (hàm n i tuy n, giá tr tr v d ng b ng), (3) multi -statement table-valued (hàm bao g m nhi u câu l nh SQL b ên trong, tr v giá tr d ng b ng) Scalar UDF: đư c s d ng đ tr v m t duy nh t m t giá tr d a tr ên m t các tham s truy n vào. Ví d : ta có th t o ra m t UDF vô h ư ng nh n Customerid là tham s và tr v CustomerName. Inline table-valued: tr v m t b ng d a trên m t câu l nh SQL duy nh t đ nh nghĩa các dòng và các c t tr v . 72
Multi-statement table-value: cũng tr v k t qu là m t t p h p nhưng có th d a trên nhi u câu l nh SQL. 5.2.1 Hàm vô hư ng - Scalar UDF Scarlar UDF đư c t o ra b ng câu l nh CREATE FUNCTION có c u trúc nh ư sau; CREATE FUNCTION tên_hàm ([danh_sách_tham_s ]) RETURNS (ki u_tr _v _c a_h àm) AS BEGIN các_câu_l nh_c a_hàm END Ví d : Câu l nh dư i đây đ nh nghĩa hàm tính ngày trong tu n (th trong tu n) c a m t giá tr ki u ngày create function f_ thu(@ngay datetime) returns nvarchar(10) as begin declare @st nvarchar(10) select @st=case datepart(dw,@ngay) when 1 then N'ch nh t' when 2 then N'th hai' when 3 then N 'th ba' when 4 then N 'th tư' when 5 then N 'th năm' when 6 then N 'th sáu' else N 'th b y' end return (@st) /* tr tr v c a hàm */ end Sau khi ch y thành công, hàm tr thành m t đ i tư ng trong CSDL và có th đư c truy xu t như các hàm đư c xây d ng s n trong SQL Server 2005 Express Edition. 73
Ví d : select CUSTOMERNAME, dbo.f_thu(BIRTHDAY) from customers 5.2.2 Hàm n i tuy n - Inline UDF Hàm n i tuy n đư c đ nh nghĩa b ng l nh CREATE FUNCTION. CREATE FUNCTION tên_hàm ([danh_sách_tham_s ]) RETURNS TABLE AS RETURN (câu_l nh_select) Cú pháp c a hàm n i tuy n ph i tuân theo các qui t c sau: Ki u tr v c a hàm ph i đư c ch đ nh b i m nh đ RETURNS TABLE. 74
Trong ph n thân c a hàm ch có duy nh t m t câu l nh RETURN xác đ nh giá tr tr v c a hàm thông qua duy nh t m t câu l nh SELECT. Ngoài ra, không s d ng b t kỳ câu l nh nào khác trong ph n thân c a hàm. Ví d : Ví d dư i đây l y ra các khách h àng tùy thu c vào giá tr mã khách hàng truy n vào cho tham s . create function f_SelectCustomer (@customerid int) returns table as return (select * from customers where customerid > @customerid) Vi c g i các hàm n i tuy n cũng tương t như vi c g i các hàm vô hư ng. Ví d : select tmp.CUSTOMERNAME, o.ORDERDATE from orders o inner join dbo.f_SelectCustomer(3) as tmp on o.customerid = tmp.customerid 5.2.3 Hàm bao g m nhi u câu l nh bên trong – Multi statement UDF Hàm này cũng đư c đ nh nghĩa b ng l nh CREATE FUNCTION CREATE FUNCTION tên_hàm ([danh_sách_tham_s ]) RETURNS @bi n_b ng TABLE đ nh_nghĩa_b ng AS BEGIN các_câu_l nh_trong_thân_hàm RETURN END Lưu ý: sau t khóa RETURNS là m t bi n b ng đư c đ nh nghĩa. Và sau t khóa RETURN cu i hàm không có tham s nào đi kèm. Ví d : create function f_SelectCustomer (@customerid int) returns @myCustomers table ( customerid int, 75