sql - 输入日期时如何在oracle中获取分区名称

Question

我有一个包含许多分区范围的表。当我给出日期时，我需要获取所有分区的名称。
例如:如果我输入日期 20/09/2014，它应该列出给定日期之前的所有分区。

create or replace function get_part_name(p_date in date)
return varchar2 is
d date;
retp varchar2(30);
mind date:=to_date('4444-01-01','yyyy-mm-dd');
str varchar2(32000);
cursor c is
select high_value, partition_name p
  from user_tab_partitions
 where table_name='TEST';
begin
  for r in c loop
     str := r.high_value;
     execute immediate 'select '||str||' from dual' into d;     
     if p_date<d and d<mind then
        retp:=r.p;
        mind:=d;
     end if;
  end loop;
  return retp;
end;

这是返回单个日期。我需要所有日期，可以吗？

Answer 1

WITH DATA AS (
select table_name,
       partition_name,
       to_date (
          trim (
          '''' from regexp_substr (
                     extractvalue (
                       dbms_xmlgen.getxmltype (
                       'select high_value from all_tab_partitions where table_name='''
                                || table_name
                                || ''' and table_owner = '''
                                || table_owner
                                || ''' and partition_name = '''
                                || partition_name
                                || ''''),
                             '//text()'),
                          '''.*?''')),
          'syyyy-mm-dd hh24:mi:ss')
          high_value_in_date_format
  FROM all_tab_partitions
 WHERE table_name = 'SALES' AND table_owner = 'SH'
 )
 SELECT * FROM DATA
   WHERE high_value_in_date_format < SYSDATE
/

TABLE_NAME           PARTITION_NAME       HIGH_VALU
-------------------- -------------------- ---------
SALES                SALES_Q4_2003        01-JAN-04
SALES                SALES_Q4_2002        01-JAN-03
SALES                SALES_Q4_2001        01-JAN-02
SALES                SALES_Q4_2000        01-JAN-01
SALES                SALES_Q4_1999        01-JAN-00
SALES                SALES_Q4_1998        01-JAN-99
SALES                SALES_Q3_2003        01-OCT-03
SALES                SALES_Q3_2002        01-OCT-02
SALES                SALES_Q3_2001        01-OCT-01
SALES                SALES_Q3_2000        01-OCT-00
SALES                SALES_Q3_1999        01-OCT-99
SALES                SALES_Q3_1998        01-OCT-98
SALES                SALES_Q2_2003        01-JUL-03
SALES                SALES_Q2_2002        01-JUL-02
SALES                SALES_Q2_2001        01-JUL-01
SALES                SALES_Q2_2000        01-JUL-00
SALES                SALES_Q2_1999        01-JUL-99
SALES                SALES_Q2_1998        01-JUL-98
SALES                SALES_Q1_2003        01-APR-03
SALES                SALES_Q1_2002        01-APR-02
SALES                SALES_Q1_2001        01-APR-01
SALES                SALES_Q1_2000        01-APR-00
SALES                SALES_Q1_1999        01-APR-99
SALES                SALES_Q1_1998        01-APR-98
SALES                SALES_H2_1997        01-JAN-98
SALES                SALES_H1_1997        01-JUL-97
SALES                SALES_1996           01-JAN-97
SALES                SALES_1995           01-JAN-96

28 rows selected.

SQL>

使用您想要的日期代替 SYSDATE在上面的查询中。或者您可以通过 FUNCTION 将其作为 INPUT 传递和 RETURN结果集。