CREATE PROC – Lệnh tạo Stored Procedure

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

Phân loại

Thủ tục nội tại của 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ố đầu 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)

  • 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 = '<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)
  • 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 vấn đề 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 <Số nguyên>:
– 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.

Leave a Reply

Your email address will not be published. Required fields are marked *