SQL Server EXECUTE指令及STORED PROCEDURE

EXECUTE
簡寫：EXEC

中文直翻叫執行，可以執行預存程序、內建的指令等等
這邊特別介紹的是EXECUTE的超能力
有的時候，需要將字串串在一起使其成為SQL語法
可以用EXECUTE直接將 字串 變成可執行的 指令
使操作上更為靈活

--實踐EXECUTE超能力→----------------------------
--將文字轉成SQL指令(只要符合SQL語法)--------------
USE 練練
GO 
DECLARE @name VARCHAR(10)
DECLARE @sql VARCHAR(MAX)
SET @name='巨巨'
SET @sql='SELECT * FROM '

EXECUTE (@sql+@name)

SET @name='匠匠'
EXECUTE (@sql+@name)
--等同於：SELECT * FROM 巨巨--------------------

STORED PROCEDURE
簡寫：PROC

預存程序，很好理解，把某些計算或常用的查詢
先存下來，使用者在呼叫時可以更快速的反應
而且可以一次存多筆查詢，使用上只需要呼叫一次
就能一次得到多個答案，相當方便

--建一個其本查詢----------------------------
CREATE PROCEDURE 巨巨產品
AS
 SELECT 產品編號,品名,價錢 
 FROM 巨巨
 ORDER BY 價錢 DESC
GO 

--DROP PROCEDURE 巨巨產品--DROP掉-----------
--改剛剛巨巨產品的內容------------------------
ALTER PROCEDURE 巨巨產品(
      @price1 MONEY=0,
      @price2 MONEY=10000,
      @discount NUMERIC(5,2)=1.0,
      @avg MONEY OUTPUT,
      @sum MONEY OUTPUT)
AS
 DECLARE @count INT
 
 SELECT @avg = AVG(價錢),@sum = SUM(價錢) 
 FROM 巨巨;
 
 SELECT 產品編號,品名,價錢,價錢*@discount as 折扣價
 FROM 巨巨
 WHERE 價錢 BETWEEN @price1 AND @price2
 ORDER BY 價錢 DESC;

 SET @count = @@ROWCOUNT
 RETURN @count
GO


--執行上面的預存程序-------------------------------
DECLARE @aa MONEY
DECLARE @bb MONEY
DECLARE @cc INT
DECLARE @dd NUMERIC(5,2)
SET @dd = 0.9

EXECUTE @cc = 巨巨產品 DEFAULT,DEFAULT, @dd ,@aa OUTPUT,@bb OUTPUT
PRINT @aa
PRINT @bb
PRINT @cc


/*以下幾個是參數運用時的參考-----------------------
--EXECUTE 巨巨產品
--EXECUTE 巨巨產品 50
--EXECUTE 巨巨產品 20,100
--EXECUTE 巨巨產品 DEFAULT,100
--EXECUTE 巨巨產品 @price2=50
--EXECUTE 巨巨產品 @price2=100,@price1=20
*/---------------------------------------------
-----------------------------------------------

再來除了查詢，也可以建表，當然也可以刪除或是修改
應用相當廣泛。

另外預存程序隔一段時間要進行
WITH RECOMPILE

因資料可能隨時間而增加不少，當初的預存程序需要
重新編譯才能最佳化，簡單的例子就是：
如果只有5頁的書，直接翻開來找就好
但是時間拉長，書變成5000頁，直接找太久了
應該要看一下索引再找才能最佳化
WITH RECOMOILE就是再重編譯
使找資料的方法更有效率

CREATE PROC 建一堆表
AS
  CREATE TABLE T1(欄1 INT,欄2 NCHAR(5));
  CREATE TABLE T2(欄1 INT,欄2 NCHAR(5));
  CREATE TABLE T3(欄1 INT,欄2 NCHAR(5));
  CREATE TABLE T4(欄1 INT,欄2 NCHAR(5));
  CREATE TABLE T5(欄1 INT,欄2 NCHAR(5));
  CREATE TABLE T6(欄1 INT,欄2 NCHAR(5));
GO

EXEC 建一堆表

CREATE PROC 刪一堆表
AS
  DROP TABLE T1,T2,T3,T4,T5,T6
GO

EXEC 刪一堆表

-----------------------------------------------
EXEC 建一堆表 WITH RECOMPILE
EXEC sp_recompile 巨巨產品