提高插入游标的性能？ [英] Increase performance on insert cursor?

问题描述

我想问你如何提高插入光标在这段代码中的性能？
我需要使用动态plsql来获取数据，但不知道如何以最好的方式改进INSERT。像大量插入可能？
如果可能，请让我知道代码示例。

I would like to ask you how would you increase the performance on Insert cursor in this code? I need to use dynamic plsql to fetch data but dont know how to improve the INSERT in best way. like Bulk Insert maybe? Please let me know with code example if possible.

//这是我如何使用cur_handle：

// This is how i use cur_handle:

    cur_HANDLE integer;
cur_HANDLE := dbms_sql.open_cursor;
DBMS_SQL.PARSE(cur_HANDLE, W_STMT, DBMS_SQL.NATIVE);
DBMS_SQL.DESCRIBE_COLUMNS2(cur_HANDLE, W_NO_OF_COLS, W_DESC_TAB);

LOOP
-- Fetch a row   
IF DBMS_SQL.FETCH_ROWS(cur_HANDLE) > 0 THEN
    DBMS_SQL.column_value(cur_HANDLE, 9, cont_ID); 
    DBMS_SQL.COLUMN_VALUE(cur_HANDLE, 3, proj_NR);    
ELSE
    EXIT;
END IF;

  Insert into w_Contracts values(counter, cont_ID, proj_NR);
counter := counter + 1;
END LOOP;

推荐答案

解决方案1） / SQL数组，然后循环插入整个数组在一个步骤使用：

Solution 1) You can populate inside the loop a PL/SQL array and then just after the loop insert the whole array in one step using:

FORALL i in contracts_tab.first .. contracts_tab.last
  INSERT INTO w_contracts VALUES contracts_tab(i);

解决方案2）如果v_stmt包含有效的SQL语句，可以直接使用

Solution 2) if the v_stmt contains a valid SQL statement you can directly insert data into the table using

EXECUTE IMMEDIATE 'INSERT INTO w_contracts (counter, cont_id, proj_nr) 
    SELECT rownum, 9, 3 FROM ('||v_stmt||')';

这篇关于提高插入游标的性能？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！