sql_day_3

-----------------------------day_3----------------
--------------------存储过程----------------------
create procedure GetAllProducts
as
select * from Products

exec GetAllProducts

------------------创建一个存储过程执行任务，没有参数别写括号-----------
---------------具有输入参数和返回值的过程-----seafood是默认参数-------------
alter proc GetAvgPriceByCategoryName(@CatetoryName varchar(15) = 'seafood')
as
----如果选择的产品名称没有，则不执行后面的-------
if not exists (select * from Categories where CategoryName = @CatetoryName)
return 1

select avg(UnitPrice) as avgPrice
from Products
where CategoryId =
(
   select CategoryId from Categories
   where CategoryName = @CatetoryName
)
return 0;

declare @n int
----------执行成功返回0，错误返回1----------
exec @n = GetAvgPriceByCategoryName 'seafood'
select @n

-----------------------------------------------------------------
------------------------------------------------------------------
------------------------------------------------------------------
-----------------------模拟银行转账-------------------------------
create database BankSystem

use BankSystem
----------插入第一条数据的编号是10000，并且一次增加1--------
create table Accounts
(
[AccountId] int primary key identity (10000,1),
[Name] nvarchar(50) not null,
[CardNo] nvarchar(19) not null,
[Password] nvarchar(50) not null,
[RegisterDate] datetime not null,
[Address] nvarchar(255) not null,
[Phone1] nvarchar(20) not null,
[Phone2] nvarchar(20),
[Notes] text
)

---------创建银行账户信息存储过程-----------------------------------------
alter proc CreateAccount
---参数列表-----
@Name nvarchar(50),
@Password nvarchar(50),
@Address nvarchar(255),
@phone1 nvarchar(20),
@phone2 nvarchar(20),
@Notes text = null
as
    ----------保证一些参数唯一性------
    -----select floor(rand())生成随机数------
     declare @rand1 int ,@rand2 int
    -------------生成四位随机数----------
    select @rand1 = floor(rand()* (9999-1000) + 1000)
    select @rand2 = floor(rand()* (9999-1000) + 1000)
    declare @CardNo varchar(19)
    set @CardNo = '621222' + cast(@rand1 as varchar(4)) +cast(@rand2 as varchar(4))
    ---------------初始化CardNo------初始化AccountId------
    declare @AccountId int
    select @AccountId = max(AccountId) from Accounts
    if(@AccountId is null)
        select @CardNo = @CardNo + '10000'
    else
        select @CardNo = @CardNo + cast(@AccountId as varchar(10))
    -------------插入一条数据-------------------
    insert into Accounts([Name],[CardNo],[Password],[RegisterDate],[Address],[Phone1],[Phone2],[Notes])
    values(@Name,@CardNo,@Password,getdate(),@Address,@Phone1,@Phone2,@Notes)
   
exec CreateAccount 'zhanghai','123456','kunshan ,su zhou','34334e','22342342343','di yi ge'

---------------------记录交易行为----------------------------
create table Transactions
(
 [TranId] int primary key identity(1000,1),
 [AccountId] int foreign key references Accounts(AccountId) not null,
 [TranDate] datetime not null,
 [TranType] varchar(5) not null,
 [Amount] money not null,
 [Notes] text
)

------------------------交易行为的存储过程-----------
alter proc CheckAccount
@CardNo varchar(19),
@Password varchar(50)
as
   /*
   if not exists(select * from Accounts where CardNo = @CardNo)
   return 1
   else
   return 0
   */
   declare @id int
   select @id = AccountId from Accounts
   where CardNo = @CardNo and [Password] = @Password
   if(@id is null)
      return 0
   else
      return @id
     
-----------------------创建存取款的存储过程---------
alter proc SaveOrGetMoney
@CardNo varchar(19),
@Password varchar(50),
@Amount money,
@TranType varchar(10),
@Notes text = null
as
   -----------是否有次用户------------
    declare @id int
    exec @id = CheckAccount @CardNo,@Password
    if(@id = 0)
    begin
     raiserror('系统找不到用户',16,1)
     return
    end
    --------确认有足够的余额----
    if(@TranType = '取款')
    begin
         declare @m money
         exec GetBalance @CardNo,@Password,@Balance = @m output
         if(@m - @Amount < 10)
         begin
            raiserror('账户余额不足，交易失败',16,1)
            return
         end
    end
    --------插入操作记录-------------
    insert into Transactions(AccountId,TranDate,TranType,Amount,Notes)
    values(@id,getdate(),@TranType,@Amount,@Notes)
   
    exec SaveOrGetMoney '6212229899225610001','123456',18990,'取款','大额取款'
   
--------------------------------------------------------------------
------------------------------指定用户的余额查询-----
    alter proc GetBalance
    @CardNo varchar(19),
    @Password varchar(50),
    @Balance money output ----输出参数，相当于c#里面的引用值--输出参数不是返回值--
    as
        -----检查用户--
         declare @id int
         exec @id = CheckAccount @CardNo,@Password
         if(@id = 0)
         begin
           raiserror('系统找不到用户',16,1)
           return
         end
        
         declare @total_save money
         declare @total_get money
         declare @total_in money
         declare @total_out money
        
         select @total_save = isnull(sum(amount),0) from Transactions where AccountId = @id and TranType = '存款'
         select @total_get = isnull(sum(amount),0) from Transactions where AccountId = @id and TranType = '取款'
         select @total_in = isnull(sum(amount),0) from Transactions where AccountId = @id and TranType = '转入'
         select @total_out = isnull(sum(amount),0) from Transactions where AccountId = @id and TranType = '转出'
         --------四个变量有一个是空的，则结果也是空的--------
         set @Balance = @total_save + @total_in - @total_get -@total_out
        
         declare @m money
         exec GetBalance '6212229899225610001','123456',@Balance = @m output
         select @m
--------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------
----------------------------常用的系统存储过程----------------------------------------------------
exec sys.sp_databases -----查看系统的所有数据库---------
exec sys.sp_tables    ----当前数据库有哪些表--------