쿼리에 의해 반환되는 각 행에 대해 저장 프로시저를 한 번 실행하려면 어떻게 해야 합니까?

쿼리에 의해 반환되는 각 행에 대해 저장 프로시저를 한 번 실행하려면 어떻게 해야 합니까?

특정 방법으로 사용자 데이터를 변경하는 저장 프로시저가 있습니다.user_id를 전달하면 됩니다.테이블에서 쿼리를 실행하고 각 user_id에 대해 해당 user_id에 대해 스토어드 프로시저를 한 번 실행합니다.

이거에 대한 질의는 어떻게 써야 하나요?

커서를 사용하다

부록: [MS SQL 커서 예시]

declare @field1 int
declare @field2 int
declare cur CURSOR LOCAL for
    select field1, field2 from sometable where someotherfield is null

open cur

fetch next from cur into @field1, @field2

while @@FETCH_STATUS = 0 BEGIN

    --execute your sproc on each row
    exec uspYourSproc @field1, @field2

    fetch next from cur into @field1, @field2
END

close cur
deallocate cur

다음은 MS SQL의 예시 기사입니다.

커서는 설정 기반 작업보다 느리지만 수동 루프보다는 빠릅니다. 자세한 내용은 이 SO 질문에 나와 있습니다.

ADDENDUM 2: 레코드를 여러 개 처리하는 경우 먼저 임시 테이블로 가져와 커서를 임시 테이블 위에 놓습니다.이렇게 하면 SQL이 테이블 잠금으로 에스컬레이션되지 않고 작업이 고속화됩니다.

ADDENDUM 3: 물론 스토어드 프로시저가 하고 있는 모든 작업을 각 사용자 ID에 인라인화하여 단일 SQL 업데이트 스테이트먼트로서 실행할 수 있다면 최적의 방법입니다.

루프가 필요한 경우 메서드를 변경해 보십시오.

부모 스토어드 프로시저 내에서 처리할 필요가 있는 데이터를 포함하는 #syslog 테이블을 만듭니다.하위 저장 프로시저를 호출하면 #temp 테이블이 표시되고 처리할 수 있습니다.커서나 루프 없이 전체 데이터 세트를 사용할 수 있습니다.

의 는, #로부터 「 루프 없이 1 수 .UPDATE-ing 의 경우는, #temp 테이블에의 참가로부터 「업데이트」해, 루프 없이 1 개의 스테이트먼트로 모든 작업을 실행할 수 있습니다.INSERT 에 DELETE 、 DELETE 。 를 는, 의 IF 로 할 수 .UPDATE FROM#temp p # # CASE 문 # WHERE WHERE WERE # # # # # # 。

데이터베이스에서 작업하는 경우 루프에 대한 인식을 상실하려고 하면 실제 성능 저하가 발생하여 잠금/차단을 유발하고 처리 속도를 늦춥니다.모든 곳에 루프를 설치하면 시스템 확장이 잘 되지 않고 사용자가 업데이트 속도가 느리다고 불평하기 시작하면 속도를 높이기가 매우 어려워집니다.

호출하고 싶은 이 프로시저의 내용을 루프에 투고하면, 10번 중 9번은 일련의 행에서 작업할 수 있습니다.

동적 쿼리를 사용하여 수행할 수 있습니다.

declare @cadena varchar(max) = ''
select @cadena = @cadena + 'exec spAPI ' + ltrim(id) + ';'
from sysobjects;
exec(@cadena);

테이블과 필드 이름에는 이와 같은 대체가 필요합니다.

Declare @TableUsers Table (User_ID, MyRowCount Int Identity(1,1)
Declare @i Int, @MaxI Int, @UserID nVarchar(50)

Insert into @TableUser
Select User_ID
From Users 
Where (My Criteria)
Select @MaxI = @@RowCount, @i = 1

While @i <= @MaxI
Begin
Select @UserID = UserID from @TableUsers Where MyRowCount = @i
Exec prMyStoredProc @UserID
Select

 @i = @i + 1, @UserID = null
End

테이블 변수 또는 임시 테이블을 사용합니다.

앞에서 설명한 바와 같이 커서는 최후의 수단입니다.대부분의 경우 리소스를 많이 사용하고 잠금 문제가 발생하며 SQL 사용 방법을 제대로 이해하지 못하는 징후일 수 있습니다.

사이드 노트:커서를 사용하여 테이블의 행을 업데이트하는 솔루션을 발견한 적이 있습니다.정밀 조사 결과 모든 것을 하나의 UPDATE 명령어로 대체할 수 있음이 판명되었습니다.그러나 이 경우 저장 프로시저를 실행해야 하는 경우에는 단일 SQL 명령이 작동하지 않습니다.

다음과 같은 테이블 변수를 만듭니다(많은 데이터를 사용하거나 메모리가 부족한 경우 대신 임시 테이블을 사용하십시오).

DECLARE @menus AS TABLE (
    id INT IDENTITY(1,1),
    parent NVARCHAR(128),
    child NVARCHAR(128));

id요합니니다다

parent ★★★★★★★★★★★★★★★★★」child적절한 데이터(예: 관련 식별자 또는 전체 데이터 세트)를 제공합니다.

테이블에 데이터를 삽입합니다. 예:

INSERT INTO @menus (parent, child) 
  VALUES ('Some name',  'Child name');
...
INSERT INTO @menus (parent,child) 
  VALUES ('Some other name', 'Some other child name');

일부 변수를 선언합니다.

DECLARE @id INT = 1;
DECLARE @parentName NVARCHAR(128);
DECLARE @childName NVARCHAR(128);

마지막으로 테이블의 데이터 위에 while loop을 만듭니다.

WHILE @id IS NOT NULL
BEGIN
    SELECT @parentName = parent,
           @childName = child 
        FROM @menus WHERE id = @id;

    EXEC myProcedure @parent=@parentName, @child=@childName;

    SELECT @id = MIN(id) FROM @menus WHERE id > @id;
END

첫 번째 선택 항목은 임시 테이블에서 데이터를 가져옵니다.@id로 하다 MIN무효로 하다

행이 입니다.SELECT TOP 1선택한 행을 임시 테이블에서 제거합니다.

WHILE EXISTS(SELECT 1 FROM @menuIDs) 
BEGIN
    SELECT TOP 1 @menuID = menuID FROM @menuIDs;

    EXEC myProcedure @menuID=@menuID;

    DELETE FROM @menuIDs WHERE menuID = @menuID;
END;

저장 프로시저가 수행하는 모든 작업을 복제하는 사용자 정의 기능을 사용하면 이 작업을 수행할 수 없습니까?

SELECT udfMyFunction(user_id), someOtherField, etc FROM MyTable WHERE WhateverCondition

여기서 udfMyFunction은 사용자 ID를 가져와 필요한 작업을 수행하는 사용자가 만드는 함수입니다.

상세한 것에 대하여는, http://www.sqlteam.com/article/user-defined-functions 를 참조해 주세요.

가능한 한 커서는 피해야 한다는 데 동의합니다.그리고 그것은 보통 가능하다!

(물론 제 답변은 SP에서 출력만 받고 실제 데이터는 변경하지 않는 것을 전제로 하고 있습니다.「사용자 데이터를 특정 방법으로 변경한다」라고 하는 것은, 당초의 질문과는 조금 애매하다고 생각되기 때문에, 가능한 해결책으로서 이것을 제안합니다.어떻게 하느냐에 따라 다르죠!

Dave Rincon의 역동적인 쿼리 방식은 커서를 사용하지 않고 작고 쉽기 때문에 좋아합니다.데이브 씨, 공유해 주셔서 감사합니다.

그러나 Azure SQL에 대한 요구와 쿼리에 "구분"이 있는 경우 다음과 같이 코드를 수정해야 했습니다.

Declare @SQL nvarchar(max);
-- Set SQL Variable
-- Prepare exec command for each distinctive tenantid found in Machines 
SELECT @SQL = (Select distinct 'exec dbo.sp_S2_Laser_to_cache ' + 
              convert(varchar(8),tenantid) + ';' 
              from Dim_Machine
              where iscurrent = 1
              FOR XML PATH(''))

--for debugging print the sql 
print @SQL;

--execute the generated sql script
exec sp_executesql @SQL;

이게 누군가에게 도움이 됐으면 좋겠는데...

언급URL : https://stackoverflow.com/questions/886293/how-do-i-execute-a-stored-procedure-once-for-each-row-returned-by-query