一:insert
1.从一个表向另一个表复制行
insert into temp2 (id, month ,year, amount)
select 10, month, year, amount
from temp
where id = 1;
二:RETURNING
returning子句返回聚合函数的计算结果
variable amount_temp number
update temp2
set amount = amount * 2
returning sum(amount) into :amount_temp
print amount_temp
AMOUNT_TEMP
-------------
240
三:数据库事务
要永久性的记录事务,需要提交commit语句。
事务结束:
1.commit和rollback语句
2.执行DDL语句
3.执行一条DCL语句
4.断开与数据库的连接
四:查询闪回
使用dbms_flashBack
错误的提交了修改操作,并想查看各行原来的值。
CONNECT sys/change_on_install as sysdba
grant execute on sys.dbms_flashback to store;
connect store/store_password
select type, month, year,amount
from temp2
where year = '2017';
TYPE MONTH YEAR AMOUNT
--------------------------
1 1 2017 240
update temp2
set amount = amount / 2
select type, month, year,amount
from temp2
where year = '2017';
TYPE MONTH YEAR AMOUNT
--------------------------
1 1 2017 120
--查询10分钟之前的状态
execute dbms_flashback.enable_at_time(sysdate - 10/1440)
connect store/store_password
select type, month, year,amount
from temp2
where year = '2017';
TYPE MONTH YEAR AMOUNT
--------------------------
1 1 2017 240
禁用闪回操作:
execute dbms_flashBack.disable();
select type, month, year,amount
from temp2
where year = '2017';
TYPE MONTH YEAR AMOUNT
--------------------------
1 1 2017 120