ORACLE链接SQLSERVER数据库数据操作函数范例

ORACLE链接SQLSERVER数据库数据操作函数范例

create or replace function FUN_NAME(LS_DJBH IN varchar2 ,LS_ITM varchar2 ,LS_Type int)
return varchar2 
as
PRAGMA AUTONOMOUS_TRANSACTION;--有DML操作必须添加，如增删改查

/*
名称：FUN_NAME
参数：LS_DJBH 单据编号,LS_ITM 项次,LS_Type 操作类型
作用：LS_Type 为1 时同步生产日工单数据至ttt系统的任务单，LS_Type 为2 时取消ttt系统中同步的日工单数据
版本：V0001
V0001 修改人：博客燕，修改日期：20191010，修改内容：创建函数
*/
LS_ORDERID varchar2(200); --订单编号
LS_JH_DD varchar2(200); --计划日期
LS_MAX_TaskMasterOID INT; -- Task_Main表主键ID
LS_OrderAID varchar2(200);--订单编号
LS_ReceiveDateOID INT; --计划日期转换为int后为接单日期
LS_MAX_TaskDetailOID INT;--Task_Detail表主键ID
LS_ProductOID INT;--产品ID
LS_PRD_NO varchar2(200); --产品编号
LS_QTY INT;--数量
LS_Output varchar2(200);--返回值
LS_PDID INT;--用于判断
LS_PJ VARCHAR(200); --拼接字符串
begin
LS_PJ:=NVL(LS_DJBH,'')||'-'||NVL(LS_ITM,'');--必须提前拼接，Oracle和SQLSERVER拼接语法不一致导致报错
if LS_Type=1 then
  LS_PDID:=0;
  SELECT nvl(MAX("TaskMasterOID"),0) into LS_PDID FROM SQL_TBMAIN@链接数据库 WHERE "TaskAID"=LS_PJ; 
  if LS_PDID=0 then --如果INET中已经存在相同单据号和项次的记录，则不重新插入
    SELECT to_char(JH_DD,'YYYY-MM-DD') INTO LS_JH_DD  FROM ORACLE_TB_HEAD where DJBH=LS_DJBH;
    select NVL(max("TaskMasterOID"),0)+1 into LS_MAX_TaskMasterOID from SQL_TBMAIN@链接数据库;--已存在的最大值+1
    select (JH_DD-date'0001-01-01')-1 into LS_ReceiveDateOID from ORACLE_TB_HEAD where DJBH=LS_DJBH; --Oracle和SQLserver日期计算相差2，所以此处-1
    SELECT ORDERID into LS_OrderAID FROM ORACLE_TB_DETAIL WHERE DJBH=LS_DJBH AND ITM=LS_ITM ;
    INSERT INTO  dbo.SQL_TBMAIN@链接数据库
           ("TaskMasterOID"
          ,"TaskAID"
          ,"OrderAID"
          ,"ReceiveDateOID"
          ,"FinishStatus"
          ,"AssignStatus"
          ,"DataResource"
          ,"Remarks")
    VALUES (LS_MAX_TaskMasterOID,LS_PJ,LS_OrderAID,LS_ReceiveDateOID,0,0,0,'[ERP系统导入]');
    COMMIT;
    SELECT PRD_NO into LS_PRD_NO FROM ORACLE_TB_DETAIL WHERE DJBH=LS_DJBH AND ITM=LS_ITM ;
    SELECT  QTY_JH INTO LS_QTY FROM ORACLE_TB_DETAIL WHERE DJBH=LS_DJBH AND ITM=LS_ITM ;
    SELECT "OID" INTO LS_ProductOID FROM Product@INET where "AID"=LS_PRD_NO;
    INSERT INTO SQL_TBDetail@链接数据库
           ("TaskMasterOID"
           ,"TaskDetailItem"
           ,"ProductOID"
           ,"Quantity"
           ,"PreEndDateOID"
           ,"Remarks")
    VALUES(LS_MAX_TaskMasterOID,1,LS_ProductOID,LS_QTY,LS_ReceiveDateOID,'[ERP系统导入]');
    COMMIT;--对链接的SQLSERVER和ORACLE之间必须提交，否则会提示：要求事务处理或保存点回退
    
    update ORACLE_TB_DETAIL set INET_TB=1 where to_char(ITM)=LS_ITM AND DJBH=LS_DJBH;
    COMMIT;
    LS_Output:='同步成功！';
  ELSE
    update ORACLE_TB_DETAIL set INET_TB=1 where to_char(ITM)=LS_DJBH AND DJBH=LS_DJBH;
    COMMIT;
    LS_Output:='项次：'||LS_ITM||' 已经存在同步记录！';
  END IF;
  
ELSE   
    select min("TaskMasterOID")  into LS_MAX_TaskMasterOID from SQL_TBMAIN@链接数据库 WHERE "TaskAID"=LS_PJ;
    LS_PDID:=0;
    SELECT nvl(MAX("TaskMasterOID"),0) into LS_PDID FROM SQL_TBMAIN@链接数据库 WHERE nvl("AssignStatus",0)=1 and "TaskMasterOID"=LS_MAX_TaskMasterOID; 
    IF LS_PDID>0 then
        LS_Output:='项次为：'||NVL(LS_ITM,'')||'的记录在INET系统中已经确认，不能取消同步，请联系车间人员取消确认！';
    else 
        delete from SQL_TBDetail@链接数据库 where "TaskMasterOID"=LS_MAX_TaskMasterOID;
        delete from SQL_TBMAIN@链接数据库 where  "TaskMasterOID"=LS_MAX_TaskMasterOID;
        COMMIT;--对链接的SQLSERVER和ORACLE之间必须提交
        
        update ORACLE_TB_DETAIL set INET_TB=0 where to_char(ITM)=LS_ITM AND DJBH=LS_DJBH;
        COMMIT;--对链接的SQLSERVER和ORACLE之间必须提交
        LS_Output:='取消同步成功！';
    end if ;
END IF ;
COMMIT;
return LS_Output;
end FUN_NAME;

 Oralce 访问链接的SQLSERVER 数据库表时，如果在where 语句里用了 查询作为条件则速度会非常慢，如下代码用时 66秒，如果直接查询 0.03 秒

SELECT * FROM ProductionLog@INET WHERE ROWNUM<10 and "BeginDateOID" = (select 737378-1 from dual)
ORDER BY "BeginDateOID" DESC;