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;