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

uil37t8c7c

好久没写过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);