Giáo trình SQL Server 2000: Phần 2

Chia sẻ: Minh Vũ | Ngày: | Loại File: PDF | Số trang:117

Thêm vào BST

Báo xấu

95
lượt xem 16
download

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

Nối tiếp nội dung phần 1 cuốn giáo trình "SQL Server 2000", phần 2 giới thiệu tới người đọc các nội dung: Tạo và quản lý Stored procedure, tạo và sử dụng Trigger, tự động hóa các tác vụ quản trị, sao lưu dữ liệu, khôi phục dữ liệu, quản lý bảo mật và người dùng. Mời các bạn cùng tham khảo nội dung chi tiết.

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

® C h ư ơ n g 10: Tạo và quán lý Stored Procedure loi m Chương 10: TẠO VÀ QUẢN LÝ STORED PROCEDURE Chương này đề cập các vấn đề sau:
w 102 C h ư ơ n g 10: Tạo và quản lý Stored Procedure Ú n g dụng của bạn có t h ể giao t i ế p với SQL Server theo 2 cách, bạn có t h ể v i ế t chương t r ì n h gởi p h á t biểu T-SQL t ừ client đ ế n server hoặc bạn tạo stored procedure, c h ú n g được lưu v à thực t h i ở server. N ế u bạn gởi p h á t biểu T-SQL từ client đ ế n server, các p h á t b i ể u được gởi qua mạng và được SQL Server b i ê n dịch l ạ i m ỗ i k h i thực t h i c h ú n g . N ế u bạn dùng stored procedure, bạn có t h ể thực t h i stored procedure b ằ n g cách gọi chúng từ ứng dụng của b ạ n b ằ n g m ộ t p h á t biểu. N h ư đ ã nói l ầ n đầu t i ê n stored procedure được thực t h i n ó được SQL Server b i ê n dịch v à k ế hoạch thực t h i của nó được tạo v à lưu t r o n g v ù n g n h ớ đ ệ m . SQL Server dùng l ạ i k ế hoạch thực t h i n à y k h i stored procedure được g ọ i l ạ i mà k h ô n g p h ả i b i ê n dịch l ạ i . N h ư v ậ y k h i các p h á t biểu T-SQL được xử lý thường xuyên, sử dụng stored procedure sẽ g i ả m lưu t h ô n g m ạ n g v à hiệu quả, nhanh h ơ n l à việc gởi các p h á t biểu t ừ client đ ế n server. Có 3 k i ể u t h ủ tục: stored procedure h ệ t h ố n g , stored procedure bên ngoài và stored procedure do người d ù n g đ ị n h nghĩa. Stored procedure hệ thống do SQL Server cung cấp có t ê n b ắ t đ ầ u b ằ n g t i ề n t ố sp_. Chúng được d ù n g để quản lý SQL Server v à h i ể n t h ị các t h ô n g t i n v ề CSDL và người dùng. Stored procedure m ở r ộ n g là những t h ư v i ệ n l i ê n k ế t động (DLL) m à SQL Server có t h ể nạp v à thực t h i . T h ư ờ n g c h ú n g được viết bằng c hoặc C++. Stored procedure b ê n n g o à i có t ê n b ắ t đ ầ u là xp_. Stored procedure do người d ù n g đ ị n h nghĩa được tạo v à t ù y chỉnh đ ể thực t h i bất kỳ t á c vụ n à o người d ù n g muốn. Chú ý: Không nên đặt tên cho thủ tục của bạn (thủ tục do người dùng định nghĩa) với tiền tô sp_ vi khi SQL Seruer gặp thủ tục với tên bắt đầu là sp_ SQL Server trước hết sẽ tìm trong CSDL master. Vì thế giả sử nếu bạn tạo thủ tục sp_myproc trong CSDL MyDB, SQL Server sẽ tìm trong CSDL master trước khi nó tìm trong bất kỳ CSDL nào khác của người dùng. Như vậy đặt tên myproc sẽ hiệu quả hon. 2. T Ạ O S T O R E D PROCEDURE Trong p h ầ n n à y b ạ n sẽ xem 3 p h ư ơ n g p h á p tạo stored procedure" dùng p h á t biểu CREATE PROCEDURE, d ù n g Enterpri.se M a n ă g e r và dùng Create Stored Procedure wizard. Dù chọn p h ư ơ n g p h á p n à o cũng
C h ư ơ n g 10: Tạo và quản lý Stored Procedure luôn đ ả m bảo r ằ n g b ạ n p h ả i chạy k i ể m t r a t h ử cho đ ế n k h i nó hoạt động đúng ý bạn muốn. 2.1. Dùng T-SQL tạo stored procedure Chú ý là phải chọn CSDL bạn muốn tạo stored procedure trước khi tạo stored procedure b ằ n g l ệ n h USE. G i ả sử bạn muốn tạo một stored procedure đ ặ t t ê n là DienThoaiKH, t h ủ tục n à y chọn và t r ả về 3 cột dữ liệu của từng dòng dữ liệu trong bảng K H A C H _ H A N G với điều k i ệ n khách h à n g có sôi đ i ệ n thoại. Trước k h i tạo t h ú tục chúng ta k i ể m tra xem t h ủ tục n à y có t ồ n t ạ i hay chưa d ù n g l ệ n h I F EXIST... N ế u t h ủ tục tồn t ạ i thì xóa nó và sau đó tạo t h ủ tục có cùng t ê n . L ệ n h thực h i ệ n n h ư sau: USE MyDB GO I F EXISTS (SELECT name FROM s y s o b j e c t s VÍHERE rĩame = • D i e n T h o a i K H ' AND type = 'p') DROP PROCEDURE D i e n T h o a i K H GO CREATE PROCEDURE DienThoaiKH AS SELECT HO_KH, TEN_KH, DIEN_THOAI FROM KHACH_HANG WHERE D I E N _ T H O A I I S NÓT NULL GO Thực thi đoạn mã T-SQL trên sẽ tạo ra thủ tục DienThoaiKH, để thực t h i thủ tục n à y bạn gọi t ê n t h ủ tục n h ư sau: USE MyDB GO exec DienThoaiKH Nếu bạn không chọn đúng CSDL có chứa thủ tục bạn sẽ thực thi, SQL Server sẽ t h ô n g báo l ỗ i sau: Server: Msg 2812, Level 16, state 62, Line Ì Could nót find stored procedure ' DienThoaiKH * .
® 104 C h ư ơ n g 10: Tạo và quản lý Stored Procedure Sử d ụ n g tham số Bây giờ c h ú n g ta sẽ tạo m ộ t t h a m số n h ậ p cho t h ủ tục v à có t h ể t r u y ề n dữ l i ệ u vào t h ủ tục. Đ ể chỉ ra t h a m số n h ậ p đ ặ t k ý h i ệ u @ trước t ê n t h ủ tục có dạng @tên_thutuc. B ạ n có t h ể chỉ r a đ ế n 1024 t h a m số trong t h ủ tục. G i ả sử tạo m ộ t t h ủ tục KhachHang có t h a m số n h ậ p là @sodt. K h i thực t h i t h ủ tục n à y b ạ n cung cấp số đ i ệ n t h o ạ i , t h ủ tục sẽ trả về dòng dữ liệu có số đ i ệ n t h o ạ i b ằ n g số b ạ n cung cấp cho t h a m sô. Tạo t h ủ tục n h ư sau: USE MyDB GO I F EXISTS (SELECT name FROM sysobjects VỈHERE name = ' K h a c h H a n g ' AND type = 'P') DROP PROCEDURE K h a c h H a n g GO CREATE PROCEDURE K h a c h H a n g @sodt c h a r ( 2 0 ) AS SELECT HO_KH, TEN_KH, DIEN_THOAI FROM KHACH_HANG VÍHERE DIEN_THOAI = @SOdt GO Nếu bạn thực t h i t h ủ tục m à k h ô n g cung cấp t h a m số, SQL Server sẽ t r ả về t h ô n g điệp l ỗ i sau: S e r v e r : Msg 201, Level 16, state 3, Procedure KhachHang, L i n e 0 Procedure 'KhachHang' expects parameter '
C h ư ơ n g 10: Tạo và quản lý Stored Procedure 105 o B ạ n có t h ể t ạ o t h ủ tục cung cấp giá t r ị mặc đ ị n h cho t h a m số. N ế u tham số k h ô n g được chỉ ra t h ì giá t r ị mặc định sẽ được dùng. Cách tạo n h ư sau: USE MyDB GO I F EXISTS (SELECT name FROM sysobjects VÍHERE name = ' K h a c h H a n g ' AND type = 'p ' ) DROP PROCEDURE K h a c h H a n g GO CREATE PROCEDURE K h a c h H a n g @sodt c h a r ( 2 0 ) = ' 8632157 • AS SELECT HO_KH, TEN_KH, DIEN_THOAI FROM KHACH_HANG VÍHERE D I E N _ T H O A I = @SOdt GO Bây giờ n ế u b ạ n thực t h i t h ủ tục m à k h ô n g cung cấp t h a m số t h ì số điện thoại mặc đ ị n h '8632157' được dùng. Để t r ả về giá t r ị của t h a m số trong t h ủ tục cho chương t r ì n h gọi, sử dụng từ k h ó a O U T P U T sau t ê n t h a m số. Giá sứ tạo t h ú tục XemDonGia có tham số nhập ©ma_hang là m ã h à n g , t h a m số xuất @don_gia là đơn giá sẽ được t r ả về. USE MyDB GO I F EXISTS (SELECT name FROM sysobjects WHERE name = ' X e m D o n G i a ' AND type = ' p ' ) DROP PROCEDURE XemDoriGia
ÃĂI® sa 106 C h ư ơ n g 10: Tạo và quản lý Stored Procedure GO CREATE PROCEDURE XemDonGia @ma_hangỉnt, @don_gia money OUTPUT AS SELECT @ d o n _ g i a = DON_GIA FROM MAT_HANG VÍHERE MA_HANG = @ma_hang GO Đ ể lưu giá t r ị t r ả về từ t h ủ tục, b ạ n p h ả i k h a i b á o m ộ t b i ế n cục bộ trong chương t r ì n h gọi, trước k h i b ạ n có t h ể d ù n g nó t r o n g t h ủ tục. Ví dụ trước h ế t bạn khai báo m ộ t b i ế n ©giaban có k i ể u là money (lưu ý l à kiểu dữ liệu của b i ế n n à y p h ả i tương đương với k i ể u dữ l i ệ u của t h a m số xuất) và sau đó thực t h i t h ủ tục n h ư sau: DECLARE @ g i a b a n m o n e y e x e c XemDonGia Ì , @ d o n _ g i a = @ g i a b a n OUTPUT PRINT CONVERT (VARCHAR ( 6 ) , @ g i a b a n ) GO P h á t biểu P R I N T i n giá t r ị t r ả v ề của © g i a b a n l à 19.00. H à m CONVERT được d ù n g đ ể chuyển giá t r ị của © g i a b a n t ừ k i ể u money về k i ể u varchar đ ể c h ú n g ta có t h ể i n nó n h ư l à m ộ t chuỗi k ý t ự hoặc n h ư là một k i ể u m à có t h ể chuyển đ ổ i h o à n t o à n sang k ý t ự , đ â y l à đòi h ỏ i của p h á t biểu PRINT. N ế u b ạ n k h ô n g chuyển sang k i ể u k ý t ự hoặc SQL Server k h ô n g t ự động chuyển sang k i ể u k ý t ự được, SQL Server sẽ t r ả về t h ô n g điệp l ỗ i sau: S e r v e r : Msg 2 5 7 , L e v e l 1 6 , s t a t e 3 , L i n e 3 I m p l i c i t c o n v e r s i o n f rom d a t a t y p e money t o n v a r c h a r i s n ó t a l l o w e d . Use t h e CONVERT f u n c t i o n t o r u n t h ĩ s query.
C h ư ơ n g 10; Tạo và quản lý Stored Procedure 107 ffl Sử d ụ n g b i ế n cục b ộ N h ư bạn t h ấ y ở p h ầ n t r ê n , từ k h ó a DECLARE được d ù n g đ ể tạo biến cục bộ. B ạ n p h ả i chỉ ra t ê n và k i ể u dữ l i ệ u cửa b i ế n cục bộ k h i tạo nó và phải đ ặ t dấu @ trước t ê n b i ế n . K h i một b i ế n được tạo giá trị k h ở i tạo của nó là N U L L . Các b i ế n cục bộ được khai báo trong bó l ệ n h , trong chương t r ì n h gọi (script) hoặc trong t h ủ tục. Các b i ế n thường được d ù n g trong t h ủ tục để giữ các giá trị sẽ được k i ể m t r a trong p h á t biểu điều k i ệ n và đ ể giữ các giá t r ị sẽ được t r ả về bởi p h á t biểu RETURN. T ầ m vực của b i ế n cục bộ trong stored procedure là từ đ i ể m biên được khai báo cho đ ế n k h i stored procedure t h o á t . Ngay k h i t h ủ tục k ế t thúc b i ế n đó k h ô n g còn được tham k h á o nữa. Ví dụ tạo m ộ t t h ủ tục chứa b i ế n cục bộ. Thủ tục n à y sẽ t h ê m 5 dòng dữ liệu vào m ộ t b ả n g bằng câu trúc vòng lặp W H I L E . Bây giờ trước hết bạn h ã y tạo một b ả n g bangvidu và một t h ủ tục them_dong trong CSDL M y D B . B i ế n cục bộ c h ú n g ta d ù n g trong t h ủ tục là @dem_vong và @bat_dau sẽ được k h a i báo t á c h b i ệ t nhau bằng dấu phẩy ( , ). Thực t h i lệnh T-SQL sau đ ể tạo b ả n g và t h ủ tục: USE MyDB GO CREATE TABLE b a n g v i d u ( sott int, diengiai char(30) ) GO CREATE PROCEDURE t h e m _ d o n g @ b a t d a u i n t AS DECLÀRE @dem_vong i n t , @ b a t _ d a u i n t SÉT
® 108 C h ư ơ n g 10: Tạo và quàn lý Stored Procedure INSERT INTO b a n g v i d u VALUES (@bat_dau + Ì , 'thèm dong moi ) 1 PRINT ( @ b a t _ d a u ) SÉT @ b a t _ d a u = @ b a t _ d a u + Ì SÉT @dein_vong = @dem_vong + Ì END GO Bây giờ bạn h ã y thực t h i t h ủ tục n à y v ớ i giá t r ị b ắ t đ ầ u (©batdau) là Ì n h ư sau: exec them_dong Ì GO B ạ n sẽ t h ấ y 5 giá trị dược i n ra cho b i ế n cục bộ @bat_dau l à : 0, Ì, 2, 3 và 4. Chọn t ấ t cả các d ò n g t ừ b ả n g bangvidu b ằ n g p h á t biểu SELECT sau: SELECT * F R O M bangvidu GO K ế t quả t r ả về n h ư sau: sott dỉengiai Ì t h è m dong moi 2 t h è m dong moi 3 t h è m dong moi 4 t h è m dong moi 5 t h è m dong moi (5 r o w ( s ) a f f e c t e d ) Sau k h i t h ủ tục h o à n t ấ t , 2 b i ế n cục bộ @bat_dau v à @dem_vong k h ô n g t h ể được truy xuất nữa. B ạ n sẽ n h ậ n t h ô n g điệp l ỗ i n ế u ban cố gắng i n chúng ra m à n h ì n h d ù n g p h á t biểu T-SQL sau: PRINT ( @ b a t _ d a u ) PRINT (@dem_vong) GO
C h ư ơ n g 10: Tạo và quản lý Stored Procedure 109 ffl T h ô n g điệp l ỗ i sẽ là: S e r v e r : Msg 1 3 7 , L e v e l 1 5 , s t a t e 2 , L i n e Ì Must d e c l a r e t h e v a r ỉ a b l e '@bat_dau' . S e r v e r : Msg 1 3 7 , L e v e l 1 5 , s t a t e Ì , L i n e 2 M u s t d e c l a r e t h e v a r i a b l e ' @dem_vong . 1 Cùng một qui tắc về t ầ m vực của biên cũng được áp dụng k h i bạn thực t h i một bó l ệ n h . Ngay k h i từ k h ó a GO được p h á t h à n h (nó đ á n h dấu k ế t thúc bó lệnh), b ấ t k ỳ b i ế n cục bộ n à o được khai báo trong bó l ệ n h đều k h ô n g t h ể truy xuất nữa. T ầ m vực của b i ế n cục bộ chỉ n ằ m trong bó lệnh. Đế hiếu rõ h ơ n về điều n à y bạn h ã y thực t h i l ạ i t h ủ tục XemDonGia: USE MyDB GO DECLARE @ g i a b a n m o n e y e x e c XemDonGia Ì , @ d o n _ g i a = @ g i a b a n OUTPUT PRINT CONVERT (VARCHAR ( 6 ) , @ g i a b a n ) GO PRINT CONVERT (VARCHAR ( 6 ) , (ạgiaban) GO P h á t biểu i n t h ứ n h ấ t sẽ i n b i ế n cục bộ ©giaban từ trong bó lệnh, phát biểu n à y t h à n h công và i n ra giá t r ị 19.00. P h á t biểu i n t h ứ hai cố gắng i n l ạ i nó b ê n ngoài bó l ệ n h , n h ư n g p h á t biểu n à y sẽ t r ả về t h ô n g diệp l ỗ i . K ế t xuất n h ư sau: 19.00 S e r v e r : Msg 1 3 7 , L e v e l 1 5 , s t a t e 2 , L i n e 2 Must d e c l a r e t h e v a r i a b l e '@giaban' . Sử d ụ n g R E T U R N Bạn có t h ể trở về chương t r ì n h gọi từ bất cứ đ i ể m nào trong t h ủ tục bằng cách d ù n g t ừ k h ó a RETURN để t h o á t k h ô n g điều k i ệ n k h ỏ i thủ tục. RETURN cũng có t h ể được dùng từ bó lệnh. K h i RETURN được thực thi trong thú tục, việc thực t h i sẽ bị dừng từ đ i ể m đó trong t h ú tục và trở về p h á t biểu t i ế p theo trong chương t r ì n h gọi. Các p h á t biểu sau
lũn® ssy n o C h ư ơ n g 10: Tạo và quản lý Stored Procedure RETURN sẽ k h ô n g được thực t h i . Cũng có t h ể t r ả v ề m ộ t số n g u y ê n bằng RETURN. B ạ n sẽ t h ấ y ví dụ sau d ù n g R E T U R N đ ể t h o á t k h ỏ i t h ủ tục. Hãy v i ế t l ạ i t h ủ tục XemGiaBan, trước t i ê n n ó k i ể m t r a giá t r ị n h ậ p , nếu k h ô n g được cung cấp sẽ i n m ộ t t h ô n g b á o v ớ i người d ù n g v à trở về chương t r ì n h gọi. Định nghĩa t h a m số n h ậ p có giá t r ị mặc đ ị n h l à N U L L . Thực t h i đoạn m ã T-SQL sau: USE MyDB GO I F EXISTS (SELECT name FROM sysobjects VÍHERE name = ' X e m D o n G i a ' AND type = 1 p') DROP PROCEDURE XemDonGia GO CREATE PROCEDURE XemDonGia @ma_hang i n t = NULL AS I F @ma_nang I S NULL BEGIN PRINT ' Hay n h á p ma h a n g ' RETURN END ELSE BEGIN SELECT DON_GIA FROM MAT_HANG WHERE MA_HANG =
C h ư ơ n g 10: Tạo và quản lý Stored Procedure HI M PRINT ( ' T r u o c k h i t h ú c t h i t h u t ú c ' ) e x e c XemDonGia PRINT ( ' Sau k h i k é t t h ú c t h u t ú c ' ) GO K ế t xuất n h ư sau: Truoc k h i t h ú c t h i thu túc Hay n h á p ma h a n g Sau k h i k é t t h ú c t h u túc L ệ n h P R I N T t h ứ hai cho t h ấ y k h i R E T U R N được thực t h i trong thủ tục t h ì bó l ệ n h t i ế p tục ở l ệ n h PRINT. Đ ể d ù n g R E T U R N t r ả về m ộ t giá trị cho chương t r ì n h gọi, giá trị t r ả về p h ả i là m ộ t số n g u y ê n , nó có t h ể là h ằ n g số hoặc là biến. B ạ n phải khai báo b i ế n trong chương t r ì n h gọi để lưu giá trị t r ả về m à sau đó sẽ được dùng l ạ i trong chương t r ì n h gọi. Ví dụ tạo thủ tục k i ể m tra giá bán, sẽ t r ả về Ì n ế u giá b á n n h ỏ h ơ n 50, ngược l ạ i t r ả về 99: USE MyDB GO CREATE PROCEDURE K i e m T r a G i a @ma_hang i n t AS I F (SELECT DON_G IA FROM MÁT_HANG VỈHERE MA_HANG = @ma_hang) < 50 RETURN Ì ELSE RETURN 99 GO Thực t h i t h ủ tục n à y , khai báo m ộ t b i ế n đ ể n h ậ n giá t r ị t r ả về từ thủ tục là @giaban, cung cấp m ã h à n g là 1: DECLARE @ t r i _ t r a v e i n t EXECUTE @ t r i _ t r a v e = K i e m T r a G i a Ì I F ( @ t r ỉ _ t r a v e = 1) PRINT ' G i a b a n n h o h o n 50 1 GO
Mi® Sa 112 C h ư ơ n g 10: Tạo và quán lý Stored Procedure Với m ã h à n g l à Ì , giá b á n l à 19 n h ỏ h ơ n 50 n ê n t h ủ tục t r ả v ề 1. K ế t quả n h ư sau: G i a b a n n h o h o n 50 Sử dụng SELECT d ể t r ả v ề giá trị B ạ n có t h ể t r ả về giá t r ị b ằ n g p h á t b i ể u SELECT t r o n g t h ủ tục hoặc t r ả về k ế t quả được t h i ế t l ậ p t ừ truy v ấ n SELECT. Ví dụ sau t r ả về giá t r ị bằng p h á t biểu S E L E C T t r o n g t h ủ tục. USE MyDB GO CREATE PROCEDURE I n D i e n T h o a i @ma_kh i n t AS SELECT HO_KH, TEN_KH, D I E N _ T H O A I FROM KHACH_HANG V7HERE MA_KH = @ma_kh GO Gọi t h ủ tục với m ã k h á c h h à n g l à 777: e x e c I n D i e n T h o a i 777 K ế t quả n h ư sau: HO_KH TEN_KH DIENTHOAI NGUYÊN T H I MINH K H A I 8145662 (lrow(s) affected) Ví dụ sau t r ả về giá t r ị b i ế n b ằ n g p h á t biểu SELECT. G i á t r ị t r ả cho b i ế n k q bằng l ệ n h SELECT n h ư sau: USE MyDB GO I F EXISTS (SELECT name FROM sysobjects WHERE name = • K i e m T r a D T " AND type = *p')
C h ư ơ n g 10: Tạo và quản lý Stored Procedure 113 H DROP PROCEDURE K i e m T r a D T GO CREATE PROCEDURE K i e m T r a D T @ma_kh I N T AS DECLARE @kq v a r c h a r ( 5 0 ) I F (SELECT D I E N _ T H O A I FROM KHÁC H_HANG WHERE MA_KH = @ma_kh) I S NÓT NU Li Li SÉT @kq = 1 Da l ư u s o d i ê n t h o a i ' ELSE SÉT @kq = C h u a l ư u so d i ê n t h o a i 1 1 SELECT " k é t q u a " = @kq PRINT • T r a v e g i a t r i c h o b i ê n k q b a n g SELECT ' GO Gọi t h ủ tục với m ã k h á c h h à n g là 779: e x e c K i e m T r a D T 77 9 K ế t quả t r ả về là: ketqua Chua l ư u s o d i ê n t h o a i (lrow(s) affected) T r a v e g i a t r i c h o b i ê n k q b a n g SELECT 2.2. D ù n g E n t e r p r i s e M a n a g e r t ạ o s t o r e d p r o c e d u r e B ạ n đã biết cách tạo stored procedure bằng T-SQL. Đ ể tạo stored procedure bằng Enterprise Manager b ạ n v ẫ n p h ả i b i ế t cách v i ế t p h á t biếu T-SQL. B ạ n sẽ học p h ư ơ n g p h á p n à y bằng cách tạo l ạ i t h ủ tục them_dong theo các bước sau: 1. Trước h ế t xóa t h ủ tục them_dong. Trong Enterprise Manager m ở rộng CSDL M y D B . Chọn danh mục Stored Procedures, trong vùng cửa sổ b ê n p h ả i b ấ m p h ả i chuột lên t h ủ tục them_dong, chọn Delete từ t r ì n h đơn t ắ t (bạn cũng có t h ể đ ổ i t ê n hoặc sao c h é p stored procedure từ t r ì n h đơn
® 114 C h ư ơ n g 10: Tạo và quần lý Stored Procedure này). M ộ t hộp t h o ạ i Drop Objects x u ấ t h i ệ n n h ư h ì n h 10-1, b ấ m n ú t Drop A l l đ ể xóa t h ủ tục them_dong. 2. B ấ m p h ả i chuột l ê n danh mục Stored Procedures, chọn New Stored Procedure t ừ t r ì n h đơn t ắ t . Cửa sổ Stored Procedure Properties xuất h i ệ n n h ư h ì n h 10-2. 3. Trong hộp Text t r ê n t h ẻ General thay [OWNER].[PROCEDURE N A M E ] bằng t ê n t h ủ tục b ạ n muốn tạo, t r o n g t r ư ờ n g hợp n à y là them_dong. Sau đó n h ậ p v à o các p h á t b i ể u T-SQL cho t h ủ tục này. H ì n h 10-3 cho t h ấ y cửa sổ Stored Procedure Properties sau k h i đ ã t h ê m m ã T-SQL cho t h ủ tục them_dong. Drop Obịecl s The foHowinrj obịedsráibe dróppeđ'. B 9 B 'ị|. 0bìect Ị Ị,'. ••• 0wnei • • »Ị Tỳte.VỊi : thern_dong Hình 10-1 Hộp thoại Drop Objects m . GcnciãlỊ i .isMHsra l o Nanie. ; •'- ''. li ilỊỊÌ' . ũsate (tài: Hình 10-2 Cửa sổ Stored Procedure Propertỉes
C h ư ơ n g 10: Tạo và quản lý Stored Procedure 115 4. B ấ m n ú t Check Syntax đ ể SQL Server chỉ ra b ấ t k ỳ l ỗ i cú p h á p T-SQL n à o nếu có trong t h ủ tục. Sửa l ỗ i h ế t nếu có. Ngay k h i k h ô n g còn l ỗ i b ạ n sẽ t h ấ y t h ô n g điệp sau n h ư h ì n h 10-4. b ấ m OK. 5. B ấ m OK trong cửa sổ Stored Procedure Properties đ ể tạo t h ủ tục của b ạ n và t r ở về Enterprise Manager. B ấ m chọn danh mục Stored Procedures trong vùng cửa sổ b ê n trái để h i ể n t h ị t h ủ tục mới tạo trong vùng cửa sổ bên phải n h ư h ì n h 10-5. stored Procedure Properties - Hevv stored Procedure ãrne: Perrriịỉsiữhs. ỊCREATE PROCEDURE them_dong @batdau int \s " DECLARE @dem_vong inl @bat_dau int SÉT @bat_dau--@batdau1 ĩ SÉT @dem_vong =- oi HILE í@dem_vong' 51 líBEGIN _ ve ' Ị 22.5/15 í , VI ÊÊ 0K SanceỊ -
M @ ne C h ư ơ n g 10; Tạo vả quán lý Stored Procedure _j ConscteRool ^pdt getobjw*hprop 08/06/20«5:30:20 PM MKrt»c/t 5CẠ S»¥Ws srítem 06/06/2004S.»;20PM ifjdt ortproperOesbytí c= Cb NTB(WhdwsNT) Sysetm 06/06/20045:30:20 PM ịWdtjetoropertiMbvid_u .-; u Datat»ses S ysetm 0B0 getproperbeĩbyri ve5 /62 /0O45:30:20 PM {•>ở AR4M0 Syítem 06/06/20045r30:20 PM g9dt_geteroperti«byi£Í_vei_u Ễ & ũ AMSAPP Ao Sysetm 06/06/2004S3 ^3|dt taffvfcr90urcecontrd_u :02:OPM 6i ộ AR4SSYS dbo Sysetm 06/06/20»5:30:20 PM rercv«r>om*fx»c«or*roí SitìDemaPremeiA t ppO l dbo Sysetm 06/06/20045:X:20PH ^dt_tetp»opertybyiá *i> Ự OemoPeimei Afjp60 dbo Sysetm 06/06/20045:30:20 PM j£9 ct _s«fjr opert )*vrf _u tì DemoPetraeSyKero dbo Sysíem 08/06/20045:30:20 PM i£Sldt_v*iỏletorjn0arams SItìr*MS dbo Syston 08/06/20045:30:20 PM 2 Users dt_v«enabled Ị^dt_vatĩamp0O6 S ysetm 08/06/20045:30:20 PM J Roets S y setm 06/06/20045:30:20 PM G3 rx«u»s §d nhocheckedout S y sctm 08/06/20045M n, Uset Deíined D*a riỊr5d _whoehMkedout _o S y : 2:0PM rtem 08/06/20045:30:20 PM Ej LSd Defmod FurtU- ưỵĩrtxen T hoa i Sysetm 06/06/20045:30:20 m gjF
C h ư ơ n g 10: Tạo và quản lý Stored Procedure 117 M @ 7. B ấ m n ú t OK đ ể t h i ế t l ậ p quyền bạn chọn và trở về cửa sô Stored Procedure Properties. B ấ m OK đ ể k ế t thúc. B ạ n cũng có t h ể d ù n g Enterprise Manager đ ể sửa đ ổ i t h ủ tục, bằng cách b ấ m p h ả i chuột l ê n t h ủ tục muốn sửa, chọn Properties cửa sổ Stored Procedure Properties xuất h i ệ n , sửa đ ổ i , k i ể m t r a cú p h á p r ồ i b ấ m n ú t Apply sau b ấ m n ú t OK. Để quản lý quyền trên thủ tục, bấm phải chuột lên thủ tục bạn muôn cấp hoặc sửa đôi quyền, t r ỏ chuột đ ế n A l l Tasks r ồ i chọn Manage Permissions, cửa sổ n h ư h ì n h 10-6 xuất h i ệ n , thực h i ệ n sửa đ ổ i quyền n h ư mong muôn. N ế u b ạ n chọn Generate SQL Script b ạ n sẽ tạo t ậ p t i n script chứa định nghĩa của t h ủ tục b ạ n chọn đ ể d à n h tạo l ạ i t h ủ tục k h i cần. 2.3. Dùng Create stored procedure wizard để tạo stored procedure Cách t h ứ 3 đế tạo t h ủ tục là d ù n g Create Stored Procedure Wizard. Để dùng wizard b ạ n thực h i ệ n các bước sau: 1. Trong Enterprise Manager chọn Wizards từ t r ì n h đơn Tools đ ể h i ể n t h ị hộp t h o ạ i Select Wizard. M ở rộng danh mục Database, chọn Create Stored Procedure Wizard. 2. B ấ m OK đ ể h i ể n thị màn hình chào Create Stored Procedure Wizard. 3. B ấ m Next đ ể h i ể n t h ị m à n h ì n h Select Database. Chọn t ê n CSDL b ạ n muốn tạo stored procedure trong đó, ví dụ chọn MyDB. 4. B ấ m Next đ ể h i ể n t h ị m à n h ì n h Select Stored Procedures n h ư h ì n h 10-7. Ớ đây bạn có t h ể t h ấ y t ấ t cả các bảng của CSDL b ạ n đ ã chọn với 3 cột hộp k i ể m . Những cột n à y t h ể h i ệ n 3 k i ể u t h ủ tục bạn muốn tạo d ù n g wizard: stored procedure xóa, cập n h ậ t và t h ê m dữ liệu. Ví dụ chọn cột Insert cho bảng NHA_CC.
© 118 C h ư ơ n g 10: Tao và quán lý Stored Procedure Hình 20-7 Màn hình Select Stored Procedures 5. B ấ m Next đ ể h i ể n t h ị m à n h ì n h Completing The Create Stored Procedure Wizard n h ư h ì n h 10-8. M à n h ì n h n à y hiển t h ị t ê n và d i ễ n g i ả i của t h ủ tục b ạ n sẽ tạo k h i b ạ n k ế t thúc wizards. 6. Để đổi tên hoặc sửa dổi thủ tục, chọn thủ tục trong cửa sổ Completing The Create Stored Procedure W i z a r d r ồ i bám n ú t E d i t đ ể h i ể n t h ị cửa sổ E d i t Stored Procedure Properties n h ư h ì n h 10-9. Các cột được đ á n h dấu k i ể m sẽ được d ù n g trong t h ủ tục. Hình 10-8 Màn hỉnh Completing The Create Stored Procedure Wizard
C h ư ơ n g 10: Tạo và quán lý Stored Procedure 119 Edit stored Procedure Propertíes CotumnName • ì' ỊỮataXype 1-TÍ 1 ỉ ììSdKl - I MAN . CC rrt 4 ã) TEN_CTY nvatchai 100 DIẠC. HI nvarchai 255 sa DIENT . HOAI chai 10 a ; FAX cha 10 0 Ị ẸdtSQL.. ỊỊ 0K Ị Caneel j ị Ị Hình 10-9 Cửa sổ Edit Stored Procedure Properties 7. Đ ể đ ổ i t ê n t h ủ tục, n h ậ p t ê n mới trong trường Name, g i ả sử đ ổ i t ê n t h à n h Them_NHA_CC. 8. Đ ể sửa đ ổ i t h ủ tục b ấ m Edit SQL để h i ể n t h ị hộp thoại Edit Stored Procedure SQL n h ư h ì n h 10-10. Xóa bỏ t ấ t cả các b i ế n và t ê n cột là MA_NCC, b ấ m n ú t Parse đ ể k i ể m tra cú p h á p sau đó b ấ m OK để trở về m à n h ì n h Completing The Create Stored Procedure Wizard. 9. B ấ m F i n i s h đ ể tạo t h ủ tục nới Them_NHA_CC. Edit Sloređ Procedure SỌL I U Hình 10-10 Hộp thoại Edit Stored Procedure SQL
® 1 2 0 C h ư ơ n g 10: Tạo và quản lý Stored Procedure 3. Q U Ả N L Ý S T O R E D PROCEDURE Bây giờ b ạ n sẽ học cách d ù n g T-SQL đ ể thay đ ổ i , x ó a v à xem n ộ i dung của t h ủ tục. 3.1. P h á t b i ể u A L T E R PROCEDURE Đ ể thay đ ổ i t h ủ tục d ù n g T-SQL b ạ n thực h i ệ n n h ư sau: G i ả sử b ạ n thay đ ổ i t h ủ tục K i e m T r a G i a t r o n g CSDL M y D B , nội dung trước k h i thay đ ổ i của t h ủ tục là: CREATE PROCEDURE K ỉ e m T r a G i a @ma_hang i n t AS I F (SELECT DON_GIA FROM MAT_HANG VÍHERE MA_HANG = @ma_hang) < 50 RETURN Ì ELSE RETURN 99 GO Thay đ ổ i t h ủ tục t h à n h , lun ý chọn CSDL l à M y D B : USE MyDB GO ALTER PROCEDURE K i e m T r a G i a @ma_hang i n t AS I F (SELECT DON_GIA FROM MAT_HANG WHERE MA_HANG = c?ma_hang) < 70 RETURN Ì ELSE RETURN 99 GO *