Oracle Control statements

PL OUTPUT Statement

Set serverout on; --turn on the output statement function of PL
Declare

n Number:=1; --Declares a variable n of type number and assigns a value of 1
V varchar2: = ' world '; --Declare a variable V of type VARCHAR2 "World"
Begin--The control statement is written between Begin-end
Dbms_output.put_line (' Hello ' | | n| | V); --Output statement, data connection using ' | | '
End

If condition statement
Set serverout on;
declare emp_count number;
Begin
Select COUNT (*) into Emp_count from EMP where sal>=3000;
If Emp_count>0 Then
Dbms_output.put_line (' There ' | | emp_count| | ' The basic salary of an employee is greater than or equal to 3000 ');
Else
Dbms_output.put_line (' No employees have a basic salary greater than or equal to 3000 ');
End If;
End

Assignment statements
Set serverout on;
declare emp_count number;
Begin
Select COUNT (*) into Emp_count from EMP where sal>=3000; --Query out data and assign value to Emp_count
If Emp_count=1 Then
Dbms_output.put_line (' 1 employees have a basic salary greater than or equal to 3000 ');
else if Emp_count>1 then
Dbms_output.put_line (' There are more than 1 employees with a basic salary greater than or equal to 3000 ');
Else
Dbms_output.put_line (' No employees have a basic salary greater than or equal to 3000 ');
End If;
End If;
End

Case when Process Control statement

Set serverout on;
declare emp_count number;
Begin
Select COUNT (*) into Emp_count from EMP where sal>=3000;
Case Emp_count
When 0 then Dbms_output.put_line (' No employee's basic salary is greater than or equal to 3000 ');
When 1 then Dbms_output.put_line (' 1 employees have a basic salary greater than or equal to 3000 ');
When 2 then Dbms_output.put_line (' 2 employees have a basic salary greater than or equal to 3000 ');
When 3 then Dbms_output.put_line (' 3 employees have a basic salary greater than or equal to 3000 ');
else Dbms_output.put_line (' basic pay of more than 3 employees is greater than or equal to 3000 ');
End case;
End

Unconditional loop Loops

Set serverout on;
DECLARE g_id number:=2;
G_losal number;
G_hisal number;
Begin
Loop
if (g_id>4) then
Exit
End If;

Select Losal,hisal into G_losal,g_hisal from Salgrade where grade=g_id;
Dbms_output.put_line (g_id | | ' Lowest salary of grade ' | | G_losal | | ', the most well-paid: ' | | G_hisal);

g_id:=g_id+1;

End Loop;
End

While loop
Set serverout on;
DECLARE g_id number:=2;
G_losal number;
G_hisal number;
Begin

While G_id<5 loop

Select Losal,hisal into G_losal,g_hisal from Salgrade where grade=g_id;
Dbms_output.put_line (g_id | | ' Lowest salary of grade ' | | G_losal | | ', the most well-paid: ' | | G_hisal);
g_id:=g_id+1;
End Loop;

End

For loop
Set serverout on;
declare g_losal number;
G_hisal number;
Begin
For g_id in 2..4 loop
Select Losal,hisal into G_losal,g_hisal from Salgrade where grade=g_id;
Dbms_output.put_line (g_id | | ' Lowest salary of grade ' | | G_losal | | ', the most well-paid: ' | | G_hisal);
End Loop;
End

Oracle Control statements