概念#存儲過程（Stored Procedure）：已預(yù)編譯為一個可執(zhí)行過程的一個或多個SQL語句。 創(chuàng)建存儲過程語法# CREATE proc | procedure procedure_name [{@參數(shù)數(shù)據(jù)類型} [=默認(rèn)值] [output],
{@參數(shù)數(shù)據(jù)類型} [=默認(rèn)值] [output],
....
]as
SQL_statements存儲過程與SQL語句對比#優(yōu)勢: 1、提高性能 劣勢： 1、存儲過程需要專門的數(shù)據(jù)庫開發(fā)人員進(jìn)行維護(hù)，但實(shí)際情況是，往往由程序開發(fā)員人員兼職 2、設(shè)計邏輯變更，修改存儲過程沒有SQL靈活 為什么在實(shí)際應(yīng)用中，存儲過程用到相對較少呢？#在通常的項目研發(fā)中，用存儲過程卻相對較少，這是為什么呢？ 存儲過程與SQL語句如何抉擇？#基于實(shí)際應(yīng)用的經(jīng)驗(yàn)，給予如下建議： 1、在一些高效率或者規(guī)范性要求比較高的項目，建議采用存儲過程 存儲過程的具體應(yīng)用#一、基礎(chǔ)查詢 1、創(chuàng)建不帶參數(shù)的存儲過程 例子：查詢學(xué)生總數(shù) --查詢存儲過程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ù)的存儲過程 --查詢存儲過程，根據(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ù)值賦值時，加上相應(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; 二、使用存儲過程進(jìn)行增刪改 1、新增 新增學(xué)生信息 --1、存儲過程：新增學(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、存儲過程：刪除學(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 三、存儲過程實(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、存儲過程，每次執(zhí)行都進(jìn)行重新編譯 --1、存儲過程，重復(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、對存儲過程進(jìn)行加密 加密后，不能查看和修改源腳本 --2、查詢存儲過程，進(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年，先就職在一家共享單車公司，做后臺開發(fā)！ |
|
|
