Oracle 四种update方法总结
Oracle 四种update方法总结
update_statement ::= UPDATE {table_reference | [THE] (subquery1)} [alias] SET { column_name = {sql_expression | (subquery2)} | (column_name [,column_name]...) = (subquery3)} [,{column_name = {sql_expression | (subquery2)} | (column_name [,column_name]...) = (subquery3) }]... [WHERE {search_condition | CURRENT_OF cursor_name}] [returning_clause]
标准update
update join view
update (select bonus
from employee_bonus b
inner join employees e on b.employee_id = e.employee_id
where e.bonus_eligible = 'N') t
set t.bonus = 0
merge into
MERGE INTO table_name alias1 USING (table | view | sub_query) alias2 ON (join condition) WHEN MATCHED THEN UPDATE table_name SET col1 = col_val1,col2 = col2_val WHEN NOT MATCHED THEN INSERT (column_list) VALUES (column_values);
游标法
快速游标begin
for cur in (table|subquery) loop
update_statement
end loop;
end;
显示游标SET SERVEROUTPUT ON DECLARE CURSOR emp_cursor IS SELECT empno,ename FROM emp; BEGIN FOR Emp_record IN emp_cursor LOOP update_statement; END LOOP; END;
更新方法: 方法 |
场景 |
|