상세 컨텐츠

본문 제목

viewRobotData

청강컴정/09DataBase기말고사

by luckey 2009. 6. 7. 10:25

본문

create proc viewRobotData
(
 @idx int,
 @visit int
)
as
begin

 declare @sensor char(5)
 declare @sensor1 char(1)
 declare @sensor2 char(1)
 declare @sensor3 char(1)
 declare @sensor4 char(1)
 declare @sensor5 char(1)

 if(not exists(select * from robotData where idx=@idx))
  print('없음')
 else
 begin
  update robotData set visit = visit + @visit where idx = @idx

  select @sensor1 = sensorTouch, @sensor2 = sensorLight, @sensor3 = sensorUltra, @sensor4 = sensorSound, @sensor5 = sensorMotor from robotData where idx=1

  set @sensor = @sensor1 + @sensor2 + @sensor3 +@sensor4 + @sensor5
  
  select
  idx,
  title,
  folderName,
  contents,
  convert(char(16), date, 120) as date,
  visit,
  imgCount,
  ckrobot.ckrobot.starImg(buildLevel) as buildLevel,
  ckrobot.ckrobot.starImg(sourceLevel) as sourceLevel,
  ckrobot.ckrobot.sensorImg(@sensor) as sensor
  from robotData
  where idx = @idx
 end
end

--별이미지 출력(레벨 출력)
create function starImg
(@level tinyint)
returns varchar(300)
as
begin
 declare @i int
 set @i = 1
 
 declare @starPrn varchar(300)
 set @starPrn = ''

 while(@i <= 5)
 begin
  if(@i <= @level)
   set @starPrn = @starPrn + '<img src="/Images/icon/icon_star_on.gif">'
  else
   set @starPrn = @starPrn + '<img src="/Images/icon/icon_star_off.gif">'
  set @i = @i + 1
 end
 return @starPrn
end

--센서이미지 출력(5개 센서 이미지)
create function sensorImg
(@sensor char(5))
returns varchar(300)
as
begin
 declare @imgType varchar(3)
 declare @imgPrn varchar(1000)
 set @imgPrn = ''

 declare @i int
 set @i = 1
 
 while(@i <= 5)
 begin
  set @imgType =
  case substring(@sensor, @i, 1)
   when 0 then 'off'
   when 1 then 'on'
  end
  set @imgPrn = @imgPrn + '<img src="/Images/icon/ico_Sensor'+convert(char(1), @i-1)+'_'+@imgType+'.gif">'
  set @i = @i + 1
 end

 return @imgPrn
end

관련글 더보기

댓글 영역