Day41_PLSQL的使用

一、数据库访问相关的技术
  1.plsql procedure 过程化sql
  2.proc/c++ 使用c或者c++访问oracle数据库
  3.odbc/ado vc中访问数据库的技术
  4.oci  oracle底层提供的连接接口
  5.sqlj/jdbc java访问数据库的技术
二、plsql的特点
  结构化 模块化编程
  良好的可移植性
  良好的可维护性
  提供系统性能
  不便于向异构数据库移植
三、sql语句的特点
  第四代  机器语言  汇编  高级  结构化
  只管做什么  不管怎么做
  没有过程 和 控制语句
  没有算法描述能力
四、plsql扩展了sql
  1.变量和类型
  2.控制语句 和 过程语句
  3.过程和函数
  4.对象类型 和 方法
五、plsql的程序结构
  declare
      /* 申明区 */
  begin
      /* 执行区 执行sql语句或者plsql语句 */
  exception
      /* 异常处理区 程序运行出错之后进行处理的区域 */
  end;
六、plsql的开发环境
  sqlplus 命令行下的开发工具
  psqlsqldeveloper  图形化的开发工具
 
  begin
    dbms_output.put_line('hello plsql');
  end;
  /
  设置输出为打开
  set serveroutput on
七、标识符
  1.作用
    给变量 类型 游标 子程序 函数 等命名的
  2.使用标识符定义变量
    变量名  类型；
    declare
      var_id   number;
      var_name varchar2(30);
    begin
      var_id:=12;
      var_name:='test';
      dbms_output.put_line(var_id||':'||var_name);
    end;
    /
八、变量
  1.变量的类型
    a.标量类型
      number binary_integer  数字类型
      char   varchar2        字符串类型
      date                   日期类型
      boolean                布尔类型
    b.复合类型
      record 类型
      table  类型
    c.参考类型
      ref 类型
    d.大类型 （数据的路径）
      BLOB   0-4g
      CLOB   0-4g
      BFILE
  2.变量的修饰
    constant 类型 not null
    
    declare
      var_id constant number:=100;
      var_name varchar2(30) not null:='t';
    begin
      -- var_id:=101;
       var_name:='test';
      dbms_output.put_line(var_id||':'||var_name);
    end;
    
    任何一个变量 没有赋值 则初始值为NULL
  3.使用binary_integer   boolean 定义变量
    declare
      var_id binary_integer:=100;
      var_bool boolean;
    begin
      var_bool:=true;
      if  var_bool  then
      dbms_output.put_line(var_id);
      end if;
    end;
  4.定义两个变量  分别和s_emp表中id的类型 和 first_name 的类型相同 并且把id=1的数据赋值给这两个变量 打印输出这两个变量的值
    declare
      var_id number(7);
      var_fname varchar2(25);
    begin
      var_id:=1;
      var_fname:='Carmen';
      dbms_output.put_line(var_id||':'||var_fname);
    end;
    
    使用表名.字段名%type 取得表的字段对应的类型
    使用sql语句对变量进行赋值
    declare
      var_id s_emp.id%type;
      var_fname s_emp.first_name%type;
    begin
      select id,first_name into var_id,var_fname from s_emp where id=1;
      dbms_output.put_line(var_id||':'||var_fname);
    end;
  5.record 类型   相当于c语言结构类型
    a.如何定义record类型
      type 记录类型名 is record(
          字段名   类型，
          字段名   类型，
          字段名   类型
      );
    b.定义一个record类型 有三个字段 分别是id firest_name salary 和 s_emp表中对应的字段类型相同 把id=2的数据赋值给这个类型的变量
      declare
        /* 定义record类型 */
        type emptype is record(
            id s_emp.id%type,
            first_name s_emp.first_name%type,
            salary s_emp.salary%type
        );
        /* 使用record类型定义变量 */
        var_emp emptype;
        var_emp1 emptype;
      begin
        /* 如何id=2的数据赋值给var_emp */
        select id,first_name,salary into var_emp from s_emp where id=1;
        -- var_emp1 := var_emp;
        var_emp1.first_name:=var_emp.first_name;
        dbms_output.put_line(var_emp.id||':'||var_emp.first_name||':'||var_emp.salary);
      end;
    当查询的字段数少于 记录类型的字段数
  6.思考：如何定义一个记录类型的变量用来接收id=1的数据？
    取的表的一行 对应的类型 表名 %rowtype
     s_emp%rowtype
     s_dept%rowtype
     s_order%rowtype
     %rowtype 得到的是 字段的名字和字段顺序 完全和表头相同的记录类型
   
    declare
      var_emp  s_emp%rowtype;
    begin
      select * into var_emp from s_emp where id=1;
      dbms_output.put_line(var_emp.id||':'||var_emp.salary);
    end;
-------------------------------------------------------------------------
    使用rowtype定义一个变量 把s_dept id=50放入这个变量中 并输出这个变量的信息；
    declare
      var_dept  s_dept%rowtype;
    begin
      select * into var_dept from s_dept where id=50;
      dbms_output.put_line(var_dept.id||':'||var_dept.name||':'||var_dept.region_id);
    end;
  7.table 类型 相当于C语言的数组
    a.table  类型的语法
      type   类型名字  is table of 表中的数据类型
      index by binary_integer;
    b.定义一个table 类型  其中的数据是数字类型的
      declare
          /* 定义一个table类型 */
          type numstype is table of number index by binary_integer;
          /* 使用table类型定义变量 */
          var_nums  numstype;
      begin
          /* 使用下表 操作table类型的变量 */
          var_nums(3):=100;
          var_nums(4):=500;
          var_nums(2):=600;
          dbms_output.put_line(var_nums(3));
      end;
    c.下标连续时对table类型变量的访问
      declare
          /* 定义一个table类型 */
          type numstype is table of number
          index by binary_integer;
          /* 使用table类型定义变量 */
          var_nums  numstype;
          /* 使用下标 操作table类型变量 */
          var_ind   binary_integer;
      begin
          /* 使用下标 操作table类型的变量 */
          var_nums(3):=100;
          var_nums(4):=500;
          var_nums(2):=600;
          var_ind:=2;
          dbms_output.put_line(var_nums(var_ind));
          var_ind:=var_ind+1;
          dbms_output.put_line(var_nums(var_ind));
          var_ind:=var_ind+1;
          dbms_output.put_line(var_nums(var_ind));
     end;
---------------------------------------------------------
    d.思考下标不连续时如何对table 类型的变量进行遍历；
      用迭代器思想
      first() 得到第一个元素的下标
      next(n)  根据一个元素的下标得到 下一个元素的下标
      last()   最后一个元素的下标
      declare
          /* 定义一个table类型 */
          type numstype is table of number
          index by binary_integer;
          /* 使用table类型定义变量 */
          var_nums  numstype;
          /* 使用下标 操作table类型变量 */
          var_ind   binary_integer;
      begin
          /* 使用下标 操作table类型的变量 */
          var_nums(3):=100;
          var_nums(4):=500;
          var_nums(2):=600;
          var_ind:=var_nums.first();
          dbms_output.put_line(var_nums(var_ind));
          var_ind:=var_nums.next(var_ind);
          dbms_output.put_line(var_nums(var_ind));
          var_ind:=var_nums.last();
          dbms_output.put_line(var_nums(var_ind));
     end;
    f.要求定义table类型的变量 把s_emp表中 id=1 和 id=3 和 id=11 的所有信息放入这个变量，然后使用迭代器思想遍历数据 打印这些信息；
     declare
        type empstype is table of s_emp%rowtype
        index by binary_integer;
        var_emps  empstype;
        var_id  s_emp.id%type:=1;
        var_ind  binary_integer;
     begin
        select * into var_emps(var_id) from s_emp where id=var_id;
        var_id:=3;
        select * into var_emps(var_id) from s_emp where id=var_id;
        var_id:=11;
        select * into var_emps(var_id) from s_emp where id=var_id;
        var_ind:=var_emps.first();
        dbms_output.put_line(var_emps(var_ind).id||':'||var_emps(var_ind).salary);
        var_ind:=var_emps.next(var_ind);
        dbms_output.put_line(var_emps(var_ind).id||':'||var_emps(var_ind).salary);
        var_ind:=var_emps.next(var_ind);
        dbms_output.put_line(var_emps(var_ind).id||':'||var_emps(var_ind).salary);       
     end;      
  8.变量的作用域和可见性
    plsql 块的嵌套
    declare
      var_m   number:=1;
    begin
      declare
        var_m number:=100;
      begin
        dbms_output.put_line('var_m='||var_m);
      end;
      /* 全局不能访问局部 */
    end;
九、控制语句
  1.分支语句
    if(a>b) then
    ...
    end if;
    -------------------
    if(a>b) then
    ...
    else
    ...
    end if;
    -------------------
    if(a>b) then
    ...
    elsif(a>c) then
    ...
    elsif(a>d) then
    ...
    end if;
    -------------------
    b.举例
    定义三个整数变量 赋值 输出其中的最大值
    declare
      var_a number;
      var_b number;
      var_c number;
    begin
      var_a:=&var_a;
      var_b:=&var_b;
      var_c:=&var_c;
      if var_a<var_b then
        if var_b<var_c then
          dbms_output.put_line(var_c);
        else
          dbms_output.put_line(var_b);
        end if;
      else
        if var_a<var_c then
          dbms_output.put_line(var_c);
        else
          dbms_output.put_line(var_a);
        end if;
      end if;
    end;
    c.NULL 值的运算特点
    declare
      var_a number;
      var_b number;
    begin
      if var_a<var_b then
        dbms_output.put_line(var_b);
      elsif var_a is null and var_b is null then
        dbms_output.put_line(a he b shi kong);
      elsif var_a=var_b then
        dbms_output.put_line(var_b);
      elsif var_a<var_b then
        dbms_output.put_line(var_b);
      end if;
    end;
  2.循环语句
    1.简单循环
      loop
        /* 循环代码 */
      end loop;
    2.结束循环的语法
      exit when 循环结束条件；
      ------------------------
      if 退出条件 then
        exit;
      end if;
    3.从 1 输出到 10
      declare
        var_a number:=1;
      begin
        loop
          dbms_output.put_line(var_a);
          exit when var_a=10;
          var_a:=var_a+1;
        end loop;
      end;
    
    c.使用for循环输出 10 到 1
    begin
      fro var_i in reverse 1..10 loop
        dbms_output.put_line(var_i);
      end loop;
    d.for循环中使用的变量不能被修改
    begin
      for var_i in 1..10 loop
        dbms_output.put_line(var_i);
        --exit when var_i=5;
        if var_i then
          var_i:=11; /* var_i不允许赋值 */
        end if;
      end loop;
    end;
  3.goto 语句
    a.<<标签名>>
      这里必须有语句  NULL也可以
      ...
      goto  标签名；
    b.使用goto 结合标签 做一个输出1到10的效果
      declare
        var_a number:=1;
      begin
        <<abc>>
        if var_a<11 then
          dbms_output.put_line(var_a);
          var_a:=var_a+1;
          goto abc;
        end if;
        dbms_output.put_line('loop over!');
      end;
    c.如何退出多重循环
      begin
        <<outerfor>>
        for x in 1..4 loop
          for y in 1..4 loop
            dbms_output.put_line(y);
            if y=2 then
              exit outerfor;
            end if;
          end loop;
        end loop;
        dbms_output.put_line('loop over!');
      end;
      
      begin        
        for x in 1..4 loop
          for y in 1..4 loop
            dbms_output.put_line(y);
            if y=2 then
              goto outerfor;
            end if;
          end loop;
        end loop;
        <<outerfor>>
        dbms_output.put_line('loop over!');
      end;