SQL 进阶学习之四

存储过程的简单练习：

--
print  N ' 今天天气不好！ '


use  Test
go

select   *   from   books
go
select   *   from  test
go


select   @@cpu_busy    as   ' cpu信息 '


select   *   from  books

select   @@rowcount   as   ' 上个命令执行的行数 '


select   @@version

insert  books
values  (dd,ff,gg)
select   @@error   as   ' error info '



select   ' me '   +   ' 2007 '

select   *   from  books

select   replace ( [ name ] , ' kaka ' , ' lize ' )  from   tmp


USE  Northwind
SELECT   COL_NAME ( OBJECT_ID ( ' Employees ' ),  1 )



use   Test

select   *   from  books
select   max (title)  as  maxtitle,pages  from  books
group   by  pages


if  ( select   avg (pages)  from  books)  =   250
select   ' A '
else
select   ' B '

IF   EXISTS  ( SELECT   *   FROM  BOOKS  WHERE  BOOK_ID  =   3 )
     BEGIN
         PRINT   ' A '
         PRINT   ' A '
         PRINT   ' A '
         PRINT    GETDATE ()
     END
ELSE
     BEGIN
         PRINT   ' B '
     END



--
use  pubs
select   *   from  titles

SELECT  Category  =
CASE  type
WHEN   ' popular_comp '   THEN   ' Popular Computing '
WHEN   ' mod_cook '   THEN   ' Modern Cooking '
WHEN   ' business '   THEN   ' Business '
WHEN   ' psychology '   THEN   ' Psychology '
WHEN   ' trad_cook '   THEN   ' Traditional Cooking '
ELSE   ' N/A '
END ,
Title, Price
FROM  titles


select   *   from  titles
go
waitfor  delay  ' 00:00:10 '

select  title  from  titles
go

--
select   *   from  titles

waitfor  time  ' 11:24:50 '

select  title  from  titles


use   @@error
select   *   from  book
select   @@error



--
USE  AdventureWorks;
GO
--  Drop the procedure if it already exists.
IF   OBJECT_ID (N ' HumanResources.usp_DeleteCandidate ' , N ' P ' )
              IS   NOT   NULL
     DROP   PROCEDURE  HumanResources.usp_DeleteCandidate;
GO
--  Create the procedure.
CREATE   PROCEDURE  HumanResources.usp_DeleteCandidate 
     @CandidateID   INT
AS
--  Execute the DELETE statement.
DELETE   FROM  HumanResources.JobCandidate
     WHERE  JobCandidateID  =   @CandidateID ;
--  Test the error value.
IF   @@ERROR   <>   0  
     BEGIN
         --  Return 99 to the calling program to indicate failure.
         PRINT  N ' An error occurred deleting the candidate information. ' ;
         RETURN   99 ;
     END
ELSE
     BEGIN
         --  Return 0 to the calling program to indicate success.
         PRINT  N ' The job candidate has been deleted. ' ;
         RETURN   0 ;
     END ;
GO


--
USE  TEST
GO
UPDATE  TMP
     SET  ID  =   4
     WHERE   [ NAME ]   =   ' kaka1 ' ;
IF   @@ERROR   =   547
     PRINT  N ' A check constraint violation occurred. ' ;
GO

--
USE  AdventureWorks;
GO
IF   OBJECT_ID (N ' Purchasing.usp_ChangePurchaseOrderHeader ' ,N ' P ' )
              IS   NOT   NULL
    DROP   PROCEDURE  Purchasing.usp_ChangePurchaseOrderHeader;
GO
CREATE   PROCEDURE  Purchasing.usp_ChangePurchaseOrderHeader
     @PurchaseOrderID   INT , 
     @EmployeeID   INT
AS
--  Declare variables used in error checking.
DECLARE   @ErrorVar   INT , 
     @RowCountVar   INT ;




--  Execute the UPDATE statement.
UPDATE  PurchaseOrderHeader 
     SET  EmployeeID  =   @EmployeeID  
     WHERE  PurchaseOrderID  =   @PurchaseOrderID ;

--  Save the @@ERROR and @@ROWCOUNT values in local 
--  variables before they are cleared.
SELECT   @ErrorVar   =   @@ERROR , 
     @RowCountVar   =   @@ROWCOUNT ;

--  Check for errors. If an invalid @EmployeeID was specified
--  the UPDATE statement returns a foreign-key violation error #547.
IF   @ErrorVar   <>   0
     BEGIN
         IF   @ErrorVar   =   547
             BEGIN
                 PRINT  N ' ERROR: Invalid ID specified for new employee. ' ;
                 RETURN   1 ;
             END
         ELSE
             BEGIN
                 PRINT  N ' ERROR: error  '
                     +   RTRIM ( CAST ( @ErrorVar   AS   NVARCHAR ( 10 )))
                     +  N '  occurred. ' ;
                 RETURN   2 ;
             END
     END

--  Check the row count. @RowCountVar is set to 0 
--  if an invalid @PurchaseOrderID was specified.
IF   @RowCountVar   =   0
     BEGIN
         PRINT   ' Warning: The EmployeeID specified is not valid ' ;
         RETURN   1 ;
     END
ELSE
     BEGIN
         PRINT   ' Purchase order updated with the new employee ' ;
         RETURN   0 ;
     END ;
GO
 



--
select   *   from  books

set  nocount  on
set  nocount  off



--
--
--  convert(  data_type [(length)],expression[, style])
--  style是样式，一般用于将datetime或smalldatetime数据转换为字符数据的日期格式的样式，
--  或者用于将float，real，money数据转化为字符数据的字符串格式的样式。如果style为null，则返回的结果也为null
--  convert和cast的区别是：可以指定转换的样式。
--  


select   *   from  Tmp

select   [ name ]   +   ' 的年龄为： '   +   cast (age  as   varchar ( 10 ))  +   ' 岁 '   as  球员年龄
from  tmp


select   [ name ]   +   ' 的年龄为： '   +  age   +   ' 岁 '   as  球员年龄
from  tmp

select   [ name ]   +   ' 的年龄为： '   +   convert ( varchar ( 5 ),age, 9 )  +   ' 岁 '   as  球员年龄
from  tmp

select   [ name ]   +   ' 的时间为： '   +   convert ( varchar ( 50 ), [ time ] , 110 )  as  球员所待时间
from  tmp

select   *   from  tmp
insert  tmp
(id, [ name ] ,age, [ time ] )
values ( 12 , ' me ' , 19 , getdate ())

--
declare   @name   char ( 50 ), @time   datetime
select   @name   =   [ name ] , @time   =   [ time ]
from  tmp
where  id  =   4
print   ' 球员姓名： ' +   @name
go

declare   @name   nvarchar ( 30 )

set   @name   =   ' 21CIT '
print   @name


declare   @name   nvarchar ( 30 )
select   @name   =   ' ac '
select   @name   as  sss


declare   @age   int ,  @time   int
set   @age   =   18
set   @time   =   convert ( int , year ( getdate ()))
print   @age  , @time



select   *   from  books
  print   ' 一共查询了　 '   +   cast ( @@rowcount   as   char ( 10 ))  +   ' 条记录 '


select   ' SQL Server 2005 启动以来尝试连接的次数： '   +   convert ( varchar ( 10 ), @@connections )


SELECT   @@MAX_CONNECTIONS   AS   ' Max Connections '



declare   @title   varchar ( 50 )
declare   @id   tinyint  
declare   @page   int
select   @title   =  title, @page   =  pages
from  books
where  book_id  =   2
set   @id   =   1
if   @id   >   @page
     begin
         print   ' 今天天气一般 '
     end
else
     begin
         select   *   from  books
         where  author_id  =   @id
     end

select   *   from  books

-- 　查看books表author_id等于2的那本书的页数是否大于300，如果大于的话，查看其书名和输出该书的页数（用print输出）；否则查看其作者名（firstname + lastname）
-- 　
declare   @title   varchar ( 30 ), @page   int , @name   nvarchar ( 50 ), @aid   int  
-- set @page = 100
select   @page   =  pages, @aid   =  author_id, @title   =  title
from  books
where  book_id  =   4
if   @page   >   300
     begin
         print   ' 输出满足条件的书名 '
         print   ' 书名:  '   +   @title  
         print   ' 页数:  '   +   cast ( @page   as   varchar ( 5 )) 
     end  
else
     begin  
         print   ' 输出满足条件的作者名 '
         select   @name   =  firstname  +   '   '   +  lastname
         from  authers
         where  author_id  =   @aid
         print   ' 作者全名为： '   +   @name
     end



--  while
--
declare   @id   int
declare   @title   varchar ( 30 )

set   @id   =   1
while   @id   <   3
     begin  
         select   @title   =  title
         from  books
         where  book_id  =   @id
         print   @title
         set   @id   =   @id   +   1   --  增加１
     end


--  输出书编号小于等于3且为奇数的书的名称和页数
declare   @id   int
declare   @title   varchar ( 30 ), @page   int , @maxid   int
select   @maxid   =   max (book_id)  from  books  --  4
set   @id   =   1
while   @id   <=   3
     begin  
             select   @title   =  title, @page   =  pages
             from  books
             where  book_id  =   @id
             if   @@rowcount   =   1    --  判断是否为一条记录
                 print   @title   +   '   '   +   cast ( @page   as   char ( 5 ))
             else  
                 begin
                     if   @id   >   @maxid
                         break
                     else
                         print   ' 没有书的编号为： ' +   cast ( @id   as   varchar ( 5 ))  +   ' 　的记录 '
                 end
             set   @id   =   @id   +   2   --  3
     end


+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--

create   table  M
(
id  int   null ,
a  varchar ( 10 ) null ,
b  varchar ( 10 ) null
)

select   *   from  m

insert  m
(a,b)
values  ( ' a1 ' , ' b1 ' )

insert  m
(a,b)
values  ( ' a2 ' , ' b2 ' )

insert  m
(a,b)
values  ( ' a3 ' , ' b3 ' )

declare   @num   int
declare   @id   int
set   @id   =   1
create   proc  pr_001
as
while ( @id   <=  ( select   @num   =   count ( * )  from  m))
     insert   top ( @id ) m
    (id)
     values ( @id )
     set   @id   =   @id   +   1

update  m
set  id  =   1
from   b
(
select   top   1   *
into  c  from  m
where   exists
(
select   *   from  m
where  id  is   null
)
)

truncate   table  m


update  m
set  id  =   1
(
select   top   1  id
  from  m
where  id  =  
(
select  id  from  m
where  id  is   null
)
)


+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
--


--  百钱百兔
--  大兔   3元   每只   
--  小兔   1元   三只  
declare   @datu   int , @xiaotu   int , @money   money
set   @datu   =   1
set   @money   =  $ 100
while  (  @datu * 3   +  ( 100 - @datu ) * 1 / 3  )  <   =   @money
     begin  
         set   @datu   =   @datu   +   1
     end
--     print @datu
set   @datu   =   @datu   - 1
set   @xiaotu   =   100   -   @datu
if  ( (  @datu * 3   +  ( 100 - @datu ) * 1 / 3  )  =   @money  )
     begin
             print   ' 大兔数量： '   +   cast ( @datu   as   varchar ( 10 ))
             print   ' 小兔数量： '   +   cast ( @xiaotu   as   varchar ( 10 ))
     end
else
     print  N ' 兔子数量不符合要求！ '


--  case
case


create   proc  edure

exec


use  northwind


create   procedure  procSelectProdunts
as
select  productid,productname,unitprice,reorderlevel
from  products

exec  procSelectProdunts

--  Tarte au sucre
--     Longlife Tofu


create   procedure  procSelectProduntsInput
@name   varchar ( 50 )
as
select  productid,productname,unitprice,reorderlevel
from  products
where  productname  =   @name

execute  procSelectProduntsInput  --  error

exec  procSelectProduntsInput  @name   =   ' Longlife Tofu '
or
execute  procSelectProduntsInput ' Longlife Tofu '

@@identity



use  test1

select   *   from  books

drop   proc  procBooksInsert

create   proc  procBooksInsert
@id   int   =   null ,
@title   varchar ( 20 )  =   null ,
@aid   int   =   null ,
@page   int   =   null
-- @num int = null output
as
insert   into  books
(book_id,title,author_id,pages)
values ( @id , @title , @aid , @page )
-- select @num = @@identity

set   identity_insert   on
exec  procBooksInsert  @id   =   1 , @title   =  C#, @aid   =   101 , @page   =   1000





--
create   table  A
(
id  int   null ,
name  varchar ( 20 )  null
)

select   *   from  A

create   proc  procA
@id   int   =   null ,
@name   varchar ( 20 )  =   null
as
insert  A
values ( @id , @name )

declare   @i   int
set   @i   =   1
while  ( @i   <   1000 )
     begin
         exec  procA  @id   =   @i , @name   =   null
         set   @i   =   @i   +   1
     end




use  northwind

select   *   from  products  where  SupplierID  =   8

--  查看SupplierID为8的产品名称和总价格
--  用case语句实现
declare   @name   varchar ( 30 )
declare   @names   varchar ( 100 )
declare   @sumprice   int
select   @name   =  ProductName  -- ),@sumprice = UnitPrice * UnitsInStock 
from  products
where   SupplierID  =   8

set   @names   =   case   @name  
     when  N ' Teatime Chocolate Biscuits '   then  N ' Teatime Chocolate Biscuits '   +  N '  总价 ' --  + @sumprice,
     when  N ' Sir Rodney '' s Marmalade '   then  N ' Sir Rodney '' s Marmalade '   +  N '  总价 '   -- + @sumprice,
--     when 'Sir Rodney''s Scones' then 'Sir Rodney''s Scones' + ' 总价' + @sumprice,
     when  N ' Scottish Longbreads '   then  N ' Scottish Longbreads '   +  N '  总价 '   -- + @sumprice
     else   N ' no '
end

print   @names


--  ====================================================================================
--  Simple CASE function: 
CASE  input_expression 
      WHEN  when_expression  THEN  result_expression 
     [  ...n  ]  
      [  
    ELSE else_result_expression 
      ]  
END  


--  Searched CASE function:
CASE
      WHEN  Boolean_expression  THEN  result_expression 
     [  ...n  ]  
      [  
    ELSE else_result_expression 
      ]  
END
--  ====================================================================================

select   *   from  products
where  productname  =   ' tofu '
--  jiandan case
select  productname, case  productid
     when   1   then   ' lize '
     when   2   then   ' jinsihu '
     when   3   then   ' wuyuhu '
     when   4   then   ' ai '
     when   5   then   ' limingzhe '
     else   ' songbin '
end   as  帅哥集合
from  products


--  search case
declare   @price   money
declare   @return   varchar ( 50 )

select   @price   =  UnitPrice
from  products
where  ProductName  =   ' Tofu '

set   @return   =   case
     when   @price   <  $ 20   then   ' A '
     when  $ 20   <=   @price    and   @price   <   30   then   ' B '
     else   ' C '
end

print   @return