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
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>
The FORALL command builds a set of SQL statements and executes all of them at once.
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>
Use for all to insert value to table collection of rowtype
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>
Use forall to loop through first element to last element in a table collection of number
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");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(2,"Smith","Sue","J","20 Ave","New York","NY","22222","2222","222", "222-111","B Company");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(3,"Anderson","Peggy","J","500 St","New York","NY","33333","3333","333", "333-3333","C Company");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(4,"Andy","Jill", null,"930 St","New York","NY","44444","4444","212", "634-7733","D Company");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(5,"OK","Carl","L","19 Drive","New York","NY","55555","3234","212", "243-4243","E Company");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(6,"Peter","Jee","Q","38 Ave","New York","NY","66666","4598","212", "454-5443","F Inc");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(7,"Baker","Paul","V","738 St.","Queens","NY","77777","3842","718", "664-4333","G Inc");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(8,"Young","Steve","J","388 Ave","New York","NY","88888","3468","212", "456-4566","H Associates Inc");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(9,"Mona","Joe","T","9300 Ave","Kansas City","MO","99999","3658","415", "456-4563","J Inc");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(10,"Hackett","Karen","S","Kings Rd. Apt 833","Bellmore","NY","61202","3898","516", "767-5677","AA Inc");
1 row created.
SQL>
SQL> insert into employee(empl_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(11,"Bob","Jack","S","12 Giant Rd.","Newark","NJ","27377","3298","908", "123-7367","Z Associates");
1 row created.
SQL>
SQL>
SQL>
SQL> DECLARE
2 TYPE CustList IS TABLE OF NUMBER;
3 custs CustList := CustList(3, 9);
4 BEGIN
5 FORALL i IN custs.FIRST..custs.LAST
6 DELETE FROM employee WHERE empl_no = custs(i);
7
8 END;
9 /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table employee;
Table dropped.
SQL>
SQL>
Use VALUES OF
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 guide (-1000) := 1;
29 guide (1000) := 5001;
30 guide (10000) := 5002;
31 FORALL indx IN VALUES OF guide INSERT INTO favorites VALUES family (indx);
32
33 SELECT COUNT(*) into l_count FROM favorites;
34 DBMS_OUTPUT.PUT_LINE (l_count);
35
36 END;
37 /
PL/SQL procedure successfully completed.
SQL>
SQL> DROP TABLE favorites;
Table dropped.
SQL>
SQL>
Use VALUES OF with undefined row
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
29 guide (-1000) := 1;
30 guide (1000) := 7589;
31 guide (10000) := 5001;
32 BEGIN
33 FORALL indx IN VALUES OF guide INSERT INTO favorites VALUES family (indx);
34 EXCEPTION
35 WHEN OTHERS
36 THEN
37 DBMS_OUTPUT.PUT_LINE (SQLERRM);
38 END;
39 SELECT COUNT(*) into l_count FROM favorites;
40 DBMS_OUTPUT.PUT_LINE (l_count);
41
42 END;
43 /
PL/SQL procedure successfully completed.
SQL>
SQL> DROP TABLE favorites;
Table dropped.
SQL>
SQL>