Oracle存储过程中 如何实现表名可动态传？

[BlueSea_W]

Oracle存储过程中表名用何方法可实现动态传？
或者你有什么变通的方法吗？

[klyh]

不能，我这里也没有变通的方法！

[keisar]

dynamic sql

[BlueSea_W]

keisar
dynamic sql 如何实现？说具体些吧！

[ll779902]

你可以用动态SQL，如：
sql:='select * from '||变量名.....;
execute immediate sql;

[beckhambobo]

CREATE OR REPLACE PACKAGE pkg_test
AS
TYPE myrctype IS REF CURSOR;
END pkg_test;
/

create procedure name_pro(p_table in varchar2,p_rc out pkg_test.myrctype)
as
str varchar2(50);
begin
str:='select * from '||p_table;
open p_rc for str;
end;
/

[hot.wind]

见下面的例子：
PROCEDURE drop_object
(object_type_in IN VARCHAR2, object_name_in IN VARCHAR2)
IS
cursor_id INTEGER;
BEGIN
/*
|| Open a cursor which will handle the dynamic SQL statement.
|| The function returns the pointer to that cursor.
*/
cursor_id := DBMS_SQL.OPEN_CURSOR;
/*
|| Parse and execute the drop command which is formed through
|| concatenation of the arguments.
*/
DBMS_SQL.PARSE
(cursor_id,
'DROP ' || object_type_in || ' ' || object_name_in,
DBMS_SQL.NATIVE);
/* Close the cursor. */
DBMS_SQL.CLOSE_CURSOR (cursor_id);
EXCEPTION
/* If any problem arises, also make sure the cursor is closed. */
WHEN OTHERS
THEN
DBMS_SQL.CLOSE_CURSOR (cursor_id);
END;