目标是使用从insert/update/delete语句中返回的表名。它无法实现所需的结果。
如果我们使用 table 执行(表如参考中所述(
中所述(delete from TABLE
(select DECODE(this returns table name as string) from REF_TABLE where where_clause);
然后:22905. 00000-"无法从非巢表项目访问行"
如果我们没有表执行:
delete from
(select DECODE(this returns table name as string) from REF_TABLE where where_clause);
然后,它实际上从ref_table中删除了满足条款时满足的记录。
将表名名称从子选项传递到外部查询的正确方法是什么?
doc示例
EXECUTE IMMEDIATE 'delete from table :1' USING (select DECODE(...) from REF_TABLE WHERE where_clause);
pls-00103:遇到符号"从表中删除:1" 期望以下一个:
:=。( @%; 06550。00000-"线%s,列%s: n%s" *原因:通常是PL/SQL汇编误差。 *动作:
执行DML查询时您从不提及表关键字,例如插入,更新和删除
delete from table_name;
update table_name set field='value';
insert into table_name (field) values ('value');
表关键字将在您执行诸如创建,更改,掉落和截断之类的DDL语句时提到
Create table table_name (field varchar2(100));
Alter table table_name modify field1 varchar2(1000);
Drop table table_name;
truncate table table_name;
因此,您提到的以下查询是正确的
delete from
(select DECODE(this returns table name as string) from REF_TABLE where where_clause);
唯一使用上述查询的限制是,您必须从固定表或视图中使用table_name源,即无法从数据字典视图中挑选出来,否则您将低于错误。
SQL Error: ORA-02030: can only select from fixed tables/views
02030. 00000 - "can only select from fixed tables/views"
*Cause: An attempt is being made to perform an operation other than
a retrieval from a fixed table/view.
*Action: You may only select rows from fixed tables/views.
您可以使用与下面相似的PL/SQL块递归删除表
DECLARE
CURSOR c
IS
SELECT table_name FROM user_tables WHERE table_name LIKE '%SANDEEP26FEB16_2%';
BEGIN
FOR c1 IN c
LOOP
EXECUTE IMMEDIATE 'delete from ' || c1.table_name;
COMMIT;
END LOOP;
END;