T-SQL Stored Procedured
T-SQL Stored Procedured

I. Khái niệm về thủ tục nội tại

  • Tạo ra những xử lý thường dùng bên trong ứng dụng
  • Chia nhỏ xử lý phức tạp thành các xử lý nhỏ dần, đơn giản hóa xử lý phức tạp
T-SQL Stored Procedured
T-SQL Stored Procedured

1. Thủ tục nội tại là gì?

  • Chứa các dòng lệnh, các biến và các cấu trúc điều khiển bên trong T-SQL
  • Tất cả các nội dung của thủ tục nội tại được lưu trữ tại CSDL của Sql Server
T-SQL Stored Procedured

2. Đặc trưng của thủ tục nội tại

Thủ tục nội tại cũng giống các thủ tục của ngôn ngữ lập trình khác

  • Tên thủ tục nội tại
  • Tham số đầu vào và tham số đầu ra
  • Bên trong thủ tục cũng được phép gọi thực thi thủ tục nội tại khác
  • Có giá trị trả về
T-SQL Stored Procedured

3. Lợi ích khi sử dụng thủ tục nội tại

  • Tái sử dụng
  • Xử lý nhanh hơn so với viết lệnh Sql thông thường
  • Có thể phân quyền trên thủ tục nội tại
  • Thủ tục nội tại khắc phục nhược điểm của view vì có thể truyền tham số
T-SQL Stored Procedured
T-SQL Stored Procedured

1. Tạo thủ tục nội tại đơn giản

Cú pháp
CREATE PROCEDURE <Procedure_name>
AS
[BEGIN]
	<SQL Statement>
[END]
GO
T-SQL Stored Procedured

1. Tạo thủ tục nội tại đơn giản

Ví dụ

Lệnh sau là ví dụ lấy ra tất cả các danh sách nhân viên

CREATE PROCEDURE GetEmployees
AS
	SELECT * FROM Employee;
GO
T-SQL Stored Procedured

2. Thực thi một thụ tục nội tại

Cú pháp
EXECUTE <Procedure_name>

Lệnh sau là ví dụ thực thi một thủ tục nội tại GetEmployees

EXECUTE GetEmployees;
GO
T-SQL Stored Procedured

3. Thay đổi nội dung một thủ tục nội tại

Cú pháp
ALTER PROCEDURE <Procedure_name>
AS
[BEGIN]
	<SQL Statement>
[END]
T-SQL Stored Procedured

3. Thay đổi nội dung một thủ tục nội tại

Ví dụ

ALTER PROCEDURE GetEmployees
AS
	SELECT Id, Name FROM Employee;
GO
T-SQL Stored Procedured

4. Hủy bỏ thủ tục nội tại

Cú pháp
DROP PROCEDURE <Procedure_name>

Lệnh sau là ví dụ hủy bỏ thủ tục nội tại GetEmployees

DROP PROCEDURE GetEmployees;
GO
T-SQL Stored Procedured

5. Kết hợp tạo mới và thay thế thủ tục nội tại

Cú pháp
IF OBJECT_ID('<Procedure_name>', 'p') IS NOT NULL
	DROP PROCEDURE <Procedure_name>
GO
CREATE PROCEDURE <Procedure_name>
AS
[BEGIN]
	<SQL Statement>
[END]
T-SQL Stored Procedured

5. Kết hợp tạo mới và thay thế thủ tục nội tại

Ví dụ
IF OBJECT_ID('GetEmployees', 'p') IS NOT NULL
	DROP PROCEDURE GetEmployees;
GO
CREATE PROCEDURE GetEmployees
AS
	SELECT * FROM Employee;
GO
T-SQL Stored Procedured
T-SQL Stored Procedured

1. Tham số đầu vào

Cú pháp
CREATE PROCEDURE <Procedure_name>(
	<parameter_name_list>
)
AS
[BEGIN]
	<SQL Statement>
[END]
T-SQL Stored Procedured

1. Tham số đầu vào

Ví dụ 1
CREATE PROCEDURE GetEmployeeByDepartment(@id INT)
AS
	SELECT * FROM Employee WHERE Id = @id;
GO
Thực thi
EXECUTE GetEmployeeByDepartment @id = 1;
T-SQL Stored Procedured

1. Tham số đầu vào

Ví dụ 2
CREATE PROCEDURE AddEmployee(
	@name NVARCHAR(128),
	@address NVARCHAR(128),
	@salary INT,
	@deptId INT)
AS
	INSERT INTO Employee (Name, Address, Salary, DeptId)
	VALUES (@name, @address, @salary, @deptId)
GO
T-SQL Stored Procedured

1. Tham số đầu vào

Thực thi ví dụ 2
EXECUTE AddEmployee @name = 'Lucky luck', @address = 'Boston', 
	@salary = 9000, @deptId = 1;
GO
T-SQL Stored Procedured

2. Giá trị mặc định tham số đầu vào

Ví dụ
CREATE PROCEDURE AddEmployee(
	@name NVARCHAR(128),
	@address NVARCHAR(128) = NULL,
	@salary INT,
	@deptId INT)
AS
	INSERT INTO Employee (Name, Address, Salary, DeptId)
	VALUES (@name, @address, @salary, @deptId)
GO
T-SQL Stored Procedured

2. Giá trị mặc định tham số đầu vào

Thực thi
EXECUTE AddEmployee  @name = 'Mr Bean', @address = 'Boston', 
	@salary = 9000, @deptId = 1;
GO
EXECUTE AddEmployee @name = 'Amstrong', @salary = 9000, @deptId = 1;
GO
T-SQL Stored Procedured

3. Giá trị trả về

Ví dụ
CREATE PROCEDURE AddDepartment( @name NVARCHAR(128) )
AS
BEGIN
	INSERT INTO Department (Name) VALUES (@name);
	RETURN SCOPE_IDNTITY();
END
T-SQL Stored Procedured

3. Giá trị trả về

Thực thi thủ tục có giá trị trả về
DECLARE @id INT;
EXECUTE @id = AddDepartment @name = 'Finace';
PRINT @id;
GO
T-SQL Stored Procedured

4. Tham số đầu ra (OUT, OUTPUT)

CREATE PROCEDURE AddDepartment(@id INT OUTPUT, @name NVARCHAR(128) )
AS
BEGIN
	INSERT INTO Department (Name) VALUES (@name);
	SET @id =  SCOPE_IDNTITY();
END
T-SQL Stored Procedured

4. Tham số đầu ra (OUT, OUTPUT)

Thực thi thủ tục có tham số đầu ra

DECLARE @id INT;
EXECUTE AddDepartment @id OUTPUT, @name = 'Development';
PRINT @id;
T-SQL Stored Procedured

5. Tham số đầu ra và đầu ra

CREATE PROCEDURE EditDepartment(@id INT, @name NVARCHAR(128) OUTPUT )
AS
BEGIN
	SET @name =  UPPER(@name);
	UPDATE  Department SET Name =  @name WHERE Id = @id;
END
T-SQL Stored Procedured

5. Tham số đầu ra và đầu ra

Thực thi thủ tục có tham số đầu vào và đầu ra

DECLARE @n NVARCHAR(128) = 'development';
EXECUTE EditDepartment @id = 5, @name = @n OUTPUT;
PRINT @n