匿名塊中的Oracle注釋

我有以下代碼，它似乎作業正常。當我在匿名塊中添加注釋時，我在第 27 行得到一個語法錯誤。呼叫的是一個名為 ddl 的函式。

怎么呼叫ddl函式，直接去掉ddl呼叫下的2行代碼？

我的目標是通過為所有 ALTER 表命令呼叫 ddl 函式來縮短代碼。

感謝所有回答的人。

CREATE TABLE PARTITION_RETENTION (
   seq_num NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
    TABLE_NAME VARCHAR2(30),
    RETENTION INTERVAL DAY(3) TO SECOND(0),
 CONSTRAINT
partition_retention_pk primary key (table_name),
CONSTRAINT CHK_NON_ZERO_DAYS CHECK (
        RETENTION > INTERVAL '0' DAY
    ),
    CONSTRAINT CHK_WHOLE_DAYS CHECK (
        EXTRACT(HOUR FROM RETENTION) = 0
        AND EXTRACT(MINUTE FROM RETENTION) = 0
        AND EXTRACT(SECOND FROM RETENTION) = 0
    )
);

insert into PARTITION_RETENTION (TABLE_NAME, RETENTION) 
select 'T1', interval '10' day from dual union all
select 'T3', interval '15' day from dual union all
select 'T4', 15 * interval '1' day from dual union all
select 'T5', 5 * interval '1 00:00:00' day to second from dual;

CREATE TABLE t1 (     
 seq_num NUMBER GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT NULL,
   dt   DATE
)
PARTITION BY RANGE (dt)
INTERVAL (NUMTODSINTERVAL(7,'DAY'))
(
   PARTITION OLD_DATA values LESS THAN (TO_DATE('2022-01-01','YYYY-MM-DD'))
);
/

INSERT into t1 (dt)
with dt (dt, interv) as (
select date '2022-01-01', numtodsinterval(1,'DAY') from dual
union all
select dt.dt   interv, interv from dt
where dt.dt   interv < date '2022-02-01')
select dt from dt;
/



DECLARE
    CANNOT_DROP_LAST_PARTITION EXCEPTION;
    PRAGMA EXCEPTION_INIT(CANNOT_DROP_LAST_PARTITION, -14758);

    CANNOT_DROP_ONLY_ONE_PARTITION EXCEPTION;
    PRAGMA EXCEPTION_INIT(CANNOT_DROP_ONLY_ONE_PARTITION, -14083);

   ts TIMESTAMP;
 
   CURSOR TablePartitions IS
    SELECT TABLE_NAME, PARTITION_NAME, p.HIGH_VALUE, t.INTERVAL, RETENTION, DATA_TYPE
    FROM USER_PART_TABLES t
        JOIN USER_TAB_PARTITIONS p USING (TABLE_NAME)
        JOIN USER_PART_KEY_COLUMNS pk ON pk.NAME = TABLE_NAME
        JOIN USER_TAB_COLS tc USING (TABLE_NAME, COLUMN_NAME)
        JOIN PARTITION_RETENTION r USING (TABLE_NAME)
    WHERE        pk.object_type     = 'TABLE' AND
   t.partitioning_type = 'RANGE' AND 
    REGEXP_LIKE (tc.data_type, '^DATE$|^TIMESTAMP.*'); 

BEGIN

    FOR aPart IN TablePartitions LOOP
        EXECUTE IMMEDIATE 'BEGIN :ret := '||aPart.HIGH_VALUE||'; END;' USING OUT ts;
        IF ts < SYSTIMESTAMP - aPart.RETENTION THEN
            BEGIN
                – ddl('alter table '||aPart.TABLE_NAME||' drop partition '||aPart.partition_name);

               EXECUTE IMMEDIATE 'ALTER TABLE '||aPart.TABLE_NAME||' DROP PARTITION '||aPart.PARTITION_NAME|| ' UPDATE GLOBAL INDEXES';
                 DBMS_OUTPUT.PUT_LINE('Dropped partittion '||aPart.PARTITION_NAME ||' from table '||aPart.TABLE_NAME);
            EXCEPTION
                WHEN CANNOT_DROP_ONLY_ONE_PARTITION THEN
                    DBMS_OUTPUT.PUT_LINE('Cannot drop the only partition '||aPart.PARTITION_NAME ||' from table '||aPart.TABLE_NAME);
                    EXECUTE IMMEDIATE 'ALTER TABLE '||aPart.TABLE_NAME||' TRUNCATE PARTITION '||aPart.PARTITION_NAME|| ' UPDATE GLOBAL INDEXES';
                    DBMS_OUTPUT.PUT_LINE('Truncated partittion '||aPart.PARTITION_NAME ||' from table '||aPart.TABLE_NAME);
                WHEN CANNOT_DROP_LAST_PARTITION THEN
                    BEGIN
                        DBMS_OUTPUT.PUT_LINE('Drop last partittion '||aPart.PARTITION_NAME ||' from table '||aPart.TABLE_NAME);
                        EXECUTE IMMEDIATE 'ALTER TABLE '||aPart.TABLE_NAME||' SET INTERVAL ()';
                        EXECUTE IMMEDIATE 'ALTER TABLE '||aPart.TABLE_NAME||' DROP PARTITION '||aPart.PARTITION_NAME;
                        EXECUTE IMMEDIATE 'ALTER TABLE '||aPart.TABLE_NAME||' SET INTERVAL( '||aPart.INTERVAL||' )';            
                    EXCEPTION
                        WHEN CANNOT_DROP_ONLY_ONE_PARTITION THEN 
                            -- Depending on the order the "last" partition can be also the "only" partition at the same time
                            EXECUTE IMMEDIATE 'ALTER TABLE '||aPart.TABLE_NAME||' SET INTERVAL( '||aPart.INTERVAL||' )';
                            DBMS_OUTPUT.PUT_LINE('Cannot drop the only partittion '||aPart.PARTITION_NAME ||' from table '||aPart.TABLE_NAME);
                            EXECUTE IMMEDIATE 'ALTER TABLE '||aPart.TABLE_NAME||' TRUNCATE PARTITION '||aPart.PARTITION_NAME|| ' UPDATE GLOBAL INDEXES';
                            DBMS_OUTPUT.PUT_LINE('Truncated partittion '||aPart.PARTITION_NAME ||' from table '||aPart.TABLE_NAME);
                    END;
            END;
        END IF;
    END LOOP;
END;

uj5u.com熱心網友回復：

在 Oracle 中，有兩種注釋；看看這個例子：

SQL> declare
  2    l_cnt number;
  3  begin
  4    -- this is one-line comment
  5    select count(*) into l_cnt from dual;
  6    /* if you want, you can
  7       comment many lines. Note that
  8       those comments can't be "nested"
  9    */
 10    null;
 11  end;
 12  /

PL/SQL procedure successfully completed.

SQL>

標籤：甲骨文 plsql

上一篇：使用LISTAGG()聚合多列

下一篇：如何在oracle中以某種模式提取列內的值