Oracle在触发器中自定义异常以及修改列的值

62 阅读 0 评论 41 点赞

我是靠谱客的博主花痴草莓，最近开发中收集的这篇文章主要介绍Oracle在触发器中自定义异常以及修改列的值，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

一：存储过程的定义
1>过程（多次编译多次执行）：
--过程实现计算器
declare p1 number:=1;
p2 number:=2;
sign varchar2(3):='-';
begin
if sign='+' then
syso(p1+p2);
elsif(sign='-' ) then
syso(p1-p2);
elsif(sign='*' ) then
syso(p1*p2);
elsif(sign='/' ) then
syso(p1/p2);
end if;
end;
2>存储过程（一次编译多次执行）
--存储过程的定义
--存储过程执行只是编译的过程如果需要执行存储过程的代码需要在过程中调用
create or replace procedure pro_arthirm(p1 number,p2 number,sign varchar2)
as
--参数的定义
begin
--过程体
if sign='+' then
syso(p1+p2);
elsif(sign='-' ) then
syso(p1-p2)
elsif(sign='*' ) then
syso(p1*p2);
elsif(sign='/' ) then
syso(p1/p2);
end if;
end;
--在plsql中调用存储过程
declare p1 number:=1;
p2 number:=2;
sign varchar2(3):='+';
begin
pro_arthirm(p1,p2,sign);
end;

--在command模式下需要使用
call 过程名称(参数。。。)
execute（exec）过程名称(参数。。。)
show errors 显示存储过程编译之后的错误


3>存储过程参数
/** 参数类型：
IN 输入参数。只能获取它的值不能修改他的值调用设置的值可以在存储过程中查看
OUT 输出参数。只能在过程体中赋值不能查看到传入的值
IN OUT 输入输出参数。可以取它的值，也可以给它赋值

public int arthirm(int p1,int p2,String sign){
int returnNum=5;
if(...){
returnNum=p1+p2
}
return returnNum;
}
**/

create or replace procedure pro_arthirmByReturn(p1 in number,p2 in number,sign in varchar2,returnNum in out number)
as
--参数的定义
rtnNum number;
begin
syso(returnNum);
--过程体
if sign='+' then
returnNum:=(p1+p2);
elsif(sign='-' ) then
returnNum:=(p1-p2);
elsif(sign='*' ) then
returnNum:=(p1*p2);
elsif(sign='/' ) then
returnNum:=(p1/p2);
end if;
end;

declare p1 number:=1;
p2 number:=2;
sign varchar2(3):='+';
returnNumber number:=10;
begin
pro_arthirmByReturn(p1,p2,sign,returnNumber);
syso(returnNumber);
end;
4>查询数据库的对象的三中方式
select count(*) from user_procedures;--当前用户的存储过程
select count(*) from all_procedures; --相同权限的用户所有的存储过程权限下有多少存储过程就输出多少不会有编译出错
select count(*) from dba_procedures; --系统所有的存储如果没有dba的权限会编译出错
5>删除存储过程
drop procedure 存储过程名称
二：函数过程的定义
CREATE [OR REPLACE] FUNCTION 函数名
[(参数名 [IN|OUT|IN OUT] 数据类型[, …])]
RETURN 返回值类型
{IS | AS}
BEGIN
函数的主体
END [函数名];
函数和存储过程的区别在于
1 函数可以返回值存储过程不行
2 函数可以在sql中使用存储过程不行
3 函数是一种特殊的存储过程
例子
create or replace function fun_arthirmbyDeclare(p1 in number,p2 in number,sign in varchar2)
return number
as

resultDNum number;
begin

if sign='+' then
resultDNum:=(p1+p2);
elsif(sign='-' ) then
resultDNum:=(p1-p2);
elsif(sign='*' ) then
resultDNum:=(p1*p2);
elsif(sign='/' ) then
resultDNum:=(p1/p2);
end if;
return resultDNum;
end;
--调用函数
declare p1 number:=1;
p2 number:=2;
sign varchar2(3):='+';
returnNumber number;
begin
returnNumber:=fun_arthirmbyDeclare(p1,p2,sign);
syso(returnNumber);
end;
--删除函数：
DROP FUNCTION 函数名;

区分存储过程和函数在user_procedures
select object_name,object_type from user_objects where object_name in(select object_name from user_procedures)
三：触发器的定义
CREATE [OR REPLACE] TRIGGER 触发器名
[BEFORE | AFTER] 激活触发器的事件(insert,update,delete)
ON 表名
[FOR EACH ROW] -- 指定为行级触发器
[WHEN 触发条件]
BEGIN
主体;
END [触发器名];
/
注意：
多种激活触发器用or来连接：insert or update or delete
在触发器主体语句中可以用“inserting”、“updating”、“deleting”判断激活事件。
在行级触发器中，可以通过:old和:new别名访问列的原值和新值。
举例：
create or replace trigger trg_grade_delete before
delete on tb_grade
FOR EACH ROW
begin
/**
在dml操作中数据的修改是存在新和旧的问题
insert语句只有新的数据
delete语句只有旧的数据
update语句有新和旧的问题
oracle通过var变量的方式存储新旧值
:new
:old 只能使用在行级触发器上
**/
syso('我删除了一行记录班级名称是：'||:old.cname );

end;
delete from tb_grade where cid=3;
演示在触发器中自定义异常以及修改列的值
create table orders(
id number primary key,
sname varchar2(20),
price number,
total number,
totalPrice number
)
--在before触发器中可以修改:new的值 after不行
create or replace trigger trg_orders before
insert on orders
FOR EACH ROW
declare
rollbackException exception; --自定义异常
begin
--判断价格小于0 不满足应该回滚
if :new.price<0 then
raise rollbackException;
end if;
--总价=单价*数量
:new.totalPrice:=:new.price*:new.total;
/**
exception when rollbackException then
syso('判断价格小于0');
**/
end;