如何用變量傳遞表名實現刪除表的操作
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;
