procedure,cursor 用法

最新推荐文章于 2023-03-09 14:22:37 发布

原创最新推荐文章于 2023-03-09 14:22:37 发布 · 977 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#classification #insert #object

SqlServer2000 专栏收录该内容

16 篇文章

订阅专栏

本文介绍了一个SQL存储过程的创建及使用实例，该过程通过游标遍历数据并插入到另一个表中，条件为单位类型不等于1000。此过程涉及数据库操作的关键步骤和技术细节。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[msw_pro]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[msw_pro]
go
SET QUOTED_IDENTIFIER ON
GO
SET ansi_nulls on
go
create procedure msw_pro
as

declare mycursor cursor
for
select unitid,unittype,unittypecode,unitname,
classificationname,ku.weight
from kangaroo_unit ku,kangaroo_classification kc
where
ku.modifytype<>3
and ku.unittype=kc.typeid

declare @unitid int
declare @unittype int
declare @weight int
declare @unittypecode nvarchar(400)
declare @unitname nvarchar(400)
declare @classificationname nvarchar(400)
open mycursor
fetch next from mycursor
--while @@Fetch_status = 0
into @unitid,@unittype,@unittypecode,@unitname,@weight,@classificationname,@weight
--//
while(@@Fetch_status = 0)
begin
    --Fetch next from mycursor
    if(@unittype<>1000)
       begin
          insert into kangaroo_unit_classification(
           unitid,classificationid,classificationtypecode,weight, unitname,
           classificationname)
           values(@unitid,@unittype,@unittypecode,@weight,@unitname,@classificationname)
       end
    --//
    fetch next from mycursor into @unitid,@unittype,@unittypecode,@unitname,@weight,@classificationname,@weight
end

close mycursor
deallocate mycursor