@[TOC]

一、游标

游标的作用：处理多行数据，类似与java中的集合

1.隐式游标

一般是配合显示游标去使用的，不需要显示声明，打开，关闭，系统自定维护,名称为：sql

常用属性：

sql%found:语句影响了一行或者多行时为true
%NOTFOUND:语句没有任何影响的时候为true
%ROWCOUNT:语句影响的行数
%ISOPEN:游标是否打开，始终为false

案例：

begin
           update t_student set age=20 ;

           if sql%found then
               dbms_output.put_line('修改成功，共修改了   ' ||  sql%rowcount  || '   条记录');
             else
                 dbms_output.put_line('没有这个学生');
             end if;

            -- commit ;-- 提交应该要放在隐式游标后面
        end ;

2.显示游标

显式游标在PL/SQL块的声明部分定义查询，该查询可以返回多行,处理多行数据

实现步骤：

声明一个游标
打开游标
循环提取数据
关闭游标

案例：

a) 无参数：查询所有学生信息，并显示出学生姓名，性别，年龄

-- 步骤：1.声明一个游标  2.打开游标  3.循环提取数据 4.关闭游标
-- 查询所有的学生信息。并且显示学生的姓名，年龄和性别
declare
   v_row t_student%rowtype;
   -- 1.游标的声明
   cursor mycursor is select * from t_student ;
begin
    -- 2.打开游标
    open mycursor;

    -- 3.循环提取数据
    loop
        fetch mycursor into v_row;
        -- 找到出口
        exit when mycursor%notfound;
        dbms_output.put_line(v_row.name||'-'||v_row.gender||'-'||v_row.age);
    end loop;
    -- 4.关闭游标
    close mycursor;
end;

b) 有参数：

declare
          v_sex varchar2(4) :='&请输入性别' ;
           v_row t_student%rowtype ;
           cursor mycursor(p_sex varchar2) is select * from t_student where sex=p_sex ; -- 注：参数的类型不要指定长度大小
        begin
           open mycursor(v_sex) ;-- 2、打开游标
           loop
               fetch mycursor into v_row;
                  exit when mycursor%notfound;
               dbms_output.put_line(v_row.stuname || ',' || v_row.sex || ',' || v_row.age);

           end loop; 
           close mycursor;--  4、 关闭游标
        end ;

c ) 循环游标. 简化游标 for：不需要打开游标也不需要关闭游标

declare
          v_sex varchar2(4) :='&请输入性别' ;
           cursor mycursor(p_sex varchar2) is select * from t_student where sex=p_sex ; -- 注：参数的类型不要指定长度大小
        begin

          for v_row   in  mycursor(v_sex)    loop
               dbms_output.put_line(v_row.stuname || ',' || v_row.sex || ',' || v_row.age);  
           end loop; 

        end ;

d) 使用显式游标更新行：

允许使用游标删除或更新活动集中的行，声明游标时必须使用 select ... for update 语句。

declare
          v_sex varchar2(4) :='&请输入性别' ;
           v_row t_student%rowtype ;
           cursor mycursor(p_sex varchar2) is select * from t_student where sex=p_sex  for update; -- 注：参数的类型不要指定长度大小
        begin
           open mycursor(v_sex) ;-- 2、打开游标
           loop
               fetch mycursor into v_row;
                  exit when mycursor%notfound;
              -- dbms_output.put_line(v_row.stuname || ',' || v_row.sex || ',' || v_row.age);

               update t_student set age = age +10 where current of mycursor;

           end loop; 
           --commit ;
           close mycursor;--  4、 关闭游标
        end ;

3.REF游标

处理运行时动态执行的 SQL 查询,特点：

优点：

动态SQL语句
在存储过程中可以当参数

缺点：

不能使用循环游标for
不能使用游标更新行

使用步骤：

定义一个ref的类型
声明游标
打开游标
提取数据
关闭游标

案例讲解

declare
         v_sex varchar2(4) ;
       --type mytype is ref cursor return t_student%rowtype; -- 强类型的 ref 游标类型
         type mytype is ref cursor  ;   --  1）弱类型的 ref 游标类型
         mycursor mytype;   --  2) 声明游标
         v_sql varchar2(100) ;
         v_row t_student%rowtype ;
     begin
         v_sql :=' select * from t_student ' ;

        -- open mycursor for select * from t_student; 
         open mycursor for v_sql ;
         loop
             fetch mycursor into v_row ;
             exit when mycursor%notfound ;
             dbms_output.put_line(v_row.stuname || ',' || v_row.sex || ',' || v_row.age);
         end loop;
         close mycursor ;  

     end ;

可以使用sys_refcursor类型

declare
         v_stuname t_student.stuname%type :='&请输入名字' ;
           v_sex varchar2(3) :='&请输入性别' ;

         mycursor  sys_refcursor ;   --  2) 声明游标
         v_sql varchar2(100) ;
         v_row t_student%rowtype ;
     begin

          v_sql :='select * from t_student  where  1=1 ';

             if  v_stuname is not null then 
                 v_sql :=v_sql  || '  and stuname like  ''%'  || v_stuname || '%'' ' ;
              end if;  

            if  v_sex is not null then
                   v_sql :=v_sql || '  and sex = '''  || v_sex || ''' ' ;
            end if;

            dbms_output.put_line('v_sql= ' || v_sql );


        -- open mycursor for select * from t_student; 
         open mycursor for v_sql ;
         loop
             fetch mycursor into v_row ;
             exit when mycursor%notfound ;
             dbms_output.put_line(v_row.stuname || ',' || v_row.sex || ',' || v_row.age);
         end loop;
         close mycursor ;  

     end ;

游标的小结：

游标用于处理查询结果集中的数据
游标类型有：隐式游标、显式游标和 REF游标
隐式游标由 PL/SQL 自动定义、打开和关闭
显式游标用于处理返回多行的查询
显式游标可以删除和更新活动集中的行
要处理结果集中所有记录时，可使用循环游标

二、子程序

什么是子程序：命名的 PL/SQL 块，编译并存储在数据库中

1.存储过程

1.1 语法结构

CREATE [OR REPLACE] PROCEDURE 
   <procedure name> [(<parameter list>)]
IS|AS 
   <local variable declaration>
BEGIN
   <executable statements>
[EXCEPTION
   <exception handlers>]
END;

1.2 案例讲解

无参数案例：写一个存储过程，往学生表中模拟 10 00 条数据（插入1000 条数据）

create or replace procedure protest01
     is
        -- 声明变量  
     begin
         for  i  in 1..100 loop
             insert into t_student(id,stuname,sex,age) values(seq_t_student.nextval  , '小李' || i  , '男' , i );
         end loop;
         commit ;
     end ;

调用存储过程：

declare
        begin
           -- protest01();
            protest01;   --  当没有参数里，括号可省略不写
        end;

有参数的案例：

create or replace procedure protest02(
    p_name varchar2,
    p_sex  varchar2, 
    p_age number
    )
     is
        -- 声明变量  
     begin

         dbms_output.put_line(p_name || ',' || p_sex || ',' || p_age );

     end ;

调用处理

declare
           v_name varchar2(10) :='&请输入名字' ;
           v_sex varchar2(4) :='&请输入性别';
           v_age number(3) :='&请输入年龄'; 
        begin
            protest02(v_name,  v_sex , v_age);   --  当没有参数里，括号可省略不写
        end;

参数的三种类型

in 输入
out 输出
in out 输入输出

案例讲解

create or replace procedure protest03(
    p_name in varchar2, 
    p_sex in out  varchar2, 
    p_age in out number)
     is
        -- 声明变量  
     begin

         dbms_output.put_line(p_name || ',' || p_sex || ',' || p_age );
         p_sex :='我是P_sex';  
     end ;

调用过程

     declare
           v_name varchar2(10) :='&请输入名字' ;
           v_sex varchar2(50) :='&请输入性别';
           v_age number(3) :='&请输入年龄'; 
        begin
            protest03(v_name,  v_sex , v_age);   --  当没有参数里，括号可省略不写
             dbms_output.put_line(v_sex);
        end;

案例：请根据性别或名字查询相关记录，并把结果返回来打印了出来提示用 sys_refcursor

create or replace procedure protest04(
        p_name varchar2,
        p_sex varchar2, 
        myresult out sys_refcursor)
          is
             v_sql  varchar2(100) ;
          begin
             v_sql :='select * from t_student where 1=1 ';
             if p_name is not null then
                v_sql :=v_sql  ||  '  and  stuname like  ''%'  || p_name  ||   '%'' ' ;
             end if;
             if p_sex is not null then
                v_sql := v_sql || '  and  sex = ''' || p_sex || '''  ';
             end if;
             dbms_output.put_line('v_sql='  || v_sql);

             open myresult for v_sql ;

          end;

调用

-- 执行测试
          declare
             v_name varchar2(20) :='&请输入名字';
             v_sex varchar2(4) :='&请输入性别' ;
             mycursor sys_refcursor ;
             v_row t_student%rowtype;
          begin
             --  protest04(v_name, v_sex , mycursor);  
    -- 1) 位置传递
                --2 ）名称传递
                 --protest04( p_sex =>v_sex , p_name => v_name , myresult => mycursor);
                 --3) 混合使用  : 先用位置传递，如果后面有用了名称传递，后面就不能用位置传递
                     protest04(v_name , myresult=>mycursor ，p_sex => v_sex,);
               loop
                   fetch mycursor into  v_row ;
                    exit when mycursor%notfound ;
                    dbms_output.put_line(v_row.stuname || ',' || v_row.sex || ',' || v_row.age );
               end loop;

               close mycursor ;   
          end;

2.存储函数

类似于java中方法，有返回值可以返回值的命名的 PL/SQL 子程序。

2.1 语法结构

CREATE [OR REPLACE] FUNCTION 
  <function name> [(param1,param2)]
RETURN <datatype>  IS|AS 
  [local declarations]
BEGIN
  Executable Statements;
  RETURN result;
EXCEPTION
  Exception handlers;
END;

2.2 案例讲解

无参案例：写一个函数，获取学生名称

create or replace function funtest02
   return varchar2
   is
      v_name varchar2(20) ;
   begin
         select stuname into v_name from t_student where id=201;
         return v_name;
   end ;

如何调用呢

a)用PL/SQL块调用函数
b)用select语句调用: 在函数中不能有增加删除修改的语句，只能是查询的语句

有参案例：

create or replace function  funtest03(p_name in varchar2, p_sex out varchar2, p_age in out number)
     return varchar2
     is
        -- 声明变量  
     begin

         dbms_output.put_line(p_name || ',' || p_sex || ',' || p_age );
         p_sex :='我是函数的p_sex' ;
            return '成功';
     end ;

调用

   -- 调用 
     declare
           v_name varchar2(10) :='&请输入名字' ;
           v_sex varchar2(20) :='&请输入性别';
           v_age number(3) :='&请输入年龄'; 
           v_result varchar2(30) ;
        begin
            v_result := funtest03(v_name, v_sex , p_age=>v_age);   
              dbms_output.put_line('v_result=' || v_result || ', v_sex=' || v_sex );
        end;

3.程序包

程序包：作用就是管理我们的存储过程和方法

-- 程序包：作用就是管理我们的存储过程和方法
-- 规范和主体两部分组成
-- 创建一个规范
create or replace package pak01
is
   procedure myprocdure01(p_name varchar2);
   function myfun01 return number;
end pak01;


-- 创建规范对应的主体：主体中的方法如果在规范中声明了。那么外包可以访问。如果没有那么就只能被内部调用
create or replace package body pak01
is

    -- myprodure01 存储过程
    procedure myprocdure01(p_name varchar2)
    as
    begin
        dbms_output.put_line('p_name = '||p_name);
    end;
    -- myfun01 方法
    function myfun01 return number
    is
    begin
         dbms_output.put_line('方法执行了.... ');
         return 666;
    end;
end pak01;


create or replace package body pak01
is

    -- myprodure01 存储过程
    procedure myprocdure01(p_name varchar2)
    as
    begin
        dbms_output.put_line('p_name = '||p_name);
    end;
    -- myfun01 方法
    function myfun01 return number
    is
    begin
         dbms_output.put_line('方法执行了.... ');
         return 666;
    end;

    function myfun02 return number
    is

    begin
       dbms_output.put_line('方法执行了..222.. ');
       return 999;
    end;
end pak01;

-- 调用package中的过程和方法 package.
begin
   pak01.myprocdure01('李四');
end;

select pak01.myfun02 from dual;

三、触发器

1.触发器的基本讲解

当特定事件出现时自动执行的存储过程

语法结构

CREATE [OR REPLACE] TRIGGER trigger_name
AFTER | BEFORE | INSTEAD OF
[INSERT] [[OR] UPDATE [OF column_list]] 
[[OR] DELETE]
ON table_or_view_name
[REFERENCING {OLD [AS] old / NEW [AS] new}]
[FOR EACH ROW]
[WHEN (condition)]
declare
begin
end;

案例：对学生表进行增加删除修改后打印一句操作成功

create or replace trigger trigger01
after insert or update or delete on t_student
declare

begin
   dbms_output.put_line('操作成功');
end ;

2.触发器的类型

2.1 语句级触发器

关注的是执行了这条语句

案例：创建一个对学生表的增删改的审计触发器

准备表

CREATE TABLE t_audit_table
(
  stablename varchar2(30),
  nins number,--记录添加次数
  nupd number,--记录修改次数
  ndel number,--记录删除次数
  startdate date,
  enddate date
)

实现：

create or replace trigger trigger02
    after insert or delete or update on t_student
    declare
       v_count number(3);
    begin
        -- 先判断t_student在这个日志表中是否有这条记录，如果没有，要先插入数据
        select count(*) into v_count from t_audit_table where stablename='t_student';
        if v_count<=0 then
             insert into t_audit_table(stablename,nins,nupd,ndel) values('t_student', 0,0 ,0);
        end if;

        if inserting then
            update t_audit_table set nins=nins+1 where stablename='t_student';
        end if;
        if updating then
             update t_audit_table set nupd=nupd+1 where stablename='t_student';
        end if;
        if deleting then
            update t_audit_table set ndel=ndel+1 where stablename='t_student';
        end if;

2.2 行级触发器

影响的行数：影响了多少行数据。那么这个触发器就会触发多少次

create or replace trigger trigger02
    after insert or delete or update on t_student
    FOR EACH ROW
    declare
       v_count number(3);
    begin
        -- 先判断t_student在这个日志表中是否有这条记录，如果没有，要先插入数据
        select count(*) into v_count from t_audit_table where stablename='t_student';
        if v_count<=0 then
             insert into t_audit_table(stablename,nins,nupd,ndel) values('t_student', 0,0 ,0);
        end if;

        if inserting then
            update t_audit_table set nins=nins+1 where stablename='t_student';
        end if;
        if updating then
             update t_audit_table set nupd=nupd+1 where stablename='t_student';
        end if;
        if deleting then
            update t_audit_table set ndel=ndel+1 where stablename='t_student';
        end if;

2.3 限制行级触发器

对部分数据做特定的处理，比如：不能删除管理员

create or replace trigger trigger03
   before  delete on t_student
    for each row
    when(old.stuname='小李6')  
  declare
  begin
         dbms_output.put_line('班长不能被删除');

        RAISE_APPLICATION_ERROR(-20001, '班长不能被删除');
  end;

Oracle 游标&子程序&触发器

一、游标

1.隐式游标

2.显示游标

3.REF游标

二、子程序

1.存储过程

1.1 语法结构

1.2 案例讲解

2.存储函数

2.1 语法结构

2.2 案例讲解

3.程序包

三、触发器

1.触发器的基本讲解

2.触发器的类型

2.1 语句级触发器

2.2 行级触发器

2.3 限制行级触发器

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

Oracle 游标&子程序&触发器

一、游标

1.隐式游标

2.显示游标

3.REF游标

二、子程序

1.存储过程

1.1 语法结构

1.2 案例讲解

2.存储函数

2.1 语法结构

2.2 案例讲解

3.程序包

三、触发器

1.触发器的基本讲解

2.触发器的类型

2.1 语句级触发器

2.2 行级触发器

2.3 限制行级触发器

热门文章

最新文章

相关课程

相关电子书

相关实验场景

推荐镜像