人工智能uu

这个屌丝很懒，什么也没留下！

热门标签

Oracle编程_oracle 专家编程

作者：人工智能uu | 2024-07-29 00:39:35

踩

oracle 专家编程

Oracle高级部分

一、PLSQL编程

是过程语言(Procedural Language)与结构化查询语言(SQL)结合而成的编程语言.通过增加变量、控制语句，使我们可以写一些逻辑更加复杂的数据库操作.

语法结构

declare
	--声明变量  变量名称 v_ 开头，规范
begin
   --执行具体的语句
   --异常处理
end;
1
2
3
4
5
6

注意：

赋值通过’:='完成
begin和end之间必须有一行可执行的代码
end之后必须跟上’;’
如果没有需要声明的变量declare可以省略掉

declare
    v_hello varchar(20);
begin
    v_hello := 'Hello Oracle';
    dbms_output.put_line(v_hello);
end;

begin
   dbms_output.put_line('hello');
end;
1
2
3
4
5
6
7
8
9
10

dbms_output不输出的问题。执行如下命令即可

set serveroutput on;

1. dbms_output用法

dbms_output包主要用于调试pl/sql程序，或者在sql*plus命令中显示信息(displaying message)和报表，譬如我们可以写一个简单的匿名pl/sql程序块，而该块出于某种目的使用dbms_output包来显示一些信息。

enable：在serveroutput on的情况下，用来使dbms_output生效(默认即打开)
disable：在serveroutput on的情况下，用来使dbms_output失效
put：将内容写到内存，等到put_line时一起输出
put_line：不用多说了，输出字符
new_line：作为一行的结束，可以理解为写入buffer时的换行符
get_line(value, index)：获取缓冲区的单行信息
get_lines(array, index)：以数组形式来获取缓冲区的多行信息

begin
   dbms_output.put('a1');
   dbms_output.put('b2');
   dbms_output.new_line(); -- 输出缓存中的信息，新起一行
   dbms_output.put_line('aaaaa'); -- 会输出缓存中的信息和当前的信息，不会换行
end;
1
2
3
4
5
6

2.赋值操作

2.1 :=

-- 定义两个变量 v_a,v_b 计算和是多少
declare
    v_a number(3); --- 声明变量
    v_b number(3) :=20 ; -- 声明变量同时赋值
    v_num number(3);
    v_f constant varchar(20) :='我是常量';
begin
   -- v_f := 'aaa'; -- 常量不能够被修改
    v_a := 30;
    v_num := v_a + v_b;
    dbms_output.put_line(v_a||'+'|| v_b ||'='||v_num); -- || 字符串拼接我们通过 || 来实现
end;
1
2
3
4
5
6
7
8
9
10
11
12

2.2 into

into我们在执行SQL操作的时候，需要把查询的字段信息赋值给变量。那么这时我们就可以通过into 关键字来实现。如果有多个字段要赋值。我们只需要在into的左右两侧建立好对应关系即可。

declare
   v_name varchar2(30);
   v_sex varchar2(3);
   v_dept varchar2(10);
begin
   select name,sex,department into v_name,v_sex,v_dept from student where id = 901;
   dbms_output.put_line(v_name||'-'||v_sex||'-'||v_dept);
end;


-- 定义两个变量 v_a,v_b 计算和是多少
declare
    v_a number(3) :=&请输入a; --- 声明变量
    v_b number(3) :=&请输入b; -- 声明变量同时赋值
    v_num number(3);
begin
    v_num := v_a + v_b;
    dbms_output.put_line(v_a||'+'|| v_b ||'='||v_num); -- || 字符串拼接我们通过 || 来实现
end;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

2.3 属性类型

%type:变量和字段类型的绑定
%rowtype：表结构中的一条记录的绑定

-- 变量的类型如果和字段的类型不一致怎么办?
-- 属性类型
declare
   v_name student.name%type;
   v_sex student.sex%type;
   v_dept student.department%type;
begin
   select name,sex,department into v_name,v_sex,v_dept from student where id = 901;
   dbms_output.put_line(v_name||'-'||v_sex||'-'||v_dept);
end;
-- 表结构中有很多个字段。我们对于的就需要声明多少个变量，很繁琐。
declare
   v_row student%rowtype;
begin
   select * into v_row from student where id = 901;
   dbms_output.put_line(v_row.id||'-'||v_row.name||'-'||v_row.sex);
end;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

3.控制语句

3.1 分支语句

3.1.1 if语句

if语句的作用是控制程序的执行顺序。范围控制

declare
   v_age number(3) := &请输入年龄;
begin 
   dbms_output.put_line('v_age='||v_age);
   if v_age = 18 then
      dbms_output.put_line('成年小伙');
   end if;
   dbms_output.put_line('-------');
   if v_age = 18 then
      dbms_output.put_line('成年小伙');
      else
      dbms_output.put_line('未知...');
   end if;
   dbms_output.put_line('-------');
   if v_age = 18 then
      dbms_output.put_line('成年小伙');
      elsif v_age < 18 then
      dbms_output.put_line('小孩子');
      elsif v_age > 18 then
      dbms_output.put_line('成年人');
      else
      dbms_output.put_line('未知...');
   end if;
end;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

3.1.2 case语句

case语句是一个非常强大的关键字。既可以实现类似于Java中的switch语句的作用。也可以像if语句一样来实现范围的处理。

-- case 语句
declare
   v_age number(3) := &输入年龄;
begin
   case
     when v_age < 18 then
      dbms_output.put_line('小朋友');
      when v_age > 18 then 
      dbms_output.put_line('成年人');
      else
      dbms_output.put_line('刚好成年');
   end case;
end;
-- case 语句可以实现类似于Java中的switch语句。在 case 和when之间声明变量就可以
-- 如果是在when 和 then 之间指定条件那么和if语句是类似的
declare
   v_age number(3) := &输入年龄;
begin
   case v_age
     when 18 then
      dbms_output.put_line('18');
      when 19 then 
      dbms_output.put_line('19');
      else
      dbms_output.put_line('未知');
   end case;
end;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

3.2 循环语句

3.2.1 无限循环

loop循环可以通过exit来指定条件跳出循环。如果不指定那么就是无限循环

-- 输出1~10
declare
  v_i number(3) := 1;
begin
  loop
     dbms_output.put_line(v_i);
     exit when v_i >= 10; -- 退出循环
     v_i := v_i + 1;
  end loop;
end;
1
2
3
4
5
6
7
8
9
10

3.2.2 有条件循环

通过while来指定循环的条件

declare
   v_i number(3) := 1;
begin
   while v_i <= 10 loop
      dbms_output.put_line(v_i);
      -- 修改变量
      v_i := v_i + 1;
   end loop;
end;
1
2
3
4
5
6
7
8
9

3.2.3 for循环

--for循环
begin
   for i in 1..10 loop
    dbms_output.put_line(i);
   end loop;
end;
select * from student;
begin
  for cur_row in (select id,name,sex,department  from student) loop
     dbms_output.put_line(cur_row.id||'-'|| cur_row.name ||'-' || cur_row.sex || '-' || cur_row.department);
  end loop;
end;
1
2
3
4
5
6
7
8
9
10
11
12

3.2.4 goto

顺序控制用于按顺序执行语句,goto关键字会跳转到我们指定的位置开始自上而下执行。

-- goto
declare
   v1 number(3) := &请输入v1的值;
begin
   if v1 > 10 then
        goto c1;
   elsif v1 = 10 then 
      goto c2; 
   else
      dbms_output.put_line('其他');
   end if;
        dbms_output.put_line('666');
   <<c1>>
   dbms_output.put_line('大于10');
   <<c2>>
   dbms_output.put_line('等于10');
   
   dbms_output.put_line('----1----');
   dbms_output.put_line('----2----');
end;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

4.动态SQL语句

动态 SQL 是指在PL/SQL程序执行时生成的SQL 语句。

语法结构为：

EXECUTE IMMEDIATE dynamic_sql_string
      [INTO  define_variable_list]
      [USING bind_argument_list];
1
2
3

案例

-- 可以根据名字或者性别来查询学生的信息
declare
    v_name student.name%type := '&请输入姓名';
    v_sex student.sex%type :='&请输入性别';
    v_sql varchar2(200);
    v_row student%rowtype;
begin
    v_sql := 'select * from student where 1=1 ';
    if v_name is not null then
        v_sql := v_sql || ' and name like ''%'||v_name||'%''' ;
    end if;
  
    if v_sex is not null then
       v_sql := v_sql || ' and sex = '''|| v_sex||'''' ;
    end if;
    execute immediate v_sql into v_row ;
  
  
    dbms_output.put_line(v_row.name||'---'||v_row.sex||'---'||v_row.department);
end;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

如果查询的结果不存在或者返回的记录过多那么都会爆出异常信息

5.异常语句

在运行程序时出现的错误叫做异常
发生异常后，语句将停止执行，控制权转移到PL/SQL 块的异常处理部分
异常有两种类型

预定义异常 - 当 PL/SQL 程序违反 Oracle 规则或超越系统限制时隐式引发
用户定义异常 - 用户可以在 PL/SQL 块的声明部分定义异常，自定义的异常通过 RAISE 语句显式引发

处理系统预定义异常：

-- 异常的应用
-- 系统预定义异常： 
-- too_many_rows 多行数据
-- no_data_found 找不到
-- others 其他异常
declare
   v_name student.name%type;
   
begin
   select name into v_name from student where id = 900 ;
   dbms_output.put_line(v_name);
   
   -- 异常语句块
   exception
       when too_many_rows then
        dbms_output.put_line('返回太多行');
       when no_data_found then
        dbms_output.put_line('找不到数据');
       when others then
        dbms_output.put_line('其他错误');
end;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

自定义异常：

步骤：

需要显示的声明自定义的异常
在业务逻辑代码中通过raise关键字抛出自定义异常
我们需要在异步部分来声明自定义异常满足条件的处理方案

-- 自定义异常
declare
   myException exception; -- 声明异常
   v_name varchar2(30) := '张三1';
begin
   if v_name not in ('张三','李四','王五') then
      -- 满足条件就抛出异常
      raise myException;
    else
      dbms_output.put_line('---------------------');
   end if;
    dbms_output.put_line('---------66666------------');
   
   exception
      when myException then
        dbms_output.put_line('---------触发了自定义异常------------');
      when others then
       dbms_output.put_line('---------其他异常------------');
end;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

二、游标

游标的作用：处理多行数据，类似与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 ; 
1
2
3
4
5
6
7
8
9
10
11

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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

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 ;   
1
2
3
4
5
6
7
8
9
10
11
12
13
14

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 ;   
1
2
3
4
5
6
7
8
9
10

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 ;   
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

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 ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

可以使用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 ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

游标的小结：

游标用于处理查询结果集中的数据
游标类型有：隐式游标、显式游标和 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
3
4
5
6
7
8
9

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 ;
1
2
3
4
5
6
7
8
9

调用存储过程：

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

有参数的案例：

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 ;
1
2
3
4
5
6
7
8
9
10
11
12

调用处理

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

参数的三种类型

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 ;
1
2
3
4
5
6
7
8
9
10
11

调用过程

     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;
  
1
2
3
4
5
6
7
8
9

案例：请根据性别或名字查询相关记录，并把结果返回来打印了出来提示用 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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

调用

-- 执行测试
          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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

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;
1
2
3
4
5
6
7
8
9
10

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 ;
1
2
3
4
5
6
7
8

如何调用呢

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 ; 
1
2
3
4
5
6
7
8
9
10

调用

   -- 调用 
     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;
1
2
3
4
5
6
7
8
9
10

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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62

四、触发器

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;
1
2
3
4
5
6
7
8
9
10
11

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

create or replace trigger trigger01
after insert or update or delete on t_student
declare
   
begin
   dbms_output.put_line('操作成功');
end ;
1
2
3
4
5
6
7

在这里插入图片描述

2.触发器的类型

2.1 语句级触发器

关注的是执行了这条语句

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

准备表

CREATE TABLE t_audit_table
(
  stablename varchar2(30),
  nins number,--记录添加次数
  nupd number,--记录修改次数
  ndel number,--记录删除次数
  startdate date,
  enddate date
)
1
2
3
4
5
6
7
8
9

实现：

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;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

在这里插入图片描述

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;
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

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;
1
2
3
4
5
6
7
8
9
10

在这里插入图片描述

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/人工智能uu/article/detail/896422