-- ================================================================================== -- 用户定义数据类型和函数 -- ================================================================================== -- 用户定义数据类型 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