曾经有没有用过存储过程游标,游标又是怎么工作的?也就是它的写作步骤亦或者工作原理
IF EXISTS (SELECT * FROM dbo.SYSOBJECTS WHERE ID =
OBJECT_ID(N'[dbo].[pro_test]')
AND OBJECTPROPERTY(ID,N'ISPROCEDURE')=1)
GO
CREATE PROCEDURE pro_test
AS
BEGIN
--变量 存数据
DECLARE
@ORDER_NO VARCHAR(7),
@SUM_1 INT
--声明游标
DECLARE CURSOR_1 CURSOR FOR
SELECT
A.order_no AS order_no
FROM [TRIGGER_オーダ発行H] A WITH (NOLOCK)
WHERE A.[海外生産管理連携区分] = '0'
AND (A.[if_kbn]='20' OR A.[order_skbn]='20')
ORDER BY A.order_no
--打开
OPEN CURSOR_1
--取第一条数据
FETCH NEXT FROM CURSOR_1 INTO @ORDER_NO
--loop开始
WHILE(@@FETCH_STATUS=0)
--业务处理
BEGIN
BEGIN
PRINT '1'
SELECT @SUM_1= ISNULL(SUM(D.size1_su),0),--足数1
FROM [TRIGGER_オーダ発行D] D WITH (NOLOCK)
WHERE D.order_no=@order_no --オーダーナンバー
GROUP BY D.order_no --オーダーナンバー
/****
--详细业务逻辑处理
update
delete
......
****/
END
--取下一条数据
FETCH NEXT FROM CURSOR_1 INTO @ORDER_NO
END
--关闭游标
CLOSE CURSOR_1
--释放游标
DEALLOCATE CURSOR_1
END
IF EXISTS (SELECT * FROM dbo.SYSOBJECTS WHERE ID =
OBJECT_ID(N'[dbo].[pro_test]')
AND OBJECTPROPERTY(ID,N'ISPROCEDURE')=1)
GO
CREATE PROCEDURE pro_test
AS
BEGIN
--变量 存数据
DECLARE
@ORDER_NO VARCHAR(7),
@SUM_1 INT
--声明游标
DECLARE CURSOR_1 CURSOR FOR
SELECT
A.order_no AS order_no
FROM [TRIGGER_オーダ発行H] A WITH (NOLOCK)
WHERE A.[海外生産管理連携区分] = '0'
AND (A.[if_kbn]='20' OR A.[order_skbn]='20')
ORDER BY A.order_no
--打开
OPEN CURSOR_1
--取第一条数据
FETCH NEXT FROM CURSOR_1 INTO @ORDER_NO
--loop开始
WHILE(@@FETCH_STATUS=0)
--业务处理
BEGIN
BEGIN
PRINT '1'
SELECT @SUM_1= ISNULL(SUM(D.size1_su),0),--足数1
FROM [TRIGGER_オーダ発行D] D WITH (NOLOCK)
WHERE D.order_no=@order_no --オーダーナンバー
GROUP BY D.order_no --オーダーナンバー
/****
--详细业务逻辑处理
update
delete
......
****/
END
--取下一条数据
FETCH NEXT FROM CURSOR_1 INTO @ORDER_NO
END
--关闭游标
CLOSE CURSOR_1
--释放游标
DEALLOCATE CURSOR_1
END