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;
秒客网

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

相关文章
