SQL Server の基本: ストアド プロシージャ

簡単に言えば、ストアド プロシージャは 1 つ以上の SQL ステートメントの集合であり、バッチ ファイルとみなすことができますが、その役割はバッチ処理に限定されません。この記事では、変数の使用法、ストアド プロシージャとストアド関数の作成、呼び出し、表示、変更、削除の操作を主に紹介します。

1: ストアド プロシージャの概要

SQL Server のストアド プロシージャは、T_SQL を使用して記述されたコード セグメントです。その目的は、システム テーブルから情報を簡単にクエリしたり、データベース テーブルの更新やその他のシステム管理タスクに関連する管理タスクを完了したりすることです。T_SQL ステートメントは、SQL Server データベースとアプリケーション プログラム間のプログラミング インターフェイスです。多くの場合、開発者は一部のコードを繰り返し記述することになりますが、毎回同じ機能のコードを記述すると、煩雑でエラーが発生しやすくなるだけでなく、SQL Server がステートメントを実行するため、システムの運用効率も低下します。一つずつ。

つまり、ストアド プロシージャとは、特定のタスクを達成するために、SQL Server が複数回呼び出す必要があるいくつかの固定操作ステートメントをプログラム セグメントに書き込み、これらのプログラム セグメントがサーバーに保存され、データベース サーバーが呼び出しを行うことを意味します。プログラムを通じて彼らをサポートします。

ストアド プロシージャの利点:

ストアド プロシージャは、作成時にのみコンパイルされ、実行するたびに再コンパイルする必要はありません。

ストアド プロシージャは、複雑なデータベース操作をカプセル化し、複数のテーブルの更新や削除などの操作プロセスを簡素化できます。

モジュール式プログラミングを実現でき、ストアド プロシージャを複数回呼び出すことができるため、統一されたデータベース アクセス インターフェイスが提供され、アプリケーションの保守性が向上します。

ストアド プロシージャでコードのセキュリティを強化できるため、ストアド プロシージャで参照されるオブジェクトを直接操作できないユーザーのために、SQL Server は指定されたストアド プロシージャに対するユーザーの実行権限を設定できます。

ストアド プロシージャは、ネットワーク トラフィックを削減できます。ストアド プロシージャ コードは、クライアントとサーバー間の通信プロセス中に、大量の T_SQL コード トラフィックが生成されません。

ストアド プロシージャの欠点:

データベースの移植は不便です。ストアド プロシージャはデータベース管理システムに依存します。SQL Server ストアド プロシージャにカプセル化されたオペレーション コードを他のデータベース管理システムに直接移植することはできません。

オブジェクト指向設計をサポートしておらず、オブジェクト指向の方法で論理ビジネスをカプセル化することも、サービスをサポートできる一般的なビジネス ロジック フレームワークを形成することさえできません。

コードは可読性が低く、保守が困難です。クラスタリングはサポートされていません。

2: ストアド プロシージャの分類

1. システム ストアド プロシージャ

システム ストアド プロシージャは、SQL Server システム自体によって提供されるストアド プロシージャであり、さまざまな操作を実行するためのコマンドとして使用できます。

システム ストアド プロシージャは、主にシステム テーブルから情報を取得するために使用されます。システム ストアド プロシージャを使用すると、データベース サーバーの管理が完了し、システム管理者にヘルプが提供され、ユーザーがデータベース オブジェクトを表示しやすくなります。 sp_ 以降、システム ストアド プロシージャはシステム定義データベースおよびユーザー定義データベースで定義され、呼び出し時にストアド プロシージャの前にデータベース修飾名を追加する必要はありません。例: sp_rename システム ストアド プロシージャは、現在のデータベース内のユーザー作成オブジェクトの名前を変更できます。sp_helptext ストアド プロシージャは、SQL SERVER サーバー内のルール、デフォルト値またはビュー、および多くの管理タスクのテキスト情報を表示できます。システム ストアド プロシージャを実行することで完了しますが、多くのシステム情報もシステム ストアド プロシージャを実行することで取得できます。

システム ストアド プロシージャはシステム データベース マスターに作成され、保存されます。一部のシステム ストアド プロシージャはシステム管理者のみが使用できますが、一部のシステム ストアド プロシージャは承認を通じて他のユーザーも使用できます。

2. ユーザー ストアド プロシージャ (カスタム ストアド プロシージャ)

カスタム ストアド プロシージャは、特定のビジネス要件を達成するために、ユーザーがユーザー データベース内の T_SQL ステートメントを使用して作成した T_SQL ステートメントのコレクションです。結果と情報をクライアントに返し、出力パラメータを返します。カスタム ストアド プロシージャを作成する場合、ストアド プロシージャ名の前に「##」を追加すると、グローバル一時ストアド プロシージャが作成されることを示し、ストアド プロシージャの前に「#」を追加すると、ローカル一時ストアド プロシージャが作成されることを示します。ローカル一時ストアド プロシージャは、それが作成されたセッション内でのみ使用でき、セッションが終了すると削除されます。どちらのストアド プロシージャも tempdb データベースに保存されます。

ユーザー定義ストアド プロシージャは、T_SQL と CLR の 2 つのカテゴリに分類されます。

T_SQL: ストアド プロシージャは、ユーザー指定のパラメータを受け入れたり返したりできる、値が保存された T_SQL ステートメントのコレクションです。ストアド プロシージャは、データベースから返されることもあります。クライアントアプリケーションデータに。

CLR ストアド プロシージャは、Microsoft.NET Framework 共通言語を使用するメソッド ストアド プロシージャを指し、ユーザーが提供するパラメータを受け取り、返すことができます。これらは、.NET Framework アセンブリ内のクラスのパブリック静的メソッドとして実装されます。

3. 拡張ストアド プロシージャ

拡張ストアド プロシージャは、SQL SERVER 環境の外部で実行される動的接続 (DLL ファイル) として実装され、SQL SERVER インスタンスが実行されているアドレス空間にロードして実行できます。 SQL SERVER 拡張ストアド プロシージャ API プログラミングを使用して実行される場合、拡張ストアド プロシージャは接頭辞 "xp_" によって識別されます。ユーザーにとって、拡張ストアド プロシージャは標準中国語ストアド プロシージャと同じであり、同じメソッドで実行できます。

3: ストアド プロシージャを作成する

仕事をうまくやりたい場合は、まず次のようにデータを準備する必要があります:

use sample_db;
--创建测试books表
create table books (
 book_id int identity(1,1) primary key,
 book_name varchar(20),
 book_price float,
 book_auth varchar(10)
);
--插入测试数据
insert into books (book_name,book_price,book_auth)
 values
 ('论语',25.6,'孔子'),
 ('天龙八部',25.6,'金庸'),
 ('雪山飞狐',32.7,'金庸'),
 ('平凡的世界',35.8,'路遥'),
 ('史记',54.8,'司马迁');

パラメーターなしでストアド プロシージャを作成する

--1.创建无参存储过程
if (exists (select * from sys.objects where name = 'getAllBooks'))
 drop proc proc_get_student
go
create procedure getAllBooks
as
select * from books;
--调用,执行存储过程
exec getAllBooks;

ストアド プロシージャを変更する

alter procedure dbo.getAllBooks 
as
select book_auth from books;

ストアド プロシージャを削除する

drop procedure getAllBooks;

名前を変更するストアド プロシージャ

sp_rename getAllBooks,proc_get_allBooks;

パラメータを使用して作成するストアド プロシージャ
ストアド プロシージャのパラメータは、入力パラメータと出力パラメータの 2 種類に分けられます

入力パラメータ: での値の転送と同様に、ストアド プロシージャに値を渡すために使用されますJava言語とかCとか。

出力パラメータ: Java 言語での参照渡しと同様に、ストアド プロシージャを呼び出した後の会議参加結果に使用されます。

値による転送と参照による転送の違い:

基本データ型への代入は値による転送であり、参照型間の代入は参照による転送です。
値渡しでは実際の変数値が渡され、参照渡しではオブジェクトの参照アドレスが渡されます。
値が渡された後、2 つの変数はそれぞれの値を変更します。参照が渡された後、2 つの参照は同じオブジェクトの状態を変更します

(1) 1 つのパラメーターを持つストアド プロシージャ

if (exists (select * from sys.objects where name = 'searchBooks'))
 drop proc searchBooks
go
create proc searchBooks(@bookID int)
as
 --要求book_id列与输入参数相等
 select * from books where book_id=@bookID;
--执行searchBooks
exec searchBooks 1;

(2) 2 つのパラメーターを持つストアド プロシージャ

if (exists (select * from sys.objects where name = 'searchBooks1'))
 drop proc searchBooks1
go
create proc searchBooks1(
 @bookID int,
 @bookAuth varchar(20)
)
as
 --要求book_id和book_Auth列与输入参数相等
 select * from books where book_id=@bookID and book_auth=@bookAuth;
exec searchBooks1 1,'金庸';

(3) 戻り値を含むストアド プロシージャを作成する

MySQL

if (exists (select * from sys.objects where name = 'getBookId'))
 drop proc getBookId
go
create proc getBookId(
 @bookAuth varchar(20),--输入参数,无默认值
 @bookId int output --输入/输出参数 无默认值
)
as
 select @bookId=book_id from books where book_auth=@bookAuth
--执行getBookId这个带返回值的存储过程
declare @id int --声明一个变量用来接收执行存储过程后的返回值
exec getBookId '孔子',@id output
select @id as bookId;--as是给返回的列值起一个名字
if (exists (select * from sys.objects where name = 'getBookId'))
 drop proc getBookId
go
create proc getBookId(
 @bookAuth varchar(20),--输入参数,无默认值
 @bookId int output --输入/输出参数 无默认值
)
as
 select @bookId=book_id from books where book_auth=@bookAuth
--执行getBookId这个带返回值的存储过程
declare @id int --声明一个变量用来接收执行存储过程后的返回值
exec getBookId '孔子',@id output
select @id as bookId;--as是给返回的列值起一个名字

(4) ワイルドカードを使用してストアド プロシージャを作成する

if (exists (select * from sys.objects where name = 'charBooks'))
 drop proc charBooks
go
create proc charBooks(
 @bookAuth varchar(20)='金%',
 @bookName varchar(20)='%'
)
as 
 select * from books where book_auth like @bookAuth and book_name like @bookName;
--执行存储过程charBooks
exec charBooks '孔%','论%';

(5) 暗号化句を使用してストアド プロシージャ

を暗号化し、ストアド プロシージャのテキストを非表示にする次の例では、暗号化プロセスを作成し、sp_helptext システム ストアド プロシージャを使用して暗号化プロセスに関する情報を取得し、syscomments テーブルから直接プロセスに関する情報を取得しようとします。

if (object_id('books_encryption', 'P') is not null)
 drop proc books_encryption
go
create proc books_encryption 
with encryption
as 
 select * from books;
--执行此过程books_encryption
exec books_encryption;
exec sp_helptext 'books_encryption';--控制台会显示"对象 'books_encryption' 的文本已加密。"

(6)ストアド プロシージャをキャッシュしません

--with recompile不缓存
if (object_id('book_temp', 'P') is not null)
 drop proc book_temp
go
create proc book_temp
with recompile
as
 select * from books;
go
exec book_temp;
exec sp_helptext 'book_temp';

(7)。カーソル パラメーターを使用してストアド プロシージャを作成します

if (object_id('book_cursor', 'P') is not null)
 drop proc book_cursor
go
create proc book_cursor
 @bookCursor cursor varying output
as
 set @bookCursor=cursor forward_only static for
 select book_id,book_name,book_auth from books
 open @bookCursor;
go
--调用book_cursor存储过程
declare @cur cursor,
 @bookID int,
 @bookName varchar(20),
 @bookAuth varchar(20);
exec book_cursor @bookCursor=@cur output;
fetch next from @cur into @bookID,@bookName,@bookAuth;
while(@@FETCH_STATUS=0)
begin 
 fetch next from @cur into @bookID,@bookName,@bookAuth;
 print 'bookID:'+convert(varchar,@bookID)+' , bookName: '+ @bookName
 +' ,bookAuth: '+@bookAuth;
end
close @cur --关闭游标
DEALLOCATE @cur; --释放游标

(8)。ページング ストアド プロシージャを作成します

if (object_id('book_page', 'P') is not null)
 drop proc book_page
go
create proc book_page(
 @TableName varchar(50), --表名
 @ReFieldsStr varchar(200) = '*', --字段名(全部字段为*)
 @OrderString varchar(200), --排序字段(必须!支持多字段不用加order by)
 @WhereString varchar(500) =N'', --条件语句(不用加where)
 @PageSize int, --每页多少条记录
 @PageIndex int = 1 , --指定当前为第几页
 @TotalRecord int output --返回总记录数
)
as
begin
 --处理开始点和结束点
 Declare @StartRecord int;
 Declare @EndRecord int; 
 Declare @TotalCountSql nvarchar(500); 
 Declare @SqlString nvarchar(2000); 
 set @StartRecord = (@PageIndex-1)*@PageSize + 1
 set @EndRecord = @StartRecord + @PageSize - 1 
 SET @TotalCountSql= N'select @TotalRecord = count(*) from ' + @TableName;--总记录数语句
 SET @SqlString = N'(select row_number() over (order by '+ @OrderString +') as rowId,'+@ReFieldsStr+' from '+ @TableName;--查询语句
 --
 IF (@WhereString! = '' or @WhereString!=null)
 BEGIN
 SET @TotalCountSql=@TotalCountSql + ' where '+ @WhereString;
 SET @SqlString =@SqlString+ ' where '+ @WhereString; 
 END
 --第一次执行得到
 --IF(@TotalRecord is null)
 -- BEGIN
 EXEC sp_executesql @totalCountSql,N'@TotalRecord int out',@TotalRecord output;--返回总记录数
 -- END
 ----执行主语句
 set @SqlString ='select * from ' + @SqlString + ') as t where rowId between ' + ltrim(str(@StartRecord)) + ' and ' + ltrim(str(@EndRecord));
 Exec(@SqlString) 
END
--调用分页存储过程book_page
exec book_page 'books','*','book_id','',3,1,0;
--
declare @totalCount int
exec book_page 'books','*','book_id','',3,1,@totalCount output; 
select @totalCount as totalCount;--总记录数。

。