PL/SQL:控制程序流

-------------------控制程序流1--------------------
declare
  v_sal number(7,2);
  begin
    select sal into v_sal from emp
     where lower(ename)=lower('&name');--SMITH
  if v_sal<2000 then
    update emp
       set sal=v_sal+200
     where lower(ename)=lower('&name');--SMITH
  end if;
  end;

SELECT * FROM EMP;

declare 
  v_comm number(7,2);
  begin
    select NVL(comm,0) into v_comm from emp
     where empno=&emp_no;--7369
  if v_comm=0 then
    dbms_output.put_line('该员工没有奖金');
  else 
    dbms_output.put_line('员工奖金为: ' || v_comm);
  end if;
  end;

declare
  v_comm number(7,2);
  begin 
    select NVL(comm,0) into v_comm from emp
     where empno=&emp_no;--7369
  if v_comm<>0 then
    update emp
       set comm=200
     where empno=&emp_no;
  end if;
  end;
  
declare 
  v_comm number(7,2);
  begin 
    select NVL(comm,0) into v_comm from emp
     where empno=&emp_no;
  if v_comm<>0 then
    update emp 
       set comm=v_comm+100
     where empno=&emp_no;
   else
    update emp
       set comm=200
     where empno=&emp_no;
  end if;
  end;
--if-elsif-else-end if
declare
  v_job varchar2(12);
  v_sal number(7,2);
  begin
   select job,sal into v_job,v_sal from emp
   where empno=&emp_no;
   if v_job='PRESIDENT' then
    update emp
    set sal=v_sal+1000
    where empno=&emp_no;
   elsif v_job='MANAGER' then
    update emp
    set sal=v_sal+500
    where empno=&emp_no;
   else
    update  emp
    set sal=v_sal+200
    where empno=&emp_no;
   end if;
 end;
--case-when
declare
    v_deptno  emp.deptno%type;
  begin
    v_deptno:=&dept_no;
   case v_deptno
    when 10 then
    update  emp
    set comm=100
    where deptno=v_deptno;
   when 20 then
     update emp
      set comm=80
     where deptno=v_deptno;
   when 30 then
     update emp
       set comm=50
      where deptno=v_deptno;
   else
     dbms_output.put_line('不存在该部门');
   end case;
 end;

declare
   v_sal emp.sal%type;
   v_ename emp.ename%type;
  begin
    select ename,sal into v_ename,v_sal from emp
    where empno=&emp_no;
   case
      when v_sal<1000 then
       update emp
        set comm=100
        where ename=v_ename;
      when v_sal<2000 then
        update emp
        set comm=80
        where ename=v_ename;
      when v_sal<6000 then
        update emp
         set comm=50
        where ename=v_ename;
       end case;
  end;
-------------------控制程序流2--------------------
create table tempjxh (colA int);
--LOOP   -   无条件循环
declare
  i int:=1;
  begin 
  loop 
  insert into tempjxh values(i);
  exit when i=10;
  i:=i+1;
  end loop;
  end;
  
select * from tempjxh;
delete tempjxh;
--WHILE  -  根据条件循环
declare 
 i int:=1;
 begin 
 while i<=10 loop
 insert into tempjxh values(i);
 i:=i+1;
 end loop;
 end;
select * from tempjxh;

declare 
  num  number;
  temp number;
  rem  number:=0;
  begin 
  num:=&nu;--1234
  while num>0 loop
   temp:=mod(num,10);
   rem:=(rem*10)+temp;
   num:=trunc(num/10);
  end loop;
  dbms_output.put_line('反转后数字为: '|| num);
  end;
--FOR  -  循环固定的次数
declare 
  result int;
  begin 
    for i in 1..5 loop
      for j in 1..5 loop
        result:=i*j;
       exit when result=30;
       exit when result=30;
      end loop;
     dbms_output.put_line(result);
    end loop;
   end;
   
--可以使用循环标号命名循环，然后通过exit和end loop 语句中的名字引用指定的循环 爬
     begin
         <<a1>>
         for i in 1..10 loop
           dbms_output.put_line('*');
        <<a2>>
        for j in 1..i loop
       dbms_output.put('&'||' ');
       end loop a2;
      end loop a1;
     dbms_output.put_line('*');
      end;
      
     begin
         <<a1>>
         for i in 1..10 loop
           dbms_output.put_line('爬');
        <<a2>>
        for j in 1..i loop
       dbms_output.put('爬');
       end loop a2;
      end loop a1;
     dbms_output.put_line('爬');
      end;
--乘法口诀      
declare
  i int;
  j int;
begin
  i := 1;
  j := 1;
  while i < 10 loop
    while j <= i loop
      dbms_output.put(i || '*' || j || '=');
      if length(i * j) = 1 and j != 1 then
        dbms_output.put(' ');
      end if;
      dbms_output.put(i * j || ' ');
      j := j + 1;
    end loop;
    j := 1;
    i := i + 1;
    dbms_output.put_line(' ');
  end loop;
end;

declare
begin
  for i in 1 .. 9 loop
    for j in 1 .. i loop
      dbms_output.put(i || '*' || j || '=');
      if length(i * j) = 1 and j != 1 then
        dbms_output.put(' ');
      end if;
      dbms_output.put(i * j || ' ');
    end loop;
      dbms_output.put_line('');
  end loop;
end;

--跳到指定循环
declare i int:=1;
  begin
     loop
      insert into tempjxh values(i);
      if i=20 then  goto end_loop;
      end if;
      i:=i+1;
      end loop;
    <<end_loop>>
   dbms_output.put_line('循环结束');
  end;
select * from tempjxh;
-------------------控制程序流3--------------------
declare
  emp_name emp.ename%type;
begin
  select ename into emp_name from emp;
EXCEPTION
  when too_many_rows then
    dbms_output.put_line('出错了，不允许返回多行!');
end;

declare
  emp_no   number(4);
  emp_name varchar2(15);
  emp_sal  number(7, 2);
begin
  select empno, ename, sal
    into emp_no, emp_name, emp_sal
    from emp
   where empno = &empno; --8900
  dbms_output.put_line(emp_no || ' ' || emp_name || ' ' || emp_sal);
EXCEPTION
  when no_data_found then
    dbms_output.put_line('职员编号不存在');
end;

declare
  emp_name varchar2(5);
begin
  select ename into emp_name from emp where empno = &emp_no; --7369
  dbms_output.put_line('职员姓名:' || emp_name);
EXCEPTION
  when value_error then
    dbms_output.put_line('要存储在姓名中的值过长');
end;

declare
  sal_error exception;
  emp_comm emp.comm%type;
begin
  select NVL(comm, 0) into emp_comm from emp where empno = &emp_no; --7499
  if emp_comm > 1000 then
    raise sal_error;
  elsif emp_comm < 1000 then
    dbms_output.put_line('奖金:' || emp_comm);
  end if;
EXCEPTION
  when sal_error then
    dbms_output.put_line('奖金超出范围');
end;
select * from emp;

declare
  sal_error exception;
  emp_comm emp.comm%type;
begin
  select NVL(comm, 0) into emp_comm from emp where empno = &emp_no;--7654
  if emp_comm > 1000 then
    raise sal_error;
  end if;
EXCEPTION
  when sal_error then
    update emp set comm = 1000 where empno = &emp_no;
end;

DECLARE
  EMP_NO EMP.EMPNO%TYPE;
  EMP_COMM EMP.COMM%TYPE;
  COMM_EXCEPTION EXCEPTION;
  BEGIN
  EMP_NO:=&EMPN;
  SELECT NVL(COMM, 0) INTO EMP_COMM FROM EMP WHERE EMPNO=EMP_NO;
  IF EMP_COMM=0 THEN
  RAISE COMM_EXCEPTION;
  ELSE 
  dbms_output.put_line('奖金为:' || emp_comm);
  END IF;
  EXCEPTION
  WHEN COMM_EXCEPTION THEN
  raise_application_error(-20001,'奖金为超出限制');
  END;

declare
  J Number(4);
  W Number(4);
  begin 
  J:=1;
  W:=0;
  if J>W then
  dbms_output.put_line('4月再说吧');
  end if;
  end;
   
declare
  J Number(4):=3;
  W Number(4):=0;
  begin 
  while W<=10 loop
  if J>W then
  dbms_output.put_line('憨批');
  elsif W>J then
  dbms_output.put_line('大憨批！');
  end if;
  W :=W+1;
  end loop;
  end;