SQL 四大動作

Stored Procedue (預存程序)

1. 可帶入、帶出參數
2. 可有回傳值 (只能 INT)
3. 方便調整
4. 可以一次存多筆查詢
5. 可刪表、建表、輸入資料、刪資料、更新
6. 時間久資料量大時，可用 WITH RECOMOILE (重新編譯)，使查詢更有效率

--CREATE PROCEDURE

CREATE PROC ProductionSP
AS
  SELECT ProductionId,Name,Price
  FROM Production
  ORDER BY Price DESC
GO

EXECUTE ProductionSP



--可給變數，就可以查

ALTER PROC ProductionSP @price1 MONEY,@price2 MONEY
AS
  SELECT ProductionId,Name,Price
  FROM Production 
  WHERE Price BETWEEN @price1 AND @price2
  ORDER BY Price DESC
GO


EXECUTE ProductionSP 50,80




--給變數預設值，以防沒輸入參數時發生錯誤也可以更靈活運用

ALTER PROC ProductionSP @price1 MONEY=0,@price2 MONEY=10000
AS
  SELECT ProductionId,Name,Price
  FROM Production 
  WHERE Price BETWEEN @price1 AND @price2
  ORDER BY Price DESC
GO


--輸入變數的方式
EXECUTE ProductionSP  
EXECUTE ProductionSP 50,80
EXECUTE ProductionSP 50
EXECUTE ProductionSP @price2=20
EXECUTE ProductionSP @price2=50,@price1=30




--也可以有帶出參數( Ex:@avg MONEY OUTPUT )
--也可以有傳回值,但傳回值只能是 INT

ALTER PROC ProductionSP @price1 MONEY=0,@price2 MONEY=10000
 ,@sum MONEY OUTPUT,@avg MONEY OUTPUT
AS
  
  DECLARE @count INT
 
  SELECT @sum=SUM(Price),@avg=AVG(Price)
  FROM Production
  WHERE Price BETWEEN @price1 AND @price2;
  
  SELECT ProductionId,Name,Price
  FROM Production
  WHERE Price BETWEEN @price1 AND @price2
  ORDER BY Price DESC;

  SET @count=@@ROWCOUNT
  RETURN @count
GO

--為了在 SQL 顯示而打的變數而已
DECLARE @ss MONEY
DECLARE @aa MONEY
DECLARE @cc INT

--實際的語法
EXECUTE @cc=ProductionSP 50,80,@ss OUTPUT,@aa OUTPUT

--只是為了秀給自己看
PRINT @ss
PRINT @aa
PRINT @cc




--Stroed Procedue不只可以查詢，也可以做其他的 Ex:建表、刪表

CREATE PROC CreateSomeTablesSP
AS
  CREATE TABLE T1 (Id INT,Name NCHAR(5));
  CREATE TABLE T2 (Id INT,Name NCHAR(5));
  CREATE TABLE T3 (Id INT,Name NCHAR(5));
  CREATE TABLE T4 (Id INT,Name NCHAR(5));
  CREATE TABLE T5 (Id INT,Name NCHAR(5));
  CREATE TABLE T6 (Id INT,Name NCHAR(5));
GO

EXEC CreateSomeTablesSP



CREATE PROC DeleteSomeTablesSP
AS
  DROP TABLE T1,T2,T3,T4,T5,T6
GO

EXEC DeleteSomeTablesSP