使用存儲過程

--使用存儲過程

/*存儲過程

使用的大多數SQL語句都是針對一個或多個表的單條語句，

并非所有的操作都這麼簡單，經常會有一個完整的操作需要多條語句才能完成。例如，考慮以下情形

1、為了外理訂單，需要核對以保證庫存中有相應的物品；

2、如果庫存有物品，這些物品需要預定以使不将它們賣給别的人，并且要減少可用物品數量以後眏正确的庫存量

3、存中沒有的物品需要訂購，這需要與供應商進行某種互動

4、關于哪些物品入庫（并且可以發貨）和哪些物品退訂，需要通知相應的客戶。

執行這個處理需要許多表及多條T-SQL語句，需要執行的具體語句及其次序也不是固定的，這就需要建立存儲過程。

就是為以後的使用而儲存一條或多條T-SQL語句的集合，可将其視為批檔案，雖然它的作用不僅限于批處理。

為什麼要使用存儲過程呢？

1、通過把處理封裝在容易使用的單元中，簡化複雜的操作（正如前面的例子所述）

2、由于不要求反複建立一系列的處理步驟，這保證了資料的完整性。如果所有開發人員和應用程式都使用同一（試

   驗和測試）存儲過程，則所用的代碼都是相同的。

   這一點延伸就是防止錯誤，需要執行的步驟越多，出錯的可能性就越大，防止錯誤保證了資料的一緻性。

3、簡化對變動的管理，如果表名、列名或業務邏輯越多（或别的内容）的變化，隻需要更改存儲過程和代碼。使用

   它的人員甚至不需要知道這些變化。

   這一些延伸就是安全性。通過存儲過程限制對基礎資料的通路減少了資料錯誤的機會。

4、提高性能。因為存儲過程比使用單獨的SQL語句要快

5、存在一些隻能用在單個請求中的T-SQL語言元素和SQL Server特性，存儲過程可以用它們來編寫功能更靈活的代碼。

  顯然使用存儲過程有3個好處：即簡單、安全、高性能。雖然他們很重要，不過在将SQL轉換為存儲過程前，必須知

道它的一些缺陷：

1、一般來說，存儲過程有的編寫比基本的SQL語句工複雜，編寫存儲過程需要更高的技能及豐富的經驗。

2、你可能沒有建立存儲過程的安全通路權限。許多資料庫管理者限制了存儲過程的建立權限，隻允許使用。*/

--使用存儲過程

--執行存儲過程：

EXECUTE productpricing @cheap OUTPUT,@expensive OUTPUT,@average OUTPUT

--執行名為：productpricing的存儲過程，它計算并傳回産品的最低、最高和平均價格。

--EXECUTE可以簡寫為：EXEC

--建立存儲過程

--建立一個傳回産品平均價格的存儲過程，以下是代碼：

--input

CREATE PROCEDURE productpricing as

BEGIN

SELECT avg(prod_price) as priceaverage

FROM products;

END;

--執行這個存儲

EXEC productpricing;

--删除存儲過程

DROP procedure productpricing

--以下是productpricing的修改版本

CREATE PROCEDURE productpricing

@price_min MONEY OUTPUT,

@price_max MONEY OUTPUT,

@price_avg MONEY OUTPUT

AS

  SELECT @price_min = min(prod_price)

  FROM products;

  SELECT @price_max = max(prod_price)

  SELECT @price_avg = avg(prod_price)

END

--使用修改後的存儲過程必須指定3個變量名：如下

DECLARE @cheap MONEY

DECLARE @expensive MONEY

DECLARE @average MONEY

EXEC productpricing @cheap output,

                    @expensive output,

                    @average output

--為了獲得3個值，可使用下面的語句。

select @cheap,@expensive,@average;

use studb

CREATE PROCEDURE ordertotal

@order_num int,

@order_total money output

  SELECT @order_total=sum(item_price*quantity)

  FROM orderitems

  WHERE order_num=@order_num;

--select order_num,item_price*quantity from orderitems

--@order_num用來傳遞一個值給存儲，是以不需要OUTPUT，@order_total定義為OUTPUT，因為要從存儲過程中傳回合計值。

DECLARE @order_total MONEY

EXEC ordertotal 20009,@order_total OUTPUT

SELECT @order_total

DROP PROCEDURE ordertotal

--建立智能存儲過程

/*考慮這個場景，你需要荼得與以前一樣的訂單合計，但需要對合計增加營業稅 ，不過隻針對某些顧客（或是你所在洲的那些

顧客），那麼，你需要做幾件事：

1、獲得合計

2、把營業稅 有條件地添加到合計

3、傳回合計（帶或不帶稅）

存儲過程的完整工作如下：*/

--Name:ordertotal

--Partmeters:@order_num   = order number

--           @taxable     =0 if not taxable,1 if taxable

--           @order_total =order total variable

CREATE PROCEDURE ordertotal

  @order_num int,

  @taxable bit,

  @order_total MONEY OUTPUT

  --DECLARE variable for total

  DECLARE @total MONEY;

  --declare tax percentage

  DECLARE @taxrate int;

  --SET tax rate (adjust as needed)

  SET @taxrate=6;

  --Get the order total

  SELECT @total=SUM(item_price*quantity)

  WHERE order_num=@order_num

  --is this taxable?

  IF @taxable=1

    --Yes,so add taxrate to the total

    SET @total=@total+(@total/100*@taxrate);

    --and finlly, save to output variable

    SELECT @order_total=@total;

END

--測試結果

--select SUM(item_price*quantity) FroM orderitems where order_num=20005