Oracle存储过程、触发器、Mysql与Oracle区别_mysql使用存储过程和触发器来实现数据的业务编辑处理,而oracle使用存储过程触发器-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_53449032/article/details/117932390

文章目录

一、课程目标
二、Oracle存储过程
三、Oracle触发器
四、mysql与Oracle区别

一、课程目标

【理解】Oracle存储过程
【理解】Oracle触发器
【掌握】mysql与oracle的区别

二、Oracle存储过程

2.1 概念

就是将oracle中的块进行命名后存储起来，达到重复使用的过程

2.2 优点

模块化:将程序分解为逻辑模块
可重用性:可以被任意数目的程序调用
可维护性:简化维护操作
安全性:通过设置权限，使数据更安全
执行效率较高

2.3 书写

2.3.1 无参过程书写

CREATE [OR REPLACE] PROCEDURE procedureName[()]
IS|AS --变量的声明部分
BEGIN
--业务
[EXCEPTION --异常处理]
END;

--查询编号为20的部门名与所在地
create or replace procedure  pro1()
 is 
   v_val dept%rowtype;
 begin
    select * into v_val from dept where deptno=20;
   dbms_output.put_line(v_val.dname);
   dbms_output.put_line(v_val.loc);
 end;

--根据部门编号,为员工加薪(部门编号=10,加薪100,部门编号=20,加薪200,部门编号=30,加薪300)
create or replace procedure pro2
is
  cursor c is select * from emp for update;
begin
  for v_emp_rec in c
     loop
        if(v_emp_rec.deptno=10) then
          update emp set sal=sal+100 where empno=v_emp_rec.empno;--current of c 当前游标指向的记录
        elsif v_emp_rec.deptno=20 then
           update emp set sal=sal+200 where current of c;
        elsif v_emp_rec.deptno=30 then
           update emp set sal=sal+300 where current of c;   
        end if;   
     end loop;
  commit;  --提交事务
end;

2.3.2 有参过程书写

IN:用于接受调用程序的值 (输入参数)
OUT:用于向调用程序返回值 (输出参数)
IN OUT：用于接受调用程序的值，并向调用程序返回更新的值(即可以充当输入参数也可以充当输出参数)

create or replace procedure pro3( emp_no in number)
is 
  v_name varchar2(20);
begin
  select ename into v_name from emp where empno=emp_no;
  dbms_output.put_line('name:'||v_name);
  exception when NO_DATA_FOUND then 
    dbms_output.put_line('no name');
end;  

call pro3(7839);

--既有输入参数又有输出参数(接受返回值)
create or replace procedure pro4(emp_no in number,emp_sal out number)
is 
  v_name varchar2(20);
begin
  select ename,sal into v_name,emp_sal from emp where empno=emp_no;
  dbms_output.put_line('名称:'||v_name);
  exception when NO_DATA_FOUND then 
    dbms_output.put_line('没有找到雇员信息..');
end; 

declare 
 emp_no number:=7934;
 emp_sal number;--接收返回值
begin
  pro4(emp_no,emp_sal);
  dbms_output.put_line('out:....'||emp_sal);
end;

--既能输入参数又能输出参数(接受返回值)
create or replace procedure pro5(emp_val in out number)
is 
begin
  select sal into emp_val from emp where empno=emp_val;
  exception when NO_DATA_FOUND then 
    dbms_output.put_line('no');
end; 
 
declare 
 val number:=7934;
begin
  pro5(val);
  dbms_output.put_line('out:....'||val);
end;

2.4 使用

与方法的使用一样，通过过程名(参数)的形式进行使用

execute 存储过程名称(sqlplus中)
```
execute pro1();
```
call 存储过程名称(sqlplus/PLSQLDev中)
```
call pro1();
```
使用块执行
```
begin
  pro1();
end;
```

三、Oracle触发器

3.1 概念

触发器是指被隐含执行的存储过程，它可以使用PL/SQL进行开发
当发生特定事件（如修改表、创建对象、登录到数据库）时，Oracle会自动执行触发器的相应代码

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

3.2 类型

DML触发器：在对数据库表进行DML操作时触发，并且可以对每行或者语句操作上进行触发。
替代触发器：是oracle8专门为进行视图操作的一种触发器
系统触发器：对数据库系统事件进行触发，如启动、关闭等

3.3 书写

3.3.1 触发器组成

触发事件：DML或DDL语句。
触发时间：是在触发事件发生之前(before)还是之后(after)触发
触发操作：使用PL/SQL块进行相应的数据库操作
触发对象：表、视图、模式、数据库
触发频率：触发器内定义的动作被执行的次数。

编写触发器执行代码时，需要注意以下限制

触发器代码的大小不能超过32K。如果确实需要使用大量的代码创建触发器，则应该首先创建过程，然后在触发器中使用CALL语句调用过程
触发器代码只能包含SELECT、INSERT、UPDATE和DELETE语句，
不能包含DDL语句（CREATE、ALTER和DROP）和事务控制语句（COMMIT、ROLLBACK和SAVEPOINT）

3.3.2 语法

CREATE [OR REPLACE] TRIGGER trigger_name
AFTER | BEFORE | INSTEAD OF
[INSERT] [[OR] UPDATE [OF column_list]]
[[OR] DELETE]
ON table_name
[FOR EACH ROW]
begin
pl/sql_block;
end;

--------------------------触发器-------------------------------

create table tbl_dept
(
  deptId number(4) primary key,
  deptName varchar2(20),
  deptNum number(4)
)

create table tbl_emp
(
  empId number(4) primary key,
  empName varchar2(20),
  deptId number(4) references tbl_dept(deptId)
)

insert into tbl_dept(deptId,deptName,deptNum) values(1,'开发部',1);
insert into tbl_dept(deptId,deptName,deptNum) values(2,'测试部',2);

insert into tbl_emp(empId,empName,deptId) values(1,'zhangsan',1);
insert into tbl_emp(empId,empName,deptId) values(2,'zhangsan2',2);
insert into tbl_emp(empId,empName,deptId) values(3,'zhangsan3',2);


--利用触发器,维护部门人数
--（如果新添加员工,让部门人数+1;
--如果删除员工,让部门人数-1;
--如果为员工更改部门:原有部门人数-1，现有部门人数+1）
create or replace  trigger tri1
after insert or delete or update
on tbl_emp



for each row
begin
   if inserting then
     update tbl_dept set deptNum=deptNum+1 where deptId=:new.deptId;--:new代表新数据
   elsif deleting then
     update tbl_dept set deptNum=deptNum-1 where deptId=:old.deptId;--:old代表旧数据  
   elsif updating then
     update tbl_dept set deptNum=deptNum-1 where deptId=:old.deptId;--:old代表旧数据 
     update tbl_dept set deptNum=deptNum+1 where deptId=:new.deptId;--:new代表新数据 
   end if;  
end;  

insert into tbl_emp values(4,'zhaoliu',1);
delete from tbl_emp where empId=4
update tbl_emp set deptId = 1 where empId=2
commit;