模擬銀行業務
預存程序(stored procedure)
跟函數概念很像，他們的本質區別在：函數特點是在SQL語句中被調用，而SP預存程序是可以由外面來調用，例如在C#、Java中調用。
- 建立SP：
create proc proc_SP名稱 - 刪除SP：
drop porc proc_SP名稱 - 調用SP：
exec proc_SP名稱 - SQL的SP返回值必須整數
語法1：沒有輸入參數，沒有輸出參數
1
2
3
4
| create proc proc_SP名稱
as
--SQL語句
go
|
語法2：有輸入參數，沒有輸出參數
1
2
3
4
5
6
7
| create proc proc_SP名稱
--寫要帶入的參數
@CardNo varchar(30),
@Money money
as
--SQL語句
go
|
語法3：有輸入參數，沒有輸出參數，但有返回值
1
2
3
4
5
6
7
8
9
10
11
12
13
| --建立SP
create proc proc_SP名稱
--寫要帶入的參數
@CardNo varchar(30),
@Money money
as
--SQL語句
--使用@@ERROR，0代表成功
if @@ERROR <> 0 --失敗
return -1
--SQL語句
return 1--成功
go
|
調用有返回值的SP
declare 宣告變數，保存返回值- 調用SP：
exec 變數 = sp名 參數1, 參數2 select 變數查看返回值
1
2
3
4
| --調用SP
declare @ReturnValue int --declare宣告變數，保存返回值
exec @ReturnValue = proc_WithdrawMoney '999998888802',2000 --調用：exec 變數 = sp名 參數1, 參數2
select @ReturnValue --select查看返回值
|
範例1：沒有輸入參數，沒有輸出參數的SP
語法
1
2
3
4
| create proc proc_SP名稱
as
--SQL語句
go
|
實作
- 先把兩個連接表的查詢寫出來
inner join - 再加上
create proc建立sp
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
| --(1)沒有輸入參數，沒有輸出參數的SP預存程序
--定義SP預存程序實現：查出帳戶餘額最低的銀行卡帳戶信息，顯示銀行卡號、姓名、帳戶餘額
--方案一：只能查出一個，不好
select top 1 AccountCode 銀行卡號, RealName 姓名, CardMoney 帳戶餘額
from BankCard
inner join AccountInfo on BankCard.AccountId = AccountInfo.AccountId
order by CardMoney
--方案二：(餘額最低，有多人都可以查出來)
--where CardMoney = 餘額最低 (用子查詢)
create proc proc_MinMoneyCard
as
select AccountCode 銀行卡號, RealName 姓名, CardMoney 帳戶餘額
from BankCard
inner join AccountInfo on BankCard.AccountId = AccountInfo.AccountId
where CardMoney =
(select min(CardMoney) from BankCard)
go
--調用SP預存程序
exec proc_MinMoneyCard
|
範例2：有輸入參數，沒有輸出參數的SP
語法
1
2
3
4
5
6
7
| create proc proc_SP名稱
--寫要帶入的參數
@CardNo varchar(30),
@Money money
as
--SQL語句
go
|
實作
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
| --(2)有輸入參數，沒有輸出參數的SP預存程序
--模擬銀行卡存錢操作，傳入銀行卡號、存錢金額，實現存錢操作
create proc proc_Deposit
--寫要帶入的參數
@CardNo varchar(30),
@Money money
as
--TODO其實裡面應該要寫個Begin Transaction(交易)，但沒有加上去
--存錢
update BankCard set CardMoney = CardMoney + @Money
where CardNo = @CardNo
--產生交易記錄
insert into CardExchange(CardNo,MoneyInBank,MoneyOutBank,ExchangeTime)
values(@CardNo,@Money,0,GETDATE())
go
--調用SP預存程序
exec proc_Deposit '999998888803',1000
|
其實裡面應該要寫個Begin Transaction(交易)，但沒有加上去
[SQL筆記] Begin Transaction 交易(事務)
範例3：有輸入參數，沒有輸出參數，但有返回值的SP
語法
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
| --建立SP
create proc proc_SP名稱
--寫要帶入的參數
@CardNo varchar(30),
@Money money
as
--SQL語句
--使用@@ERROR，0代表成功
if @@ERROR <> 0 --失敗
return -1
--SQL語句
return 1--成功
go
--調用SP
declare @ReturnValue int --declare宣告變數，保存返回值
exec @ReturnValue = proc_WithdrawMoney '999998888802',2000 --調用：exec 變數 = sp名 參數1, 參數2
select @ReturnValue --select查看返回值
|
實作
先將BankCard 改造一下，加個約束：CardMoney>=0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
| --(3)有輸入參數，沒有輸出參數，但有返回值的SP預存程序(返回值必須整數)
--模擬銀行卡取錢操作，傳入銀行卡號、存錢金額，實現取錢操作
--取錢成功，返回1，取成失敗返回-1
create proc proc_WithdrawMoney
@CardNo varchar(30),
@Money money
as
--TODO其實裡面應該要寫個Begin Transaction(交易)，但沒有加上去
--取錢
update BankCard set CardMoney = CardMoney - @Money
where CardNo = @CardNo
--使用@@ERROR，0代表成功
if @@ERROR <> 0 --失敗
return -1
--產生交易記錄
insert into CardExchange(CardNo,MoneyInBank,MoneyOutBank,ExchangeTime)
values(@CardNo,0,@Money,GETDATE())
return 1
go
--調用SP
declare @ReturnValue int --保存返回值
exec @ReturnValue = proc_WithdrawMoney '999998888802',2000 --調用
select @ReturnValue --查看返回值
|
SQL的SP返回值必須整數
https://www.bilibili.com/video/BV1XV411C7TP?p=30