Oracle条件控制与循环（小练习）完美解答

1 条件控制

 PL/SQL中关于条件控制的关键字有IF-THEN、IF-THEN-ELSE、IF-THEN-ELSIF和多分枝条件CASE。

IF-THEN IF-THEN-ELSE IF-THEN-ELSIF CASE
IF 条件 THEN
--条件结构体
END;
IF 条件 THEN
--条件成立结构体
ELSE
--条件不成立结构体
END IF; 
IF 条件1 THEN
--条件1成立结构体
ELSIF 条件2 THEN
--条件2成立结构体
ELSE
--以上条件都不成立结构体
END IF;
CASE
WHEN 表达式1 THEN 语句序列1；
WHEN 表达式2 THEN 语句序列2；
……
ELSE 语句序列N；
END CASE;
IF-THEN

该结构先判断一个条件是否为TRUE，条件成立则执行对应的语句块。

①用 IF 关键字开始， END IF 关键字结束，注意 END IF 后面有一个分号。

②条件部分可以不使用括号，但是必须以关键字 THEN 来标识条件结束，如果条件成立，则执行 THEN 后

到对应 END IF 之间的语句块内容。如果条件不成立，则不执行条件语句块的内容。

③条件可以使用关系运算符和逻辑运算符。

④在 PL/SQL 块中可以使用事务控制语句，该 COMMIT 同时也能把 PL/SQL 块外没有提交的数据一并提交，

使用时需要注意。

• IF-THEN-ELSE

把 ELSE 与 IF-THEN 连在一起使用，如果 IF 条件不成立则执行就会执行 ELSE 部分的语句。

• IF-THEN-ELSIF

PL/SQL 中的再次条件判断中使用关键字 ELSIF 。

练习1

例子：查询 JAMES 的工资，如果大于 1500 元，则发放奖金 1000 元，如果工资大于 900 元，               则 发奖金 800 元， 否则发奖金400元。        

SELECT * FROM EMP;
declare
  newsal emp.sal%type;
begin
  select sal into newsal from emp where ename = 'JAMES';
  IF newsal > 1500 then
    update emp set comm = 1000 where ename = 'JAMES';
  elsif newsal > 900 then
    update emp set comm = 800 where ename = 'JAMES';
  ELSE
    UPDATE EMP set comm = 400 WHERE ename = 'JAMES';
  END IF;
end;

练习2

1 、复制 EMP 表的数据给 EMP_1 ，判断 30 号部门的中最高工资的员工，如果 工资大                 于  3000 ，工资加 500，如果工资大于 2500 ，奖金加 1000 ，如果没有奖金，奖金为 1500

 declare
   sal     number;
   comm    number;
   newsal  number;
   newcomm number;
 begin
   select max(sal) into sal from emp2 where deptno = 30;
   select nvl(comm, 0)
     into comm
     from emp2
    where deptno = 30
      and sal = (select max(sal) from emp2 where deptno = 30);
   newsal := sal;
   if sal > 3000 and comm <> 0 then
     newsal := sal + 500;
   elsif sal > 2500 and comm <> 0 then
     newcomm := comm + 1000;
   elsif comm = 0 then
     newcomm := 1500;
   end if;
   update emp2
      set sal = newsal, comm = newcomm
    where deptno = 30
      and sal = sal;
 end;

2、EMP_1 表中 10 部门，工资最低的员工入职时间的月份有 30 天的，工资奖金加 500 ，入职         时间的月份大 于30 天的，工资加 1000 ，奖金为 1000 ，否则 工资加 2000

SELECT HIREDATE, SAL, COMM
  FROM EMP
 WHERE DEPTNO = 10
   AND SAL = (SELECT MIN(SAL) FROM EMP WHERE DEPTNO = 10)

1982/1/23	1300.00	NVLL


SELECT HIREDATE ,TO_NUMBER(TO_CHAR(LAST_DAY(HIREDATE), 'DD')) FROM EMP_D;


DECLARE
  V_NUM      NUMBER;
  V_SAL      NUMBER;
  V_COMM     NUMBER;
  V_SAL_NEW  NUMBER;
  V_COMM_NEW NUMBER;

BEGIN
  SELECT TO_NUMBER(TO_CHAR(LAST_DAY(HIREDATE), 'DD')), SAL, NVL(COMM, 0)
    INTO V_NUM, V_SAL, V_COMM
    FROM EMP_D
   WHERE DEPTNO = 10
     AND SAL = (SELECT MIN(SAL) FROM EMP WHERE DEPTNO = 10);

  IF V_NUM >= 30 THEN
    V_SAL_NEW := (V_SAL + 500) , V_COMM_NEW := (V_COMM + 500);
  ELSIF V_NUM > 30 THEN
    V_SAL_NEW := (V_SAL + 1000) , V_COMM_NEW := 1000;
  ELSE
    V_SAL_NEW := (V_SAL + 2000) , V_COMM_NEW := V_COMM;
  END IF;

  UPDATE EMP_D
     SET SAL = V_SAL_NEW, COMM = V_COMM_NEW
   WHERE DEPTNO = 10
     AND SAL = (SELECT MIN(SAL) FROM EMP WHERE DEPTNO = 10);

END;

2.循环控制

PL/SQL 提供了丰富的循环结构来重复执行一些列语句。 Oracle 提供的循环类型有：

1. 无条件循环 LOOP-END LOOP 语句

2.WHILE 循环语句

3.FOR 循环语句

在上面的三类循环中 EXIT 用来强制结束循环。

LOOP 循环：

LOOP 循环是最简单的循环，也称为无限循环， LOOP 和 END LOOP 是关键字。

语法格式： LOOP 循环

LOOP --循环体 END LOOP；

语法格式：

1. 循环体在 LOOP 和 END LOOP 之间，在每个 LOOP 循环体中，首先执行循环体中的语句序列，执行完后

再重新开始执行。

2. 在 LOOP 循环中可以使用 EXIT 或者 EXIT WHEN 条件 的形式终止循环。否则该循环就是死循环。

例子 1 ：执行 1+2+3+…+100 的值

declare 
  v_num number:=0;
  v_sum number:=0;
begin
  loop
    v_num :=v_num+1;
    v_sum :=v_sum+v_num;
    exit when v_num > 100;
    dbms_output.put_line(v_num);

    dbms_output.put_line(v_sum);
  end loop;
  exception when others then
  dbms_output.put_line(sqlerrm)
end;
   

例子2：执行1..100的偶数项的和

---- LOOP 循环

declare
  v_num number := 0;
  v_sum number := 0;
begin
  loop
    v_num := v_num + 1;
    if mod(v_num, 2) = 0 then
      v_sum := v_sum + v_num;
    end if;
    exit when v_num > 101;
    dbms_output.put_line(v_num);
    dbms_output.put_line(v_sum);
  end loop;
exception
  when others then
DBMS_OUTPUT.PUT_LINE(SQLERRM);
end;

---- WHILE 循环

declare 
v_num number := 0;
v_sum number := 0;
begin
  while(v_num<=100) loop
  v_num := v_num + 1;
  if mod(v_num,2)=0 then
  v_sum := v_sum + v_num;
  end if;
  dbms_output.put_line(v_sum);
  dbms_output.put_line(v_num);
  end loop;
  exception when others then
    dbms_output.put_line(sqlerrm);
end;

---- FOR 循环

FOR 循环自带一个变量（可以不用声明） 

declare 
  v_sum number:=0;
begin
   for x in 1..100 loop
     if mod(x,2)=0 then
       v_sum := v_sum + x;
     end if;
     dbms_output.put_line(v_sum);
     dbms_output.put_line(x);

   end loop;
     exception when others then
       dbms_output.put_line(sqlerrm);
end;

幕一下(练习题)

1、打印出emp表中员工编号是7844的姓名、工作、部门名称 和 工资

select * from emp;

--打印
declare
 v_ename varchar2(30);
 v_job varchar2(30);
 v_dname varchar2(30);
 v_sal number;
begin
  select e.ename, e.job, d.dname, e.sal into v_ename,v_job,v_dname,v_sal
  from emp e
 inner join dept d
    on e.deptno = d.deptno
 where empno = 7844;
  dbms_output.put_line
  ('这个人的信息是：'||v_ename||'-'||v_job||'-'||v_dname||'-'||v_sal);
end;

--查询sqld语句
select e.ename, e.job, d.dname, e.sal
  from emp e
 inner join dept d
    on e.deptno = d.deptno
 where empno = 7844;

 2、打印输出30号部门的工资最高的员工姓名 以及员工编号，入职时间，部门名称

--打印
 declare
 v_ename varchar2(90);
 v_empno number;
 v_hirdate date;
 v_dname varchar2(90);
 begin
   select e.ename,e.empno,e.hiredate,d.dname into v_ename,v_empno,v_hirdate,v_dname
   from emp e
   inner join dept d
   on e.deptno = d.deptno 
   where e.deptno = 30 and e.sal = (select max(sal)from emp where deptno = 30);
 dbms_output.put_line('30号部门的工资最高的员工信息：'||v_ename||'-'||v_empno||'-'
 ||(TO_CHAR(v_hirdate,'YYYY-MM-DD'))||'-'||v_dname);
 
 end;

--sql语句
select e.ename,e.empno,e.hiredate,d.dname
 from emp e
 inner join dept d
 on e.deptno = d.deptno 
 where e.deptno = 30 and e.sal = (select max(sal)from emp where deptno = 30);