SQL Server的存儲過程詳解

Posted on 2022-01-19 by WalkonNet

存儲過程的概念

存儲過程Procedure是一組為瞭完成特定功能的SQL語句集合，經編譯後存儲在數據庫中，用戶通過指定存儲過程的名稱並給出參數來執行。

存儲過程中可以包含邏輯控制語句和數據操縱語句，它可以接受參數、輸出參數、返回單個或多個結果集以及返回值。

由於存儲過程在創建時即在數據庫服務器上進行瞭編譯並存儲在數據庫中，所以存儲過程運行要比單個的SQL語句塊要快。同時由於在調用時隻需用提供存儲過程名和必要的參數信息，所以在一定程度上也可以減少網絡流量、簡單網絡負擔。

存儲過程的優點

A、存儲過程允許標準組件式編程

存儲過程創建後可以在程序中被多次調用執行，而不必重新編寫該存儲過程的SQL語句。而且數據庫專業人員可以隨時對存儲過程進行修改，但對應用程序源代碼卻毫無影響，從而極大的提高瞭程序的可移植性。

B、存儲過程能夠實現較快的執行速度

如果某一操作包含大量的T-SQL語句代碼，分別被多次執行，那麼存儲過程要比批處理的執行速度快得多。因為存儲過程是預編譯的，在首次運行一個存儲過程時，查詢優化器對其進行分析、優化，並給出最終被存在系統表中的存儲計劃。而批處理的T-SQL語句每次運行都需要預編譯和優化，所以速度就要慢一些。

C、存儲過程減輕網絡流量

對於同一個針對數據庫對象的操作，如果這一操作所涉及到的T-SQL語句被組織成一存儲過程，那麼當在客戶機上調用該存儲過程時，網絡中傳遞的隻是該調用語句，否則將會是多條SQL語句。從而減輕瞭網絡流量，降低瞭網絡負載。

D、存儲過程可被作為一種安全機制來充分利用

系統管理員可以對執行的某一個存儲過程進行權限限制，從而能夠實現對某些數據訪問的限制，避免非授權用戶對數據的訪問，保證數據的安全。

系統存儲過程

系統存儲過程是系統創建的存儲過程，目的在於能夠方便的從系統表中查詢信息或完成與更新數據庫表相關的管理任務或其他的系統管理任務。系統存儲過程主要存儲在master數據庫中，以“sp”下劃線開頭的存儲過程。盡管這些系統存儲過程在master數據庫中，但我們在其他數據庫還是可以調用系統存儲過程。有一些系統存儲過程會在創建新的數據庫的時候被自動創建在當前數據庫中。

常用系統存儲過程有：

exec sp_databases; --查看數據庫
exec sp_tables;        --查看表
exec sp_columns student;--查看列
exec sp_helpIndex student;--查看索引
exec sp_helpConstraint student;--約束
exec sp_stored_procedures;
exec sp_helptext 'sp_stored_procedures';--查看存儲過程創建、定義語句
exec sp_rename student, stuInfo;--修改表、索引、列的名稱
exec sp_renamedb myTempDB, myDB;--更改數據庫名稱
exec sp_defaultdb 'master', 'myDB';--更改登錄名的默認數據庫
exec sp_helpdb;--數據庫幫助，查詢數據庫信息
exec sp_helpdb master;

系統存儲過程示例：

--表重命名
exec sp_rename 'stu', 'stud';
select * from stud;
--列重命名
exec sp_rename 'stud.name', 'sName', 'column';
exec sp_help 'stud';
--重命名索引
exec sp_rename N'student.idx_cid', N'idx_cidd', N'index';
exec sp_help 'student';
--查詢所有存儲過程
select * from sys.objects where type = 'P';
select * from sys.objects where type_desc like '%pro%' and name like 'sp%';

用戶自定義存儲過程

1、創建語法

create proc | procedure pro_name
    [{@參數數據類型} [=默認值] [output],
     {@參數數據類型} [=默認值] [output],
     ....
    ]
as
    SQL_statements

2、創建不帶參數存儲過程

--創建存儲過程
if (exists (select * from sys.objects where name = 'proc_get_student'))
    drop proc proc_get_student
go
create proc proc_get_student
as
    select * from student;
--調用、執行存儲過程
exec proc_get_student;

3、修改存儲過程

--修改存儲過程
alter proc proc_get_student
as
select * from student;

4、帶參存儲過程

--帶參存儲過程
if (object_id('proc_find_stu', 'P') is not null)
    drop proc proc_find_stu
go
create proc proc_find_stu(@startId int, @endId int)
as
    select * from student where id between @startId and @endId
go
exec proc_find_stu 2, 4;

5、帶通配符參數存儲過程

--帶通配符參數存儲過程
if (object_id('proc_findStudentByName', 'P') is not null)
    drop proc proc_findStudentByName
go
create proc proc_findStudentByName(@name varchar(20) = '%j%', @nextName varchar(20) = '%')
as
    select * from student where name like @name and name like @nextName;
go
exec proc_findStudentByName;
exec proc_findStudentByName '%o%', 't%';

6、帶輸出參數存儲過程

if (object_id('proc_getStudentRecord', 'P') is not null)
    drop proc proc_getStudentRecord
go
create proc proc_getStudentRecord(
    @id int, --默認輸入參數
    @name varchar(20) out, --輸出參數
    @age varchar(20) output--輸入輸出參數
)
as
    select @name = name, @age = age  from student where id = @id and sex = @age;
go
-- 
declare @id int,
        @name varchar(20),
        @temp varchar(20);
set @id = 7; 
set @temp = 1;
exec proc_getStudentRecord @id, @name out, @temp output;
select @name, @temp;
print @name + '#' + @temp;

7、不緩存存儲過程

--WITH RECOMPILE 不緩存
if (object_id('proc_temp', 'P') is not null)
    drop proc proc_temp
go
create proc proc_temp
with recompile
as
    select * from student;
go
exec proc_temp;

8、加密存儲過程

--加密WITH ENCRYPTION 
if (object_id('proc_temp_encryption', 'P') is not null)
    drop proc proc_temp_encryption
go
create proc proc_temp_encryption
with encryption
as
    select * from student;
go
exec proc_temp_encryption;
exec sp_helptext 'proc_temp';
exec sp_helptext 'proc_temp_encryption';

9、帶遊標參數存儲過程

if (object_id('proc_cursor', 'P') is not null)
    drop proc proc_cursor
go
create proc proc_cursor
    @cur cursor varying output
as
    set @cur = cursor forward_only static for
    select id, name, age from student;
    open @cur;
go
--調用
declare @exec_cur cursor;
declare @id int,
        @name varchar(20),
        @age int;
exec proc_cursor @cur = @exec_cur output;--調用存儲過程
fetch next from @exec_cur into @id, @name, @age;
while (@@fetch_status = 0)
begin
    fetch next from @exec_cur into @id, @name, @age;
    print 'id: ' + convert(varchar, @id) + ', name: ' + @name + ', age: ' + convert(char, @age);
end
close @exec_cur;
deallocate @exec_cur;--刪除遊標

10、分頁存儲過程

---存儲過程、row_number完成分頁
if (object_id('pro_page', 'P') is not null)
    drop proc proc_cursor
go
create proc pro_page
    @startIndex int,
    @endIndex int
as
    select count(*) from product
;    
    select * from (
        select row_number() over(order by pid) as rowId, * from product 
    ) temp
    where temp.rowId between @startIndex and @endIndex
go
--drop proc pro_page
exec pro_page 1, 4
--
--分頁存儲過程
if (object_id('pro_page', 'P') is not null)
    drop proc pro_stu
go
create procedure pro_stu(
    @pageIndex int,
    @pageSize int
)
as
    declare @startRow int, @endRow int
    set @startRow = (@pageIndex - 1) * @pageSize +1
    set @endRow = @startRow + @pageSize -1
    select * from (
        select *, row_number() over (order by id asc) as number from student 
    ) t
    where t.number between @startRow and @endRow;
exec pro_stu 2, 2;

Raiserror

Raiserror返回用戶定義的錯誤信息，可以指定嚴重級別，設置系統變量記錄所發生的錯誤。

語法如下：

Raiserror({msg_id | msg_str | @local_variable}
  {, severity, state}
  [,argument[,…n]]
  [with option[,…n]]
)

# msg_id:在sysmessages系統表中指定的用戶定義錯誤信息
# msg_str:用戶定義的信息，信息最大長度在2047個字符。
# severity：用戶定義與該消息關聯的嚴重級別。當使用msg_id引發使用sp_addmessage創建的用戶定義消息時，raiserror上指定嚴重性將覆蓋sp_addmessage中定義的嚴重性。

任何用戶可以指定0-18直接的嚴重級別。隻有sysadmin固定服務器角色常用或具有alter trace權限的用戶才能指定19-25直接的嚴重級別。19-25之間的安全級別需要使用with log選項。

# state：介於1至127直接的任何整數。State默認值是1。

raiserror('is error', 16, 1);
select * from sys.messages;
--使用sysmessages中定義的消息
raiserror(33003, 16, 1);
raiserror(33006, 16, 1);

總結

本篇文章就到這裡瞭，希望能夠給你帶來幫助，也希望您能夠多多關註WalkonNet的更多內容!

SQL Server的存儲過程詳解

目錄

存儲過程的概念

存儲過程的優點

系統存儲過程

用戶自定義存儲過程

1、創建語法

2、創建不帶參數存儲過程

3、修改存儲過程

4、帶參存儲過程

5、帶通配符參數存儲過程

6、帶輸出參數存儲過程

7、不緩存存儲過程

8、加密存儲過程

9、帶遊標參數存儲過程

10、分頁存儲過程

Raiserror

總結

推薦閱讀：

發佈留言取消回覆

近期文章

目錄

存儲過程的概念

存儲過程的優點

系統存儲過程

用戶自定義存儲過程

1、 創建語法

2、 創建不帶參數存儲過程

3、 修改存儲過程

4、 帶參存儲過程

5、 帶通配符參數存儲過程

6、 帶輸出參數存儲過程

7、 不緩存存儲過程

8、 加密存儲過程

9、 帶遊標參數存儲過程

10、 分頁存儲過程

Raiserror

總結

推薦閱讀：

發佈留言 取消回覆

近期文章

標籤

1、創建語法

2、創建不帶參數存儲過程

3、修改存儲過程

4、帶參存儲過程

5、帶通配符參數存儲過程

6、帶輸出參數存儲過程

7、不緩存存儲過程

8、加密存儲過程

9、帶遊標參數存儲過程

10、分頁存儲過程

發佈留言取消回覆