Блог

Предположим, я запускаю запрос:

 select MyProc(id) from tableName;
  

Есть ли способ, которым я могу получить доступ к имени таблицы, используемому в предложении from в приведенном выше запросе, из процедуры: MyProc ()?

Тогда я смог бы динамически использовать ‘from table’ в процедуре MyProc().’

Спасибо.

Нет, вы не можете этого сделать. Вы могли бы отправить таблицу в качестве параметра процедуры и использовать динамический SQL внутри вашей процедуры:

 SELECT MyProc( id, 'tablename' ) FROM dual;
  

Но даже это неудобно и имеет все ограничения динамического SQL. Зачем вашей процедуре нужно имя таблицы? Что вы пытаетесь сделать?

Вам придется использовать динамически созданный запрос, чтобы сделать то, что вы пытаетесь. Попробуйте что-то вроде

 strSQL  VARCHAR2(32767);
csr     SYS_REFCURSOR;
nVal1    NUMBER;
nVal2    NUMBER;
strVal3  VARCHAR2(2000);

strSQL := 'SELECT val1, val2, val3 FROM ' || tableName || ' WHERE whatever = somethingelse';

OPEN csr FOR strSQL;
FETCH csr INTO nVal1, nVal2, strVal3;
CLOSE csr;
  

Делитесь и наслаждайтесь.

 create or replace function myProc(p_id number) return varchar2 is
    v_sql_id varchar2(13);
    v_table_name varchar2(100);
begin
    --Get the SQL used to call this function
    select sql_id into v_sql_id
    from v$sql
    where lower(sql_text) like 'select myproc(id)%'
        and users_executing > 0;

    --Get the table name
    select object_name into v_table_name
    from v$sql_plan
    where sql_id = v_sql_id
        and operation = 'TABLE ACCESS';

    --For testing, return the table name.
    return v_table_name;
end;
/

create table test1(id number);
create table test2(id number);
insert into test1 values(1);
insert into test2 values(2);
commit;

--Returns TEST1 (careful, your IDE may add this comment to the SQL!)
select MyProc(id) from test1;

--Returns TEST2
select MyProc(id) from test2;
  

Идея здесь состоит в том, чтобы найти выполняемый в данный момент SQL, а затем найти таблицу, используемую этим SQL. Но есть много потенциальных проблем.

Получение SQL_ID

Существует множество способов найти SQL_ID, но ни один из них не работает хорошо. Вот подходы, которые я попробовал, может быть, кто-нибудь сможет придумать, как заставить один из них работать лучше.

Например, в v $ session SQL_ID будет ссылаться на самого себя, а PREV_SQL_ID ссылается на какой-то бесполезный запрос транзакции (по крайней мере, в моей системе).

 select sql_id, prev_sql_id from v$session where sid = sys_context('USERENV', 'SID');
  

Поиск запроса в v $ sql и упорядочивание LAST_LOAD_TIME не всегда работает, LAST_LOAD_TIME не всегда обновляется.

 select sql_id from v$sql
where lower(sql_text) like 'select myproc(id)%'
order by last_load_time desc;
  

Использование SQL_TEXT и USERS_EXECUTING > 0 будет работать, но только в том случае, если одновременно этот запрос выполняется только одним сеансом. И поиск по тексту, подобному этому, очень опасен. Некоторые среды могут помещать текст перед select, например пробелы или комментарии. Но вы не можете выполнить поиск по ‘%select …’, потому что тогда запрос вернул бы сам себя.

 select sql_id into v_sql_id
from v$sql
where lower(sql_text) like 'select myproc(id)%'
    and users_executing > 0;
  

Поиск таблицы

С помощью SQL_ID мы можем легко получить текст запроса из v $ sql.sql_text или v $ sql.sql_fulltext. У вас может быть возможность проанализировать этот запрос, но в целом я бы рекомендовал вам избегать синтаксического анализа SQL. Это намного сложнее, чем думает большинство людей. Если вы абсолютно уверены, что будет использоваться только конкретный, простой запрос, то, возможно, этот подход сработает.

Вероятно, более реалистичным подходом является использование v $ sql_plan для поиска используемых таблиц. Это сработает для вашего запроса, но вам придется проделать больше работы, если в вашем запросе может быть несколько таблиц, или если есть представления или индексы (вам придется присоединиться к user_index, чтобы найти фактическую таблицу) и т.д.

 select object_name
from v$sql_plan
where sql_id = <SQL_ID>
    and operation = 'TABLE ACCESS'
  

Вероятно, вам потребуется предоставить пользователю select в v_$ sql и v_$sql_plan. О, и это будет действительно медленно. идея @Eaolson о передаче имени таблицы в качестве параметра намного лучше, если это работает.