Lập Trình Với SQL - Biến, Cấu Trúc Lệnh, Thủ Tục và Hàm | Đại học Hoa Sen

lOMoAR cPSD| 47206417 CHƯƠNG4 LẬP TRÌNH VỚI SQL
4.1 Khai báo và sử dụng biến
Có 2 loại biến: Biến cục bộ và biến toàn cục.
Biến cục bộ là biến chỉ sử dụng trong đoạn chương trình khai
báo nó như Query Batch, Stored Procedure, Function, chứa giá
trị thuộc một kiểu nhất định.
Biến cục bộ được bắt đầu bằng 1 ký hiệu @. Khai báo:
DECLARE <@tên_biến> ,... Gán giá trị cho biến
SET @tên_biến = {giá_trị|biến|SELECT...}
Ví dụ: Câu lệnh sau đây khai báo một biến có tên @mssv, với
kiểu dữ liệu CHAR(4) và sử dụng lệnh SET để gán giá trị cho biến này. DECLARE @mssv CHAR(4) SET @mssv = ’2468’ 126 lOMoAR cPSD| 47206417
4.1 Khai báo và sử dụng biến
Biến toàn cục là biến được sử dụng bất kỳ đâu trong hệ thống.
Trong SQL biến toàn cục là các biến hệ thống do SQL Server cung
cấp và được tự cập nhật giá trị, người sử dụng không thể gán giá
trị trực tiếp cho các biến này.
Bản chất là 1 hàm (Function) và bắt đầu bằng ký tự @@.
Một số biến toàn cục trong SQL: Tên biến Ý nghĩa @@ERROR
Mã số lỗi của câu lệnh T-SQL
@@FETCH_STATUS Trạng thái truy cập Con trỏ: 0 nếu
trạng thái truy cập thành công, -1 nếu không thành công @@IDENTITY
Giá trị xác định (identity) được thêm vào @@ROWCOUNT
Số lượng dòng của kết quả câu lệnh SQL @@SERVERNAME
Tên của Server địa phương @@TRANSCOUNT
Số giao dịch đang được mở @@VERSION
Phiên bản SQL Server đang dùng @@CURSOR
Số lượng các dòng dữ liệu của Con trỏ
Ví dụ: Câu lệnh sau đây cho biết có bao nhiêu dòng bị ảnh hưởng bởi lệnh UPDATE UPDATE KetQua
SET diem = diem + 0.5 WHERE mahp = ’002’ 127 lOMoAR cPSD| 47206417 CH4. LẬP TRÌNH VỚI SQL
PRINT N’số dòng bị cập nhật là’ + STR(@@ROWCOUNT)
4.2 Một số cấu trúc lệnh cơ bản 4.2.1 Câu lệnh IF
Dùng kiểm tra điều kiện, nếu điều kiện đúng thì thực hiện khối
lệnh 1, ngược lại thực hiện khối lệnh 2 và kết thúc. ♡
Trong đó: Khối lệnh là một hoặc nhiều lệnh nằm trong cặp từ khóa BEGIN...END
Ví dụ: Câu lệnh sau dùng để kiểm tra tính chẵn lẻ của một số nguyên cho trước. DECLARE @x INT = 10; IF @x % 2 = 0 BEGIN
PRINT N’Số này là số chẵn.’ END ELSE BEGIN
PRINT N’Số này là số lẻ.’ 128 lOMoAR cPSD| 47206417 END;
4.2 Một số cấu trúc lệnh cơ bản 4.2.2 Câu lệnh CASE
Câu lệnh CASE dùng kiểm tra các điều kiện và trả về giá trị tương
ứng dựa trên kết quả của các điều kiện đó, gồm hai loại: CASE
đơn giản và CASE được mở rộng.
Loại 1: Kiểm tra điều kiện, nếu điều kiện 1 đúng thì nhận kết quả
1 và kết thúc CASE, ngược lại nếu điều kiện 2 đúng thì nhận kết
quả 2 và kết thúc CASE, ..., ngoài ra nhận kết quả khác và kết thúc CASE. CASE ♡
Loại 2: Nếu biểu thức là giá trị 1 thì nhận kết quả 1 và kết thúc
CASE, ngược lại nếu biểu thức là giá trị 2 thì nhận kết quả 2 và kết
thúc CASE, ... , ngoài ra thì nhận kết quả khác và kết thúc. 129 lOMoAR cPSD| 47206417 CH4. LẬP TRÌNH VỚI SQL ♡ Cú
pháp câu lệnh CASE mở rộng Ví dụ: Câu lệnh sau dùng để xếp
loại sinh viên dựa trên điểm trung bình tích lũy SELECT CASE
WHEN dtb >= 8.5 THEN ’A’
WHEN dtb >= 7.0 THEN ’B’
WHEN dtb >= 5.5 THEN ’C’
WHEN dtb >= 4.0 THEN ’D’ ELSE ’F’ END AS xeploai FROM sinhvien;
Ví dụ: Câu lệnh sau dùng để xác định tín hiệu giao thông dựa trên màu sắc. SELECT CASE color
WHEN ’Red’ THEN ’Stop’
WHEN ’Yellow’ THEN ’Caution’
WHEN ’Green’ THEN ’Go’ ELSE ’Unknown’ END AS Signal 130 lOMoAR cPSD| 47206417 FROM TrafficLights
4.2 Một số cấu trúc lệnh cơ bản 4.2.3 Câu lệnh WHILE
Dùng để kiểm tra điều kiện, nếu điều kiện đúng thì thực hiện khối
lệnh, tiếp tục kiểm tra điều kiện, cho đến khi nào điều kiện sai thì
thoát khỏi WHILE. Để vòng lặp không bị vô hạn thì trong nhóm
lệnh phải có lệnh thay đổi điều kiện và sau một số lần lặp thì điều
kiện sẽ sai và kết thúc WHILE. ♡
Dưới đây là một ví dụ đơn giản về cách sử dụng câu lệnh WHILE
trong SQL Server để đếm từ 1 đến 5. DECLARE @counter INT = 1 WHILE @counter <= 5 BEGIN PRINT @counter SET @counter = @counter + 1 END; 131 lOMoAR cPSD| 47206417 CH4. LẬP TRÌNH VỚI SQL 4.3 Thủ tục lưu trữ
Thủ tục là một đối tượng trong hệ quản trị CSDL bao gồm các câu
lệnh SQL, chúng được kết hợp lại với nhau thành một khối lệnh,
dùng để thực hiện một số công việc nào đó như cập nhật, thêm
mới, xóa, hiển thị, tính toán và có thể trả về các giá trị.
4.3.1 Tạo thủ tục lưu trữ
Thủ tục lưu trữ được tạo bởi câu lệnh CREATE PROC. ♡ Trong đó:
• tên_thủ_tục: Tên của thủ tục cần tạo.
• ds_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 cách nhau bởi dấu phẩy. 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ố Ví dụ: @mahp NVARCHAR(10)
• RECOMPILE: Thông thường, thủ tục sẽ được phân tích, 132 lOMoAR cPSD| 47206417 4.3 Thủ tục lưu trữ
tối ưu và dịch sẵn ở lần gọi đầu tiên. Nếu tùy chọn WITH
RECOMPILE được chọn thì thủ tục sẽ được dịch lại mỗi khi được gọi.
• ENCRYPTION: Nếu tùy chọn WITH ENCRYPTION được
chỉ định thì thủ tục sẽ được mã hoá. 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ừ khóa BEGIN...END hoặc có thể không.
Ví dụ: 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
1. Bổ sung thêm học phần Hệ quản trị CSDL có mã 023, số
đơn vị học trình là 3 và được mở ở học kỳ 2 vào bảng HocPhan.
2. Lên danh sách nhập điểm thi môn Hệ quản trị CSDL cho
các sinh viên học lớp có mã PM24 (tức là bổ sung thêm
vào bảng KetQua các bản ghi với cột mahp nhận giá trị
023, cột masv nhận giá trị lần lượt là mã các sinh viên
học lớp có mã PM24 và các cột điểm mặc định 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 hai câu lệnh như sau: INSERT INTO HocPhan
VALUES(’023’,N’Hệ quản trị CSDL’,3,2) 133 lOMoAR cPSD| 47206417 CH4. LẬP TRÌNH VỚI SQL và câu lệnh
INSERT INTO KetQua(mahp, masv) SELECT ’023’, masv FROM SinhVien WHERE MaLop = ’PM24’
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ữ như sau: CREATE PROC sp_dsDiem( @mahp NVARCHAR(10), @tenph NVARCHAR(50), @stc SMALLINT, @hocky TINYINT, @malop NVARCHAR(10)) AS BEGIN INSERT INTO HocPhan
VALUES(@mahp, @tenph, @stc, @hocky)
INSERT INTO KetQua(mahp, masv) SELECT @mahp, masv FROM SinhVien WHERE malop = @malop END
Khi thủ tục trên đã được tạo ra, ta có thể thực hiện được hai
yêu cầu đặt ra ở trên một cách đơn giản thông qua lòi gọi thủ tục: 134 lOMoAR cPSD| 47206417 4.3 Thủ tục lưu trữ
sp_dsDiem ’023’,N’Hệ quản trị CSDL’,3,2,’PM24’
4.3.2 Lời gọi thủ tục lưu trữ
Như đã thấy ở ví dụ ở trên, khi một thủ tục lưu trữ đã được
tạo ra, ta có thể yêu cầu hệ quản trị cơ sở dữ liệu thực thi thủ
tục bằng lời gọi thủ tục có dạng: tên_thủ_tục [danh_sách_các_đối_số]
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.
Trong trường hợp lời gọi thủ tục được thực hiện bên trong
một thủ tục khác, bên trong một trigger hay kết hợp với các
câu lệnh SQL khác, ta sử dụng cú pháp như sau:
EXECUTE tên_thủ_tục [danh_sách_các_đối_số]
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
thủ tục nếu tất cả các đối số được viết dưới dạng: @tên_tham_số = giá_trị
Ví dụ: Lời gọi thủ tục ở ví dụ trên có thể viết như sau
sp_dsDiem @malop = ’PM24’,
@tenhp = N’Hệ quản trị CSDL’, @mahp = ’023’, @stc = 3, @hocky = 2 135 lOMoAR cPSD| 47206417 CH4. LẬP TRÌNH VỚI SQL
4.3.3 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ừ khóa 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ụ dưới đây minh hoạ việc sử dụng biến trong thủ tục.
Ví dụ: Trong định nghĩa của thủ tục dưới đây sử dụng các biến
chứa các giá trị truy xuất được từ cơ sở dữ liệu. 136 lOMoAR cPSD| 47206417 4.3 Thủ tục lưu trữ CREATE PROC sp_SoSanhKhoaHoc( @malop1 NVARCHAR(10), @malop2 NVARCHAR(10)) AS BEGIN
DECLARE @tenlop1 NVARCHAR(30), @khoa1 INT
DECLARE @tenlop2 NVARCHAR(30), @khoa2 INT
SELECT @tenlop1 = tenlop, @khoa1 = khoa FROM Lop WHERE malop = @malop1
SELECT @tenlop2 = tenlop, @khoa2 = khoa FROM Lop WHERE malop = @malop2
PRINT @tenlop1 + N’ thuộc khóa ’ + str(@khoa1)
PRINT @tenlop2 + N’ thuộc khóa ’ + str(@khoa2) IF @khoa1 = @khoa2
PRINT N’Hai lớp cùng khóa’ ELSE
PRINT N’Hai lớp khác khóa’ END 137 lOMoAR cPSD| 47206417 CH4. LẬP TRÌNH VỚI SQL
4.3.4 Tham số với giá trị trả về
Trong các ví dụ trước, nếu đối số truyền cho thủ tục khi có lời
gọi đến thủ tục là biến, những thay đổi giá trị của biến trong
thủ tục sẽ không được giữ lại khi kết thúc quá trình thực hiện thủ tục.
Ví dụ: Xét câu lệnh sau đây
CREATE PROC sp_Cong(@a INT,@b INT,@c INT) AS SELECT @c = @a + @b
Nếu sau khi đã tạo thủ tục với câu lệnh trên, ta thực thi một
tập các câu lệnh như sau: DECLARE @tong INT = 0 EXECUTE sp_Cong 100,200,@tong SELECT @tong
Câu lệnh “SELECT @tong” cuối cùng trong loạt các câu lệnh
trên sẽ cho kết quả là: 0
Trong trường hợp cần phải giữ lại giá trị của đối số sau khi kết
thúc thủ tục, ta phải khai báo tham số của thủ tục theo cú pháp như sau:
@tên_tham_số kiểu_dữ_liệu OUTPUT hoặc
@tên_tham_số kiểu_dữ_liệu OUT 138 lOMoAR cPSD| 47206417 4.3 Thủ tục lưu trữ
và trong lời gọi thủ tục, sau đối số được truyền cho thủ tục, ta
cũng phải chỉ định thêm từ khóa OUTPUT (hoặc OUT).
Ví dụ: Ta định nghĩa lại thủ tục ở ví dụ trên như sau:
CREATE PROC sp_Cong(@a INT,@b INT,@c INT OUT) AS SELECT @c = @a + @b
và thực hiện lời gọi thủ tục như sau: DECLARE @tong INT = 0
EXECUTE sp_Cong 100, 200, @tong OUT SELECT @tong
thì câu lệnh “SELECT @tong” sẽ cho kết quả là: 300
4.3.5 Tham số với giá trị mặc định
Các tham số được khai báo trong thủ tục có thể nhận các giá
trị mặc định. Giá trị mặc định sẽ được gán cho tham số trong
trường hợp không truyền đối số cho tham số khi có lời gọi đến thủ tục.
Cú pháp khai báo tham số với giá trị mặc định:
@tên_tham_số kiểu_dữ_liệu = giá_trị_mặc_định
Ví dụ: Trong câu lệnh dưới đây 139 lOMoAR cPSD| 47206417 CH4. LẬP TRÌNH VỚI SQL CREATE PROC sp_hienthi_hoten( @tenlop NVARCHAR(30) = NULL,
@noisinh NVARCHAR(100) = ’HCM’) AS BEGIN IF @tenlop IS NULL SELECT hodem, ten FROM SinhVien JOIN Lop ON SinhVien.malop = Lop.malop WHERE noisinh = @noisinh ELSE SELECT hodem, ten FROM SinhVien JOIN Lop ON SinhVien.malop = Lop.malop WHERE noisinh = @noisinh AND tenlop = @tenlop END
Với thủ tục sp_hienthi_hoten được định nghĩa như trên, ta có
thể thực hiện các lời gọi với các mục đích khác nhau như sau:
• Cho biết họ tên của các sinh viên sinh tại HCM: sp_hienthi_hoten
• Cho biết họ tên của các sinh viên học lớp Toán tin sinh
tại HCM: sp_hienthi_hoten @tenlop = ’Toán tin’
• Cho biết họ tên của các sinh viên sinh tại Long An:
sp_hienthi_hoten @noisinh = N’Long An’ 140 lOMoAR cPSD| 47206417 4.3 Thủ tục lưu trữ
• Cho biết họ tên của các sinh viên học lớp Toán tin sinh tại Long An:
sp_hienthi_hoten @tenlop = ’Toán tin’, @noisinh = ’Long An’
4.3.6 Sửa đổi thủ tục
Khi một thủ tục đã được tạo ra, ta có thể tiến hành định nghĩa
lại thủ tục đó bằng câu lệnh ALTER PROC có cú pháp như sau:
ALTER PROC tên_thủ_tục [(danh_sách_tham_số)]
[WITH RECOMPILE|ENCRYPTION] AS
Các_câu_lệnh_của_thủ_tục
Việc sửa đổi lại một thủ tục đã có không làm thay đổi đến các
quyền đã cấp phát trên thủ tục cũng như không tác động đến
các thủ tục khác hay trigger phụ thuộc vào thủ tục này. 141 lOMoAR cPSD| 47206417 CH4. LẬP TRÌNH VỚI SQL 4.3.7 Xóa thủ tục
Để xóa một thủ tục đã có, ta sử dụng câu lệnh DROP PROC với cú pháp như sau: DROP PROC tên_thủ_tục
Ví dụ: Để xóa thủ tục sp_hienthi_hoten ta dùng lệnh: DROP PROC sp_hienthi_hoten
Khi xóa một thủ tục, tất cả các quyền đã cấp cho người sử dụng
trên thủ tục đó cũng đồng thời bị xóa bỏ. Do đó, nếu tạo lại
thủ tục, ta phải tiến hành cấp phát lại các quyền trên thủ tục đó. 4.4 Hàm
Hàm là đối tượng cơ sở dữ liệu tương tự như thủ tục. Điểm
khác biệt giữa hàm và thủ tục là hàm trả về một giá trị thông
qua tên hàm còn thủ tục thì không. Điều này cho phép ta sử
dụng hàm như là một thành phần của một biểu thức (chẳng
hạn trong danh sách chọn của câu lệnh SELECT). 142 lOMoAR cPSD| 47206417 4.4 Hàm
Ngoài những hàm do hệ quản trị cơ sở dữ liệu cung cấp sẵn,
người sử dụng có thể định nghĩa thêm các hàm nhằm phục vụ
cho mục đích riêng của mình. 4.4.1 Tạo hàm
Hàm được định nghĩa thông qua câu lệnh CREATE FUNCTION. ♡
Hàm do người dùng tạo ra gồm 3 dạng:
• Scalar_valued Function: Giá trị trả về là kiểu dữ liệu cơ
sở (int, varchar, float, datetime,...).
• Table_valued Function: Giá trị trả về là một bảng có
được từ một câu truy vấn.
• Aggregate Function: Giá trị trả về là một bảng mà dữ liệu
có được nhờ tích lũy dần sau một chuỗi thao tác xử lý và insert.
Ví dụ: Câu lệnh dưới đây định nghĩa hàm tính ngày trong tuần
(thứ trong tuần) của một giá trị kiểu ngày 143 lOMoAR cPSD| 47206417 CH4. LẬP TRÌNH VỚI SQL
CREATE FUNCTION fc_thu(@ngay DATETIME) RETURNS NVARCHAR(10) AS BEGIN DECLARE @st NVARCHAR(10)
SELECT @st = CASE DATEPART(DW,@ngay)
WHEN 1 THEN N’Chủ nhật’ WHEN 2 THEN N’Thứ hai’ WHEN 3 THEN N’Thứ ba’ WHEN 4 THEN N’Thứ tư’ WHEN 5 THEN N’Thứ năm’ WHEN 6 THEN N’Thứ sáu’ ELSE N’Thứ bảy’ END RETURN @st END
Ví dụ: Câu lệnh SELECT dưới đây sử dụng hàm đã được định nghĩa ở ví dụ trên
SELECT masv,hodem,ten,dbo.fc_thu(ngaysinh),ngaysinh FROM
SinhVien WHERE malop = ’PM22’ có kết quả là: masv hodem ten ngaysinh 2402 Trần Văn Đại Thứ hai 2002-09- 23 144 lOMoAR cPSD| 47206417 4.4 Hàm 2403 Lê Mạnh Thịnh Thứ năm 2004-11- 25 2412 Nguyễn Hoài Tiên Thứ năm 2000-09- 28 2466 Bùi Gia Sương Thứ tư 2002-08- 21 2477 Võ Văn Hồng Thứ năm 2004-05- 13
4.4.2 Hàm trả về dữ liệu kiểu bảng
Ta đã biết được chức năng cũng như sự tiện lợi của việc sử
dụng các khung nhìn trong cơ sở dữ liệu. Tuy nhiên, nếu cần
phải sử dụng các tham số trong khung nhìn (chẳng hạn các
tham số trong mệnh đề WHERE của câu lệnh SELECT) thì ta lại
không thể thực hiện được. Điều này phần nào đó làm giảm
tính linh hoạt trong việc sử dụng khung nhìn. Ví dụ: Xét khung
nhìn được định nghĩa như sau CREATE VIEW vw_sinhvien_k22 AS
SELECT masv, hodem, ten, ngaysinh FROM SinhVien JOIN Lop ON SinhVien.malop = Lop.malop WHERE khoa = 22
với khung nhìn trên, thông qua câu lệnh: SELECT * FROM vw_sinhvien_k22 145