DECLARE V_SQL VARCHAR2(200); BEGIN FOR T IN ( SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE='TABLE' AND OBJECT_NAME LIKE 'TEST%') LOOP V_SQL :='DROP TABLE '|| T.OBJECT_NAME; EXECUTE IMMEDIATE v_sql; END LOOP;END;
先谢谢楼上和各位大哥们,小弟还有不明白的地方 as DECLARE V_SQL VARCHAR2(200); begin FOR T IN ( SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE='TABLE' AND OBJECT_NAME LIKE 'TEST%') LOOP V_SQL :='DROP TABLE '|| T.OBJECT_NAME ||'purge' EXECUTE IMMEDIATE V_SQL; END LOOP; end; 行号= 3 列号= 1 错误文本= PLS-00103: 出现符号 "DECLARE"在需要下列之一时: begin function package pragma procedure subtype type use <an identifier> <a double-quoted delimited-identifier> form current cursor external language 符号 "begin" 被替换为 "DECLARE" 后继续。 行号= 9 列号= 1 错误文本= PLS-00103: 出现符号 "EXECUTE"在需要下列之一时: . ( * @ % & = - + ; < / > at in is mod remainder not rem <an exponent (**)> <> or != or ~= >= <= <> and or like between || member SUBMULTISET_ 我编译就出这个错误,请问怎么解决!
SQL> CREATE OR REPLACE PROCEDURE eric_1 2 IS 3 V_SQL VARCHAR2(200); 4 BEGIN 5 FOR T IN ( 6 select table_name 7 from all_tables 8 where table_name like 'test%') 9 LOOP 10 V_SQL :='DROP TABLE '|| T.table_name; 11 EXECUTE IMMEDIATE v_sql; 12 END LOOP; 13 14 END; 15 /Procedure created.SQL> exec eric_1PL/SQL procedure successfully completed.SQL>
as DECLARE V_SQL VARCHAR2(200); 这个就有问题 你应该好好看看PL/SQL中如何定义变量 以及PL/SQL的语法
to- Eric_1999 大哥,我执行你的语句V_SQL VARCHAR2(200); 出错了
我是运行过的,例如:我删除表以TMP开头的:SQL> SELECT object_name 2 FROM all_objects 3 WHERE object_type = 'TABLE' AND object_name LIKE 'TMP%' 4 /OBJECT_NAME ------------------------------ TMP TMP1SQL> select * from tmp 2 /NAMES COURSE GRADE ---------- ---------- --------- a 20 30 b 20 30 c 20 30 d 20 30SQL> select * from tmp1 2 /AA BB CC ---------- ---------- --------- d 20 30 c 20 30 b 20 30 a 20 30SQL> CREATE OR REPLACE PROCEDURE eric_1 2 IS 3 V_SQL VARCHAR2(200); 4 BEGIN 5 FOR T IN ( 6 select table_name 7 from all_tables 8 where table_name like 'TMP%') 9 LOOP 10 V_SQL :='DROP TABLE '|| T.table_name; 11 EXECUTE IMMEDIATE v_sql; 12 END LOOP; 13 END; 14 /Procedure created.SQL> set serveroutput on SQL> CREATE OR REPLACE PROCEDURE eric_1 2 IS 3 V_SQL VARCHAR2(200); 4 BEGIN 5 FOR T IN ( 6 select table_name 7 from all_tables 8 where table_name like 'TMP%') 9 LOOP 10 V_SQL :='DROP TABLE '|| T.table_name; 11 EXECUTE IMMEDIATE v_sql; 12 END LOOP; 13 END; 14 /Procedure created.SQL> exec eric_1PL/SQL procedure successfully completed.SQL> SELECT object_name 2 FROM all_objects 3 WHERE object_type = 'TABLE' AND object_name LIKE 'TMP%' 4 /no rows selectedSQL> select * from tmp 2 / select * from tmp * ERROR at line 1: ORA-00942: table or view does not exist SQL> select * from tmp1 2 / select * from tmp1 * ERROR at line 1: ORA-00942: table or view does not exist SQL>
BEGIN
FOR T IN (
SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE='TABLE' AND OBJECT_NAME LIKE 'TEST%')
LOOP
V_SQL :='DROP TABLE '|| T.OBJECT_NAME;
EXECUTE IMMEDIATE v_sql;
END LOOP;END;
as
DECLARE V_SQL VARCHAR2(200);
begin
FOR T IN (
SELECT * FROM USER_OBJECTS WHERE OBJECT_TYPE='TABLE' AND OBJECT_NAME LIKE 'TEST%')
LOOP
V_SQL :='DROP TABLE '|| T.OBJECT_NAME ||'purge'
EXECUTE IMMEDIATE V_SQL;
END LOOP;
end;
行号= 3 列号= 1 错误文本= PLS-00103: 出现符号 "DECLARE"在需要下列之一时: begin function package pragma procedure subtype type use <an identifier> <a double-quoted delimited-identifier> form current cursor external language 符号 "begin" 被替换为 "DECLARE" 后继续。
行号= 9 列号= 1 错误文本= PLS-00103: 出现符号 "EXECUTE"在需要下列之一时: . ( * @ % & = - + ; < / > at in is mod remainder not rem <an exponent (**)> <> or != or ~= >= <= <> and or like between || member SUBMULTISET_
我编译就出这个错误,请问怎么解决!
2 IS
3 V_SQL VARCHAR2(200);
4 BEGIN
5 FOR T IN (
6 select table_name
7 from all_tables
8 where table_name like 'test%')
9 LOOP
10 V_SQL :='DROP TABLE '|| T.table_name;
11 EXECUTE IMMEDIATE v_sql;
12 END LOOP;
13
14 END;
15 /Procedure created.SQL> exec eric_1PL/SQL procedure successfully completed.SQL>
DECLARE V_SQL VARCHAR2(200);
这个就有问题
你应该好好看看PL/SQL中如何定义变量 以及PL/SQL的语法
大哥,我执行你的语句V_SQL VARCHAR2(200);
出错了
2 FROM all_objects
3 WHERE object_type = 'TABLE' AND object_name LIKE 'TMP%'
4 /OBJECT_NAME
------------------------------
TMP
TMP1SQL> select * from tmp
2 /NAMES COURSE GRADE
---------- ---------- ---------
a 20 30
b 20 30
c 20 30
d 20 30SQL> select * from tmp1
2 /AA BB CC
---------- ---------- ---------
d 20 30
c 20 30
b 20 30
a 20 30SQL> CREATE OR REPLACE PROCEDURE eric_1
2 IS
3 V_SQL VARCHAR2(200);
4 BEGIN
5 FOR T IN (
6 select table_name
7 from all_tables
8 where table_name like 'TMP%')
9 LOOP
10 V_SQL :='DROP TABLE '|| T.table_name;
11 EXECUTE IMMEDIATE v_sql;
12 END LOOP;
13 END;
14 /Procedure created.SQL> set serveroutput on
SQL> CREATE OR REPLACE PROCEDURE eric_1
2 IS
3 V_SQL VARCHAR2(200);
4 BEGIN
5 FOR T IN (
6 select table_name
7 from all_tables
8 where table_name like 'TMP%')
9 LOOP
10 V_SQL :='DROP TABLE '|| T.table_name;
11 EXECUTE IMMEDIATE v_sql;
12 END LOOP;
13 END;
14 /Procedure created.SQL> exec eric_1PL/SQL procedure successfully completed.SQL> SELECT object_name
2 FROM all_objects
3 WHERE object_type = 'TABLE' AND object_name LIKE 'TMP%'
4 /no rows selectedSQL> select * from tmp
2 /
select * from tmp
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> select * from tmp1
2 /
select * from tmp1
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL>