2.跳转控制语句及游标

知识总结:

1.分支条件判断语法

2.loop循环语法

3.while循环语法

4.for循环语法

5.三种循环特点和区别

6.静态游标的语法及注意点

7.静态游标的常用属性

8.动态游标

一、条件判断

1.条件判断语法

1.单分支

if 条件 then--条件成立需要执行的内容

2.双分支

if 条件 then--条件成立需要执行的内容

else--条件不成立需要执行的内容

end if;

3.多分支

declare--定义变量
  begin
    if 条件 then --条件成立需要执行的内容
    elsif 条件1 then--条件1成立需要执行的内容
    ......
    else--所有条件不成立需要执行的内容
    end if;   
  end;

2.条件判断练习

2.1键盘输入一个员工编号,如果该员工的工资小于 2000 输出 '<2000' ;如果该员工的工资小于3000 输出 '<3000' ;如果该员工的工资小于 5000 输出 '<5000' ;如果该员工的工资大于等于5000 输出 '你太牛了'!

declare
      v_bh  number :=&输入员工编号;
      v_sal number;
      v_sql varchar2(1023);
    begin
      select sal into v_sal from emptest where empno=v_bh;
      if v_sal<2000 then 
        dbms_output.put_line('<2000');
      elsif v_sal<3000 then
        dbms_output.put_line('<3000');
      elsif v_sal<5000 then
        dbms_output.put_line('<5000');
      else
        dbms_output.put_line('你太牛了');
      end if;
    end;

2.2输入一个数字,如果是奇数,输出"奇数";如果不是奇数输出"偶数"

declare
      v_num number := &请输入数字;
    begin
      if mod(v_num, 2) = 0 then
        dbms_output.put_line('偶数');
      else
        dbms_output.put_line('奇数');
      end if;
    end;

二.循环

1.loop循环

declare--定义变量
  begin
   loop --需要重复做的事情
   exit when 退出循环条件;
   end loop;
  end;

2.while循环

declare --定义变量
  begin
    while 进入循环条件 loop --需要重复做的事情
    end loop;
  end;

3.for循环

3.1顺循环

begin
    for 循环变量 in 数字A..数字b loop  --重复做的事情
      end loop;    
    end;

3.2倒循环 reverse

for 循环变量 in reverse 数字A..数字b

3.3循环结果集

for 循环变量 in 查询语句 loop

三种循环的区别：

有退出循环条件的时候适合while（条件在循环前），loop（条件在循环后）；而for 循环的变量不需要定义

4.循环嵌套

continue;--退出本次循环进入下次循环

exit;--退出所在循环

for v_i in 1..5 loop--第一个循环
      dbms_output.put_line('v_i===:'||v_i);
      for v_j in 1..3 loop--第二个循环
        if v_j =2 then
          continue;--退出本次循环进入下次循环
          --exit;--退出所在循环
        end if;
        dbms_output.put_line('v_j--:'||v_j);
      end loop;--第二个循环结束
    end loop;   --第一个循环结束
  end;

学习案例

 declare
    v_i number := 1;
  begin
    loop
      dbms_output.put_line('跑圈:'||v_i);
      v_i := v_i+1;
      exit when v_i > 10;
    end loop;
  end;

declare
     v_i number := 1;
  begin
     while v_i <= 10 loop
       dbms_output.put_line('跑圈:'||v_i);
       v_i := v_i + 1;
     end loop;
  end;

begin
      for v_i in 1..10 loop
        dbms_output.put_line('跑圈:'||v_i);
      end loop;
end;

begin
      for v_i in reverse 1..10 loop
        dbms_output.put_line('跑圈:'||v_i);
      end loop;
    end;

 begin
      for v_i in (select * from emp where deptno = 30)loop
        dbms_output.put_line(v_i.empno||' '||v_i.ename);
      end loop;
    end;
    

三.游标

1.定义

游标它是一个服务器端的存储区，这个区域可以提供给用户使用，在这个区域里存储的是用户通过一个查询语句得到的结果集，

用户通过控制这个游标区域当中的指针 来提取游标中的数据，然后进行操作

2.静态游标语法：

declare
    cursor 游标名称 is 查询语句;
    定义变量1; --存游标内的一行数据
  begin
    open 游标名称;
    fetch 游标名称 into 变量1;
    --进行相应的业务操作
    close 游标名称;
  end;

3.游标的属性

数据类型：布尔型(bool) 真（ture）、假（false）

3.1 游标名称%found 返回值：布尔型 作用：判断游标是否获取到数据，是 返回 true，否返回 false

3.2 游标名称%notfound 返回值：布尔型 作用：判断游标是否“没有获取到”， 如果没有 返回 true，否则返回 false

3.3 游标名称%rowcount 返回值：整数 作用：获取当前执行成功的记录数，不是表的总记录数

4.学习案例

4.1使用游标输出7788的姓名和工资

declare
      cursor cur_e is select * from emp where empno= 7788;
      v_emp emp%rowtype;
    begin
      open cur_e;
      fetch cur_e into v_emp;
      dbms_output.put_line(v_emp.ename||' '||v_emp.sal);
      close cur_e;
    end;

4.2使用游标输出10号部门的姓名和工资

--loop
declare
      cursor cur_e is select *  from emp where deptno= 10;
      v_emp emp%rowtype;
    begin
      open cur_e;
      
       loop
        fetch cur_e into v_emp;
        exit when cur_e%notfound;
        dbms_output.put_line(v_emp.ename||' '||v_emp.sal);   
      end loop;  
      
      close cur_e;
    end;
--while
declare
      cursor cur_e is select *  from emp where deptno= 10;
      v_emp emp%rowtype;
    begin
      open cur_e;
      
      fetch cur_e into v_emp;
      while cur_e%found loop       
        dbms_output.put_line(v_emp.ename||' '||v_emp.sal);
        fetch cur_e into v_emp;
      end loop;
      
      close cur_e;
    end;
--for
declare
      cursor cur_e is select *  from emp where deptno= 10;
    begin
      
      for v_emp in cur_e loop
        dbms_output.put_line(v_emp.ename||' '||v_emp.sal);
      end loop;

    end;

4.3输入一个 部门编号,使用游标给该部门的员工更新工资,工资等级为 1 的涨薪 1%;工资等级为 2 的涨薪 2%...以此类推

declare
    v_deptno number := &部门编号;
    cursor cur_e is select e.empno,s.grade from emptest e join salgrade s on e.sal between s.losal and s.hisal where e.deptno = v_deptno;
    v_empno number;
    v_grade number;
  begin
    open cur_e;
    loop
      fetch cur_e into v_empno,v_grade;
      exit when cur_e%notfound;
      
      if v_grade = 1 then
        update  emptest set sal = sal * 1.01 where empno = v_empno;
      elsif v_grade = 2 then
        update  emptest set sal = sal * 1.02 where empno = v_empno; 
      elsif v_grade = 3 then
        update  emptest set sal = sal * 1.03 where empno = v_empno;  
      elsif v_grade = 4 then
        update  emptest set sal = sal * 1.04 where empno = v_empno;  
      elsif v_grade = 5 then
        update  emptest set sal = sal * 1.05 where empno = v_empno;   
      end if;
      
    end loop;  
    close cur_e;
  end;

4.动态游标

1.定义:

程序执行的时候含有参数,对于sql语句的解析必要等到参数确定的时候才能完成,相对对于静态游标,动态游标的结果集是不确定的

2.语法

declare
  type 游标类型名称 is ref cursor;
  游标名称 游标类型名称;
  变量1   ; --存查询语句的变量
  变量2   ; --存游标内的一行数据
begin
  变量1 := '动态sql';
  open 游标名称 for 变量1;
  loop
    fetch 游标名称
      into 变量2;
    exit when 游标名称%notfound;
    --获取数据进行操作
  end loop;
  close 游标名称;
end;

3.学习案例

3.1使用游标打印输出工资高于2000的员工的姓名,入职时间,入职天数

declare
      type t1 is ref cursor;
      cur_e t1;
      v_sql varchar2(200);
      v_name varchar2(20);
      v_hdate date;
      v_days number;
    begin
      v_sql := 'select ename, hiredate, round(sysdate - hiredate) days from emp where sal > 2000';
      open cur_e for v_sql;
        loop
          fetch cur_e into v_name,v_hdate,v_days;
          exit when cur_e%notfound;
          dbms_output.put_line(v_name||' '||v_hdate||' '||v_days);
        end loop;
      close cur_e;
    end;

3.2打印输出名称中包含 "EMP" 的所有表并显示表内信息

EMP表中的信息:

xxxx xxxx

EMPTEST表中的信息:

-- xxxx xxxx

declare
      type t1 is ref cursor;
      cur_e t1;
      v_sql varchar2(200);
      v_emp emp%rowtype;
    begin
      for v_i in (select table_name from user_tables where table_name like '%EMP%')loop
        dbms_output.put_line(v_i.table_name||'表中的信息:');
        
        v_sql := 'select * from '||v_i.table_name;
        open cur_e for v_sql;
          loop
            fetch cur_e into v_emp;
            exit when cur_e%notfound;
            dbms_output.put_line('   '||v_emp.empno||' '||v_emp.ename);
          end loop;
        close cur_e;
        
      end loop;     
    end;

4.练习

4.1使用动态游标打印输出工资最低的前5个员工的姓名，部门编号和工资

declare
       type t1 is ref cursor;
       cur_e   t1;
       v_deptno number;
       v_sal   number;
       v_sql   varchar2(300);
       v_ename varchar2(20);
     begin
       v_sql := 'select q.ename, q.deptno, q.sal
                 from (select t.*, row_number() over(order by sal) as px from emptest t) q
                 where px <= 5';
       open cur_e for v_sql;
       loop
         fetch cur_e
           into v_ename, v_deptno, v_sal;
         exit when cur_e%notfound;
         dbms_output.put_line(v_ename ||' '||v_deptno ||' '|| v_sal);
       end loop;
     
       close cur_e;
     end；    

4.2使用动态游标打印输出员工的姓名,部门名称（按照工资排倒序后只显示第 10 个到第 14 个员工的信息）

declare
      type t is ref cursor;
      cur_e    t;
      v_sql    varchar2(400);
      v_ename  varchar2(20);
      v_dname dept.dname%type;
    begin
      v_sql := 'select ename, dname
                from (select t.*, row_number() over(order by sal desc) xh
                        from emptest t) e
                join dept t on e.deptno = t.deptno 
               where xh between 10 and 14';
      open cur_e for v_sql;
      loop
        fetch cur_e
          into v_ename, v_dname;
        exit when cur_e%notfound;
        dbms_output.put_line(v_ename || ' ' || v_dname);
      end loop;
      close cur_e;
    end;

5.作业

1.输入年份，并判断是否为闰年

4的倍数，且不是100的倍数，为普通闰年

公历年份是是 400 的倍数才是世纪闰年

declare
v_hiredate number := &请输入年份;
begin 
  if mod(v_hiredate,4)=0 and mod(v_hiredate,100)!=0 then 
    dbms_output.put_line('普通闰年');
  elsif mod(v_hiredate,400)=0 then 
    dbms_output.put_line('世纪闰年');
  else
    dbms_output.put_line('不是闰年');
end if;
end;

2.输入一个员工编号，如果该员工 job 是经理，请输出“编号为XX的员工（员工姓名）职位为：经理”，其它情况则输出"普通员工"

declare
    v_empno number := &编号;
    v_emp   emptest%rowtype;
  begin
    select * into v_emp from emptest where empno = v_empno;
    if v_emp.job = 'MANAGER' then
      dbms_output.put_line('编号为' || v_emp.empno || '的员工(' || v_emp.ename ||
                           ')职位为：经理');
    else
      dbms_output.put_line('普通员工');
    end if;
  end;

3.使用游标打印输出工资高于2000的员工的姓名,入职时间,入职天数

declare
  cursor cur_e is
    select sal, round(sysdate - hiredate), ename from emp;
  v_sal   number;
  v_m     number;
  v_ename varchar2(10);
begin
  open cur_e;
  loop
    fetch cur_e into v_sal, v_m, v_ename;
    exit when cur_e%notfound;
    if v_sal > 2000 then
      dbms_output.put_line(v_ename || ' ' || v_m || ' ' || v_sal);
    end if;
  end loop;
  close cur_e;
end;

4.用游标属性 %rowcount 实现输出前十个员工的信息

declare
  cursor cur_e is
    select * from emp;
  v_emp emp%rowtype;
begin
  open cur_e;
  loop
    fetch cur_e into v_emp;
    dbms_output.put_line(v_emp.ename || '--' || v_emp.empno);
    exit when cur_e%rowcount = 10;
  end loop;
  close cur_e;
end;

5.使用游标根据salgrade表中的等级给所有员工加薪，等级1：5%，等级2：4%，等级3：3%，等级4：2%，等级5：1%，并打印输出每个人加薪前后的工资

declare
  cursor cur_e is
    select s.grade,e.sal,e.enpno
      from emptest e
      join salgrade s
        on e.sal between s.losal and s.hisal;
  v_grade number;
  v_sal number;
  v_sal2 number;
  v_empno number;
begin
  open cur_e;
  loop
    fetch cur_e into v_grade,v_sal,v_empno;
    exit when cur_e%notfound;
  
    if v_grade = 1 then
      update emptest set sal = sal * 1.05 into v_sal2;
    elsif v_grade = 2 then
      update emptest set sal = sal * 1.04 into v_sal2;
    elsif v_grade = 3 then
      update emptest set sal = sal * 1.03 into v_sal2;
    elsif v_grade = 4 then
      update emptest set sal = sal * 1.02 into v_sal2;
    elsif v_grade = 5 then
      update emptest set sal = sal * 1.01 into v_sal2;
    end if;
  dbms_output.put_line(v_grade||v_sal||v_empno||v_sal2);
  
  end loop;
  close cur_e;
end;

6.使用代码块在控制台打印输出九九乘法口诀表

begin
    for v_i in 1..9 loop
      for v_j in 1..v_i loop
        dbms_output.put(v_j||'*'||v_i||'='||v_i*v_j||' ');
      end loop;
      dbms_output.put_line('');
    end loop;
  end;