SQLサマリーストアドプロシージャ

概念

ストアド プロシージャ: 実行可能プロシージャにプリコンパイルされた 1 つ以上の SQL ステートメント。

ストアド プロシージャ構文の作成

CREATE proc | procedure procedure_name
    [{@参数数据类型} [=默认值] [output],
     {@参数数据类型} [=默认值] [output],
     ....
    ]
as
    SQL_statements
go

ストアド プロシージャと SQL ステートメントの比較

利点:

1. パフォーマンスの向上
SQL ステートメントは、プロシージャの作成時に分析およびコンパイルされます。 ストアド プロシージャは、初めて実行されるときにプリコンパイルされ、クエリ オプティマイザーによって分析および最適化され、最終的にシステム テーブルに保存されるストレージ プランが提供されます。これにより、プロシージャの実行時にこのオーバーヘッドが節約されます。 。
2. ネットワーク オーバーヘッドの削減
ストアド プロシージャを呼び出すときは、ストアド プロシージャ名と必要なパラメータ情報を指定するだけで済むため、ネットワーク トラフィックが削減されます。
3. コードの移植を容易にする
データベースの専門家はいつでもストアド プロシージャを変更できますが、アプリケーションのソース コードには影響を与えないため、プログラムの移植性が大幅に向上します。
4. より強力なセキュリティ
1) システム管理者は、権限のないユーザーがデータにアクセスするのを避けるために、実行中の特定のストアドプロセスの権限を制限できます
2) ネットワーク経由でプロセスを呼び出す場合、呼び出された実行プロセスのみが表示されます。 したがって、悪意のあるユーザーはテーブルやデータベースのオブジェクト名を確認したり、独自の Transact-SQL ステートメントを埋め込んだり、重要なデータを検索したりすることができません。
3) プロシージャ パラメーターを使用すると、SQL インジェクション攻撃を回避できます。 パラメーター入力は実行可能コードではなくリテラル値として扱われるため、攻撃者がプロシージャ内の Transact-SQL ステートメントにコマンドを挿入してセキュリティを侵害することがより困難になります。
4) プロセスは暗号化できるため、ソース コードを難読化するのに役立ちます。

短所:

1. ストアド プロシージャの保守には専門のデータベース開発者が必要ですが、実際にはプログラム開発者はパートタイムで作業することが多いです。

2. ストアド プロシージャの設計ロジックの変更や変更は SQL ほど柔軟ではありません。

実際、アプリケーションではストアド プロシージャがあまり使用されないのはなぜですか?

通常のプロジェクト開発では、ストアド プロシージャが使用されることは比較的まれですが、これはなぜでしょうか。
その理由は以下のとおりです: 1) 特定のデータベース開発者が存在せず、一般のプログラマーがパートタイムでデータベース操作を実行します
2) プログラマーは、多くの場合、データベース上で開発する必要がなく、データアクセスを完了するためのプログラムを操作するだけで済みます
3) プロジェクト 要件は頻繁に変更されるため、特にロジックの変更が含まれる場合、SQL ステートメントを変更する方が便利です。

ストアド プロシージャと SQL ステートメントのどちらを選択するか?

実際のアプリケーションの経験に基づいて、次の提案が行われます:

1. 高効率または高度な標準化要件がある一部のプロジェクトでは、ストアド プロシージャを使用することをお勧めします。

2. 一般的なプロジェクトの場合は、パラメーター化されたコマンドを使用することをお勧めします。メソッド (ストアド プロシージャと SQL ステートメントは妥協の方法です)
3. 要件が高く、複数のデータ ロジックが関与する一部のアルゴリズムでは、ストアド プロシージャを使用することをお勧めします

ストアド プロシージャの特定のアプリケーション

1 . 基本的なクエリ

1. パラメーターを使用してストアド プロシージャを作成します

例: 生徒の総数をクエリします

--查询存储过程
IF OBJECT_ID (N'PROC_SELECT_STUDENTS_COUNT', N'P') IS NOT NULL
    DROP procedure PROC_SELECT_STUDENTS_COUNT;
GO
CREATE procedure PROC_SELECT_STUDENTS_COUNT
AS 
    SELECT COUNT(ID) FROM Students
GO

実行ステートメント:

EXEC PROC_SELECT_STUDENTS_COUNT

3.

ワイルドカード、パラメーター値を割り当てる場合、対応するワイルドカードを追加します

--查询存储过程，根据城市查询总数
IF OBJECT_ID (N'PROC_SELECT_STUDENTS_BY_CITY_COUNT', N'P') IS NOT NULL
    DROP procedure PROC_SELECT_STUDENTS_BY_CITY_COUNT;
GO
CREATE procedure PROC_SELECT_STUDENTS_BY_CITY_COUNT(@city nvarchar(50))
AS
    SELECT COUNT(ID) FROM Students WHERE City=@city
GO

実行:

EXEC PROC_SELECT_STUDENTS_BY_CITY_COUNT N'Beijing'

4. 出力パラメーターを使用して

--3、查询姓氏为李的学生信息，含通配符
IF OBJECT_ID (N'PROC_SELECT_STUDENTS_BY_SURNNAME', N'P') IS NOT NULL
    DROP procedure PROC_SELECT_STUDENTS_BY_SURNNAME;
GO
CREATE procedure PROC_SELECT_STUDENTS_BY_SURNNAME
    @surnName nvarchar(20)='李%' --默认值
AS 
    SELECT ID,Name,Age FROM Students WHERE Name like @surnName
GO

2. ストアド プロシージャを使用して追加、削除、変更します

1.

生徒情報を追加

EXEC PROC_SELECT_STUDENTS_BY_SURNNAME
EXEC PROC_SELECT_STUDENTS_BY_SURNNAME N'李%'
EXEC PROC_SELECT_STUDENTS_BY_SURNNAME N'%李%'

実行:

--根据姓名查询的学生信息，返回学生的城市及年龄
IF OBJECT_ID (N'PROC_SELECT_STUDENTS_BY_NAME', N'P') IS NOT NULL
    DROP procedure PROC_SELECT_STUDENTS_BY_NAME;
GO
CREATE procedure PROC_SELECT_STUDENTS_BY_NAME
    @name nvarchar(50),     --输入参数
    @city nvarchar(20) out, --输出参数
    @age  int output        --输入输出参数
AS 
    SELECT @city=City,@age=Age FROM Students WHERE Name=@name AND Age=@age
GO

2. 変更

生徒IDに基づいて生徒情報を更新

--执行
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;

実行:

--1、存储过程：新增学生信息
IF OBJECT_ID (N'PROC_INSERT_STUDENT', N'P') IS NOT NULL
    DROP procedure PROC_INSERT_STUDENT;
GO
CREATE 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

3. IDに基づいて生徒記録を削除

EXEC PROC_INSERT_STUDENT 1001,N'张三',19,'ShangHai'

実行:

IF OBJECT_ID (N'PROC_UPDATE_STUDENT', N'P') IS NOT NULL
    DROP procedure PROC_UPDATE_STUDENT;
GO
CREATE 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=@id
GO

Three、ページングクエリを実装するストアドプロシージャ

1. ページングに row_number 関数を使用します

EXEC PROC_UPDATE_STUDENT 1001,N'张思',20,'ShangHai'

実行:

--3、存储过程：删除学生信息
IF OBJECT_ID (N'PROC_DELETE_STUDENT_BY_ID', N'P') IS NOT NULL
    DROP procedure PROC_DELETE_STUDENT_BY_ID;
GO
CREATE procedure PROC_DELETE_STUDENT_BY_ID
    @id int
AS 
    DELETE FROM  Students WHERE ID=@id
GO

2. 従来のトップページングを使用します

EXEC PROC_DELETE_STUDENT_BY_ID 1001

Four 。その他の機能:

1. ストアド プロシージャを実行するたびに再コンパイルします

--分页查询
IF OBJECT_ID (N'PROC_SELECT_BY_PAGE', N'P') IS NOT NULL
    DROP procedure PROC_SELECT_BY_PAGE;
GO
CREATE procedure PROC_SELECT_BY_PAGE
    @startIndex int,
    @endIndex int
AS 
    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 @endIndex
GO

2. ストアド プロシージャを暗号化します

暗号化後、ソース スクリプトを表示および変更することはできません

EXEC PROC_SELECT_BY_PAGE 1,10

実行:

--使用TOP分页
IF OBJECT_ID (N'PROC_SELECT_BY_PAGE_WITH_TOP', N'P') IS NOT NULL
    DROP procedure PROC_SELECT_BY_PAGE_WITH_TOP;
GO
CREATE procedure PROC_SELECT_BY_PAGE_WITH_TOP
    @pageIndex int,
    @pageSize int
AS 
    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

効果、スクリプトを作成するためにスクリプトを表示またはエクスポートすることはできません