Oracle存储过程转MSSQL失败求助：附原代码及转换后代码

Let's break down why the converted code isn't working, then fix it with two valid MSSQL implementations—one matching your original Oracle loop logic, and a more efficient set-based version (since MSSQL excels at set operations).

What's Wrong with the Converted Code? #

The SQLINE output has several critical issues:

• Invalid cursor usage: You can't declare a cursor for 0..1000 directly in MSSQL, and there's no variable to fetch the cursor value into.
• Incorrect string concatenation: Oracle's || does string concatenation, but in MSSQL, p+1 will treat numbers as integers and add them (not concatenate). You need to convert p to a string first.
• Unreliable date handling: Using string dates like '02-feb-2004' relies on implicit conversion, which can fail if your server's date format settings change.
• Broken logic for Lata's row: The converted code only inserts this row when p<>0, but your original Oracle procedure inserts it every loop iteration.

Solution 1: WHILE Loop (Matches Original Oracle Logic) #

This version uses a simple WHILE loop to replicate your original Oracle procedure's behavior exactly:

CREATE OR ALTER PROCEDURE insert_emp
AS
BEGIN
    SET NOCOUNT ON; -- Suppress "X rows affected" messages for cleaner execution
    DECLARE @p INT = 0; -- Initialize loop counter

    WHILE @p <= 1000
    BEGIN
        -- Convert @p to string before concatenating to get empno like '01', '11', etc.
        INSERT INTO emp (empno, empname, salary, hiredate, dept, title)
        VALUES(CONVERT(VARCHAR(10), @p) + '1', 'Bob', 30000, DATEFROMPARTS(2004, 2, 2), 'SALES', 'EMP');

        INSERT INTO emp (empno, empname, salary, hiredate, dept, title)
        VALUES(CONVERT(VARCHAR(10), @p) + '2', 'Raj', 80000, DATEFROMPARTS(2018, 3, 2), 'MRKTNG', 'EMP');

        INSERT INTO emp (empno, empname, salary, hiredate, dept, title)
        VALUES(CONVERT(VARCHAR(10), @p) + '3', 'Kevin', 90000, DATEFROMPARTS(2010, 4, 5), 'ADMIN', 'EMP');

        INSERT INTO emp (empno, empname, salary, hiredate, dept, title)
        VALUES(CONVERT(VARCHAR(10), @p) + '4', 'Laila', 30000, DATEFROMPARTS(2004, 2, 12), 'PURCHASE', 'EMP');

        INSERT INTO emp (empno, empname, salary, hiredate, dept, title)
        VALUES(CONVERT(VARCHAR(10), @p) + '5', 'Steve', 100000, DATEFROMPARTS(2008, 1, 15), 'SALES', 'MGR');

        INSERT INTO emp (empno, empname, salary, hiredate, dept, title)
        VALUES(CONVERT(VARCHAR(10), @p) + '6', 'Lucy', 80000, DATEFROMPARTS(2014, 2, 10), 'MRKTNG', 'MGR');

        INSERT INTO emp (empno, empname, salary, hiredate, dept, title)
        VALUES(CONVERT(VARCHAR(10), @p) + '7', 'Nathan', 120000, DATEFROMPARTS(2012, 6, 17), 'ADMIN', 'MGR');

        INSERT INTO emp (empno, empname, salary, hiredate, dept, title)
        VALUES(CONVERT(VARCHAR(10), @p) + '8', 'Jose', 60000, DATEFROMPARTS(2015, 7, 16), 'DESIGN', 'EMP');

        INSERT INTO emp (empno, empname, salary, hiredate, dept, title)
        VALUES(CONVERT(VARCHAR(10), @p) + '9', 'Andrew', 80000, DATEFROMPARTS(2005, 8, 18), 'SALES', 'EMP');

        INSERT INTO emp (empno, empname, salary, hiredate, dept, title)
        VALUES(CONVERT(VARCHAR(10), @p) + '10', 'Lata', 90000, DATEFROMPARTS(1998, 2, 2), 'SALES', 'EMP');

        SET @p = @p + 1; -- Increment counter
    END

    COMMIT;
END;

Key improvements here:

• Uses a straightforward WHILE loop instead of a broken cursor implementation
• DATEFROMPARTS ensures dates are parsed correctly regardless of server settings
• Converts @p to a string before concatenation to match Oracle's || behavior
• Restores the original logic for Lata's row
• Adds SET NOCOUNT ON to avoid unnecessary execution messages

Solution 2: Set-Based Insert (More Efficient for MSSQL) #

MSSQL is optimized for set operations, so this version generates all rows in one go instead of looping—this will be much faster for bulk inserts:

CREATE OR ALTER PROCEDURE insert_emp
AS
BEGIN
    SET NOCOUNT ON;

    -- Generate a set of numbers from 0 to 1000
    WITH Numbers AS (
        SELECT TOP (1001) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS p
        FROM sys.all_columns ac1
        CROSS JOIN sys.all_columns ac2
    )
    -- Insert all rows at once by cross-joining numbers with your template data
    INSERT INTO emp (empno, empname, salary, hiredate, dept, title)
    SELECT
        CONVERT(VARCHAR(10), n.p) + v.suffix,
        v.empname,
        v.salary,
        v.hiredate,
        v.dept,
        v.title
    FROM Numbers n
    CROSS JOIN (
        -- Your original row templates, defined once
        VALUES
            ('1', 'Bob', 30000, DATEFROMPARTS(2004, 2, 2), 'SALES', 'EMP'),
            ('2', 'Raj', 80000, DATEFROMPARTS(2018, 3, 2), 'MRKTNG', 'EMP'),
            ('3', 'Kevin', 90000, DATEFROMPARTS(2010, 4, 5), 'ADMIN', 'EMP'),
            ('4', 'Laila', 30000, DATEFROMPARTS(2004, 2, 12), 'PURCHASE', 'EMP'),
            ('5', 'Steve', 100000, DATEFROMPARTS(2008, 1, 15), 'SALES', 'MGR'),
            ('6', 'Lucy', 80000, DATEFROMPARTS(2014, 2, 10), 'MRKTNG', 'MGR'),
            ('7', 'Nathan', 120000, DATEFROMPARTS(2012, 6, 17), 'ADMIN', 'MGR'),
            ('8', 'Jose', 60000, DATEFROMPARTS(2015, 7, 16), 'DESIGN', 'EMP'),
            ('9', 'Andrew', 80000, DATEFROMPARTS(2005, 8, 18), 'SALES', 'EMP'),
            ('10', 'Lata', 90000, DATEFROMPARTS(1998, 2, 2), 'SALES', 'EMP')
    ) v(suffix, empname, salary, hiredate, dept, title);

    COMMIT;
END;

This approach avoids looping entirely, which is the preferred way to handle bulk operations in MSSQL. It generates all 10010 rows (10 rows per number 0-1000) in a single insert statement.

内容的提问来源于stack exchange，提问作者Kwa Arboncana