SQL總結預存程序

概念

預存程序（Stored Procedure）：已預先編譯為可執行程序的一個或多個SQL語句。 

建立預存程序語法

CREATE proc | procedure procedure_name
    [{@参数数据类型} [=默认值] [output],
     {@参数数据类型} [=默认值] [output],
     ....
    ]
as
    SQL_statements
go

預存程序與SQL語句比較

優勢:

1、提升效能
SQL語句在建立流程時進行分析與編譯。 預存程序是預先編譯的，在首次執行一個預存程序時，查詢最佳化器對其進行分析、最佳化，並給出最終被存在系統表中的儲存計劃，這樣，在執行過程時便可節省此開銷。
2、降低網路開銷
預存程序呼叫時只需用提供預存程序名稱和必要的參數信息，從而可降低網路的流量。
3、便於進行程式碼移植
資料庫專業人員可以隨時對預存程序進行修改，但對應用程式原始碼卻毫無影響，從而極大的提高了程式的可移植性。
4、更強的安全性
1）系統管理員可以對執行的某一個預存程序進行權限限制，避免非授權使用者對資料的存取
2）在透過網路調用過程時，只有對執行過程的調用是可見的。 因此，惡意使用者無法看到表格和資料庫物件名稱、嵌入自己的 Transact-SQL 語句或搜尋關鍵資料。
3）使用過程參數有助於避免 SQL 注入攻擊。 因為參數輸入被視為文字值而非可執行程式碼，所以，攻擊者將命令插入過程內的 Transact-SQL 語句並損害安全性將更為困難。
4）可以對過程進行加密，這有助於對原始碼進行模糊處理。

劣勢：

1、儲存過程需要專門的資料庫開發人員進行維護，但實際情況是，往往由程式開發員人員兼職

2、設計邏輯變更，修改儲存過程沒有SQL彈性

為什麼在實際實際應用中，儲存過程用到相對較少呢？

在通常的專案研發中，用儲存過程卻相對較少，這是為什麼呢？
分析原因如下：
1）沒有特定的資料庫開發人員，普通程式設計師兼職進行資料庫操作
2）程式設計師往往只需操作程序，即可完成資料訪問，無需再在資料庫上進行開發
3）項目需求變動比較頻繁，修改SQL語句比較方便，特別是涉及邏輯變更 

預存程序與SQL語句如何抉擇？

基於實際應用的經驗，給予以下建議：

1、在一些高效率或規範性要求比較高的項目，建議採用預存程序
2、對於一般項目建議採用參數化指令方式，是預存程序與SQL語句一種折下來的方式
3、對於某些演算法要求比較高，涉及多個資料邏輯，建議採用預存程序

 

預存程序的具體應用

 一、基礎查詢

1、創建不帶參數的預存程序

範例：查詢學生總數

--查询存储过程
IF OBJECT_ID (N'PROC_SELECT_STUDENTS_COUNT', N'P') IS NOT NULL
    DROP procedure PROC_SELECT_STUDENTS_COUNT;
GO
CREATE procedure PROC_SELECT_STUDENTS_COUNT
AS 
    SELECT COUNT(ID) FROM Students
GO

執行：

EXEC PROC_SELECT_STUDENTS_COUNT

2、帶參數的預存程序

--查询存储过程，根据城市查询总数
IF OBJECT_ID (N'PROC_SELECT_STUDENTS_BY_CITY_COUNT', N'P') IS NOT NULL
    DROP procedure PROC_SELECT_STUDENTS_BY_CITY_COUNT;
GO
CREATE procedure PROC_SELECT_STUDENTS_BY_CITY_COUNT(@city nvarchar(50))
AS
    SELECT COUNT(ID) FROM Students WHERE City=@city
GO

執行語句：

EXEC PROC_SELECT_STUDENTS_BY_CITY_COUNT N'Beijing'

3、帶有通配符

值分配加上對應的通配符

--3、查询姓氏为李的学生信息，含通配符
IF OBJECT_ID (N'PROC_SELECT_STUDENTS_BY_SURNNAME', N'P') IS NOT NULL
    DROP procedure PROC_SELECT_STUDENTS_BY_SURNNAME;
GO
CREATE procedure PROC_SELECT_STUDENTS_BY_SURNNAME
    @surnName nvarchar(20)='李%' --默认值
AS 
    SELECT ID,Name,Age FROM Students WHERE Name like @surnName
GO

執行：

EXEC PROC_SELECT_STUDENTS_BY_SURNNAME
EXEC PROC_SELECT_STUDENTS_BY_SURNNAME N'李%'
EXEC PROC_SELECT_STUDENTS_BY_SURNNAME N'%李%'

4、帶有輸出參數

--根据姓名查询的学生信息，返回学生的城市及年龄
IF OBJECT_ID (N'PROC_SELECT_STUDENTS_BY_NAME', N'P') IS NOT NULL
    DROP procedure PROC_SELECT_STUDENTS_BY_NAME;
GO
CREATE procedure PROC_SELECT_STUDENTS_BY_NAME
    @name nvarchar(50),     --输入参数
    @city nvarchar(20) out, --输出参数
    @age  int output        --输入输出参数
AS 
    SELECT @city=City,@age=Age FROM Students WHERE Name=@name AND Age=@age
GO

執行：

--执行
declare @name nvarchar(50),
        @city nvarchar(20),
        @age int;
set @name = N'李明';
set @age = 20;
exec PROC_SELECT_STUDENTS_BY_NAME @name,@city out, @age output;
select @city, @age;

二、使用預存程序進行增補

1、新增刪除學生資料

執行：

--1、存储过程：新增学生信息
IF OBJECT_ID (N'PROC_INSERT_STUDENT', N'P') IS NOT NULL
    DROP procedure PROC_INSERT_STUDENT;
GO
CREATE procedure PROC_INSERT_STUDENT
    @id int,
    @name nvarchar(20),
    @age int,
    @city nvarchar(20)
AS 
    INSERT INTO Students(ID,Name,Age,City) VALUES(@id,@name,@age,@city)
GO

2、修改

根據學生ID，更新學生資料 

EXEC PROC_INSERT_STUDENT 1001,N'张三',19,'ShangHai'

執行：

IF OBJECT_ID (N'PROC_UPDATE_STUDENT', N'P') IS NOT NULL
    DROP procedure PROC_UPDATE_STUDENT;
GO
CREATE procedure PROC_UPDATE_STUDENT
    @id int,
    @name nvarchar(20),
    @age int,
    @city nvarchar(20)
AS 
    UPDATE Students SET Name=@name,Age=@age,City=@city WHERE ID=@id
GO

 

3、刪除

、預存程序實作分頁查詢

1、使用row_number函數分頁

EXEC PROC_UPDATE_STUDENT 1001,N'张思',20,'ShangHai'

執行：

--3、存储过程：删除学生信息
IF OBJECT_ID (N'PROC_DELETE_STUDENT_BY_ID', N'P') IS NOT NULL
    DROP procedure PROC_DELETE_STUDENT_BY_ID;
GO
CREATE procedure PROC_DELETE_STUDENT_BY_ID
    @id int
AS 
    DELETE FROM  Students WHERE ID=@id
GO

2、使用傳統的top分頁

EXEC PROC_DELETE_STUDENT_BY_ID 1001

執行：

--分页查询
IF OBJECT_ID (N'PROC_SELECT_BY_PAGE', N'P') IS NOT NULL
    DROP procedure PROC_SELECT_BY_PAGE;
GO
CREATE procedure PROC_SELECT_BY_PAGE
    @startIndex int,
    @endIndex int
AS 
    SELECT  * FROM (SELECT ID,Name,Age,City,ROW_NUMBER() OVER(ORDER BY ID DESC) AS RowNumber FROM Students) AS Temp 
    WHERE Temp.RowNumber BETWEEN @startIndex AND @endIndex
GO

，每次執行都重新編譯

EXEC PROC_SELECT_BY_PAGE 1,10

 

2、對預存程序進行加密

加密後，不能查看和修改來源腳本

--使用TOP分页
IF OBJECT_ID (N'PROC_SELECT_BY_PAGE_WITH_TOP', N'P') IS NOT NULL
    DROP procedure PROC_SELECT_BY_PAGE_WITH_TOP;
GO
CREATE procedure PROC_SELECT_BY_PAGE_WITH_TOP
    @pageIndex int,
    @pageSize int
AS 
    SELECT TOP(@pageSize) * FROM Students 
    WHERE ID >=(SELECT MAX(ID) FROM (SELECT TOP(@pageSize*(@pageIndex-1) + 1) ID FROM Students ORDER BY ID) AS Temp)    
GO

執行：

EXEC PROC_SELECT_BY_PAGE_WITH_TOP 1,2