Oracle存储过程详解(三)-嵌套

1. 嵌套块

-- Created on 2016/4/22 by ADMINISTRATOR 
declare 
  p_dname varchar2(50) := '微软';
  p_deptno number := 60;
  p_deptloc varchar2(100) := '米国';
  v_state varchar2(50);--状态
begin

    --内部嵌套块
  <<查询是否存在>>
  begin
    select dname into v_state from dept where deptno = p_deptno;
    dbms_output.put_line('部门名:' || v_state) ;
  end;

  --内部嵌套块（可访问外部变量，但外部不能访问内部变量）
  <<更新部门信息>>
  declare

  BEGIN
    UPDATE DEPT SET DNAME = p_dname,LOC = p_deptloc WHERE DEPTNO = p_deptno ;
    dbms_output.put_line('更新部门信息成功') ;
  END;

  --处理异常
  EXCEPTION
    WHEN NO_DATA_FOUND  THEN
      dbms_output.put_line('未找到部门，新建...:') ;
      --内部嵌套块
      <<插入一个新部门>>
      declare
      begin
        insert into dept(deptno,dname,loc) values(p_deptno,p_dname,p_deptloc);
        dbms_output.put_line('新建部门成功') ;

        exception
          when others then
            dbms_output.put_line('此过程彻底失败') ;
      end;

  --rollback;

END;

2. 循环

declare 
   v_number1 NUMBER;
   v_number2 NUMBER;
BEGIN
  --进行外部循环
  FOR v_number1 in 1 .. 9
    LOOP
      --进行内部循环
      FOR v_number2 in 1 .. v_number1
        LOOP
          --打印九九乘法口决表
          DBMS_OUTPUT.put(v_number1 
                              || '*'
                              ||v_number2
                              ||'='
                              ||v_number1*v_number2
                              ||'   ');
         END LOOP;
         DBMS_OUTPUT.put_line('');
   END LOOP;
  DBMS_OUTPUT.put_line('=====完成=========');
END;

结果：

1*1=1   
2*1=2   2*2=4   
3*1=3   3*2=6   3*3=9   
4*1=4   4*2=8   4*3=12   4*4=16   
5*1=5   5*2=10   5*3=15   5*4=20   5*5=25   
6*1=6   6*2=12   6*3=18   6*4=24   6*5=30   6*6=36   
7*1=7   7*2=14   7*3=21   7*4=28   7*5=35   7*6=42   7*7=49   
8*1=8   8*2=16   8*3=24   8*4=32   8*5=40   8*6=48   8*7=56   8*8=64   
9*1=9   9*2=18   9*3=27   9*4=36   9*5=45   9*6=54   9*7=63   9*8=72   9*9=81   
=====完成=========

3. 动态SQL

declare 
   sqlStr varchar2(200);
   v_empno number := 7902;
BEGIN

  <<一条普通查询>>
  begin
     --定义SQL语句
     sqlStr := 'select * from emp';
     --执行上面这名SQL
     execute immediate sqlStr;
     DBMS_OUTPUT.put_line('第一条成功!');

     exception
       when others then
         null;
  end;

  <<传值和返回值的查询>>
  declare
     v_ename varchar2(50);
     v_job varchar2(50);
     v_sal number;
  begin
     --定义SQL语句
     sqlStr := 'select ename,job,sal from emp where empno = :tempId ';
     --执行上面这名SQL，并传值我取返回值
     execute immediate sqlStr
                       into v_ename,v_job,v_sal
                       USING v_empno;
     DBMS_OUTPUT.put_line('工号:'||v_empno||'名字:'||v_ename||'职位:'||v_job||'工资:'||v_sal);

     exception
       when others then
         null;
  end;


  DBMS_OUTPUT.put_line('=====完成=========');
END;

结果:

第一条成功!
工号:7902名字:FORD职位:ANALYST工资:3000
=====完成=========