游标的用法

create procedure example2 as
--定义变量--
DECLARE @lst_cust_no char(17),@lst_cust_name nvarchar(100),
	@lst_cust_manager_id bigint,@lst_manager_name nvarchar(50),
	@lst_status varchar(10)

--创建游标，查询cust_no 为KH071207032218637的结果集--
declare lst_cst_customer cursor for
select cust_no,cust_name,cust_manager_id,cust_manager_name,cust_status
from cst_customer
where cust_no ='KH071207032218637'

--打开游标--
open lst_cst_customer
fetch next from lst_cst_customer
into @lst_cust_no,@lst_cust_name,@lst_cust_manager_id,@lst_manager_name,@lst_status

--创建新表--
create table newtable(
lst_cust_no char(17),
lst_cust_name nvarchar(100),
lst_cust_manager_id bigint,
lst_manager_name nvarchar(50),
lst_status varchar(10)
)

while @@fetch_status=0
begin 
--向新表存储游标的数据--
insert into newtable(lst_cust_no,lst_cust_name,lst_cust_manager_id,lst_manager_name,lst_status)
values(@lst_cust_no,@lst_cust_name,@lst_cust_manager_id,@lst_manager_name,@lst_status)
fetch next from lst_cst_customer
into @lst_cust_no,@lst_cust_name,@lst_cust_manager_id,@lst_manager_name,@lst_status
end
--关闭游标--
close lst_cst_customer
deallocate lst_cst_customer


exec example2