T-SQL筆記2_Stored Procedure觀念_為何要使用它_如何建立

Stored Procedure (預存程序)

是一個 T-SQL 陳述式之集合
有點類似其他語言中的  函數(function) 或是 方法(method , subroutine)
可以透過呼叫這些預存程序之名稱 來執行對應對應陳述式集合(命令集)


Stored Procedure優點:
1.為資料庫物件(DataBase Objects) ，存置於DB中
2.將方法功能進行封裝
3.可以確保功能是能夠跨應用程式做調用呼叫的
4.比起常規查詢來的更有效率!!!! 試想假若商業邏輯因過於複雜使SQL陳述過長又龐大

，這時若仍直接ado.net傳送一大串SQL就會導致網路傳輸量過多也會較耗費時間。

但若改為SP則用戶端(client)只會傳遞SP Name給SQL Server就可節省傳輸量。

為何要學習  Stored Procedure 這項技術??

在大型企業公司中 多半系統是伴隨著DB(可視為系統命脈)
做連動性交互，也因此時常會遇到
DB之間可能存有同一時間在多個大系統做類似DB操作的程式運行
功能可能一樣 ,也可能不一樣
有的甚至是像打棒球或是接力賽交接棒、傳球之概念般
可能做DB操作回傳之結果
會從A系統做完一些處裡再傳給B系統再跑到C系統去.....
更可能反敘倒回來


所以會有不只一個應用程式(系統)
去對 DB 資料表做搜尋、更新.....等等

由上述示意圖可以看到黃色方框
可以理解為  每個不同部門或者單位各自所掌管之系統
可能都有存在一段程式 是 對DB中做更新的操作的話

假設四個不同應用系統都在Local端 所運行的這區塊程式功能都是做一樣的事情時
當一有功能調整
此時你會有需要對四個地方去做同樣調整
共需要做四次更改

這是違背開發上降低重複動作
設計觀念的


預存程序之優點就在於可以集中控管做功能代碼修改之存取處裡
功能版本統一更新(一次到位)
Stored Procedure 就是一種被各個不同應用程式呼叫的一個T-SQL執行命令集

建立  Stored Procedure

假設我們目前要做的一串命令集如下
(會有極高重複使用可能)

1 2 3 4

SELECT ProductID , SUM(OrderQty) Orders, AVG(UnitPrice) 'Avg Price', SUM(LineTotal) Total FROM Sales.SalesOrderDetail WHERE ProductID = 777 GROUP BY ProductID

此時命令已順利完成
已建立了  ProdOrders 的 預存程式

執行  Stored Procedure

寫法1. EXEC  預存程序名

寫法2.直接Call 名稱

預存程序名

修改  Stored Procedure

假設今天要更改 變為其他產品時
我們就將 CREATE 改成  ALTER 之後更改再執行即可!!

可是你會不會覺得很怪????

剛剛產品只鎖定為777

後來又只鎖定為 776

這個設計並不彈性!!!!!!!!

預存程序  是否可以傳入參數呢???

如此一來我就可以更為彈性透過變數傳入去指定對應更新

一些建立sp的細節

1.stored procedure命名勿使用 sp_ 開頭

因為使用sp_開頭的預設都會先至SQL Server的master database搜尋完後，

才會搜尋現階段連線的database，不僅耗時更容易有機會出錯(比方跟master db存有的sp撞名)

SP範例操作

CREATE TABLE Employee
(EmpId INT PRIMARY KEY,
Name VARCHAR(50),
Salary INT,
Address VARCHAR(100))

SELECT * FROM Employee

INSERT INTO Employee VALUES(101, 'King Kocchar', 12000, 'Delhi')
INSERT INTO Employee VALUES(102, 'John Smith', 8000, 'Mumbai')
INSERT INTO Employee VALUES(103, 'Sarah Bowling', 32000, 'Delhi')
INSERT INTO Employee VALUES(104, 'James Bond', 22000, 'Chennai')

-- Creating Stored Procedure
CREATE PROCEDURE usp_GetAllEmployees
AS
	SELECT * FROM Employee
GO;

-- Executing Procedure
EXECUTE usp_GetAllEmployees
EXEC usp_GetAllEmployees
usp_GetAllEmployees

-- Creating Stored Procedure with IN Parameter
CREATE PROCEDURE usp_GetEmployeeById
(@emp_id INT)
AS
BEGIN
	SELECT * FROM Employee
	WHERE EmpId = @emp_id;
END;

-- Executing Stored Prcedure with IN Parameter
EXEC usp_GetEmployeeById 102

-- Creating Stored Procedure with OUT Parameter
CREATE PROCEDURE usp_EmployeeCount
(@EmployeeCount INT OUTPUT)
AS
BEGIN
	SELECT @EmployeeCount = COUNT(*)
	FROM Employee;
END;

-- Executing Stored Procedure with OUT Parameter
DECLARE @TotalEmployees INT
EXEC usp_EmployeeCount @TotalEmployees OUTPUT
PRINT @TotalEmployees

-- Creating Stored Procedure to Insert New Employee
CREATE PROCEDURE usp_InsertEmployee
(@empid INT, 
@name VARCHAR(50), 
@salary INT, 
@address VARCHAR(100))
AS
BEGIN
	INSERT INTO Employee VALUES(@empid, @name, @salary, @address)
END;

-- Executing Stored Procedure to Insert New Employee
EXEC usp_InsertEmployee 105, "Gautam", 12000, "Delhi"


-- Altering Stored Procedure to Insert New Employee
 ALTER PROCEDURE usp_InsertEmployee
(@flag bit OUTPUT,
@EmpID int,
@Name varchar(50),
@Salary int,
@Address varchar(100))
AS
BEGIN
	BEGIN TRANSACTION 
	BEGIN TRY 
		Insert into Employee Values(@EmpID,@Name,@Salary,@Address)
		set @flag=1;
		IF @@TRANCOUNT > 0
		BEGIN
			commit TRANSACTION;
		END
		END TRY 
		BEGIN CATCH
		IF @@TRANCOUNT > 0
		BEGIN
		rollback TRANSACTION;
		END
		set @flag=0;
		END CATCH
END 

-- Executing Stored Procedure using OUTPUT Parameter to Insert New Employee
DECLARE @flag BIT
EXEC usp_InsertEmployee @flag OUTPUT, 106, "James", 22000, "Mumbai"
IF @flag=1
	PRINT 'Employee Inserted Successfully.'
ELSE
	PRINT 'There is some error.'


EXEC usp_GetAllEmployees

-- Creating Stored Procedure to Delete Existing Employee
CREATE PROCEDURE usp_DeleteEmployee
@empid INT
AS
BEGIN
	DELETE FROM Employee
	WHERE EmpId = @empid
END;

-- Executing Stored Procedure to Delete Existing Employee
EXEC usp_DeleteEmployee 106

-- Altering Stored Procedure to Delete Existing Employee
ALTER PROCEDURE usp_DeleteEmployee
@flag bit output,
@EmpID int
AS
BEGIN
 BEGIN TRANSACTION 
 BEGIN TRY
	 Delete from Employee Where EmpId=@EmpID 
	 set @flag=1; 
	IF @@TRANCOUNT > 0
	 BEGIN 
		commit TRANSACTION;
	 END
 END TRY
 BEGIN CATCH
	IF @@TRANCOUNT > 0
	 BEGIN 
		rollback TRANSACTION; 
		set @flag=0; 
	 END
	
END CATCH 
END 

-- Executing Stored Procedure to Delete Existing Employee
DECLARE @flag bit
EXEC usp_DeleteEmployee @flag OUTPUT, 104
IF @flag=1
	PRINT 'Employee Deleted Successfully.'
ELSE
	PRINT 'There is some error.'

EXEC usp_GetAllEmployees

-- Creating Stored Procedure to Update Existing Employee
CREATE PROCEDURE usp_UpdateEmployee
@EmpId INT,
@Name VARCHAR(50),
@Salary INT,
@Address VARCHAR(100)
AS
BEGIN
	UPDATE Employee
	SET Name=@Name, Salary=@Salary, Address=@Address
	WHERE EmpId=@EmpId
END

-- Executing Stored Procedure to Update Existing Employee
EXEC usp_UpdateEmployee 103, 'Sarah', 40000, 'Chennai'

-- Altering Stored Procedure to Update Existing Employee
ALTER PROCEDURE usp_UpdateEmployee
@flag BIT OUTPUT,
@EmpId INT,
@Name VARCHAR(50),
@Salary INT,
@Address VARCHAR(100)
AS
BEGIN
	BEGIN TRANSACTION
	BEGIN TRY
		UPDATE Employee
		SET Name=@Name, Salary=@Salary, Address=@Address
		WHERE EmpId=@EmpId
		SET @flag=1
		IF @@TRANCOUNT > 0
		BEGIN
			COMMIT TRANSACTION
		END
	END TRY
	BEGIN CATCH
		IF @@TRANCOUNT > 0
		BEGIN
			ROLLBACK TRANSACTION
		END
		SET @flag=0
	END CATCH
END

-- -- Executing Stored Procedure to Update Existing Employee
DECLARE @flag BIT
EXEC usp_UpdateEmployee @flag OUTPUT, 103, 'Sarah Bowling', 23000, 'Hyderabad'
if @flag=1
	PRINT 'Employee Updated Successfully.'
else
	PRINT 'There is some error.'


EXEC usp_GetAllEmployees


-- Creating Stored Procedure with IN Parameter
ALTER PROCEDURE usp_GetEmployeeById
(@emp_id INT)
WITH ENCRYPTION
AS
BEGIN
	SELECT * FROM Employee
	WHERE EmpId = @emp_id;
END;

-- Executing Stored Prcedure with IN Parameter
EXEC usp_GetEmployeeById 102

----------------------------------------------------

ALTER PROCEDURE usp_GetEmployeeById
(@emp_id INT)
WITH ENCRYPTION
AS
BEGIN
	SELECT EmpId, Name, Salary FROM Employee
	WHERE EmpId = @emp_id;
END;


EXEC usp_GetEmployeeById 101

EXEC sp_helptext usp_GetEmployeeById

EXEC sp_rename 'usp_GetEmployeeById', 'usp_GetEmployee'

EXEC usp_GetEmployee 101

-------------------------------------

CREATE PROCEDURE usp_GetEmployeeById
(@emp_id INT)
WITH ENCRYPTION
AS
BEGIN
	SELECT EmpId, Name, Salary FROM Employee
	WHERE EmpId = @emp_id;
END;

DROP PROCEDURE usp_GetEmployeeById

SQL Server stored procedures for beginners
https://www.sqlshack.com/sql-server-stored-procedures-for-beginners/

Basics of stored procedure in SQL Server Management Studio
https://blog.e-zest.com/basics-of-stored-procedure-in-sql-server-management-studio

Getting started with Stored Procedures in SQL Server
https://www.mssqltips.com/sqlservertip/1495/getting-started-with-stored-procedures-in-sql-server/

[SQL SERVER][Memo]撰寫Stored Procedure小細節
https://dotblogs.com.tw/ricochen/2011/06/23/29628

MS SQL建立Store Procedure教學
https://david740204.pixnet.net/blog/post/448695917-ms-sql%E5%BB%BA%E7%AB%8Bstore-procedure%E6%95%99%E5%AD%B8