Динамическая функция — Oracle

Я пытаюсь создать функцию, предоставив 2 переменные в качестве входных данных. Эти 2 переменные используются в функции динамически. Я использую оператор select в начале цикла, чтобы найти столбец первичного ключа в определенной таблице. Этот столбец присваивается переменной value1. Я использую эту переменную value1 в качестве переменной последовательности.

create FUNCTION Test(schemaname in varchar2, tablename in varchar2)
return number
IS cnpParmId NUMBER;
good VARCHAR(1) := 'F';
exist VARCHAR(1) := 'F';
value1 varchar2(500);
  
begin
good  := 'F';
exist := 'F';
loop
   SELECT cols.column_name into value1
        FROM all_constraints cons, all_cons_columns cols
        WHERE cols.TABLE_NAME= 'tablename' 
        And cols.OWNER='schemaname'
        And cons.constraint_type = 'P'
        AND cons.constraint_name = cols.constraint_name
        AND cons.owner = cols.owner
        ORDER BY cols.table_name, cols.position; 
  
   select schemaname.value1_seq.nextval into cnpParmId from dual;
   begin
      select 'T' into good from dual 
      where cnpParmId not in 
      (select value1 from schemaname.tablename);
      exception when NO_DATA_FOUND then good := 'F';
   end;
   exit when good = 'T';
end loop;
return cnpParmId;
end;
/

Test(XYZ,ABC);

но получаю следующие ошибки:

Ошибка (21,11): PLS-00487: неверная ссылка на переменную «ИМЯ СХЕМЫ»

Ошибка (21,22): PL/SQL: ORA-02289: последовательность не существует

Ошибка (23,7): PL/SQL: оператор SQL игнорируется

Ошибка (25,38): PL/SQL: ORA-00942: таблица или представление не существует


oracle stored-procedures plsql plsqldeveloper dynamicquery
person bin    schedule 10.06.2016    source источник

Ответы (1)


arrow_upward
1
arrow_downward

Когда вы вводите переменные в виде имен таблиц или столбцов, вам необходимо выполнить динамический запрос, используя EXECUTE IMMEDIATE.

Ваш запрос последовательности должен быть:

execute immediate 'select ' || schemaname || '.nextval from dual' into cnpParmId;

И ваш запрос «получить возвращаемое значение» должен быть:

execute immediate
  'select ''T'' from dual where cnpParmId not in ' ||
  '(select value1 from ' || schemaname || '.' || tablename || ')' into good;

Также обратите внимание, что ваш запрос для получения value1 ищет литеральные значения schemaname и tablename:

SELECT cols.column_name into value1
    FROM all_constraints cons, all_cons_columns cols
    WHERE cols.TABLE_NAME= 'tablename'
    And cols.OWNER='schemaname'
    ... and so on

Вы можете использовать переменные для представления значений, поэтому просто избавьтесь от одинарных кавычек вокруг имен переменных:

SELECT cols.column_name into value1
    FROM all_constraints cons, all_cons_columns cols
    WHERE cols.TABLE_NAME= tablename
    And cols.OWNER=schemaname
    ... and so on
person Ed Gibbs    schedule 10.06.2016