Oracle 光标内不允许使用 “带子句” 吗？

嗨，汤姆，

对于某些按间隔划分的表，我们需要定期删除旧分区。

例如，要保留按天间隔划分的测试表的60天数据，我可以使用以下SQL获取两个月以上的分区名称，并在以后删除它们:

with max_partition as ( select table_owner, table_name, max(partition_position) max_p from dba_tab_partitions where table_name = 'TEST' and table_owner=‘TEST' group by table_owner, table_name )
select t1.table_owner, t1.table_name, t1.partition_name 
       from dba_tab_partitions t1,
            max_partition t2
       where t1.table_name = 'TEST' 
       and t1.table_owner =‘TEST'
       and t1.table_name = t2.table_name
       and t1.table_owner = t2.table_owner
       and t1.partition_position <= (t2.max_p - 60);
   

-输出:
 
测试测试SYS_P59342
测试测试SYS_P59403
测试测试系统 _ p59543
测试测试系统 _ p59684
测试测试系统 _ p59784
测试测试系统 _ p59883
测试测试SYS_P60002
 
 
为了对多个表执行相同的任务，我创建了一个具有分区数的表，该分区应为每个按间隔分区的表保留。

 

CREATE TABLE TB_PART_RETENTION (
    OWN_NAME VARCHAR2(30),
    TAB_NAME VARCHAR2(30),
    NUM_RETAIN_PARTITION NUMBER);

 
 

Insert into tb_part_retention values (‘TEST’,’TEST’,60);

当我尝试编译以下过程时，使用类似的 “wit clause” 语句，它将失败。错误消息在 “wing子句” 语句的行上说: “SQL语句已忽略”。

光标内不允许使用 “带子句” 吗？

 

CREATE OR REPLACE 
PROCEDURE proc_drop_partition (p_schema IN VARCHAR2, p_table IN VARCHAR2, 
p_num_retention IN NUMBER) AS

CURSOR c_expired_partition IS
WITH max_partition as 
( SELECT table_owner, table_name, max(partition_position) max_p FROM dba_tab_partitions WHERE table_owner=p_schema AND table_name = p_table GROUP BY table_owner, table_name)
SELECT t1.table_owner, t1.table_name, t1.partition_name
       FROM dba_tab_partitions t1,
            max_partition t2          
       WHERE t1.table_name = p_table AND t1.table_owner=p_schema
       AND t1.table_name = t2.table_name
       AND t1.table_owner = t2.table_owner
       AND t1.partition_position <= (t2.max_p - p_num_retention);                          
 
BEGIN
     FOR i_c_expired_partition in c_expired_partition
     LOOP
         exec_command ('ALTER TABLE'||c_expired_partition.table_owner||'.'||c_expired_partition.table_name||' DROP PARTITION '|| c_expired_partition.partition_name || ' UPDATE INDEXES');
     END LOOP;

END proc_drop_partition;

   
显示错误
--
 
过程PROC_DROP_PARTITION compilado
 
错误: 检查编译器日志
错误的过程PROC_DROP_PARTITION:
行/列错误
-
3/5 PL/SQL: SQL语句已忽略
11/9 PL/SQL: ORA-00904: “T1”。“分区名称”: 标识inv á lido
16/5 PL/SQL: 语句已忽略
16/55 PLS-00225: 一个参考文件
 

提前感谢

您可以使用与一个明确的游标声明:

declare
  cursor cur is 
    with rws as (
      select level x from dual
      connect by level <= 10
    )
      select * from rws;
      
begin

  for c in cur loop
    dbms_output.put_line ( c.x );
  end loop;

end;
/

1
2
3
4
5
6
7
8
9
10

问题出在代码中的其他地方。仔细看看你是如何在循环中引用光标记录的。