Oracle-存储过程、存储函数、触发器

Oracle-存储过程、存储函数、触发器

---

目录

---

---

内容

---

1、存储过程

1.1、概念

  存储过程是在大型数据库系统中，一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程名并给定参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。

1.2、创建语法

	create [or replace] procedure 过程名[(参数名 in/ out 数据类型)]
	as 
	begin
		PLSQL 子程序体;
	end;

1.3、实例

• 示例： 给指定员工涨工资

    create or replace procedure p_sal(eno emp.empno%type)
    as
    	
    begin
    	update emp set sal = sal + 100 where empno = eno;
    	commit;
    end;
    
    declare
    begin
    	p_sal(7733);
    end;
  

2、存储函数

• 定义格式：

    create or replace function 函数名[(数据类型 in/out 数据类型)] return 返回值类型
    as
    	变量 数据类型;
    begin
    	PLSQL 子程序体;
    	return 变量;
    end;
  

• 示例：计算指定员工的年薪

    create or replace function f_yearsal(eno emp.empno%type) return number 
    as
    	s number(10);
    begin
    	select sal*12+nvl(comm, 0) into s from emp where empno = eno;
    	return s;
    end;
    
    -- 测试
    declare 
    
    begin
    	dbms_output.put_line(f_yearsal(7788));
    end;
    -- 结果：
    36000
  

3、out类型参数

• 存储过程实现计算指定员工的年薪

    create or replace precedure p_yearsal(eno emp.empno%type, yearsal out number)
    as 
    	s number(10);
    	c emp.comm%type;
    begin
    	select sal*12, nvl(comm, 0) into s, c from emp where empno = eno;
    	yearsal := s + c;
    end;
    
    -- 测试
    declare
    	yearsal number(10);
    begin
    	p_yearsal(7788, yearsal);
    	dbms_output.put_line(yearsal);
    end;
  

• 注意事项：out参数数据类型不能指定长度

4、存储过程与存储函数直接的区别

• 语法区别：
  • 关键字不同
  • 存储函数比存储过程多了2个return
• 本质区别
  • 存储函数有返回值，而存储过程没有返回值
  • 如果存储过程要实现由返回值的业务，必须使用out类型的参数
• 存储过程只是在存储过程内部给out类型的参数赋值，并没有返回值
• 使用存储函数有返回值的特性，我们可以自定义函数

3、触发器

• 概念：制定一个规则，在我们增删改操作的时候，只要满足规则，会自动触发，无须主动调用。

• 格式：

    create or replace trigger
    before/after insert/update/delete [of 字段] on 表名
    [for each row]
    declare
    	
    begin
    	PLSQL语句
    end;
  

• 对象

  • :old：没更改之前的对象或者一条记录
  • :new：更改之后的对象或者一条记录

• 在触发器中触发语句与伪记录变量的值

触发语句	:old	:new
insert	所有字段都为空(null)	将要插入的数据
update	更新之前该行的值	更新后的值
delete	删除以前改行的值	所有字段都为空(null)

• 分类
  • 语句级触发器：没有for each row 的就是语句级触发器。

    • 示例：学生表student插入新数据，显示欢迎新同学

        create or replace trigger t_hi_i
        after insert on student
        declare
        begin
        	dbms_output.put_line('欢迎新同学');
        end;
      

  • 行级触发器：包含有for each row的就是行级触发器。

    • 示例：不能给员工降薪

        create or replace trigger t_u_sal
        after update of sal on emp
        for each row
        declare
        begin
        	if :old.sal < :new.sal then
        		raise_applicaiton_error(-20001, '不能给员工降薪');
        	end if;
        end;
      

  • 典型应用：实现主键自增
    • 示例：实现student表的主键id自增

    1. 创建序列

        create sequence s_stu;
       

    2. 创建触发器

        create or replace trigger t_pk_i
        before insert on student
        for each row
        declare 
        begin
        	select s_stu.nextval into :new.id from dual;
        end;
        -- 测试 自己测试
       

后记 ：

  所有的数据库测试，如果没有明确指定，都是使用scott用户下的4张表.
  本项目为参考某马视频开发，相关视频及配套资料可自行度娘或者联系本人。上面为自己编写的开发文档，持续更新。欢迎交流，本人QQ:806797785

前端项目源代码地址：https://gitee.com/gaogzhen/vue-leyou
    后端JAVA源代码地址:https://gitee.com/gaogzhen/JAVA