--上課內容：第七章 存儲過程及觸發器


-- 7.1 存儲過程的創建和使用

  -- 7.1.1 存儲過程的概念

    -- 存儲過程是一種數據庫對象

    -- 存儲過程創建時就被編譯和優化，調用一次以后，就保存在內存，下次調用直接執行，執行起來比單個語句快

    -- 可以將某個特定任務的代碼段寫在存儲過程里，通過用戶定義的存儲過程名進行多次調用。

   -- 特點：

    -- 1. 可以在一個存儲過程里執行一系列 SQL 語句

    -- 2. 存儲過程之間可以相互調用

    -- 3. 存儲過程可以接受輸入參數并以輸出參數的形式將多個值返回至調用過程或批處理

    -- 4. 存儲過程向調用過程或批處理返回狀態值，以表明成功或失敗（以及失敗原因）


   -- 功能：

    -- 1. 通過存儲過程的使用，可以簡化復雜 SQL 語句

    -- 2. 存儲過程可以被多個用戶共享和重用

    -- 3. 可以加快程序的運行速度

    -- 4. 可以提高數據庫的安全性


   -- 創建存儲過程的原則：

    -- 1. 只能在當前數據庫中創建存儲過程。

    -- 2. 數據庫的所有者可以創建存儲過程，也可以授權其他用戶創建存儲過程。

    -- 3. 存儲過程是數據庫對象，其名稱必須遵守標識符命名規則。

    -- 4. 存儲過程可以根據表、視圖來創建

    -- 5. 不能將 CREATE  PROCEDURE 語句與其它 SQL 語句組合到單個批處理中。

    -- 6. 創建存儲過程時，應指定所有輸入參數和向調用過程或批處理返回的輸出參數、執行數據庫操作的編程語句和返回至調用過程或批處理以表明成功或失敗的狀態值


   -- 分類：

    -- 1. 系統存儲過程：系統自動創建的，存儲在master數據庫里

    -- 2. 用戶自定義的存儲過程

    -- 3. 臨時存儲過程：

 
  -- 7.1.2 存儲過程的分類和創建方法

    -- 語法：

     -- 簡單存儲過程：

        CREATE PROCEDURE 存儲過程名 AS 查詢語句

     -- 帶參數的存儲過程

        CREATE PROCEDURE 存儲過程名 [@局部變量名 數據類型]  AS 查詢語句(要使用局部變量)

     -- 帶返回值的存儲過程

        CREATE PROCEDURE 存儲過程名 [@局部變量名 數據類型] [OUTPUT] AS 查詢語句(要使用局部變量)


    -- 7.1.2.1 創建不帶參數的存儲過程

CREATE PROCEDURE stu_pro1 AS select * from exam

-- 執行該存儲過程

stu_pro1

execute stu_pro1

-- 修改存儲過程

ALTER PROCEDURE stu_pro1 AS select * from exam where t_number='20040301'

 


   -- 7.1.2.2 創建帶參數簡單的存儲過程

CREATE PROCEDURE stu_pro3 @tnum char(8) AS select * from exam where t_number=@tnum

-- 執行該存儲過程

stu_pro3 '20040302'

execute stu_pro3 '20040301'

execute stu_pro3 @tnum='20040301'


 -- 我們經常把需要復雜計算的工作交給計算機來做，但做法是要你預先寫好，編譯好的

    -- 7.1.2.3 創建帶參數的存儲過程,并根據執行結果返回不同的值

     -- OUTPUT 返回參數，將信息返回給調用過程

CREATE PROCEDURE stu_pro4 @tnum char(8),@cnum char(6),@grade char(10) output
 AS 
select  @grade=t_grade from exam where t_number=@tnum and c_number=@cnum

-- 執行
declare @tnum char(8),@cnum char(6),@grade char(10)
select @tnum=20040301,@cnum=100101
execute stu_pro4 @tnum,@cnum,@grade output
select @tnum as '學號',@cnum as '課程號', @grade as '成績'


-- 我們還可以返回一個存儲過程在執行過程中是否出錯的信息

-- 例：帶多個參數，有返回值，又可以返回程序執行成功與否的存儲過程

CREATE PROCEDURE stu_pro5 @tnum char(8),@cnum char(6),@grade char(10) output 
 AS 
declare @errorvalue int
set @errorvalue=0
select  @grade=t_grade from exam where t_number=@tnum and c_number=@cnum
if (@@ERROR<>0)
  set @errorvalue=@@ERROR
return @errorvalue

-- 執行

declare @returnvalue int,@tnum char(8),@cnum char(6),@grade char(10)
select @tnum=20040301,@cnum=100101
execute @returnvalue=stu_pro5 @tnum,@cnum,@grade output
select @returnvalue as '返回是否出錯'
select @grade as '學號為20040301，課程號為100101的成績'

  -- 7.1.3 查看stu_pro3信息sp_help stu_pro3

  -- 查看存儲過程信息
sp_help  stu_pro1

  -- 查看stu_pro1定義文本

sp_helptext stu_pro1

  -- WITH ENCRYPTION 參數

CREATE PROCEDURE stu_pro1 AS select * from exam

ALTER PROCEDURE stu_pro1 WITH ENCRYPTION AS select * from exam 

  -- 注意：WITH ENCRYPTION 和視圖的一樣，在修改時沒寫就是默認可以讓他人看到你存儲過程的定義


  -- 查看存儲過程目錄信息
sp_stored_procedures stu_pro3

  -- 查看存儲過程相關性
sp_depends stu_pro3


  -- 7.1.4 重命名存儲過程
sp_rename 舊存儲過程名，新存儲過程名 
sp_rename stu_pro1,stu_pro

  -- 7.1.5 刪除存儲過程
Drop procedure stu_pro


-- 準備工作：

create table exam
(
t_number char(8),
c_number char(6),
t_grade decimal(5,2)
)

insert into exam values('20040301','100101',79)
insert into exam values('20040301','100102',88)
insert into exam values('20040302','100101',90)
insert into exam values('20040302','100103',75)
insert into exam values('20040303','100101',79)
insert into exam values('20040303','100102',75)
insert into exam values('20040303','100103',95)
insert into exam values('20040304','100102',43)
insert into exam values('20040304','100103',68)
insert into exam values('20040305','100101',64)
insert into exam values('20040305','100102',87)
insert into exam values('20040305','100103',92)


-- 練習：

-- 1. 創建存儲過程pro_exam，顯示exam表中的所有記錄


-- 2. 執行存儲過程pro_exam


-- 3. 查欄存儲過程pro_exam的定義文本


-- 4. 修改存儲過程pro_exam，加密定義文本，并驗證


-- 5. 查看存儲過程pro_exam的相關性信息


-- 6. 查看存儲過程pro_exam的目錄信息


-- 7. 重命名存儲過程pro_exam 為 pro_exam1,并查看


-- 8. 刪除存儲過程pro_exam1


-- 9. 創建一個帶有參數的存儲過程pro_exam2，該存儲過程根據傳入的學生編號、課程號顯示該學生的考試成績


-- 10. 執行存儲過程pro_exam2,查看學號為'20040301'課程號為'100101'的成績


-- 11. 使用pubs 數據庫authors表，創建一個帶有參數的存儲過程pro_au，該存儲過程傳入作者所在的州，顯示所有這個州的所有作者信息


-- 12. 執行存儲過程pro_au,查看所有state 為 'CA'州的所有作者信息


-- 13. 使用northwind 數據庫order details表，創建一個帶參數的存儲過程pro_sale
    -- 該存儲過程根據傳入的訂單號、產品號，計算銷售額（=unitprice*quantity*(1-diacount)）
    -- 根據程序執行結果返回不同的值，程序成功返回0，程序失敗返回錯誤號

select * from [order details]


--  14. 執行存儲過程pro_sale，計算訂單號為10248產品號為11的銷售額







-- 練習參考答案：

-- 1. 創建存儲過程pro_exam，顯示exam表中的所有記錄
create procedure pro_exam as select * from exam

-- 2. 執行存儲過程pro_exam
pro_exam
-- 或
execute pro_exam

-- 3. 查欄存儲過程pro_exam的定義文本
sp_helptext pro_exam

-- 4. 修改存儲過程pro_exam，加密定義文本，并驗證
alter  procedure pro_exam  with encryption as select * from exam
sp_helptext pro_exam

-- 5. 查看存儲過程pro_exam的相關性信息
sp_depends pro_exam

-- 6. 查看存儲過程pro_exam的目錄信息
sp_stored_procedures pro_exam 

-- 7. 重命名存儲過程pro_exam 為 pro_exam1,并查看
sp_rename pro_exam,pro_exam1
sp_help pro_exam1

-- 8. 刪除存儲過程pro_exam1
drop procedure pro_exam1

-- 9. 創建一個帶有參數的存儲過程pro_exam2，該存儲過程根據傳入的學生編號、課程號顯示該學生的考試成績
create procedure pro_exam2 @tnum char(8),@cnum char(6) as select * from exam where t_number=@tnum and c_number=@cnum

-- 10. 執行存儲過程pro_exam2,查看學號為'20040301'課程號為'100101'的成績
pro_exam2 '20040301','100101'
--或
execute pro_exam2 '20040301','100101'
--或
execute pro_exam2 @tnum='20040301',@cnum='100101'

-- 11. 使用pubs 數據庫authors表，創建一個帶有參數的存儲過程pro_au，該存儲過程傳入作者所在的州，顯示所有這個州的所有作者信息
create procedure pro_au @state char(2) as select * from authors where state=@state 

-- 12. 執行存儲過程pro_au,查看所有state 為 'CA'州的所有作者信息
pro_au 'CA'
--或
execute pro_au 'CA'
--或
execute pro_au @tnum='CA'

-- 13. 使用northwind 數據庫order details表，創建一個帶參數的存儲過程pro_sale
    -- 該存儲過程根據傳入的訂單號、產品號，計算銷售額（=unitprice*quantity*(1-discount)）
    -- 根據程序執行結果返回不同的值，程序成功返回0，程序失敗返回錯誤號

select * from [order details]

CREATE
alter PROCEDURE pro_sale @oid int,@pid int ,@sale decimal(10,2)output
 AS 
declare @errorvalue int
set @errorvalue=0
select @sale=unitprice*quantity*(1-discount) from [order details] where @oid=orderid and @pid=productid 
if (@@ERROR<>0)
  set @errorvalue=@@ERROR
return @errorvalue

--  14. 執行存儲過程pro_sale，計算訂單號為10248產品號為11的銷售額
declare @returnvalue int,@oid int,@pid int,@sale decimal(10,2)
select @oid=10248,@pid=11
execute @returnvalue=pro_sale @oid,@pid,@sale output
select @returnvalue as '返回是否出錯'
select @sale as '銷售額'