Tóm Tắt
Mô tả
- 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…
Cú pháp
--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
Ví dụ
- 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
Stored Procedure nâng cao
Stored Procedure nâng cao
Phân loại
Thủ tục nội tại của mạng lưới hệ thống ( built-in )
--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
Thủ tục nội tại do người lập trình tạo ra
- 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
Tham số
Tham số nguồn vào ( INPUT )
- Ví dụ 1
--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
- Ví dụ 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
Tham số đầu ra (OUTPUT)
Bạn đang đọc: CREATE PROC – Lệnh tạo Stored Procedure
- Ví dụ 1
--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)
- Ví dụ 2
--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
Tham số có giá trị mặc định
--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
Tham số kiểu đa trị
- 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
- Kiểu XML
--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 = '' 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 = '' 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 = '' 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) = '' 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)
- Kiểu CURSOR
--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 Ma_sinh_vien=@Ma_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...
- Kiểu CHUỖI
Khai báo tham số kiểu chuỗi trong thủ tục
Chuỗ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 ”
Các dạng thủ tục thường gặp
- Tính toán và in ra kết quả tính toán
Ví dụ: viết thủ tục giải phương trình bậc nhất: ax + b = 0
Thuật giải:
– Nhập a và b
– Xét các trường hợp của nghiệm
– In kết quả
- Tính toán và trả ra kết quả tính toán
Ví dụ: viết thủ tục kiểm tra một số nguyên N (N>1) có phải là số nguyên tố hay là không?
Thuật giải:
– Nhập số N
– Kiểm tra N có > 1?
– Xét N có là số nguyên tố hay là không?
– Nếu N là số nguyên tố thì trả ra 1. Ngược lại thì trả ra 0.
- Đọc và hiển thị dữ liệu
Ví dụ: viết thủ tục hiển thị các đơn ĐH có đặt vật tư theo mã vật tư truyền vào. Thông tin hiển thị gồm Số ĐH, ngày ĐH, SL đặt, Mã NCC, Tên NCC.
Thủ tục thực hiện câu truy vấn để hiển thị dữ liệu.
- Thêm/ Sửa/ Xóa dữ liệu của bảng
Ví dụ: viết thủ tục thêm mới một đơn ĐH vào bảng DONDH
Thủ tục thực hiện lệnh INSERT để thêm mới.
Các yếu tố khác
- Xem thông tin thủ tục
EXEC sp_help Tên_thủ_tục EXEC sp_helptext Tên_thủ_tục
- Mã hóa thủ tục
CREATE PROC Tên_thủ_tục Khai_báo_tham_số Kiểu_dữ_liệu WITH ENCRYPTION AS Lệnh A Lệnh B ... GO
- Biên dịch
--Có ba cách để biên dịch thủ tục --1. CREATE PROC Tên_thủ_tục Khai_báo_tham_số WITH RECOMPILE AS... --2. EXEC Tên_thủ_tục WITH RECOMPILE --3. EXEC sp_recompile Tên_thủ_tục
- Sử dụng bảng tạm trong thủ tục
Để lưu tạm các dữ liệu quan hệ, có ba cách sau đây:
– DECLARE @bang TABLE(id INT,…)
– DECLARE @bang kieu_bang CREATE TYPE kieu_bang AS TABLE(id INT,…)
– Sử dụng bảng tạm.
- Lệnh RETURN
Thủ tục sẽ kết thúc sau khi đã thực hiện xong câu lệnh cuối cùng
Lệnh RETURN làm kết thúc thủ tục
Lệnh RETURN :
– Kết thúc thủ tục
– Thủ tục trả về một giá trị số nguyên cho chương trình gọi thực hiện
Xem thêm : lệnh sửa Stored Procedure ALTER PROC, lệnh xóa Stored Procedure DROP PROC .
Source: https://final-blade.com
Category : Kiến thức Internet