в 13:45, 26 мая 2010

2010-05-26T13:45:19Z

Admin: 1 версия

2010-05-26T09:59:38Z

1 версия

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

==Call EXIT to jump out of a while loop==



<source lang="sql">

SQL>
SQL> DECLARE
2 v_Radius NUMBER := 2;
3 BEGIN
4 WHILE TRUE LOOP
5 DBMS_OUTPUT.PUT_LINE("The Area is " || v_Radius * v_Radius);
6 IF v_Radius = 10 THEN
7 EXIT;
8 END IF;
9 v_Radius := v_Radius + 2 ; -- Calculates Area for Even Radius
10 END LOOP;
11 END;
12 /
The Area is 4
The Area is 16
The Area is 36
The Area is 64
The Area is 100
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> --

</source>

==Change while loop counter==



<source lang="sql">

SQL> set serverout on;
SQL> -- while loop
SQL>
SQL> declare
2 loops number := 0;
3 begin
4 dbms_output.put_line("Before my loop");
5
6 while loops < 5 loop
7 dbms_output.put_line("Looped " || loops || " times");
8 loops := loops + 1;
9 end loop;
10
11 dbms_output.put_line("After my loop");
12 end;
13 /
Before my loop
Looped 0 times
Looped 1 times
Looped 2 times
Looped 3 times
Looped 4 times
After my loop
PL/SQL procedure successfully completed.
SQL>

</source>

==Corrected WHILE loop that executes==



<source lang="sql">

SQL>
SQL> -- Corrected WHILE loop that executes.
SQL>
SQL> set serveroutput on;
SQL>
SQL> DECLARE
2 c NUMBER := 0;
3 BEGIN
4 WHILE c <= 10 LOOP
5 c := c + 1;
6 DBMS_OUTPUT.PUT_LINE("The value of c is " || c);
7 END LOOP;
8 END;
9 /
The value of c is 1
The value of c is 2
The value of c is 3
The value of c is 4
The value of c is 5
The value of c is 6
The value of c is 7
The value of c is 8
The value of c is 9
The value of c is 10
The value of c is 11
PL/SQL procedure successfully completed.
SQL>

</source>

==Do calculation with while loop counter==



<source lang="sql">

SQL>
SQL> DECLARE
2 v_Radius NUMBER := 2;
3 BEGIN
4 WHILE v_Radius <=10 LOOP
5 DBMS_OUTPUT.PUT_LINE("The Area is " || v_Radius * v_Radius);
6 v_Radius := v_Radius + 2 ; -- Calculates Area for Even Radius
7 END LOOP;
8 END;
9 /
The Area is 4
The Area is 16
The Area is 36
The Area is 64
The Area is 100
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> --

</source>

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



<source lang="sql">

SQL>
SQL> -- Example of a WHILE loop that never executes.
SQL> set serveroutput on;
SQL>
SQL> DECLARE
2 c NUMBER := 0;
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE("The value of variable_Calc is " || c);
5 WHILE c >= 10 LOOP
6 c := c + 1;
7 DBMS_OUTPUT.PUT_LINE("The value of variable_Calc is " || c);
8 END LOOP;
9 END;
10 /
The value of variable_Calc is 0
PL/SQL procedure successfully completed.
SQL>

</source>

==Insert value in while loop==



<source lang="sql">

SQL>
SQL> create table supplier(
2 supplier_no integer primary key
3 ,supplier_name varchar2(50)
4 ,address varchar(30)
5 ,city varchar(20)
6 ,state varchar2(2)
7 ,area_code varchar2(3)
8 ,phone varchar2(8)
9 );
Table created.
SQL>
SQL>
SQL> declare
2 v_ctr number := 2 ;
3 v_prod_ctr number := 3 ;
4 v_loop number := 1;
5
6 v_curr_supplier supplier.supplier_no%TYPE ;
7 begin
8 WHILE v_loop <= v_ctr LOOP
9 INSERT INTO supplier (SUPPLIER_NO, SUPPLIER_NAME)VALUES (v_loop+999, "#"||v_loop);
10 v_loop := v_loop + 1 ;
11 END LOOP;
12 COMMIT;
13 end;
14 /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from supplier;
SUPPLIER_NO SUPPLIER_NAME ADDRESS CITY ST ARE PHONE
----------- -------------------------------------------------- ------------------------------ -------------------- -- --- --------
1000 #1
1001 #2
2 rows selected.
SQL>
SQL> drop table supplier;
Table dropped.
SQL>
SQL> --

</source>

==Put two statement in while loop==



<source lang="sql">

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 /
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>
SQL>
SQL>
SQL>
SQL> --

</source>

==The WHILE loop uses a loop index value as its gate on entry criterion:==



<source lang="sql">

SQL>
SQL> DECLARE
2 counter NUMBER := 1;
3 BEGIN
4 WHILE (counter < 5) LOOP
5 dbms_output.put_line("Index ["||counter||"].");
6 IF counter >= 1 THEN
7 counter := counter + 1;
8 END IF;
9 END LOOP;
10 END;
11 /
Index [1].
Index [2].
Index [3].
Index [4].
PL/SQL procedure successfully completed.
SQL>

</source>

==This script demonstrates variable visibility==



<source lang="sql">

SQL>
SQL>
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
2 visibleValue VARCHAR2(30);
3 hiddenValue VARCHAR2(30);
4 BEGIN
5 visibleValue := "visible";
6 hiddenValue := "hidden";
7
8 DBMS_OUTPUT.PUT_LINE("OUTER BLOCK");
9 DBMS_OUTPUT.PUT_LINE(visibleValue);
10 DBMS_OUTPUT.PUT_LINE(hiddenValue);
11
12 DECLARE
13 hiddenValue NUMBER(10);
14 BEGIN
15 DBMS_OUTPUT.PUT_LINE("INNER BLOCK");
16 hiddenValue := "inner hiddenValue";
17 DBMS_OUTPUT.PUT_LINE(hiddenValue);
18 EXCEPTION
19 WHEN OTHERS
20 THEN
21 DBMS_OUTPUT.PUT_LINE("hiddenValue of type VARCHAR2 was...hidden");
22 END;
23 END;
24 /
OUTER BLOCK
visible
hidden
INNER BLOCK
hiddenValue of type VARCHAR2 was...hidden
PL/SQL procedure successfully completed.

</source>

==Use EXIT WHEN to exit a while loop==



<source lang="sql">

SQL>
SQL> DECLARE
2 v_Radius NUMBER := 2;
3 BEGIN
4 WHILE TRUE LOOP
5 DBMS_OUTPUT.PUT_LINE("The Area is " ||v_Radius * v_Radius);
6 EXIT WHEN v_RADIUS = 10;
7 v_Radius := v_Radius + 2 ; -- Calculates Area for Even Radius
8 END LOOP;
9 END;
10 /
The Area is 4
The Area is 16
The Area is 36
The Area is 64
The Area is 100
PL/SQL procedure successfully completed.
SQL>
SQL> --

</source>

==WHILE Loop==



<source lang="sql">

SQL>
SQL> DECLARE
2 v_count PLS_INTEGER := 1;
3 BEGIN
4 WHILE v_count <= 20
5 LOOP
6 DBMS_OUTPUT.PUT_LINE("While loop iteration: "||v_count);
7 v_count := v_count + 1;
8 END LOOP;
9 END;
10 /
While loop iteration: 1
While loop iteration: 2
While loop iteration: 3
While loop iteration: 4
While loop iteration: 5
While loop iteration: 6
While loop iteration: 7
While loop iteration: 8
While loop iteration: 9
While loop iteration: 10
While loop iteration: 11
While loop iteration: 12
While loop iteration: 13
While loop iteration: 14
While loop iteration: 15
While loop iteration: 16
While loop iteration: 17
While loop iteration: 18
While loop iteration: 19
While loop iteration: 20
PL/SQL procedure successfully completed.
SQL>

</source>

==While loop and number counter==



<source lang="sql">

SQL> -- create demo table
SQL> create table emp(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 fname VARCHAR2(10 BYTE),
4 lname 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>
SQL>
SQL> -- prepare data
SQL> insert into emp(ID, fname, lname, 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 emp(ID, fname, lname, 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>
SQL>
SQL> declare
2 s Number(8,2);
3 totalValue Number(8,2);
4 begin
5 WHILE totalValue < 100 LOOP
6 Select salary into s from emp;
7 totalValue := totalValue + s;
8 END LOOP;
9
10 dbms_output.put_line(totalValue);
11 end;
12 /
PL/SQL procedure successfully completed.
SQL>
SQL> drop table emp;
Table dropped.
SQL>

</source>

==WHILE..LOOP, Cursor Loop==



<source lang="sql">

SQL>
SQL> CREATE TABLE lecturer (
2 id NUMBER(5) PRIMARY KEY,
3 first_name VARCHAR2(20),
4 last_name VARCHAR2(20),
5 major VARCHAR2(30),
6 current_credits NUMBER(3)
7 );
Table created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10001, "Scott", "Lawson","Computer Science", 11);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major, current_credits)
2 VALUES (10002, "Mar", "Wells","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10003, "Jone", "Bliss","Computer Science", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10004, "Man", "Kyte","Economics", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10005, "Pat", "Poll","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10006, "Tim", "Viper","History", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10007, "Barbara", "Blues","Economics", 7);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10008, "David", "Large","Music", 4);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10009, "Chris", "Elegant","Nutrition", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10010, "Rose", "Bond","Music", 7);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10011, "Rita", "Johnson","Nutrition", 8);
1 row created.
SQL>
SQL> INSERT INTO lecturer (id, first_name, last_name, major,current_credits)
2 VALUES (10012, "Sharon", "Clear","Computer Science", 3);
1 row created.
SQL>
SQL> CREATE TABLE myStudent (
2 student_id NUMBER(5) NOT NULL,
3 department CHAR(3) NOT NULL,
4 course NUMBER(3) NOT NULL,
5 grade CHAR(1)
6 );
Table created.
SQL>
SQL>
SQL>
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10000, "CS", 102, "A");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10002, "CS", 102, "B");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10003, "CS", 102, "C");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10000, "HIS", 101, "A");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10001, "HIS", 101, "B");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10002, "HIS", 101, "B");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10003, "HIS", 101, "A");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10004, "HIS", 101, "C");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10005, "HIS", 101, "C");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10006, "HIS", 101, "E");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10007, "HIS", 101, "B");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10008, "HIS", 101, "A");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10009, "HIS", 101, "D");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10010, "HIS", 101, "A");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10008, "NUT", 307, "A");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10010, "NUT", 307, "A");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10009, "MUS", 410, "B");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10006, "MUS", 410, "E");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10011, "MUS", 410, "B");
1 row created.
SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
2 VALUES (10000, "MUS", 410, "B");
1 row created.
SQL>
SQL> CREATE TABLE MyTable (
2 num_col NUMBER,
3 char_col VARCHAR2(60)
4 );
Table created.
SQL>
SQL>
SQL> DECLARE
2 CURSOR myHistoryLecturer IS
3 SELECT id, first_name, last_name
4 FROM lecturer
5 WHERE major = "History";
6
7 myLecturerData myHistoryLecturer%ROWTYPE;
8 BEGIN
9 OPEN myHistoryLecturer;
10 FETCH myHistoryLecturer INTO myLecturerData;
11
12 WHILE myHistoryLecturer%FOUND LOOP
13 INSERT INTO myStudent (student_id, department, course)
14 VALUES (myLecturerData.ID, "HIS", 101);
15
16 INSERT INTO MyTable (num_col, char_col)
17 VALUES (myLecturerData.ID,
18 myLecturerData.first_name || " " || myLecturerData.last_name);
19
20 FETCH myHistoryLecturer INTO myLecturerData;
21 END LOOP;
22
23 CLOSE myHistoryLecturer;
24
25 COMMIT;
26 END;
27 /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from MyTable;
NUM_COL CHAR_COL
---------- ------------------------------------------------------------
10002 Mar Wells
10005 Pat Poll
10006 Tim Viper
SQL>
SQL> drop table myStudent;
Table dropped.
SQL> drop table lecturer;
Table dropped.
SQL> drop table MyTable;
Table dropped.
SQL>
SQL>
--

</source>

==WHILE Loop: Test the loop counter before each loop iteration to insure that it is still less than 50==



<source lang="sql">

SQL>
SQL>
SQL> CREATE TABLE MyTable (
2 num_col NUMBER,
3 char_col VARCHAR2(60)
4 );
Table created.
SQL>
SQL>
SQL> DECLARE
2 v_Counter BINARY_INTEGER := 1;
3 BEGIN
4 WHILE v_Counter <= 50 LOOP
5 INSERT INTO MyTable
6 VALUES (v_Counter, "Loop index");
7 v_Counter := v_Counter + 1;
8 END LOOP;
9 END;
10 /
PL/SQL procedure successfully completed.
SQL>
SQL> select * from MyTable;
NUM_COL CHAR_COL
---------- ------------------------------------------------------------
1 Loop index
2 Loop index
3 Loop index
4 Loop index
5 Loop index
6 Loop index
7 Loop index
8 Loop index
9 Loop index
10 Loop index
11 Loop index
NUM_COL CHAR_COL
---------- ------------------------------------------------------------
12 Loop index
13 Loop index
14 Loop index
15 Loop index
16 Loop index
17 Loop index
18 Loop index
19 Loop index
20 Loop index
21 Loop index
22 Loop index
NUM_COL CHAR_COL
---------- ------------------------------------------------------------
23 Loop index
24 Loop index
25 Loop index
26 Loop index
27 Loop index
28 Loop index
29 Loop index
30 Loop index
31 Loop index
32 Loop index
33 Loop index
NUM_COL CHAR_COL
---------- ------------------------------------------------------------
34 Loop index
35 Loop index
36 Loop index
37 Loop index
38 Loop index
39 Loop index
40 Loop index
41 Loop index
42 Loop index
43 Loop index
44 Loop index
NUM_COL CHAR_COL
---------- ------------------------------------------------------------
45 Loop index
46 Loop index
47 Loop index
48 Loop index
49 Loop index
50 Loop index
50 rows selected.
SQL>
SQL> drop table MyTable;
Table dropped.
SQL>

</source>

==WHILE Loop: This condition will evaluate to NULL, since v_Counter is initialized to NULL by default==



<source lang="sql">

SQL>
SQL> CREATE TABLE MyTable (
2 num_col NUMBER,
3 char_col VARCHAR2(60)
4 );
Table created.
SQL>
SQL>
SQL> DECLARE
2 v_Counter BINARY_INTEGER;
3 BEGIN
4 WHILE v_Counter <= 50 LOOP
5 INSERT INTO MyTable
6 VALUES (v_Counter, "Loop index");
7 v_Counter := v_Counter + 1;
8 END LOOP;
9 END;
10 /
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> select * from MyTable;
no rows selected
SQL>
SQL> drop table MyTable;
Table dropped.

</source>

==While loop with complex conditions==



<source lang="sql">

SQL>
SQL>
SQL> --While loop with conditions
SQL>
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
2 counter NUMBER(2):= 1;
3 counter2 NUMBER(2):= 1;
4 BEGIN
5 WHILE counter <= 12 AND counter2 <= 14 LOOP
6 counter := counter + 1;
7 counter2 := counter2 + 1;
8 END LOOP;
9 DBMS_OUTPUT.PUT_LINE("counter2:"||counter2);
10 DBMS_OUTPUT.PUT_LINE("counter:"||counter);
11 END;
12 /
counter2:13
counter:13
PL/SQL procedure successfully completed.
SQL>
SQL>

</source>

==While Loop with condition==



<source lang="sql">

SQL>
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 counter INTEGER := 2;
3 BEGIN
4
5
6 counter := 0;
7 WHILE counter < 6 LOOP
8 counter := counter + 1;
9 DBMS_OUTPUT.PUT_LINE(counter);
10 END LOOP;
11 END;
12 /
1
2
3
4
5
6
PL/SQL procedure successfully completed.
SQL>

</source>

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

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

в 13:45, 26 мая 2010

Admin: 1 версия