怎样在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
createdsysdate-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
identifierdouble-quoteddelimited-identifierbindvariableclosecurrentdeletefetchlockinsertopenrollbacksavepointsetsqlexecutecommitforallmergesingle-quotedSQLstringpipe符号"declare在"DROP"继续之前已插入。ORA-06550:第10行,第1列:PLS-00103:出现符号"END"在需要下列之一时：beginfunctionpackagepragmaproceduresubtypetypeuseidentifierdouble-quoteddelimited-identifierformcurrentcursor如将第＊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)IScursor_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.*/WHENOTHERSTHENDBMS_SQL.CLOSE_CURSOR(cursor_id);END;
