Oracle 动态传递分区名称以从分区表中获取特定分区的记录

CREATE TABLE TOM_RESER_DERESERVATION
(
  ELEMENT_id           VARCHAR2(200 BYTE),
  ELEMENT_LABEL        VARCHAR2(200 BYTE),
  PRODUCT_NAME         VARCHAR2(100 BYTE),
  CIRCLE               VARCHAR2(100 BYTE),
  COUNTRY              VARCHAR2(150 BYTE)
) partition     by list (COUNTRY) 
  subpartition  by list (circle)
(
partition P1 values('INDIA')
(
 subpartition EP11 values('ANE'),
 subpartition EP12 values('AP'),
 subpartition EP13 values('BIH'),
 subpartition EP14 values('CHN'),
 subpartition EP15 values('DEL'),
 subpartition EP16 values('GUJ'),
 subpartition EP17 values('HAR'),
 subpartition EP18 values('HP'),
 subpartition EP19 values('JNK'),
 subpartition EP20 values('KAR'),
 subpartition EP21 values('KER'),
 subpartition EP22 values('KOL'),
 subpartition EP23 values('MAH'),
 subpartition EP24 values('MP'),
 subpartition EP25 values('MUM'),
 subpartition EP26 values('NLD'),
 subpartition EP27 values('ORS'),
 subpartition EP28 values('PUN'),
 subpartition EP29 values('RAJ'),
 subpartition EP30 values('ROB'),
 subpartition EP31 values('ROTN'),
 subpartition EP32 values('UPE'),
 subpartition EP33 values('UPW'),
 subpartition EP63 values(default)
)
) enable row movement

begin
insert into TOM_RESER_DERESERVATION values('1','ABC','Light','ANE','INDIA');
insert into TOM_RESER_DERESERVATION values('2','PQR','Wires','ANE','INDIA');
insert into TOM_RESER_DERESERVATION values('3','STV','Nuts','ANE','INDIA');
insert into TOM_RESER_DERESERVATION values('1','ABC','Light','HP','INDIA');
insert into TOM_RESER_DERESERVATION values('2','PQR','Wires','HP','INDIA');
insert into TOM_RESER_DERESERVATION values('3','STV','Nuts','HP','INDIA');
insert into TOM_RESER_DERESERVATION values('4','XYZ','Clips','HP','INDIA');
insert into TOM_RESER_DERESERVATION values('1','ABC','Rubbers','MUM','INDIA');
insert into TOM_RESER_DERESERVATION values('2','PQR','Typres','MUM','INDIA');
insert into TOM_RESER_DERESERVATION values('3','STV','Chains','MUM','INDIA');
end;


CREATE TABLE TOM_RESER_PARTITION_LIST
(
  PARTITION       VARCHAR2(50 BYTE),
  PARTITION_NAME  VARCHAR2(50 BYTE),
  COUNTRY          VARCHAR2(100 BYTE)
);

select * from TOM_RESER_PARTITION_LIST;

create type circle as table of varchar2(25);

CREATE TABLE USER_TAB
(
  SR_NO         NUMBER                          NOT NULL,
  USER_MAIL_ID  VARCHAR2(280 BYTE),
  PASSWORD      VARCHAR2(300 BYTE)              NOT NULL,
  USER_TYPE     VARCHAR2(60 BYTE)               NOT NULL,
  CIRCLES       CIRCLE
) nested table circles store as Nest_TOM;

create table user_partition_mapping ( 
    USER_SR_NO      NUMBER,
    PARTITION_NAME  VARCHAR2(50 BYTE)
);

CREATE OR REPLACE TRIGGER user_mapping
   AFTER INSERT
   ON user_tab
   FOR EACH ROW
DECLARE
   PRAGMA AUTONOMOUS_TRANSACTION;
   sqlcodes   VARCHAR2 (100);
   sqlmsg     VARCHAR2 (400);
BEGIN
   INSERT INTO user_partition_mapping
      (SELECT *
         FROM (WITH w AS
                    (SELECT :NEW.sr_no sr_no, t.COLUMN_VALUE circle
                       FROM DUAL, TABLE (:NEW.circles) t)
               SELECT w.sr_no, PARTITION
                 FROM tom_reser_partition_list, w
                WHERE partition_name = w.circle));

   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      sqlcodes := SQLCODE;
      sqlmsg := SQLERRM;

      INSERT INTO user_creation_errors
           VALUES (sqlcodes, sqlmsg);

      COMMIT;
END reser_user_mapping;


insert into USER_TAB values(1,'ABC@Ora.com','QWERT','Circle',circle('ANE','HP'));

insert into USER_TAB values(2,'ABC@Ora.com','QWERT','Circle',circle('MUM'));

I am trying to get partition names dynamically for which a user has been given access at the time of registration from another table named as
user_partition_mapping. but i am not able to run below query as im getting error "ORA-00933: SQL command not properly ended".
Please can you help me out for the below query?

---- 基于有权访问分区的用户查询TOM_RESER_DERESERVATION

WITH w AS
     (SELECT urm.partition_name, u.sr_no, u.user_type
        FROM user_partition_mapping urm, user_tab u
       WHERE u.sr_no = urm.user_sr_no
         AND u.user_mail_id = 'ABC@Ora.com')
SELECT ELEMENT_ID, ELEMENT_LABEL, PRODUCT_NAME, CIRCLE, COUNTRY
  FROM TOM_RESER_DERESERVATION SUBPARTITION (select distinct partition_name from w);

您不能在 (sub)partition子句中使用子查询。您必须命名分区。

但是还有另一种方法可以指定分区:

分区

使用此方法，传递分区键的值。它会返回你那个 (子) 分区:

CREATE TABLE TOM_RESER_DERESERVATION
(
  ELEMENT_id           VARCHAR2(200 BYTE),
  ELEMENT_LABEL        VARCHAR2(200 BYTE),
  PRODUCT_NAME         VARCHAR2(100 BYTE),
  CIRCLE               VARCHAR2(100 BYTE),
  COUNTRY              VARCHAR2(150 BYTE)
) partition     by list (COUNTRY) 
  subpartition  by list (circle)
(
partition P1 values('INDIA')
(
 subpartition EP11 values('ANE'),
 subpartition EP12 values('AP'),
 subpartition EP13 values('BIH'),
 subpartition EP14 values('CHN'),
 subpartition EP15 values('DEL'),
 subpartition EP16 values('GUJ'),
 subpartition EP17 values('HAR'),
 subpartition EP18 values('HP'),
 subpartition EP19 values('JNK'),
 subpartition EP20 values('KAR'),
 subpartition EP21 values('KER'),
 subpartition EP22 values('KOL'),
 subpartition EP23 values('MAH'),
 subpartition EP24 values('MP'),
 subpartition EP25 values('MUM'),
 subpartition EP26 values('NLD'),
 subpartition EP27 values('ORS'),
 subpartition EP28 values('PUN'),
 subpartition EP29 values('RAJ'),
 subpartition EP30 values('ROB'),
 subpartition EP31 values('ROTN'),
 subpartition EP32 values('UPE'),
 subpartition EP33 values('UPW'),
 subpartition EP63 values(default)
)
) enable row movement;

begin
insert into TOM_RESER_DERESERVATION values('1','ABC','Light','ANE','INDIA');
insert into TOM_RESER_DERESERVATION values('2','PQR','Wires','ANE','INDIA');
insert into TOM_RESER_DERESERVATION values('3','STV','Nuts','ANE','INDIA');
insert into TOM_RESER_DERESERVATION values('1','ABC','Light','HP','INDIA');
insert into TOM_RESER_DERESERVATION values('2','PQR','Wires','HP','INDIA');
insert into TOM_RESER_DERESERVATION values('3','STV','Nuts','HP','INDIA');
insert into TOM_RESER_DERESERVATION values('4','XYZ','Clips','HP','INDIA');
insert into TOM_RESER_DERESERVATION values('1','ABC','Rubbers','MUM','INDIA');
insert into TOM_RESER_DERESERVATION values('2','PQR','Typres','MUM','INDIA');
insert into TOM_RESER_DERESERVATION values('3','STV','Chains','MUM','INDIA');
end;
/
commit;

select element_id, element_label, product_name, circle, country
  from tom_reser_dereservation sub分区 ( 'INDIA', 'ANE' );

ELEMENT_ID   ELEMENT_LABEL   PRODUCT_NAME   CIRCLE   COUNTRY   
1            ABC             Light          ANE      INDIA     
2            PQR             Wires          ANE      INDIA     
3            STV             Nuts           ANE      INDIA   

select element_id, element_label, product_name, circle, country
  from tom_reser_dereservation 分区 ( 'INDIA' );

ELEMENT_ID   ELEMENT_LABEL   PRODUCT_NAME   CIRCLE   COUNTRY   
1            ABC             Light          ANE      INDIA     
2            PQR             Wires          ANE      INDIA     
3            STV             Nuts           ANE      INDIA     
1            ABC             Light          HP       INDIA     
2            PQR             Wires          HP       INDIA     
3            STV             Nuts           HP       INDIA     
4            XYZ             Clips          HP       INDIA     
1            ABC             Rubbers        MUM      INDIA     
2            PQR             Typres         MUM      INDIA     
3            STV             Chains         MUM      INDIA

但同样，您不能使用子查询。或绑定变量。所以无论哪种方式，你都在看动态SQL。

这带来了一个问题:

你为什么需要这样做？您可以在分区键上加入并让数据库为您进行分区消除吗？