不用使用触发器: MERGE INTO table_name table_alias USING (table|view|sub_query) alias ON (join condition) WHEN matched THEN UPDATE SET col1 = col_val1, col2 = col2_val WHEN NOT matched THEN INSERT (column_list) VALUES (column_values); 比如说: MERGE INTO copy_emp c USING employees e ON (c.employee_id = e.employee_id) WHEN matched THEN UPDATE SET c.first_name = e.first_name,c.last_name = e.last_name,...,c.department_id = e.department_id WHEN NOT matched THEN INSERT VALUES(e.employee_id, e.first_name, e.last_name,…,e.manager_id,e.department_id);
update .... set ... exception when ... then Insert into ...
create or replace trigger tri_name(触发器名称) after update of col_name(要更新表的列名) on table_name(要更新的表名) for each row begin if(:new.col_name=:old.col_name) (这里判断如果更新后的值和更新前的值是一样的,就判定为失败, 不知道合不合你要求) then insert into table_name2(插入记录的表名) values(...); end if; end;
谢谢大家!我找到了一种利用隐式游标sql%found属性的方法: declare v_idno test01.idno%type := &idno; begin update test01 set name = '中国' where idno = v_idno; if sql%found then dbms_output.put_line('语句执行成功!'); else insert into test01(idno) values(v_idno); end if; end;
呵呵,最近找到了一种利用隐式游标sql%found属性的方法~~~~ declare v_idno table_name.idno%type := &idno; v_name table_name.name%type := '&name'; begin update test01 set name = v_name where idno = v_idno; if sql%found then dbms_output.put_line('语句执行成功!'); else insert into test01(idno,name) values(v_idno,v_name); end if; end;
MERGE INTO table_name table_alias
USING (table|view|sub_query) alias ON (join condition)
WHEN matched THEN
UPDATE SET col1 = col_val1, col2 = col2_val
WHEN NOT matched THEN
INSERT (column_list) VALUES (column_values);
比如说:
MERGE INTO copy_emp c
USING employees e ON (c.employee_id = e.employee_id)
WHEN matched THEN
UPDATE SET c.first_name = e.first_name,c.last_name = e.last_name,...,c.department_id = e.department_id
WHEN NOT matched THEN
INSERT VALUES(e.employee_id, e.first_name, e.last_name,…,e.manager_id,e.department_id);
exception when ... then
Insert into ...
为对象赋值前必需初始化对象。对应ORA-06530错误。CASE_NOT_FOUND
使用CASE语句时在WHEN子句中没有包含必需的条件分支,并且没有包含ELSE语句。
对应ORA-06592错误。COLLECTION_IS_NULL
给集合元素赋值前,必需初始化集合元素。对应ORA-06531错误。CURSOR_ALRADY_OPEN
重新打开已经打开的游标。对应ORA-06511错误。DUP_VAL_ON_INDEX
在惟一索引所对应的列上键入重复值。对应ORA-00001错误。INVALID_CURSOR
试图操作不合法的游标。对应ORA-01001错误。INVALID_NUMBER
内嵌SQL语句不能有效的将字符转换成数字。对应ORA-01722错误。NO_DATA_FOUND
执行SELECT INTO未返回行。对应ORA-01403错误。TOO_MANY_ROWS
执行SELECT INTO语句时,返回超过一行。对应ORA-01422错误。ZERO_DIVIDE
PL/SQL块中,使用数字除0,对应ORA-01476错误。SUBSCRIPT_BEYOND_COUNT
元素下标超出嵌套表或VARRAY元素的范围。对应ORA-06533错误。SUBSCRIPT_OUTSIDE_LIMIT
使用嵌套表或VARRAY元素时,元素下标为负。对应ORA-06532错误。VALUE_ERROR
PL/SQL中赋值操作时,变量长度不足以容纳实际数据,或尝试将无效的字符串转换成数据。
对应ORA-06502错误。LOGIN_DENIED
连接到ORACLE数据库时,用户名/密码不正确。对应ORA-01017错误。NOT_LOGGED_ON
应用程序没有连接到数据库。对应ORA-01012错误。PROGRAM_ERROR
PL/SQL内部问题。对应ORA-06510错误。ROWTYPE_MISMATCH
执行赋值操作时,宿主游标变量和PL/SQL游标变量返回类型不兼容。对应ORA-06504错误。SELF_IS_NULL
在NULL实例上调用成员方法。对应ORA-30625错误。STORAGE_ERROR
PL/SQL块运行时超出了内存空间或者内存被破坏。SYS_INVALID_ROWID
将字符串转变为ROWID时没有使用有效的字符串。对应ORA-01410错误。TIMEOUT_ON_RESOURCE
ORACLE在等待资源时超时。对应ORA-00051错误。TRANSACTION_BACKED_OUT
由于死锁提交被退回 对应ORA-006 错误
others
其他异常
after update of col_name(要更新表的列名) on table_name(要更新的表名)
for each row
begin
if(:new.col_name=:old.col_name) (这里判断如果更新后的值和更新前的值是一样的,就判定为失败,
不知道合不合你要求)
then insert into table_name2(插入记录的表名)
values(...);
end if;
end;
declare
v_idno test01.idno%type := &idno;
begin
update test01 set name = '中国' where idno = v_idno;
if sql%found then
dbms_output.put_line('语句执行成功!');
else
insert into test01(idno) values(v_idno);
end if;
end;
declare
v_idno table_name.idno%type := &idno;
v_name table_name.name%type := '&name';
begin
update test01 set name = v_name where idno = v_idno;
if sql%found then
dbms_output.put_line('语句执行成功!');
else
insert into test01(idno,name) values(v_idno,v_name);
end if;
end;