Oracle 游标中的动态SQL

嗨，我正在尝试创建一个将显示日志的过程。它有一个IN参数，即表nam，光标将根据该参数选择数据。我无法编译我的程序。希望你能帮助我。:)

CREATE OR REPLACE PROCEDURE pr_display_log ( pv_staging_table  VARCHAR2
                                           , pv_temp_table     VARCHAR2
                                           , pv_area           VARCHAR2
                                           , pv_rec_identifier VARCHAR2
                                           , pd_process_start  TIMESTAMP
                                           , pd_process_end    TIMESTAMP)
AS
  ln_total_count NUMBER;
  ln_valid_count NUMBER;
  ln_error_count NUMBER;
  ln_ctr NUMBER := 1;
  lv_rec_identifier VARCHAR2(30) := 'log_rec.'||pv_rec_identifier;

BEGIN
  -- Log Header
  DBMS_OUTPUT.PUT_LINE ('**Starts** '||pv_area||' - '||pd_process_start);
  DBMS_OUTPUT.PUT_LINE (LPAD('-', 150, '-'));
  DBMS_OUTPUT.PUT_LINE(RPAD('CTR', 8)||RPAD(pv_rec_identifier, 35)||RPAD('CONV_STATUS',15)||'CONVERSION REMARKS');
  DBMS_OUTPUT.PUT_LINE (LPAD('-', 150, '-'));

  -- Get Status of the records
  FOR log_rec IN (SELECT * FROM pv_temp_table)
  LOOP
    DBMS_OUTPUT.PUT_LINE(RPAD(ln_ctr, 8)
                         ||RPAD(lv_rec_identifier, 35)
                         ||RPAD(log_rec.conversion_status,15)
                         ||log_rec.error_message);
    ln_ctr := ln_ctr + 1;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE (LPAD('-', 150, '-'));
  DBMS_OUTPUT.PUT_LINE (CHR(10));

  -- Get count summary
  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM :staging_table;'
     INTO ln_total_count
    USING pv_staging_table;

  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM :temp_table WHERE valid_flag = c_yes;'
     INTO ln_valid_count
    USING pv_temp_table;

  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM :temp_table WHERE valid_flag = c_no;'
     INTO ln_error_count
    USING pv_temp_table;

  -- Display the count summary
  DBMS_OUTPUT.PUT_LINE (LPAD('-', 150, '-'));
  DBMS_OUTPUT.PUT_LINE (pv_staging_table||' Summary');
  DBMS_OUTPUT.PUT_LINE (LPAD('-', 150, '-'));
  DBMS_OUTPUT.PUT_LINE (RPAD('Total No. of '||pv_area||' in the staging table :', 55)||ln_total_count);
  DBMS_OUTPUT.PUT_LINE (RPAD('No. of '||pv_area||' successfully validated :', 55)||ln_valid_count);
  DBMS_OUTPUT.PUT_LINE (RPAD('No. of '||pv_area||' with Error :', 55)||ln_error_count);
  DBMS_OUTPUT.PUT_LINE (LPAD('-', 150, '-'));
  DBMS_OUTPUT.PUT_LINE ('**Ends** '||pv_area||' - '||pd_process_end);

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('No records found in '||pv_temp_table||' table.');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error encountered while generating the log file!');

END pr_display_log;

它也不为我编译:

LINE/COL ERROR
-------- -----------------------------------------------------------------
33/5     PL/SQL: SQL Statement ignored
34/12    PL/SQL: ORA-00942: table or view does not exist
36/5     PL/SQL: Statement ignored
38/13    PLS-00364: loop index variable 'LOG_REC' use is invalid

它来自代码的这一部分:

  for log_rec in (
    select *
    from   pv_temp_table
  ) loop

那么，什么是pv_temp_table？数据库中的表？还是程序的参数？还是两者兼而有之？

另外，您有很多尝试绑定表名的语句，如下所示:

  execute immediate 'SELECT COUNT(*) FROM :staging_table;' into
    ln_total_count
    using pv_staging_table;

可悲的是你不能这么做。您必须将表名粘贴到SQL的文本中。确保在这样做的同时防御SQL注入攻击!

您可以使用DBMS_assert来执行此操作。所以动态语句会变成这样:

  execute immediate 'SELECT COUNT(*) FROM ' || 
   dbms_assert.sql_object_name(pv_staging_table)