Với phần 2 sử dụng stored procedure với tham số, người dùng có thêm kiến thức và cách vận hành. Đây là tổng hợp kiến thức từ giảng viên FPT Polytechnic với mong muốn bật mí những tips nhanh – gọn – hiệu quả khi sử dụng.
PHẦN 2: Sử dụng stored procedure với tham số
Tạo Stored Procedure với tham số truyền vào:
Trong phần này Stored Procedure sẽ thao tác trên csdl với các bảng như sau:
Thủ tục lưu trữ được tạo bởi câu lệnh CREATE PROCEDURE với cú pháp như sau:
CREATE PROCEDURE tên_thủ_tục [(danh_sách_tham_số)] [WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION]
AS
Các_câu_lệnh_của_thủ_tục
Trong đó:
| Tên_thủ_tục | Tên của thủ tục cần tạo. Tên phải tuân theo qui tắc định danh và không được vượt quá 128 ký tự. |
| Danh_sách_tham_số | Các tham số của thủ tục được khai báo ngay sau tên thủ tục và nếu thủ tục có nhiều tham số thì các khai báo phân cách nhau bởi dấu phẩy. Khai báo của mỗi một tham số tối thiểu phải bao gồm hai phần:
– Tên tham số được bắt đầu bởi dấu @. – kiểu dữ liệu của tham số |
| RECOMPILE | Thông thường, thủ tục sẽ được phân tích, tối ưu và dịch sẵn ở lần gọi đầu tiên. Nếu tuỳ chọn WITH RECOMPILE được chỉ định, thủ tục sẽ được dịch lại mỗi khi được gọi. |
| ENCRYPTION | Thủ tục sẽ được mã hoá nếu tuỳ chọn WITH ENCRYPTION được chỉ định. Nếu thủ tục đã được mã hoá, ta không thể xem được nội dung của thủ tục. |
| Các_câu_lệnh_của_thủ_tục | Tập hợp các câu lệnh sử dụng trong nội dung thủ tục. Các câu lệnh này có thể đặt trong cặp từ khoá BEGIN…END hoặc có thể không. |
Giả sử ta cần thực hiện một chuỗi các thao tác như sau trên cơ sở dữ liệu:
- Bổ sung thêm môn học “CSDL Nâng Cao” có mã SOA2041 và số giờ học là 36 vào bảng MONHOC.
- Nhập điểm thi môn cơ sở dữ liệu cho :
– Cột sinh viên có mã sinh viên là: PH06234,
– Cột mã lớp học lớp có mã lớp PT13302 (tức là bổ sung thêm vào bảng KETQUA 1 bản ghi với cột MAMONHOC nhận giá trị SOA2041, cột MASV nhận giá trị PH06234 và học lớp có mã PT13302)
– Cột lần thi: 1
– và cột điểm là NULL).
Nếu thực hiện yêu cầu trên thông qua các câu lệnh SQL như thông thường, ta phải thực thi 2 câu Insert into lệnh như sau:
INSERT INTO MONHOC VALUES(‘SOA2041’,CSDL Nang Cao’,36)
INSERT INTO KETQUA (Masv, Mamh, Lanthi) VALUES (PH06234, SOA2041, 1)
Thay vì phải sử dụng hai câu lệnh như trên, ta có thể định nghĩa môt thủ tục lưu trữ với các tham số vào là @mamonhoc, @tenmonhoc, @sodvht, @masv và @malop như sau:
CREATE PROC sp_ThemMonHoc(
@mamonhoc NVARCHAR(10),
@tenmonhoc NVARCHAR(50),
@sodvht SMALLINT,
@masv NVARCHAR(10),
@malop NVARCHAR(10))
AS
BEGIN
INSERT INTO monhoc
VALUES(@mamonhoc,@tenmonhoc,@sodvht)
INSERT INTO KETQUA(Masv, Mamh, Lanthi)
VALUES(@masv, @mamonhoc,1)
END
Stored Procedure được ra trong cây thư mục của csdl sql Server:
- Thực thi Stored Procedure với các tham số:
2.1. Số lượng các đối số cũng như thứ tự của chúng phải phù hợp với số lượng và thứ tự của các tham số khi định nghĩa thủ tục.
tên_thủ_tục [danh_sách_các_đối_số]
sp_ThemMonHoc’SOA2041′,’CSDL NangCao’,36,’ph06234′,’PT13302′
2.1. Thứ tự của các đối số được truyền cho thủ tục có thể không cần phải tuân theo thứ tự của các tham số như khi định nghĩa khi tất cả các đối số được viết dưới dạng:
@tên_tham_số = giá_trị
Câu lệnh thực thi Stored Procedure với tham số truyền vào ở trên có thể viết như sau:
sp_ThemMonHoc @malop=’PT13302′,
@tenmonhoc=’CSDL NangCao’,
@mamonhoc=’SOA2041′,
@sodvht=36
@masv=’ph06234′
Sau khi gọi Stored Procedure và truyền tham số vào ta có kết quả dữ liệu trong bảng như sau:
Bảng MonHoc:
Bảng KetQua:
Bảng SinhVien:
- Sử dụng biến trong thủ tục:
Ngoài những tham số được truyền cho thủ tục, bên trong thủ tục còn có thể sử dụng các biến nhằm lưu giữ các giá trị tính toán được hoặc truy xuất được từ cơ sở dữ liệu. Các biến trong thủ tục được khai báo bằng từ khoá DECLARE theo cú pháp như sau:
DECLARE @tên_biến kiểu_dữ_liệu
Tên biến phải bắt đầu bởi ký tự @ và tuân theo qui tắc về định danh.
Ví dụ minh hoạ việc sử dụng biến trong Stored Procedure:
CREATE OR ALTER PROCEDURE sp_KtraQueQuan(
@masv NVARCHAR(10))
AS
BEGIN
DECLARE @tensv NVARCHAR(30)
DECLARE @QueQuan NVARCHAR(30)
SELECT @tensv=Hoten,
@QueQuan=Quequan
FROM SINHVIEN WHERE Masv=@masv
PRINT @tensv+’ Que Quan ‘+@QueQuan
IF @QueQuan=N’Hà Nội’
PRINT N’Sinh viên sống ở Hà nội’
ELSE
PRINT N’Sinh viên ngoại tỉnh’
END
Trong Procedure trên 2 biến @tensv và QueQuan được tạo ra để lưu trữ kết quả của câu lệnh Select: Hoten và Quequan được lấy ra từ câu lệnh Select sẽ lưu trong trong 2 biến này.
Tiếp theo 2 biến này được sử dụng để in ra thông tin và so sánh trong câu lệnh IF.
Câu lệnh thực thi Stored Procedure với mã sv được truyền vào là: PH06324
sp_KtraQueQuan ‘ph06234’
kết quả chúng ta nhận được như sau:
Giảng viên Hoàng Quốc Việt
Bộ môn Ứng dụng phần mềm
Trường Cao đẳng FPT Polytechnic cơ sở Hà Nội
