Oracle学习----高级篇（三）-----Oracle控制语句

Oracle控制语句

• 为后面的存储过程作铺垫

--让控制台输出信息
set serveroutput on;
--定义数值
declare n number:=1;
        v varchar2(10):='world';
begin
  --控制台输出
  dbms_output.put_line('hello'||n||v);
end;
  

IF条件语句

set serveroutput on;
declare emp_count number;
begin
  select count(*) into emp_count from emp where sal>=3000;
  if emp_count=1 then 
    dbms_output.put_line('有1名员工的基本薪资大于等于3000');
  else if emp_count>1 then
    dbms_output.put_line('有'||emp_count||'名员工的基本薪资大于等于3000');
  else
    dbms_output.put_line('没有员工的基本薪资大于等于3000'); 
  end if; 
  end if;
 end;

CASE WHEN 流程控制语句

set serveroutput 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('没有员工的薪资大于3000');
  when 1 then dbms_output.put_line('有1名员工的薪资大于3000');
  when 2 then dbms_output.put_line('有2名员工的薪资大于3000');
  when 3 then dbms_output.put_line('有3名员工的薪资大于3000');
  else dbms_output.put_line('超过3名员工的薪资大于3000');
  end case;
end;

循环语句

无条件循环 loop

set serveroutput 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('ID为'||g_id||'的最低薪资为：'||g_losal||',最高薪资为：'||g_hisal);
    g_id:=g_id+1;
  end loop;
end;

while 循环

set serveroutput 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('ID为'||g_id||'的最低薪资为：'||g_losal||',最高薪资为：'||g_hisal);
    g_id:=g_id+1;
  end loop;
end;

for 循环

set serveroutput 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('ID为'||g_id||'的最低薪资为：'||g_losal||',最高薪资为：'||g_hisal);
  end loop;
end;