Oracle 10g using FORALL to deal with non-consecutive array

In PL / SQL implementation of cycle data manipulation language (DML) is a time-consuming, because each cycle should be from PL / SQL engine SQL engine turning. FORALL is a better application of the method, which a one-time to a group of SQL statements submitted to the provisional value.

Oracle 10g in the former, FORALL statement can only deal with the continuity of the syntax of the array elements:

FORALL index_name IN lower_bound .. upper_bound sql_statement;


This means that: in the past, the use of the FORALL nested table can not be processed to remove the middle element of the array, and an array of processing must be carried out continuously. Oracle 10g solve these two problems, and to increase the INDICES OF and VALUES OF clauses.

INDICES OF clause to replace lower_bound .. upper_bound, all have to deal with the effective index values, even if the interval between these values. Like this:
FORALL index_name IN INDICES OF collection_name
BETWEEN
lower_bound AND upper_bound
sql_statement;


You can still BETWEEN syntax can be applied to deal with the scope of restrictions, this is an optional content.

VALUES OF clause to help you deal with a different sequence of the main collection. You then create another set, which contains only the index you want to deal with numbers, these numbers you wish to order. The statement then becomes:
FORALL index_name IN VALUES OF index_collection
sql_statement;


A list of applications is a model example of HR samples. I will load into memory the name of the department in a nested table, and then search inside the relevant departments and IT. Each to a search, I save it the index table entry. VALUES OF clauses combined index for this group of table INSERT statement for each department to deal with. (This is just an example; with a single SQL statement can also be treated in the same.) Lists B is output.
  • del.icio.us
  • StumbleUpon
  • Digg
  • TwitThis
  • Mixx
  • Technorati
  • Facebook
  • NewsVine
  • Reddit
  • Google
  • LinkedIn
  • YahooMyWeb

Related Posts of Oracle 10g using FORALL to deal with non-consecutive array

  • Introduction and analysis instrument Tkprof

    Tkprof is an Oracle trace file for analysis and have a more clear and reasonable instrument output executable. If a system is relatively low efficiency, a better method is by tracking the user's session and use tools to sort Tkprof formatted outp ...

  • Oracle9i database manually create specific steps

    Oracle9i database manually create specific steps Article from the Internet, thanks to the author! At the operating system on linux, oracle user name to log in 1, and stop running linux on the database instance 2, change the ORACLE_SID environment par ...

  • SQLPlus skills

    SQLPlus skills 1. The use of dynamically generated SQL * PLUS script volume Spool command will be combined with the use of select, you can generate a script, the script may have included the implementation of a bulk of the mission statement. Example ...

  • RMAN created physical standby

    Monitor in the standby host to create the necessary directory structure and parameter files. Hot COPY after the main library to the standby, and on standby to restore. +++++++++++++++++++++++++++++ Standby ++++++++++++++++++++++++++++++++++++++++++++ ...

  • ProC will select the data on the two-dimensional array

    Examples are as follows: (Over)

  • SQL optimization (5) - commonly used test hints

    First, the purpose of testing SunnyXu in accordance with the "SQL Performance adjust - summarized," commonly used to test the following four categories of hints: 1. The method of instruction optimizer hints with the aim ALL_ROWS: Based on t ...

  • Oracle locking mechanism (reprint)

    Blocking mechanism is set up in order to control the concurrent operation of the block interference, to ensure data consistency and accuracy. Oracle database blockade There are three ways: sharing blockade blockade exclusive, shared update blockade [ ...

  • Turning optimization oracle article

    DB_BLOCK_LRU_LATCHES = 6 BUFFER_POOL_KEEP = (BUFFERS: 14000, LRU_LATCHES: 1) BUFFER_POOL_RECYCLE = (BUFFERS: 2000, LRU_LATCHES: 3) ... Note: buffer_pool_buffers = 2000 * 3 +14000 * 1 = 20000 keep the district will once again be used to retain the use ...

  • An example of the use of TKPROF

    First, view and edit parameters SQL> show parameter max_dump_file_size NAME TYPE VALUE ------------------------------------ ----------- --- --------------------------- max_dump_file_size string UNLIMITED SQL> show parameter user_dump_dest NAME TYPE

Leave a Reply

Recent
Recent Entries
Tag Cloud
Random Entries
Latest Comments