Oracle使用小結

標籤：ora   flashback   sysdate   nbsp   style   移動   ecif   lte   drop   

誤刪資料庫表解決方案

SQL> alter table test1 enable row movement;//在閃回前必須 啟動行移動功能 否則會報錯誤： ORA-08189: 因為未啟用行移動功能, 不能閃回表

SQL> FLASHBACK TABLE test1 TO TIMESTAMP to_timestamp(‘2013-06-03 15:35:00‘,‘yyyy-mm-dd hh24:mi:ss‘);//注意：恢復點必須是在刪除資料之前 這裡是2013-06-03 15:35:57 之前就可以

 

INSERT INTO BOOKUSER SELECT * FROM BOOKUSER AS OF TIMESTAMP SYSDATE-100/1440 100分鐘之前的

閃回操作
FLASHBACK TABLE T_SCM_COST_UNIT TO BEFORE DROP;

 

 

 

使用UNION ALL把兩個查詢結果放在一張表中，‘入庫‘ as mark，新列，其中資料都是‘入庫’

(SELECT   T0.ID MATERIAL_ID,T0.MATERIAL_NO,T0.NAME MATERIAL_NAME,T0.SPECIFICATION,T1.PLAN_PRICE PLAN_PRICE_IN,T1.AMOUNT AMOUNT_IN, ‘入庫‘ as mark

FROM T_SCM_STORE_IN_DETAIL T1     LEFT JOIN T_EMS_MATERIAL T0     ON T0.ID= T1.MATERIAL_ID

GROUP BY T0.ID,T0.MATERIAL_NO,T0.NAME,T0.SPECIFICATION,T1.PLAN_PRICE,T1.AMOUNT,‘入庫‘)
UNION ALL
(SELECT T0.ID MATERIAL_ID,T0.MATERIAL_NO,T0.NAME MATERIAL_NAME,T0.SPECIFICATION,T2.PLAN_PRICE PLAN_PRICE_OUT,T2.AMOUNT AMOUNT_OUT,‘出庫‘ as mark

FROM  T_SCM_STORE_OUT_DETAIL T2   LEFT JOIN T_EMS_MATERIAL T0 
ON T0.ID= T2.MATERIAL_ID

GROUP BY T0.ID,T0.MATERIAL_NO,T0.NAME,T0.SPECIFICATION,T2.PLAN_PRICE,T2.AMOUNT,‘出庫‘)

Oracle使用小結