MS-SQL存储过程到MySQL

can anybody help me to write the following MS-SQL sp to MySQL sp,

谁能帮我写一下MS-SQL sp到MySQL sp，

use of this:

使用:

CREATE PROCEDURE sp_InputWork 
    @_DelimitedString nvarchar(MAX) 
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @_DataRow nvarchar(MAX)
    DECLARE @_DescriptionOfWorkDone nvarchar(MAX)
    DECLARE @_TemporaryStorage nvarchar(MAX)
    DECLARE @_QTY int
    DECLARE @_Total int
    DECLARE @_CurrentField int

    WHILE CHARINDEX(';', @_DelimitedString) > 0
    BEGIN
        SET @_DataRow = CAST(SUBSTRING(@_DelimitedString, 0, CHARINDEX(';', @_DelimitedString)) AS nvarchar(MAX))
        SET @_CurrentField = 1
        WHILE CHARINDEX(',', @_DataRow) > 0
            BEGIN
            SET @_TemporaryStorage = CAST(SUBSTRING(@_DataRow, 0, CHARINDEX(',', @_DataRow)) AS nvarchar(MAX))
            IF @_CurrentField = 1
                SET @_QTY = CAST(@_TemporaryStorage AS int)
                    IF @_CurrentField = 2
            SET @_DescriptionOfWorkDone = @_TemporaryStorage
                    IF @_CurrentField = 3
                    SET @_Total = CAST(@_TemporaryStorage AS int)           
            SET @_DataRow = SUBSTRING(@_DataRow, CHARINDEX(',', @_DataRow) + 1, LEN(@_DataRow))
            SET @_CurrentField = @_CurrentField + 1
        END
        INSERT INTO tblWorkDone (QTY, DescriptionOfWorkDone, Total) VALUES (@_QTY, @_DescriptionOfWorkDone, @_Total)
        SET @_DelimitedString = SUBSTRING(@_DelimitedString, CHARINDEX(';', @_DelimitedString) + 1, LEN(@_DelimitedString))
        END 
END

1 个解决方案

#1

I have changed the SP to Mysql

我把SP改成了Mysql

BEGIN
DECLARE _DataRow VARCHAR(21000);
 DECLARE _DescriptionOfWorkDone VARCHAR(21000);
DECLARE _TemporaryStorage VARCHAR(21000) ;
DECLARE _QTY int;
DECLARE _Total int;
DECLARE _CurrentField int;

WHILE INSTR(_DelimitedString, ';') >0 DO

    SET _DataRow = CAST(SUBSTRING(_DelimitedString, 1, INSTR(_DelimitedString,';' )) AS CHAR);
    SET _CurrentField = 1;
    WHILE INSTR(_DataRow,',' ) > 0 DO         
        SET _TemporaryStorage = CAST(SUBSTRING(_DataRow, 1, INSTR(_DataRow,',' )-1) AS CHAR);
        IF _CurrentField = 1 then
            SET _QTY = CAST(_TemporaryStorage AS UNSIGNED);
                end if;
                      IF _CurrentField = 2 then
        SET _DescriptionOfWorkDone = _TemporaryStorage;
                end if;
                      IF _CurrentField = 3 then
                SET _Total = CAST(_TemporaryStorage AS UNSIGNED)    ;   
                     end if;    

        SET _DataRow = SUBSTRING(_DataRow, INSTR( _DataRow,',') + 1, LENGTH(_DataRow));
        SET _CurrentField = _CurrentField + 1;
         END while;

    INSERT INTO tblWorkDone (QTY, DescriptionOfWorkDone, Total) VALUES (_QTY, _DescriptionOfWorkDone, _Total);
    SET _DelimitedString = SUBSTRING(_DelimitedString, INSTR(_DelimitedString,';' ) + 1, LENGTH(_DelimitedString));

   END WHILE;END

Data inserted

数据插入

CALL `sp_InputWork`('1,TEST,100,;2,TEST1,200,;3,TEST3,300,;')

Successfully looped and inserted to Mysql Table

成功循环并插入到Mysql表

#1