PL/SQL 数据库访问的相关技术(2)游标 、异常、存储过程、函数、包、触发器

时间:2021-12-23 22:58:31

用table类型变量保存s_region表中id为1,2,3信息,

并遍历输出(可以使用循环输出)

 

declare

 type regiontable is table of s_region%rowtype

 index by binary_integer;

 var_regions regiontable;

 var_ind binary_integer;

begin

 select * into var_regions(1) from s_region where id=1;

 select * into var_regions(2) from s_region where id=2;

 select * into var_regions(3) from s_region where id=3;

 var_ind:=var_regions.first();

 loop

    dbms_output.put_line(var_regions(var_ind).id||':'||

                          var_regions(var_ind).name);

    exit when var_ind=3;

    var_ind:=var_regions.next(var_ind);

  endloop;

end;

/

 

---------------------------------------------------------

1、动态sql

 1.1 概念

 1.2 举例(ddl)

    ddl语句不能直接在plsql中使用,必须用动态sql实现

 1.3dml的动态sql

    1)dml语句可以直接在plsql中使用

    2)常规的字符串的拼接

    3)带变量的字符串的拼接

    4)用占位符配合using解决字符串的拼接问题

      :标识符   占位符

      execute immediate sqlstr using 变量列表;

 1.4select语句的动态sql

   /*定义两个变量,类型分别和s_emp表中的id,first_name

     相同,使用查询语句查询id=1的员工的id,first_name

     存入变量并输出 */

  declare

    sqlstr varchar2(100);

    var_id s_emp.id%type;

    var_name s_emp.first_name%type;

  begin

    sqlstr:='select id,first_name from s_emp where id=1';

    execute immediate sqlstr into var_id,var_name;

    dbms_output.put_line(var_id||':'||var_name);

  end;

  /   

 

2、游标  cursor

  2.1作用

    处理多行的结果集

  2.2游标的使用步骤

   1)声明游标

     cursor 游标名 is select语句;

   2)打开游标

     open 游标名;

   3)提取数据

     fetch 游标名 into 变量;

   4)关闭游标

     close 游标名;

  2.3把s_emp表中所有数据保存在游标中,遍历输出

   declare

     /* 声明游标 */

     cursor empscursor is select * from s_emp;

     /* 声明变量接收一行数据 */

     var_emp empscursor%rowtype;

   begin

     /* 打开游标 */

     open empscursor;

     /* 提取数据 */

     fetch empscursor into var_emp;

     dbms_output.put_line(var_emp.id||':'

                         ||var_emp.first_name);

     fetch empscursor into var_emp;

     dbms_output.put_line(var_emp.id||':'

                         ||var_emp.first_name);

     /* 关闭游标 */

     close empscursor;

   end;

    /

  2.4如何使用循环遍历游标?-- 循环条件或者退出条件

   使用游标的属性

   游标名%属性

  

  found         在提取数据时,如果提取到了新数据则返回真

                              如果没有提取到新数据则返回假

                 如果没有打开游标,则返回非法游标

                 如果没有fetch,则返回null

  notfound      在提取数据时,如果提取到了新数据则返回假

                              如果没有提取到新数据则返回真

                 如果没有打开游标,则返回非法游标

                 如果没有fetch,则返回null

  isopen        判断游标的状态是否为打开

                 已打开返回真,没有打开返回假

              打开的游标不能再打开

              关闭的游标不能再关闭

  rowcount      游标的偏移量

   

 

  2.5使用简单循环遍历游标

   declare

     /* 声明游标 */

     cursor empscursor is select * from s_emp;

     /* 声明变量接收一行数据 */

     var_emp empscursor%rowtype;

   begin

     /* 打开游标 */

     open empscursor;

     /* 循环提取数据 */

     loop

        fetch empscursor into var_emp;

        exit when empscursor%notfound;

        dbms_output.put_line(var_emp.id||':'

                         ||var_emp.first_name);

     end loop;

     /* 关闭游标 */

     close empscursor;

   end;

    /

 

  2.6使用while循环遍历游标

  declare

     /* 声明游标 */

     cursor empscursor is select * from s_emp;

     /* 声明变量接收一行数据 */

     var_emp empscursor%rowtype;

   begin

     /* 打开游标 */

     open empscursor;

     /* 循环提取数据 */

     fetch empscursor into var_emp;

     while empscursor%found loop

        dbms_output.put_line(var_emp.id||':'

                         ||var_emp.first_name);

        fetch empscursor into var_emp;

     end loop;

     /* 关闭游标 */

     close empscursor;

   end;

    /

 

  2.7使用for循环遍历游标

   for循环:智能循环(自动打开游标、提取数据、关闭游标)

   declare

     cursor empscursor is select * from s_emp;

   begin

     for var_emp in empscursor loop

       dbms_output.put_line(var_emp.id||':'

                         ||var_emp.first_name);

     end loop;

   end;

 

  2.8带参的游标

  select * from s_emp where id>10;

  

  cursor 游标名(参数) is select 语句;

   参数的数据类型:不允许带长度和精度的修饰

                   可以使用表名.字段%type的方式

 

   打开游标的时候传值

  open 游标名(实参);

  

  declare

    cursor empscursor(var_id number)

        is select * from s_emp where id>var_id;

  begin

    for var_emp in empscursor(16) loop

       dbms_output.put_line(var_emp.id||':'||

                            var_emp.first_name||':'||

                             var_emp.salary);

    end loop;

  end;

   /

 

  2.9参考游标 ref cursor

    游标 + 动态sql

    参考游标的使用步骤

   sqlstr:='select * from s_emp';

   1)定义参考游标类型

     type 参考游标类型名 is ref cursor;

   2)声明参考游标类型变量

   3)把参考游标变量和动态sql语句结合

     open 参考游标变量 for sqlstr;

 

   

   

   declare

     /* 定义参考游标类型 */

     type emprefcursor is ref cursor;

     /* 声明参考游标变量 */

     empscursor emprefcursor;

     /* 声明变量接收一行数据 */

     var_emp s_emp%rowtype;

     /* 动态sql字符串 */

     sqlstr varchar2(100);

   begin

     sqlstr:='select * from s_emp';

     /* 参考游标变量和动态sql结合 */

     open empscursor for sqlstr;

     /* 循环提取数据 */

     loop

        fetch empscursor into var_emp;

        exit when empscursor%notfound;

        dbms_output.put_line(var_emp.id||':'||

                             var_emp.first_name||':'||

                              var_emp.salary);

     end loop;

     /* 关闭游标 */

     close empscursor;

   end;

    /

 

 

   /* 动态sql 字符串带占位符的情况 */

   declare

     type emprefcursor is ref cursor;

     var_empscursor emprefcursor;

     var_emp s_emp%rowtype;

     sqlstr varchar2(100);

   begin

     sqlstr:='select * from s_emp where id>:b0';

     /* using后边可以使用变量,也可以使用常量通常是变量 */

     open var_empscursor for sqlstr using 16;

     loop

       fetch var_empscursor into var_emp;

       exit when var_empscursor%notfound;

       dbms_output.put_line(var_emp.id||':'||

                            var_emp.first_name||':'||

                             var_emp.salary);

     end loop;

     close var_empscursor;

   end;

 

 

3plsql中的异常

  3.1异常处理的步骤

    1)定义异常

    2)根据条件 引发异常

    3)捕获异常

    4)处理异常

  3.2异常的分类

    1)系统预定义异常

     oracle定义和自动引发

    2)系统非预定义异常

     自己定义 oracle引发

    3)自定义异常

  3.3常用的系统预定义异常

   cursor_already_open:试图打开已打开的游标

   invalid_cursor:在不合法的游标上操作

   invalid_number:内嵌的sql无法将字符串转换成数字

   dup_val_on_index:在唯一性的字段上有重复值

   no_data_found:在执行select into语句时,未返回行

   too_many_rows:在执行select into语句时,返回超过一行的数据

   zero_divide:除数为0

  3.4案例:预定义异常的使用

   declare

     var_id s_emp.id%type;

     var_name s_emp.first_name%type;

     var_i number:=100;

   begin

     select id,first_name into var_id,var_name

           from s_emp where id<var_i;

     dbms_output.put_line(var_id||':'||var_name);

   exception

     when no_data_found then

        dbms_output.put_line('no employee whose number is '||

                   var_i);

     --when too_many_rows then

        --dbms_output.put_line('too many rows');

     when others then

        dbms_output.put_line('others:'||sqlcode||'###'

                              ||sqlerrm);

   end;

    /

        

  3.5非预定义异常的处理语法

   declare

      异常名称 exception;

      pragma exception_init(异常名称,错误编号);

   begin

 

   exception

      when 异常名称 then

         异常处理

   end;

    /

  3.6自定义异常

   raise 引发异常

   

   declare

     /* 定义异常 */

     too_many_emps exception;

     var_num number;

   begin

     select count(id) into var_num from s_emp

          where salary>1500;

     /* 根据条件引发异常 */

     if var_num>2 then

        raise too_many_emps;

     end if;

   exception

     when too_many_emps then

       dbms_output.put_line('too many emps');

   end;

    /

 

4、存储过程--procedure

  4.1 匿名块和有名块

   /* 定义两个变量,输出最大值 */

   declare

    var_a number:=10;

    var_b number:=20;

   begin

     if var_a>var_b then

        dbms_output.put_line(var_a);

     else

        dbms_output.put_line(var_b);

     end if;

   end;

    /

 

    匿名块:

    不保存在数据库中

    每次使用会进行编译

    不能被其它的块调用

    有名块:

    一般保存在数据库中

    可以在需要的时候调用

   procedure  function  package trigger

  4.2创建存储过程的语法

   create [or replace] procedure 过程名[(

     参数名[{in|out|in out}]类型[:=值|default 值]  

   )]

   {is|as}

      -- 临时变量

   begin

     -- 执行的操作

   end;

    /

  4.3无参的存储过程

   create or replace procedure getmax_zsm_00

   is

    var_a number:=10;

    var_b number:=20;

   begin

     if var_a>var_b then

        dbms_output.put_line(var_a);

     else

        dbms_output.put_line(var_b);

     end if;

   end;

    /

   /* 查看警告 */

   show errors;

  4.4调用无参的存储过程

   begin

     getmax_zsm_00;

   end;

    /

  4.5带参的存储过程的定义

    参数的数据类型不允许带有任何长度、精度的修饰

   

   create or replace procedure getmax_zsm_00(

      a number:=10,b in number)

   is

   begin

     if a>b then

       dbms_output.put_line(a);

     else

        dbms_output.put_line(b);

     end if;

   end;

 

  4.6参数的模式和默认值

   /*查看存储过程 */

  desc getmax_zsm_00;

  PROCEDURE getmax_zsm_00

  Argument Name       Type         In/Out Default?

  ------------------- ------------ ------ --------

  A                   NUMBER       IN    DEFAULT

  B                   NUMBER       IN

 

  in:    输入参数 调用时向过程中传值

         缺省方式(默认)

         值或者赋值后的变量

  out:   输出参数 从过程中返回值

         变量,不需要赋值

   inout:输入输出参数  调用时向过程传入一个值,

         在过程中重新赋值后带回

         赋值后的变量

 

   默认值:有默认值的参数可以不用传参

 

  4.7有参的存储过程的调用

   1)按位置传参

   declare

     var_a number:=100;

     var_b number:=200;

   begin

     getmax_zsm_00(20,30);

     getmax_zsm_00(var_a,var_b);

   end;

    /

   2)按名称传参

     参数名称=>参数值

   declare

     var_b number:=2;

   begin

     getmax_zsm_00(b=>var_b);

   end;

    /

  4.8练习:创建一个存储过程,

     有两个参数,输出两个参数的最大值

     把两数之和保存在第二个参数中

     测试这个存储过程

    create or replace procedure getmax_zsm_00(

        a in number,b in out number

    )

    is

    begin

     if a>b then

       dbms_output.put_line(a);

     else

       dbms_output.put_line(b);

     end if;

     b:=a+b;

    end;

    /

 

    declare

      var_b number:=100;

    begin

       getmax_zsm_00(50,var_b);

      dbms_output.put_line(var_b);

    end;

    /

 

5、函数 --function

  5.1 函数和存储过程的区别

    1)关键字不同 存储过程是procedure 函数是function

    2)函数有返回值和返回类型,存储过程没有

    3)调用时,在plsql中存储过程是直接调用,函数要组成表达式调用

  5.2案例:创建函数,传入两个参数,返回最小值

   create or replace function getmin_zsm_00(

     a in number,b number

    )return number

   is

   begin

     if a<b then

       return a;

     else

       return b;

     end if;

   end;

 

    调用函数:

   declare

     var_res number;

   begin

      var_res:=getmin_zsm_00(10,20);

     dbms_output.put_line(var_res);

   end;

    /

  5.3练习:创建一个函数,

     传入两个参数,返回两个参数的最小值

     并把两数之和保存在第二个参数中

     测试这个函数

    create or replace function getmin_zsm_00(

      a in number,b in out number

    ) return number

    is

       temp number;

    begin

      temp:=b;

      b:=a+b;

      if a<temp then

         return a;

      else

         return temp;

      end if;

    end;

    /

    /* 调用函数 */

    declare

      var_res number;

       var_a number:=10;

      var_b number:=100;

    begin

      var_res:=getmin_zsm_00(var_a,var_b);

      dbms_output.put_line('sum='||var_b);

      dbms_output.put_line('min='||var_res);

    end;

    /

 

 

6. -- package

  6.1概念

    对相关的函数、存储过程、变量、类型、游标、异常等的封装

  6.2常用的系统包

   dbms_output:输出包

   dbms_random:随机包

   dbms_job:任务调度包

 

   /* 查看包 */

   desc dbms_output;

   desc dbms_random;

   desc dbms_job;

   /* 调用包中的数据时,前面加包名 */

   begin

     dbms_output.put_line(dbms_random.value(1,100));

   end;

 

  6.3自定义包

    1)包由包规范和包主体构成

     

    包规范:

   create or replace package 包名

   is|as

     procedure 过程名(参数列表);

     function 函数名(参数列表) return type;

   end [包名];

    /

    包主体:

   create or replace package body 包名

   is|as

     procedure 过程名(参数列表)

     is

       

     begin

 

     end;

     function 函数名(参数列表) return type

     is

 

     begin

 

     end;

    end [包名];

    /

 

    /* 创建包规范 */

    create or replace package mypack_zsm_00

    is

      procedure getmax(a number,b number);

      function getmin(a number,b number) return number;

    end mypack_zsm_00;

    /

    /* 创建包主体 */

    create or replace package body mypack_zsm_00

    is

      procedure getmax(a number,b number)

      is

      begin

         if a>b then

            dbms_output.put_line(a);

         else

            dbms_output.put_line(b);

         end if;

      end;

      function getmin(a number,b number) return number

      is

      begin

        if a<b then

            return a;

        else

            return b;

        end if;

      end;

    end mypack_zsm_00;

    /

    /* 调用包中的数据 */

    begin

      mypack_zsm_00.getmax(10,200);

      dbms_output.put_line(mypack_zsm_00.getmin(10,200));

    end;

    /

 

7、触发器 -- trigger

  7.1 概念

   在数据库中保存的,隐式执行的有名块

  

  dml触发器

 

  7.2 语法

   create or replace trigger 触发器名

   before|after dml{insert|delete|update}

   on 表名 {   |for each row}

   declare

 

   begin

 

   end;

    /

 

  7.3 语句级触发器

   /* 复制s_emp */

   create table emp_zsm_00

        as select id,first_name,salary from s_emp;

 

   create or replace trigger emp_tri_zsm_00

   before update

   on emp_zsm_00

   declare

   begin

     dbms_output.put_line('table updated');

   end;

    /

 

   update emp_zsm_00 set salary=salary+100 where id=1;

   update emp_zsm_00 set salary=salary+100 where id<1;

   update emp_zsm_00 set salary=salary+100 where id>1;

 

  7.4行级触发器

   create or replace trigger emp_tri_zsm_00

   before update

   on emp_zsm_00 for each row

   declare

   begin

     dbms_output.put_line('table updated');

     dbms_output.put_line('old: '||:old.id||'   '||:old.salary);

     dbms_output.put_line('new: '||:new.id||'   '||:new.salary);

   end;

    /

   update emp_zsm_00 set salary=salary+100 where id=1;

   update emp_zsm_00 set salary=salary+100 where id<1;

   update emp_zsm_00 set salary=salary+100 where id>1;

 

    表名%rowtype类型

   :old

   :new

 

   insert    delete   update

               :old     :old

     :new               :new

 

 

    注意:触发器中不能使用事务控制语句

--------------------------------------------------------

总结:

1、动态sql

2、游标

3、异常

4、存储过程

5、函数

6、包

7、触发器

      

 

练习:

1.使用游标保存s_dept表中的全部信息并遍历输出

2.设计一个存储过程,包括两个参数(n,n_sum)

  其中第一个参数代表从1加到的数字

  第二个参数保存前n项的和

  测试这个存储过程