SQL-Based ATM business storage process

#1 SQL-Based ATM business storage process

 

/* Create an account table */

/* ATM service! */
Create Table bank
(
Bid int Primary Key Identity (1000,1 ),
-- Serial number
Bcid numeric (1000000000000000) default rand () * 100000000000000 unique check (bcid> = ),
-- The bank card number, which is limited to 15 digits and should be automatically assigned by the System
-- Here we use the rand function to obtain a random value. After the last 15 zeros of * 1, we can ensure that the maximum value is no more than 15 digits. The check constraint is 14 zeros.
-- Make sure that the minimum value is 15 bits, and add the unique constraint to prevent random numbers from getting repeated values.
Bname varchar (10 ),
-- Owner name
Bpassword numeric (6, 0 ),
-- Password, limited to 6 digits
Bmoney money
-- Account balance
)
-- Select * from bank

-- Drop table bank
Insert into Bank (bname, bpassword, bmoney) values ('Lee sehan ', 111111,000000)
Insert into Bank (bname, bpassword, bmoney) values ('dugang', 222222,1)
Insert into Bank (bname, bpassword, bmoney) values ('zhang jun', 333333,7551)
Insert into Bank (bname, bpassword, bmoney) values ('wang huang', 444444,80000)
Insert into Bank (bname, bpassword, bmoney) values ('jun li ', 555555,756)
Insert into Bank (bname, bpassword, bmoney) values ('Weekly peng ', 666666,890)
-- The insert operation may be subject to check constraints and an error is reported. It is mainly because the number of random numbers obtained by bcid may be less than 15 bits. Just execute it again.


/* Open an account */
Create proc padd @ name varchar (10), @ pass numeric (6, 0), @ money Money
-- @ Name, @ pass, @ money parameter 1 account name, parameter 2 account password, parameter 3 account deposit
As
Begin
If Len (ltrim (rtrim (@ name) = 0
Begin
Print 'do you have a name? '
Return
End
-- Prevent empty or blank usernames
If @ pass <100000 or @ pass> 999999

Begin
Print 'Enter the six-digit password, that is, add one to your fingers on your left. Thank you'
Return
End
If (@ money <10)

Begin
Print 'Big Brother, do you have 10 yuan? Opening an account requires at least 10 yuan'
Return
End

Insert into bank select rand () * 1000000000000000, @ name, @ pass, @ money
-- Insert a new record, that is, opening an account
If @ rowcount <> 1

Begin
Print 'An error occurred. Please try again. Sorry! '
Return
-- If an error is reported here, the reason is that the random number is not enough to be checked.
End
Else

Begin
Print 'account opening successful! '
End
End

Exec padd 'zhang wei', 777777,10
-- Parameter 1 account name, parameter 2 account password, parameter 3 account deposit
-- Drop proc padd

/* Deposit */

Create proc plus @ CID numeric (15, 0), @ pass numeric (6, 0), @ money Money
-- Parameter 1: The card number to be read by the ATM. Parameter 2: password. Parameter 3 is the deposit amount.
As
Begin
If convert (INT, @ money) % 100 <> 0 or @ money = 0
Begin
Print 'the Inventory Machine only accepts the face amount of 100 yuan! '
Return
End
-- Modulo 100 to ensure that the deposit amount can be fully divided by 100. Convert must be forcibly converted to int type.
Update Bank set bmoney = bmoney + @ money where bcid = @ CID and bpassword = @ pass

If @ rowcount <> 1
-- If the number of affected rows is not 1, an error may occur, including incorrect passwords.
Begin
Print 'your password is incorrect! Or your bank card has a problem. Please try again or contact the issuing bank'
Return
End
Else
Begin
Print 'deposit successful! You just saved '+ convert (varchar, @ money) +'. Thank you for your money! '
-- @ Money is of the money type. If the string is connected, convert must be converted to varchar.
End
End

Declare @ t numeric (15, 0)
Set @ t = (select bcid from bank where Bid = 1001)
-- The preceding two statements can not be executed in this way, but the random bcid value of the user needs to be identified,
-- This value should be different when you create tables. Therefore, when you add the preceding two statements to facilitate debugging, the random value is assigned to the variable first. The following questions are the same:
Exec plus @ T, 222222,0
-- Drop proc plus

/* Query the balance */
Create proc find @ CID numeric (15, 0), @ pass numeric (6, 0)
As
Begin
Declare @ temp int
Set @ temp = (select bmoney from bank where (bcid = @ CID) and (bpassword = @ pass ))
-- Assign the account balance value to @ temp
If @ rowcount <> 1
Begin
Print 'your password is incorrect! Or your bank card has a problem. Please try again or contact the issuing bank'
Return
End
Print 'your balance also has '+ convert (varchar, @ temp)
-- Forced conversion to varchar is used to connect strings
End

Declare @ t numeric (15, 0)
Set @ t = (select bcid from bank where Bid = 1002)
-- The preceding two statements can not be executed in this way, but the random bcid value of the user needs to be identified,
-- This value should be different when you create tables. Therefore, when you add the preceding two statements to facilitate debugging, the random value is assigned to the variable first. The following questions are the same:
Exec find @ T, 333333
-- Drop proc find

/* Change the password */

Create proc change @ CID numeric (15, 0), @ pass numeric (6, 0), @ newpass numeric (6, 0)
-- Parameter 1 Card Number Parameter 2 original password parameter 3 new password
As
Begin
If @ newpass <100000 or @ newpass> 999999

Begin
Print 'Enter the six-digit password, that is, add one to your fingers on your left. Thank you'
Return
End

Update Bank set bpassword = @ newpass where (bcid = @ CID) and (bpassword = @ pass)
-- Change the password
If @ rowcount <> 1
-- Verify that the original password is incorrect. If the execution is incorrect, the number of rows affected by update should be 0.
Begin
Print 'your original password is incorrect! Or your bank card has a problem. Please try again or contact the issuing bank'
Return
End
Else
Begin
Print 'your password has been changed! '
End
End
Declare @ t numeric (15, 0)
Set @ t = (select bcid from bank where Bid = 1004)
-- The preceding two statements can not be executed in this way, but the random bcid value of the user needs to be identified,
-- This value should be different when you create tables. Therefore, when you add the preceding two statements to facilitate debugging, the random value is assigned to the variable first. The following questions are the same:
Exec change @ T, 5555,787878
-- Drop proc change

/* Withdrawal */
Create proc dec @ CID numeric (15, 0), @ pass numeric (6, 0), @ money Money
As
Begin
If convert (INT, @ money) % 50 <> 0 or @ money = 0
-- Modulo 50 to ensure that the acquisition amount is an integer multiple of 50 (it will also be an integer multiple of 100)
Begin
Print 'the ATM only accepts RMB 50 yuan or 100 yuan! '
Return
End
Declare @ temp money
Set @ temp = (select bmoney from bank where (bcid = @ CID) and (bpassword = @ pass ))
If (@ temp-@ money) <0
-- If the balance is less than the amount, the balance is insufficient.
Begin
Print 'your balance is insufficient !!! '
Return
End
Update Bank set bmoney = bmoney-@ money where bcid = @ CID and bpassword = @ pass
-- Change the balance minus the withdrawal amount
If @ rowcount <> 1
Begin
Print 'your password is incorrect! Or your bank card has a problem. Please try again or contact the issuing bank'
Return
End
Else
Begin
Print 'withdrawal successful! You just pulled '+ convert (varchar, @ money) +', please get the card, or else I will eat it! '
End
End

Declare @ t numeric (15, 0)
Set @ t = (select bcid from bank where Bid = 1006)
-- The preceding two statements can not be executed in this way, but the random bcid value of the user needs to be identified,
-- This value should be different when you create tables. Therefore, when you add the preceding two statements to facilitate debugging, the random value is assigned to the variable first. The following questions are the same:
Exec dec @ T, 444444,50000

/* Cancel the account */

Create proc pdel @ CID numeric (15, 0), @ pass numeric (6, 0)
-- @ Name, @ pass, @ money
As
Begin

If @ pass <100000 or @ pass> 999999

Begin
Print 'Enter the six-digit password, that is, add one to your fingers on your left. Thank you'
Return
End

Delete from bank where (bcid = @ CID) and (bpassword = @ pass)
-- Delete the user record, that is, cancel the account
If @ rowcount <> 1

Begin
Print 'An error occurred. Please try again. Sorry! '
Return
End
Else

Begin
Print 'you have nothing to do with our bank! '
End
End

Declare @ t numeric (15, 0)
Set @ t = (select bcid from bank where Bid = 1005)
-- The preceding two statements can not be executed in this way, but the random bcid value of the user needs to be identified,
-- This value should be different when you create tables. Therefore, when you add the preceding two statements to facilitate debugging, the random value is assigned to the variable first. The following questions are the same:
Exec pdel@ T, 666666