You need to enable JavaScript to run this app.
优惠活动
大模型
产品
解决方案
定价
更多

执行MSSQL存储过程时PB12.5采购模块无法打开的问题

avatar
数据老陈
2026-8-8
问题描述

我们有一个MSSQL存储过程，用于将合同数据从SAP对接的中间数据库同步到我方数据库，包含循环逻辑和多表操作。同时部署了PB12.5桌面应用，涵盖采购、船员管理、员工等模块。

现象：执行该存储过程耗时约2分钟（依数据量而定），执行期间无法打开采购窗口，必须等存储过程完成才能打开；但员工窗口可正常打开。

已排查内容

  • 采购模块使用table1和table2，当前存储过程未操作这两张表（后续扩展会用到）
  • 存储过程执行期间，可正常执行select * from table1查询采购模块表
  • 存储过程未锁定任何表
  • 多台PC测试均存在相同问题
  • 后续计划扩展存储过程，增加更多表和多游标循环

尝试添加事务控制语句（Begin tran和commit Tran），但未解决问题。

存储过程部分代码 

alter PROC [spectwosuite].[CST_GENERATE_IPurchase_contract] 
as begin

SET NOCOUNT ON;
/**/

--truncate table spectwosuite.CUSTOM_SAP_CONTRACT_ITEM;
--truncate table spectwosuite.CUSTOM_SAP_CONTRACT_header;
--truncate table spectwosuite.CUSTOM_SAP_PROC_LOG;

--Variable declaration
DECLARE @MATERIALNO VARCHAR(20);
DECLARE @PURCHASEDOCNO VARCHAR(20);
DECLARE @PORTID NUMERIC(15);
DECLARE @PRICE DECIMAL(11,2);
DECLARE @PKID NUMERIC(16);

DECLARE @STOCKTYPEID NUMERIC(15);
DECLARE @STOCKTYPECODE VARCHAR(50);
DECLARE @STNAME VARCHAR(50);
DECLARE @UNITID NUMERIC(15);
DECLARE @BUSFLOWID NUMERIC(15);
DECLARE @BUSSTATUSID NUMERIC(15);

DECLARE @ITEMNO VARCHAR(5);
DECLARE @SUBITEMNO VARCHAR(10);
DECLARE @STOCKDISID NUMERIC(15);

DECLARE @PURCONTRACTID NUMERIC(15);
DECLARE @REVISIONNO NUMERIC(15);
DECLARE @DESCR NVARCHAR(100);
DECLARE @ADDRESSID NUMERIC(15);
DECLARE @DELTERMSID NUMERIC(15);
DECLARE @PAYTERMSID NUMERIC(15);
DECLARE @VALIDSTART DATETIME;
DECLARE @VALIDEND DATETIME;
DECLARE @CURRENCY NCHAR(3);
DECLARE @PRODUCTGROUPID NUMERIC(15);
DECLARE @PURCONTRACTPRODUCTGROUPID NUMERIC(15);
DECLARE @MATERIALGRP NVARCHAR(20);
DECLARE @NETPRICE NUMERIC(16,6);
DECLARE @BASEPRICE NUMERIC(16,6);
DECLARE @PRODUCTGROUPLINEID NUMERIC(15);
DECLARE @SECTION VARCHAR(20);
DECLARE @PUREXIST NUMERIC(1);
DECLARE @HEADERPKID NUMERIC(16);

DECLARE @ITEMPRICE NUMERIC(16,6);
DECLARE @BASEITEMPRICE NUMERIC(16,2);
DECLARE @PURCONTRACTVARIABLEID NUMERIC(15);
DECLARE @FACTORVALUE NUMERIC(9,4);
DECLARE @ITEMPERCENTAGE NUMERIC(15,2);
DECLARE @ITEMPRICEDIFF1 NUMERIC(15,2);
DECLARE @ITEMPRICEDIFF2 NUMERIC(15,2);
DECLARE @ITEMPKID NUMERIC(16);

/*CONT1: Transfering all the portid and purchase document number to different table */


--PRINT 'CONT1 START'
--PRINT GETDATE();

BEGIN TRY
--BEGIN TRANSACTION

--BEGIN
    SET @SECTION='CONT1';
        BEGIN TRANSACTION CONT1
            INSERT INTO SAP_INTERFACE.spectwosuite.CUSTOM_SAP_CONTRACT_PORT
                       (PORTID
                       ,PURCHASE_DOC_NO
                        )
     
                SELECT DISTINCT PORTID,PURCHASE_DOC_NO FROM SAP_INTERFACE.spectwosuite.CUSTOM_SAP_CONTRACT_ITEM WITH(NOLOCK) WHERE IMPORT_STATUS IS NULL and DELETION_IND  NOT IN('L','1')  
                and portid>0 and MATERIAL_NO<>''  and  FACTOR>0 and
                       not exists (select PORTID from SAP_INTERFACE.spectwosuite.CUSTOM_SAP_CONTRACT_PORT where PORTID=SAP_INTERFACE.spectwosuite.CUSTOM_SAP_CONTRACT_PORT.PORTID 
                       and PURCHASE_DOC_NO =SAP_INTERFACE.spectwosuite.CUSTOM_SAP_CONTRACT_PORT.PURCHASE_DOC_NO) ORDER BY PURCHASE_DOC_NO,PORTID ASC;
            
        COMMIT TRANSACTION CONT1
--END

----INSERT INTO spectwosuite.CUSTOM_SAP_PROC_LOG
----           (STATUS
----           ,DESCRIPTION
----           ,PROCESSEDDATE
----           ,RECORDCOUNT)
----     VALUES
----           (1,
----           'CONT1'
----           ,GETDATE()
----           ,@@ROWCOUNT
----           );

--PRINT 'CONT1 END'
--PRINT GETDATE();
/* CONT1 END */

/*CONT2 Inserting new item which one dont have price in all ports*/

/*Variable declaration*/

--PRINT 'CONT2 START'
--PRINT GETDATE();

    --  BEGIN 
                SET @SECTION='CONT2';
                
                --SELECT DISTINCT CAST(MATERIAL_NO AS VARCHAR(20))AS MATERIAL_NO,PURCHASE_DOC_NO FROM SAP_INTERFACE.spectwosuite.CUSTOM_SAP_CONTRACT_ITEM WITH(NOLOCK) WHERE  PURCHASE_DOC_NO='4600000820' and IMPORT_STATUS IS NULL  and MATERIAL_NO<>'' and DELETION_IND NOT IN('L','1') and len(material_no)>0 and factor>0 and portid>0 ORDER BY MATERIAL_NO,PURCHASE_DOC_NO ASC;
                DECLARE CONT2CR CURSOR FOR
                SELECT DISTINCT CAST(MATERIAL_NO AS VARCHAR(20))AS MATERIAL_NO,PURCHASE_DOC_NO FROM SAP_INTERFACE.spectwosuite.CUSTOM_SAP_CONTRACT_ITEM WITH(NOLOCK) WHERE IMPORT_STATUS IS NULL  and MATERIAL_NO<>'' and DELETION_IND NOT IN('L','1') and len(material_no)>0 and factor>0 and portid>0 ORDER BY MATERIAL_NO,PURCHASE_DOC_NO ASC;
                OPEN CONT2CR
                FETCH NEXT FROM CONT2CR INTO @MATERIALNO,@PURCHASEDOCNO

                WHILE @@FETCH_STATUS=0
                    BEGIN                               
                            BEGIN TRAN CONT2        
                                        select @PKID=MIN(pk_id),@PRICE=MIN(NET_PRICE) from SAP_INTERFACE.spectwosuite.CUSTOM_SAP_CONTRACT_ITEM WITH(NOLOCK) WHERE MATERIAL_NO=@MATERIALNO AND PURCHASE_DOC_NO=@PURCHASEDOCNO and  IMPORT_STATUS IS NULL and DELETION_IND NOT IN('L','1') and factor>0 and portid>0;
                                    /*Retreiving port details table*/
                                        DECLARE CONT2PORTCR CURSOR FOR
                                        SELECT DISTINCT portid FROM SAP_INTERFACE.spectwosuite.custom_sap_contract_port WITH(NOLOCK) WHERE (SAP_INTERFACE.spectwosuite.custom_sap_contract_port.purchase_doc_no = @PURCHASEDOCNO) and ( custom_sap_contract_port.portid > 0 )  ;
                                        OPEN CONT2PORTCR
                                        FETCH NEXT FROM CONT2PORTCR INTO @PORTID
                                        WHILE @PORTID>0
                                        BEGIN
                            

                                            IF @PORTID>0 
                                            BEGIN 
                                                BEGIN TRAN
                                                    /*Insert Script*/
                                                    SET @SECTION='ITEM_ADDITION';                               

                                                    IF (select COUNT(*) from SAP_INTERFACE.spectwosuite.CUSTOM_SAP_CONTRACT_ITEM WITH(NOLOCK) where  MATERIAL_NO=CAST(@MATERIALNO AS varchar(20)) AND PORTID=@PORTID and PURCHASE_DOC_NO=@PURCHASEDOCNO and IMPORT_STATUS IS NULL and DELETION_IND NOT IN('L','1') and factor>0 and portid>0)<=0
                                                    BEGIN                                            
                                                        INSERT INTO SAP_INTERFACE.spectwosuite.CUSTOM_SAP_CONTRACT_ITEM
                                                                                                    (DELETION_IND,PURCHASE_DOC_NO,ITEM_NO,MATERIAL_NO,SHORT_TEXT,MATERIAL_GRP,QTY,UOM,NET_PRICE,PORTID,TEMPPORTID,FACTOR)
                                                        select DELETION_IND,PURCHASE_DOC_NO,ITEM_NO,MATERIAL_NO,SHORT_TEXT,MATERIAL_GRP,QTY,UOM,NET_PRICE,@PORTID,1252,FACTOR  from SAP_INTERFACE.spectwosuite.CUSTOM_SAP_CONTRACT_ITEM WITH(NOLOCK) WHERE pk_id=@PKID and DELETION_IND<>'1'  and IMPORT_STATUS IS NULL;
                                                    END

                                                    SET @PORTID=0;
                                                COMMIT TRAN
                                            END
                                            FETCH NEXT FROM CONT2PORTCR INTO @PORTID
                                        END
                                        CLOSE CONT2PORTCR
                                        DEALLOCATE CONT2PORTCR
                            COMMIT TRAN CONT2
                        FETCH NEXT FROM CONT2CR INTO @MATERIALNO,@PURCHASEDOCNO
                    END

                    CLOSE CONT2CR
                    DEALLOCATE CONT2CR



--END 

--INSERT INTO spectwosuite.CUSTOM_SAP_PROC_LOG
--           (STATUS
--           ,DESCRIPTION
--           ,PROCESSEDDATE
--           ,RECORDCOUNT)
--     VALUES
--           (1,
--         'CONT2'
--         ,GETDATE()
--         ,@@ROWCOUNT
--         );

--PRINT 'CONT2 END'
--PRINT GETDATE();

/*CONT2 END*/

        /*CONT3*/

--PRINT 'CONT3 START'
--PRINT GETDATE();

SET @SECTION='CONT3';   
            BEGIN TRAN CONT3
                INSERT INTO spectwosuite.CUSTOM_SAP_CONTRACT_HEADER
                           (TABLEPKID
                           ,PURCHASE_DOC_NO
                           ,COMPANY_CODE
                           ,CREATED_ON_DATE
                           ,VENDOR_CODE
                           ,PAYMENT_TERMS
                           ,PURCHASE_GRP
                           ,VALIDITY_START
                           ,VALIDITY_END
                           ,YOUR_REFERENCE
                           ,OUR_REFERNCE
                           ,TARGET_AMOUNT
                           ,CURRENCY          
                           ,DESCRIPTION
                           ,SHIPCODE
                           ,STATUS           
                           ,CREATEDATE
                           ,VALIDATESTART
                           ,VALIDATEEND
                           ,INTERNAL_STATUS
                           ,INTERNAL_MESSAGE
                           )
                    SELECT 
                        PK_ID
                       ,PURCHASE_DOC_NO
                      ,COMPANY_CODE
                      ,CREATED_ON_DATE
                      ,VENDOR_CODE
                      ,PAYMENT_TERMS
                      ,PURCHASE_GRP
                      ,VALIDITY_START
                      ,VALIDITY_END
                      ,YOUR_REFERENCE
                      ,OUR_REFERNCE
                      ,TARGET_AMOUNT
                      ,CURRENCY
                      ,DESCRIPTION
                      ,SHIPCODE
                      ,STATUS
                      ,CREATEDATE
                      ,VALIDATESTART
                      ,VALIDATEEND
                      ,INTERNAL_STATUS
                      ,INTERNAL_MESSAGE
                  FROM SAP_INTERFACE.spectwosuite.CUSTOM_SAP_CONTRACT_HEADER WITH(NOLOCK)
                  where import_status is null and purchase_doc_no<>'' and validity_start<>'' and validity_end<>'' and currency<>''and status>0
                  and not exists(select pk_id from spectwosuite.CUSTOM_SAP_CONTRACT_HEADER WHERE TABLEPKID= SAP_INTERFACE.spectwosuite.CUSTOM_SAP_CONTRACT_HEADER.PK_ID)
            COMMIT TRAN CONT3
    --INSERT INTO spectwosuite.CUSTOM_SAP_PROC_LOG
 --          (STATUS
 --          ,DESCRIPTION
 --          ,PROCESSEDDATE
 --          ,RECORDCOUNT)
 --    VALUES
 --          (1,
    --     'CONT3'
    --     ,GETDATE()
    --     ,@@ROWCOUNT
    --     );
    
--PRINT 'CONT3 END'
--PRINT GETDATE();
        /*CONT3 END*/

        /*CONT4*/
        
--PRINT 'CONT4 START'
--PRINT GETDATE();
SET @SECTION='CONT4';   
            BEGIN TRAN CONT4
                    INSERT INTO spectwosuite.CUSTOM_SAP_CONTRACT_ITEM
                               (TABLEPKID,PURCHASE_DOC_NO
                               ,ITEM_NO
                               ,DELETION_IND
                               ,MATERIAL_NO
                               ,SHORT_TEXT
                               ,MATERIAL_GRP
                               ,QTY
                               ,UOM
                               ,NET_PRICE          
                               ,PORTID 
                               ,STOCKTYPEID
                               ,INTERNAL_MESSAGE
                               ,INTERNAL_STATUS 
                                ,CONTRACTTYPE
                                ,AGREEMENTSUBITEMNO 
                                ,FACTOR
                    )
        
            select  PK_ID,PURCHASE_DOC_NO
                  ,ITEM_NO
                  ,DELETION_IND
                  ,MATERIAL_NO
                  ,SHORT_TEXT
                  ,MATERIAL_GRP
                  ,QTY
                  ,UOM
                  ,NET_PRICE    
                  ,PORTID      
                  ,STOCKTYPEID
                  ,INTERNAL_MESSAGE
                  ,INTERNAL_STATUS
                    ,CONTRACTTYPE
                    ,AGREEMENTSUBITEMNO
                    ,FACTOR 
                      from SAP_INTERFACE.spectwosuite.CUSTOM_SAP_CONTRACT_ITEM WITH(NOLOCK)
                       where IMPORT_STATUS is null and MATERIAL_NO<>'' and DELETION_IND<>'L' AND PORTID>0 and FACTOR>0 and  LEN(SHORT_TEXT)>0 AND LEN(PURCHASE_DOC_NO)>0 AND 
                       not exists (select PK_ID from spectwosuite.CUSTOM_SAP_CONTRACT_ITEM where TABLEPKID= SAP_INTERFACE.spectwosuite.CUSTOM_SAP_CONTRACT_ITEM.PK_ID )  ORDER BY PK_ID ASC;
        COMMIT TRAN CONT4
        

--      /*Inserting History*/

--      /*Transfering process status to interface table.*/
    TRUNCATE TABLE spectwosuite.CUSTOM_SAP_CONTRACT_HEADER;
    TRUNCATE TABLE spectwosuite.CUSTOM_SAP_CONTRACT_ITEM;

    DELETE FROM spectwosuite.CUSTOM_SAP_PROC_LOG WHERE STATUS=99;
    COMMIT
--      /*PURCHASE CONTRACT CREATION END*/
--      SET NOCOUNT OFF;
--      --COMMIT TRANSACTION
END TRY
    BEGIN CATCH
        DECLARE @ERRORNUMBER VARCHAR(20);
        DECLARE @ERRMSG VARCHAR(MAX);
        SET @ERRORNUMBER=CAST(ERROR_LINE() AS VARCHAR(20));
        SET @ERRMSG=ERROR_MESSAGE();

        BEGIN TRAN ER
            INSERT INTO spectwosuite.CUSTOM_SAP_PROC_LOG
                   (STATUS
                   ,DESCRIPTION
                   ,PROCESSEDDATE
                   ,RECORDCOUNT
                   ,ERRORMSG)
             VALUES
                   (
                   88,
                   @SECTION
                   ,getdate()
                   ,1
                   ,@ERRORNUMBER +','+ @ERRMSG
                   );
        COMMIT TRAN ER
    END CATCH
END

问题分析与解决方案

核心原因推测

虽然存储过程未直接操作采购模块的table1和table2，但PB12.5的采购窗口大概率使用了同一数据库连接，存储过程执行时长时间占用连接资源，导致采购窗口的请求被阻塞。员工窗口正常是因为其使用的连接或资源未被存储过程占用。

另外，存储过程中嵌套游标（CONT2CR套CONT2PORTCR）的设计会持续占用数据库连接，加剧资源竞争；同时未合理优化事务和锁策略，可能引发隐性的资源冲突。

具体解决步骤

  1. 分离数据库连接

    • 配置PB12.5应用，为采购模块单独分配数据库连接池，避免与存储过程共享同一连接。这样存储过程执行时，采购窗口可通过独立连接访问数据库。

  2. 改为异步执行存储过程

    • 使用SQL Server代理作业，通过sp_start_job调用存储过程，让存储过程在后台异步执行，不占用应用前台连接。
    • 或在PB应用中通过后台线程调用存储过程，避免阻塞UI操作。

  3. 重构存储过程，替换游标为集合操作
    嵌套游标是性能和资源占用的主要瓶颈，将CONT2部分的游标逻辑改为纯SQL集合操作，大幅减少执行时间和资源占用：

    -- 替换CONT2的游标逻辑
INSERT INTO SAP_INTERFACE.spectwosuite.CUSTOM_SAP_CONTRACT_ITEM
(DELETION_IND,PURCHASE_DOC_NO,ITEM_NO,MATERIAL_NO,SHORT_TEXT,MATERIAL_GRP,QTY,UOM,NET_PRICE,PORTID,TEMPPORTID,FACTOR)
SELECT 
    c.DELETION_IND,c.PURCHASE_DOC_NO,c.ITEM_NO,c.MATERIAL_NO,c.SHORT_TEXT,c.MATERIAL_GRP,c.QTY,c.UOM,c.NET_PRICE,p.PORTID,1252,c.FACTOR
FROM SAP_INTERFACE.spectwosuite.CUSTOM_SAP_CONTRACT_ITEM c
CROSS JOIN SAP_INTERFACE.spectwosuite.custom_sap_contract_port p
JOIN (
    SELECT MATERIAL_NO,PURCHASE_DOC_NO,MIN(pk_id) AS PK_ID
    FROM SAP_INTERFACE.spectwosuite.CUSTOM_SAP_CONTRACT_ITEM WITH(NOLOCK) 
    WHERE IMPORT_STATUS IS NULL 
      AND DELETION_IND NOT IN('L','1') 
      AND len(material_no)>0 
      AND factor>0 
      AND portid>0
    GROUP BY MATERIAL_NO,PURCHASE_DOC_NO
) min_c ON c.pk_id = min_c.PK_ID
WHERE NOT
相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分)， 限时 9.9 元起

产品介绍相关文档
火山引擎 最新活动
全面开放
Doubao-Seedance-2.5全新模型升级

模型再升级,30秒超长叙事, 模态参考扩容

HOT
方舟Codingplan

模型自由，工具不限，最新支持 Deepseek-V4 系列、GLM-5.3 系列

HOT
方舟Agent plan

超全模态模型 × Harness 升级，最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列

HOT
新用户特惠专场

大模型19元起，Al应用9.9元畅享，新人首购爆款尽享优惠

最近更新时间：2026.08.08 09:01:15