Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 Oracle PL/SQL: FORALL Insert Jump to:  
Category: >> Oracle PL/SQL >> FORALL Insert Bookmark and Share

<< lastnext >>

Snippet Name: FORALL Insert

Description: FORALL transfers data from a PL/SQL collection to the specified table using collections.

Also see:
» INSERT With Returning Clause
» Create Table INSERT (CTAS)
» Inserting into SELECT statement
» INSERT WITH CHECK OPTION
» INSERT FIRST WHEN
» INSERT ALL WHEN
» INSERT ALL
» INSERT WHEN
» INSERT: Using A Record
» INSERT with Select
» INSERT: Multiple Column Table Or View ...
» INSERT: Multiple Column Table Or View ...
» INSERT: Single Column Table Or View
» INSERT

Comment: (none)

Language: PL/SQL
Highlight Mode: PLSQL
Last Modified: March 02nd, 2009

CREATE TABLE servers2 AS
SELECT *
FROM servers
WHERE 1=2;
 
DECLARE
 CURSOR s_cur IS
 SELECT *
 FROM servers;
 
 TYPE fetch_array IS TABLE OF s_cur%ROWTYPE;
 s_array fetch_array;
BEGIN
  OPEN s_cur;
  LOOP
    FETCH s_cur BULK COLLECT INTO s_array LIMIT 1000;
 
    FORALL i IN 1..s_array.COUNT
    INSERT INTO servers2 VALUES s_array(i);
 
    EXIT WHEN s_cur%NOTFOUND;
  END LOOP;
  CLOSE s_cur;
  COMMIT;
END;
/
 
 
--FORALL Update      
 
SELECT DISTINCT srvr_id
FROM servers2
ORDER BY 1;
 
DECLARE
 TYPE myarray IS TABLE OF servers2.srvr_id%TYPE
 INDEX BY BINARY_INTEGER;
 
 d_array myarray;
BEGIN
  d_array(1) := 608;
  d_array(2) := 610;
  d_array(3) := 612;
 
  FORALL i IN d_array.FIRST .. d_array.LAST
  UPDATE servers2
  SET srvr_id = 0
  WHERE srvr_id = d_array(i);
 
  COMMIT;
END;
/
 
SELECT srvr_id
FROM servers2
WHERE srvr_id = 0;
 
 
--FORALL Delete       
 
SET serveroutput ON
 
DECLARE
 TYPE myarray IS TABLE OF servers2.srvr_id%TYPE
 INDEX BY BINARY_INTEGER;
 
 d_array myarray;
BEGIN
  d_array(1) := 614;
  d_array(2) := 615;
  d_array(3) := 616;
 
  FORALL i IN d_array.FIRST .. d_array.LAST
  DELETE servers2
  WHERE srvr_id = d_array(i);
 
  COMMIT;
 
  FOR i IN d_array.FIRST .. d_array.LAST LOOP
    DBMS_OUTPUT.put_line('Iteration #' || i || ' deleted ' ||
    SQL%BULK_ROWCOUNT(i) || ' rows.');
  END LOOP;
END;
/
 
SELECT srvr_id
FROM servers2
WHERE srvr_id IN (614, 615, 616);


Free
Oracle Magazine
Subscriptions
and Oracle White Papers


SQL University.net courses meet the most demanding needs of the business world for advanced education in a cost-effective manner. SQL University.net courses are available immediately for IT professionals and can be taken without disruption of your workplace schedule or processes.

Compared to traditional travel-based training, SQL University.net saves time and valuable corporate resources, allowing companies to do more with less. That's our mission, and that's what we deliver.

Click here to find out more
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 203 users online    © 2009 psoug.org

PSOUG LOGIN
Username: 
Password: 
Forgot your password?