怎样在oracle用变量名传递表名进行查询?这里面我想删除一些过期的没用的费表．
1.set serveroutput on;
2.declare
3.DropTableName sys.dba_objects%rowtype;
4.Cursor Object_Name is select OBJECT_NAME FROM sys.dba_objects where 5.object_type in('TABLE') and
6.owner='SYSTEM' and created>sysdate-2;
7.begin
8.for cnt_var in Object_Name
9.loop
10.fetch Object_Name into DropTableName.OBJECT_NAME;
＊11.drop table DropTableName.OBJECT_NAME;
12.end loop;
13.end;
/
执行上面的过程的时候,出现下面的错误.
DROP TABBLE DropTableName.OBJECT_NAME;
*
ERROR 位于第 9 行:
ORA-06550: 第 9 行, 第 1 列:
PLS-00103: 出现符号 "DROP"在需要下列之一时：
begin case declare end
exit for goto if loop mod null pragma raise return select
update while with 过程包dbms_sql构造sql，然后执行。见下面的例子（摘自sql programing）
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;
【责编:admin】
--------------------next---------------------
