在Oracle中执行立即选择语句 [英] Execute immediate select statement in Oracle

问题描述

我在其中一个查询中使用了execute立即执行语句.

I am using execute immediate statement in one of my queries.

    procedure p1 (p_pk1_column, p_pk2_column , p_conv_table_name ,p_MODUE_NAME )
    is
    v_select_string   := 'SELECT'''||p_MODUE_NAME||''',''' ||p_pk1_column || ''',''' || p_pk2_column ||''' FROM ' ||p_conv_table_name || v_where_condition;
    execute immediate v_select_string  ;
    dbms_output.put_line('string:'||v_select_string  );
    end p1;

我在另一个过程p2中调用p1过程

Here I am calling p1 procedure in another procedure p2

PROCEDURE P2 IS
v_pk1_column:='a';
v_pk2_columnm:='b';
v_mod_name:='mOD1';
p1(v_pk1_column,v_pk2_columnm);
end p2;
/

在p2过程a中，b是p_conv_table_name的列名.我想从条件p_conv_table_name中执行select语句，例如select p_mod_name，a，b.以便它应为p_conv_table_name中的a和b列提供值.

In p2 procedure a, b are the column names of p_conv_table_name . I want to execute the select statement like select p_mod_name, a, b from p_conv_table_name where condition; so that it should give values for a and b columns in p_conv_table_name .

但是它的执行就像从p_conv_table_name中选择select p_mod，p_pk1_col，p_pk2_col一样；
因此，只需选择列名即可代替该列中的值.

But it is executing like select p_mod, p_pk1_col,p_pk2_col from p_conv_table_name where condition;
So simply column names are selecting instead of values in that column.

请在此列中提出一些实现价值的方法.

Please suggest some approach to achieve values in that column.

预先感谢

推荐答案

在构建SELECT语句时，列名被单引号引起来，从而将其转换为字符串文字.将您的过程更改为:

When the SELECT statement is built the column names are surrounded in single-quotes, which turns them into string literals. Change your procedure to something like:

CREATE OR REPLACE PROCEDURE P1 (p_pk1_column      IN VARCHAR2,
                                p_pk2_column      IN VARCHAR2,
                                p_conv_table_name IN VARCHAR2,
                                p_MODUE_NAME      IN VARCHAR2)
IS
  v_select_string    VARCHAR2(2000);
  v_where_condition  VARCHAR2(2000) := ' WHERE SOMETHING = SOMETHING_ELSE';
  csr                SYS_REFCURSOR;
  v_val_1            VARCHAR2(2000);
  v_val_2            VARCHAR2(2000);
  v_mod_name         VARCHAR2(2000);
BEGIN
  v_select_string   := 'SELECT ' || p_MODUE_NAME || ',' ||
                                    p_pk1_column || ',' ||
                                    p_pk2_column ||
                       ' FROM ' || p_conv_table_name ||
                       v_where_condition;

  dbms_output.put_line('string:' || v_select_string);

  OPEN csr FOR v_select_string;

  LOOP
    FETCH csr INTO v_mod_name, v_val_1, v_val_2;

    EXIT WHEN csr%NOTFOUND;         

    DBMS_OUTPUT.PUT_LINE('v_mod_name=''' || v_mod_name || '''  ' ||
                         'v_val_1=''' || v_val_1 || '''  ' ||
                         'v_val_2=''' || v_val_2 || '''');
  END LOOP;

  CLOSE csr;
END P1;

我也将代码更改为游标OPEN和FETCH而不是使用EXECUTE IMMEDIATE. OPEN和FETCH通常更适合与动态SELECT语句一起使用.

I've also changed the code to OPEN and FETCH a cursor rather than using EXECUTE IMMEDIATE. OPEN and FETCH are generally more appropriate for use with a dynamic SELECT statement.

分享并享受.

这篇关于在Oracle中执行立即选择语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！