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