PL/SQL Process Control

1 assigning values to a database

database assignment is done by Select statement, each time a Select statement is executed, the assignment variable is generally required to correspond to the column name in select.

2 You cannot assign a column in a SELECT statement to a Boolean variable

3 Conditional Statements

IF< Boolean expressions > then

PL/SQL and statements;

END IF;

if< Boolean expression > Then

PL/SQL and statements;

ELSE

other statements;

END IF;

IF < Boolean expressions > Then

PL/SQL and statements ;

Elsif< Other Boolean expressions > Then

Other statements ;

elsif< other Boolean expressions > Then

Other statements ;

ELSE

Other statements ;

END IF;  

Case expression (switch)

Case Selector

when expression1 then RESULT1

WHENexpression2 THENRESULT2

Whenexpressionn Thenresultn

[ ELSEresultn+1]

END;

Example:

/* use IF ... Then ... Elsif ... Then ... ELSE ... END IF;

requirements : to inquire about the salary of the staff of the number , if their wages are greater than or equal to 10000 Then print ' salary >= 10000 ';

if between 10000 , print ' 5000<= salary < 10000 '; otherwise print ' Salary < ' * *

Declare

V_salary Employees.salary%type;

Begin

Select Salary into V_salary

From Employees

where employee_id=150;

if (v_salary>=10000) then Dbms_output.put_line (' Salary >= 10000 ');

elsif (v_salary>5000 and v_salary<10000) then Dbms_output.put_line (' 5000<= salary < 10000 ');

else Dbms_output.put_line (' salary <5000 ');

End If;

End

Method 2

/* use IF ... Then ... Elsif ... Then ... ELSE ... END IF;

Requirements : Check out The employee's salary , if its salary is greater than or equal to 10000 print ' Salary >= 10000 ';

if between 10000 , print ' 5000<= Salary < 10000 '; Otherwise print ' Salary < ' * *

Declare

V_salary Employees.salary%type;

V_temp VARCHAR2 (30);

Begin

Select Salary into V_salary

From Employees

where employee_id=150;

v_temp:=

Case Trunc (v_salary/5000) while 0 Then ' salary<5000 '

When 1 Then ' 5000<= salary< 10000 '

Else ' salary >=10000 '

End

Dbms_output.put_line (v_temp);

End

This is a very limited approach.

4 Cycles

1. Simple Cycle

LOOP

The statement to execute ;

Exit when < conditional statement >;/* Condition satisfied, exiting Loop statement */

END LOOP;

Example 3.

DECLARE

int number (2): = 0;

BEGIN

LOOP

int: = int + 1;

Dbms_output. The current value of put_line (' int ' is: ' | | int);

EXIT when int = 10;

END LOOP;

END;

2. while loop ( compared with 1, 2 recommended )

While < Boolean expressions > LOOP

The statement to execute ;

END LOOP;

Example 4.

DECLARE

X number: = 1;

BEGIN

While x<=10 LOOP

Dbms_output. The current value of Put_Line (' x ' is: ' | | x);

X:= x+1;

END LOOP;

END;

3 Digital Loop ( for Loop)

For loop counter in [REVERSE] lower limit: Upper LOOP

The statement to execute;

END LOOP;

For Each loop, the loop variable is automatically added 1, and the loop variable is automatically minus 1 using the keyword reverse. The numbers following the in REVERSE must be in order from small to large, and must be integers, not variables or expressions. You can use exit to exit the loop.

Example 5.

BEGIN

for int in 1..10 LOOP

Dbms_output. The current value of put_line (' int ' is: ' | | int);

END LOOP;

END;

Example

-- seeking The prime number between 2-100

Declare

V_i Number (3): = 2;

V_j Number (3): = 2;

V_flag Number (1): = 1;

Begin

Dbms_output.put_line (' prime number as follows : ');

While v_i<=100 loop

While V_J<=SQRT (v_i) loop

If V_i mod v_j=0 then v_flag:=0; End If;

v_j:=v_j+1;

End Loop;

if (v_flag=1) then Dbms_output.put_line (v_i); End If;

V_flag:=1;

v_i:=v_i+1;

v_j:=2;

End Loop;

End

The second method of

Declare

V_flag Number (1): = 1;

Begin

Dbms_output.put_line (' prime number as follows : ');

For I in 2..100 loop

For j in 2..sqrt (i) loop

if (i mod j =0) then v_flag:=0; Goto label; End If;

End Loop;

<< label >>

if (v_flag=1) then dbms_output.put_line (i); End If;

V_flag:=1;

End Loop;

End

Other examples

9. Use the Loop statement to print the 1-100. (three different ways)

1). LOOP ... EXIT when ... END LOOP

Declare

-- initialization conditions

V_i Number (3): = 1;

Begin

Loop

-- Loop body

Dbms_output.put_line (v_i);

-- Cycle conditions

Exit when v_i = 100;

-- Iteration conditions

V_i: = v_i + 1;

End Loop;

End

2). While ... LOOP ... END LOOP

Declare

-- initialization conditions

V_i Number (3): = 1;

Begin

-- Cycle conditions

While V_i <= loop

-- Loop body

Dbms_output.put_line (v_i);

-- Iteration conditions

V_i: = v_i + 1;

End Loop;

End

3).

Begin

For I in 1.. Loop

Dbms_output.put_line (i);

End Loop;

End

Comprehensive use if, while statement to print all primes between 1-100

( primes : integers with only two positive numbers , 2, 3, 5, 7, one, four, ...).

Declare

V_flag Number (1): = 1;

V_i Number (3): = 2;

V_j Number (2): = 2;

Begin

while (v_i<=100) loop

While V_j <= sqrt (v_i) loop

if (mod (v_i,v_j) =0) then v_flag:= 0;

End If;

V_j: =v_j +1;

End Loop;

if (v_flag=1) then Dbms_output.put_line (v_i);

End If;

V_flag: = 1;

V_j: = 2;

V_i: =v_i +1;

End Loop;

End

( Act Two ) Use for Loop Implementation 1-100 The output of the prime number between

Declare

--The tag value , if 1 , is a prime number , Otherwise it is not

V_flag Number (1): = 0;

Begin

For I in 2.. Loop

V_flag: = 1;

For j in 2.. sqrt (i) loop

If I mod j = 0 Then

V_flag: = 0;

End If;

End Loop;

If V_flag = 1 Then

Dbms_output.put_line (i);

End If;

End Loop;

End

One by one. using goto

Declare

--The tag value , if 1 , is a prime number , Otherwise it is not

V_flag Number (1): = 0;

Begin

For I in 2.. Loop

V_flag: = 1;

For j in 2.. sqrt (i) loop

If I mod j = 0 Then

V_flag: = 0;

Goto label;

End If;

End Loop;

<<label>>

If V_flag = 1 Then

Dbms_output.put_line (i);

End If;

End Loop;

End

11+. Print 1 -- - the natural number, when printed to - , out of the loop, output "End of print"

( method I )

Begin

For I in 1..100 loop

Dbms_output.put_line (i);

if (i =) Then

Goto label;

End If;

End Loop;

<<label>>

Dbms_output.put_line (' end of print ');

End

( method Two )

Begin

For I in 1..100 loop

Dbms_output.put_line (i);

if (i mod = 0) then Dbms_output.put_line (' end of print ');

Exit

End If;

End Loop;

End

PL/SQL Process Control