怎样在Oracle用变量名传递表名进行查询?这里面我想删除一些过期的没用的费表．
1.setserveroutputon;
2.declare
3.DropTableNamesys.dba_objects%rowtype;
4.CursorObject_NameisselectOBJECT_NAMEFROMsys.dba_objectswhere5.object_typein('TABLE')and
6.owner='SYSTEM'andcreatedsysdate-2;
7.begin
8.forcnt_varinObject_Name
9.loop
10.fetchObject_NameintoDropTableName.OBJECT_NAME;
＊11.droptableDropTableName.OBJECT_NAME;
12.endloop;
13.end;
/
执行上面的存储过程的时候,出现下面的错误.
DROPTABBLEDropTableName.OBJECT_NAME;
*
ERROR位于第9行:
ORA-06550:第9行,第1列:
PLS-00103:出现符号"DROP"在需要下列之一时：
begincasedeclareend
exitforgotoifloopmodnullpragmaraisereturnselect
updatewhilewith
closecurrentdeletefetchlockinsertopenrollback
savepointsetsqlexecutecommitforallmerge
pipe
符号"declare在"DROP"继续之前已插入。
ORA-06550:第10行,第1列:
PLS-00103:出现符号"END"在需要下列之一时：
beginfunctionpackage
pragmaproceduresuBTypetypeuse
formcurrentcursor
如将第＊11句改为dbms_output.put_line(DropTableName.OBJECT.NAME)程序执行正确．oracle不能用变量传递表名？请教！急急，在线等待！！！
droptableDropTableName.OBJECT_NAME;
改成
executeimmediate'droptable'DropTableName.OBJECT_NAME;
直接执行是不行的，用下面的试试：
SQL_STR='droptable'DropTableName.OBJECT_NAME;
EXECUTEIMMEDATESQL_STR;
用oracle的内部存储过程包dbms_sql构造sql，然后执行。见下面的例子（摘自sqlprograming）
PROCEDUREdrop_object
(object_type_inINVARCHAR2,object_name_inINVARCHAR2)
IS
cursor_idINTEGER;
BEGIN
/*
OpenacursorwhichwillhandlethedynamicSQLstatement.
Thefunctionreturnsthepointertothatcursor.
*/
cursor_id:=DBMS_SQL.OPEN_CURSOR;
/*
Parseandexecutethedropcommandwhichisformedthrough
concatenationofthearguments.
*/
DBMS_SQL.PARSE
(cursor_id,
'DROP'object_type_in''object_name_in,
DBMS_SQL.NATIVE);
/*Closethecursor.*/
DBMS_SQL.CLOSE_CURSOR(cursor_id);
EXCEPTION
/*Ifanyproblemarises,alsomakesurethecursorisclosed.*/
WHENOTHERS
THEN
DBMS_SQL.CLOSE_CURSOR(cursor_id);
END;
