SQL 进阶学习之五

 

--  ==================================================================================
--  用户定义数据类型和函数
--  ==================================================================================
--  用户定义数据类型 UDT
--  用户定义数据函数 UDF
--
EXEC  sp_addtype ssn1,  ' varchar(12) ' ,  ' NULL ' ;
--
EXEC  sp_droptype ssn;
--
CREATE  TYPE  [  schema_name.  ]  type_name
{ 
     FROM  base_type 
     [  ( precision [ , scale  ]  )  ]
     [  NULL | NOT NULL  ]  
   |  EXTERNAL NAME assembly_name  [  .class_name  ]  
}  [  ;  ]

--
drop  type ssn

CREATE  TYPE SSN
FROM   varchar ( 11 )  NOT   NULL  ;

create  type num
from   int   null

create   table  tmp
(
id num  not   null  ,
[ name ]  ssn1 ,
)

select   *   from  tmp
insert  tmp
values ( 1 , ' me ' )

--  用户定义函数
--  创建一个标量函数,返回当前的时间

create   function  myfun1() 
returns    datetime
begin
     return   getdate ()
end
go

drop   function  person.myfun1

create   function  person.myfun1() 
returns    int
begin
     return   month ( getdate ())
end
go

select  dbo.myfun1()  as  当前时间
print  person.myfun1()

select   getdate ()
select  person.myfun1()


--  ==================================================================
Scalar Functions
CREATE   FUNCTION   [  schema_name.  ]  function_name 
(  [  { @parameter_name [ AS  ][  type_schema_name.  ]  parameter_data_type 
     [  = default  ]  } 
     [  ,...n  ]
  ]
)
RETURNS  return_data_type
     [  WITH <function_option> [ ,...n  ]  ]
     [  AS  ]
     BEGIN  
                function_body 
         RETURN  scalar_expression
     END
[  ;  ]

Inline  Table - valued Functions
CREATE   FUNCTION   [  schema_name.  ]  function_name 
(  [  { @parameter_name [ AS  ]   [  type_schema_name.  ]  parameter_data_type 
     [  = default  ]  } 
     [  ,...n  ]
  ]
)
RETURNS   TABLE
     [  WITH <function_option> [ ,...n  ]  ]
     [  AS  ]
     RETURN   [  (  ]  select_stmt  [  )  ]
[  ;  ]

Multistatement  Table - valued Functions
CREATE   FUNCTION   [  schema_name.  ]  function_name 
(  [  { @parameter_name [ AS  ]   [  type_schema_name.  ]  parameter_data_type 
     [  = default  ]  } 
     [  ,...n  ]
  ]
)
RETURNS   @return_variable   TABLE   <  table_type_definition  >
     [  WITH <function_option> [ ,...n  ]  ]
     [  AS  ]
     BEGIN  
                function_body 
         RETURN
     END
[  ;  ]

CLR Functions
CREATE   FUNCTION   [  schema_name.  ]  function_name 
( {  @parameter_name   [ AS ]   [  type_schema_name.  ]  parameter_data_type 
         [  = default  ]  } 
     [  ,...n  ]
)
RETURNS  { return_data_type  |   TABLE   < clr_table_type_definition >  }
     [  WITH <clr_function_option> [ ,...n  ]  ]
     [  AS  ]  EXTERNAL NAME  < method_specifier >
[  ;  ]

Method Specifier
< method_specifier > :: =
    assembly_name.class_name.method_name

Function  Options
< function_option > :: =  
{
     [  ENCRYPTION  ]
   |   [  SCHEMABINDING  ]
   |   [  RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT  ]
   |   [  EXECUTE_AS_Clause  ]
}

< clr_function_option > :: =
}
     [  RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT  ]
   |   [  EXECUTE_AS_Clause  ]
}

Table  Type Definitions
< table_type_definition > ::  =  
( {  < column_definition >   < column_constraint >  
   |   < computed_column_definition >  } 
         [  <table_constraint>  ]   [  ,...n  ]
) 

< clr_table_type_definition > :: =  
( { column_name data_type }  [  ,...n  ]  )

< column_definition > :: =
{
    { column_name data_type }
     [  [ DEFAULT constant_expression  ]  
       [  COLLATE collation_name  ]   |   [  ROWGUIDCOL  ]
    ]
     |   [  IDENTITY [ (seed , increment )  ]  ]
     [  <column_constraint> [ ...n  ]  ] 
}
< column_constraint > :: =  
{
     [  NULL | NOT NULL  ]  
    {  PRIMARY   KEY   |   UNIQUE  }
       [  CLUSTERED | NONCLUSTERED  ]  
         [  WITH FILLFACTOR = fillfactor 
        | WITH ( < index_option > [ , ...n  ]  )
       [  ON { filegroup | "default" }  ]
   |   [  CHECK ( logical_expression )  ]   [  ,...n  ]
}

< computed_column_definition > :: =
column_name  AS  computed_column_expression 

< table_constraint > :: =
{ 
    {  PRIMARY   KEY   |   UNIQUE  }
       [  CLUSTERED | NONCLUSTERED  ]  
            ( column_name  [  ASC | DESC  ]   [  ,...n  ]  )
         [  WITH FILLFACTOR = fillfactor 
        | WITH ( <index_option> [ , ...n  ]  )
   |   [  CHECK ( logical_expression )  ]   [  ,...n  ]
}

< index_option > :: =
{ 
    PAD_INDEX  =  {  ON   |   OFF  }    |   FILLFACTOR   =   fillfactor  
   |  IGNORE_DUP_KEY  =  {  ON   |   OFF  }
   |  STATISTICS_NORECOMPUTE  =  {  ON   |   OFF  } 
   |  ALLOW_ROW_LOCKS  =  {  ON   |   OFF  }
   |  ALLOW_PAGE_LOCKS  = {  ON   |   OFF  } 
}

--  ==================================================================


create   function  dbo.funSum( @id   int )
returns   money
begin
     declare   @sumprice   money
     select   @sumprice   =   sum (UnitPrice  *  UnitsInStock)  from  products
     where  productid  =   @id
     return   @sumprice
end
go

print  N ' 总价格为： ' +   cast (dbo.funsum( 1 )  as   varchar ( 20 ))  +  N '  人民币 '

select   *   from  products
select   *   from  employees



--  创建一个标量值函数，用于统计某个雇员的订单总数
create   function  funSumOrder( @firstname   nvarchar ( 10 ), @lastname   nvarchar ( 20 ))
returns   int
begin
     declare   @sumorder   int
     select   @sumorder   =   count (orderid)
     from  orders 
     where  EmployeeID  =
    (
         select  EmployeeID  from  employees
         where  firstname  =   @firstname   and  lastname  =   @lastname
    )
     return   @sumorder
end
go

drop   function  funsumorder

select  dbo.funsumorder( ' anne ' , ' dodsworth ' )  as  总订单数



--  查看某段时间内的订单情况
select   *   from  orders

--
create   function  orderinfo( @starttime   datetime , @endtime   datetime )
returns   table
     return  
     select   *   from  orders
     where  orderdate  between   @starttime   and   @endtime
go

select   *   from  orderinfo( ' 1996-6-1 ' , ' 1996-8-31 ' )


--
create   function  funorder()
returns   @note   table
        (
        id  int   identity ( 1 , 1 )  not   null ,
         [ name ]   nvarchar ( 40 )  not   null ,
        addr  nvarchar ( 100 )  null ,
        tel  varchar ( 24 )  null ,
        info  nvarchar ( 10 )
        )
     begin
             insert   @note
             select  firstname  +  lastname,address,homephone,N ' 雇员 '
             from  employees
             insert   @note
             select  contactname ,address,phone,N ' 供应商 '
             from  suppliers
             insert   @note
             select  contactname,address,phone,N ' 客户 '
             from  customers
         return
     end
go

drop   function  funorder


select   *   from  funorder() 


--  练习
--  创建一个函数，用于查看某年度订单数最多的雇员的上级主管信息

select   *   from  employees
select   *   from  orders

print   @@error


--  
create   function  funOrderMax( @year   int )
returns   @note   table
        (
        id  int   identity ( 1 , 1 )  not   null ,
        employeeid  int   not   null ,
         [ name ]   nvarchar ( 40 )  not   null ,
        title  nvarchar ( 50 )  null ,
        info  nvarchar ( 10 )
        )
begin
         declare   @eid   int
         declare   @rid   int
         --
         select   top   1   @eid   =  employeeid        
         from  orders
         where   year (OrderDate)  =   @year
         group   by  employeeid
         order   by   count (OrderID)  desc
         --
         select   @rid   =  ReportsTo 
         from  Employees
         where  EmployeeID  =   @eid
         --
         insert   @note
         select  employeeid,FirstName  +  LastName,Title,N '  上级主管 '
         from  Employees
         where  EmployeeID  =   @rid
         --
         return
end
go

drop   function  funOrderMax


select   *   from  funOrderMax( 1997 )

--  l
select  reportsto  from  employees  where  employeeid =
(
select  id  from  a  where  num =
(
select    top   1 (num)  from  a 
order   by   num  desc
) 
)



--  游标的介绍
--  定义游标
-- SQL 92 Syntax
DECLARE  cursor_name  [  INSENSITIVE  ]   [  SCROLL  ]  
CURSOR   FOR  select_statement  [  FOR { READ ONLY | UPDATE [ OF column_name [ ,...n  ]  ] } ]
[ ; ]

-- Transact-SQL Extended Syntax
DECLARE  cursor_name  CURSOR [  LOCAL | GLOBAL  ][  FORWARD_ONLY | SCROLL  ]
[  STATIC | KEYSET | DYNAMIC | FAST_FORWARD  ][  READ_ONLY | SCROLL_LOCKS | OPTIMISTIC  ][  TYPE_WARNING  ]
FOR  select_statement [  FOR UPDATE [ OF column_name [ ,...n  ]  ] ]
[ ; ]


--  
declare  mycur  cursor   for
     select  ShipCity,ShipName  from  orders
--
open  mycur

--  ====================================
FETCH  
           [  [ NEXT | PRIOR | FIRST | LAST 
                    | ABSOLUTE { n | @nvar } 
                    | RELATIVE { n | @nvar } 
                ]  
                FROM  
          ] 
{ {  [  GLOBAL  ]  cursor_name }  |   @cursor_variable_name  } 
[  INTO @variable_name [ ,...n  ]  ] 

--  ====================================
select   *   from  orders
--  操作游标
declare   @city   nvarchar ( 100 )
declare   @name   nvarchar ( 50 )

fetch   next   from  mycur
     into   @city , @name
--  判断是否从游标中取得数据
while  ( @@fetch_status   =   0  )
begin
     select   @city   as  城市
     select   @name   as  姓名
     --
     fetch   next   from  mycur
     into   @city , @name
end

--
close  mycur


--  删除游标
drop  mycur     --  error
delete  mycur   --  error

deallocate  mycur


--  游标练习
--  查看产品表,将单价高于100元的产品打九折
--
declare  curpro  cursor   for
select  UnitPrice  from  products
-- where UnitPrice > $100

open  curpro

declare   @price   money

fetch   next   from  curpro
     into   @price
while  ( @@fetch_status   =   0  )
begin
     if  ( @price   >   100 )    
     begin
         set   @price   =    @price   *   0.9  
         --  
         update  products
         set  UnitPrice  =   @price
         where   current   of  curpro
     end

     fetch   next   from  curpro
     into   @price
end



--  
select  UnitPrice  from  products
where  UnitPrice  >  $ 100



--  =========================================================================================
--  2007/06/02 SQL练习
--  =========================================================================================
use  test
go

select   *   from  books
--  定义全局游标（默认的方式）
declare  curbook  cursor   scroll  for
     select  title,pages     from   books
--  
--  第一个批开始
open  curbook
--  
declare   @title   nvarchar ( 20 ) 
declare   @page   int
fetch  first  from  curbook
     into   @title , @page
if  ( @@fetch_status   =   0 )
begin
     print   ' 第一个书名为： '   +   @title
     print   ' 第一个书的页数为： '   +   cast ( @page   as   nvarchar ( 5 ))
     print   ''
end
--
close  curbook
go

--  第二个批开始
open  curbook
declare   @title   nvarchar ( 20 ) 
declare   @page   int
fetch  absolute  4   from  curbook
     into   @title , @page
if  ( @@fetch_status   =   0 )
begin
     print   ' 第4个书名为： '   +   @title
     print   ' 第4个书的页数为： '   +   cast ( @page   as   nvarchar ( 5 ))
     print   ''
end
close  curbook
go

--  第三个批开始
open  curbook
declare   @title   nvarchar ( 20 ) 
declare   @page   int
fetch  relative  1   from  curbook
-- fetch PRIOR from curbook
     into   @title , @page
if  ( @@fetch_status   =   0 )
begin
     print   ' 第3个书名为： '   +   @title
     print   ' 第3个书的页数为： '   +   cast ( @page   as   nvarchar ( 5 ))
     print   ''
end
close  curbook
go




--  课堂练习
--  对northwind数据库中的orders表进行游标操作
--  OrderID,ShipName
--  要求：打印出表中的第一条，第3条，和第4条数据，以及最后一条数据
use  northwind
go
select   *   from  orders
--
declare  curbyorders  cursor  global  scroll  for
     select  OrderID,ShipName     from   orders
--  

open  curbyorders
--  
declare   @id   int  
declare   @name   nvarchar ( 50 )
fetch  first  from  curbyorders
     into   @id , @name
if  ( @@fetch_status   =   0 )
begin
     print  N ' 第一个订单的订单号为： '   +   cast ( @id   as   nvarchar ( 5 ))
     print  N ' 第一个船名为： '   +   @name
     print   ''
end
--
close  curbyorders
go

--  第二个批开始
open  curbyorders
declare   @id   int  
declare   @name   nvarchar ( 50 )
fetch  absolute  3   from  curbyorders
     into   @id , @name
if  ( @@fetch_status   =   0 )
begin
     print  N ' 第3个订单的订单号为 '   +   cast ( @id   as   nvarchar ( 5 ))
     print  N ' 第3个船名为： '   +   @name
     print   ''
end
close  curbyorders
go

--  第三个批开始
open  curbyorders
declare   @id   int  
declare   @name   nvarchar ( 50 )
fetch  last  from  curbyorders
-- fetch PRIOR from curbook
     into   @id , @name
if  ( @@fetch_status   =   0 )
begin
     print  N ' 最后一个订单的订单号为 '   +   cast ( @id   as   nvarchar ( 5 ))
     print  N ' 最后一个船名为： '   +   @name
     print   ''
end
close  curbyorders
go



--  嵌套游标的举例：
--  查看类别表中的所有类别下的所有产品名
--
select   *   from  Categories
--
declare  curbyC  cursor   for
select  CategoryID,CategoryName  from  Categories
--
open  curbyC
--
declare   @cid   int
declare   @cname   nvarchar ( 15 )
--
fetch   next   from  curbyC
     into   @cid , @cname
while ( @@fetch_status   =   0 )
     begin
         print  N ' 属于  '   +   @cname   +  N '  的产品有： '
         --
         --  嵌套一个游标
          declare  curbyP  cursor   for
             select  ProductName  from  products
             where  CategoryID  =   @cid
         declare   @pname   nvarchar ( 50 )
         open  curbyP
         fetch   next   from  curbyP
             into   @pname
         while ( @@fetch_status   =   0 )
             begin
                 print   @pname
                 fetch   next   from  curbyP
                     into   @pname
             end
         close  curbyP
         deallocate  curbyP
         --
         fetch   next   from  curbyC
             into   @cid , @cname
         print   ''
     end
     close  curbyC
     deallocate  curbyC
    




--  在存储过程中使用游标参数
--  示例
--  创建一个存储过程，该存储过程包含类别表里记录的游标，并通过该存储过程来显示所有类别名称
--  create sp
create   proc  porc_c
     @curbyc   cursor   varying  output
as
     --
     set   @curbyc   =   cursor  static local  for  
         select  CategoryName  from  Categories
     open   @curbyc
go

--  
declare   @curc   cursor   --  新定义的游标变量
declare   @cname   nvarchar ( 15 )
--  将存储过程的返回值赋给游标
exec  porc_c  @curc  output

fetch   next   from   @curc   into   @cname
while  ( @@fetch_status   =   0  )
begin
     print   @cname
     fetch   next   from   @curc   into   @cname
end
close   @curc