Use stored procedures to implement the following applications:
Transfer from one account to the specified amount to another account.
*/Build Table: Create TABLE account (AccountNum int,uname char (+), total float)
Insert into account VALUES (1001, ' U1 ', 50000)
Insert into account values (1002, ' U2 ', 1000)
SELECT * FROM Account
To build stored procedures:
CREATE PROCEDURE TRANSFER
@outAccount int,
@inAccount int,
@amount
FLOAT as
DECLARE @totalDeposit FLOAT
BEGIN TRANSACTION/* Check the balance of the transferred account */SELECT @totalDeposit =total from Accounts WHE RE [email protected]
IF @totalDeposit is NULL/* The transfer out account does not exist or there is no deposit in the account * * BEGIN ROLLBACK TRANSACTION
PRINT ' Transfer out of account does not exist or no deposit in account, cannot operate! ‘
RETURN
END
IF @totalDeposit < @amount/* Transfer out account Insufficient deposit * *
BEGIN
ROLLBACK TRANSACTION
PRINT ' Transfer out account is not enough to operate! '/* Recommended use of function RAISERROR (' Transfer out of account deposit is not enough to operate! ', 10, 1) */
RETURN
END
if exists (SELECT * from account WHERE [e-mail protected])/* Check for the presence of a transfer * * BEGIN
UPDATE account SET [email protected] WHERE [email protected]/* Modify the transfer out accounts minus the transfer amount */
UPDATE account SET [email protected] WHERE [email protected]/* Modify transfer to accounts, increase Plus transfer out amount */
Commit TRANSACTION/* Submit a Transfer Transaction */
PRINT ' Transfer is successful, thank you for using this stored procedure! ‘
RETURN
END
ELSE
BEGIN
ROLLBACK TRANSACTION
PRINT ' into account does not exist, cannot operate! ‘
RETURN
END
Classic stored procedures; SQl Stored Procedures