Estoy tratando de ejecutar un procedimiento en el que envío el nombre de la tabla y los nombres de 2 columnas como parámetros:

EXECUTE IMMEDIATE 'select avg(@column1) from @Table1 where REF_D = @column2' into ATTR_AVG;

He intentado usar las variables en combinaciones de '@', ':', '||' pero nada parece funcionar.

¿Alguien ha usado nombres de tablas como parámetro? Hay algunas soluciones aquí, pero para SQL Server

0
thealchemist 4 abr. 2017 a las 18:38

2 respuestas

La mejor respuesta

Solo puede usar variables de enlace (indicadas por dos puntos) para los valores, no para partes de la estructura. Tendrá que concatenar los nombres de tabla y columna en la consulta:

EXECUTE IMMEDIATE 'select avg(' || column1 | ') from ' || Table1 
  || ' where REF_D = ' || column2 into ATTR_AVG;

Lo que implica que REF_D es un nombre de columna fijo que puede aparecer en cualquier tabla para la que llame; en una pregunta anterior que parece ser una variable. Si en realidad es una variable de cadena, entonces deberías vincular y configurar eso:

EXECUTE IMMEDIATE 'select avg(' || column1 | ') from ' || Table1 
  || ' where ' || column2 || ' = :REF_D' into ATTR_AVG using REF_D;

Si se supone que es una fecha, debe asegurarse de que la variable local sea del tipo correcto o convertirla explícitamente.

2
Alex Poole 4 abr. 2017 a las 15:53

Debe construir la instrucción ejecutable usando || (o definirla como una cadena que contiene marcadores de posición que luego puede manipular con replace). Algo como:

create or replace procedure demo
    ( p_table   user_tab_columns.table_name%type
    , p_column1 user_tab_columns.column_name%type
    , p_column2 user_tab_columns.column_name%type )
is
    attr_avg number;
begin
    execute immediate
        'select avg(' || p_column1 || ') from ' || p_table ||
        ' where ref_d = ' || p_column2
    into attr_avg;

    dbms_output.put_line('Result: ' || attr_avg);
end demo;

Generalmente es una buena idea construir primero la cadena en una variable amigable para el depurador, es decir, algo como:

create or replace procedure demo
    ( p_table   user_tab_columns.table_name%type
    , p_column1 user_tab_columns.column_name%type
    , p_column2 user_tab_columns.column_name%type )
is
    attr_avg number;
    sql_statement varchar2(100);
begin
    sql_statement :=
        'select avg(' || p_column1 || ') from ' || p_table ||
        ' where ref_d = ' || p_column2;

    execute immediate sql_statement into attr_avg;

    dbms_output.put_line('Result: ' || attr_avg);
end demo;

Dependiendo de lo que sea ref_d, es posible que tenga que tener cuidado con lo que compara, por lo que lo anterior podría requerir un poco más de trabajo, pero con suerte le dará la idea.

Editar: sin embargo, vea la respuesta de Alex Poole para una nota sobre el uso de variables de enlace. Si ref_d es una variable que puede necesitar convertirse en:

    sql_statement :=
        'select avg(' || p_column1 || ') from ' || p_table ||
        ' where ' || p_column2 || ' = :b1';

    execute immediate sql_statement into attr_avg using ref_d;

(La convención es colocar la expresión de búsqueda a la derecha, por ejemplo, where name = 'SMITH' en lugar de where 'SMITH' = name, aunque son lo mismo para SQL).

2
William Robertson 4 abr. 2017 a las 16:27