CHNG 5:

LP TRNH, TH TC LU TR, HM V TRIGGER

Mc ch
Nm vng cc khi nim l (batch) v x l theo l Vit cc cu lnh SQL th hin logic ca ng dng nh ngha v gn gi tr cho cc bin Nm vng v dng c cc lnh iu khin cu trc lp trnh Nm cch dng bin con tr Vit c cc th tc c bn p ng yu cu qt csdl Vit c v S dng c hm SQL trong truy vn To c cc trigger c bn

Lp trnh trn SQL server

Cc pht biu SQL c ghi thnh mt tp tin gi l kch bn (script). Mi script c to theo mt mc ch nht nh.

Vd: USE qlysv DECLARE @tt INT CREATE TABLE sv (stt INT, masv NVARCHAR(10) NOT NULL CONSTRAINT pk_sinhvien PRIMARY KEY, hoten NVARCHAR(50) NOT NULL , ngaysinh SMALLDATETIME NULL , gioitinh BIT NULL , noisinh NVARCHAR(100) NULL , malop NVARCHAR(10) NULL ) SELECT @stt = @@IDENTITY INSERT INTO sv VALUES(@stt,SV01CDT2K8, 'Trn Thu Thu', '11/10/1987',0,'Thi bnh','CDT001K008') SELECT * FROM SV

Gii thiu v x l theo l (SQL Batch Processing)

Cc lnh SQL ring r c nhm li thnh l (batch)

L (Batch)

c bin dch thnh mt k hoch thc thi

nh ngha
Qu trnh trong mt tp lnh c x l cng lc c gi l

V d v mt l (batch)
Use QlSach Select * from tacgia Update tacgia set phone= 098890 4566 where tentg = Trung' Go

Lnh bo hiu kt thc l

Mc ch: Hay dng phn tch nhm thc hin c lp

VD: Xt kch bn CREATE DaTaBASE qlbanhang USE qlbanhang CREATE TABLE ktra ( A INT, B INT ) SELECT * FROM ktra --S b bo li

Cn sa li
CREATE DaTaBASE qlbanhang GO USE qlbanhang CREATE TABLE ktra ( A INT, B INT ) GO SELECT * FROM ktra GO

Ch thch trong mt l x l
Cc chui k t trong m lnh chng trnh (cn c gi l ch thch) khng c x l bi trnh bin dch. Dng gii thch cho m lnh hay v hiu ha tm thi cc thnh phn cu lnh T-SQL ang x l Gip vic bo tr m lnh d dng hn. Ch thch thng c s dng ghi li tn chng trnh, tn tc gi v ngy thng thc hin thay i m lnh. Ch thch c th c dng m t cc php tnh ton phc tp hay gii thch v phng php lp trnh.

Cc hnh thc ch thch

SQL Server h tr hai hnh thc ch thch:
1) --(hai ghch ngang)
V d: USE Qlsach GO -- y l ch thch.

2) /* ... */ (cp du ghch cho v du sao)

V d: SELECT * FROM nhanvien /*y l ch thch*/

Ch thch nhiu dng

Ch thch nhiu dng /* */ khng th vt qu mt l. Mt ch thch hon chnh phi nm trong mt l x l. V d, trong cng c Query Analyzer, lnh GO bo hiu kt thc l. Khi gp lnh GO trn dng lnh n s gi tt c cc m lnh sau t kha GO cui cng ln my ch SQL trong mt l x l. Nu lnh GO xut hin trn mt dng gia /* v */ th Query Analyzer s gi i mt on ch thch c cc k t nh du sai trong mi l v s gy ra li c php.

Ch :
i vi cc lnh CREATE nh l: CREATE DEFAULT, CREATE PROCEDURE, CREATE RULE, CREATE TRIGGER, CREATE VIEW khng c php kt hp vi cc lnh khc trong cng mt l.

Bin cc b

Khai bo bien
DECLARE @Tn_bin Kiu_d_liu [, ...] Kiu d liu text, ntext hoc image khng c chp nhn khi khai bao bien V d: khai bo cc bin lu tr gi tr tng s lng t hng, h tn nh cung cp, ngy xut hng. S dng lnh DECLARE nh sau: DECLARE @Tongsldat INT, @Hotenncc CHAR(50) DECLARE @Ngayxh DATETIME

Gn gi tr cho bin
T kha SET hay SELECT c dng gn gi tr cho bin. C php: SET @<tn bin cc b> = <gi tr> Hoc l: SELECT @<Tn bin cc b> = <gi tr> Ch : Phm vi hot ng ca bin ch nm trong mt th tc hoc mt l c cha lnh khai bo bin

V du:
gn gi tr l ngy 25/03/2002 vo bin ngy xut hng ta s dng lnh SET nh sau: DECLARE @Ngayxh DATETIME SET @Ngayxh='2002-03-25' Ch :i vi kiu d liu dng ngy trong Microsoft SQL Server thng s dng theo nh dng yyyy-mm-dd gn gi tr vo bin hoc vo trong c s d liu.

V du:
tnh ra s lng t hng cao nht ca mt hng u DVD Hitachi 1 a c m vt t l DD01. S dng lnh SELECT nh sau: DECLARE @MaxSldat INT SELECT @MaxSldat=MAX(SLDAT) FROM CTDONDH WHERE MAVTU="DD01"

Xem gi tr hin hnh ca bin

PRINT @Tn_bin | Biu_thc_chui tnh ng thi gi tr s lng t hng thp nht v cao nht, hin th kt qu ra mn hnh. Ta s dng lnh SELECT v PRINT : DECLARE @MinSldat INT, @MaxSldat INT SELECT@MinSldat=MIN(SLDAT), @MaXSldat=MAX(SLDAT) FROM CTDONDH PRINT "S lng thp nht l : " PRINT @MinSldat PRINT "S lng cao nht l : " + CONVERT(VARCHAR(10), @MaxSldat)

Cc loi bin
SQL Server h tr hai loi bin sau trong T-SQL:

Cc bin ton cc
Bin ton cc trong SQL Server bt u bng 2 k t @. Ta c th truy xut gi tr ca cc bin ny bng truy vn SELECT n gin

Danh sch cc bin ton cc

Cc bin @@CONNECTIONS @@CPU_BUSY ngha S cc kt ni n my ch t ln khi ng cui. S milliseconds (mt phn ngn giy) h thng x l t khi SQL Server c khi ng S bn ghi trong cursor m gn nht. Gi tr hin ti ca tham s trong lnh SET DATEFIRSTquyt nh ngy u tin ca tun. M li ca li xy ra gn nht 0 nu trng thi ln truy xut cui thnh cng. -1 nu c li

@@CURSOR_ROWS
@@DATEFIRST @@ERROR @@FETCH_STATUS

Danh sch cc bin ton cc(tip)

Cc bin
@@IDENTITY @@LANGUAGE @@MAX_CONNECTIONS @@ROWCOUNT @@SERVERNAME @@SERVICENAME @@TIMETICKS @@TRANSCOUNT @@VERSION

ngha
Gi tr identity gn nht c sinh ra Tn ca ngn ng ang c s dng. S kt ni ti a c th. S bn ghi b tc ng bi cu lnh SQL gn nht. Tn ca my ch Tn ca dch v SQL trn my ch S milliseconds trong mt tick trn my ch S giao dch ang hot ng trn kt ni hin ti Thng tin v phin bn ca SQL Server

Cc lnh iu khin

Cc lnh iu khin(tip..)

BEGIN..END
BEGIN...END : Mt tp lnh SQL c thc thi s c t trong BEGIN..END. C php: BEGIN <lnh> | <on lnh> END

IF..ELSE
IF...ELSE: Chng ta c th thc thi cc tp lnh

SQL khc nhau da vo cc iu kin khc nhau.

C php: IF <iu kin> < lnh sql1> | <tp lnh1> [ ELSE < lnh sql2>|< tp lnh2> ]
IF statement

TRUE

FALSE

V d v IF

IF c kt hp t kha EXISTS
kim tra s tn ti ca cc dng d liu bn trong bng IF EXISTS (Cu_lnh_SELECT) Cu_lnh1 | Khi_lnh1 [ ELSE Cu_lnh2 | Khi_lnh2 ]

Cu trc WHILE
WHILE: C th thc thi mt lnh SQL hay mt tp lnh da vo iu kin no . Cc cu lnh c thc thi nhiu ln khi no iu kin vn cn ng. C php: WHILE <iu kin> BEGIN Cc_lnh_lp END

BREAK v CONTINUE
Chng ta c th dng t kha CONTINUE v BREAK trong vng lp while iu khin phn thc thi ca cc cu lnh.
USE pubs GO WHILE (SELECT AVG(price) FROM titles) < $30 BEGIN UPDATE titles SET price = price * 2 SELECT MAX(price) FROM titles IF (SELECT MAX(price) FROM titles) > $50 BREAK ELSE CONTINUE END PRINT 'Too much for the market to bear'

S y :
WHILE Biu_thc_logic BEGIN Cc_lnh_nhm_lp1 [ IF Biu_thc_lp_tip CONTINUE ] [ IF Biu_thc_thot BREAK ] Cc_lnh_nhm_lp2 END Cc_lnh_khc

T kha GOTO
GOTO: C th thay i dng thc thi ca chng trnh n mt im (cn gi l nhn). Cc lnh sau t kha GOTO s c b qua v tin trnh thc thi tip tc v tr nhn ch ra trong mnh GOTO. C php: GOTO <nhn>

RETURN
RETURN: Ta c th dng RETURN bt c lc no thot khi mt on lnh hay mt th tc. Cc lnh sau t kha RETURN s khng c thc thi.
C php: RETURN [s nguyn]

Con tr
Mt con tr l mt i tng csdl, c s dng
thao tc vi tng hng d liu Vi con tr ta c th: Cho php nh v cc hng ch nh ca tp kt qu. Nhn v mt hng n hoc tp hp cc hng t v tr hin ti ca tp kt qu. H tr sa i d liu ca hng v tr hin ti trong tp kt qu. H tr quan st i vi cc thay i c to ra bi cc ngi dng khc trn cc d liu ca tp kt qu.

To con tr
Lnh DECLARE dng to mt con tr. Lnh ny cha cc lnh SELECT bao gm cc bn ghi t
bng. C php l:

DECLARE <Tn con tr> CURSOR [LOCAL | GLOBAL] [FORWARD ONLY | SCROLL] [STATIC | KEYSET | DYNAMIC ] [READ_ONLY | SCROLL_LOCKS] FOR <Lnh SELECT> [FOR UPDATE [OF <Tn ct> [,.N]]]

Cc bc s dng con tr
M con tr
OPEN <Cursor_name>

Nhn v cc bn ghi
FETCH <Cursor_name>

ng con tr
CLOSE <Cursor_name>

Xo cc tham chiu ti con tr

DEALLOCATE <Cursor_name>

Truy xut v duyt con tr

FETCH [NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | RELATIVE n] FROM Tn_cursor [INTO Danh_sch_bin] FETCH FIRST: Truy xut hng u tin. FETCH NEXT: Truy xut hng tip theo FETCH PRIOR: Truy xut hng trc hng truy xut trc . FETCH LAST: Truy xut hng cui cng. FETCH ABSOLUTE n: Nu n l mt s nguyn dng, truy
xut hng n trong con tr. Nu n l mt s nguyn m, hng n trc hng cui cng trong con tr c truy xut. Nu n bng 0, khng hng no c truy xut.

Truy xut v duyt con tr

FETCH RELATIVE n: Truy xut n hng t hng truy xut trc
, nu n l s dng. Nu n l s m, n hng trc hng truy xut trc c truy xut. Nu n bng 0, hng hin ti c nhn v.

Cc bin ton cc ca lnh FETCH

@@FETCH _STATUS: Bin ny tr v mt s nguyn biu
din kt qu ca lnh truy xut cui cng ca con tr. @@CURSOR_ROWS: Bin ny tr v tng s hng hin ti trong con tr ang m.

V d to con tr

V d
cp nht gi tr d liu cho ct TGNHAP (tr gi nhp) trong bng PNHAP bng cch duyt qua tng phiu nhp, tnh ra tr gi nhp ca tng phiu cn c vo s lng nhp v n gi nhp ca tng vt t trong bng CTPNHAP, sau cng cp nht vo ct TGNHAP

DECLARE @sSopn CHAR(4), @nTongtg MONEY DECLARE cur_Pnhap CURSOR FORWARD_ONLY FOR SELECT SOPN FROM PNHAP OPEN cur_Pnhap WHILE 0=0 BEGIN FETCH NEXT FROM cur_Pnhap INTO @sSopn IF @@FETCH_STATUS<>0 BREAK SELECT @nTongtg = SUM(SLNHAP*DGNHAP)

FROM Ctnhap WHERE Sopn=@sSopn PRINT ang cp nht phiu nhp:+@sSopn+ UPDATE Pnhap SET Tgnhap = @nTongtg WHERE CURRENT OF cur_Pnhap END CLOSE cur_Pnhap DEALLOCATE cur_Pnhap

Tng kt
Con tr c to bng lnh DECLARE. u tin con tr c
khai bo v to ra trong b nh. Sau n mi c m. Lnh OPEN m con tr. Vic nhn v cc bn ghi t mt con tr c gi l fetching. Mt ngi dng ch c th nhn v mt bn ghi ti mt thi im. Lnh FETCH c s dng c cc bn ghi t con tr. Ngm nh, mt con tr l forward only. N c th truy xut tun t cc bn ghi t bn ghi u tin n bn ghi cui cng.

Th tc lu tr

Mc tiu
nh ngha cc th tc lu tr. Gii thch qu trnh to lp, sa v thc thi cc th tc lu tr
do ngi dng nh ngha. S dng cc tham s v cc bin trong th tc lu tr. Thc hin ci t th tc trn v d Chn cc tu chn bin dch li ph hp. Tm hiu bo li trong th tc lu tr.

Th tc lu tr
Tp hp bin dch cc cu lnh T-SQL c lu tr vi mt tn xc nh S dng thc hin cc nhim v qun tr, hoc p dng cc lut giao dch phc tp C hai loi th tc lu tr: Th tc lu h thng cp n phng php qun tr d liu v cp nht thng tin vo cc bng (thng bt u bng sp_). Th tc lu do ngi dng nh ngha.

Th tc lu tr < tip tc>

LI ch ca th tc
Tng tc thc hin : Cc th tc c ti u ha ln u tin khi chng bin dch ->cho php thc thi vi chi ph it hn so vi T-SQL thng thng. Tc truy nhp d liu nhanh hn: SQl khng phi la chn cch tt nht x l cc lnh SQL v truy sut csdl mi khi chng c bin dch Modular programming:Mt th tc c th phn thnh cc th tc nh hn, cc th tc ny c th c dng chung gia cc th tc khc->gim thi gian thit k v thc thi cc th tc ng thi cng d qun l v g ri. S nht qun. Ci thin s bo mt: Nng cao an ton bo mt. C th ch ra quyn thc thi cho cc th tc v vy n thc hin ng tc v ngi dng.

Cc danh mc ca th tc lu tr h thng
SQL Mail SQL Server Query Agent Catalog Extended

Distributed Query

System stored procedures

Replication

System Cursor Security

V d v h thng th tc lu tr
sp_databases sp_server_info sp_stored_procedures sp_tables sp_start_job sp_stop_job sp_password

System stored procedures

sp_configure sp_help sp_helptext

nh ngha th tc lu tr bng EM: Bc 1:

Bc 2:

To th tc lu tr bng T-SQL

To th tc lu tr trong csdl hin thI bng C php: CREATE PROC[EDURE] <tn th tc> [(<DSch tham s>) ] [WITH RECOMPILE|ENCRYPTION|RECOMPILE, ENCRYPTION] AS [DECLARE <bin cc b> <Cc cu lnh ca th tc>

Cc th tc lu tr c quyn truy cp ti tt c cc i tng khi th tc c gi. 2100 tham s c th c s dng trong mt th tc lu tr. Tham so bt u bi @, cn ch ra kiu dliu ca tham s C th to lp nhiu bin cc b trong th tc tu kh nng cung cp ca b nh. Dung lng ti a ca th tc lu tr l 128 MB.

V du: Cho csdl qlsv

Yeu cau:
Gi s ta cn thc hin mt chui cc thao tc trn c s d liu . 1. B sung thm mn hc c s d liu c m CST005 v s n v hc trnh l 5 vo bng MONHOC 2. Ln danh sch nhp im thi mn c s d liu cho cc sinh vin hc lp c m CDT002K009 (b sung thm vo bng DIEMTHI cc bn ghi vi ct MAMONHOC nhn gi tr CST005, ct MASV nhn gi tr ln lt l m cc sinh vin hc lp c m CDT002K009 v cc ct im l NULL).

CREATE PROC sp_LenDanhSachDiem( @mamh NVARCHAR(10), @tenmh NVARCHAR(50), @sodvht SMALLINT, @malop NVARCHAR(10)) AS BEGIN INSERT INTO monhoc VALUES(@mamh,@tenmh,@sodvht) INSERT INTO diemthi(mamh,masv) SELECT @mamh,masv FROM sinhvien WHERE malop=@malop END Khi th tc trn c to ra, thc hin c hai yu cu trn qua li gi th tc: sp_LenDanhSachDiem CST005','C s d liu',5,'CDT002K009'

vd:

Tnh mt hng no c s lng bn cao nht trong thng 01/2002.

CREATE PROC MaxSLhang_200201 AS DECLARE @sTenhang VARCHAR(100), @smahang char(4), @nMaxSL INT SELECT @smahang=a.mahang,@sTenhang=tenhang,@nMaxSL =Sum(b.soluong) FROM mathang a INNER JOIN chitietdathang b ON a.Mahang=b.Mahang JOIN dondathang c ON b.SOhd=c.SOhd WHERE CONVERT(CHAR(7),ngaydathang,21)="200201 GROUP BY a.mahang, tenhang

HAVING sum(soluong)>=ALL (SELECT sum(soluong) FROM (FROM mathang a INNER JOIN chitietdathang b ON a.Mahang=b.Mahang JOIN dondathang c ON b.SOhd=c.SOhd WHERE CONVERT(CHAR(7),ngaydathang,21)="200201 GROUP BY a.mahang, tenhang ) PRINT @sTenhang + " c doanh s bn cao nht," PRINT VI s lng: " + CAST(@nMaxSL AS CHAR(10)) GO

Thc thi cc th tc ngi dng

Li gi th tc c dng: <tn_th_tc> [<danh_sch_cc_i_s>] S lng cc i s v th t ca chng phi ph hp vi s lng v th t ca cc tham s hnh thc. Trng hp li gi th tc c thc hin bn trong mt th tc khc, bn trong mt trigger hay kt hp vi cc cu lnh SQL khc, ta s dng c php nh sau: EXEC[UTE] <tn_th_tc> [<danh_sch_cc_i_s>] vd: EXECUTE MaxSLhang_200201 Kt qu tr v : S lng 10 Th t ca cc i s c truyn cho th tc c th khng cn phi tun theo th t ca cc tham s nh khi nh ngha th tc nu tt c cc i s c vit di dng: @<tn_tham_s> = <gi_tr>

VD s dng bin trong th tc:

CREATE PROC sp_Vidu(@malop1 NVARCHAR(10), @malop2 NVARCHAR(10)) AS DECLARE @tenlop1 NVARCHAR(30) DECLARE @namnhaphoc1 INT DECLARE @tenlop2 NVARCHAR(30) DECLARE @namnhaphoc2 INT SELECT @tenlop1=tenlop,@namnhaphoc1=namnhaphoc FROM lop WHERE malop=@malop1 SELECT @tenlop2=tenlop, @namnhaphoc2=namnhaphoc FROM lop WHERE malop=@malop2 PRINT @tenlop1+' nhap hoc nam '+str(@namnhaphoc1) print @tenlop2+' nhap hoc nam '+str(@namnhaphoc2) IF @namnhaphoc1=@namnhaphoc2 PRINT 'Hai lp nhp hc cng nm' ELSE PRINT 'Hai lp nhp hc khc nm'

Gi tr tr v ca tham s trong th tc
trng hp cn gi li gi tr ca i s sau khi kt thc th tc, ta khai bo tham s ca th tc theo c php: @tn_tham_s kiu_d_liu OUTPUT Hoc: @tn_tham_s kiu_d_liu OUT Trong li gi th tc, sau i s c truyn cho th tc, ta cng phi ch nh thm t kho OUTPUT (hoc OUT) CREATE PROCEDURE sp_Conghaiso( @a INT, @b INT, @c INT OUTPUT) AS SELECT @c=@a+@b

Thc hin li gi th tc trong mt tp cc cu lnh nh sau: DECLARE @tong INT SELECT @tong=0 EXECUTE sp_Conghaiso 100,200,@tong OUTPUT SELECT @tong => cu lnh SELECT @tong s cho kt qu l: 300

Tham s vI gi tr mc nh:
Tham s vi gi tr mc nh c khai bo theo c php nh sau: @<tn_tham_s> <kiu_d_liu> = <gi_tr_mc_nh> CREATE PROC sp_TestDefault( AS BEGIN

@tenlop NVARCHAR(30)=NULL,
@noisinh NVARCHAR(100)='Hu')

IF @tenlop IS NULL SELECT hodem,ten FROM sinhvien INNER JOIN lop ON sinhvien.malop=lop.malop WHERE noisinh=@noisinh ELSE SELECT hodem,ten FROM sinhvien INNER JOIN lop ON sinhvien.malop=lop.malop WHERE noisinh=@noisinh AND tenlop=@tenlop END

Cho bit h tn ca cc sinh vin sinh ti Hu: sp_testdefault Cho bit h tn ca cc sinh vin lp Tin K24 sinh ti Hu: sp_testdefault @tenlop='Tin K24' Cho bit h tn ca cc sinh vin sinh ti Ngh An: sp_testDefault @noisinh=N'Ngh An' Cho bit h tn ca cc sinh vin lp Tin K26 sinh ti Nng: sp_testdefault @tenlop='Tin K26',@noisinh=' Nng'

Bin dch li cc th tc lu tr
Cc th tc lu tr c bin dch li phn nh s thay i ti cc ch s. C ba cch bin dch li cc th tc: S dng th tc h thng sp_recompile system Ch r WITH RECOMPILE vi lnh CREATE PROCEDURE Ch r WITH RECOMPILE vi lnh EXECUTE

Sa cc th tc lu tr
Cu lnh ALTER PROCEDURE c s dng sa cha mt th tc lu tr C php ging nh lnh CREATE PROCEDURE S thay i ny vn gi li cc quyn ngi dng Cp: ALTER PROCEDURE tn_th_tc [(danh_sch_tham_s)] [WITH RECOMPILE|ENCRYPTION| RECOMPILE,ENCRYPTION] AS <Cc_cu_lnh_Ca_th_tc>

Cc thng bo li
Tr v cc m hoc lnh RAISERROR c th c dng a ra cc li ca ngi dng Tr v m trong th tc lu tr l cc gi tr nguyn Lnh RAISERROR statement ghi cc li v gn cc cp nghim trng ca li

Xo th tc
S dng cu lnh DROP PROCEDURE vi c php nh sau: DROP PROCEDURE <tn_th_tc >

Tm tt
Mt th tc lu tr l mt nhm cc cu lnh SQL c bin
dch li. Ngi pht trin CSDL hoc ngi qun tr h thng vit th tc chy cc nhim v qun tr thng thng, hoc ng dng cc lut giao dch phc tp. Th tc lu tr cha cc thao tc hoc cc cu lnh truy vn d liu. Cc th tc lu tr tng tc thc thi ca truy vn, h tr truy cp d liu nhanh, h tr vic lp trnh theo m un, duy tr tnh nht qun, v tng tnh bo mt.

Tm tt <tip theo>
C hai kiu th tc lu tr:
Cc th tc lu tr yu cu cc c ch i vi CSDL qun tr, v cp nht cc bng. Cc th tc ngi dng nh ngha. Cu lnh CREATE PROCEDURE c s dng to lp mt th tc lu tr ngi dng dnh ngha. Cu lnh EXECUTE c s dng chy th tc lu tr. Cc tham s c th c s dng truyn cc gi tr vo v ra t th tc lu tr.

Tm tt <tip theo>
C ba cch bin dch li cc th tc lu tr:
S dng th tc h thng sp_recompile Ch r WITH RECOMPILE vi lnh CREATE PROCEDURE Ch r WITH RECOMPILE vi ln EXECUTE Cu lnh ALTER PROCEDURE c s dng sa cha mt th tc lu tr. Tr v cc m hoc lnh RAISERROR c s dng a ra cc li ca ngi s dng.

Hm
Hm l i tng c s d liu tng t nh th tc. im khc bit gia hm v th tc: Hm tr v mt gi tr thng qua tn hm cn th tc th khng. C th s dng hm nh l mt thnh phn ca mt biu thc (chng hn, trong dsch chn ca lnh SELECT). Co hm do HQT CSDL cung cp sn Ngi s dng c th nh ngha cc hm nhm phc v cho mc ch ring ca mnh

Hm h thng

Ba loi hm

Cc hm thao tc vi tp bn ghi c th c dng thay cho tn cc bng trong SQL.

Cc hm tp hp tnh ton cho ra kt qu l mt gi tr n nht (v d tnh tng hay trung bnh). Cc hm v hng thao tc trn mt gi tr v tr v mt gi tr. Cc hm ny c th c dng trong cc biu thc.

Cc hm chuyn i
Hm chuyn i c dng chuyn 1 gi tr t mt kiu d liu sang kiu d liu khc. Ngoi ra n cn c dng nh dng ngy thng. SQL Server cung cp cho ta hm chuyn i duy nht l CONVERT(). C php: CONVERT(datatype[(length)], expression [,style])
V d: SELECT EMP ID: + CONVERT (CHAR(4), EMPLOYEEID FROM EMPLOYEES

Date Parts
DatePart Hour Minute Second Ss Millisecond Day of year Day T vit tt hh Mi
0-59 Ms Dy Dd 0-999 1-366 1-31

Gi tr 0-23 0-59

Date Parts (tip)

Datepart
Week Weekday Month Quarter Year

T vit tt
wk dw mm qq yy

Gi tr
1-53 1-7 1-12 1-4 1753-9999

Cc hm ngy thng v s hc
Cc hm ngy thng GETDATE() DATEADD(datepart,number,date) DATEDIFF(datepart,date1,date2) DATENAME(datepart,date) DATEPART(datepart,date)

Cc hm s hc
ABS(num_expr) CEILING(num_expr) FLOOR(num_expr) POWER(num_expr,y) ROUND(num_expr,length) Sign(num_expr) Sqrt(float_expr)

Cc hm h thng
Hm
DB_ID([database_name]) DB_NAME([database_id]) HOST_ID() HOST_NAME() ISNULL(expr,value) OBJECT_ID(obj_name) OBJECT_NAME(object_id) SUSER_SID([login_name]) SUSER_ID([login_name]) SUSER_SNAME([server_user_id]) SUSER_NAME([server_user_id]) USER_ID([user_name]) USER_NAME([user_id])

Cc hm tp hp
Hm Sum(col_name) Avg(col_name) COUNT(*) Max(col_name) Min(col_name) Gi tr tr v Tr v gi tr tng. Tr v gi tr trung bnh. Hm m cc bn ghi trong bng tha mn iu kin Tr v gi tr ln nht trong mt tp gi tr. Tr v gi tr nh nht trong mt tp hp.

nh ngha v s dng hm
CREATE FUNCTION tn_hm ([danh_sch_tham_s]) RETURNS (kiu_tr_v_ca_hm) AS BEGIN cc_cu_lnh_ca_hm END

VD: nh ngha hm tnh ngy trong tun (th) ca mt gi tr kiu ngy

CREATE FUNCTION thu(@ngay DATETIME) RETURNS NVARCHAR(10) AS BEGIN DECLARE @st NVARCHAR(10) SELECT @st=CASE DATEPART(DW,@ngay) WHEN 1 THEN 'Chu nht' WHEN 2 THEN 'Th hai' WHEN 3 THEN 'Th ba' WHEN 4 THEN 'Th t' WHEN 5 THEN 'Th nm' WHEN 6 THEN 'Th su' ELSE 'Th by' END RETURN (@st) /* Tr tr v ca hm */ END

S dng hm
s dng nh hm do hqt csdl cung cp: SELECT masv,hodem,ten,dbo.thu(ngaysinh),ngay sinh FROM sinhvien WHERE malop=C24102

Hm vi gi tr tr v l d liu kiu bng

CREATE FUNCTION tn_hm ([danh_sch_tham_s]) RETURNS TABLE AS RETURN (cu_lnh_select)

Cc qui tc:
Kiu tr v ca hm c ch nh bi mnh RETURNS TABLE. Trong phn thn ca hm ch c duy nht mt cu lnh RETURN xc nh gi tr tr v ca hm thng qua duy nht mt cu lnh SELECT (khng s dng bt k cu lnh no khc trong phn thn ca hm).

VD: Dnh ngha hm func_XemSV

CREATE FUNCTION func_XemSV(@khoa SMALLINT) RETURNS TABLE AS RETURN(SELECT masv,hodem,ten,ngaysinh FROM sinhvien INNER JOIN lop ON sinhvien.malop=lop.malop WHERE khoa=@khoa)

Dng hm nh ngha:
bit danh sch cc sinh vin kho 25, ta s dng cu lnh nh sau: SELECT * FROM dbo.func_XemSV(25) Cn cu lnh di y cho ta bit c danh sch sinh vin kho 26 SELECT * FROM dbo.func_XemSV(26)

Khi cn phi s dng nhiu cu lnh trong phn thn hm, c php ngha hm:

CREATE FUNCTION <tn_hm>([<danh_sch_tham_s>]) RETURNS @<bin_bng> TABLE <nh_ngha_bng> AS BEGIN <cc_cu_lnh_trong_thn_hm> RETURN END

Lu
Cu trc bng tr v bi hm c xc nh da vo nh ngha ca bng trong mnh RETURNS. Bin @<bin_bng> trong mnh RETURNS c phm vi s dng trong hm v c s dng nh mt tn bng. Cu lnh RETURN trong thn hm khng ch nh gi tr tr v. Gi tr tr v ca hm chnh l cc dng d liu trong bng c tn l @<binbng> c nh ngha trong mnh RETURNS

V d di y minh ho cch s dng dng hm ny trong SQL

CREATE FUNCTION Func_Tongsv(@khoa SMALLINT) RETURNS @bangthongke TABLE ( makhoa NVARCHAR(5), tenkhoa NVARCHAR(50), tongsosv INT ) AS BEGIN IF @khoa=0 INSERT INTO @bangthongke SELECT khoa.makhoa,tenkhoa,COUNT(masv)

FROM (khoa INNER JOIN lop ON khoa.makhoa=lop.makhoa) INNER JOIN sinhvien on lop.malop=sinhvien.malop
GROUP BY khoa.makhoa,tenkhoa ELSE INSERT INTO @bangthongke SELECT khoa.makhoa,tenkhoa,COUNT(masv) FROM (khoa INNER JOIN lop ON khoa.makhoa=lop.makhoa) INNER JOIN sinhvien ON lop.malop=sinhvien.malop WHERE khoa=@khoa GROUP BY khoa.makhoa,tenkhoa RETURN /*Tr kt qu v cho hm*/ END

SELECT * FROM dbo.func_TongSV(25) S cho kt qu thng k tng s sinh vin kho 25 ca mi khoa:
Cn cu lnh: SELECT * FROM dbo.func_TongSV(0) Cho ta bit tng s sinh vin hin c (tt c cc kho) ca mi khoa

Cc trigger

Mc ch
nh ngha cc trigger. Lit k cc li ch ca trigger. Hiu v s dng lnh CREATE TRIGGER T-SQL. M t cc bng Inserted v Deleted c s dng bi trigger Gii thch cc kiu trigger: INSERT, UPDATE, v DELETE Xc nh c cc lnh SQL no khng th s dng trong cc
trigger Khai bo trigger xp theo tng v trigger xp lng vo nhau. Khai bo cc trigger INSTEAD OF Ch ra cc nhn t tc ng n vic thc thi hnh trigger

Cc Trigger
Cc trigger l cc th tc lu, chng c thc thi phn ng li cc thao tc chn, cp nht, hoc xa trong mt bng. Cc trigger c kch hot mt cch t ng khi c mt trong cc thao tc trn xut hin. Cc trigger c s dng mt cch ph bin p cc thao tc tun theo cc quy tc nht nh. Cc trigger m bo tnh ton vn v tnh nht qun ca d liu.

Cc trigger

Allen

Mark

Brian Cathy Greg Derek

Carl Kathy

Cc trigger

Allen

Mark Trigger Fired

Brian Cathy Greg Derek

Carl Kathy
Derek Inserted

S dng Trigger
So snh kiu d liu. c d liu t cc bng nm trong c s d liu khc. Thay i theo tng hoc xo lin tc cc bng lin quan trong mt c s d liu Hu b cc thay i khng ng Tun theo cc gii hn,cc gii hn phc tp hn vic bt li bng rng buc CHECK Thc thi cc th tc lu cc b v cc th tc lu t xa

To Trigger
Trigger c th c to bng cng c Enterprise Manager, hoc Query Analyzer. Trong c hai trng hp, cu lnh CREATE TRIGGER c s dng to ra trigger. C php: CREATE TRIGGER Trigger_name ON table FOR [DELETE, INSERT, UPDATE] [WITH ENCRYPTION] AS Sql_statements

Hng dn to cc Trigger
Mt trigger c th kt hp ba thao tc thc hin trn mt bng nh INSERT, UPDATE, v DELETE. Mt trigger thch ng vi mt bng n. Tu chn WITH ENCRYPTION c th c s dng lm n cc khai bo ca mt trigger i vi ngi s dng.Tuy nhin, mt trigger c m ho s khng th b gii m. Mt trigger c th tham chiu n mt view hoc mt bng tm thi, nhng khng th kt hp vi chng. Mt trigger c th bao gm cc cu lnh SQL vi s lng bt k.

Hng dn to cc Trigger
Mc nh, ch c ngi to ra c s d liu mi c quyn to mt trigger trong n. Quyn ny khng th chuyn giao . Mt trigger ch c th c to trong c s d liu hin hnh. Tuy nhin, trigger c th tham chiu n cc i tng nm trong c s d liu khc.

S dng bng Inserted v Deleted

Cc Trigger truy nhp ti hai bng logic c tn l Inserted v Deleted. Bng Inserted v Deleted cha nh ca d liu trc v sau qu trnh cp nht. Gin tng ng vi bng c cp nht . D liu trong bng khng b tc ng bi thao tc cp nht s khng nm trong bng Inserted v Deleted.

Cc kiu Trigger

Cc Trigger INSERT
Mt trigger INSERT c thc thi bt c khi no c thao tc chn thm d liu vo mt bng. trigger INSERT thc thi cc thao tc di y: Chn mt bn sao ca hng mi vao bng Inserted. Kim tra hng mi trong bng Inserted, xc nh xem d liu c chn vo c hp l hay khng . Nu tm thy cc gi tr trong hng chn vo ph hp iu kin ch ra, chn chng vo trong bng trigger .

Cc Trigger INSERT

V d: CREATE TRIGGER CheckRoyalty ON roysched FOR INSERT AS IF (SELECT royalty FROM inserted) > 30 BEGIN PRINT RoyaltyTrigger: Royalty cannot exceed 30 PRINT Change the royalty to a value less than 30 ROLLBACK TRANSACTION END

Cc Trigger INSERT

Cc Trigger UPDATE
Mt trigger UPDATE c thc hin khi mt tc v cp nht thc thi trn bng. N thc thi cc tc v di y: Di chuyn hng d liu gc vo bng logic Deleted. Chn mt hng mi vo bng Inserted, v bng trigger. Tnh ton cc gi tr trong cc bng Deleted v Inserted xc nh can thip cn thit. Cc trigger UPDATE c th c to ra xc nhn vic cp nht trn mt ct n, hoc trn ton b bng.

Cc Trigger UPDATE mc ct

V d:
CREATE TRIGGER NoUpdatePayterms ON sales FOR UPDATE AS IF UPDATE (payterms) BEGIN PRINT You cannot modify the payment terms for an order ROLLBACK TRANSACTION END

Column-level UPDATE Triggers Contd

Cc Trigger UPDATE mc bng

V d:
CREATE TRIGGER NoUpdateDiscount ON discounts FOR UPDATE AS IF (SELECT discount FROM inserted) > 12 BEGIN PRINT You cannot assign a discount greater than 12 percent ROLLBACK TRANSACTION END

Cc Trigger UPDATE mc bng

Cc Trigger DELETE
Mt trigger DELETE c thc hin mi khi xo cc hng t mt bng. trigger DELETE thc hin cc cng vic sau: Xo cc hng t bng trigger. Chn cc hng c xo vo bng Deleted. Kim tra cc hng trong bng Deleted xc nh cc tc v trigger s c thc hin nh th no.

Cc Trigger DELETE

V d:
CREATE TRIGGER NoDelete9901 ON pub_info FOR DELETE AS IF (SELECT pub_id FROM deleted) = 9901 BEGIN PRINT You cannot delete the details of publisher 9901 ROLLBACK TRANSACTION END

Cc Trigger DELETE

Cc lnh SQL ko th s dng Trong Trigger

trigger dy chuyn
Cc trigger dy chuyn c s dng bt buc s ton vn trong tham chiu. Khi mt thay i xut hin trong mt bng, cc trigger dy chuyn sa i d liu trong cc bng lin quan. cc trigger khng th c s dng thc hin vic cp nht dy chuyn hoc xo m nh hng n cc rng buc ca cac kho chnh v kho ngoi. Cc trigger c thc hin sau khi cc rng buc c kim tra; nu mt tc v b qua mt rng buc, trigger khng c thc thi.

trigger lng nhau

Cc trigger lng nhau khi mt tc v trong mt trigger ny khi to mt trigger khc, v ti lt n c th li khi to mt trigger khc na v c nh vy. Cc trigger c th lng nhau ti 32 mc. cho php lng cc trigger, chng ta s dng th tc lu h thng sp_configure nh sau:

sp_configure nested trigger, 1

ko cho php cc trigger lng nhau, chng ta thc hin lnh di y: sp_configure nested trigger, 0

Cc Trigger INSTEAD OF
Mt trigger INSTEAD OF cha on m thay th cc lnh thao tc vi c s d liu gc . Cc trigger INSTEAD OF hu dng nht khi vic sa i d liu c thc thi trn view m chng khng th cp nht theo cch thng thng. Cc trigger INSTEAD OF c th da trn ch mt tc v sa i d liu.

Cc Trigger INSTEAD OF

V d v nh ngha view: CREATE VIEW Emp_pub AS SELECT emp_id, lname, job_id, pub_name FROM employee e, publishers p WHERE e.pub_id = p.pub_id

INSTEAD OF Triggers on View

V d v nh ngha trigger: CREATE TRIGGER del_emp ON Emp_pub INSTEAD OF DELETE AS DELETE employee WHERE emp_id IN (SELECT emp_id FROM DELETED)

Cc Trigger v Hiu sut

Chi ph dnh cho cc trigger l nh. Hu ht thi gian i hi chy mt trigger l dnh cho vic tham chiu n cc bng khc, c th nm trong cc thit b c s d liu Cc bng Deleted v Inserted lun c mt trong b nh

Tng kt
Cc trigger l cc th tc lu m c thc hin t ng
tng tc vi cc tc v thm, cp nht v xo trn mt bng. Cc trigger thng c s dng thc hin cc quy tc nghip v i hi. Lnh CREATE TRIGGER c s dng to mt trigger. Cc trigger truy nhp ti cc bng logic Inserted v Deleted. Cc bng ny cha cc hnh nh ca d liu trc , v sau qu trnh cp nht.

Tng kt
Cc kiu trigger:
INSERT: Thc hin mi khi xut hin vic thm mi d liu vo bng. Cc trigger ny m bo rng d liu c chn vo bng l hp l. UPDATE: Thc hin khi mt tc v cp nht xy ra trn mt bng. Cc trigger ny c th c thi hnh mc bng hoc mc ct. DELETE: Thc hin khi d liu c xo khi mt bng. Cc trigger dy chuyn sa i d liu trong cc bng lin quan, nu mt thay i xut hin trong mt bng.

Tng kt
Cc trigger lng nhau khi mt tc v trong mt trigger ny khi
to mt trigger khc, v ti lt n c th li khi to mt trigger khc na v c nh vy. Th tc lu h thng sp_configure c s dng cho php hoc khng cho php lng cc trigger. trigger INSTEAD OF cha on m thay th cc lnh thao tc vi d liu gc. Cc trigger INSTEAD OF hu dng khi vic sa i d liu c thc hin trn view m chng khng th cp nht theo cch thng thng. Thi gian i hi thc thi mt trigger ch yu dnh nhiu cho vic tham chiu n cc bng khc hn l dnh cho cc bng logic