alter procedure proc_oracle_text
AS
DECLARE @ID_READER int
DECLARE @CD_STATE_DEV char(2)
DECLARE @FLG_DISTRIBUTED tinyint
DECLARE @CD_AREA char(2)
DECLARE @NAME_CREATOR varchar(64)
DECLARE @DT_CREATE datetime
DECLARE @NAME_MODIFIER varchar(64)
DECLARE @DT_MODIFICATION datetime
DECLARE @RMK varchar(128)
DECLARE @FLAG intIF NOT EXISTS(SELECT * FROM OBJ_ORACLE_TEMP WHERE SING = 0)
BEGIN
TRUNCATE TABLE OBJ_ORACLE_TEMP
RETURN
ENDdeclare test_cur_sql cursor
for
select id_reader,
cd_state_dev,
flg_distributed,
cd_area,
name_creator,
dt_create,
name_modifier,
dt_modification,
rmk,
flag
from OBJ_ORACLE_TEMP
where SING = 0
for UPDATEOPEN test_cur_sql
FETCH NEXT FROM test_cur_sql
INTO @ID_READER,
@CD_STATE_DEV,
@FLG_DISTRIBUTED,
@CD_AREA,
@NAME_CREATOR,
@DT_CREATE,
@NAME_MODIFIER,
@DT_MODIFICATION,
@RMK,
@FLAG
WHILE @@FETCH_STATUS=0
BEGIN
IF(@FLAG=1)--插入
BEGIN
INSERT INTO VIEW_ORACLE_READER (
reader_id,
reader_state,
reader_assigned,
entry_user,
entry_ymd,
update_user,
update_ymd,
re
)
VALUES (
@ID_READER,
@CD_STATE_DEV,
@FLG_DISTRIBUTED,
'shzt',
getdate(),
'shzt',
getdate(),
@RMK
)
END
IF(@FLAG=2)--修改
BEGIN
UPDATE VIEW_ORACLE_READER
SET reader_id=@ID_READER,
reader_state=@CD_STATE_DEV,
reader_assigned=@FLG_DISTRIBUTED,
re=@RMK
where reader_id=@ID_READER
END
IF(@FLAG=3)--删除
BEGIN
DELETE FROM VIEW_ORACLE_READER
[color=#00FF00]where current of test_cur_sql END[/color]
UPDATE OBJ_ORACLE_TEMP
SET SING = 1
WHERE current of test_cur_sql
FETCH NEXT FROM test_cur_sql INTO @ID_READER,
@CD_STATE_DEV,
@FLG_DISTRIBUTED,
@CD_AREA,
@NAME_CREATOR,
@DT_CREATE,
@NAME_MODIFIER,
@DT_MODIFICATION,
@RMK,
@FLAG
END
DEALLOCATE test_cur_sql
报错信息:消息 16933,级别 16,状态 1,过程 proc_oracle_text,第 75 行
此游标不包括正在修改的表,或该表不能通过此游标更新。
语句已终止。where current of test_cur_sql END[/color] 可以用在当前的delete里么?
AS
DECLARE @ID_READER int
DECLARE @CD_STATE_DEV char(2)
DECLARE @FLG_DISTRIBUTED tinyint
DECLARE @CD_AREA char(2)
DECLARE @NAME_CREATOR varchar(64)
DECLARE @DT_CREATE datetime
DECLARE @NAME_MODIFIER varchar(64)
DECLARE @DT_MODIFICATION datetime
DECLARE @RMK varchar(128)
DECLARE @FLAG intIF NOT EXISTS(SELECT * FROM OBJ_ORACLE_TEMP WHERE SING = 0)
BEGIN
TRUNCATE TABLE OBJ_ORACLE_TEMP
RETURN
ENDdeclare test_cur_sql cursor
for
select id_reader,
cd_state_dev,
flg_distributed,
cd_area,
name_creator,
dt_create,
name_modifier,
dt_modification,
rmk,
flag
from OBJ_ORACLE_TEMP
where SING = 0
for UPDATEOPEN test_cur_sql
FETCH NEXT FROM test_cur_sql
INTO @ID_READER,
@CD_STATE_DEV,
@FLG_DISTRIBUTED,
@CD_AREA,
@NAME_CREATOR,
@DT_CREATE,
@NAME_MODIFIER,
@DT_MODIFICATION,
@RMK,
@FLAG
WHILE @@FETCH_STATUS=0
BEGIN
IF(@FLAG=1)--插入
BEGIN
INSERT INTO VIEW_ORACLE_READER (
reader_id,
reader_state,
reader_assigned,
entry_user,
entry_ymd,
update_user,
update_ymd,
re
)
VALUES (
@ID_READER,
@CD_STATE_DEV,
@FLG_DISTRIBUTED,
'shzt',
getdate(),
'shzt',
getdate(),
@RMK
)
END
IF(@FLAG=2)--修改
BEGIN
UPDATE VIEW_ORACLE_READER
SET reader_id=@ID_READER,
reader_state=@CD_STATE_DEV,
reader_assigned=@FLG_DISTRIBUTED,
re=@RMK
where reader_id=@ID_READER
END
IF(@FLAG=3)--删除
BEGIN
DELETE FROM VIEW_ORACLE_READER
[color=#00FF00]where current of test_cur_sql END[/color]
UPDATE OBJ_ORACLE_TEMP
SET SING = 1
WHERE current of test_cur_sql
FETCH NEXT FROM test_cur_sql INTO @ID_READER,
@CD_STATE_DEV,
@FLG_DISTRIBUTED,
@CD_AREA,
@NAME_CREATOR,
@DT_CREATE,
@NAME_MODIFIER,
@DT_MODIFICATION,
@RMK,
@FLAG
END
DEALLOCATE test_cur_sql
报错信息:消息 16933,级别 16,状态 1,过程 proc_oracle_text,第 75 行
此游标不包括正在修改的表,或该表不能通过此游标更新。
语句已终止。where current of test_cur_sql END[/color] 可以用在当前的delete里么?
AS
DECLARE @ID_READER INT
DECLARE @CD_STATE_DEV CHAR(2)
DECLARE @FLG_DISTRIBUTED TINYINT
DECLARE @CD_AREA CHAR(2)
DECLARE @NAME_CREATOR VARCHAR(64)
DECLARE @DT_CREATE DATETIME
DECLARE @NAME_MODIFIER VARCHAR(64)
DECLARE @DT_MODIFICATION DATETIME
DECLARE @RMK VARCHAR(128)
DECLARE @FLAG INT
IF NOT EXISTS(
SELECT *
FROM OBJ_ORACLE_TEMP
WHERE SING = 0
)
BEGIN
TRUNCATE TABLE OBJ_ORACLE_TEMP
END
DECLARE test_cur_sql CURSOR FOR
SELECT id_reader, cd_state_dev, flg_distributed, cd_area, name_creator, dt_create,
name_modifier, dt_modification, rmk, flag
FROM OBJ_ORACLE_TEMP
WHERE SING = 0
OPEN test_cur_sql
FETCH NEXT FROM test_cur_sql
INTO @ID_READER,@CD_STATE_DEV,@FLG_DISTRIBUTED,@CD_AREA,
@NAME_CREATOR, @DT_CREATE,@NAME_MODIFIER,@DT_MODIFICATION,@RMK,@FLAG
WHILE @@FETCH_STATUS=0
BEGIN
IF (@FLAG=1)--插入
BEGIN
INSERT INTO VIEW_ORACLE_READER( reader_id, reader_state, reader_assigned, entry_user,
entry_ymd, update_user, update_ymd, re )
VALUES( @ID_READER, @CD_STATE_DEV, @FLG_DISTRIBUTED,
'shzt', GETDATE(), 'shzt', GETDATE(), @RMK )
END
IF (@FLAG=2)--修改
BEGIN
UPDATE VIEW_ORACLE_READER
SET reader_id = @ID_READER, reader_state = @CD_STATE_DEV, reader_assigned = @FLG_DISTRIBUTED,
re = @RMK
WHERE reader_id=@ID_READER
END
IF (@FLAG=3)--删除
BEGIN
DELETE
FROM VIEW_ORACLE_READER
WHERE reader_id=@ID_READER
END
UPDATE OBJ_ORACLE_TEMP
SET SING = 1
WHERE CURRENT OF test_cur_sql
FETCH NEXT FROM test_cur_sql
INTO @ID_READER,@CD_STATE_DEV, @FLG_DISTRIBUTED, @CD_AREA, @NAME_CREATOR,
@DT_CREATE,@NAME_MODIFIER,@DT_MODIFICATION,@RMK,@FLAG
END
CLOSE test_cur_sql
DEALLOCATE test_cur_sql
GO
此游标不包括正在修改的表,或该表不能通过此游标更新。
语句已终止。