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