SqlServer遊標、預存程序及資料區塊執行

資料區塊遊標案例如下：

begindeclare @item_code varchar(32)--定義變數declare @item_name varchar(32)declare @invest_money_sum float--定義遊標declare my_cursor cursor forselect item_code,item_name,invest_money_sum from zftz_project_sheji_result--開啟遊標open my_cursorfetch next from my_cursor into @item_code,@item_name,@invest_money_sumwhile(@@fetch_status=0)beginupdate zftz_project_item_info1 set mainamount=@invest_money_sum where code=@item_code fetch next from my_cursor into @item_code,@item_name,@invest_money_sumend--關閉遊標close my_cursordeallocate my_cursor --卸載遊標end

預存程序執行個體如下：

drop procedure test_test;gocreate procedure test_test@parm1 varchar(20),@parm2 varchar(20)asbegin transaction declare @item_code varchar(32)--定義變數declare @item_name varchar(32)declare @invest_money_sum float--定義遊標declare my_cursor cursor forselect item_code,item_name,invest_money_sum from zftz_project_sheji_result--開啟遊標open my_cursorfetch next from my_cursor into @item_code,@item_name,@invest_money_sumwhile(@@fetch_status=0)beginupdate zftz_project_item_info1 set mainamount=@invest_money_sum where code=@item_code fetch next from my_cursor into @item_code,@item_name,@invest_money_sumendset @parm2=@parm1+','+@parm2print @parm2--關閉遊標close my_cursordeallocate my_cursor --卸載遊標--可以添加傳回值與添加事務控制if(@@error>0)beginrollback transelect 0returnend    elsebegincommit transelect 1returnend--endexec test_test 'ab','cd'  --執行預存程序

查詢sqlserver資料庫的所有資料表：

select name from sysobjects where xtype='U'----查詢資料庫的所有資料表