NỘI DUNG
Khái niệm
Thủ tục
Thủ tục với tham số đầu vào
Thủ tục với tham số đầu ra
Thủ tục có lệnh trả về Return
Sử dụng bảng tạm trong thủ tục
Tham số cursor bên trong thủ tục
Giao tác (Transaction)
28 trang |
Chia sẻ: phuongt97 | Lượt xem: 500 | Lượt tải: 0
Bạn đang xem trước 20 trang nội dung tài liệu Bài giảng SQL server - Chương 5: Store Procedure (Thủ tục) - Lê Thị Minh Nguyện, để xem tài liệu hoàn chỉnh bạn click vào nút DOWNLOAD ở trên
10/03/2015
TRƯỜNG CAO ĐẲNG CÔNG NGHỆ THÔNG TIN
THÀNH PHỐ HỒ CHÍ MINH
CHƯƠNG 5. Store
Procedure (thủ tục)
GV: Lê Thị Minh Nguyện
Email: leminhnguyen@itc.edu.vn
NỘI DUNG
Khái niệm
Thủ tục
Thủ tục với tham số đầu vào
Thủ tục với tham số đầu ra
Thủ tục có lệnh trả về Return
Sử dụng bảng tạm trong thủ tục
Tham số cursor bên trong thủ tục
Giao tác (Transaction)
1
10/03/2015
1.Khái niệm
Store procedure (thủ tục): là một tập hợp chứa các
dòng lệnh, các biến và các cấu trúc điều khiển trong
ngôn ngữ Transaction-SQL dùng để thực hiện một hành
động nào đó.
Các nét đặc trưng
. Tên thủ tục
. Tham số truyền giá trị vào
. Tham số đón nhận giá trị ra
. Trong thủ tục nội tại được phép gọi thực thi một thủ tục nội
tại khác
. Có tính cục bộ bên trong một cơ sở dữ liệu lưu trữ thủ tục đó
. Có thể gọi thực hiện trong môi trường không phải Microsoft
SQL Server.
1.Khái niệm
Lợi ích của thủ tục
. Tốc độ xử lý của các thủ tục nội tại rất nhanh.
. Việc tổ chức và phân chia các xử lý thành hai nơi khác
nhau: tại máy chủ hoặc tại máy trạm sẽ giúp giảm thời
gian xây dựng ứng dụng.
Thủ tục hệ thống
. Bắt đầu bằng chữ sp_ và hầu hết tất cả các thủ tục hệ
thống được lưu trữ bên trong CSDL Master.
2
10/03/2015
2.Thủ tục
Cú pháp:
CREATE PROC[EDURE] Tên_thủ_tục
AS
[Declare biến_cục_bộ]
các_lệnh
2.Thủ tục
Ví dụ 1: cho lược đồ CSDL như sau:
MAT_HANG(MaMH, TenMH, DVT, MaNCC, DonGia)
PHIEU_XUAT(SoPX, NgayXuat, #SoDH)
CTPX(Ma_MH, SoPX, SLXuat)
HOA_DONDH(SoDH, NgayDat)
CTDH(SoDH, MaMH, SLDH)
3
10/03/2015
2.Thủ tục
Ví dụ 1: cho lược đồ CSDL như sau:
NHANVIEN(MaNV, HoTen, NgaySinh, NgayVaoLam,
MaPhong, ChucVu, LuongCB, PhuCap)
Phong(MaPh, TenPh, DiaDiem)
DEAN(MaDA, TenDA, KinhPhi, NgayKhoiCong)
PHANCONG(MaNV,MaDA, NgayBatDau, NgayKetThuc)
2.Thủ tục
Ví dụ 1: Cho biết mặt hàng nào có doanh số bán cao
nhất trong tháng 01/20014.
CREATE PROC sp_MaxSLHang
AS
Declare @TenMH varchar(50), @MaxSL int
Select @TenMH=RTRIM(TenMH), @MaxSL=SLXuat*dongia
From CTPX, PHIEU_XUAT, MAT_HANG
Where CTPX.SoPX= PHIEU_XUAT.SoPX
And MAT_HANG.MaMH=CTPX.MaMH
And convert(char(7), NgayXuat, 21)= ‘2014-01’
And SLXuat *dongia= (Select Max(SLXuat*dongia)
From CTPX, PHIEU_XUAT
Where CTPX.SoPX=PHIEU_XUAT.SoPX
And convert(char(7), NgayXuat, 21)= ‘2014-01’)
Print @TenMH + ‘Co doanh so cao nhat la’ + Cast(@MaxSL as char(10))
4
10/03/2015
2.Thủ tục
Gọi thực hiện thủ tục:
Cú pháp:
EXEC[UTE] Tên_thủ_tục
Ví dụ:
EXEC sp_MaxSLHang
Thay đổi nội dung thủ tục
Cú pháp:
ALTER PROC[EDURE] Tên_thủ_tục
AS
[Declare biến_cục_bộ]
Các_lệnh.
2.Thủ tục
ALTER PROC sp_MaxSLHang
AS
Declare @TenMH varchar(50), @MaxSL int
IF NOT EXISTS(Select Ma_mh
From CTPX, PHIEU_XUAT
Where CTPX.SoPX= PHIEU_XUAT.SoPX
And convert(char(7), NgayXuat, 21)= ‘2014-01’)
Begin
Print ‘thang 01 nam 2014 chưa bán mặt hàng nào cả’
Return
End
Select @TenMH=RTRIM(TenMH), @MaxSL=SLXuat
From CTPX, PHIEU_XUAT, MAT_HANG
Where CTPX.SoPX= PHIEU_XUAT.SoPX
And MAT_HANG.MaMH=CTPX.MaMH
And convert(char(7), NgayXuat, 21)= ‘2007-01’
And SLXuat = (Select Max(SLXuat)
From CTPX, PHIEU_XUAT
Where CTPX.SoPX=PHIEU_XUAT.SoPX
And convert(char(7), NgayXuat, 21)= ‘2007-01’)
Print @TenMH + ‘Co doanh so cao nhat la’ + Cast(@MaxSL as char(10))
5
10/03/2015
3.Thủ tục với tham số đầu vào
Cú pháp:
CREATE PROC[EDURE] Tên_thủ_tục
@Tên_tham_số kiểu_dữ_liệu [= giá_trị]
AS
[Declare biến_cục_bộ]
các_lệnh
3.Thủ tục với tham số đầu vào
Create Proc sp_Days(@Thang Int, @Nam Int)
As
Declare @SN Int
Set @SN = Case
When @Thang In (1,3,5,7,8,10,12) Then 31
When @Thang In (4,6,9,11) Then 30
When @Nam % 4 = 0 Then 29
Else 28
End
Return @SN
6
10/03/2015
3.Thủ tục với tham số đầu vào
Gọi thực hiện thủ tục
Declare @SN int
Exec @SN = spr_ngaytrongthang 2,2015
print 'So ngay trong thang 2/2002 la '
+ Cast(@SN As Char)
3.Thủ tục với tham số đầu vào
Tạo thủ tục tính tổng giá trị của một phiếu xuất hàng hoá với tham
số vào là số phiếu xuất với kiểu dữ liệu là chuỗi.
CREATE PROC sp_TongTGXuat @SoPX char(4)
AS
Declare @TongTG money
Select @TongTG=SUM(SLXuat*DGXuat)
From CTPX
Where @SoPX=SoPX
Print ‘Tri gia phieu xuat’ + CAST(@SoPX AS char(4))
Print ‘là: ’ + CAST(@TongTG as Varchar(15))
Gọi thực hiện thủ tục
Exec sp_TongTGXuat ‘PX01’
Hoặc:
Exec sp_TongTGXuat @SoPX=‘PX01’
7
10/03/2015
3.Thủ tục với tham số đầu vào
Tạo thủ tục tính số đặt hàng của một mặt hàng trong một đơn
đặt hàng có 2 tham số vào là số đặt hàng và mã mặt hàng.
CREATE PROC sp_TinhSLDat @SoDH char(4), @MaMH char(4)
AS
Declare @Sldat int
IF NOT EXISTS(Select MoDH From CTDH
Where SoDH=@SoDH And MaMH=@MaMH)
Begin
Print ‘khong hop le, xem lai don dat hang’
Return
End
Select @SLDat = SLDat
From CTDH
Where SoDH = @SoDH And MaMH = @MaMH
Print ‘Don dat hang ’ + @SoDH
Print ‘Voi ma mat hang ’ + @MaMH
Print ‘Co so luong dat la: ’ + Cast(@SLDat as varchar(10))
3.Thủ tục với tham số đầu vào
Gọi thực hiện thủ tục:
Exec sp_TinhSLDat ‘DH01’, ‘Fe’
Hoặc
Exec sp_TinhSLDat @MaMH = ‘Fe’, @SoDH = ‘DH01’
8
10/03/2015
3.Thủ tục với tham số đầu vào
Tạo thủ tục thêm mới dữ liệu vào bảng MAT_HANG với
tên sp_MATHANG_Them gồm có 4 tham số vào chính là
các giá trị thêm mới cho các cột trong bảng
MAT_HANG: mã mặt hàng, tên mặt hàng, đơn vị tính
Trong đó cần kiểm tra các ràng buộc dữ liệu phải hợp lệ
trước khi thực hiện lệnh INSERT INTO để thêm dữ liệu
vào bảng MAT_HANG.
. Mã mặt hàng phải duy nhất
. Tỷ lệ phần trăm phải nằm trong miền giá trị 0 đến
100
MATHANG(MAMH, TENMH, DVT, PHANTRAM)
3.Thủ tục với tham số đầu vào
CREATE PROC SP_MATHANG_Them
@MaMH char(4), @TenMH varchar(50), @DVT varchar(50),
@PhanTram INT
AS
--Định nghĩa chuỗi lỗi
DECLARE @ErrMsg varchar(200)
--Kiểm tra có mặt hàng chưa?
IF EXISTS(SELECT MaMH FROM MAT_HANG
WHERE MaMH=@MaMH)
BEGIN
SET @ErrMsg = ‘Mã mặt hàng [’ + @MaMH + ‘] đã có’
RAISERROR(@ErrMsg, 16, 1)
RETURN
END
9
10/03/2015
3.Thủ tục với tham số đầu vào
--Kiểm tra tỷ lệ phần trăm nằm ngoài 0..100
IF @PhamTram NOT BETWEEN 0 AND 100
BEGIN
SET @ErrMsg = ‘Tỷ lệ phần trăm nằm trong đoạn [0, 100]’
RAISERROR(@ErrMsg, 16, 1)
Return
END
--Khi các RBTV hợp lệ thì thêm dữ liệu vào bảng MatHang
INSERT INTO MAT_HANG(MaMH, TenMH, DVT, PhanTram)
VALUES(@MaMH, @TenMH, @DvTinh, @PhanTram)
4.Thủ tục với tham số đầu ra
Cú pháp:
CREATE PROC Tên_thủ_tục
@Tên_tham_số kiểu_dữ_liệu OUTPUT [,]
AS
[Declare Biến cục bộ]
Các_lệnh
10
10/03/2015
4.Thủ tục với tham số đầu ra
Create Proc sp_ThuTrongTuan
@D As SmallDateTime, @Thu VarChar(10) OUTPUT
As
Set @Thu = Case Datepart(w, @D)
When 1 Then 'Chu Nhat’
When 2 Then 'Thu Hai’
When 3 Then 'Thu Ba’
When 4 Then ‘Thu Tu’
When 5 Then ‘Thu Nam’
When 6 Then ‘Thu Sau’
Else 'Thu Bay’
End
4.Thủ tục với tham số đầu ra
Gọi thực hiện thủ tục
set dateformat dmy
declare @t varchar(10)
exec sp_thutrongtuan
@d='2/9/2015',@thu=@t Output
print @t
11
10/03/2015
4.Thủ tục với tham số đầu ra
Tạo thủ tục tính số đặt hàng của một mặt hàng trong
một đơn đặt hàng có 2 tham số vào là số ĐƠN đặt
hàng và mã mặt hàng, trả ra số lượng đặt hàng của
một vật tư tương ứng trong đơn đặt hàng thông qua
tham số đầu ra.
CREATE PROC sp_TinhSLDat
@Sodh char(4), @MaMH char(4), @SLDat int OUTPUT
AS
IF NOT EXISTS(Select MaDH From CTDH
Where MaDH=@SoDH And MaMH=@MaMH)
Begin
Print 'khong hop le, xem lai don dat hang'
Return
End
Select @SLDat = SLDH
From CTDH
Where SoDH = @SoDH And MaMH = @MaMH
4.Thủ tục với tham số đầu ra
Gọi thực hiện thủ tục
DECLARE @SLDatHang int
EXEC sp_TinhSLDat @MaMH = ‘Fe’, @SoDH = ‘DH01’,
@SLDat = @SLDatHang OUTPUT
Print ‘Don dat hang DH01 với mặt hàng Fe’
Print ‘co so luong dat la: ’ + CAST(@SLDatHang AS varchar(10))
12
10/03/2015
5.Thủ tục có lệnh trả về Return
Return không có giá trị chỉ định thì thủ tục sẽ trả về giá trị
là không (0).
Return [Số_nguyên]
Ví dụ: Tạo thủ tục tính tổng số lượng đặt hàng của một mặt
hàng đối với một nhà cung cấp chỉ định, kiểm tra xem giá
trị của mặt hàng và mã nhà cung cấp mà người dùng
truyền vào thủ tục có đúng hay không? Qui định thủ tục trả
về 1 khi mã mặt hàng không tồn tại, trả về 2 khi mã nhà
cung cấp không tồn tại.
5.Thủ tục có lệnh trả về Return
CREATE PROC sp_TinhTongSLDat
@MaNCC char(3), @MaMH char(4),
@TongSLdat INT OUTPUT
AS
IF NOT EXISTS(Select * From Mat_Hang Where MaMH=@MaMH)
Return 1
IF NOT EXISTS(Select * From Mat_Hang Where MaNCC=@MaNCC)
Return 2
Select @TongSLdat = SUM(SLDat)
From HoaDon_DH, CTDH
Where HoaDon_DH.MaDH = CTDH.MaDH
And MaNCC=@MaNCC And MaMH=@MaMH
IF @TongSLdat IS NULL
Set @TongSLdat=0
Return
13
10/03/2015
5.Thủ tục có lệnh trả về Return
Gọi thực hiện thủ tục:
Declare @TongSLD INT, @Ketqua INT
EXEC @ketqua = sp_TinhTongSLDat 'NCCA', 'Fe',
@TongSLdat=@TongSLD OUTPUT
IF @ketqua =1
Print 'Mã mặt hàng không hợp lệ'
ELSE IF @ketqua=2
Print 'Mã nhà cung cấp không hợp lệ'
ELSE
Print 'Tổng số lượng đặt là: ' + CAST(@TongSLD as char(10))
6.Sử dụng bảng tạm trong thủ tục
Cú pháp:
SELECT danh_sách_các_cột
INTO #Tên_bảng_tạm
FROM Tên_bảng_dữ_liệu
(#): tạo ra các bảng tạm cục bộ
(##): tạo ra các bảng tạm toàn cục
14
10/03/2015
6.Sử dụng bảng tạm trong thủ tục
Tạo thủ tục đưa vào một năm tháng bất kỳ cho biết mặt
hàng nào bán ra doanh thu cao nhất trong tháng năm đó
CREATE PROC sp_TinhDTCaoNhat
@namThang char(7),
@TenMH char(50) OUTPUT, @TongTien Money OUTPUT
AS
Select MH.MaMH, TenMH, Sum(SLXuat*DGXuat) AS TT
INTO #DoanhThu
From Phieu_xuat PX, CTPX, Mat_Hang MH
Where PX.SoPX = CTPX.SoPX And CTPX.MaMH = MH.MaMH
And Convert(char(7), ngayxuat, 21) = @namthang
Group By MH.MaMH, TenMH
Order by SUM(SLXUAT*DGXUAT) DESC
Select Top 1 @TenMH=TenMH, @Tongtien = TT
From #DoanhThu
6.Sử dụng bảng tạm trong thủ tục
Declare @Ten_MH char(50), @Tong_Tien Money
EXEC sp_TinhDTCaoNhat ‘2007-01’,
@TenMH = @Ten_MH OUTPUT,
@TongTien = @Tong_Tien OUTPUT
IF @TenMH IS NULL
Print ‘không có dữ liệu tính toán’
ELSE
Begin
Print Rtrim(@TenMH) + ‘có doanh thu cao nhất’
Print ‘là ’ + CAST(@TongTien AS Varchar(20)) + ‘VND’
End
15
10/03/2015
7.Tham số cursor bên trong thủ tục
Tham số cursor trả về danh sách các dòng dữ liệu theo
điều kiện chọn lọc nào đó.
Cursor được chia làm 2 phần: bên trong thủ tục và bên
ngoài thủ tục.
. Các hành động trong thủ tục:định nghĩa dữ liệu cho biến kiểu
cursor và mở cursor.
. Các hành động bên ngoài thủ tục: đọc từng dòng dữ liệu bên
trong cursor và sau cùng là đóng cursor lại.
7.Tham số cursor bên trong thủ tục
Tạo thủ tục trả về danh sách các mã vật tư đã bán ra nhiều
nhất trong năm tháng nào đó.
Bước 1: tạo thủ tục có tham số kiểu dữ liệu cursor chứa danh
sách các mặt hàng đã bán ra nhiều nhất.
CREATE PROC sp_TinhDSoBan
@NamThang char(6),
@cur_Dsmh CURSOR VARYING OUTPUT
AS
SELECT CTDH.MAMH, SUM(SLDH) AS TongSL
INTO #TongSLBan
FROM CTDH, MAT_HANG, HOA_DONDH
WHERE Convert(char(6), NgayDat, 112) = @NamThang
AND CTDH.MaMH=MAT_HANG.MaMH
AND CTDH.SoDH=HOA_DONDH.SoDH
Group By CTDH.MaMH
16
10/03/2015
7.Tham số cursor bên trong thủ tục
--Kiểm tra dữ liệu có phát sinh
IF EXISTS(SELECT MaMH FROM #TongSLBan)
Begin
-- Khởi tạo giá trị biến CURSOR
SET @cur_Dsmh = CURSOR Forward_Only
FOR
SELECT MAMH, TongSLBan
From #TongSLBan
Where TongSLBan = (SELECT MAX(TongSLBan)
FROM #TongSLBan)
--Mở cursor
OPEN @cur_Dsmh
DROP TABLE #TongSLBan
Return
End
-- Khi không có dữ liệu phát sinh
DROP Table #TongSLBan
Return 1
7.Tham số cursor bên trong thủ tục
Bước 2: đọc cursor, đón nhận danh sách các mã mặt
hàng đã bán ra nhiều nhất trong tháng 01 năm 2002
DECLARE @cur_Dsmh CURSOR, @Gtmh INT,
@MaMH char(4), @TongslBan INT
EXEC @Gtmh = sp_TinhDsoBan ‘200702’, @cur_Dsmh OUTPUT
--Xử lý tiếp sau đó
IF @Gtmh = 0
Begin
Print ‘danh sách các mặt hàng’
While(0=0)
Begin
Fetch Next From @cur_Dsmh INTO @MaMH, @TongslBan
IF @@Fetch_status0
Break;
Print ‘Mã vật tư: ’ + @MaMH
Print ‘Tổng số lượng: ’ + CAST(@TongslBan AS varchar(10))
Print Replicate(‘-’, 50)
End
End
ELSE Print ‘không có bán hàng trong năm tháng chỉ định’
17
10/03/2015
8.Giao tác (Transaction)
Khái niệm
Các tính chất
Giao tác không tường minh
Giao tác tường minh
Phân vùng giao tác
Kiểm tra lỗi bên trong giao tác
8.1.Khái niệm
Một giao tác là một đơn vị xử lý nguyên tố gồm nhiều hành
động. Khi thực hiện một giao tác hoặc phải thực hiện tất cả
các hành động của nó hoặc thì không thực hiện hành động
nào hết.
Giao tác trong các loại CSDL quan hệ lớn được sử dụng
trong những trường hợp mà các hành động cập nhật dữ liệu
trên nhiều bảng khác nhau được thực hiện trong cùng một
đơn vị (unit).
18
10/03/2015
8.2.Các tính chất
Tính nguyên tố (Atomicity)
Tính nhất quán (Consistency)
. Một giao tác phải biến csdl từ trạng thái nhất quán này sang trạng
thái nhất quán khác
Tính độc lập (Isolation)
. Một giao tác phải thực hiện độc lập với giao tác khác đang được
xử lý đồng thời với nó
Tính bền vững (Durability)
. Tất cả các thay đổi trên dữ liệu về thao tác thực hiện phải được ghi
nhận bền vững trên csdl.
8.3.Giao tác không tường minh
Mặc định các lệnh bên trong lô (batch) chứa các câu lệnh
không tường minh, điều này có nghĩa là nếu có ít nhất 1
câu lệnh thực hiện không thành công trong lô thì tất cả
các lệnh còn lại sẽ không được ghi nhận lại.
19
10/03/2015
8.3.Giao tác không tường minh
Ví dụ: cho thực hiện cùng lúc 3 lệnh để cập nhật dữ liệu vào 3
bảng khác nhau trong cùng một lô.
--Thêm mặt hàng mới
INSERT INTO MAT_HANG(MaMH, TenMH, DVT)
VALUES(‘D001’, ‘đèn ngủ’, ‘cái’ )
--Sửa đổi tên nhà cung cấp ‘NCC01’
UPDATE NHACC
SET TenNCC = ‘Le Khai Hoan’
Where MaNCC = ‘NCC01’
--Xoá đơn đặt hàng ‘DH001’
DELETE HOA_DONDH
Where MaDH = ‘DH001’ Vi phạm toàn vẹn DL về
GO khóa ngoại. Nên các lệnh
trứơc đó không thực hiện
8.4.Giao tác tường minh
Giao tác tường minh trong những trường hợp
cập nhật dữ liệu trên nhiều bảng khác nhau
và phải đảm bảo các hành động này nằm
trong cùng một đơn vị xử lý.
20
10/03/2015
8.4.Giao tác tường minh
Bắt đầu một giao tác
. BEGIN TRAN[SACTION]
[Tên_giao_tác] Kết thúc giao tác nhưng không
ghi nhận lại các hành động cập
Kết thúc một giao tác nhật dữ liệu bên ngoài giao tác.
. ROLLBACK TRAN[SACTION]
[Tên_giao_tác]
Hoặc
. COMMIT TRAN[SACTION]
[Tên_giao_tác]
Kết thúc giao tác nhưng đồng ý ghi
nhận lại các hành động cập nhật dữ
liệu bên trong giao tác.
8.4.Giao tác tường minh
Ví dụ
Begin tran cap1
insert into #test values(1,'aaa')
begin tran cap2
insert into #test values(2,'bbb')
begin tran cap3
insert into #test values(3,'ccc')
commit tran cap3
go
commit tran cap2
go
rollback tran cap1
21
10/03/2015
8.5.Phân vùng trong giao tác
Ta chia nhỏ các hành động bên trong giao tác
thành nhiều phần, tương ứng từng phần nhỏ
ta có thể dễ dàng chủ động đồng ý ghi nhận
hoặc không ghi nhận lại việc cập nhật dữ liệu.
Cú pháp
SAVE TRAN[SACTION] [Tên vùng]
Các lệnh
8.5.Phân vùng trong giao tác
Ví dụ:
Begin tran
--Vùng 1
SAVE TRAN Vung_1_2
insert into #test values(1,'aaa')
insert into #test values(2,'bbb')
--Vùng 2
SAVE TRAN Vung_3
insert into #test values(3,'ccc')
RollBack Tran Vung_3 Chỉ có mẩu
Commit Tran Vung_1_2 tin thứ 1 và
thứ 2 được
ghi nhận lại
22
10/03/2015
8.6.Kiểm tra lỗi bên trong giao tác
Biến hệ thống @@ERROR kiểm tra kết quả một câu
lệnh thực hiện gần nhất (ERROR trả về 0 thì thực
hiện thành công)
Ví dụ
Thực hiện việc cấp phát chứng từ tự động cho các bảng
DONDH, PNHAP, PXUAT đảm bảo rằng các số này không
bị trùng lắp khi cùng lúc có nhiều người sử dụng cùng lập
các chứng từ liên quan.
. B1. Xây dựng bảng CAPCTU dùng lưu trữ số chứng từ được cấp
kế tiếp cho các bảng gồm: tên bảng (khoá chính), số chứng từ, ký
tự đầu.
. B2: Lần lượt thêm các dòng dữ liệu vào bảng CAPCTU
. B3: Xây dựng thủ tục cấp số chứng từ tự động đảm bảo không
trùng lắp
. B4: Gọi thực hiện thủ tục trên để có chứng từ kế tiếp trong bảng
PNHAP
23
10/03/2015
Ví dụ
. B1. Xây dựng bảng CAPCTU dùng lưu trữ số chứng từ
được cấp kế tiếp cho các bảng gồm: tên bảng (khoá
chính), số chứng từ, ký tự đầu.
Create table CAPCTU
( tenbang char(20),
soctu int,
kytu char(1),
primary key(tenbang)
)
Ví dụ
B2: lần lượt thêm các dòng dữ liệu vào bảng CAPCTU
INSERT INTO CAPCTU
VALUES('DonDH',100,'D')
INSERT INTO CAPCTU
VALUES('PNhap',100,'N')
INSERT INTO CAPCTU
VALUES('PXuat',100,'X')
24
10/03/2015
Ví dụ
B3: xây dựng thủ tục cấp số chứng từ tự động đảm bảo
không trùng lắp
CREATE PROC spud_cap_soctu_ke
@stenbang char(20), @sSoctuke char(4) OUTPUT
AS
DECLARE @nError int, @nRowCount int,
@nSoctuke int, @sChuoiTam char(4), @sKytu char(1)
Begin Tran
-- Tang so chung tu ke tiep
UPDATE CAPCTU
SET soctu=soctu+1
WHERE tenbang=@sTenbang
Ví dụ
--kiem tra viec tang co thanh cong khong?
SELECT @nError=@@Error,
@nRowCount=@@rowcount
--Neu co loi hoac khong cap nhat duoc mau tin nao
IF @nError0 or @nRowCount1
BEGIN
Rollback tran
return -999
END
25
10/03/2015
Ví dụ
--Lay ra so chung tu ma ta da tang thanh cong
SELECT @nSoctuke=SOCTU,@skytu=kytu
FROM CAPCTU
WHERE tenbang=@stenbang
--kiem tra viec lay du lieu co thanh cong khong
SELECT @nError=@@ERROR,
@nRowCount=@@rowcount
Ví dụ
--Nếu có lỗi hoặc không lấy đươc mẩu tin nào
IF @nError0 or @nRowCount1
BEGIN
rollback tran
return -998
end
--Tính số chứng từ khi không có lỗi nào hết
SET @SchuoiTam = LTRIM(STR(@nSoctuke))
SET @sSoctuke=@skytu+@sChuoiTam
COMMIT TRAN
RETURN 0
26
10/03/2015
Ví dụ
B4: Gọi thực hiện thủ tục trên để có chứng từ kế tiếp trong bảng
PNHAP
DECLARE @sSoctu char(4),@nGttv int
EXEC @nGttv=spud_Cap_Soctu_ke 'PNhap',@sSoctu OUTPUT
IF @nGttv0
print 'co loi khi cap so chung tu, xem lai...'
else
print 'So chung tu moi la: '+@sSoctu
Kết quả:
So chung tu moi la: N101
27
10/03/2015
TRƯỜNG CAO ĐẲNG CÔNG NGHỆ THÔNG TIN
THÀNH PHỐ HỒ CHÍ MINH
28
Các file đính kèm theo tài liệu này:
- bai_giang_sql_server_chuong_5_store_procedure_thu_tuc_le_thi.pdf