Cursor、Exception、Procedure、Function、Package、Trigger(day06)

回顾：

　　1.record类型

　　　　定义record类型，声明变量，保存s_dept表中id = 31部门信息

declare
      /* 定义record类型 */ type deptrecord is record( id number, name s_dept.name%type, r_id number ); /* 声明变量 */ var_dept deptrecord; var_id number:=&id; begin
      select * into var_dept from s_dept where id=var_id; dbms_output.put_line(var_dept.id||','|| var_dept.name||','|| var_dept.r_id); end;

　　2.动态SQL

　　　　使用动态SQL语句，删除 testdsql 表中指定id 信息

declare
       sqlstr varchar2(100);
       var_id number:=1;
    begin
       -- sqlstr:='delete from testdsql_zsm_00 where id='
       --        ||var_id;
       sqlstr:='delete from testdsql_zsm_00 where id=:b0';
       dbms_output.put_line(sqlstr);
       execute immediate sqlstr using var_id;
       commit;
    end;

1.游标：

　　1.1概念：

　　　　　　游标是映射在结果集中一行数据的位置指针或位置实体。

　　1.2作用：

　　　　　　处理多行结果集！

　　　　　　用户可以通过游标访问多行结果集中的任何一行数据，即可以对该行数据做操作，比如，把数据提取出来

　　1.3使用游标步骤：

　　　 1) 声明游标

　　　　　　　在声明区声明游标名

　　　　　　　cursor 游标名 is select语句;

　　　　　　　var_emp empcursor%rowtype;//声明游标变量

2）打开游标

　　　　　　　在执行区打开游标

　　　　　　　open 游标名;

　　　 3）提取数据

　　　　　　　执行区提取数据

　　　　　　　fetch 游标名 into 游标变量;

　　　 4）关闭游标　　　　　　　　　

　　　　　　　当提取和处理数据结束后，应该及时关闭游标释放系统资源

　　　　　　　close 游标名;

　　　1.4使用游标读取s_emp 表中的全部数据　　　　　

      declare
        /* 声明游标 */
        cursor empcursor is select * from s_emp;
        /* 声明变量 */
        var_emp empcursor%rowtype;
      begin
         /* 打开游标 */
         open empcursor;
         /* 提取数据 */
         fetch empcursor into var_emp;
         dbms_output.put_line(var_emp.id||','||
                                              var_emp.first_name||','||
                                              var_emp.salary);
         fetch empcursor into var_emp;
         dbms_output.put_line(var_emp.id||','||
                                              var_emp.first_name||','||
                                              var_emp.salary);
         fetch empcursor into var_emp;
         dbms_output.put_line(var_emp.id||','||
                                              var_emp.first_name||','||
                                              var_emp.salary);
         /* 关闭游标 */
         close empcursor;
      end;

　　1.5游标属性

　　　　found 　　　　提取到数据，为真；没有提取到，则为假在open之前，返回非法游标，fetch之前返回null

　　　　notfound　　　没提取到数据，为真；提取到，则为假 .. .. .. ..

　　　　isopen　　　　游标是否是打开状态，打开返回真，失败返回假

　　　　rowcount　　　游标的偏移量没有打开返回非法游标

　　1.6使用简单循环配合 notfound 属性遍历游标　　

declare
        /* 声明游标 */
        cursor empcursor is select * from s_emp;
        /* 声明变量 */
        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||','||
                                                  var_emp.salary);
        end loop;
        /* 关闭游标 */
        close empcursor;
     end;

　　1.7使用while循环配合found属性遍历游标

declare
        /* 声明游标 */
        cursor empcursor is select * from s_emp;
        /* 声明变量 */
        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||','||
                                                  var_emp.salary);
            fetch empcursor into var_emp;
        end loop;
        /* 关闭游标 */
        close empcursor;
     end;

　　1.8使用for循环遍历游标

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

declare
       cursor empcursor is select * from s_emp;
    begin
       for var_emp in empcursor loop
          dbms_output.put_line(var_emp.id||','||
                                                var_emp.first_name||','||
                                                var_emp.salary);
       end loop;
    end;

　　　1.9带参游标

　　　　　　参数的数据类型不能包含长度或精度的修饰，但是可以使用%type

　　　　　　声明：

　　　　　　　　　　cursor 游标名（形参列表） is select语句 where .... ;

　　　　　　传参：在打开游标时传实参

　　　　　　　　　　open 游标名（实参列表）;

declare
       cursor empcursor(var_id number)
            is select * from s_emp where id>var_id;
    begin
       for var_emp in empcursor(10) loop
          dbms_output.put_line(var_emp.id||','||
                                                var_emp.first_name||','||
                                                var_emp.salary);
       end loop;
    end;

　　　1.10参考游标 ref cursor

　　　　　　动态SQL + 游标

参考游标的使用步骤：
     sqlstr := 'select * from s_emp';
    1) 定义一个参考游标类型
      type 参考游标类型名  is ref cursor;
    2) 声明参考游标类型变量
      var_empcursor  参考游标类型名;
    3) 把动态sql语句和参考游标变量结合
      open var_empcursor for sqlstr;
    ......

    案例：使用参考游标 遍历s_emp表中的全部数据
    declare
      sqlstr varchar2(100);
      type emprefcursor is ref cursor;
      var_empcursor emprefcursor;
      var_emp s_emp%rowtype;
    begin
      sqlstr:='select * from s_emp';
      open var_empcursor for sqlstr;
      loop
         fetch var_empcursor into var_emp;
         exit when var_empcursor%notfound;
         dbms_output.put_line(var_emp.id||','||
                                               var_emp.first_name||','||
                                               var_emp.salary);
      end loop;
      close var_empcursor;
    end;
    
    带有占位符的sql语句
    declare
      sqlstr varchar2(100);
      type emprefcursor is ref cursor;
      var_empcursor emprefcursor;
      var_emp s_emp%rowtype;
    begin
      sqlstr:='select * from s_emp where id>:b0';
      -- using后可以使用变量，也可以使用值。一般用变量
      open var_empcursor for sqlstr using 10;
      loop
         fetch var_empcursor into var_emp;
         exit when var_empcursor%notfound;
         dbms_output.put_line(var_emp.id||','||
                                               var_emp.first_name||','||
                                               var_emp.salary);
      end loop;
      close var_empcursor;
    end;

2.PLSQL中的异常

　　2.1 系统预定义异常

　　　　Oracle 系统自身为用户提供可以在PLSQL中使用的预定义异常，用于检查用户代码失败的一般原因。

　　　　系统预定义异常由系统定义和引发，用户只需根据名字捕获和处理异常

案例：
   declare
       var_name varchar2(25);
       var_id number:=&id;
   begin
       select first_name into var_name from s_emp
          where id = var_id;
       select first_name into var_name from s_emp
          where first_name like 'M%';
       dbms_output.put_line(var_name); 
   exception
       when no_data_found then
          dbms_output.put_line('no emp');
       when others then
          dbms_output.put_line(sqlcode||'.....'||sqlerrm);
   end;

　　常用的异常：

　　　　no_data_found : 　　　　select ..into 语句没有返回行

　　　　too_many_rows: 　　　select..into 语句返回多于一行的结果集

　　　　invalid_cursor: 　　　　非法游标

　　　　cursor_already_open: 　游标已打开

　　　　dup_val_on_index: 　　　唯一索引对应的列上有重复值

　　　　zero_divide:　　　　　　除数为0

案例：处理多个异常
   declare
       var_name varchar2(25);
       var_id number:=&id;
   begin
       select first_name into var_name from s_emp
          where id = var_id;
       select first_name into var_name from s_emp
          where first_name like 'M%';
       dbms_output.put_line(var_name); 
   exception
       when no_data_found then
          dbms_output.put_line('no emp');
       when too_many_rows then
          dbms_output.put_line(' too many rows');
       when others then
          dbms_output.put_line(sqlcode||'.....'||sqlerrm);
   end;

　　2.2自定义异常：

　　　　　2.2.1 定义异常的步骤：　

　　　　　　　　1）定义异常

　　　　　　　　　　　　异常名 exception ;

　　　　　　　　2）根据条件引发异常

　　　　　　　　　　　　if 引发异常条件 then

　　　　　　　　　　　　　　raise 异常名;

　　　　　　　　　　　　end if;

　　　　　　　　3）捕获异常和处理异常

　　　　　　　　　　　　when 异常名 then

　　　　　　　　　　　　　　　处理异常

　　　　2.2.2 案例：更新员工表中指定员工的工资，员工不存在时提示异常

declare
       var_id s_emp.id%type:=&id;
       /* 定义异常 */
       no_result exception;
    begin
       update s_emp set salary=salary+500 where id=var_id;
       if  sql%notfound then
           raise no_result;
       end if;
       commit;
    exception
       when no_result then
           dbms_output.put_line('no result');
end;

　　　　　　隐式游标：在执行一个SQL语句时，Oracle会自动创建一个隐式游标。这个游标是内存中为处理该条SQL语句的工作区。

　　　　　　隐士游标只要用于处理数据操作语句（insert、delete、update）的执行结果

3.存储过程 procedure

　3.1匿名块和有名块

　　匿名块：

　　　　匿名块不保存在与数据库中；每次使用都要进行编译；不能在其他块调用

　　有名块：

　　　　可以存储在数据库中；可以在任何需要的地方调用

　　　　有名块包括：procedure function package trigger

　3.2存储过程的创建

　　语法：

　　　　create [ or replace ] procedure 过程名 [ (参数列表) ]

　　　　is|as

　　　　　　 --临时变量

　　　　begin

　　　　exception

　　　　end;

　3.3无参过程的创建和调用

　　1）创建：输出两数中的较大值

 create or replace procedure getmax_zsm_00
       is
           var_a number:=10;
           var_b number:=100;
       begin
           if var_a > var_b then
               dbms_output.put_line(var_a);
           else
               dbms_output.put_line(var_b);
           end if;
       end;

　　2）创建无参的存储过程

begin
         getmax_zsm_00;
end;

　3.4 带参的存储过程

　　3.4.1 使用的注意事项

　　　　1）参数的数据类型不能包含长度或精度的修饰

　　　　2）参数可以有多种模式，并且可以有默认值

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

　　　　　　　　参数模式：

　　　　　　　　　　in 　　　输入参数负责向过程传入值系统默认

　　　　　　　　　　out 输出参数负责传出值实参必须是变量不必赋值在过程内必须赋值

　　　　　　　　　　in out 输入输出参数既负责传入又负责传出实参是赋值后的变量在过程内可以赋值

　　　　　　　　只有in模式的参数才可以有默认值

　　3.4.2案例：

案例：创建一个带参的存储过程，传入两个数字，输出最大值
      create or replace procedure getmax_zsm_00(
           var_a in number:=10,var_b number)
      is
      begin
          if var_a > var_b then
               dbms_output.put_line(var_a);
          else
               dbms_output.put_line(var_b);
          end if;
      end;

　　3.4.3带参的存储过程的调用　

declare
        var_x number:=123;
        var_y number:=12;
     begin
        getmax_zsm_00(1,10);
        getmax_zsm_00(var_x,100);
        getmax_zsm_00(var_x,var_y);
        -- getmax_zsm_00(1);
     end;

　　3.4.4 参数赋值方式

　　　1）按照位置赋值

　　　2）按照名字赋值参数名=> 值

declare
        var_x number:=123;
        var_y number:=12;
     begin
        getmax_zsm_00(1,10);
        getmax_zsm_00(var_x,100);
        getmax_zsm_00(var_b=>10,var_a=>200);
        getmax_zsm_00(var_b=>1);
     end;

　　3.4.5

3.4.5 练习：创建一个存储过程，传入两个数字，输出最大值，同时把两数之和保存在第二个变量，并测试
      create or replace procedure getmax_zsm_00(
           var_a in number,var_b in out number)
      is
      begin
          if var_a > var_b then
               dbms_output.put_line(var_a);
          else
               dbms_output.put_line(var_b);
          end if;
          var_b:=var_a + var_b;
      end;   

      -- 调用
      declare
          var_x number:=100;
      begin
          getmax_zsm_00(10,var_x);
          dbms_output.put_line(var_x);
      end;

　　3.5 查看存储过程

　　　　desc 过程名;

　　　　desc user_source;

4.函数 Function

　　4.1 plsql 中的函数和过程的区别

　　　　1）关键字不同，过程是procedure 函数是 function

　　　　2）过程没有返回类型和返回值，函数有返回类型和返回值

　　　　3）调用方式不同，过程在PLSQL中是直接调用，函数在PLSQL中需要组成表达式调用（使用变量接收返回值、作为函数或过程的参数）

　　4.2语法

　　　　create or replace function 函数名[参数列表] return 数据类型

　　　　is | as

　　　　　　-- 临时变量

　　　　begin

　　　　　　-- 必须有return语句

　　　　end;

　　4.3 创建一个函数，传入两个数字，返回最小值

create or replace function getmin_zsm_00(
         var_a  number,var_b number) return number
      is
      begin
         if var_a < var_b then
             return var_a;
         else
             return var_b;
         end if;
      end;  

      declare
        var_x number:=100;
        var_y number:=1;
        var_result number;
      begin
         var_result:=getmin_zsm_00(var_x,var_y);
         dbms_output.put_line(var_result);
         dbms_output.put_line(getmin_zsm_00(123,10));
      end;

5.包 package

　　　　5.1概念

　　　　　　把一组逻辑上相关的过程、函数、变量、类型等组织到一起的一种逻辑结构

　　　　5.2 系统提供的包

　　　　　　dbms_output: 　　　　输入输出包

　　　　　　dbms_random:　　　　随机包

　　　　　　dbms_job:　　　　　　定时任务调度包

查看包中的数据：
     desc 包名;   
     desc dbms_random;
     function value(low number,high number)
              return number 
     包名.成员
     select dbms_random.value(1,100) from dual;
        
  5.3 自定义包
    1）定义包 package    -- 类似于C中.h文件
     create [or replace] package 包名
     is
      -- 函数、过程的声明，类型的定义，变量的声明等
     end[ 包名];

     -- 定义一个包，包含一个过程和一个函数
     create or replace package mypackage_zsm_00
     is
        procedure getmax(var_a number,var_b number);
        function getmin(var_a number,var_b number) 
                return number;
     end;

    2) 定义包的主体(包的实现)  
      create [or replace] package body 包名
      is
        -- 函数、过程的实现
      end[ 包名];

      create or replace package body mypackage_zsm_00
      is
        procedure getmax(var_a number,var_b number)
        is
        begin
            if var_a > var_b then
                dbms_output.put_line(var_a);
            else
                dbms_output.put_line(var_b);
            end if;
        end;

        function getmin(var_a number,var_b number) 
                return number
        is
        begin
           if var_a < var_b then
                 return var_a;
           else
                 return var_b;
           end if;
        end;
     end;

     -- 测试
     declare
        var_x number:=11;
        var_y number:=1234;
        var_res number;
     begin
        mypackage_zsm_00.getmax(var_x,var_y);
        var_res:=mypackage_zsm_00.getmin(var_x,var_y);
        dbms_output.put_line(var_res);
     end;

6.触发器 trigger

　　6.1概念

　　　　触发器是一种特殊的‘存储过程’，它定义了一些和数据库相关事件（如insert、delete、update等）执行时应该执行的 ‘功能代码块’，通常用来管理

　　复杂的完整性约束、或监控对表的修改，对数据审计功能

6.2 语法
     create [or replace] trigger 触发器名
     {before|after}  {insert|update|delete}
     on 表名 [for each row]
     declare
     begin
     exception
     end;

   6.3 语句级触发器
     emp_zsm_00

     create or replace trigger tri_emp_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;

     
   6.4 行级触发器
     create or replace trigger tri_emp_zsm_00
     before update on emp_zsm_00 for each row
     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;
  
     标识符:
       :old    原值标识符
       :new  新值标识符      表名%rowtype类型

     insert              :new
     update   :old  :new
     delete    :old
     
     create or replace trigger tri_emp_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;

    注意：触发器中不能包含任何有关事务的操作
              事务 谁发起 谁结束

    6.5 使用触发器产生主键的值
     -- 创建一个表
     create table testtrigger_zsm_00(
            id number primary key,name varchar2(20));
     -- 创建一个序列
     create sequence trigger_id_zsm_00;
     -- 创建触发器
     create or replace trigger tri_pk_zsm_00
     before insert on testtrigger_zsm_00 for each row
     begin
        select trigger_id_zsm_00.nextval into :new.id 
            from dual;
     end;

     -- 插入一条语句
     insert into testtrigger_zsm_00(name) values('test1');

练习：
1.使用游标 遍历s_dept表中的全部数据
2.创建一个存储过程，传入数字n(大于1的整数)，计算1..n的累加和，保存在第二个参数，并测试。

秒客网

Cursor、Exception、Procedure、Function、Package、Trigger(day06)

回顾：

1.record类型

2.动态SQL

1.游标：

1.1概念：

1.2作用：

1.3使用游标步骤：

1) 声明游标

3）提取数据

4）关闭游标

1.4使用游标读取s_emp 表中的全部数据

1.5游标属性

1.6使用简单循环配合 notfound 属性遍历游标

1.7使用while循环配合found属性遍历游标

1.8使用for循环遍历游标

1.9带参游标

1.10参考游标 ref cursor

2.PLSQL中的异常

2.1 系统预定义异常

2.2自定义异常：

2.2.1 定义异常的步骤：

2.2.2 案例：更新员工表中指定员工的工资，员工不存在时提示异常

3.存储过程 procedure

3.1匿名块和有名块

匿名块：

有名块：

3.2存储过程的创建

3.3无参过程的创建和调用

3.4 带参的存储过程

4.函数 Function

5.包 package

6.触发器 trigger

相关文章

Cursor、Exception、Procedure、Function、Package、Trigger(day06)

回顾：

1.record类型

2.动态SQL

1.游标：

1.1概念：

1.2作用：

1.3使用游标步骤：

1) 声明游标

3）提取数据

4）关闭游标

1.4使用游标 读取s_emp 表中的全部数据

1.5游标属性

1.6使用简单循环 配合 notfound 属性 遍历游标

1.7使用while循环 配合found属性 遍历游标

1.8使用for循环遍历游标

1.9带参游标

1.10参考游标 ref cursor

2.PLSQL中的异常

2.1 系统预定义异常

2.2自定义异常：

2.2.1 定义异常的步骤：

2.2.2 案例：更新员工表中指定员工的工资，员工不存在时提示异常

3.存储过程 procedure

3.1匿名块和有名块

匿名块：

有名块：

3.2存储过程的创建

3.3无参过程的创建和调用

3.4 带参的存储过程

4.函数 Function

5.包 package

6.触发器 trigger

相关文章

　　1.record类型

　　2.动态SQL

　　1.1概念：

　　1.2作用：

　　1.3使用游标步骤：

　　　 1) 声明游标

　　　 3）提取数据

　　　 4）关闭游标　　　　　　　　　

　　　1.4使用游标读取s_emp 表中的全部数据　　　　　

　　1.5游标属性

　　1.6使用简单循环配合 notfound 属性遍历游标　　

　　1.7使用while循环配合found属性遍历游标

　　1.8使用for循环遍历游标

　　　1.9带参游标

　　　1.10参考游标 ref cursor

　　2.1 系统预定义异常

　　2.2自定义异常：

　　　　　2.2.1 定义异常的步骤：　

　　　　2.2.2 案例：更新员工表中指定员工的工资，员工不存在时提示异常

　3.1匿名块和有名块

　　匿名块：

　　有名块：

　3.2存储过程的创建

　3.3无参过程的创建和调用

　3.4 带参的存储过程