11db.txt

the original code of the book the principle and practice in sql server 2005

create proc zfproc6
@zghao varchar(50),
@newzghao varchar(50)
as 
begin
  if exists(select *from 职工 where 职工号=@zghao)
 
 begin
   if exists(select * from 订购单 where 职工号=@zghao)
    begin
        update 订购单 set 职工号=@newzghao where 职工号=@zghao
        print '该职工有订单，并成功修改了订单中的职工号！'
    end
   else
      print '该职工没有订单！'
      update 职工 set 职工号=@newzghao where 职工号=@zghao
      print '更新该职工的信息!'

 end
 else
    print '该职工不存在，请重新输入职工号'
end



--execute zfproc6 'zg1','zfzg1'
----execute zfproc6 'zg42','zfzg24'
--select *
--from 职工
--execute zfproc 'zg42','wh1','张平','女',1350
--select *
--from 订购单\
--update 职工 set 职工号='zg3' where 职工号='zfzg3'

select max(工资) as 最大工资,stdev(工资) as 工资标准差,stdevp(工资) as 工资总体标准差,
var(工资) as 工资统计方差,varp(工资) as 工资总体变异方差
from 职工

declare @i int
set @i=0
while @i<26
 begin
   print char(ASCII('A')+@i)+'的ＡＳＣＩＩ码是：'+cast(ascii('A')+@i as varchar(50))
    set @i=@i+1
 end






--print LOWER(UPPER(lower('HAO ARE YOU!')))
--Print str(123.483,6,1)
--print str(124.654,3,1)
print '   hello,hao are you!   '
print ltrim('   hello,hao are you!   ')
print rtrim('   hello,hao are you!   ')
print len(ltrim('   hello,hao are you!   '))
print len(rtrim('   hello,hao are you!   '))
print len('   hello,hao are you!   ')
print left('   hello,hao are you!   ',10)
print right('   hello,hao are you!   ',10)
print substring('   hello,hao are you!   ',7,10)

declare @count int
set @count=1
while @count<=5
begin
  print space(5-@count)+replicate('*',@count*2-1)
  set @count=@count+1
end
set @count=5
 while @count>=0
 begin
   print space(5-@count)+replicate('*',@count*2-1)
   set @count=@count-1 
 end


print replicate('hello!',10)
print replicate('hello! ',-5)
print reverse(replicate('hello!',10))

print replace('SmallFish Online','SmallFish','whiteGood')

print stuff('yezuoyi',2,3,'lili')

print cast(month(getdate()) as varchar(50))+'月'+cast(year(getdate()) as varchar(50))+'年'+cast(day(getdate())as varchar(50))+'日'


print '现在的时间是：'+datename(hour,getdate())+':'+datename(minute,getdate())+':'+datename(second,getdate())


--create function hyfun(@x int) returns int 
--as
--begin
--  declare @y int
--  set @y=@x*@x
--  return @y
--end

print dbo.hyfun(15)

declare @x int
set @x=1
while @x<=10
begin
  select cast(@x as varchar(50))+'*'+cast(@x as varchar(50)) as 运算符,dbo.hyfun(@x) as 乘方结果
  set @x=@x+1
end


----use 企业销售管理系统
--select 职工.*,dbo.hyfun(工资) as 工资的平方
--from 职工

--create function hyfun2(@t varchar(10))
--returns table
--as
--return (select * from 职工 where 职工号=@t)
--
--select * 
--from hyfun2('zg9')


--create function hyfun3(@gz int)
--returns varchar(30)
--as
--begin
--  declare @y varchar(30)
--  if @gz>=2000
--     set @y='高工资'
--  else if @gz>=1500
--     set @y='一般工资'
--  else if @gz>=1200
--     set @y='低工资'
--  else
--      set @y='低收入'
--   return @y
--end
--
--select 职工.*,dbo.hyfun3(工资) as 职工评语 into 职工信息
--from 职工
----group by 仓库号
--order by 工资 desc
--select *
--from 职工信息
--drop table 职工信息