你可以用动态SQL,如: sql:='select * from '||变量名.....; execute immediate sql;
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; /
见下面的例子: 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;
sql:='select * from '||变量名.....;
execute immediate sql;
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;
/
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;