将Informix光标转换为MSSQL光标 [英] Convert Informix cursor to MSSQL Cursor

问题描述

我在Informix数据库中有一个游标，它是函数的一部分，我想将其转换为MS-SQL中的游标.

I have a cursor in Informix database which is part of a function, I would like to convert that into a cursor in MS-SQL.

下面是代码:

DECLARE select distinct agentname, agentloginid 
           from selected_agents

            call;
   OPEN cur;
   FETCH cur INTO @l_AgentName, @l_AgentLoginID;
   WHILE @@FETCH_STATUS = 0 BEGIN getAgentLogActivity(@l_AgentName, @l_AgentLoginID, @p_startTime, @p_endTime);
           insert into final_result(Agent_Name, Agent_Login_ID, op1, Login_Time, op2, 
                           Logout_Time, Logout_Reason_Code, Logon_Duration)
            select @l_AgentName, @l_AgentLoginID, op1, logintime, op2, logouttime, reasoncode, duration
                from temp_login_logout;
   FETCH cur INTO @l_AgentName, @l_AgentLoginID;
   end
   CLOSE cur;
   DEALLOCATE cur;

在这种情况下，我发现Declare语句存在问题.我知道我必须用下面的方式写这个.

Here in this case I am finding issues with Declare statement. I know I have to write this in the way below.

DECLARE cur CURSOR FOR
   SELECT DISTINCT agentname, agentloginid 
           from selected_agents
   OPEN cur;
   FETCH cur INTO @l_AgentName, @l_AgentLoginID;
   WHILE @@FETCH_STATUS = 0 BEGIN getAgentLogActivity(@l_AgentName, @l_AgentLoginID, @p_startTime, @p_endTime);
           insert into final_result(Agent_Name, Agent_Login_ID, op1, Login_Time, op2, 
                           Logout_Time, Logout_Reason_Code, Logon_Duration)
            select @l_AgentName, @l_AgentLoginID, op1, logintime, op2, logouttime, reasoncode, duration
                from temp_login_logout;
   FETCH cur INTO @l_AgentName, @l_AgentLoginID;
   end
   CLOSE cur;
   DEALLOCATE cur;

已更新-selected_agents声明

UPDATED - selected_agents declaration

DECLARE @selected_agents TABLE (
        agentloginid NVARCHAR(50), 
        agentname NVARCHAR(50),
        agentID INT,
        profileid INT,
        resourcegroupid int,
        dateinactive datetime,
        --filter boolean default 'f',
        rsmid INT,
        teamid INT
    );

现在，这里 selected_agents 是我在函数中创建的临时表.

Now here selected_agents is a temp table which I have created in the function.

该错误表示未声明agentname，agentloginid.我已经声明过顺便说一句.

The error what it says is that agentname, agentloginid is not declared. Which I have already declared btw.

有人可以帮助我如何纠正它.

Can someone help me with how can i correct it.

推荐答案

这可能不是100％的解决方案，但其中有很多注释可以帮助您入门.随时发表评论，让我知道您仍然遇到问题.

This may not be 100% a solution, but it has a lot of comments to get you started. Feel free to comment to let me know where you are still having problems.

DECLARE @selected_agents TABLE (
        agentloginid NVARCHAR(50), 
        agentname NVARCHAR(50),
        agentID INT,
        profileid INT,
        resourcegroupid int,
        dateinactive datetime,
        --filter boolean default 'f',
        rsmid INT,
        teamid INT
    );


/*
Do something here to insert data into @selected_agents
*/

DECLARE @l_AgentName NVARCHAR(50), @l_AgentLoginID NVARCHAR(50); 
DECLARE @l_AgentExtension NVARCHAR(50); 
DECLARE @l_op1 NVARCHAR(1), @l_op2 NVARCHAR(1); 
DECLARE @l_LoginTime DATETIME2(3), @l_LogoutTime DATETIME2(3), @l_latestSynchedTime DATETIME2(3); 
DECLARE @l_LogoutReasonCode SMALLINT, @l_selType SMALLINT;
DECLARE @l_LogonDuration INT, @l_resCount INT, @l_op INT; 
DECLARE @l_selValue varchar(4000);

--Added @ in front of the table name, since it is a table variable you need the @.
DECLARE cur CURSOR FOR
    SELECT DISTINCT agentname, agentloginid 
    from @selected_agents

OPEN cur

--Here we added NEXT FROM to make the syntax correct
FETCH NEXT FROM cur INTO @l_AgentName, @l_AgentLoginID;  

WHILE @@FETCH_STATUS = 0 
BEGIN 

    --Here you seem to be attempting to call a function to potentially set your variables, but this likley isn't happening
    --In another window, try this function and then select the values of your variables to make sure they are being set
    getAgentLogActivity(@l_AgentName, @l_AgentLoginID, @p_startTime, @p_endTime);

    insert into final_result(Agent_Name, Agent_Login_ID, op1, Login_Time, op2,Logout_Time, Logout_Reason_Code, Logon_Duration)
    select 
        @l_AgentName, 
        @l_AgentLoginID, 
        op1, 
        logintime, 
        op2, 
        logouttime, 
        reasoncode, 
        duration
    from 
        temp_login_logout;

--Here we added NEXT FROM to make the syntax correct
FETCH NEXT FROM cur INTO @l_AgentName, @l_AgentLoginID; t

END
CLOSE cur;
DEALLOCATE cur;

这篇关于将Informix光标转换为MSSQL光标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！