这是一个包名称为trigger_pkg下的一个过程,现在把他改成sqlserver下的,初学者,各位帮忙看看
oracle:
TYPE REC_ID IS RECORD (id define.id%TYPE);
TYPE CUR_ID IS REF CURSOR RETURN REC_ID;create or replace PROCEDURE delete_define
(
dev_id_var INT,
trigger_flag INT
)
AS
action_str VARCHAR2(1024);
rec_curs TRIGGER_PKG.REC _ID;
curs trigger_pkg.CUR_ID;
BEGIN IF (trigger_flag = 1)
THEN
trigger_pkg.get_id_by_key(dev_id_var);
LOOP
FETCH curs INTO rec_curs ;
EXIT WHEN (curs %NOTFOUND);
action_str := 'DELETE rl_ define WHERE id = ' || char_to_keyidvalue(rec_curs .id));
trigger_pkg.insert_trigger_actions(action_str, 1);
END LOOP; CLOSE curs;
END IF;
DELETE FROM RL_ DEFINE WHERE (to_number(substr( ID, 1, 10)) = dev_id_var);
RETURN;
END;sqlserver:create procedure delete_define
@dev_id_var int,
@trigger_flag int
AS
DECLARE
@action_str varchar(1024),
@curs cursor for select id from define
BEGIN IF (@trigger_flag = 1)
BEGIN
EXEC dbo. get_id_by_key @dev_id_var,
OPEN @curs
while (@@fetch_status = 0)
BEGIN
FETCH NEXT FROM @curs
SET @action_str = ('DELETE define WHERE id = ' + isnull(CHAR_TO_KEYIDVALUE(@ id), ''))
EXEC insert_trigger_actions @action_str, 1
END
CLOSE @curs
DEALLOCATE @curs
END
DELETE FROM dbo. DEFINE
WHERE CAST(SUBSTRING( DEFINE. ID, 1, 10) AS int) = @dev_id_var END
oracle:
TYPE REC_ID IS RECORD (id define.id%TYPE);
TYPE CUR_ID IS REF CURSOR RETURN REC_ID;create or replace PROCEDURE delete_define
(
dev_id_var INT,
trigger_flag INT
)
AS
action_str VARCHAR2(1024);
rec_curs TRIGGER_PKG.REC _ID;
curs trigger_pkg.CUR_ID;
BEGIN IF (trigger_flag = 1)
THEN
trigger_pkg.get_id_by_key(dev_id_var);
LOOP
FETCH curs INTO rec_curs ;
EXIT WHEN (curs %NOTFOUND);
action_str := 'DELETE rl_ define WHERE id = ' || char_to_keyidvalue(rec_curs .id));
trigger_pkg.insert_trigger_actions(action_str, 1);
END LOOP; CLOSE curs;
END IF;
DELETE FROM RL_ DEFINE WHERE (to_number(substr( ID, 1, 10)) = dev_id_var);
RETURN;
END;sqlserver:create procedure delete_define
@dev_id_var int,
@trigger_flag int
AS
DECLARE
@action_str varchar(1024),
@curs cursor for select id from define
BEGIN IF (@trigger_flag = 1)
BEGIN
EXEC dbo. get_id_by_key @dev_id_var,
OPEN @curs
while (@@fetch_status = 0)
BEGIN
FETCH NEXT FROM @curs
SET @action_str = ('DELETE define WHERE id = ' + isnull(CHAR_TO_KEYIDVALUE(@ id), ''))
EXEC insert_trigger_actions @action_str, 1
END
CLOSE @curs
DEALLOCATE @curs
END
DELETE FROM dbo. DEFINE
WHERE CAST(SUBSTRING( DEFINE. ID, 1, 10) AS int) = @dev_id_var END
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货