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

留言

張貼留言

這個網誌中的熱門文章

何謂淨重(Net Weight)、皮重(Tare Weight)與毛重(Gross Weight)

-
圖片
當談到貨物的重量時，往往會分成所謂的淨重(Net Weight)與毛重(Gross Weight)。 會有以下公式關係存在 淨重=毛重-皮重(包裝物的重量) 毛重=淨重+皮重 淨重( Net Weight 縮寫為N.W.) 指物品本身的重量，不包括容器或包裝材料的重量。 凈重是國際貿易中最常見的計重辦法。 例如，如果運輸原材料，原材料的重量就是所運輸貨物的淨重。包裝原材料的容器或包裝不計入淨重。同樣地，當我們考慮一罐豆子時，罐子內所含物品的重量就算作淨重。因此，淨重將包括豆子、罐頭里的任何液體，以及保存豆子所含的物品。 大多數 以重量為計價的貨物買賣 ，採用此計價方法。 若合同中並未明確規範以何種方式計量和計價時，按慣例以凈重計價。 毛重( Gross Weight 縮寫G.W.) 貨物連同它的包裝的重量，是你托運的所有物品的總重量。 皮重(Tare Weight) 指商品外包裝材料的重量(即運輸包裝的重量)，不包括內包裝材料和襯墊物的重量。 比方一個空的貨櫃、容器、或任何包裝材料的重量。 Net Net Weight純淨重 (指從Net Weight中扣除內包裝後的重量) 進出口商品為了因應運輸及消費需求, 一般都有內包裝與外包裝。 一般在買賣合同與信用狀中,通常只要求於裝箱單上註明Gross Weight和Net Weight即可, 但有些L/C 要求 Net Net Weight,大多是海關徵收從量稅時的依據 運輸重量對於航運業或者航空運輸等各自都通用這些術語 為何特別在意重量? 主要就在於會影響像是傳輸安全乘載限制。 就運輸層面(不同方式的運輸) 航空運輸：對機長而言，毛重包括皮重、產品淨重以及飛機、燃料、乘客和機組人員的重量。 公路/鐵路運輸:對於火車車長而言，毛重包括產品淨重、皮重和運輸車輛的重量。 水路航運運輸：對船長而言，毛重就是淨重和皮重之和。 就公司ERP成本角度 成本一般都會採用 "淨重" 主要是因為若用毛重，可能會有輔材不可控因素存在 比方一個 5cm 固定塑膠件，今天用一個 10cm 紙箱測重，明天用一個 100cm 紙箱測重，那可能就容易導致ERP資料比較會失真。 就物流層面來探討 通常還會有一名詞即所謂「實際重量」(Actual Weight) 根據稱重(過磅)所得到的重量，實際重量分為實際毛重(Gross Weig...
閱讀完整內容

Architecture(架構) 和 Framework(框架) 有何不同?_軟體設計前的事前規劃的藍圖概念

-
圖片
一個應用程式(軟體) 不管是  互動拍照 也好、 投影也好、車牌偵測... 舞台演唱會後方電視牆的互動應用程序、 粒子特效、3D或2D遊戲  等等 開發者通常都需要在一開始做規劃 ㄟ  假設我今天要寫 跟影像有關 可能需要偵測、可能需要提取前景、可能需要偵測圓形 之類的 我有  emgucv 、AForge.Net、 opencv 等等可以用 但是 假設我今天要做的應用程序是需要有視窗介面的~~ emgucv 跟 C# windows form 媒合性 就很高 而且直接在 visual studio 做開發 opencv 我可能還需要依賴 Qt 之類等等  framework 假設我今天是要寫一個 演唱會電視牆互動粒子特效 可能就會挑   openframeworks  、 processing 、kinect 來寫互動 Achitecture  --->全稱 :  Software Architecture   軟體架構 Achitecture   由好多 framework 組成 軟體架構（Software Architecture）是一種軟體在開發前的設計藍圖， 用來告訴軟體的結構，功能，介面，用法，與其他系統的構連以及資料交換等等規範， 但它並沒有叫你要用什麼方式實作， 因此軟體架構通常會產生文件，圖樣，原型以及規格等， 就是沒有可用的程式碼，因為那不是軟體架構應該有的東西， 就像蓋房子時是給你藍圖，而不是一幢蓋好的房子。 軟體框架（Framework):是一個已經成形的方法，而且有程式碼實體 （例如鋼構工法也是要有鋼材才能做），並且會告訴你要如何使用它 （即 Framework Documentation，MSDN Library 即為一最佳例子）
閱讀完整內容

經得起原始碼資安弱點掃描的程式設計習慣培養(五)_Missing HSTS Header

-
圖片
  接續前幾篇系列 經得起原始碼資安弱點掃描的程式設計習慣培養(一)_OWASP Top 10(十大網站安全風險)_學習寫更安全程式碼的網站推薦 經得起原始碼資安弱點掃描的程式設計習慣培養(二)_8.Insecure Deserialization ：不安全的反序列化漏洞ASP.NET處理方式 經得起原始碼資安弱點掃描的程式設計習慣培養(三)_7.Cross Site Scripting(XSS)_Stored XSS_Reflected XSS All Clients 經得起原始碼資安弱點掃描的程式設計習慣培養(四)_1.Injection注入攻擊_SQL Injection)_order by 語句正確參數化套入 最近又遇到被若安掃描到高風險要修正的程式碼啦.... 一看這還真是搞不懂該怎麼修呢 HSTS(HTTP Strict Transport Security)  主要是一份國際標準規格 (RFC 6797) 的網際網路瀏覽安全機制， 用於宣告瀏覽器與伺服器之間通訊方式必須強制採用 TLS/SSL 加密通道。 只要從伺服器端送出一個 Strict-Transport-Security 標頭 (Header) 給瀏覽器，即可告知瀏覽器於未來的某段時間內一律使用 SSL 來和該網站連接 (設定可涵蓋所有子域名網站)， 一旦發生憑證失效情況，使用者將無法再瀏覽該網站，如此一來便可大幅減少中間人攻擊的問題發生。 再更白話一點 HSTS Header 就是負責將 http 強制轉為 https CheckMarx掃碼報告給的說明 ============================================================ 風險: 如果web config沒有設置HSTS表頭、"max-age"(有效期)不足一年， 或沒有設定"includeSubDomains"(包含所有子網域)，可能會使用戶容易遭受中間人攻擊。 發生原因: 許多使用者只要在瀏覽器的網址列輸入網域名稱(Domain Name)即可瀏覽網站， 並沒有輸入協定(Protocol)的名稱。 瀏覽器會自動假設使用者使用的是HTTP協定而不是有加密機制的HTTPS協定。 在第一次發出前往特定網站的請求時，攻擊者可以執行中間人攻擊並將用戶轉址到...
閱讀完整內容