Conditional statement

Process control if else

set serverout on;
declare employee_number number;
begin
 select count(*) into employee_count from employees where employee_age>30;
 if employee_count>10 then
 dbms_output.put_line('The company has employees older than 30')；
 else
 dbms_output.put_line('There are no employees older than 30 in the company')；
 end if;
end

When there are multiple elsif criteria for statistics of employee information older than 30, they are as follows:

declare employee_number number;
begin
 select count(*) into employee_count from employees where employee_age>30;
 if employee_count=1 then
 dbms_output.put_line('The company has an employee older than 30')；
 elsif employee_count>1 then
 dbms_output.put_line('How many employees are older than 30 in the company')；
 else
 dbms_output.put_line('There are no employees older than 30 in the company')；
 end if;
end

Process control case when branch

case when has the same effect as if else. When there are more matching situations, the process control can be more clear

declare employee_number number;
begin
 select count(*) into employee_count from employees where employee_age>30;
 case employee_count
  when 1 then
 dbms_output.put_line('The company has an employee older than 30')；
 when 0 then
 dbms_output.put_line('There are no employees older than 30 in the company')；
 else
 dbms_output.put_line('The company has more than 30 employees')；
 end case
end

Loop statement

Unconditional loop

declare v_id number :=0;
v_name varchar2(20);
begin
    loop
    if v_id>=5 then
    exit;--Exit loop
    end if;
    v_id:=v_id+1;

    select empolyee_name into v_name from employees where employee_id=v_id;

    dbms_output.put_line(v_id||'Staff number is'||v_name);
    end loop;
end;

In addition to using if to judge the exit loop, you can also jump out of the loop in the form of exit when

while Loop

while condition judgment loop
 Cyclic operation
end loop；

While specifies the loop condition, which is still the same as the loop. The loop loop here is exactly the same as the loop in the unconditional loop, except that the while condition is added

declare v_id number :=0;
v_name varchar2(20);
begin
while v_id>=5
loop

v_id:=v_id+1;

select empolyee_name into v_name from employees where employee_id=v_id;

dbms_output.put_line(v_id||'Staff number is'||v_name);
end loop;
end;

for cycle

declare v_id number :=0;
v_name varchar2(20);
begin
for v_id in 1..5
loop

v_id:=v_id+1;

select empolyee_name into v_name from employees where employee_id=v_id;

dbms_output.put_line(v_id||'Staff number is'||v_name);
end loop;
end;

1.. 5 represents all integers from 1 to 5