How to dynamically construct table name-CodePudding

I would like to construct a query where a table name is based off of another table's column mod 12. For example:

SELECT * 
FROM table_b_XX

where XX here is determined by table_a.column_a % 12.

CodePudding user response：

Presuming you have such a tables:

SQL> create table table_a as
  2  select 1212 as column_a from dual;

Table created.

As the following result returns 0, we need table_b_00 so I'll create it:

SQL> select mod(1212, 12) from dual;

MOD(1212,12)
------------
           0

SQL> create table table_b_00 as select 'table 00' name from dual;

Table created.

SQL> create table table_b_01 as select 'table 01' name from dual;

Table created.

Now, create a function which returns ref cursor; it selects rows from a table whose name is designed by the help of the table_a contents:

SQL> create or replace function f_test return sys_refcursor
  2  is
  3    l_str varchar2(200);
  4    rc    sys_refcursor;
  5  begin
  6    select 'select * from table_b_' || lpad(mod(a.column_a, 12), 2, '0')
  7      into l_str
  8      from table_a a;
  9
 10    open rc for l_str;
 11    return rc;
 12  end f_test;
 13  /

Function created.

Let's try it:

SQL> select f_test from dual;

F_TEST
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

NAME
--------
table 00

Right; that's contents of table_b_00.

CodePudding user response：

Consider the following meta code:

DECLARE
   n VARCHAR2(32767);
   r VARCHAR2(32767);
BEGIN
   SELECT column_a INTO name FROM table_a;
   EXECUTE IMMEDIATE 'SELECT r FROM table_b_'||n INTO r;
END;
/