如何用变量传递表名实现删除表的操作
2024-07-21 02:39:09
供稿：网友
怎样在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
<<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge
pipe
符号 "declare在 "DROP" 继续之前已插入。
ORA-06550: 第 10 行, 第 1 列:
PLS-00103: 出现符号 "END"在需要下列之一时：
begin function package
pragma procedure suBType type use
form current cursor
如将第＊11句改为 dbms_output. put_line(DropTableName.OBJECT.NAME)程序执行正确．oracle 不能用变量传递表名？请教！急急，在线等待！！！
drop table DropTableName.OBJECT_NAME;
改成
execute immediate 'drop table ' DropTableName.OBJECT_NAME;
直接执行是不行的，用下面的试试：
SQL_STR = 'drop table ' DropTableName.OBJECT_NAME ;
EXECUTE IMMEDATE SQL_STR;
用oracle的内部存储过程包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;
