과제 - 스토어드 프로시져 작성

열차의 출발지, 목적지, 형태에 따른 이용료를 계산하여 출력하는 프로시져를 작성하라.

use master;
go
if DB_ID('Sample_200872032') IS NOT NULL
DROP DATABASE Sample_200872032;
go
CREATE DATABASE Sample_200872032;
go
use Sample_200872032;
go

create table trainArea
(
 idx int primary key identity(1,1),
 st_Station nvarchar(5),
 ed_Station nvarchar(5),
 basicMoney money
)
go
create table trainMoney
(
 tType nvarchar(5),
 tMoney money
)
go
begin
insert into trainArea values('서울', '대전', 10000)
insert into trainArea values('서울', '대구', 20000)
insert into trainArea values('서울', '부산', 25000)
insert into trainArea values('서울', '광주', 22000)

insert into trainArea values('대전', '대구', 10000)
insert into trainArea values('대전', '부산', 17000)
insert into trainArea values('대전', '광주', 12000)

insert into trainArea values('대구', '부산', 7500)
insert into trainArea values('대구', '광주', 6000)

insert into trainArea values('부산', '광주', 15000)
end
go

begin
 insert into trainMoney values ('ktx', 15000)
 insert into trainMoney values ('새마을호', 7000)
 insert into trainMoney values ('무궁화호', 3000)
end
go

create proc trainSearch
(
 @st_Station nvarchar(5),
 @ed_Station nvarchar(5),
 @trainType nvarchar(5)
)
as
begin
 declare @basicMoney money
 declare @trainMoney money
 declare @totalMoney money
 declare @printTxt varchar(70)

 set @basicMoney = (
 select basicMoney from trainArea where st_Station = @st_Station and ed_Station = @ed_Station
 union
 select basicMoney from trainArea where ed_Station = @st_Station and st_Station = @ed_Station
 )
set @printTxt = @st_Station + '역 ~ ' + @ed_Station + '역 까지 운행하는' + @trainType + '열차'
select @trainType = '%' + @trainType + '%'
select @trainMoney = tMoney, @traintype=tType from trainMoney where tType like @trainType
set @totalMoney = @basicMoney + @trainMoney

if(@basicMoney > 0)
begin
set @printTxt = @printTxt + ' 운임은 ' + convert(varchar(10), @totalMoney) + '원 입니다.'
end
else
begin
set @printTxt = @printTxt + '는 없습니다.'
end
select @printTxt
end
go