Viết store với tham số truyền vào là MaSV hãy cho biết tổng số môn học mã sinh viên đã thi lần 1

  • Lệnh CREATE PROC (hoặc CREATE PROCEDURE) dùng để tạo Stored Procedure (thủ tục nội tại) trong CSDL.
  • Thủ tục nội tại là một chương trình con (Sub Program).
  • Thủ tục nội tại dùng để thực hiện một xử lý. Ví dụ xử lý tính lương các nhân viên, xử lý thêm mới nhân viên, xử lý kết chuyển tồn kho vật tư, xử lý giải phương trình bậc hai…

--Tạo thủ tục CREATE PROC Tên_thủ_tục Khai_báo_tham_số Kiểu_dữ_liệu AS Lệnh A Lệnh B ... GO --Hoặc CREATE PROCEDURE Tên_thủ_tục Khai_báo_tham_số Kiểu_dữ_liệu AS Lệnh A Lệnh B ... GO

--Gọi thực hiện thủ tục EXEC Tên_thủ_tục Tham_số_truyền_vào --Hoặc EXECUTE Tên_thủ_tục Tham_số_truyền_vào

  • Xử lý tăng lương 10% cho các nhân viên (thủ tục không có tham số)

--Tạo thủ tục CREATE PROC Tang_luong AS UPDATE EMPLOYEES SET SALARY = SALARY * 1.1 GO

--Gọi thực hiện thủ tục EXEC Tang_luong

  • Xử lý tăng lương x% cho các nhân viên (thủ tục có tham số)

--Tạo thủ tục CREATE PROC Tang_luong @Phan_tram int AS DECLARE @Ty_le decimal(3,1) = 1 + @Phan_tram / 100 UPDATE EMPLOYEES SET SALARY = SALARY * @Ty_le GO

--Gọi thực hiện thủ tục EXEC Tang_luong @Phan_tram = 10

--Gọi thực hiện thủ tục: Tạo login USE master GO EXEC sp_addlogin @loginame = 'tom', @passwd = 'tom123' GO

--Gọi thực hiện thủ tục: Tạo user USE HumanResource GO EXEC sp_adduser @loginame = 'tom', @name_in_db = 'tom' GO

  • Ví dụ tạo thủ tục tính tổng hai số nguyên và in ra kết quả tổng

--Tạo thủ tục CREATE PROC Tong @a int, @b int AS --Khai báo biến DECLARE @Tong int --Tính tổng SET @Tong = @a + @b --In kết quả tổng PRINT CONCAT(N'Tổng của ', @a, N' và ', @b, N' là: ', @Tong) GO

--Gọi thực hiện thủ tục EXEC Tong 1, 2

--Tạo thủ tục CREATE PROC Tong @a int, @b int AS --Khai báo biến DECLARE @Tong int --Tính tổng SET @Tong = @a + @b --In kết quả PRINT N'Tổng là: ' + STR(@Tong) GO

--Gọi thực hiện thủ tục EXEC Tong 1, 2

--Tạo thủ tục CREATE PROC Dem_nhan_vien @depid int AS --Bỏ message (x row(s) affected) SET NOCOUNT ON --Khai báo biến DECLARE @Dem int --Đếm số nhân viên của phòng SELECT @Dem = COUNT(*) FROM EMPLOYEES WHERE Department_id = @depid --In kết quả đếm được PRINT N'Tổng số nhân viên: ' + STR(@Dem) GO

--Gọi thực hiện thủ tục EXEC Dem_nhan_vien 80

--Tạo thủ tục CREATE PROC Tong @a int, @b int, @Tong int OUT AS --Tính tổng SET @Tong = @a + @b GO

-- Gọi thực hiện thủ tục DECLARE @Tong int EXEC Tong 1, 2, @Tong OUT PRINT N'Tổng là: ' + STR(@Tong)

--Tạo thủ tục CREATE PROC Dem_nhan_vien @depid int, @Dem int OUT AS --Bỏ message (x row(s) affected) SET NOCOUNT ON --Đếm số nhân viên của phòng SELECT @Dem = COUNT(*) FROM EMPLOYEES WHERE department_id = @depid GO

--Gọi thực hiện thủ tục DECLARE @Dem int EXEC Dem_nhan_vien 80, @Dem OUT PRINT N'Tổng là: ' + STR(@Dem)

  • Ví dụ 3: Đọc và in ra Họ tên của sinh viên C01

--Tạo thủ tục CREATE PROC In_thong_tin_SV @Ma_sinh_vien char(3) AS --Bỏ message (x row(s) affected) SET NOCOUNT ON DECLARE @Ho_ten nvarchar(50) -- SELECT @Ho_ten = Ho_sinh_vien + ' ' + Ten_sinh_vien FROM SINH_VIEN WHERE Ma_sinh_vien = @Ma_sinh_vien -- PRINT N'Họ tên: ' + @Ho_ten GO

--Gọi thực hiện thủ tục EXEC In_thong_tin_SV C01 --Hoặc EXEC In_thong_tin_SV @Ma_sinh_vien = 'C01' --Hoặc DECLARE @Ma_sinh_vien char(3) = 'C01' EXEC In_thong_tin_SV @Ma_sinh_vien

  • Ví dụ 4: Đọc và trả ra Họ, tên của sinh viên C01

--Tạo thủ tục CREATE PROC Doc_thong_tin_SV @Ma_sinh_vien char(3), @Ho_sinh_vien nvarchar(30) OUT, @Ten_sinh_vien nvarchar(20) OUT AS --Bỏ message (x row(s) affected) SET NOCOUNT ON -- SELECT @Ho_sinh_vien = Ho_sinh_vien, @Ten_sinh_vien = Ten_sinh_vien FROM SINH_VIEN WHERE Ma_sinh_vien = @Ma_sinh_vien GO

--Gọi thực hiện thủ tục DECLARE @Ma_sinh_vien char(3) = 'C01' DECLARE @Ho_sinh_vien nvarchar(30), @Ten_sinh_vien nvarchar(20) EXEC Doc_thong_tin_SV @Ma_sinh_vien, @Ho_sinh_vien OUT, @Ten_sinh_vien OUT PRINT N'Họ tên: ' + @Ho_sinh_vien + ' ' + @Ten_sinh_vien

--Tạo thủ tục CREATE PROC In_so_nhan_vien @depid int = NULL AS DECLARE @Dem int --Đếm số nhân viên của phòng SELECT @Dem = COUNT(*) FROM EMPLOYEES WHERE Department_id = @depid OR @depid IS NULL -- PRINT N'Số nhân viên: ' + STR(@Dem) GO

--Gọi thực hiện thủ tục --1. In số nhân viên phòng 80 EXEC In_so_nhan_vien @depid = 80 --2. In số nhân viên tất cả các phòng EXEC In_so_nhan_vien

  • Kiểu TABLE do người lập trình định nghĩa

--Định nghĩa kiểu TABLE CREATE TYPE Kieu_NCC AS TABLE ( Ma char(3) PRIMARY KEY, Ten nvarchar(100), Dia_chi nvarchar(200) ) GO

--Biến kiểu TABLE DECLARE @Danh_sach Kieu_NCC -- INSERT INTO @Danh_sach VALUES('T05', N'Công ty Hừng Sáng', N'246 An Dương Vương') INSERT INTO @Danh_sach VALUES('T06', N'Công ty Tầm Cao', N'123 Nguyễn Trãi') INSERT INTO @Danh_sach VALUES('T07', N'Công ty Hoàng Hôn', N'345 Bình Thới') -- SELECT * FROM @Danh_sach

--Tham số kiểu TABLE CREATE PROC Them_NCC @Danh_sach Kieu_NCC READONLY AS SET NOCOUNT ON -- INSERT INTO NHACC(MaNhaCC, TenNhaCC, DiaChi) SELECT Ma, Ten, Dia_chi FROM @Danh_sach GO

--Tham số kiểu TABLE DECLARE @Danh_sach Kieu_NCC -- INSERT INTO @Danh_sach VALUES('T05', N'Công ty Hừng Sáng', N'246 An Dương Vương') INSERT INTO @Danh_sach VALUES('T06', N'Công ty Tầm Cao', N'123 Nguyễn Trãi') -- EXEC Them_NCC @Danh_sach GO

--Biến kiểu XML --Khai báo biến kiểu XML DECLARE @Danh_sach XML, @Chuoi NVARCHAR(1000) --Gán dữ liệu XML cho biến SET @Chuoi = '<DANH_SACH><SO Gia_tri="12"/>' + '<SO Gia_tri="34"/>' + '<SO Gia_tri="21"/>' + '</DANH_SACH>' SET @Danh_sach = CAST(@Chuoi AS XML) --In PRINT CAST(@Danh_sach AS NVARCHAR(1000)) SELECT @Danh_sach

--Biến kiểu XML --Khai báo biến kiểu XML DECLARE @Danh_sach XML, @Chuoi NVARCHAR(1000) --Gán dữ liệu XML cho biến SET @Chuoi = '<DANH_SACH><SO Gia_tri="12"/>' + '<SO Gia_tri="34"/>' + '<SO Gia_tri="21"/>' + '</DANH_SACH>' SET @Danh_sach = CAST(@Chuoi AS XML) --Truy vấn SELECT So.value('@Gia_tri', 'integer') Gia_tri FROM @Danh_sach.nodes('/DANH_SACH/SO') AS Danh_sach (So)

--Tham số kiểu XML CREATE PROC spud_Tong @Danh_sach XML, @Tong INT OUT AS SET NOCOUNT ON --Tính tổng SELECT @Tong = SUM(So.value('@Gia_tri', 'integer')) FROM @Danh_sach.nodes('/DANH_SACH/SO') AS Danh_sach (So) GO

--Tham số kiểu XML DECLARE @Danh_sach XML, @Chuoi NVARCHAR(1000) --Gán dữ liệu XML cho biến SET @Chuoi = '<DANH_SACH><SO Gia_tri="12"/>' + '<SO Gia_tri="34"/>' + '<SO Gia_tri="21"/>' + '</DANH_SACH>' SET @Danh_sach = CAST(@Chuoi AS XML) --Gọi thực hiện thủ tục DECLARE @Tong INT EXEC spud_Tong @Danh_sach, @Tong OUT PRINT N'Tổng là: ' + STR(@Tong)

--Tham số kiểu XML ALTER PROC spud_Tong @Danh_sach XML, @Ket_qua XML OUT AS DECLARE @Tong INT --Tính tổng SELECT @Tong = SUM(So.value('@Gia_tri', 'integer')) FROM @Danh_sach.nodes('/DANH_SACH/SO') AS Danh_sach (So) --Trả ra kết quả DECLARE @Chuoi NVARCHAR(1000) = '<KET_QUA Tong="{0}" />' SET @Chuoi = REPLACE(@Chuoi,'{0}',@Tong) SET @Ket_qua = CAST(@Chuoi AS XML) GO

--Tham số kiểu XML DECLARE @Danh_sach XML, @Chuoi NVARCHAR(1000) --Gán dữ liệu XML cho biến ... --Gọi thực hiện thủ tục DECLARE @Tong INT, @Ket_qua XML EXEC spud_Tong @Danh_sach, @Ket_qua OUT --In kết quả tổng SELECT @Tong = Dong.value('@Tong', 'integer') FROM @Ket_qua.nodes('/KET_QUA') AS Ket_qua (Dong) PRINT N'Tổng là: ' + STR(@Tong)

--Khai báo biến kiểu CURSOR DECLARE Tên_biến CURSOR --Gán giá trị cho biến SET Biến = CURSOR FOR Câu_lệnh_SELECT

--Ví dụ --Khai báo biến DECLARE @cur CURSOR --Gán giá trị SET @cur = CURSOR FOR SELECT Ho_sinh_vien + ' ' + Ten_sinh_vien AS Ho_ten, Ngay_sinh, Gioi_tinh FROM SINH_VIEN WHERE Ma_sinh_vien='C01'

--Khai báo tham số OUT kiểu CURSOR trong thủ tục Tên_tham_số CURSOR VARYING OUTPUT --Xử lý trong thủ tục 1. Gán giá trị cho tham số OUT kiểu CURSOR 2. Mở CURSOR

CREATE PROC Cursor_Test @Ma_sinh_vien CHAR(3), @cur CURSOR VARYING OUTPUT AS --Gán SET @cur = CURSOR FOR SELECT Ho_sinh_vien + ' ' + Ten_sinh_vien AS Ho_ten, Ngay_sinh, Gioi_tinh FROM SINH_VIEN WHERE [email protected]_sinh_vien --Mở OPEN @cur GO

--Khai báo biến kiểu CURSOR DECLARE @cur CURSOR --Gọi thực hiện thủ tục, truyền vào biến @cur và nhận lại một CURSOR EXEC Cursor_Test 'C01', @cur OUT --Tiếp tục...

Khai báo tham số kiểu chuỗi trong thủ tụcChuỗi là danh sách các giá trị và có ký hiệu phân cách

Ví dụ: “C01 C02 C03 C04 C05 ”