oracle 怎么触发异常,oracle触发器及异常处理 简单例子

oracle触发器及异常处理 简单例子

好久没写过oracle触发器了，今天再次写到，记录下:

create sequence person_seq

start with 1

increment by 1

order                     --按顺序

nocycle                 --不循环不重复

;

insert into person values(person_seq.nextval,null,'iteye);

delete from person;

update person set user_name ='zwllxs6' where id = 3;

---触发器

create or replace trigger person_tr before insert or update or delete on person

for each row

Declare

can_not_update_exception  EXCEPTION;

begin

dbms_output.put_line('testest');

if inserting then

dbms_output.put_line('inserting');

end if;

if updating then

dbms_output.put_line('updating');

dbms_output.put_line('old: '||:old.user_name);

dbms_output.put_line('new: '||:new.user_name);

if(:old.user_name<>:new.user_name) then

dbms_output.put_line('不一样，不能更新');

Raise_application_error(-20000,'不一样，不能更新');

end if;

end if;

if deleting then

dbms_output.put_line('deleting');

end if;

end;

java代码test:

Class.forName("oracle.jdbc.driver.OracleDriver");

Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "******");

// 创建状态

Statement stmt = conn.createStatement();

// 执行SQL语句，返回结果集

ResultSet rs = stmt.executeQuery("SELECT * FROM person");

while (rs.next()) {

int id = rs.getInt("id");

String userName = rs.getString("user_name");

System.out.println("id: "+id);

System.out.println("userName: "+userName);

}

int num=stmt.executeUpdate("update person set user_name ='zwllxs5' where id = 3");

System.out.println("num: "+num);