/*遊標*/

/*遊標*/
　　
　　--遊標實際上是一種能從包括多條資料記錄的結果集中每次提取一條記錄的機制
　　
　　/*遊標的作用*/
　　--允許定位到結果集中的特定行。
　　--從結果集的當前位置檢索一行或多行資料。
　　--支援對結果集中當前位置的行進行修改
　　
　　/*建立遊標*/
　　DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR FOR select_statement [ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]
　　/*cursor_name
　　是所定義的 Transact-SQL 遊標名稱。
　　 INSENSITIVE
　　定義一個遊標，以建立將由該遊標使用的資料的臨時複本。對遊標的所有請求都從 tempdb 中的該暫存資料表中得到應答；
　　因此，在對該遊標進行提取操作時返回的資料中不反映對基表所做的修改，並且該遊標不允許修改。使用 SQL-92 文法時，
　　如果省略 INSENSITIVE，（任何使用者）對基表提交的刪除和更新都反映在後面的提取中。
　　 SCROLL
　　指定所有的提取選項（FIRST、LAST、PRIOR、NEXT、RELATIVE、ABSOLUTE）均可用。
　　如果在 SQL-92 DECLARE CURSOR 中未指定 SCROLL，則 NEXT 是唯一支援的提取選項。
　　如果指定 SCROLL，則不能也指定 FAST_FORWARD。
　　 select_statement
　　是定義遊標結果集的標準 SELECT 語句。在遊標聲明的 select_statement 內不允許使用關鍵字 COMPUTE、COMPUTE BY、和 INTO。
　　*/
　　
　　/*遊標操作*/
　　+ expand sourceview plaincopy to clipboardprint?
declare cr cursor  
　　for 
　　select * from authors  
　　--定義遊標  
　　--該遊標未指定scroll，只可用next  
　　  
　　open cr  
　　--開啟遊標  
　　  
　　fetch next from cr  
　　--推進遊標  
　　  
　　close cr  
　　--關閉遊標  
　　  
　　deallocate cr  
　　--刪除遊標  
　　  
　　create table emp  
　　(  
　　eid int primary key,  
　　ename varchar(10),  
　　sal money  
　　)  
　　  
　　insert into emp select 1001,'rose',1234  
　　union select 1002,'jack',2564  
　　union select 1003,'will',245  
　　union select 1004,'lecky',456  
　　  
　　  
　　--首次推進的結果為第一條  
　　  
　　/*@@fetch_status*/ 
　　  
　　--返回被 FETCH 語句執行的最後遊標的狀態，而不是任何當前被串連開啟的遊標的狀態。  
　　-- 0 FETCH 語句成功。  
　　-- -1 FETCH 語句失敗或此行不在結果集中。(無法fetch的時候)  
　　-- -2 被提取的行不存在  
　　fetch next from cr  
　　while @@fetch_status=0  
　　begin  
　　 fetch next from cr  
　　end  
　　--如不推進，則全域變數的值對於迴圈是否能夠啟動沒有參考價值  
　　--最後一行fetch_status值為0  
　　--下一條為-1  
　　  
　　/*滾動遊標*/ 
　　declare cr cursor scroll for select * from emp  
　　--需用scroll關鍵字  
　　open cr  
　　fetch next from cr  
　　close cr  
　　deallocate cr 
declare cr cursor
　　for
　　select * from authors
　　--定義遊標
　　--該遊標未指定scroll，只可用next
　　
　　open cr
　　--開啟遊標
　　
　　fetch next from cr
　　--推進遊標
　　
　　close cr
　　--關閉遊標
　　
　　deallocate cr
　　--刪除遊標
　　
　　create table emp
　　(
　　eid int primary key,
　　ename varchar(10),
　　sal money
　　)
　　
　　insert into emp select 1001,'rose',1234
　　union select 1002,'jack',2564
　　union select 1003,'will',245
　　union select 1004,'lecky',456
　　
　　
　　--首次推進的結果為第一條
　　
　　/*@@fetch_status*/
　　
　　--返回被 FETCH 語句執行的最後遊標的狀態，而不是任何當前被串連開啟的遊標的狀態。
　　-- 0 FETCH 語句成功。
　　-- -1 FETCH 語句失敗或此行不在結果集中。(無法fetch的時候)
　　-- -2 被提取的行不存在
　　fetch next from cr
　　while @@fetch_status=0
　　begin
　　 fetch next from cr
　　end
　　--如不推進，則全域變數的值對於迴圈是否能夠啟動沒有參考價值
　　--最後一行fetch_status值為0
　　--下一條為-1
　　
　　/*滾動遊標*/
　　declare cr cursor scroll for select * from emp
　　--需用scroll關鍵字
　　open cr
　　fetch next from cr
　　close cr
　　deallocate cr
　　　　
　　
　　--FETCH FIRST：提取遊標的第一行。
　　--FETCH NEXT：提取上次提取的行的下一行。
　　--FETCH PRIOR：提取上次提取的行的前一行。
　　--FETCH LAST：提取遊標中的最後一行。
　　--FETCH ABSOLUTE n：
　　-- 如果n 為正整數，則提取 遊標中的第n行
　　-- 如果n為負整數，則提取遊標最後一行之前的第n行
　　-- 如果n 為0，則不提取任何行
　　--FETCH RELATIVE n ：
　　-- 如果n為正，則提取上次提取的行之後的第n行。
　　-- 如果n為負，則提取上提取的行之前的第n行。
　　-- 如果n為0，則再次提取同一行
　　
　　/*@@cursor_rows*/
　　--返回當前開啟的遊標中合格行的數目
　　
　　/*遊標執行個體（更新）*/
　　begin tran tr
　　declare cr cursor for select * from emp for update of ename
　　--通過該遊標只能更新ename列
　　--多個列逗號分隔
　　open cr
　　
　　fetch next from cr
　　update emp set ename='log' where current of cr
　　--current of cr通過遊標
　　--只更新一行
　　close cr
　　deallocate cr
　　rollback tran tr
　　
　　/*刪除遊標指向的行*/
　　delete from emp where current of cr
　　/*預存程序*/
　　
　　/*取記錄*/
　　create proc p1 @no int
　　as select * from emp where eid=@no
　　--as必須
　　
　　exec p1 2004
　　--執行預存程序
　　
　　/*加法*/
　　create proc p1 @a int,@b int
　　as return @a+@b
　　declare @res int
　　set @res=exec p1 20,30
　　print @res
　　--drop proc p1
　　--帶有傳回值的預存程序的執行
　　
　　/*按地址傳遞輸出*/
　　create proc p1 @a int output,@b int output
　　as
　　begin
　　declare @temp int
　　set @temp=@a
　　set @a=@b
　　set @b=@temp
　　end
　　
　　declare @a int,@b int
　　set @a=90
　　set @b=890
　　exec p1 @a output,@b output
　　print @a
　　print @b 

SQL之隨機函數及遊標應用樣本

 view plaincopy to clipboardprint?
create table emp  
(  
    eid varchar(10)  
)  
GO  --注意這裡一定要加go  
--drop table emp  
create proc prand   
    as   
    begin  
     declare @i int 
     set @i=0  
     while @i<100  
     begin  
     insert into emp select floor(rand()*100000)  
     --rand（）*100000的取值範圍為1－－99999  
     set @i=@i+1  
     --迴圈插入100條隨機數  
     end  
       
     declare crl scroll cursor for select * from emp  
     --定義遊標  
     open crl  
     --開啟遊標  
     --fetch first from crl   
     declare @max int,@min int,@temp int 
     --@max最大值,@min最小值,@temp 臨時變數  
     fetch next from crl into @max  
     --首次推進遊標，'into @max'是把其推進結果賦值給@max，關於into子句的用法建議參看聯機叢書瞭解一下，帥的很...  
     set @min=@max  
     --將此初值也賦給最小值  
     while @@fetch_status=0  
     begin  
 
     fetch next from crl into @temp  
     if @temp>@max  
     begin  
        set @max=@temp  
     end  
 
     if @temp<@min  
     begin  
     set @min=@temp  
     end  
 
     end  
     print '最大值為'+convert(varchar,@max)  
     print '最小值為'+convert(varchar,@min)  
     --輸出結果，需要強制轉換  
     close crl  
     --關閉遊標  
     deallocate crl  
     --刪除遊標  
    end  
    --drop proc prand  
 
    exec prand 
create table emp
(
 eid varchar(10)
)
GO  --注意這裡一定要加go
--drop table emp
create proc prand
 as
 begin
  declare @i int
  set @i=0
  while @i<100
  begin
  insert into emp select floor(rand()*100000)
  --rand（）*100000的取值範圍為1－－99999
  set @i=@i+1
  --迴圈插入100條隨機數
  end
 
  declare crl scroll cursor for select * from emp
  --定義遊標
  open crl
  --開啟遊標
  --fetch first from crl
  declare @max int,@min int,@temp int
  --@max最大值,@min最小值,@temp 臨時變數
     fetch next from crl into @max
  --首次推進遊標，'into @max'是把其推進結果賦值給@max，關於into子句的用法建議參看聯機叢書瞭解一下，帥的很...
  set @min=@max
  --將此初值也賦給最小值
  while @@fetch_status=0
  begin

  fetch next from crl into @temp
  if @temp>@max
  begin
  set @max=@temp
  end

  if @temp<@min
  begin
  set @min=@temp
  end

  end
  print '最大值為'+convert(varchar,@max)
  print '最小值為'+convert(varchar,@min)
  --輸出結果，需要強制轉換
  close crl
  --關閉遊標
  deallocate crl
  --刪除遊標
 end
 --drop proc prand

 exec prand

GO表示一批T-SQL語句結束，GO之後的T-SQL語句屬於另一個批處理範圍，在T-SQL所有語句的最後都要預設有一個GO。但是，GO不是T-SQL語句，而只是一個能被SQL Server工具 + 生產力識別的命令

USE [北風貿易]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER PROCEDURE [dbo].[testProcedure]
@社會安全號碼碼 NVARCHAR(18)
AS
SELECT*FROM dbo.成績 WHERE 社會安全號碼碼=@社會安全號碼碼
上面樣本將發生錯誤：'CREATE/ALTER PROCEDURE' 必須是查詢批次中的第一個語句。

應該改成

 

USE [北風貿易]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[testProcedure]
@社會安全號碼碼 NVARCHAR(18)
AS
SELECT*FROM dbo.成績 WHERE 社會安全號碼碼=@社會安全號碼碼

本文來自CSDN部落格，轉載請標明出處：http://blog.csdn.net/ztj111/archive/2009/04/28/4133122.aspx