SQL ストアド プロシージャの使用を開始するための基本について

大学ではストアドプロシージャについて何も言及されていません。しばらく働いても、ストアドプロシージャをまったく使用する必要がありません。これは、ソフトウェアの方向性によるものかもしれません。今後の発展のために、一から勉強することにしました。

ストアド プロシージャの定義は次のとおりです。ストアド プロシージャ (ストアド プロシージャ) は、特定の関数を実行するための一連の SQL ステートメントであり、ユーザーがストアド プロシージャの名前を指定してデータベースに保存します。パラメーター。ストアド プロシージャがパラメーターを使用して実行される場合。

SQL Server のシリーズ バージョンでは、ストアド プロシージャは、システム提供のストアド プロシージャとユーザー定義のストアド プロシージャの 2 つのカテゴリに分類されます。

システム SP は主にマスター データベースに格納され、接頭辞として sp_ が付けられます。システム ストアド プロシージャは主にシステム テーブルから情報を取得し、システム管理者向けに SQL Server を管理します。

一般的に使用されるシステム ストアド プロシージャは次のとおりです。

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;

ユーザー定義ストアド プロシージャはユーザーによって作成され、ユーザーが必要とするデータ情報をクエリするストアド プロシージャなどの特定の機能を実行できます。

ここでは、ストアド プロシージャの利点を見ていきます

(1) 再利用。ストアド プロシージャは再利用できるため、データベース開発者の作業負荷が軽減されます。

(2) パフォーマンスを向上させます。ストアド プロシージャは作成時にコンパイルされ、将来使用するときに再コンパイルする必要はありません。一般的な SQL ステートメントは実行するたびにコンパイルする必要があるため、ストアド プロシージャを使用すると効率が向上します。

(3) ネットワークトラフィックを削減します。ストアド プロシージャはサーバー上に配置されているため、呼び出すときはストアド プロシージャの名前とパラメータを渡すだけで済むため、ネットワーク上で送信されるデータの量が削減されます。

(4) 安全性。パラメーター化されたストアド プロシージャは SQL インジェクション攻撃を防ぐことができ、ストアド プロシージャにアクセス許可の付与、拒否、取り消しを適用できます。

それでは、作成の基本的な構文を見てみましょう

定义存储过程的语法
    CREATE  PROC[EDURE]  存储过程名
              @参数1  数据类型 = 默认值,
               …… ,
              @参数n  数据类型 OUTPUT
            AS
            SQL语句
    GO
,参数是可选的
,参数分为输入参数、输出参数
,输入参数允许有默认值
  这里来创建一个简单的存储过程
 
CREATE PROCEDURE UserLogin
@name varchar(20),
@password varchar(20)
AS
-- 定义一个临时用来保存密码的变量
--DECLARE @strPwd NVARCHAR(20) 这里先不介绍变量。稍后的文章会详细讲到
BEGIN
select * from userinfo where userName=@name and userPass=@password
END
GO
首先我们用简单的sql查询
select * from userinfo where userName='admin'
查询结果：
---------------------
UserName  UserPass
Admin      Admin
现在我们来执行我们的存储过程
exec UserLogin admin,admin
--或这样调用：
EXEC UserLogin @name='admin',@password='admin'
查询结果：
---------------------
UserName  UserPass
Admin      Admin

SQL SERVER では、すべてのユーザー定義変数は "@" で始まり、OUTPUT キーワードは、このパラメーターが出力、AS に使用されることを示します。その後にストアド プロシージャの内容が続きます。上記のコードが「クエリ アナライザー」で一度実行される限り、SQL SERVER は現在のデータベースに「UserLogin」という名前のストアド プロシージャを作成します。 「Enterprise Manager」を開き、現在操作しているデータベースを選択し、左側のツリーリストで「Programmability -> Stored Procedures」を選択すると、リストに先ほど作成したデータベースが表示されます。右側にストアド プロシージャがあります (ない場合は更新してください)。

データ内のストアド プロシージャを呼び出すには 2 つの方法があることがわかりました (ここでは EXEC と EXECUTE は同等です)。 1 = パラメータ値 1、パラメータ 2 = パラメータ値 2....

上でも見ました。

ストアド プロシージャを削除したい場合は、次のように Drop

drop PROCEDURE UserLogin

Web または winform プログラムを作成しているとします。ログインが必要だとします。ログインするには、このストアド プロシージャを呼び出します。渡されたパラメーターに従って、クエリにレコードが存在することになります。データベースはログイン成功を示し、そうでない場合は失敗を示します。

これはより安全であり、SQL インジェクションを防ぐことができます。