Bài giảng Ngôn ngữ lập trình trong hệ quản trị cơ sở dữ liệu SQL server 2012

Ữ Ậ

Ị Ơ Ở Ữ Ệ

Ả

Ệ

NGÔN NG L P TRÌNH TRONG H QU N TR C S D LI U SQL SERVER 2012

ữ ậ Ngôn ng l p trình

• Ki u d li u

– Char, VarChar, Nchar, NvarChar

– Bit, TinyInt, SmallInt, Int, BigInt

– Float, SmallMoney, Money, Real

– DateTime

– Text, Image, XML

• Toán tử : + * / %

ể ữ ệ

ữ ậ Ngôn ng l p trình

• Cú pháp khai báo

declare @tên_biến kiểu_dữ_liệu [,…

• Gán giá tr ị

set @tên_biến =

giá_trị | biểu_thức | @biến | hàm

ố ệ

• Kh i l nh

khối_lệnh := begin

lệnh … | khối_lệnh

end

declare @x int, @y int

set @y = 5

set @x = @y + 3

ữ ậ Ngôn ng l p trình

• C u trúc l nh IF

if (điều_kiện)

lệnh …. | khối_lệnh

else

lệnh …. | khối_lệnh

ệ ấ

ấ ệ • C u trúc l nh WHILE

while (điều_kiện)

lệnh | khối_lệnh

ệ ặ ắ • L nh ng t vòng l p

break

continue

ữ ậ Ngôn ng l p trình (Cursor)

• Bi n Cursor

declare tên_biến_cursor cursor

for câu_truy_vấn

ế

open tên_biến_cursor

….

close tên_biến_cursor

ử ụ • S d ng

• H y cursor

deallocate tên_biến_cursor

ủ

ữ ậ Ngôn ng l p trình (Cursor)

fetch định_vị

from tên_biến_cursor

into @tên_biến [,… n]

ể • Di chuy n Cursor

định_vị := next | prior | last | first |

absolute (giá_trị | biến) |

relative (giá_trị | biến)

ạ

• Tr ng thái Cursor

@@fetch_status

ữ ệ ế ế ầ =0 : Đang trong dòng d li u (l n đi k ti p thành công)

ữ ệ ế ế ầ ≠0 : Ngoài dòng d li u (l n đi k ti p không thành công)

ữ ậ Ngôn ng l p trình (ví d )ụ

Declare @t int, @x int

Set @t = 0 ; Set @x = 1

While (@x <= 100)

begin

if ((@x % 2) = 0)

set @t = @t + @x

set @x = @x + 1

end

Print @t

ố ẳ ừ ổ Tính t ng s ch n t 1 > 100

ữ ậ Ngôn ng l p trình (ví d )ụ

Declare sv cursor for select * from sinhvien

Open sv

Declare @ma char(5),@ten char(10)

Fetch next from sv into @ma,@ten

While (@@fetch_status = 0)

begin

print @ma + ‘ : ‘ + @ten

Fetch next from sv into @ma,@ten

end

Close sv; Deallocate sv

In các sinhvien(masv char(5),tensv char(10))

Th t củ ụ (Procedure) là một chương trình trong cơ sở dữ liệu gồm nhiều câu lệnh mà bạn lưu lại

ạ ậ

cho những lần sử dụng sau. Trong SQL Server, bạn có thể truyền các tham số vào procedure, tuy nó không trả về một giá trị cụ thể như function (hàm) nhưng cho biết việc thực thi thành công hay thất bại. ủ ụ T o l p th t c

create procedure tên_thủ_tục

@tên_tham_số kiểu_dữ_liệu loại [,…n]

lệnh | khối_lệnh

loại := input (không cần ghi)|

output

ự

ủ ụ Th c thi th t c

exec tên_thủ_tục giá_trị|@biến [output] [,…n]

ủ ụ

Xóa th t c

Drop procedure tên_thủ_tục

ổ ủ ụ

Thay đ i th t c

Alter procedure tên_thủ_tục

Th t củ ụ (ví d )ụ

ế

ủ ụ

ố t th t c xóa các sinh viên theo thành ph

sinhvien (masv char(5), tp char(5))

create procedure xoasinhvien

@tp char(5)

begin

delete from sinhvien where tp = @tp

end

exec xoasinhvien ‘HCM’

Th t củ ụ (ví d )ụ

ế

ủ ụ ế

ố

t th t c đ m xem có bao nhiêu sinh viên theo thành ph

create procedure dem @tp char(5), @t int output

begin

select @t = count(*) from sinhvien

where tp = @tp

end

declare @tong int

exec dem ‘HCM’ , @tong output

print @tong

Hàm (Function)

ạ ậ

T o l p hàm

create function tên_hàm

( @tên_tham_số kiểu [,…n] ) returns kiểu_trả_về

lệnh | khối_lệnh

ự

Th c thi hàm

= tên_hàm (giá_trị | @biến [,…n] )

Xóa hàm

Drop function tên_hàm

Thay đ i hàmổ

Alter function tên_hàm

Hàm (ví d )ụ

create function dem (@tp char(5)) returns int

begin

declare @t int

select @t = count(*) from sinhvien

where tp = @tp

return @t

end

declare @tong int

set @tong = dbo.dem(‘HCM’)

ế ế Vi ố t hàm đ m xem có bao nhiêu sinh viên theo thành ph .

Hàm (ví d )ụ

ự ộ

t hàm sinh ra mã sinh viên t

ự

ị

ắ đ ng theo quy t c: mã sinh viên có ); 0001 : là i sinh viên có mã cao nh t là BA0024,

ế Vi ạ d ng: BA0001. Trong đó ‘BA’ : quy đ nh (luôn có 2 ký t ấ ệ ạ ố ố ồ s g m 4 ký s . VD: Hi n t ớ thì sinh mã m i là BA0025.

Create function sinhkhoa () returns char(6) As

Begin

declare @max int

select

@max = max(cast(substring(masv,3,4) as int)) + 1

from sinhvien

declare @s char(8), @s1 char(6)

set @s = ‘0000’ + cast(@max as char(4))

set @s1 = ‘BA’ + right(rtrim(@s),4)

return @s1

end

Hàm ụ (ví d table function)

create function laydssv (@malop char(5))

returns TABLE

return (

select masv,tensv from sinhvien

where malop = @malop

)

select * from laydssv('QT1')

Hàm ụ (ví d table function)

create function laydssv1 (@malop char(5))

returns @btam table(masv char(5),tensv char(20))

begin

insert into @btam

select masv,tensv from sinhvien

where malop = @malop

return

end

select * from laydssv1('QT1')

ẫ ự ệ B y s ki n (Trigger)

ữ

ớ

c phát sinh sau nh ng hành vi thêm m i hay

ẫ ệ • B y l nh đ ổ

ả

ượ thay đ i, xóa trên b ng.

ể ủ

ậ

ữ ệ ậ – Có th h y các c p nh p trên d li u

ượ

ế ữ

ể

c phát sinh đ thay th nh ng hành vi thêm,

ẫ ệ • B y l nh đ ổ đ i, xóa.

ử

ờ

ị ớ ượ ư

ẫ ự ệ ư • B y s ki n l u gi

c đ a vào và

ượ

ị giá tr cũ đ

tách r i giá tr m i đ ỏ c xóa b .

ả

ạ

– Dùng b ng t m Inserted và deleted

ẫ ự ệ

ụ

• B y s ki n còn áp d ng cho

ẫ ự ệ

B y s ki n (trigger)

ạ

Lo i trigger FOR

Insert | Update | Delete

Điểm lưu (lưu thể hiện dữ liệu tại thời điểm hiện tại)

Lưu vào dữ liệu

Deleted

Inserte d

Lệnh bẫy sự kiện

Không chấp nhận sự thay đổi (RollBack)

Chấp nhận (Commit)

Kết thúc

ẫ ự ệ

B y s ki n (trigger)

ạ

Lo i trigger INSTEAD OF

Insert | Update | Delete

K h ô n g

h a y

đ ổ

d ữ

l i

ệ u

Deleted

Inserte d

Lệnh bẫy sự kiện

Kết thúc

ẫ ự ệ B y s ki n (Trigger)

ả ạ ẫ ự ệ B ng t m cho b y s ki n

ắ ượ ư ữ ệ ư ữ inserted: L u nh ng thông tin s p đ c đ a vào d li u

ị ượ ữ ư ẩ ế deleted: L u nh ng thông tin đã có và chu n b đ c thay th

ặ ự ệ ả ả ấ ạ ố B ng t m có c u trúc gi ng b ng đ t s ki n.

VD:

ậ ậ ố ớ C p nh p sinh viên mã ‘BA0002’ v i thành ph ‘HCM’ thành

‘HN’

ả ộ => Vào b ng inserted là b (‘BA0002’,’aaa’,’HN’)

ả ộ => Vào b ng deleted là b (‘BA0002’,’aaa’,’HCM’)

ẫ ự ệ B y s ki n (Trigger)

ộ ữ ệ ể ể ộ ị Các hàm dùng đ ki m tra c t d li u b tác đ ng:

update(tên_c tộ ) :

ả ề ế ế ộ ị ậ ả ậ Tr v k t qu True / False n u c t đó b c p nh p.

columns_updated() :

ả ề ộ ố ộ ượ ậ ậ ị Tr v m t s xác đ nh các c t đ c c p nh p.

Ví d : ụ Bang (c1,c2,c3,c4)

ế ượ ậ ậ ế ả N u c2 đ c c p nh t => 0010: k t qu hàm là 2

ế ượ ậ ậ ế ả N u c1,c3,c4 đ c c p nh t => 1101: k t qu hàm là 13

ẫ ự ệ B y s ki n (Trigger)

alter database tendatabase

set recursive_triggers { on | off }

ầ ữ

(Update T1, kích ch y ạ Trigger 1. Trigger 1 ch y ạ update T1 l n n a, kích

ch y ạ Trigger 1…)

ự ế ố ổ ệ Thay đ i thông s cho phép đ quy tr c ti p

exec sp_configure ‘Nested Triggers’ n

(Update T1, kích ch y ạ Trigger 1. Trigger 1 ch y ạ update T2, kích ch y ạ

Trigger 2. Trigger 2 ch y ạ update T1, kích ch y ạ Trigger 1. …)

ồ

ượ ố

ấ

ộ

L ng nhau đ

c t

i đa là 32 c p đ .

ế ậ ớ ạ ồ ế ệ Thi t l p gi i h n l ng nhau (đ quy gián ti p)

ẫ ự ệ B y s ki n (Trigger)

create trigger tên_trigger

tên_bảng

{for|instead of} {insert|delete|update}

lệnh | khối_lệnh

ạ T o trigger

Xóa và thay đ iổ

{Alter | Drop} trigger tên_trigger

ẫ ự ệ B y s ki n (ví d )ụ

ạ

ỏ

ườ

ệ

ề

ả T o trigger cho b ng sinhvien (masv, tensv, malop) th a ộ ớ mãn đi u ki n m t l p không quá 20 ng

Create trigger tssv on sinhvien for insert,update

Begin

declare @malop char(5), @ts int

select @malop = malop from inserted

select @ts = count(*) from sinhvien

where malop=@malop

if (@ts > 20)

rollback transaction

end

ẫ ự ệ B y s ki n (ví d )ụ

ứ

ộ

ạ ỏ ạ

T o trigger cho b ng sinhvien (masv, tensv, trangthai) ổ th a mãn đi u ki n khi xóa m t sinh viên t c thay đ i tr ng thái t

ả ề ệ ừ 0 thành 1

Create trigger tssv on sinhvien instead of delete

Begin

update sinhvien set trangthai = 1

where

masv in (select masv from deleted)

end

Bài giảng Ngôn ngữ lập trình trong hệ quản trị cơ sở dữ liệu SQL server 2012