课堂示范.txt

主要介绍了sql的基础教程

-----1.用户自定义的标量函数的应用
Create Function tiji(@CubeLength decimal(4,1),@CubeWidth decimal(4,1),
@CubeHeight decimal(4,1))
Returns decimal(12,3)
As
Begin
  Return (@CubeLength*@CubeWidth*@CubeHeight)
End
----------------------------------------------------------------------------------------------------------------------
-----标量函数应用1
Select  dbo.CubicVolume(100,100,100)
----------------------------------------------------------------------------------------------------------------------
-----标量函数应用2
Create Table Bricks
(
BrickPartNumber int primary key,
BrickColor varchar(20),
BrickHeight decimal(4,1),
BrickLength decimal(4,1),
BrickWidth decimal(4,1),
BrickVolume As (dbo.CubicVolume(BrickLength,BrickWidth,BrickHeight))
)
----------------------------------------------------------------------------------------------------------------------
insert into Bricks(BrickPartNumber,BrickColor,BrickHeight,BrickLength,BrickWidth)
values(1,120,12,12,12)
----------------------------------------------------------------------------------------------------------------------
Select * from Bricks
----------------------------------------------------------------------------------------------------------------------

----2.用户自定义内嵌表值函数
Use pubs
go
Create Function SalesByStore(@storeid varchar(30))
Returns TABLE
As
  Return(Select title,qty
         From sales s,titles t
	 Where s.stor_id=@storeid and t.title_id=s.title_id
        )
----------------------------------------------------------------------------------------------------------------------
----内嵌表值函数的应用
Select * from SalesByStore('7066')