關於SQL 預存程序入門基礎

大學裡面對預存程序沒有講到什麼，工作了一段時間，對預存程序還是沒有用到，根本不需要去寫存儲過程，可能是做的軟體方向的原因吧。為了以後發展，決定從零開始學習下。

這裡看看預存程序的定義,

預存程序(Stored Procedure),是一組為了完成特定功能的SQL 語句，集經編譯後儲存在資料庫中，使用者透過指定預存程序的名字並給參數，如果該預存程序帶有參數來執行。

在SQL Server 的系列版本中，預存程序分為兩類：系統提供的預存程序和使用者自訂預存程序。

　　系統SP，主要存儲master 資料庫中，並以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注入式的攻擊，而且可以將Grant、Deny以及Revoke權限套用到預存程序。

 

   好了，我們來看看建立的基本語法

定义存储过程的语法
    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」的預存程序。你可以打開“企業管理器”，選擇當前操作的資料庫，然後在左邊的樹型列表中選擇"可編程性->存儲過程"，此時就可以在右邊的列表中看到你剛剛創建的存儲過程了（如果沒有，刷新一下即可）。

看到了在資料中呼叫預存程序的方法有兩種（這裡EXEC與EXECUTE等效）；

      EXEC  過程名稱  參數值1，參數值2，....

或或

=參數值1，參數2=參數值2....

上面我們也看到了。

 如果要刪除一個預存過程，用drop

像這樣

drop PROCEDURE UserLogin

創建這樣的預存過程有什麼用呢，不是只能在數據中查看賽。

我們在做web或者winform 程序，假設需要個登錄，好，我們可以調用用這個存儲過程來登錄，根據傳入的參數，如果查詢出來有記錄，那麼這條記錄在數據庫中存在，表示登錄成功，否則失敗。

這樣做的目的更加安全，可以防止sql注入。