while循環

declare @avgprice numeric(10,2)
set @avgprice=(select avg(price)from titles)
//自定義參數
while @avgprice<10
//循環條件
begin
update titles
set price=price*1.1
end
//循環語句操作，當avgprice<10,所有price都加0.1

case語句

查詢authors表，當state為各種情況時，為新加的一列賦上不同的值。功能類似于C語言中的switch語句

select *, //加逗號
case state
when’CA’ then ‘California’
when ‘KS’ then ‘kaness’
when ‘MI’ then ‘Michigan’
when ‘IN’ then ‘India’
when ‘MD’ then ‘Maryland’
when ‘UT’ then ‘Utah’
when ‘HN’ then ‘hunan’
else ‘Other’
end as statename
from authors

存儲過程

例

創建一個存儲過程名為searchbook，自定義變量后可以查詢符合條件的對象信息，使用自定義變量可以讓用戶自定義查詢條件。
create procedure searchbook
@inputlow float,
@inputhigh float

as
begin
select title,price,pub_name
from titles inner join publishers
on(publishers.pub_id=titles.pub_id)
where price>@inputlow and price<@inputhigh
end

//查詢語句：
exec searchbook 10.1, 20.2
使用存儲過程后，用戶可以用很簡單的查詢語句傳入參數，查詢到自己想要的結果
–存儲過程優點，3-4條
–1.簡化代碼
–實現共享，提高開發速度
–隔離復雜性
–簡化軟件分發
–提高運行速度

練

寫一個存儲過程，查詢在某個價格區間的書籍
如果該價格區間沒有任何書，則給出銷量最高的3本書
create procedure searchbook
@inputlow float,
@inputhigh float //全局變量

as
begin

declare @num int //局部變量
set @num=(select count(*) from titles
where price>@inputlow and price<@inputhigh)

//if語句
if @num>0
begin
select title,price,pub_name
from titles inner join publishers
on(publishers.pub_id=titles.pub_id)
where price>@inputlow and price<@inputhigh
end

//else語句
else
begin
select top 3 title,price,pub_name
from titles inner join publishers
on(publishers.pub_id=titles.pub_id)
order by ytd_sales desc
end

end
//查詢語句
exec searchbook 30,50.1

output參數

例

設一個全局變量out為OUTPUT，在存儲過程中定義它的數據，
在使用查詢語句時，用戶可以自定義變量，將OUTPUT的值賦給該變量，用select語句得到輸出

//定義存儲結構
create procedure searchbook1
@inputlow int,
@inputhigh int,
@out int OUTPUT //定義全局變量為OUTPUT

as
begin
select title,price,pub_name
from titles inner join publishers
on(publishers.pub_id=titles.pub_id)
where price>@inputlow and price<@inputhigh

//定義OUTPUT的數據
set @out=(select count(*) from titles
where price>@inputlow and price<@inputhigh)
end

//查詢語句
declare @myout int
exec searchbook1 30,50, @myout OUTPUT
select @myout

可能報錯

1.超出了存儲過程、函數、觸發器或視圖的最大嵌套層數(最大層數為 32)
可能是觸發器的影響，在 titles 或者 publishers 表上存在觸發器，執行 SELECT 語句時，觸發器會被觸發，導致嵌套調用。
解決：
– 禁用 titles 表上的所有觸發器
DISABLE TRIGGER ALL ON titles;
– 禁用 publishers 表上的所有觸發器
DISABLE TRIGGER ALL ON publishers;
2.CREATE/ALTER PROCEDURE’必須是查詢批次中的第一個語句
在 SQL Server 里，CREATE PROCEDURE 語句需要作為一個批次里的首個語句，它的前面不應有其他語句
如果有其他語句，可使用 GO 關鍵字來劃分不同的批次
如報錯1需要增加語句
可如下：

//添加語句
DISABLE TRIGGER ALL ON titles;
DISABLE TRIGGER ALL ON publishers;
GO

//原語句
create procedure searchbook1
@inputlow int,
@inputhigh int,
@out int OUTPUT

as
begin
select title,price,pub_name
from titles inner join publishers
on(publishers.pub_id=titles.pub_id)
where price>@inputlow and price<@inputhigh

set @out=(select count(*) from titles
where price>@inputlow and price<@inputhigh)
end； //加分號
GO

declare @myout int
exec searchbook1 30,50, @myout OUTPUT
select @myout