в 13:45, 26 мая 2010

2010-05-26T13:45:46Z

Admin: 1 версия

2010-05-26T10:05:30Z

1 версия

Новая страница

== A complete example of using the cursor variable using a WHILE LOOP==



<source lang="sql">
SQL>
SQL>
SQL> create table product(
2 product_id number(4) not null,
3 product_description varchar2(20) not null
4 );
Table created.
SQL>
SQL> insert into product values (1,"Java");
1 row created.
SQL> insert into product values (2,"Oracle");
1 row created.
SQL> insert into product values (3,"C#");
1 row created.
SQL> insert into product values (4,"Javascript");
1 row created.
SQL> insert into product values (5,"Python");
1 row created.
SQL>
SQL>
SQL> create table company(
2 product_id number(4) not null,
3 company_id NUMBER(8) not null,
4 company_short_name varchar2(30) not null,
5 company_long_name varchar2(60)
6 );
Table created.
SQL> insert into company values(1,1001,"A Inc.","Long Name A Inc.");
1 row created.
SQL> insert into company values(1,1002,"B Inc.","Long Name B Inc.");
1 row created.
SQL> insert into company values(1,1003,"C Inc.","Long Name C Inc.");
1 row created.
SQL> insert into company values(2,1004,"D Inc.","Long Name D Inc.");
1 row created.
SQL> insert into company values(2,1005,"E Inc.","Long Name E Inc.");
1 row created.
SQL> insert into company values(2,1006,"F Inc.","Long Name F Inc.");
1 row created.
SQL>
SQL>
SQL>
SQL> DECLARE
2 CURSOR cursorValue IS
3 SELECT h.product_description,o.rupany_short_name
4 FROM company o,product h
5 WHERE o.product_id =h.product_id
6 ORDER by 2;
7 v_company_rec cursorValue%ROWTYPE;
8 BEGIN
9 OPEN cursorValue;
10
11 FETCH cursorValue INTO v_company_rec;
12
13 WHILE (cursorValue%FOUND) LOOP
14 dbms_output.put_line(rpad(v_company_rec.product_description,20," ")||" "||
15 rpad(v_company_rec.rupany_short_name,30," "));
16 FETCH cursorValue INTO v_company_rec;
17 END LOOP;
18 CLOSE cursorValue;
19 END;
20 /
Java A Inc.
Java B Inc.
Java C Inc.
Oracle D Inc.
Oracle E Inc.
Oracle F Inc.
PL/SQL procedure successfully completed.
SQL>
SQL> drop table product;
Table dropped.
SQL>
SQL> drop table company;
Table dropped.
SQL>
SQL></source>

== Calculate circle Area in WHILE loop==



<source lang="sql">
SQL>
SQL> set serveroutput on
SQL> set echo on
SQL>
SQL> DECLARE
2 v_Radius NUMBER := 2;
3 BEGIN
4 WHILE v_Radius <=10 LOOP
5 DBMS_OUTPUT.PUT_LINE("The Area is " ||
6 mypi * v_Radius * v_Radius);
7 v_Radius := v_Radius + 2 ;
8 END LOOP;
9 END;
10 /
The Area is 12.56
The Area is 50.24
The Area is 113.04
The Area is 200.96
The Area is 314
PL/SQL procedure successfully completed.</source>

== Example of a WHILE loop that never executes.==



<source lang="sql">
DECLARE
v_Calc NUMBER := 0;
BEGIN
WHILE v_Calc >= 10 LOOP
v_Calc := v_Calc + 1;
DBMS_OUTPUT.PUT_LINE("The value of v_Calc is " || v_Calc);
END LOOP;
END;
/

Corrected WHILE loop that executes.

SQL> DECLARE
2 v_Calc NUMBER := 0;
3 BEGIN
4 WHILE v_Calc <= 10 LOOP
5 v_Calc := v_Calc + 1;
6 DBMS_OUTPUT.PUT_LINE("The value of v_Calc is " || v_Calc);
7 END LOOP;
8 END;
9 /
The value of v_Calc is 1
The value of v_Calc is 2
The value of v_Calc is 3
The value of v_Calc is 4
The value of v_Calc is 5
The value of v_Calc is 6
The value of v_Calc is 7
The value of v_Calc is 8
The value of v_Calc is 9
The value of v_Calc is 10
The value of v_Calc is 11
PL/SQL procedure successfully completed.
SQL></source>

== EXIT a WHILE LOOP with exit command==



<source lang="sql">
SQL>
SQL> set serveroutput on
SQL> set echo on
SQL>
SQL> DECLARE
2 v_Radius NUMBER := 2;
3 BEGIN
4 WHILE TRUE LOOP
5 DBMS_OUTPUT.PUT_LINE("The Area is "||mypi * v_Radius * v_Radius);
6 IF v_Radius = 10 THEN
7 EXIT;
8 END IF;
9 v_Radius := v_Radius + 2 ;
10 END LOOP;
11 END;
12 /
The Area is 12.56
The Area is 50.24
The Area is 113.04
The Area is 200.96
The Area is 314
PL/SQL procedure successfully completed.
SQL>
SQL></source>

== Nested WHILE Loop==



<source lang="sql">
SQL>
SQL>
SQL> declare
2 v_ind NUMBER;
3 v_current NUMBER;
4 begin
5 v_current:=0;
6 while v_current<=25
7 loop
8 v_ind:=0;
9 while v_ind<4
10 loop
11 v_ind:= v_ind+1;
12 DBMS_OUTPUT.put_line(v_current);
13 end loop;
14 v_current:=v_current+5;
15 end loop;
16 end;
17 /
0
0
0
0
5
5
5
5
10
10
10
10
15
15
15
15
20
20
20
20
25
25
25
25
PL/SQL procedure successfully completed.
SQL></source>

== The EXIT and EXIT WHEN Statements==

The EXIT and EXIT WHEN statements enable you to escape out of the control of a loop.
The format of the EXIT loop is



<source lang="sql">
EXIT;</source>

== Use WHILE Loop to insert data to table==



<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>
SQL> DECLARE
2 v_Counter BINARY_INTEGER := 1;
3 BEGIN
4 WHILE v_Counter <= 50 LOOP
5 INSERT INTO employee (id) VALUES (v_Counter);
6 v_Counter := v_Counter + 1;
7 END LOOP;
8 END;
9 /
PL/SQL procedure successfully completed.
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 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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
58 rows selected.
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL></source>

== While Counter is initialized to NULL==



<source lang="sql">
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 v_Counter BINARY_INTEGER;
3 BEGIN
4
5 WHILE v_Counter <= 50 LOOP
6 INSERT INTO employee (id) VALUES (v_Counter);
7 v_Counter := v_Counter + 1;
8 END LOOP;
9 END;
10 /
PL/SQL procedure successfully completed.
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 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> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
SQL>
SQL>
SQL></source>

== While loop==



<source lang="sql">
SQL> set serveroutput on
SQL> DECLARE
2 just_a_num NUMBER := 1;
3 BEGIN
4 WHILE (just_a_num <= 10) LOOP
5 dbms_output.put_line(just_a_num);
6 just_a_num := just_a_num + 1;
7 END LOOP;
8 END;
9 /
1
2
3
4
5
6
7
8
9
10
PL/SQL procedure successfully completed.
SQL></source>

== WHILE Loops==

WHILE loops check the condition and execute a set of commands.
The loop is repeated until the loop is exited.
A WHILE loop is exactly equivalent to a regular loop with an EXIT WHEN as the first statement.
The condition is checked before the code in the loop is executed.
If the condition is false, the code in the loop will never be executed.
WHILE loop makes your code a little easier to read than with a normal loop.



<source lang="sql">
WHILE condition LOOP
statements
END LOOP;</source>

== WHILE LOOP with counter==



<source lang="sql">
SQL>
SQL> set serveroutput on
SQL> set echo on
SQL>
SQL> DECLARE
2 v_Calc NUMBER := 0;
3 BEGIN
4 WHILE v_Calc >= 10 LOOP
5 v_Calc := v_Calc + 1;
6 DBMS_OUTPUT.PUT_LINE("The value of v_Calc is " || v_Calc);
7 END LOOP;
8 END;
9 /
PL/SQL procedure successfully completed.
SQL>
SQL></source>

← Предыдущая	Версия 13:45, 26 мая 2010
(нет различий)

Oracle PL/SQL Tutorial/PL SQL Statements/While - История изменений

в 13:45, 26 мая 2010

Admin: 1 версия