閱讀目錄
- 定義
- 優點
- 缺點
- 建立存儲過程
-
- 建立不帶參數的存儲過程
- 建立帶參數的存儲過程
-
- 1.不帶預設值的參數
- 2.帶預設值的參數
- 帶傳回參數的存儲過程
- 建立存儲過程的優化
- 修改存儲過程
- 删除存儲過程
- 系統存儲過程
定義
存儲過程（Stored Procedure）是一種在資料庫中存儲複雜程式，以便外部程式調用的一種資料庫對象。
存儲過程是為了完成特定功能的SQL語句集，經編譯建立并儲存在資料庫中，使用者可通過指定存儲過程的名字并給定參數(需要時)來調用執行。
你可以了解為其他程式設計語言中的函數或者子程式
優點
- 将重複性很高的一些操作，封裝到一個存儲過程中，簡化了對這些SQL的調用
- 批量處理：可以直接批量運作存儲過程中的多個SQL語句
- 提高速度：由于存儲過程是先編譯再執行的，同時可以批量執行語句，是以存儲過程能夠實作較快的執行速度并減少網絡時間消耗
- 統一接口，同時将業務邏輯隐藏，確定資料的安全
缺點
- 存儲過程，往往定制化于特定的資料庫上，因為支援的程式設計語言不同。當切換到其他廠商的資料庫系統時，需要重寫原有的存儲過程。
- 存儲過程的性能調校與撰寫，受限于各種資料庫系統。
建立存儲過程
建立存儲過程的文法為：
-- 适用于SQL Server 與 Azure SQL Database
CREATE [ OR ALTER ] { PROC | PROCEDURE }
[schema_name.] procedure_name [ ; number ]
[ { @parameter [ type_schema_name. ] data_type }
[ VARYING ] [ = default ] [ OUT | OUTPUT | [READONLY]
] [ ,...n ]
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ]
AS { [ BEGIN ] sql_statement [;] [ ...n ] [ END ] }
[;]
<procedure_option> ::=
[ ENCRYPTION ]
[ RECOMPILE ]
[ EXECUTE AS Clause ]
注意不同的資料庫的文法可能會有些許不同。
看到這個文法是不是很複雜，但是不用擔心，常用的參數其實很少。在這篇文章中我們不會介紹所有參數，是以想要了解所有參數可以檢視官網文檔：https://docs.microsoft.com/zh-cn/sql/t-sql/statements/create-procedure-transact-sql?view=sql-server-ver15
建立不帶參數的存儲過程
文法為：
create proc | procedure pro_name
as
SQL_statements
其中pro_name為自定義的存儲過程名稱，SQL_statements為SQL語句。proc和procedure選擇其中一個均可使用（proc是procedure的縮寫）
例如建立一個存儲過程，實作從student表中選取所有的資料
create proc proc_get_student
as
select * from student;
之後使用exec+存儲過程名即可調用存儲過程
exec proc_get_student
建立帶參數的存儲過程
建立帶參數的存儲過程首先要在存儲過程中聲明該參數，每個存儲過程參數都必須用唯一的名稱進行定義。與T-SQL變量相同，參數名必須以@為字首，并且遵從辨別符規則。當使用者不提供該參數的值時可以使用一個預設值來代替。
建立帶參數的存儲過程的基本語句為：
create proc | procedure pro_name
[{@參數資料類型} [=預設值] [output],
{@參數資料類型} [=預設值] [output],
....
]
as
SQL_statements
1.不帶預設值的參數
建立一個參數不帶預設值的存儲過程，在調用該存儲過程時，必須對存儲過程中的所有參數進行指派，如果有一個參數沒有指派，則無法調用該存儲過程。例如：
use db_student
--建立存儲過程
create procedure proc_group
@課程類别 varchar(20), --定義參數
@學分 int
as
select * from course
where 課程類别=@課程類别 and 學分>@學分
執行不帶參數的存儲過程就是：
use db_student
exec proc_group '歌曲',8
如果不按順序指派可以寫成：
use db_student
exec proc_group @學分=8,@課程類别=‘籃球課’
2.帶預設值的參數
在SQL中我們可以對字段進行預設值的限制，在存儲過程中也可以建立使用預設值的參數。隻要在參數的定義之後加上等号，并在等号後面寫出預設值即可。
--建立存儲過程
use db_student
create procedure proc_group
@課程類别 varchar(20)='體育課',
@學分 int=6
as
select * from course where 課程類别=@課程類别 and 學分>@學分
執行參數帶預設值的存儲過程
use db_student
exec proc_group @學分=8
帶傳回參數的存儲過程
建立存儲過程時，可以用output/out參數來建立一個帶傳回值的存儲過程，例如：
@a int output
如果建立帶傳回參數的存儲過程proc_group，那麼SQL語句如下：
--建立帶傳回值的存儲過程
create procedure proc_group
@課程類别 varchar(20),
@平均學分 int output --設定帶傳回值的參數
as
select @平均學分=avg(學分) from course where 課程類别=@課程類别
執行帶傳回值的存儲過程與普通的執行過程會有差別：
DECLARE @平均學分 int;
exec proc_group 'D', @平均學分 output
print (@平均學分)
建立存儲過程的優化
存儲過程是不允許重名的，是以有時我們會在建立存儲過程的語句中加一個判斷，如果已存在同名的存儲過程則删除
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;
修改存儲過程
使用alter修改存儲過程
alter proc proc_get_student
as
select * from student;
删除存儲過程
drop proc proc_get_student
系統存儲過程
系統存儲過程是系統建立的存儲過程，目的在于能夠友善的從系統表中查詢資訊或完成與更新資料庫表相關的管理任務或其他的系統管理任務。系統存儲過程主要存儲在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;--資料庫幫助，查詢資料庫資訊
個人公衆号：Smilecoc的雜貨鋪，歡迎關注！
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)