Store procedure
- 1. Bi 1 STORED PROCEDURE M畛c ti棚u bi h畛c: - Kh叩i ni畛m Stored Procedure - 働u i畛m c畛a Stored Procedure - C叩c lo畉i Stored Procedure - C叩ch t畉o Stored Procedure I. Kh叩i ni畛m Stored Procedure - Trong nh畛ng bi h畛c tr動畛c 但y khi d湛ng Query Analyzer ch炭ng ta c坦 th畛 畉t t棚n v save c叩c nh坦m c但u l畛nh SQL vo m畛t file d動畛i d畉ng script 畛 c坦 th畛 s畛 d畛ng tr畛 l畉i sau ny. Tuy nhi棚n thay v狸 save vo text file ta c坦 th畛 save vo trong SQL Server d動畛i d畉ng Stored Procedure. - Stored Procedure l m畛t nh坦m c但u l畛nh Transac-SQL 達 動畛c complied (bi棚n d畛ch) v ch畛a trong SQL Server d動畛i m畛t t棚n no 坦 v 動畛c x畛 l箪 nh動 m畛t 董n v畛 (ch畛 kh担ng ph畉i nhi畛u c但u SQL ri棚ng l畉). 1. 働u i畛m Stored Procedure Stored Procedure c坦 m畛t s畛 動u i畛m ch鱈nh nh動 sau: - Performance: Khi th畛c thi m畛t c但u l畛nh SQL th狸 SQL Server ph畉i ki畛m tra permission xem user g畛i c但u l畛nh 坦 c坦 動畛c ph辿p th畛c hi畛n c但u l畛nh hay kh担ng 畛ng th畛i ki畛m tra c炭 ph叩p r畛i m畛i t畉o ra m畛t execute plan v th畛c thi. N畉u c坦 nhi畛u c但u l畛nh nh動 v畉y g畛i qua network c坦 th畛 lm gi畉m i t畛c 畛 lm vi畛c c畛a server. SQL s畉 lm vi畛c hi畛u qu畉 h董n n畉u d湛ng stored procedure v狸 ng動畛i g畛i ch畛 g畛i m畛t c但u l畛nh 董n v SQL Server ch畛 ki畛m tra m畛t l畉n sau 坦 t畉o ra m畛t execute plan v th畛c thi. N畉u stored procedure 動畛c g畛i nhi畛u l畉n th狸 execute plan c坦 th畛 動畛c s畛 d畛ng l畉i n棚n s畉 lm vi畛c nhanh h董n. Ngoi ra c炭 ph叩p c畛a c叩c c但u l畛nh SQL 達 動畛c SQL Sever ki畛m tra tr動畛c khi save n棚n n坦 kh担ng c畉n ki畛m l畉i khi th畛c thi. - Programming Framework: M畛t khi stored procedure 動畛c t畉o ra n坦 c坦 th畛 動畛c s畛 d畛ng l畉i. i畛u ny s畉 lm cho vi畛c b畉o tr狸 (maintainability) d畛 dng h董n do vi畛c t叩ch r畛i gi畛a business rules (t畛c l nh畛ng logic th畛 hi畛n b棚n trong stored procedure) v database. V鱈 d畛 n畉u c坦 m畛t s畛 thay 畛i no 坦 v畛 m畉t logic th狸 ta ch畛 vi畛c thay 畛i code b棚n trong stored procedure m th担i. Nh畛ng
- 2. 畛ng d畛ng d湛ng stored procedure ny c坦 th畛 s畉 kh担ng c畉n ph畉i thay 畛i m v畉n t動董ng th鱈ch v畛i business rule m畛i. C滴ng gi畛ng nh動 c叩c ng担n ng畛 l畉p tr狸nh kh叩c stored procedure cho ph辿p ta 動a vo c叩c input parameters (tham s畛) v tr畉 v畛 c叩c output parameters 畛ng th畛i n坦 c滴ng c坦 kh畉 nng g畛i c叩c stored procedure kh叩c. - Security: Gi畉 s畛 ch炭ng ta mu畛n gi畛i h畉n vi畛c truy xu畉t d畛 li畛u tr畛c ti畉p c畛a m畛t user no 坦 vo m畛t s畛 tables, ta c坦 th畛 vi畉t m畛t stored procedure 畛 truy xu畉t d畛 li畛u v ch畛 cho ph辿p user 坦 動畛c s畛 d畛ng stored procedure 達 vi畉t s畉n m th担i ch畛 kh担ng th畛 "畛ng" 畉n c叩c tables 坦 m畛t c叩ch tr畛c ti畉p. Ngoi ra stored procedure c坦 th畛 動畛c encrypt (m達 h坦a) 畛 tng c動畛ng t鱈nh b畉o m畉t. 2. C叩c lo畉i Stored Procedure Stored Procedure c坦 th畛 動畛c chia thnh 5 nh坦m nh動 sau: - System Stored Procedure: L nh畛ng stored procedure ch畛a trong Master database v th動畛ng b畉t 畉u b畉ng ti畉p 畉u ng畛 sp_. C叩c stored procedure ny thu畛c lo畉i built-in v ch畛 y畉u d湛ng trong vi畛c qu畉n l箪 database (administration) v security. V鱈 d畛 b畉n c坦 th畛 ki畛m tra t畉t c畉 c叩c processes ang 動畛c s畛 d畛ng b畛i user DomainNameAdministrators b畉n c坦 th畛 d湛ng sp_who @loginame='DomainNameAdministrators. C坦 hng trm system stored procedure trong SQL Server. B畉n c坦 th畛 xem chi ti畉t trong SQL Server Books Online. - Local Stored Procedure: 但y l lo畉i th動畛ng d湛ng nh畉t. Ch炭ng 動畛c ch畛a trong user database v th動畛ng 動畛c vi畉t 畛 th畛c hi畛n m畛t c担ng vi畛c no 坦. Th担ng th動畛ng ng動畛i ta n坦i 畉n stored procedure l n坦i 畉n lo畉i ny. Local stored procedure th動畛ng 動畛c vi畉t b畛i DBA ho畉c programmer. Ch炭ng ta s畉 bn v畛 c叩ch t畉o stored prcedure lo畉i ny trong ph畉n k畉 ti畉p. - Temporary Stored Procedure: L nh畛ng stored procedure t動董ng t畛 nh動 local stored procedure nh動ng ch畛 t畛n t畉i cho 畉n khi connection 達 t畉o ra ch炭ng b畛 坦ng l畉i ho畉c SQL Server shutdown. C叩c stored procedure ny 動畛c t畉o ra tr棚n TempDB c畛a SQL Server n棚n ch炭ng s畉 b畛 delete khi connection t畉o ra ch炭ng b畛 c畉t 畛t hay khi SQL Server down. Temporary stored procedure 動畛c chia lm 3 lo畉i : local (b畉t 畉u b畉ng #), global (b畉t 畉u b畉ng ##) v stored procedure 動畛c t畉o ra tr畛c ti畉p tr棚n TempDB. Lo畉i local ch畛 動畛c s畛 d畛ng b畛i connection 達 t畉o ra ch炭ng v b畛 x坦a khi disconnect, c嘆n lo畉i global
- 3. c坦 th畛 動畛c s畛 d畛ng b畛i b畉t k畛 connection no. Permission cho lo畉i global l dnh cho m畛i ng動畛i (public) v kh担ng th畛 thay 畛i. Lo畉i stored procedure 動畛c t畉o tr畛c ti畉p tr棚n TempDB kh叩c v畛i 2 lo畉i tr棚n 畛 ch畛 ta c坦 th畛 set permission, ch炭ng t畛n t畉i k畛 c畉 sau khi connection t畉o ra ch炭ng b畛 c畉t 畛t v ch畛 bi畉n m畉t khi SQL Server shut down. - Extended Stored Procedure: 但y l m畛t lo畉i stored procedure s畛 d畛ng m畛t ch動董ng tr狸nh ngo畉i vi (external program) v畛n 動畛c compiled thnh m畛t DLL 畛 m畛 r畛ng ch畛c nng ho畉t 畛ng c畛a SQL Server. Lo畉i ny th動畛ng b畉t 畉u b畉ng ti畉p 畉u ng畛 xp_ .V鱈 d畛, xp_sendmail d湛ng 畛 g畛i mail cho m畛t ng動畛i no 坦 hay xp_cmdshell d湛ng 畛 ch畉y m畛t DOS command... V鱈 d畛 xp_cmdshell 'dir c:' . Nhi畛u lo畉i extend stored procedure 動畛c xem nh動 system stored procedure v ng動畛c l畉i. - Remote Stored Procedure: Nh畛ng stored procedure g畛i stored procedure 畛 server kh叩c. II. T畉o Stored Procedure - T棚n v nh畛ng th担ng tin v畛 Stored Procedure khi 動畛c t畉o ra s畉 ch畛a trong SysObjects table c嘆n ph畉n text c畛a n坦 ch畛a trong SysComments table. - V狸 Stored Procedure c滴ng 動畛c xem nh動 m畛t object n棚n ta c滴ng c坦 th畛 d湛ng c叩c l畛nh nh動 CREATE, ALTER, DROP 畛 t畉o m畛i, thay 畛i hay x坦a b畛 m畛t stored procedure. - V鱈 d畛 v畛 Stored Procedure: 畛 t畉o m畛t stored procedure ch炭ng ta c坦 th畛 d湛ng Enterprise Manager click l棚n tr棚n Stored Procedure -> New Stored Procedure V鱈 d畛 1: T畉o Stored Procedure 畛 c畉p nh畉t t棚n 畛 ti m畛i v畛i MST c滴 kh担ng 畛i. N畉u kh担ng t狸m th畉y tr畉 v畛 0, ng動畛c l畉i c畉p nh畉t v tr畉 v畛 1
- 4. CREATE PROC capnhatdetai (@msdt char(6), @tendetaimoi varchar(30)) AS If Exists (select msdt from DETAI where msdt=@msdt) Begin Update DETAI Set tendt=@tendetaimoi Where msdt=@msdt Return 1 -- da cap nhat End Else Return 0 --khong co de tai nao co MSDT t動董ng ung V鱈 d畛 2: T畉o Stored Procedure v 動a vo TENSV n畉u kh担ng vi ph畉m rng bu畛c v畛 kho叩 th狸 xo叩. Ng動畛c l畉i tr畉 v畛 0. CREATE PROC xoasinhvien(@tensv varchar(30)) AS -- n畉u c坦 t棚n sinh vi棚n c畉n xo叩 If exists (select mssv from SINHVIEN where tensv = @tensv) Begin -- n畉u c坦 d畛 li畛u t動董ng 畛ng trong table THUCHIEN thi khong the xoa If exists (select msdt from SINHVIEN t1, THUCHIEN t2 where t1.mssv=t2.mssv and t1.tensv = @tensv) Begin Print khong the xoa du lieu trong table SINHVIEN vi rang buoc khoa ngoai voi table THUCHIEN Return End Else -- Xoa du lieu khoi table SINHVIEN vi khong con vi pham rang
- 5. buoc khoa ngoai Delete from SINHVIEN where tensv = @tensv Print Da xoa sinh vien co ten: +@tensv End Else Begin Print khong co sinh vien co ten: +@tensv Return 0 End