为此，您需要动态SQL。
表格：

SQL> create table table_a (id, c_descr, c_sql) as
  2    (select 1, 'EMP' , 'select count(*) from emp'  from dual union all
  3     select 2, 'DEPT', 'select count(*) from dept' from dual);

Table created.

SQL> create table table_b (c_descr varchar2(20), result number);

Table created.

程序：

SQL> declare
  2    l_str table_a.c_sql%type;
  3    l_cnt number;
  4  begin
  5    for cur_r in (select c_descr, c_sql from table_a) loop
  6      execute immediate cur_r.c_sql into l_cnt;
  7      insert into table_b (c_descr, result) values (cur_r.c_descr, l_cnt);
  8    end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed.

结果：

SQL> select * from table_b;

C_DESCR                  RESULT
-------------------- ----------
EMP                          14
DEPT                          4

SQL>

如果select语句真的包含group by子句，那么result就不仅仅是一个值，而是它们的集合。例如：

SQL> select count(*) from emp group by deptno;

  COUNT(*)
----------
         5
         6
         3

SQL>

在这种情况下，它仍然是动态SQL，但into子句的时间目标不是标量变量，而是集合：

SQL> create table table_a (id, c_descr, c_sql) as
  2    select 3, 'EMP', 'select count(*) from emp group by deptno' from dual;

Table created.

SQL> create table table_b (c_descr varchar2(20), result number);

Table created.

程序：

SQL> declare
  2    l_str table_a.c_sql%type;
  3    l_coll sys.odcinumberlist;
  4  begin
  5    for cur_r in (select c_descr, c_sql from table_a) loop
  6      execute immediate cur_r.c_sql bulk collect into l_coll;
  7      insert into table_b (c_descr, result)
  8        select cur_r.c_descr, column_value
  9          from table(l_coll);
 10    end loop;
 11  end;
 12  /

PL/SQL procedure successfully completed.

结果：

SQL> select * from table_b;

C_DESCR                  RESULT
-------------------- ----------
EMP                           5
EMP                           6
EMP                           3

SQL>

[编辑，基于您的评论]

当然，当查询返回多个行时，您可以“连接”结果（并且您不希望向table_B插入多行，这是我前面的示例所做的）。在这种情况下，一个选择是使用listagg函数。此外，table_b的result列不能再是number。

SQL> select * from table_a;

        ID C_D C_SQL
---------- --- ----------------------------------------
         3 EMP select count(*) from emp group by deptno

SQL> alter table table_b modify result varchar2(20);

Table altered.

SQL> declare
  2    l_str table_a.c_sql%type;
  3    l_coll sys.odcinumberlist;
  4  begin
  5    for cur_r in (select c_descr, c_sql from table_a) loop
  6      execute immediate cur_r.c_sql bulk collect into l_coll;
  7      insert into table_b (c_descr, result)
  8        select cur_r.c_descr, listagg(column_value, ', ') within group (order by null)
  9          from table(l_coll);
 10    end loop;
 11  end;
 12  /

PL/SQL procedure successfully completed.

SQL> select * from table_b;

C_DESCR              RESULT
-------------------- --------------------
EMP                  5, 6, 3

SQL>

[另一个编辑，基于新评论]

如果你想在查询中包含额外的列，最简单的选择是在select语句中预先连接它们。如下所示：

SQL> select * from table_a;

        ID C_D C_SQL
---------- --- -------------------------------------------------------
         3 EMP select count(*) ||'-'|| deptno from emp group by deptno

l_coll现在是sys.odcivarchar2list（以前是sys.odcinumberlist，因为它只包含数字）：

SQL> declare
  2    l_coll sys.odcivarchar2list;
  3  begin
  4    for cur_r in (select c_descr, c_sql from table_a) loop
  5      execute immediate cur_r.c_sql bulk collect into l_coll;
  6      insert into table_b (c_descr, result)
  7        select cur_r.c_descr, listagg(column_value, ', ') within group (order by null)
  8          from table(l_coll);
  9    end loop;
 10  end;
 11  /

PL/SQL procedure successfully completed.

结果：

SQL> select * from table_b;

C_DESCR              RESULT
-------------------- ----------------------------------------
EMP                  3-10, 5-20, 6-30

SQL>