请教数据库方面经验丰富的高手?高分拜谢
以下为DB2的function,用在oracle下应如何修改CREATE FUNCTION DB2INST1.UPSTATE
(RTID INTEGER
)
RETURNS TABLE
(RID INTEGER
)
SPECIFIC DB2INST1.SQL080506153345600
LANGUAGE SQL
DETERMINISTIC
MODIFIES SQL DATA
STATIC DISPATCH
CALLED ON NULL INPUT
EXTERNAL ACTION
INHERIT SPECIAL REGISTERS
BEGIN ATOMIC
UPDATE DB2INST1.VOUCHERINTERFACE SET GETSTATE = 2
WHERE ID = RTID;
RETURN
SELECT GETSTATE
FROM DB2INST1.VOUCHERINTERFACE
WHERE ID = RTID;
END;
以下为DB2的function,用在oracle下应如何修改CREATE FUNCTION DB2INST1.UPSTATE
(RTID INTEGER
)
RETURNS TABLE
(RID INTEGER
)
SPECIFIC DB2INST1.SQL080506153345600
LANGUAGE SQL
DETERMINISTIC
MODIFIES SQL DATA
STATIC DISPATCH
CALLED ON NULL INPUT
EXTERNAL ACTION
INHERIT SPECIAL REGISTERS
BEGIN ATOMIC
UPDATE DB2INST1.VOUCHERINTERFACE SET GETSTATE = 2
WHERE ID = RTID;
RETURN
SELECT GETSTATE
FROM DB2INST1.VOUCHERINTERFACE
WHERE ID = RTID;
END;
L_C SYS_REFCURSOR;
BEGIN
UPDATE VOUCHERINTERFACE SET GETSTATE = 2 WHERE ID = RTID;
OPEN L_C FOR
SELECT GETSTATE FROM VOUCHERINTERFACE WHERE ID = RTID;
RETURN L_C;
END;
/
...CURSOR_NAME OUT SYS_REFCURSOR...
然后UPDATE语句应该没有问题,不用修改.到最后返回的时候只需给这个定义的输出参数赋值就行:
OPEN CURSOR_NAME FOR
SELECT GETSTATE
FROM DB2INST1.VOUCHERINTERFACE
WHERE ID = RTID;
就可以了. 没有测试环境.下面的不太明白,应该是DB2里特有的.
SPECIFIC DB2INST1.SQL080506153345600
LANGUAGE SQL
DETERMINISTIC
MODIFIES SQL DATA
STATIC DISPATCH
CALLED ON NULL INPUT
EXTERNAL ACTION
INHERIT SPECIAL REGISTERS
给你个例子:CREATE OR REPLACE FUNCTION UPSTATE(RTID INTEGER) RETURN SYS_REFCURSOR IS
L_C SYS_REFCURSOR;
BEGIN
--UPDATE VOUCHERINTERFACE SET GETSTATE = 2 WHERE ID = RTID;
OPEN L_C FOR
SELECT ename FROM scott.emp WHERE empno = RTID;
RETURN L_C;
END;
/--测试
DECLARE
L_C SYS_REFCURSOR;
L_V VARCHAR2(10);
BEGIN
L_C := UPSTATE(7369);
LOOP
FETCH L_C
INTO L_V;
EXIT WHEN L_C%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(L_V);
END LOOP;
CLOSE l_c;
END;
/输出:
SMITH
请教一下oracledbalgtu,
就是
RETURNS TABLE
(RID INTEGER
)这段代码的意思
在db2中是不是返回表函数,而在oracle中只能用游标实现,我数据库很菜,而且没有接触过存储过程,谢谢
http://topic.csdn.net/u/20081009/11/956d6b94-4f08-4466-925f-c0a74d80e24f.html