[SQL筆記] 預存程序(stored procedure) 二

模擬銀行業務
[SQL筆記] 預存程序(stored procedure) 一

• 寫SP輸入參數：@參數名 資料型態
• 寫SP輸出參數：@sum 資料型態 ouput，輸出參數後面加上ouput

• 調用輸出參數SP：exec proc_sp名稱 輸入參數的值,@sum output，輸出參數後面加上ouput

• floor(rand()*10)隨機數：select floor(rand()*10)

SP基本結構

建立SP

create proc proc_SP名
    --這裡寫傳入&輸出的參數
    @num int,--傳入的參數
    @sum money output--輸出的參數，加上output
as
    --這裡寫業務邏輯
go

調用SP

--調用SP
--(1)宣告變數，定義輸出參數，用來存放結果
declare @sum money
--(2)調用SP，輸出參數要加上output
exec proc_SelectExChange 100,@sum output
--(3)可以select輸出參數看結果
select @sum

範例4：有輸入參數，有輸出參數的SP

語法

create proc proc_SP名
    --傳入的參數
    @num int,
    --輸出的參數，加上output
    @sum money output
as
    --這裡寫業務邏輯
go

調用SP

--調用SP
--(1)宣告變數，定義輸出參數，用來存放結果
declare @sum money
--(2)調用SP，輸出參數要加上output
exec proc_SelectExChange 100,@sum output
--(3)可以select輸出參數看結果
select @sum

實作

--(4)有輸入參數，有輸出參數的SP
--查詢出某時間段的銀行存取信息，以及存款總金額，取款總金額
--傳入開始時間、結束時間，顯示存取款交易信息的同時，返回存款總金額，取款總金額

create proc proc_SelectExChange
    @start varchar(20),--開始時間
    @end varchar(20),--結束時間
    @sumIn money output,--存款總金額
    @sumOut money output--取款總金額
as
    --查詢出某時間段的銀行存取信息，以及存款總金額，取款總金額
    --存款總金額
    select @sumIn = (select sum(MoneyInBank) from CardExchange
where ExchangeTime between @start + ' 00:00:00' and @end + ' 23:59:59')
    --取款總金額
    select @sumOut = (select sum(MoneyOutBank) from CardExchange
where ExchangeTime between @start + ' 00:00:00' and @end + ' 23:59:59')
    --顯示存取款交易信息
    select * from CardExchange where ExchangeTime between @start + ' 00:00:00' and @end + ' 23:59:59'
go

--調用SP
--(1)宣告變數，定義輸出參數，用來存放結果
declare @sumIn money
declare @sumOut money
--(2)調用SP，輸出參數要加上output
exec proc_SelectExChange '2023-5-5','2023-5-15',@sumIn output,@sumOut output
--(3)可以select輸出參數看結果
select @sumIn
select @sumOut

範例5：具有同時輸出輸入參數的SP

rand()

--(5)具有同時輸出輸入參數的SP
--密碼升級，傳入卡號和密碼，如果卡號密碼正確，並且密碼長度<8，自動升級成8位密碼
create proc proc_PwdUpgrade
  @CardNo nvarchar(20),--卡號
  @Pwd nvarchar(20) output --密碼
as
  if not exists(select * from BankCard where CardNo = @CardNo and CardPwd = @Pwd)
	--不存在，密碼給空值
	set @Pwd=''
  else
  begin --因為有一段邏輯要寫，用begin-end包起來
    --判斷傳進來的密碼<8，就升級
	if len(@Pwd) < 8
	begin
	    declare @len int = 8 - len(@Pwd)
		declare @i int = 1--用來循環
		while @i <= @len
		begin 
		  set @Pwd = @Pwd + cast(floor(rand()*10)as varchar(1))--密碼加上隨機數
		  set @i = @i + 1--循環自增，每循環一次就加1，不然會變成死循環
		end
		--新密碼產生後，要更新資料表
		update BankCard set CardPwd = @Pwd where CardNo = @CardNo
	end
  end
go

--調用SP
declare @pwd varchar(20) = '123456' --輸出參數有給值，所以同時具備「輸入&輸出」
exec proc_PwdUpgrade '999998888800',@pwd output
select @pwd

--查看結果
select * from BankCard

隨機數：select floor(rand()*10)

怎麼區分這個參數同時具備了「輸入&輸出」?

怎麼區分這個參數同時具備了「輸入&輸出」，取決於調用SP的時候。
如果在調用SP時，定義的輸出參數有給值的話，就是同時具備了「輸入&輸出」。

也就是說，output到底是用來輸出，還是既輸入又輸出，是看調用它的地方，是否有給它值來決定。

調用SP的時候，如果有輸出參數，就必須要先宣告定義輸出參數，用來存放結果，此時，如果也要讓這個變數可以成為輸入參數，就在後面賦值：

• declare @sum money：只用來輸出
• declare @sum money = 100：同時具備了「輸入&輸出」

--建立SP
create proc proc_PwdUpgrade
  @CardNo nvarchar(20),--卡號(輸入參數)
  @Pwd nvarchar(20) output --密碼(輸出參數，所以後面加上output)
as
    --這裡是業務邏輯
go

--調用SP
declare @pwd varchar(20) = '123456' --輸出參數有給值，所以同時具備「輸入&輸出」
exec proc_PwdUpgrade '999998888800',@pwd output --調用SP時，輸出參數後面要加上ouput
select @pwd--以select輸出參數看結果

https://www.bilibili.com/video/BV1XV411C7TP?p=31