SQL Server 与 Oracle 跨库操作、高并发场景实现方案(进阶方案)

项目需求：将sqlserver中临时表sqlservertemp_table的COMPANY,OGB04,OGA01,OGA03数据传入到已建立链接的oracle实例名ds中，便于从oracle中提取数据时，读取表里数据限定查询条件，减少无效资料的读取到sqlserver。

以下是(结构化参数)完整的部署脚本，包含：

　　所有数据库对象（临时表、中间表、日志表）

　　存储过程封装（主入口、内部处理、异步调用）

　　清理机制（定时删除旧数据）

　　SQL Agent Job 示例

　　Web API 接口调用支持（.NET Core 简化示例）

一、完整部署脚本

✅ 1. 创建中间表和日志表

-- 中间表：用于保存 #Params 数据供后台处理
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Temp_Params_Session]') AND type in (N'U'))
CREATE TABLE dbo.Temp_Params_Session (
    SessionID VARCHAR(50),
    COMPANY VARCHAR(50),
    OGB04 VARCHAR(100),
    OGA01 VARCHAR(100),
    OGA03 VARCHAR(100)
);
GO

-- 日志表：记录同步执行信息
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Sync_Logs]') AND type in (N'U'))
CREATE TABLE dbo.Sync_Logs (
    LogID INT IDENTITY PRIMARY KEY,
    SessionID VARCHAR(50),
    StartTime DATETIME,
    EndTime DATETIME,
    Status VARCHAR(20), -- 'Started', 'Completed', 'Failed'
    RowCount INT,
    ErrorMessage NVARCHAR(MAX)
);
GO

✅ 2. 内部处理存储过程 Sync_Params_To_Oracle_Internal

-- =============================================
-- 存储过程：Sync_Params_To_Oracle_Internal
-- 功能：将中间表中的数据插入 Oracle 临时表（字段映射）
-- =============================================
IF OBJECT_ID('dbo.Sync_Params_To_Oracle_Internal') IS NOT NULL
    DROP PROCEDURE dbo.Sync_Params_To_Oracle_Internal;
GO

CREATE PROCEDURE dbo.Sync_Params_To_Oracle_Internal
    @SessionID VARCHAR(50),
    @BatchSize INT = 500  -- 限定每次插入操作的笔数，防止锁表发生
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @StartTime DATETIME = GETDATE();
    DECLARE @RowCount INT;
    DECLARE @OracleTableName NVARCHAR(128) = 'TMP_' + REPLACE(CAST(NEWID() AS VARCHAR(36)), '-', '_');
    DECLARE @ColumnList NVARCHAR(MAX);
    DECLARE @SQL NVARCHAR(MAX);

    BEGIN TRY

        -- 创建会话级临时表（模拟从中间表加载）
        IF OBJECT_ID('tempdb..#Temp_Params') IS NOT NULL
            DROP TABLE #Temp_Params;

        SELECT *
        INTO #Temp_Params
        FROM dbo.Temp_Params_Session
        WHERE SessionID = @SessionID;

        IF NOT EXISTS (SELECT 1 FROM tempdb.sys.tables WHERE name LIKE '#Temp_Params%')
        BEGIN
            THROW 50001, '没有找到与 SessionID 对应的数据。', 1;
            RETURN;
        END

        -- 获取字段列表
        SELECT @ColumnList = STRING_AGG(QUOTENAME(name), ', ')
        FROM tempdb.sys.columns
        WHERE object_id = OBJECT_ID('tempdb..#Temp_Params');

        -- 创建 Oracle 表
        IF NOT EXISTS (
            SELECT 1
            FROM OPENQUERY(DS, 'SELECT table_name FROM all_tables WHERE table_name = ''' + UPPER(@OracleTableName) + '''' )
        )
        BEGIN
            SET @SQL = 'CREATE TABLE ' + @OracleTableName + ' (session_id VARCHAR2(50), ' +
                       STUFF((SELECT ',' + name + ' VARCHAR2(4000)'
                              FROM tempdb.sys.columns
                              WHERE object_id = OBJECT_ID('tempdb..#Temp_Params')
                              ORDER BY column_id
                              FOR XML PATH('')), 1, 1, '') + ')';
            EXEC (@SQL) AT DS;
        END

        -- 清空当前 session 数据
        EXEC ('DELETE FROM ' + @OracleTableName + ' WHERE session_id = ''' + @SessionID + '''') AT DS;

        -- 插入数据
        WHILE EXISTS(SELECT 1 FROM #Temp_Params)
        BEGIN
            SET @SQL = '
                INSERT INTO OPENQUERY(DS, ''SELECT session_id, ' + @ColumnList + ' FROM ' + @OracleTableName + ''')
                SELECT ''' + @SessionID + ''', ' + @ColumnList + '
                FROM (
                    SELECT TOP (' + CAST(@BatchSize AS NVARCHAR) + ') *
                    FROM #Temp_Params
                ) AS tmp';

            EXEC sp_executesql @SQL;

            DELETE TOP (@BatchSize) FROM #Temp_Params;
        END

        SET @RowCount = (SELECT COUNT(*) FROM Temp_Params_Session WHERE SessionID = @SessionID);

        INSERT INTO dbo.Sync_Logs (SessionID, StartTime, EndTime, Status, RowCount)
        VALUES (@SessionID, @StartTime, GETDATE(), 'Completed', @RowCount);

    END TRY
    BEGIN CATCH

        INSERT INTO dbo.Sync_Logs (SessionID, StartTime, EndTime, Status, RowCount, ErrorMessage)
        VALUES (@SessionID, @StartTime, GETDATE(), 'Failed', NULL, ERROR_MESSAGE());

    END CATCH

END
GO

✅ 3. 主入口存储过程 Sync_Params_To_Oracle_Async

-- =============================================
-- 存储过程：Sync_Params_To_Oracle_Async
-- 功能：启动异步插入任务
-- =============================================
IF OBJECT_ID('dbo.Sync_Params_To_Oracle_Async') IS NOT NULL
    DROP PROCEDURE dbo.Sync_Params_To_Oracle_Async;
GO

CREATE PROCEDURE dbo.Sync_Params_To_Oracle_Async
    @SessionID VARCHAR(50) = NULL OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    IF OBJECT_ID('tempdb..#Params') IS NULL
    BEGIN
        THROW 50001, '#Params 临时表不存在，请先创建后再调用本存储过程。', 1;
        RETURN;
    END

    IF @SessionID IS NULL
        SET @SessionID = REPLACE(CAST(NEWID() AS VARCHAR(36)), '-', '_');

    -- 复制 #Params 到全局中间表
    IF OBJECT_ID('tempdb..#Temp_Params_Global') IS NOT NULL
        DROP TABLE #Temp_Params_Global;

    SELECT @SessionID AS SessionID, * INTO #Temp_Params_Global FROM #Params;

    INSERT INTO dbo.Temp_Params_Session
    SELECT * FROM #Temp_Params_Global;

    -- 启动 SQL Agent Job（示例参数替换方式）
    EXEC msdb.dbo.sp_start_job @job_name = 'Sync_Params_To_Oracle_Job',
                               @step_name = 'Run Internal Procedure',
                               @input_parameters = N'@SessionID=''' + @SessionID + '''';

    SELECT @SessionID AS SessionID;

END
GO

✅ 4. 定时清理任务（SQL Server Agent Job 或作业脚本）

✅ 创建清理存储过程

-- 清理过期的 Session 数据（如超过 1 小时）
IF OBJECT_ID('dbo.Cleanup_Old_Sessions') IS NOT NULL
    DROP PROCEDURE dbo.Cleanup_Old_Sessions;
GO

CREATE PROCEDURE dbo.Cleanup_Old_Sessions
AS
BEGIN
    SET NOCOUNT ON;

    -- 删除日志中超过1小时的数据
    DELETE FROM dbo.Sync_Logs
    WHERE StartTime < DATEADD(HOUR, -1, GETDATE());

    -- 删除中间表中过期数据
    DELETE tps
    FROM dbo.Temp_Params_Session tps
    LEFT JOIN dbo.Sync_Logs logs ON tps.SessionID = logs.SessionID
    WHERE logs.SessionID IS NULL
      AND tps.SessionID IN (
          SELECT SessionID
          FROM dbo.Sync_Logs
          WHERE StartTime < DATEADD(HOUR, -1, GETDATE())
      );
END
GO

 

💻 二、Web API 接口调用支持（.NET Core 示例）

假设你使用 .NET Core 与 SQL Server 进行交互，以下是一个简化版控制器：

[ApiController]
[Route("api/[controller]")]
public class SyncController : ControllerBase
{
    private readonly string _connectionString;

    public SyncController(IConfiguration configuration)
    {
        _connectionString = configuration.GetConnectionString("Default");
    }

    [HttpPost("start")]
    public async Task<IActionResult> StartAsync([FromBody] ParamsRequest request)
    {
        var sessionId = Guid.NewGuid().ToString();

        using (var conn = new SqlConnection(_connectionString))
        {
            await conn.OpenAsync();

            // 创建 #Params 临时表并插入数据
            using (var cmd = new SqlCommand())
            {
                cmd.Connection = conn;
                cmd.CommandText = $@"
                    CREATE TABLE #Params (COMPANY VARCHAR(50), OGB04 VARCHAR(100), OGA01 VARCHAR(100), OGA03 VARCHAR(100));
                    
                    INSERT INTO #Params (COMPANY, OGB04, OGA01, OGA03)
                    VALUES {string.Join(",", request.Records.Select(r => $"'{r.Company}','{r.Ogb04}','{r.Oga01}','{r.Oga03}'"))};

                    EXEC dbo.Sync_Params_To_Oracle_Async @SessionID OUTPUT;
                ";
                cmd.Parameters.Add(new SqlParameter("@SessionID", SqlDbType.VarChar, 50) { Direction = ParameterDirection.Output };

                await cmd.ExecuteNonQueryAsync();

                var outputSessionId = cmd.Parameters["@SessionID"].Value?.ToString() ?? sessionId;

                return Ok(new { SessionID = outputSessionId });
            }
        }
    }

    [HttpGet("status/{sessionId}")]
    public async Task<IActionResult> GetStatus(string sessionId)
    {
        using (var conn = new SqlConnection(_connectionString))
        {
            var log = await conn.QueryFirstOrDefaultAsync<SyncLog>(
                "SELECT * FROM dbo.Sync_Logs WHERE SessionID = @SessionID",
                new { SessionID = sessionId });

            return Ok(log);
        }
    }
}

public class ParamsRequest
{
    public List<ParamRecord> Records { get; set; }
}

public class ParamRecord
{
    public string Company { get; set; }
    public string Ogb04 { get; set; }
    public string Oga01 { get; set; }
    public string Oga03 { get; set; }
}

public class SyncLog
{
    public int LogID { get; set; }
    public string SessionID { get; set; }
    public DateTime StartTime { get; set; }
    public DateTime? EndTime { get; set; }
    public string Status { get; set; }
    public int? RowCount { get; set; }
    public string ErrorMessage { get; set; }
}

 

✅ 总结

你现在拥有了一个完整的解决方案，包括：

模块	内容
数据结构	中间表、日志表
存储过程	异步入口、内部处理、清理机制
SQL Agent Job	实现后台异步执行
Web API	支持外部系统调用接口

📦 下一步建议

你可以进一步扩展的功能包括：

• 使用 JSON 格式支持任意字段结构
• 添加 RabbitMQ / Kafka 消息队列实现更复杂的异步调度
• 增加前端页面查看日志和状态
• 部署到 Azure Function / AWS Lambda 实现无服务器架构