CREATE OR REPLACE PROCEDURE synchronous_pm_t_material AS PRAGMA AUTONOMOUS_TRANSACTION; ---打开自动事务机制,此处的分号别忘了
last_version VARCHAR2(14);
current_version VARCHAR2(14);
CURSOR sync_row_cursor(p_last_version varchar2,p_current_version varchar2)
IS
SELECT OBJID,OBJVERSION,PART_NO AS MATERIAL_NO,DESCRIPTION AS MATERIAL_NAME,REQUISITION_NO,LINE_NO,STATE AS PROCESS_TYPE,1 AS AREA,
REQUESTED_QTY AS AMOUNT,BUY_UNIT_MEAS AS UNIT,TYPE_DESIGNATION AS SPEC,DIM_QUALITY AS IMAGE_NO,ACCOUNTING_GROUP AS ACCOUNT,
REQUISITIONER_ORG AS ORG,CONTACT,BUYER_CODE,DEL_DES_CODE,NOTE_TEXT AS NOTES,VENDOR_NO
FROM GZB_EPMS_material WHERE OBJVERSION > p_last_version AND OBJVERSION <= p_current_version
UNION
SELECT OBJID,OBJVERSION,PART_NO AS MATERIAL_NO,DESCRIPTION AS MATERIAL_NAME,REQUISITION_NO,LINE_NO,STATE AS PROCESS_TYPE,2 AS AREA,
REQUESTED_QTY AS AMOUNT,BUY_UNIT_MEAS AS UNIT,TYPE_DESIGNATION AS SPEC,DIM_QUALITY AS IMAGE_NO,ACCOUNTING_GROUP AS ACCOUNT,
REQUISITIONER_ORG AS ORG,CONTACT,BUYER_CODE,DEL_DES_CODE,NOTE_TEXT AS NOTES,VENDOR_NO
FROM TG_EPMS_material WHERE OBJVERSION > p_last_version AND OBJVERSION <= p_current_version;
BEGIN
SELECT last_SYNCHRONOUS_TIME INTO last_version FROM (SELECT (to_char(SYNCHRONOUS_TIME,'yyyymmddhhmiss')) AS last_SYNCHRONOUS_TIME FROM PM_T_SYNC_TIME ORDER BY SYNCHRONOUS_TIME DESC) WHERE ROWNUM <= 1;
SELECT to_char(SYSDATE,'yyyymmddhhmiss') INTO current_version FROM dual;
FOR sync_row IN sync_row_cursor(last_version,current_version)
LOOP
IF (fun_material_haved(sync_row.objid)=0) THEN
INSERT INTO pm_t_material(MATERIAL_ID,material_no,material_name,requisition_no,line_no,process_type,area,amount,unit,spec,account,org,contact
,buyer_code,del_des_code,notes,vendor_no,image_no,objid,objversion,create_type,create_date,update_date) VALUES (
seq_pm_t_material.nextval,sync_row.material_no,sync_row.material_name,sync_row.requisition_no,sync_row.line_no,coverToProcessType(sync_row.process_type),
sync_row.area,sync_row.amount,sync_row.unit,sync_row.spec,sync_row.account,sync_row.org,sync_row.contact,coverToBuyerCode(sync_row.buyer_code),sync_row.del_des_code,
sync_row.notes,sync_row.vendor_no,sync_row.image_no,sync_row.objid,sync_row.objversion,1,to_date(current_version,'yyyy-mm-dd hh:mi:ss'),to_date(current_version,'yyyy-mm-dd hh:mi:ss'));
ELSE
UPDATE pm_t_material SET material_no= sync_row.material_no,material_name=sync_row.material_name,requisition_no=sync_row.requisition_no,
line_no=sync_row.line_no,process_type=coverToProcessType(sync_row.process_type),area=sync_row.area,amount=sync_row.amount,unit=sync_row.unit,spec=sync_row.spec,
account=sync_row.account,org=sync_row.org,contact=sync_row.contact,buyer_code=coverToBuyerCode(sync_row.buyer_code),del_des_code=sync_row.del_des_code,
notes=sync_row.notes,vendor_no=sync_row.vendor_no,image_no=sync_row.image_no,objversion=sync_row.objversion,update_date=to_date(current_version,'yyyy-mm-dd hh:mi:ss')
WHERE objid = sync_row.objid;
END IF;
END LOOP;
INSERT INTO PM_T_SYNC_TIME(SYNCHRONOUS_TIME) VALUES(to_date(current_version,'yyyy-mm-dd hh:mi:ss'));
COMMIT; --提交别忘了,要不然执行完成就自动回滚了
END synchronous_pm_t_material;
分享到:
相关推荐
解决 Oracle 杀死死锁进程 Oracle 杀死死锁进程是数据库管理员经常遇到的问题,本文将介绍解决 Oracle 杀死死锁进程的方法。 在 Oracle 中,死锁是指两个或多个会话在等待对方释放资源,从而导致互相阻塞的情况。...
执行查询语句查询Oracle是否有死锁,以及叫你如何解锁。
《Oracle PL/SQL专家指南-高级PL/SQL解决方案的设计与开发》是一本深入探讨Oracle数据库中的PL/SQL编程的专业书籍。PL/SQL是Oracle数据库特有的编程语言,它结合了SQL的查询能力与过程式编程语言的功能,使得数据库...
Oracle 8 PL/SQL程序设计是一门深度探讨Oracle数据库中PL/SQL编程语言的应用技术的主题。...通过深入阅读和实践,你可以提升对Oracle数据库管理和应用开发的能力,从而更好地应对企业级的数据库解决方案需求。
清除锁表是解决并发控制中的死锁问题,通过DBA_LOCKED_OBJECTS视图可以查看锁定对象,然后使用ALTER SYSTEM KILL SESSION来解除锁。 二、数据库备份与恢复 数据库备份是保障数据安全的重要环节,Oracle提供完整备份...
6. **复制与高可用性**(Les13_chinese.ppt):讨论Oracle的复制技术,如逻辑复制(DBLink、Materialized Views)、物理复制(Dataguard、GoldenGate),以及高可用性解决方案,如RAC(实时应用集群)和Exadata。...
FROM ...@dblink`语句进行分区数据导入,这在多数据库环境中尤其有用,可提高数据迁移效率。 6. **死锁检测与处理**:`V$LOCKED_OBJECT`视图用于查找数据库中的死锁信息,结合`V$SESSION`和`V$SQLTEXT`,可以识别...
文中具体演示了基于触发器的用户表复制全过程,包括创建用户、表、dblink、复制组等步骤,并展示了DML和DDL同步测试。基于物化视图的复制部分则着重于创建物化视图日志、全量刷新等操作。最后指出高级复制虽有局限性...