복붙노트

[SQL] SQL 호출은 커서를 사용하지 않고 각 행에 대해 저장 프로 시저

SQL

SQL 호출은 커서를 사용하지 않고 각 행에 대해 저장 프로 시저

행의 열이 커서를 사용하지 않고 SP에 입력 매개 변수가 어디에 어떻게 하나, 테이블의 각 행에 대해 저장 프로 시저를 호출 할 수 있습니다?

해결법

  1. ==============================

    1.일반적으로 난 항상 (때로는 스키마를 변경하는 비용으로) 집합 기반의 접근 방식을 찾아 말하기.

    일반적으로 난 항상 (때로는 스키마를 변경하는 비용으로) 집합 기반의 접근 방식을 찾아 말하기.

    그러나,이 조각은 그것의 장소를 가지고있다 ..

    -- Declare & init (2008 syntax)
DECLARE @CustomerID INT = 0

-- Iterate over all customers
WHILE (1 = 1) 
BEGIN  

  -- Get next customerId
  SELECT TOP 1 @CustomerID = CustomerID
  FROM Sales.Customer
  WHERE CustomerID > @CustomerId 
  ORDER BY CustomerID

  -- Exit loop if no more customers
  IF @@ROWCOUNT = 0 BREAK;

  -- call your sproc
  EXEC dbo.YOURSPROC @CustomerId

END
  2. ==============================

    2.당신이 뭔가를 할 수 있습니다 : 예를 들면하여 테이블을 주문 CustomerID를은 WHILE 루프를 사용하는 고객을 반복을 (에서는 AdventureWorks Sales.Customer 샘플 테이블을 사용), 및 :

    당신이 뭔가를 할 수 있습니다 : 예를 들면하여 테이블을 주문 CustomerID를은 WHILE 루프를 사용하는 고객을 반복을 (에서는 AdventureWorks Sales.Customer 샘플 테이블을 사용), 및 :

    -- define the last customer ID handled
DECLARE @LastCustomerID INT
SET @LastCustomerID = 0

-- define the customer ID to be handled now
DECLARE @CustomerIDToHandle INT

-- select the next customer to handle    
SELECT TOP 1 @CustomerIDToHandle = CustomerID
FROM Sales.Customer
WHERE CustomerID > @LastCustomerID
ORDER BY CustomerID

-- as long as we have customers......    
WHILE @CustomerIDToHandle IS NOT NULL
BEGIN
    -- call your sproc

    -- set the last customer handled to the one we just handled
    SET @LastCustomerID = @CustomerIDToHandle
    SET @CustomerIDToHandle = NULL

    -- select the next customer to handle    
    SELECT TOP 1 @CustomerIDToHandle = CustomerID
    FROM Sales.Customer
    WHERE CustomerID > @LastCustomerID
    ORDER BY CustomerID
END

    일부 열에 의해 주문의 어떤 종류를 정의 할 수있는 즉 한 모든 테이블과 함께 작동합니다.

  3. ==============================

    3.

    DECLARE @SQL varchar(max)=''

-- MyTable has fields fld1 & fld2

Select @SQL = @SQL + 'exec myproc ' + convert(varchar(10),fld1) + ',' 
                   + convert(varchar(10),fld2) + ';'
From MyTable

EXEC (@SQL)

    좋아, 그래서 생산에 같은 코드를 삽입하지 않을 것이다, 그러나 당신의 요구 사항을 만족한다.

  4. ==============================

    4.마크의 대답은 좋다 (어떻게 내가 해결할 수 있다면 그것에 대해 언급 것!) 그냥 내가 SELECT 한 번만 존재 있도록 루프를 변경하는 것이 더 나은 수 있음을 지적 거라고 생각 (나는이 작업을 수행하는 데 필요한 실제 경우, SELECT는 매우 복잡하고, 그것을 작성하는 것은 두 번 위험한 유지 보수 문제였다) .

    마크의 대답은 좋다 (어떻게 내가 해결할 수 있다면 그것에 대해 언급 것!) 그냥 내가 SELECT 한 번만 존재 있도록 루프를 변경하는 것이 더 나은 수 있음을 지적 거라고 생각 (나는이 작업을 수행하는 데 필요한 실제 경우, SELECT는 매우 복잡하고, 그것을 작성하는 것은 두 번 위험한 유지 보수 문제였다) .

    -- define the last customer ID handled
DECLARE @LastCustomerID INT
SET @LastCustomerID = 0
-- define the customer ID to be handled now
DECLARE @CustomerIDToHandle INT
SET @CustomerIDToHandle = 1

-- as long as we have customers......    
WHILE @LastCustomerID <> @CustomerIDToHandle
BEGIN  
  SET @LastCustomerId = @CustomerIDToHandle
  -- select the next customer to handle    
  SELECT TOP 1 @CustomerIDToHandle = CustomerID
  FROM Sales.Customer
  WHERE CustomerID > @LastCustomerId 
  ORDER BY CustomerID

  IF @CustomerIDToHandle <> @LastCustomerID
  BEGIN
      -- call your sproc
  END

END
  5. ==============================

    5.테이블을 반환하는 함수에 저장 프로 시저를 설정할 수 있습니다 경우에, 당신이 사용할 수있는 상호 적용됩니다.

    테이블을 반환하는 함수에 저장 프로 시저를 설정할 수 있습니다 경우에, 당신이 사용할 수있는 상호 적용됩니다.

    예를 들어, 고객의 테이블이 있다고, 당신은, 당신이 CustomerID를했다 및 합계를 반환하는 함수를 만들 것입니다 그들의 주문의 합을 계산합니다.

    그리고 당신이 할 수 있습니다 :

    SELECT CustomerID, CustomerSum.Total

FROM Customers
CROSS APPLY ufn_ComputeCustomerTotal(Customers.CustomerID) AS CustomerSum

    어디 기능과 같을 것이다 :

    CREATE FUNCTION ComputeCustomerTotal
(
    @CustomerID INT
)
RETURNS TABLE
AS
RETURN
(
    SELECT SUM(CustomerOrder.Amount) AS Total FROM CustomerOrder WHERE CustomerID = @CustomerID
)

    물론, 위의 예는 단일 쿼리에서 사용자 정의 함수없이 할 수있다.

    저장 프로 시저의 많은 기능은 사용자 정의 함수에서 사용할 수없는 및 함수에 저장 프로 시저를 변환 항상 일을하지 않습니다 - 단점은 기능이 매우 제한되어 있다는 점이다.

  6. ==============================

    6.이후 SQL Server 2005를 들어, CROSS하여이 작업을 수행 할 수 있습니다 적용 및 테이블 반환 함수.

    이후 SQL Server 2005를 들어, CROSS하여이 작업을 수행 할 수 있습니다 적용 및 테이블 반환 함수.

    그냥 명확성을 위해, 내가 저장 프로 시저가 테이블 반환 함수로 변환 할 수있다 이런 경우를 말하는 겁니다.

  7. ==============================

    7.상기 표에 가입 I는 허용 대답을 사용할 것이나, 다른 가능성은 A의 행 번호에 의해 해당 통해 루프 (이 경우 테이블의 단지 ID 필드) 값 번째 세트를 보유하는 테이블 변수를 사용하는 당신은 루프 내에서 작업에 필요한 무엇이든 검색 할 수 있습니다.

    상기 표에 가입 I는 허용 대답을 사용할 것이나, 다른 가능성은 A의 행 번호에 의해 해당 통해 루프 (이 경우 테이블의 단지 ID 필드) 값 번째 세트를 보유하는 테이블 변수를 사용하는 당신은 루프 내에서 작업에 필요한 무엇이든 검색 할 수 있습니다.

    DECLARE @RowCnt int; SET @RowCnt = 0 -- Loop Counter

-- Use a table variable to hold numbered rows containg MyTable's ID values
DECLARE @tblLoop TABLE (RowNum int IDENTITY (1, 1) Primary key NOT NULL,
     ID INT )
INSERT INTO @tblLoop (ID)  SELECT ID FROM MyTable

  -- Vars to use within the loop
  DECLARE @Code NVarChar(10); DECLARE @Name NVarChar(100);

WHILE @RowCnt < (SELECT COUNT(RowNum) FROM @tblLoop)
BEGIN
    SET @RowCnt = @RowCnt + 1
    -- Do what you want here with the data stored in tblLoop for the given RowNum
    SELECT @Code=Code, @Name=LongName
      FROM MyTable INNER JOIN @tblLoop tL on MyTable.ID=tL.ID
      WHERE tl.RowNum=@RowCnt
    PRINT Convert(NVarChar(10),@RowCnt) +' '+ @Code +' '+ @Name
END
  8. ==============================

    8.이것은 상기 n3rds 용액의 변형이다. ORDER BY를 사용하여 어떤 정렬 ()가 MIN으로, 필요하지 않습니다 사용됩니다.

    이것은 상기 n3rds 용액의 변형이다. ORDER BY를 사용하여 어떤 정렬 ()가 MIN으로, 필요하지 않습니다 사용됩니다.

    고유 제한 조건이 있어야합니다 (수치 어떤 다른 열은 진행을 위해 사용하거나) 그 CustomerID를 기억하십시오. 수의 CustomerID가에 색인해야하기 때문에 또한, 빠른 속도를 확인합니다.

    -- Declare & init
DECLARE @CustomerID INT = (SELECT MIN(CustomerID) FROM Sales.Customer); -- First ID
DECLARE @Data1 VARCHAR(200);
DECLARE @Data2 VARCHAR(200);

-- Iterate over all customers
WHILE @CustomerID IS NOT NULL
BEGIN  

  -- Get data based on ID
  SELECT @Data1 = Data1, @Data2 = Data2
    FROM Sales.Customer
    WHERE [ID] = @CustomerID ;

  -- call your sproc
  EXEC dbo.YOURSPROC @Data1, @Data2

  -- Get next customerId
  SELECT @CustomerID = MIN(CustomerID)
    FROM Sales.Customer
    WHERE CustomerID > @CustomerId 

END

    일부는 내가 그들에게 ID를 제공하기 위해, 먼저 임시 테이블에 그들을 넣어, 이상 볼 필요으로 VARCHAR에 나는이 방법을 사용합니다.

  9. ==============================

    9.당신이 커서 무엇을 사용하지 않는 경우 나는 (표를 얻고, 각 문에 대해 실행하고 각 시간이 SP 전화) 당신이 외부를해야 할 것 같아요 그것은 커서를 사용하는 것과 동일하지만 외부 SQL입니다. 왜 커서를 사용하지 않습니다?

    당신이 커서 무엇을 사용하지 않는 경우 나는 (표를 얻고, 각 문에 대해 실행하고 각 시간이 SP 전화) 당신이 외부를해야 할 것 같아요 그것은 커서를 사용하는 것과 동일하지만 외부 SQL입니다. 왜 커서를 사용하지 않습니다?

  10. ==============================

    10.나는 보통 그것을 그것은 꽤 몇 행의이 방법을 수행합니다

    나는 보통 그것을 그것은 꽤 몇 행의이 방법을 수행합니다

    (내가 사용하는 거라고 큰 데이터 세트에 대한 솔루션 중 하나는하지만 위에서 언급 한).

  11. ==============================

    11.DELIMITER //

    DELIMITER //

    CREATE PROCEDURE setFakeUsers (OUT output VARCHAR(100))
BEGIN

    -- define the last customer ID handled
    DECLARE LastGameID INT;
    DECLARE CurrentGameID INT;
    DECLARE userID INT;

    SET @LastGameID = 0; 

    -- define the customer ID to be handled now

    SET @userID = 0;

    -- select the next game to handle    
    SELECT @CurrentGameID = id
    FROM online_games
    WHERE id > LastGameID
    ORDER BY id LIMIT 0,1;

    -- as long as we have customers......    
    WHILE (@CurrentGameID IS NOT NULL) 
    DO
        -- call your sproc

        -- set the last customer handled to the one we just handled
        SET @LastGameID = @CurrentGameID;
        SET @CurrentGameID = NULL;

        -- select the random bot
        SELECT @userID = userID
        FROM users
        WHERE FIND_IN_SET('bot',baseInfo)
        ORDER BY RAND() LIMIT 0,1;

        -- update the game
        UPDATE online_games SET userID = @userID WHERE id = @CurrentGameID;

        -- select the next game to handle    
        SELECT @CurrentGameID = id
         FROM online_games
         WHERE id > LastGameID
         ORDER BY id LIMIT 0,1;
    END WHILE;
    SET output = "done";
END;//

CALL setFakeUsers(@status);
SELECT @status;
  12. ==============================

    12.이것에 대한 더 나은 솔루션에있다

    이것에 대한 더 나은 솔루션에있다

    이것은 당신이 깨끗한 테이블 형식의 출력을 얻을 수 있었다. 당신이 모든 행에 대해 SP를 실행하면 동안, 당신은 못생긴 각 반복에 대해 별도의 쿼리 결과를 얻을.

  13. ==============================

    13.이것은 이미 제공 한 답변의 변형이지만 ORDER BY, COUNT 또는 MIN / MAX가 필요하지 않기 때문에 더 나은 수행을해야합니다. 이 방법의 유일한 단점은 모든 ID를 보유 할 임시 테이블을 작성해야한다는 것입니다 (가정은 CustomerIDs의 목록에 격차를 가지고있다).

    이것은 이미 제공 한 답변의 변형이지만 ORDER BY, COUNT 또는 MIN / MAX가 필요하지 않기 때문에 더 나은 수행을해야합니다. 이 방법의 유일한 단점은 모든 ID를 보유 할 임시 테이블을 작성해야한다는 것입니다 (가정은 CustomerIDs의 목록에 격차를 가지고있다).

    즉이 있지만, 일반적으로 집합 기반의 접근 방식은 아직 더해야 내가 @ 마크 파월에 동의했다.

    DECLARE @tmp table (Id INT IDENTITY(1,1) PRIMARY KEY NOT NULL, CustomerID INT NOT NULL)
DECLARE @CustomerId INT 
DECLARE @Id INT = 0

INSERT INTO @tmp SELECT CustomerId FROM Sales.Customer

WHILE (1=1)
BEGIN
    SELECT @CustomerId = CustomerId, @Id = Id
    FROM @tmp
    WHERE Id = @Id + 1

    IF @@rowcount = 0 BREAK;

    -- call your sproc
    EXEC dbo.YOURSPROC @CustomerId;
END
  14. ==============================

    14.경우에는 순서가 중요하다

    경우에는 순서가 중요하다

    --declare counter
DECLARE     @CurrentRowNum BIGINT = 0;
--Iterate over all rows in [DataTable]
WHILE (1 = 1)
    BEGIN
        --Get next row by number of row
        SELECT TOP 1 @CurrentRowNum = extendedData.RowNum
                    --here also you can store another values
                    --for following usage
                    --@MyVariable = extendedData.Value
        FROM    (
                    SELECT 
                        data.*
                        ,ROW_NUMBER() OVER(ORDER BY (SELECT 0)) RowNum
                    FROM [DataTable] data
                ) extendedData
        WHERE extendedData.RowNum > @CurrentRowNum
        ORDER BY extendedData.RowNum

        --Exit loop if no more rows
        IF @@ROWCOUNT = 0 BREAK;

        --call your sproc
        --EXEC dbo.YOURSPROC @MyVariable
    END
  15. ==============================

    15.나는 아래의 코드를위한 프레임 워크입니다, 한 번에 20 명의 직원을 처리 할 수있는 몇 가지 생산 코드를했다. 난 그냥 생산 코드를 복사하여 아래의 물건을 제거했습니다.

    나는 아래의 코드를위한 프레임 워크입니다, 한 번에 20 명의 직원을 처리 할 수있는 몇 가지 생산 코드를했다. 난 그냥 생산 코드를 복사하여 아래의 물건을 제거했습니다.

    ALTER procedure GetEmployees
    @ClientId varchar(50)
as
begin
    declare @EEList table (employeeId varchar(50));
    declare @EE20 table (employeeId varchar(50));

    insert into @EEList select employeeId from Employee where (ClientId = @ClientId);

    -- Do 20 at a time
    while (select count(*) from @EEList) > 0
    BEGIN
      insert into @EE20 select top 20 employeeId from @EEList;

      -- Call sp here

      delete @EEList where employeeId in (select employeeId from @EE20)
      delete @EE20;
    END;

  RETURN
end
  16. ==============================

    16.(여전히 커서를 사용하여 매우 유사하다하지만) 나는이 비슷한을 좋아

    (여전히 커서를 사용하여 매우 유사하다하지만) 나는이 비슷한을 좋아

    [암호]

    -- Table variable to hold list of things that need looping
DECLARE @holdStuff TABLE ( 
    id INT IDENTITY(1,1) , 
    isIterated BIT DEFAULT 0 , 
    someInt INT ,
    someBool BIT ,
    otherStuff VARCHAR(200)
)

-- Populate your @holdStuff with... stuff
INSERT INTO @holdStuff ( 
    someInt ,
    someBool ,
    otherStuff
)
SELECT  
    1 , -- someInt - int
    1 , -- someBool - bit
    'I like turtles'  -- otherStuff - varchar(200)
UNION ALL
SELECT  
    42 , -- someInt - int
    0 , -- someBool - bit
    'something profound'  -- otherStuff - varchar(200)

-- Loop tracking variables
DECLARE @tableCount INT
SET     @tableCount = (SELECT COUNT(1) FROM [@holdStuff])

DECLARE @loopCount INT
SET     @loopCount = 1

-- While loop variables
DECLARE @id INT
DECLARE @someInt INT
DECLARE @someBool BIT
DECLARE @otherStuff VARCHAR(200)

-- Loop through item in @holdStuff
WHILE (@loopCount <= @tableCount)
    BEGIN

        -- Increment the loopCount variable
        SET @loopCount = @loopCount + 1

        -- Grab the top unprocessed record
        SELECT  TOP 1 
            @id = id ,
            @someInt = someInt ,
            @someBool = someBool ,
            @otherStuff = otherStuff
        FROM    @holdStuff
        WHERE   isIterated = 0

        -- Update the grabbed record to be iterated
        UPDATE  @holdAccounts
        SET     isIterated = 1
        WHERE   id = @id

        -- Execute your stored procedure
        EXEC someRandomSp @someInt, @someBool, @otherStuff

    END

    [/암호]

    내가 루프를 통해 내가 반복 처리로 컬렉션에서 최고 기록을 삭제하지 않아도 참고하면 신원 또는 임시 / 변수 테이블에 isIterated 열을하지 않아도, 난 그냥이 방법을 선호한다.

    17. from https://stackoverflow.com/questions/1656804/sql-call-stored-procedure-for-each-row-without-using-a-cursor by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] COUNTIF 집계 함수의 SQL 서버에 해당  (0) 2020.03.29
[SQL] 어떻게 엑셀 테이블에 SQL 쿼리를 실행하려면?  (0) 2020.03.29
[SQL] 가장 빠른 방법은 매우 큰 테이블의 행의 정확한 수를 계산하려면?  (0) 2020.03.29
[SQL] 어떻게 데이터베이스에 걸쳐 문자 세트 (및 정렬을) 변경하려면?  (0) 2020.03.29
[SQL] 무엇 MySQL의 데이터베이스 테이블 및 관계 조건 질문과 Q & A 설문 조사를 지원하는 것? [닫은]  (0) 2020.03.29
,

티스토리툴바