SQL 預存程序 Stored Procedures

一直都不知到啥是預存程序，後來大概知道它是做什麼用的，直到現在才開始面對它，大概了解一下它怎麼用。

出處:http://yes.nctu.edu.tw/SQL/StoredProcedure/StoredProcedure.htm

撰寫 Stored Procedures

利用 ADO 來存取資料庫是一件既輕鬆又簡單的工作, 不過有的時候又臭又長的 SQL 敘述實在很煩人, 這篇文章將介紹如何利用 Stored Procedure 來簡化你的 ASP 程式

本質上 Stored procedures (有時又稱 Sproc) 就是儲存在資料庫中的 Script 或 Batch, 撰寫 Sproc 並不是太難的一件事, 通常你會這樣查詢資料庫

Select * From Table

如何將它變成 Sproc? 以下是簡單的例子

Create Proc sp_SprocName1
As
  Select * From Table
Go

現在 Sproc 已成功的建立, 你可以試著去執行它

Exec sp_SprocName1

你可以為你的 Sproc 取任何的名字, 在 SQL 7.0 的命名原則以 128 個字元為限, SQL 並不規定 Stores procedure 之命名要以 sp_ 開頭, 但強烈建議你這麼做!

接下來我們再做些例子, 例如在查詢中加入 where 子句, Select * From Table Where Column1 = 0 , 這裡你是不是會覺得 Column1 的值如果可以改為一個輸入參數由我們輸入, 那就更加完美!

Create Proc sp_SprocName2
@MyInput int
As
  Select * From Table
  Where Column1 = @MyInput
Go

完整的參數宣告語法為

@parameter_name datatype [= default|Null] [Varying] [Output]

如果你未提供 default 值, 則這個參數會被假設為必要輸入的, 在宣告參數時你還必須指定資料型態, 應為合法的 SQL Server 資料型態

在 Sproc 接收到輸入值後, 便將它存放在 @MyInput 變數中, 變數以 @ 符號開頭, 以 @@ 開頭的則為全域變數, 你可以這麼執行這個 Sproc

Exec sp_SprocName2 0

我們再來看一個 Insert 的例子

Create Proc sp_SprocName3
  @Column1 varchar(20)
  @Column2 varchar(36) = Null
As
  Insert Into Table
  Values
    (@Column1, @Column2)
Go

在這例子中, Column2 的值即為選擇性輸入, 你可以這麼執行

Exec sp_SprocName3 ‘SomeString’

最後要告訴你如何在 ASP 中使用 Stored procedure, 不使用 Sproc 時你會這麼做

Set oConn = Server.CreateObject("Adodb.Connection")
oConn.Open YpurDSN
sSQL = "Select * From Table"
Set oRs = oConn.Execute(sSQL)

使用 Sproc 時, 這樣改就可以了

Set oConn = Server.CreateObject("Adodb.Connection")
oConn.Open YourDSN
sSQL = "sp_MySproc"
Set oRs = oConn.Execute(sSQL)

如果你的 Sproc 需要輸入參數, 你可以這麼做

Set oConn = Server.CreateObject("Adodb.Connection")
oConn.Open YourDSN
sSQL = "sp_MySproc " & MyInput
Set oRs = oConn.Execute(sSQL)

是不是很簡單呢!

希望這篇文章對你有幫助!