这是一个包名称为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
--------------------------
你的这个是个函数吧
@dev_id_var int,
@trigger_flag int
AS
begin
declare @action_str varchar(1024)
declare @id varchar(20) --接受游标取出的id值的变量
declare curs cursor for select [id] from define --id列为什么数据类型? 我把它当作是字符串类型
IF (@trigger_flag = 1)
BEGIN
select dbo.get_id_by_key(@dev_id_var) --我认为这个是函数 ,如果是过程就用exec 执行
OPEN curs
FETCH NEXT FROM curs into @id
while (@@fetch_status = 0)
BEGIN
SET @action_str = ('DELETE define WHERE id = ' + isnull(@id, ''))
EXEC insert_trigger_actions @action_str, 1
END
CLOSE curs
DEALLOCATE curs
DELETE FROM dbo.DEFINE
WHERE CAST(SUBSTRING(ID,1,10) AS int) = @dev_id_var
END
end
OPEN curs
FETCH NEXT FROM curs into @id
while (@@fetch_status = 0)
BEGIN
SET @action_str = ('DELETE define WHERE id = ' + isnull(@id, ''))
EXEC insert_trigger_actions @action_str, 1
FETCH NEXT FROM curs into @id
END
CLOSE curs
DEALLOCATE curs