plsql_day02

     9.2.3 while 循环 
    while  循环条件  loop
        /* 循环代码 */ 
    end loop;
    
    使用while 循环 输出1到10 
    declare
        var_i    number:=1;
    begin
        while  var_i < 11 loop
            dbms_output.put_line(var_i);
            var_i:=var_i+1;
        end loop;
    end;
    /
    
    declare
        var_i    number:=1;
    begin
        while  var_i < 11 loop
            dbms_output.put_line(var_i);
            exit  when  var_i=5;
            var_i:=var_i+1;
        end loop;
    end;
    /
    
    declare
        var_i    number:=1;
    begin
        while  var_i < 11 loop
            dbms_output.put_line(var_i);
            if  var_i=5 then
                var_i:=11; 
            end if;
            var_i:=var_i+1;
        end loop;
    end;
    /
    
    
    
    
   9.2.4 for 循环 (智能循环)
   for  var_i in a..b  loop
      /* 不允许人为的改变 */
   end loop; 
   
   使用for循环输出1到10 
   begin 
       for var_i  in 1..10 loop
           dbms_output.put_line(var_i);
       end loop;
   end;
   /
    
   begin 
       for var_i  in 1..10 loop
           dbms_output.put_line(var_i);
           exit  when  var_i=5;
       end loop;
   end;
   /  
   
   begin 
       for var_i  in 1..10 loop
           dbms_output.put_line(var_i);
           if  var_i=5  then
               /* var_i 不允许修改 */
               var_i:=11;
           end if;
       end loop;
   end;
   /  
   
   
   begin 
       for var_i  in reverse 1..10 loop
           dbms_output.put_line(var_i);
       end loop;
   end;
   /
   
  9.2.5 循环嵌套 
  declare
      var_x  number:=1;
      var_y  number:=1;
  begin
      while  var_x < 4 loop
          var_y:=1; 
          while var_y < 4 loop
              dbms_output.put_line(var_y);
              if var_y=2 then 
                 exit;
              end if;
              var_y:=var_y+1;
          end loop;
          dbms_output.put_line('var_x='
          ||var_x);
          var_x:=var_x+1; 
      end loop;
  end;
  / 
  
  
  declare
      var_x  number:=1;
      var_y  number:=1;
  begin
      while  var_x < 4 loop
          var_y:=1; 
          while var_y < 4 loop
              dbms_output.put_line(var_y);
              if var_y=2 then 
                 var_x:=4;
                 exit;
              end if;
              var_y:=var_y+1;
          end loop;
          dbms_output.put_line('var_x='
          ||var_x);
          var_x:=var_x+1; 
      end loop;
  end;
  /  
  
  9.3 goto 语句
  9.3.1 语法
  <<标签名>>
  goto   标签名;
  9.3.2  输出1到10 程序
  declare
      var_i   number:=1;
  begin
      <<myloop>>
      if var_i < 11 then 
          dbms_output.put_line(var_i);
          var_i:=var_i+1;
          goto  myloop;
      end if;
  end;
  /     
  
  
  begin
      <<outerloop>>
      for  var_x in 1..3 loop
          for var_y in 1..3 loop
              dbms_output.put_line(var_y);
              if var_y=2 then
                  exit outerloop;
              end if;
          end loop;
      end loop;
  end;
  /
  
  begin
      for  var_x in 1..3 loop
          for var_y in 1..3 loop
              dbms_output.put_line(var_y);
              if var_y=2 then
                  goto outerloop;
              end if;
          end loop;
      end loop;
      <<outerloop>>
      NULL;
  end;
  /
  
  declare
      var_x  number:=1;
      var_y  number:=1;
  begin
      while  var_x < 4 loop
          var_y:=1; 
          while var_y < 4 loop
              dbms_output.put_line(var_y);
              if var_y=2 then 
                  goto  abc;
              end if;
              var_y:=var_y+1;
          end loop;
          dbms_output.put_line('var_x='
          ||var_x);
          var_x:=var_x+1; 
      end loop;
      <<abc>>
      NULL;
  end;
  / 
  
 十.plsql 中如何使用sql语句
   10.1  select 语句 需要结合 into 关键字
       才能在plsql中使用。
   10.2  DML(insert delete update)
         TCL(commit rollback savepoint )
       可以直接在plsql中使用。
   10.3  DDL(create  drop   alter)
       不能直接在plsql中使用 需要使用
       动态sql。
 十一.动态sql
    11.1 概念
    在程序运行的过程中 sql语句可以根据
    条件发生改变的哪称之为动态sql。
    11.2 如何做到?
    只要把sql语句 变成字符串
    把一个sql语句对应的字符串当sql语句来
    执行。
    execute  immediate  sqlstr;  
    declare
        sqlstr  varchar2(100);
    begin                      
        sqlstr:=
        'create table testdsql(id number)';
        sqlstr:=substr(sqlstr,1,
        length(sqlstr)-1);
        sqlstr:=sqlstr||
        ', name varchar2(30))';
        dbms_output.put_line(sqlstr);
        execute immediate sqlstr; 
    end;
    /
   11.3 DML 能不能是动态sql
   sqlstr:='insert  into testdsql 
   values(100,''test100'')';
   execute  immediate sqlstr;
   
   declare
       id   number:=100;
       name varchar2(30):='test100';
       sqlstr  varchar2(100);
   begin
       sqlstr:='insert into testdsql 
       values('||id||','''||name||''')';
       dbms_output.put_line(sqlstr);
   end;
   /
   11.4 可以使用占位符 简化字符串的拼接
   declare
       id   number:=100;
       name varchar2(30):='test100';
       sqlstr  varchar2(100);
   begin
       sqlstr:='insert into testdsql 
       values(:b0,:b1)';
       dbms_output.put_line(sqlstr);
       execute  immediate sqlstr 
           using id,name;
       commit;    
   end;
   /
   
   11.5 select 语句能不能是动态sql
     select语句不能带into
     能且只能返回一个结果 
     'select first_name from s_emp 
       where id=1'
     
     declare
         var_name varchar2(30);
         sqlstr   varchar2(100);
     begin
         sqlstr:='select first_name from 
         s_emp  where id=1';
         execute immediate sqlstr 
             into var_name;
         dbms_output.put_line(var_name);     
     end;
     /
    
 十二.游标  cursor 
  12.1 概念
  用来存储多条数据的一个结果集。
  12.2 游标的使用步骤
      声明游标
      declare   
          cursor 游标名 is  select语句;
      打开游标
          open   游标名;
      提取数据  处理数据
          fetch  游标名  into 变量;
      关闭游标
          close  游标名;                 
  12.3 举例 
      把s_emp 表中所有的id 大于10的信息
      放入一个游标中。然后提取游标的前
      两条数据 打印这两条数据 id  
      first_name 
      
      declare
          cursor  empcursor is select *
              from s_emp where id>10;
          var_emp  empcursor%rowtype;    
      begin
          open   empcursor;
          fetch  empcursor into var_emp;
          dbms_output.put_line(var_emp.id
          ||':'||var_emp.first_name);
          fetch  empcursor into var_emp;
          dbms_output.put_line(var_emp.id
          ||':'||var_emp.first_name);
          close  empcursor;
      end;
      /
   12.4 如何提取游标中所有的数据   
      declare
          cursor  empcursor is select *
              from s_emp where id>10;
          var_emp  empcursor%rowtype;    
      begin
          open   empcursor;
          loop
          fetch  empcursor into var_emp;
          /* 满足一个条件 终止循环 */
          dbms_output.put_line(var_emp.id
          ||':'||var_emp.first_name);
          end  loop;
          close  empcursor;
      end;
      /     
   12.5 游标的属性 
   游标名%found  在提取游标数据时 如果提取
       到新数据 则这个属性返回真。如果没有
       提取到新数据则返回假。
       游标必须处于打开状态  否则这个属性
       使用时返回非法游标。打开游标不提取
       则这个属性返回NULL值。    
   游标名%notfound 在提取游标数据时,如果
       提取到新数据 则这个属性返回假。如果
       没有提取到新数据就返回真。
       游标必须处于打开状态  否则这个属性
       使用时返回非法游标。打开游标不提取
       则这个属性返回NULL值。
   游标名%isopen      
       游标是否处于打开状态
       游标打开就返回真 游标关闭返回假
       打开的游标不能再打开
       关闭的游标不能再关闭
   游标名%rowcount 游标指针偏移量
       (了解)    
       
   12.6 使用 简单循环 结合notfound 属性
      遍历上面的游标。    
      declare
          cursor  empcursor is select *
              from s_emp where id>10;
          var_emp  empcursor%rowtype;    
      begin
          open   empcursor;
          loop
          fetch  empcursor into var_emp;
          /* 满足一个条件 终止循环 */
          exit  when  empcursor%notfound;
          dbms_output.put_line(var_emp.id
          ||':'||var_emp.first_name);
          end  loop;
          close  empcursor;
      end;
      /          
   12.7 结合found属性 和 while循环
      遍历上面的游标。   
      declare
          cursor  empcursor is select *
              from s_emp where id>10;
          var_emp  empcursor%rowtype;    
      begin
          open   empcursor;
          fetch  empcursor into var_emp;
          while  empcursor%found loop
          dbms_output.put_line(var_emp.id
          ||':'||var_emp.first_name);
          fetch  empcursor into var_emp;
          end  loop;
          close  empcursor;
      end;
      /     
   12.8  for 循环来遍历游标 
      (智能循环 )
      智能循环 会自动定义变量
      自动打开游标 提取数据 和 关闭游标     
      declare
          cursor  empcursor is select *
              from s_emp where id>10;    
      begin
          for var_emp in empcursor loop
          dbms_output.put_line(var_emp.id
          ||':'||var_emp.first_name);
          end loop;
      end;
      /      
   12.9 带参游标 
      一个游标中可以设计参数 并且在游标
      对应的select语句中可以使用这些参数。
      plsql中的参数 不能加任何长度修饰。
      但可以是 %type。
     declare
         cursor  empcursor(var_id number)
             is select * from s_emp  
             where id>var_id;
         var_emp  empcursor%rowtype;    
     begin
         open  empcursor(15);
         loop
             fetch empcursor into var_emp;
             exit  when empcursor%notfound;
             dbms_output.put_line(var_emp.id
             ||':'||var_emp.first_name);
         end loop;
         close  empcursor;  
     end;
     /       
         
     declare
         cursor  empcursor(var_id number)
             is select * from s_emp  
             where id>var_id;   
     begin
         for var_emp in empcursor(20) loop
             dbms_output.put_line(var_emp.id
             ||':'||var_emp.first_name);
         end loop;    
     end;
     / 
     
     12.10 参考游标 
     游标对应的查询语句 是一个查询字符串
     定义一个参考游标类型
         type  参考游标类型 is ref cursor;
     使用参考游标类型定义游标变量
         游标变量名  参考游标类型;
     打开游标时关联到对应的字符串
         open  游标变量 for 字符串;
     'select * from s_emp where id>:b0'
     
     declare
         type  myrefcursor is ref cursor;
         empcursor  myrefcursor;
         sqlstr  varchar2(100):='select * 
         from s_emp where id>:b0';
         var_emp  s_emp%rowtype;
     begin
         open empcursor for sqlstr 
             using 20;
         loop
             fetch empcursor into var_emp;
             exit when empcursor%notfound;
             dbms_output.put_line(var_emp.id
             ||':'||var_emp.first_name);
         end loop;
         close empcursor;     
     end;
     /
     
 十三.异常 
      exception 
       
      编译时
      运行时:
      13.1 默认的plsql程序的错误处理         
      declare
          var_name  s_emp.first_name%type;  
      begin
          select first_name into var_name
              from s_emp where id>1;
          dbms_output.put_line(var_name);    
      end;
      /
      13.2 让程序进入exception区进行处理 
      declare
          var_name  s_emp.first_name%type;  
      begin
          select first_name into var_name
              from s_emp where id>1;
          dbms_output.put_line(var_name);
      exception
          when  no_data_found   then
          dbms_output.put_line(
          'not found emp');
          when  too_many_rows   then
          dbms_output.put_line(
          'too many emp '||SQLCODE||'@'
          ||SQLERRM);
          when  others  then
          dbms_output.put_line
          ('have exception');         
      end;
      /
     13.3 用户自定义异常 
       定义异常 
           异常变量名  exception;
           too_many_emp  exception;
       根据条件抛出异常
           raise  too_many_emp; 
       捕获异常
           exception
               when  too_many_emp then          
       针对异常做处理    
      
       declare
           too_many_emp   exception;
       begin
           if 1=1  then
               raise  too_many_emp;
           end if;
           dbms_output.put_line
           ('app run success!');
       exception
           when too_many_emp  then
           dbms_output.put_line
           ('too many emp');
           dbms_output.put_line
           ('app continue!');        
       end;
       /     
 十四.过程   procedure
     14.1 概念 
     把一组相关的sql语句 和 plsql语句组织
     到一起然后为这个逻辑结构起一个逻辑名
     这个逻辑名就是过程。
     14.2 语法
     create  or  replace procedure 
       过程名(参数名 参数类型,参数名 
       参数类型)
     is
     begin
     end;
     /
     写一个匿名块 定义两个整数变量
     赋值 从这个变量中选出最大值。 
     declare
         var_x   number:=100;
         var_y   number:=200;
     begin
         if var_x < var_y then
             dbms_output.put_line(var_y);
         else
             dbms_output.put_line(var_x);
         end if;   
     end;
     /    
     drop function  progetmax;        
     create  or replace procedure 
     progetmax(var_x number,var_y  number)    
     is
     begin
         if var_x < var_y then
             dbms_output.put_line(var_y);
         else
             dbms_output.put_line(var_x);
         end if;   
     end;
     / 
   14.3 调用存储过程
   declare
       var_x  number:=100;
       var_y  number:=200;
   begin
       progetmax(var_x,var_y);
       progetmax(1,9527);
   end;
   /                   
   14.4 查看存储过程
   desc   过程名;
   desc   progetmax;  
   SQL> desc   progetmax;
  PROCEDURE progetmax
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 VAR_X                          NUMBER                  IN
 VAR_Y                          NUMBER                  IN
 
 参数名  
 参数的类型 
 参数的模式 in  默认的 
              负责给存储过程传入值 
            out 负责给存储过程传出值
              (相当于指针类型的参数)
            in out 既负责传入又负责传出
 参数的缺省值 或者默认值 
 create  or replace procedure 
     progetmax(var_x number,
       var_y  number:=1)    
     is
     begin
         if var_x < var_y then
             dbms_output.put_line(var_y);
         else
             dbms_output.put_line(var_x);
         end if;   
     end;
     /  
 查看存储过程的源代码
    select  text from  user_source 
        where name='PROGETMAX'; 
        
  14.5 参数的模式 
            in  默认的 
              负责给存储过程传入值 
            out 负责给存储过程传出值
              (相当于指针类型的参数)
            in out 既负责传入又负责传出
            带有out修饰的参数必须是变量。
    设计一个存储过程 传入三个整数参数
    负责把前两个参数的最大值打印,再把
    前两个参数的和存入第三个参数中。
    create  or replace  procedure
        getMaxAndSum(var_x number,
        var_y in number,var_z out number)
    is
    begin
        if  var_x < var_y  then
            dbms_output.put_line(var_y);
        else
            dbms_output.put_line(var_x);
        end if;    
        var_z:=var_x+var_y;
    end;
    /
    
    declare
        var_x   number:=1;
        var_y   number:=2;
        var_z   number:=0;
    begin
        getMaxAndSum(var_x,var_y,var_z);
        dbms_output.put_line(var_z);
        getMaxAndSum(1,100,var_z);
        dbms_output.put_line(var_z);
    end;
    / 
   设计一个存储过程 传入二个整数参数
    负责把前两个参数的最大值打印,再把
    前两个参数的和存入第二个参数中。
    create  or replace  procedure
        getMaxAndSum(var_x number,
        var_y in out number)
    is
    begin
        if  var_x < var_y  then
            dbms_output.put_line(var_y);
        else
            dbms_output.put_line(var_x);
        end if;    
        var_y:=var_x+var_y;
    end;
    /  
    
    declare
        var_x   number:=123;
        var_y   number:=1;
    begin
        getMaxAndSum(var_x,var_y);
        dbms_output.put_line(var_y);     
    end;
    /   
   
    /* 参数的位置赋值 */
    declare
        var_y   number:=1;
    begin
        getMaxAndSum(123,var_y);
        dbms_output.put_line(var_y);     
    end;
    /
    /* 参数的名字赋值 */
    declare
        var_y   number:=1;
    begin
        getMaxAndSum(var_y=>var_y,
            var_x=>123);
        dbms_output.put_line(var_y);     
    end;
    /    
    
   14.6  n 
    1+2+3+...+n 
    设计一个存储过程 传入一个参数代表从
    1加到的数  把这个数对应的前n项的和
    存入第二个参数中。调用这个存储过程
    验证存储过程的功能。