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
執行:
EXEC PROC_INSERT_STUDENT 1001,N'張三',19,'ShangHai'
2、修改
根據學生ID,更新學生資訊
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
執行:
EXEC PROC_UPDATE_STUDENT 1001,N'張思',20,'ShangHai'
3、刪除
根據ID,刪除某學生記錄
--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
執行:
EXEC PROC_DELETE_STUDENT_BY_ID 1001
三、儲存過程實現分頁查詢
1、使用row_number函式分頁
--分頁查詢
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分頁
--使用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
四、其他功能:
1、儲存過程,每次執行都進行重新編譯
--1、儲存過程,重複編譯
IF OBJECT_ID (N'PROC_SELECT_STUDENTS_WITH_RECOMPILE', N'P') IS NOT NULL
DROP procedure PROC_SELECT_STUDENTS_WITH_RECOMPILE;
GO
CREATE procedure PROC_SELECT_STUDENTS_WITH_RECOMPILE
with recompile --重複編譯
AS
SELECT * FROM Students
GO
2、對儲存過程進行加密
加密後,不能檢視和修改源指令碼
--2、查詢儲存過程,進行加密
IF OBJECT_ID (N'PROC_SELECT_STUDENTS_WITH_ENCRYPTION', N'P') IS NOT NULL
DROP procedure PROC_SELECT_STUDENTS_WITH_ENCRYPTION;
GO
CREATE procedure PROC_SELECT_STUDENTS_WITH_ENCRYPTION
with encryption --加密
AS
SELECT * FROM Students
GO
執行:
EXEC PROC_SELECT_STUDENTS_WITH_ENCRYPTION
效果,無法檢視指令碼或者匯出建立指令碼
轉載自:SQL總結(五)儲存過程
相關文章
- SQL Server系統儲存過程和引數總結SQLServer儲存過程
- SQL儲存過程示例SQL儲存過程
- SQL Server 儲存過程SQLServer儲存過程
- 【SQL Server】--儲存過程SQLServer儲存過程
- SQL 儲存過程裡呼叫另一個儲存過程SQL儲存過程
- 用java呼叫oracle儲存過程總結JavaOracle儲存過程
- SQL 分頁儲存過程SQL儲存過程
- MS SQL Server儲存過程SQLServer儲存過程
- Sql Server系列:儲存過程SQLServer儲存過程
- SQL 建立儲存過程PROCEDURESQL儲存過程
- 解密SQL SERVER儲存過程解密SQLServer儲存過程
- SQL儲存過程迴圈SQL儲存過程
- sql儲存過程分頁SQL儲存過程
- 在儲存過程A中呼叫儲存過程B的結果儲存過程
- 兩種SQL分頁方法儲存過程和遊標儲存過程SQL儲存過程
- Sql儲存過程分頁--臨時表儲存SQL儲存過程
- sql 儲存過程命名規範SQL儲存過程
- Oracle Pl/SQL 之 儲存過程OracleSQL儲存過程
- PL/SQL 05 儲存過程 procedureSQL儲存過程
- SQL Server基礎:儲存過程SQLServer儲存過程
- sql server儲存過程語法SQLServer儲存過程
- MySQL儲存過程in、out、inout引數示例與總結MySql儲存過程
- Oracle SQL Developer Debug儲存過程OracleSQLDeveloper儲存過程
- SQL Server 儲存過程的運用SQLServer儲存過程
- SQL Server基礎之儲存過程SQLServer儲存過程
- oracle動態sql儲存過程示例OracleSQL儲存過程
- (SQL Server)分頁的儲存過程SQLServer儲存過程
- SQL分隔字串的儲存過程 (轉)SQL字串儲存過程
- 儲存過程結果進行查詢 select 存過過程儲存過程
- MySQL儲存過程詳解 mysql 儲存過程MySql儲存過程
- 儲存過程儲存過程
- SQL儲存過程【筆記】一個較簡單的庫存月結SQL儲存過程筆記
- SQL Server 禁用擴充套件儲存過程SQLServer套件儲存過程
- SQL Server 資料備份儲存過程SQLServer儲存過程
- ms sql server儲存過程目前使用模板SQLServer儲存過程
- SQL Server儲存過程中raiserror的使用SQLServer儲存過程AIError
- SQL學習-隨機數,儲存過程SQL隨機儲存過程
- mssql 儲存過程呼叫另一個儲存過程中的結果的方法分享SQL儲存過程