Oracle数据中的PL/SQL介绍

什么是Pl/SQL：

• PL/SQL是Procedure Language & Sturtured Query Language的缩写。
• PL/SQL的优点：
  • 有利于客户/服务器环境应用的性能提高。
  • PL/SQL是Oracle系统的核心语言，现在Oracle很多部件是由PL/SQL写成。
  • 使一组语句功能形成模块化程序开发。
  • 使用过程性语言控制程序机构。
  • 可以对程序中的异常进行处理。
  • 集成在数据库中，不推荐把业务逻辑放到数据库里做。
  • 有助于提高程序性能，C/S结构很明显。
  • 是所有数据库扩张SQL的共同目标。
• PL/SQL程序由三个块组成：
  • 申明部分。
  • 执行部分。
  • 异常处理部分。
• PL/SQL基本语法：
  • Declare /*申明部分：在此申明PL/SQL用到的变量，类型及游标，已经局部的存储过程和函数*/
  • Begin/*执行部分：过程及SQL语句，即程序的主要部分*/
  • Exception/*执行异常部分：错误处理*/
  • End;/*结束标志*/

PL/SQL的基本写法：

• 第一个PL/SQL：如果要输出异常内容写法如下，如果要输出Hello Oracle就将i:=‘aa’;去掉。

  --PL/SQL的写法
  declare
    /*申明部分：在此申明PL/SQL用到的变量，类型及游标，以及局部存储过程和函数*/
    str varchar2(20);
    i number;
  begin
    /*执行部分：过程及SQL语句，即程序的主要部分*/
    str :='Hello Oracle';
    i:='aa';
    dbms_output.put_line(str);
  exception
   /*执行异常部分：错误处理*/
   when others then
   dbms_output.put_line('错误');
  end;

  
  
• 赋值符号是:=和常量的定义：

  declare
   str varchar2(10):='Hello ';/*定义时赋值*/
   str2 varchar2(10);
   i constant number:=100;/*定义常量，但是必须在申明时赋值*/
  begin
    
    str2:= 'Oracle';/*在begin中赋值*/
    dbms_output.put_line(str||str2);/*\\是Oracle的连接符*/
    dbms_output.put_line(i);
  exception 
    when others then
      dbms_output.put_line('出错了');
  end;

  
  
  • PL/SQL中的运算符大部分和Java中的一样。
• PL/SQL最大的作用就是处理数据：(一些错误的原因)
  • PL/SQL不能直接将数据查询出来(错误)：出现Select语句中缺少into子句(原因)。
  • 每次查询只能查询一条数据赋值给变量(错误)：出现实际返回的行数超出请求的行数，需要使用游标(原因)。出现错误如下：表要先建好

    declare
     v_stu_id number;
     v_stu_name varchar2(10);
     v_stu_age number;
    begin
     select stu_id,stu_name,stu_age 
      into v_stu_id,v_stu_name,v_stu_age/*这里查询出来要赋值给变量*/
     from tab_stu; 
    end;

    
    
  • 下面就查询一条记录，并输出

    declare
     v_stu_id number;
     v_stu_name varchar2(10);
     v_stu_age number;
    begin
     select stu_id,stu_name,stu_age 
      into v_stu_id,v_stu_name,v_stu_age
     from tab_stu
     where stu_id=1;
     dbms_output.put_line('stu_id:'||v_stu_id);
     dbms_output.put_line('stu_name:'||v_stu_name);
     dbms_output.put_line('stu_age:'||v_stu_age);
     end;

    stu_id:1
    stu_name:张三
    stu_age:21

    
    
  • 插入数据，其他的DML语句也就类似了。

     --插入
     declare
     
     begin
          insert into tab_stu(stu_id,stu_name,stu_age) values(4,'赵六','22');  
          commit;/*记得提交*/
     end;

    
    
  • %type和%rowtype
    • 当不知道要输出表的字段的类型的时候，可以使用%type。来定义变量。如下：

      --%type的使用
      declare
      v_stu_id tab_stu.stu_id%type;
      v_stu_name tab_stu.stu_name%type;
      v_stu_age tab_stu.stu_age%type;
      begin
          select stu_id,stu_name,stu_age
          into v_stu_id,v_stu_name,v_stu_age
          from tab_stu
          where stu_id=1;
          dbms_output.put_line('stu_id:'||v_stu_id);
          dbms_output.put_line('stu_name:'||v_stu_name);
          dbms_output.put_line('stu_age:'||v_stu_age);
      end; 

      
      
    • %rowtype的使用，表示和指定表的一行有相同的数据类型。

      --%rowtype的使用
      
      declare
      v_tab_stu_row tab_stu%rowtype;
      begin
          select stu_id,stu_name,stu_age
          into v_tab_stu_row.stu_id,v_tab_stu_row.stu_name,v_tab_stu_row.stu_age
          from tab_stu
          where stu_id=2;
          dbms_output.put_line('stu_id:'||v_tab_stu_row.stu_id);
          dbms_output.put_line('stu_name:'||v_tab_stu_row.stu_name);
          dbms_output.put_line('stu_age:'||v_tab_stu_row.stu_age); 
      end;
      

      第二种用法

      --%rowtype的使用
      declare 
      v_tab_stu_row tab_stu%rowtype;
      begin
        select * 
        into v_tab_stu_row
        from tab_stu
        where stu_id=3;
        dbms_output.put_line('stu_id:'||v_tab_stu_row.stu_id);
        dbms_output.put_line('stu_name:'||v_tab_stu_row.stu_name);
        dbms_output.put_line('stu_age:'||v_tab_stu_row.stu_age); 
      end;

      
      

PL/SQL的复合数据类型：

• 可变数组varray的使用，可以就看成一个数组：

  --可变数组的使用varray
  declare
   type strings is varray(6) of varchar(10);--这里其实就像定义了一个对象
   var_array strings:=strings('张三','孙八','孙七','赵六','王五','李四');--这里就是这个对象的具体实现，和定义
  begin
    dbms_output.put_line(var_array(1));
    dbms_output.put_line(var_array(2));
    dbms_output.put_line(var_array(3));
    dbms_output.put_line(var_array(4));
    dbms_output.put_line(var_array(5));
    dbms_output.put_line(var_array(6));
  end;

  输出

  张三
  孙八
  孙七
  赵六
  王五
  李四

  
  
• table复合类型的使用：

  --可变数据类型table
  declare 
    type tableStrings is table of varchar2(10)
    index by binary_integer;--定义table类型的下标是二进制类型的无限增长的
    var_table tableStrings;--为定义好的类型，定义一个变量
  begin
    var_table(1):='Hello';--为变量的索引赋值
    var_table(999):='Word';--为变量的索引赋值
    dbms_output.put_line(var_table(1)||' '||var_table(999));
  end;

  输出：Hello Word
• table复合类型的使用2：

  --可变数据类型table的使用2
  declare
   type table_row is table of tab_stu%rowtype
   index by binary_integer;
   var_tab_stu table_row;
  begin
    select stu_id,stu_name 
    into var_tab_stu(100).stu_id,var_tab_stu(100).stu_name
    from tab_stu
    where stu_id=2;
    dbms_output.put_line(var_tab_stu(100).stu_id ||' '|| var_tab_stu(100).stu_name);
  end;

  输出：2 李四
• 其实table和可变数组都可以理解为Java中的数组，只不过table是没有限定长度的数组，而可变数组在定义的时候定义了长度。所以能用可变数组的地方一定能用table变量来处理。
• 还有一个数据类型:Record,可以理解为Java中的集合对象。具体使用如下：

  --record理解为Java中的集合
  declare
   type v_record is record(
        re_id number,--可以是普通变量
        re_name tab_stu.stu_name%type,--可以是表中的一个字段的变量
        r_tab_stu tab_stu%rowtype--也可以是表中的一行
   );
   v_r v_record;--需要赋给变量在使用时
  begin
    select stu_id,stu_name,stu_age
    into v_r.re_id,
    v_r.re_name,
    v_r.r_tab_stu.stu_age
    from tab_stu
    where stu_id=1;
    dbms_output.put_line(v_r.re_id);
    dbms_output.put_line(v_r.re_name);
    dbms_output.put_line(v_r.r_tab_stu.stu_age);
  end;