Oracle PL/SQL Tutorial/PL SQL Statements/forall

Содержание

1 Forall in indices of
2 The FORALL command builds a set of SQL statements and executes all of them at once.
3 Use for all to insert value to table collection of rowtype
4 Use forall to loop through first element to last element in a table collection of number
5 Use VALUES OF
6 Use VALUES OF with undefined row

Forall in indices of

   <source lang="sql">

SQL> SQL> CREATE TABLE employee (

 2     employee_id NUMBER,
 3     last_name VARCHAR2(30),
 4     first_name VARCHAR2(30),
 5     salary NUMBER
 6  );

Table created. SQL> INSERT INTO employee (employee_id, last_name, first_name, salary)VALUES (1, "G", "J", 100000); 1 row created. SQL> SQL> INSERT INTO employee (employee_id, last_name, first_name, salary)VALUES (2, "G", "H", 100000); 1 row created. SQL> SQL> SELECT employee_id FROM employee WHERE salary = 10000; no rows selected SQL> SQL> DECLARE

 2     TYPE employee_aat IS TABLE OF employee.employee_id%TYPE INDEX BY PLS_INTEGER;
 3
 4     l_employees employee_aat;
 5
 6     TYPE boolean_aat IS TABLE OF BOOLEAN
 7        INDEX BY PLS_INTEGER;
 8
 9     l_employee_indices   boolean_aat;
10  BEGIN
11     l_employees (1) := 7839;
12     l_employees (100) := 7654;
13     l_employees (500) := 7950;
14     --
15     l_employee_indices (1) := TRUE;
16     l_employee_indices (500) := TRUE;
17     l_employee_indices (799) := TRUE;
18     --
19     FORALL l_index IN INDICES OF l_employee_indices
20        BETWEEN 1 AND 500
21        UPDATE employee
22           SET salary = 10000
23         WHERE employee_id = l_employees (l_index);
24  END;
25  /

PL/SQL procedure successfully completed. SQL> SQL> SELECT employee_id FROM employee WHERE salary = 10000; no rows selected SQL> SQL> SQL> drop table employee; Table dropped. SQL></source>

The FORALL command builds a set of SQL statements and executes all of them at once.

   <source lang="sql">

SQL> SQL> -- create demo table SQL> create table Employee(

 2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
 3    First_Name         VARCHAR2(10 BYTE),
 4    Last_Name          VARCHAR2(10 BYTE),
 5    Start_Date         DATE,
 6    End_Date           DATE,
 7    Salary             Number(8,2),
 8    City               VARCHAR2(10 BYTE),
 9    Description        VARCHAR2(15 BYTE)
10  )
11  /

Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2               values ("01","Jason",    "Martin",  to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto",  "Programmer")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("02","Alison",   "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("03","James",    "Smith",   to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("04","Celia",    "Rice",    to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("05","Robert",   "Black",   to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("06","Linda",    "Green",   to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York",  "Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("07","David",    "Larry",   to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York",  "Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("08","James",    "Cat",     to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
 3  /

1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee

 2  /

ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION

-------------------- -------------------- --------- --------- ---------- ---------- ---------------

01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> declare

 2      type number_nt is table of VARCHAR(20);
 3      v_deptNo_nt number_nt:=number_nt("01","02");
 4  begin
 5      forall i in v_deptNo_nt.first()..v_deptNo_nt.last()
 6        update employee set salary = 0 where id =v_deptNo_nt(i);
 7  end;
 8  /

PL/SQL procedure successfully completed. SQL> SQL> SQL> select * from employee; ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION

-------------------- -------------------- --------- --------- ---------- ---------- ---------------

01 Jason Martin 25-JUL-96 25-JUL-06 0 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 0 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> -- clean the table SQL> drop table Employee

 2  /

Table dropped. SQL> SQL></source>

Use for all to insert value to table collection of rowtype

   <source lang="sql">

SQL> CREATE TABLE favorites (

 2     flavor VARCHAR2(100),
 3     NAME VARCHAR2(100));

Table created. SQL> SQL> DECLARE

 2     TYPE favorites_tt IS TABLE OF favorites%ROWTYPE INDEX BY PLS_INTEGER;
 3     TYPE guide_tt IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;
 4
 5     family   favorites_tt;
 6     guide    guide_tt;
 7     l_count INTEGER;
 8
 9     PROCEDURE cleanup IS
10     BEGIN
11        DELETE FROM favorites;
12        guide.DELETE;
13     END;
14  BEGIN
15     family (1).flavor := "CHOCOLATE";
16     family (1).NAME := "VEVA";
17     family (25).flavor := "STRAWBERRY";
18     family (25).NAME := "STEVEN";
19     family (500).flavor := "VANILLA";
20     family (500).NAME := "CHRIS";
21     family (5000).flavor := "ROCKY ROAD";
22     family (5000).NAME := "ELI";
23     family (5001).flavor := "PINEAPPLE";
24     family (5001).NAME := "MOSHE";
25     family (5002).flavor := "EVERYTHING";
26     family (5002).NAME := "MICA";
27
28     FORALL indx IN indices of guide
29        INSERT INTO favorites VALUES family (indx);
30
31     SELECT COUNT(*) into l_count FROM favorites;
32     DBMS_OUTPUT.PUT_LINE (l_count);
33
34  END;
35  /

PL/SQL procedure successfully completed. SQL> SQL> DROP TABLE favorites; Table dropped. SQL></source>

Use forall to loop through first element to last element in a table collection of number

   <source lang="sql">

SQL> SQL> create table employee

 2          (
 3           empl_no                integer         primary key
 4          ,lastname               varchar2(20)    not null
 5          ,firstname              varchar2(15)    not null
 6          ,midinit                varchar2(1)
 7          ,street                 varchar2(30)
 8          ,city                   varchar2(20)
 9          ,state                  varchar2(2)
10          ,zip                    varchar2(5)
11          ,zip_4                  varchar2(4)
12          ,area_code              varchar2(3)
13          ,phone                  varchar2(8)
14          ,company_name           varchar2(50));

Table created. SQL> SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)

 2  values(1,"Jones","Joe","J","10 Ave","New York","NY","11111","1111","111", "111-1111","A Company");