使用check约束,一大插入0或者空值,数据库回出错
SQL> create table prod(
2 goodsid char(10),
3 goodsname char(10),
4 goodsqty char(10),
5 goodsprice number(10,6)
6 );表已创建。SQL>
SQL> alter table prod
2 add constraint check_goodsprice check (goodsprice is not null and goodsprice<>0);表已更改。SQL>
SQL> insert into prod values('1','a','a',0);
insert into prod values('1','a','a',0)
*
ERROR 位于第 1 行:
ORA-02290: 违反检查约束条件 (SYSTEM.CHECK_GOODSPRICE)
SQL> create table prod(
2 goodsid char(10),
3 goodsname char(10),
4 goodsqty char(10),
5 goodsprice number(10,6)
6 );表已创建。SQL>
SQL> alter table prod
2 add constraint check_goodsprice check (goodsprice is not null and goodsprice<>0);表已更改。SQL>
SQL> insert into prod values('1','a','a',0);
insert into prod values('1','a','a',0)
*
ERROR 位于第 1 行:
ORA-02290: 违反检查约束条件 (SYSTEM.CHECK_GOODSPRICE)
before insert on goods
for each row
declare
e_null exception;
begin
if :new.goodsprice is null then
raise e_null;
end if;
select id.nextval into :new.goodsid from dual;
exception
when e_null then
--出错处理(检查当没插入goodsid,唯范唯一性);
end;
/
before insert on goods
for each row
declare
e_null exception;
begin
if :new.goodsprice is null and :new.goodsprice=0 then
raise e_null;
end if;
select id.nextval into :new.goodsid from dual;
exception
when e_null then
--出错处理(检查当没插入goodsid,违反唯一性);
end;
/
使用insert after 行级触发器,
create trigger name_tri_update
before update on goods
~~~~~~
for each row
declare
e_null exception;
begin..........
如果是一人完成记录的话,最好用maohaisheng() 的方法。
before update on goods
for each row
declare
b number;--判定是否数字型
e_null exception;
begin
if :new.goodsprice is null and :new.goodsprice=0 then
raise e_null;
end if;
b:=:new.goodsprice;
exception
when e_null then
dbms_output.put_line('此处不能为空');
when others then
dbms_output.put_line('此处必需填数字');
end;
/
这样即使是错误的数据,也会被提交。
应该在异常处理部分,再次引发异常或回滚,修改如下:create trigger name_tri_update
before update on goods
for each row
declare
b number;--判定是否数字型
e_null exception;
begin
if :new.goodsprice is null and :new.goodsprice=0 then
raise e_null;
end if;
b:=:new.goodsprice;
exception
when e_null then
begin
dbms_output.put_line('此处不能为空');
raise; --rollback;
end;
when others then
begin
dbms_output.put_line('此处必需填数字');
raise; --rollback;
end;end;
raise e_null;
end if;要改成
if :new.goodsprice is null OR :new.goodsprice=0 then
raise e_null;
end if;应该是或条件!
当单击按钮事件,就调用此过程,而p_goodsid参数用一个方法去捕捉它.
create procedure name_pro(p_goodsid in varchar2,p_goodsprice in number)
as
e_null exception;
begin
if p_goodsprice is not null or lengthb(p_goodsprice)>0 then
update goods set goodsprice=p_goodsprice where goodsid=p_goodsid;
else
raise e_null;
end if;
exception
when e_null then
dbms_output.put_line('单价不能为空');
end;
/