数据仓库服务 GAUSSDB(DWS)-表（Oracle）:分区（二）

时间：2024-10-26 11:41:26

数据仓库服务 GAUSSDB(DWS) 模式对象

分区（二）

在非分区表中，删除表“ALTER TABLE TRUNCATE PARTITION”中的数据。

Oracle语法

迁移后语法

       
        
          
          
            CREATE TABLE product_list
(
  product_id      VARCHAR2(20),
  Product_Name    VARCHAR2(50),
  Year_Manufacture vARCHAR2(10)
)
partition by list (Year_Manufacture)
( partition PART_2015 VALUES (2011,2012,2013,2014,2015) pctfree 10 initrans 1 ,
 partition PART_2016 VALUES (2016) pctfree 10 initrans 1 ,
 partition PART_2017 VALUES (2017) pctfree 10 initrans 1 ,
 partition PART_2018 VALUES (2018) pctfree 10 initrans 1 ,
 partition PART_2019 VALUES (2019) pctfree 10 initrans 1 ,
 partition PART_2020 VALUES (2020) pctfree 10 initrans 1 ,
 PARTITION PART_unknown VALUES (DEFAULT) );

CREATE OR REPLACE PROCEDURE List_test
IS
V_ID VARCHAR2(10);
BEGIN
 EXECUTE IMMEDIATE 'ALTER TABLE product_list TRUNCATE PARTITION PART_2020;
 NULL;
END;
/

CREATE OR REPLACE PROCEDURE List_test
IS
V_ID VARCHAR2(10);
BEGIN
 EXECUTE IMMEDIATE 'ALTER TABLE product_list TRUNCATE PARTITION PART_' || V_ID;
 NULL;
END;
/

           

         

       
      

       
        
          
          
            CREATE TABLE product_list
(
  product_id      VARCHAR2(20),
  Product_Name    VARCHAR2(50),
  Year_Manufacture vARCHAR2(10)
)
/*partition by list (Year_Manufacture)
( partition PART_2015 VALUES (2011,2012,2013,2014,2015) pctfree 10 initrans 1 ,
 partition PART_2016 VALUES (2016) pctfree 10 initrans 1 ,
 partition PART_2017 VALUES (2017) pctfree 10 initrans 1 ,
 partition PART_2018 VALUES (2018) pctfree 10 initrans 1 ,
 partition PART_2019 VALUES (2019) pctfree 10 initrans 1 ,
 partition PART_2020 VALUES (2020) pctfree 10 initrans 1 ,
 PARTITION PART_unknown VALUES (DEFAULT) )*/;


CREATE OR REPLACE PROCEDURE List_test
IS
V_ID VARCHAR2(10);
BEGIN
 EXECUTE IMMEDIATE 'ALTER TABLE product_list TRUNCATE PARTITION PART_' || V_ID;
 NULL;
END;
/

CREATE OR REPLACE PROCEDURE List_test
IS
V_ID VARCHAR2(10);
BEGIN
 /* EXECUTE IMMEDIATE 'ALTER TABLE product_list TRUNCATE PARTITION PART_' || V_ID; */
 IF 'PART_' || V_ID = 'PART_2015' THEN
    DELETE FROM product_list WHERE Year_Manufacture IN (2011,2012,2013,2014,2015);
 ELSIF 'PART_' || V_ID = 'PART_2016' THEN
    DELETE FROM product_list WHERE Year_Manufacture IN (2016);
 ELSIF 'PART_' || V_ID = 'PART_2017' THEN
    DELETE FROM product_list WHERE Year_Manufacture IN (2017);
 ELSIF 'PART_' || V_ID = 'PART_2018' THEN
    DELETE FROM product_list WHERE Year_Manufacture IN (2018);
 ELSIF 'PART_' || V_ID = 'PART_2019' THEN
    DELETE FROM product_list WHERE Year_Manufacture IN (2019);
 ELSIF 'PART_' || V_ID = 'PART_2020' THEN
    DELETE FROM product_list WHERE Year_Manufacture IN (2020);
 ELSE
    DELETE FROM product_list WHERE Year_Manufacture NOT IN (2011,2012,2013,2014,2015,2016,2017,2018,2019,2020);
 END IF;
 NULL;
END;
/

           

         

       
      