在PL/SQL过程中打开动态表名称的游标 [英] Open cursor for dynamic table name in PL/SQL procedure

问题描述

我想创建将使用游标的过程，这对于任意表都是相同的.我目前的样子是这样的:

I want to create procedure, that will use cursor, which is the same for arbitrary tables. My current one looks like this:

create or replace
  PROCEDURE
    some_name(
      p_talbe_name IN VARCHAR2,
      p_chunk_size IN NUMBER,
      p_row_limit IN NUMBER
    ) AS

  CURSOR v_cur IS
     SELECT common_column,
       ora_hash(substr(common_column, 1, 15), p_chunk_size) as chunk_number
     -- Here it can find the table!
     FROM p_table_name;

  TYPE t_sample IS TALBE OF v_cur%rowtype;
  v_sample t_sample;
BEGIN
  OPEN v_cur;
  LOOP FETCH v_cur BULK COLLECT INTO v_sample LIMIT p_row_limit;
    FORALL i IN v_sample.first .. v_sample.last
    INSERT INTO chunks VALUES v_sample(i);
    COMMIT;
    EXIT WHEN v_cur%notfound;
  END LOOP;
  CLOSE v_cur;
END;

问题在于它找不到我要参数化的名为p_table_name的表.事实是，我需要基于存在于所有预期表中的common_column的哈希值创建块.该如何处理呢?也许有等效的oracle代码可以完成相同的工作?然后，我需要相同的查询效率.谢谢！

The problem is that it cannot find the table named p_table_name which I want to parametrize. The thing is that I need to create chunks based on hashes for common_column which exists in all intended tables. How to deal with that problem? Maybe there is the equivalent oracle code that will do the same thing? Then I need the same efficiency for the query. Thanks!

推荐答案

我将其作为单个insert-as-select语句来完成，仅由于您要传递table_name的事实而使它变得复杂，因此我们需要使用动态sql.

I would do this as a single insert-as-select statement, complicated only by the fact you're passing in the table_name, so we need to use dynamic sql.

我会这样做:

CREATE OR REPLACE PROCEDURE some_name(p_table_name IN VARCHAR2,
                                      p_chunk_size IN NUMBER,
                                      p_row_limit  IN NUMBER) AS

  v_table_name VARCHAR2(32); -- 30 characters for the tablename, 2 for doublequotes in case of case sensitive names, e.g. "table_name"

  v_insert_sql CLOB;
BEGIN
  -- Sanitise the passed in table_name, to ensure it meets the rules for being an identifier name. This is to avoid SQL injection in the dynamic SQL
  -- statement we'll be using later.
  v_table_name := DBMS_ASSERT.ENQUOTE_LITERAL(p_table_name);

  v_insert_sql := 'insert into chunks (common_column_name, chunk_number)'||CHR(10)|| -- replace the column names with the actual names of your chunks table columns.
                  'select common_column,'||CHR(10)||
                  '       ora_hash(substr(common_column, 1, 15), :p_chunk_size) AS chunk_number'||CHR(10)||
                  'from   '||v_table_name||CHR(10)||
                  'where  rownum <= :p_row_limit';

  -- Used for debug purposes, so you can see the definition of the statement that's going to be run.
  -- Remove before putting the code in production / convert to proper logging code:
  dbms_output.put_line(v_insert_sql);

  -- Now run the statement:
  EXECUTE IMMEDIATE v_insert_sql USING p_chunk_size, p_row_limit;

  -- I've included the p_row_limit in the above statement, since I'm not sure if your original code loops through all the rows once it processes the
  -- first p_row_limit rows. If you need to insert all rows from the p_table_name into the chunks table, remove the predicate from the insert sql and the extra bind variable passed into the execute immediate.
END some_name;
/

通过使用单个insert-as-select语句，您正在使用最有效的方法来完成工作.进行批量收集(正在使用)会消耗内存(将数组中的数据存储)，并导致在PL/SQL和SQL引擎之间进行额外的上下文切换，而insert-as-select语句避免了这种情况.

By using a single insert-as-select statement, you are using the most efficient way of doing the work. Doing the bulk collect (which you were using) would use up memory (storing the data in the array) and cause extra context switches between the PL/SQL and SQL engines that the insert-as-select statement avoids.

这篇关于在PL/SQL过程中打开动态表名称的游标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！