轉(zhuǎn)自：http://www.cnblogs.com/yank/p/4235609.html
存儲(chǔ)過程（Stored Procedure）：已預(yù)編譯為一個(gè)可執(zhí)行過程的一個(gè)或多個(gè)SQL語句。
CREATE proc | procedure procedure_name [{@參數(shù)數(shù)據(jù)類型} [=默認(rèn)值] [output],
{@參數(shù)數(shù)據(jù)類型} [=默認(rèn)值] [output],
....
]as
SQL_statements
go優(yōu)勢:
1、提高性能
SQL語句在創(chuàng)建過程時(shí)進(jìn)行分析和編譯。 存儲(chǔ)過程是預(yù)編譯的，在首次運(yùn)行一個(gè)存儲(chǔ)過程時(shí)，查詢優(yōu)化器對其進(jìn)行分析、優(yōu)化，并給出最終被存在系統(tǒng)表中的存儲(chǔ)計(jì)劃，這樣，在執(zhí)行過程時(shí)便可節(jié)省此開銷。
2、降低網(wǎng)絡(luò)開銷
存儲(chǔ)過程調(diào)用時(shí)只需用提供存儲(chǔ)過程名和必要的參數(shù)信息，從而可降低網(wǎng)絡(luò)的流量。
3、便于進(jìn)行代碼移植
數(shù)據(jù)庫專業(yè)人員可以隨時(shí)對存儲(chǔ)過程進(jìn)行修改，但對應(yīng)用程序源代碼卻毫無影響，從而極大的提高了程序的可移植性。
4、更強(qiáng)的安全性
1）系統(tǒng)管理員可以對執(zhí)行的某一個(gè)存儲(chǔ)過程進(jìn)行權(quán)限限制，避免非授權(quán)用戶對數(shù)據(jù)的訪問
2）在通過網(wǎng)絡(luò)調(diào)用過程時(shí)，只有對執(zhí)行過程的調(diào)用是可見的。 因此，惡意用戶無法看到表和數(shù)據(jù)庫對象名稱、嵌入自己的 Transact-SQL 語句或搜索關(guān)鍵數(shù)據(jù)。
3）使用過程參數(shù)有助于避免 SQL 注入攻擊。 因?yàn)閰?shù)輸入被視作文字值而非可執(zhí)行代碼，所以，攻擊者將命令插入過程內(nèi)的 Transact-SQL 語句并損害安全性將更為困難。
4）可以對過程進(jìn)行加密，這有助于對源代碼進(jìn)行模糊處理。
劣勢：
1、存儲(chǔ)過程需要專門的數(shù)據(jù)庫開發(fā)人員進(jìn)行維護(hù)，但實(shí)際情況是，往往由程序開發(fā)員人員兼職
2、設(shè)計(jì)邏輯變更，修改存儲(chǔ)過程沒有SQL靈活
在通常的項(xiàng)目研發(fā)中，用存儲(chǔ)過程卻相對較少，這是為什么呢？
分析原因如下：
1）沒有特定的數(shù)據(jù)庫開發(fā)人員，普通程序員兼職進(jìn)行數(shù)據(jù)庫操作
2）程序員往往只需操作程序，即可完成數(shù)據(jù)訪問，無需再在數(shù)據(jù)庫上進(jìn)行開發(fā)
3）項(xiàng)目需求變動(dòng)比較頻繁，修改SQL語句比較方便，特別是涉及邏輯變更
基于實(shí)際應(yīng)用的經(jīng)驗(yàn)，給予如下建議：
1、在一些高效率或者規(guī)范性要求比較高的項(xiàng)目，建議采用存儲(chǔ)過程
2、對于一般項(xiàng)目建議采用參數(shù)化命令方式，是存儲(chǔ)過程與SQL語句一種折中的方式
3、對于一些算法要求比較高，涉及多條數(shù)據(jù)邏輯，建議采用存儲(chǔ)過程
一、基礎(chǔ)查詢
1、創(chuàng)建不帶參數(shù)的存儲(chǔ)過程
例子：查詢學(xué)生總數(shù)
--查詢存儲(chǔ)過程IF OBJECT_ID (N'PROC_SELECT_STUDENTS_COUNT', N'P') IS NOT NULL DROP procedure PROC_SELECT_STUDENTS_COUNT;GOCREATE procedure PROC_SELECT_STUDENTS_COUNTAS SELECT COUNT(ID) FROM StudentsGO
執(zhí)行：
EXEC PROC_SELECT_STUDENTS_COUNT
2、帶參數(shù)的存儲(chǔ)過程
--查詢存儲(chǔ)過程，根據(jù)城市查詢總數(shù)IF OBJECT_ID (N'PROC_SELECT_STUDENTS_BY_CITY_COUNT', N'P') IS NOT NULL DROP procedure PROC_SELECT_STUDENTS_BY_CITY_COUNT;GOCREATE procedure PROC_SELECT_STUDENTS_BY_CITY_COUNT(@city nvarchar(50))AS SELECT COUNT(ID) FROM Students WHERE City=@cityGO
執(zhí)行語句：
EXEC PROC_SELECT_STUDENTS_BY_CITY_COUNT N'Beijing'
3、帶有通配符
通配符，在參數(shù)值賦值時(shí)，加上相應(yīng)的通配符
--3、查詢姓氏為李的學(xué)生信息，含通配符IF OBJECT_ID (N'PROC_SELECT_STUDENTS_BY_SURNNAME', N'P') IS NOT NULL DROP procedure PROC_SELECT_STUDENTS_BY_SURNNAME;GOCREATE procedure PROC_SELECT_STUDENTS_BY_SURNNAME @surnName nvarchar(20)='李%' --默認(rèn)值A(chǔ)S SELECT ID,Name,Age FROM Students WHERE Name like @surnNameGO
執(zhí)行：
EXEC PROC_SELECT_STUDENTS_BY_SURNNAMEEXEC PROC_SELECT_STUDENTS_BY_SURNNAME N'李%'EXEC PROC_SELECT_STUDENTS_BY_SURNNAME N'%李%'
4、帶有輸出參數(shù)
--根據(jù)姓名查詢的學(xué)生信息，返回學(xué)生的城市及年齡IF OBJECT_ID (N'PROC_SELECT_STUDENTS_BY_NAME', N'P') IS NOT NULL DROP procedure PROC_SELECT_STUDENTS_BY_NAME;GOCREATE procedure PROC_SELECT_STUDENTS_BY_NAME @name nvarchar(50), --輸入?yún)?shù) @city nvarchar(20) out, --輸出參數(shù) @age int output --輸入輸出參數(shù)AS SELECT @city=City,@age=Age FROM Students WHERE Name=@name AND Age=@ageGO
執(zhí)行：
--執(zhí)行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;
二、使用存儲(chǔ)過程進(jìn)行增刪改
1、新增
新增學(xué)生信息
--1、存儲(chǔ)過程：新增學(xué)生信息IF OBJECT_ID (N'PROC_INSERT_STUDENT', N'P') IS NOT NULL DROP procedure PROC_INSERT_STUDENT;GOCREATE 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
執(zhí)行：
EXEC PROC_INSERT_STUDENT 1001,N'張三',19,'ShangHai'
2、修改
根據(jù)學(xué)生ID，更新學(xué)生信息
IF OBJECT_ID (N'PROC_UPDATE_STUDENT', N'P') IS NOT NULL DROP procedure PROC_UPDATE_STUDENT;GOCREATE 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=@idGO
執(zhí)行：
EXEC PROC_UPDATE_STUDENT 1001,N'張思',20,'ShangHai'
3、刪除
根據(jù)ID，刪除某學(xué)生記錄
--3、存儲(chǔ)過程：刪除學(xué)生信息IF OBJECT_ID (N'PROC_DELETE_STUDENT_BY_ID', N'P') IS NOT NULL DROP procedure PROC_DELETE_STUDENT_BY_ID;GOCREATE procedure PROC_DELETE_STUDENT_BY_ID @id intAS DELETE FROM Students WHERE ID=@idGO
執(zhí)行：
EXEC PROC_DELETE_STUDENT_BY_ID 1001
三、存儲(chǔ)過程實(shí)現(xiàn)分頁查詢
1、使用row_number函數(shù)分頁
--分頁查詢IF OBJECT_ID (N'PROC_SELECT_BY_PAGE', N'P') IS NOT NULL DROP procedure PROC_SELECT_BY_PAGE;GOCREATE procedure PROC_SELECT_BY_PAGE @startIndex int, @endIndex intAS 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 @endIndexGO
執(zhí)行：
EXEC PROC_SELECT_BY_PAGE 1,10
2、使用傳統(tǒng)的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;GOCREATE procedure PROC_SELECT_BY_PAGE_WITH_TOP @pageIndex int, @pageSize intAS 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
執(zhí)行：
EXEC PROC_SELECT_BY_PAGE_WITH_TOP 1,2
四、其他功能：
1、存儲(chǔ)過程，每次執(zhí)行都進(jìn)行重新編譯
--1、存儲(chǔ)過程，重復(fù)編譯IF OBJECT_ID (N'PROC_SELECT_STUDENTS_WITH_RECOMPILE', N'P') IS NOT NULL DROP procedure PROC_SELECT_STUDENTS_WITH_RECOMPILE;GOCREATE procedure PROC_SELECT_STUDENTS_WITH_RECOMPILEwith recompile --重復(fù)編譯AS SELECT * FROM StudentsGO
2、對存儲(chǔ)過程進(jìn)行加密
加密后，不能查看和修改源腳本
--2、查詢存儲(chǔ)過程，進(jìn)行加密IF OBJECT_ID (N'PROC_SELECT_STUDENTS_WITH_ENCRYPTION', N'P') IS NOT NULL DROP procedure PROC_SELECT_STUDENTS_WITH_ENCRYPTION;GOCREATE procedure PROC_SELECT_STUDENTS_WITH_ENCRYPTIONwith encryption --加密AS SELECT * FROM StudentsGO
執(zhí)行：
EXEC PROC_SELECT_STUDENTS_WITH_ENCRYPTION
效果，無法查看腳本或者導(dǎo)出創(chuàng)建腳本
作者介紹：半路學(xué)IT，做開發(fā)3年，先就職在一家共享單車公司，做后臺(tái)開發(fā)！
聯(lián)系客服
