T-SQL: 기존의 값 배열에 대한 루프

T-SQL: 기존의 값 배열에 대한 루프

제 시나리오는 다음과 같습니다.

특정 ID 세트에 대해 다른 저장 프로시저를 호출해야 하는 저장 프로시저가 있다고 가정해 보겠습니다.이 방법을 사용할 수 있을까요?

즉, 이 작업을 수행할 필요가 없습니다.

exec p_MyInnerProcedure 4
exec p_MyInnerProcedure 7
exec p_MyInnerProcedure 12
exec p_MyInnerProcedure 22
exec p_MyInnerProcedure 19

다음과 같은 작업을 수행합니다.

*magic where I specify my list contains 4,7,12,22,19*

DECLARE my_cursor CURSOR FAST_FORWARD FOR
*magic select*

OPEN my_cursor 
FETCH NEXT FROM my_cursor INTO @MyId
WHILE @@FETCH_STATUS = 0
BEGIN

exec p_MyInnerProcedure @MyId

FETCH NEXT FROM my_cursor INTO @MyId
END

여기서의 주된 목표는 단순한 유지보수성(비즈니스 변화에 따라 ID를 삭제/추가하기 쉬움)이며, 모든 ID를 한 줄에 나열할 수 있는 것입니다.퍼포먼스는 큰 문제가 되지 않는다

declare @ids table(idx int identity(1,1), id int)

insert into @ids (id)
    select 4 union
    select 7 union
    select 12 union
    select 22 union
    select 19

declare @i int
declare @cnt int

select @i = min(idx) - 1, @cnt = max(idx) from @ids

while @i < @cnt
begin
     select @i = @i + 1

     declare @id = select id from @ids where idx = @i

     exec p_MyInnerProcedure @id
end

이 시나리오에서는 ID를 유지하는 테이블 변수를 만듭니다.

  Declare @Ids Table (id integer primary Key not null)
  Insert @Ids(id) values (4),(7),(12),(22),(19)

--(또는 다른 테이블 값 함수를 호출하여 이 테이블을 생성합니다)

그런 다음 이 표의 행을 기준으로 루프합니다.

  Declare @Id Integer
  While exists (Select * From @Ids)
    Begin
      Select @Id = Min(id) from @Ids
      exec p_MyInnerProcedure @Id 
      Delete from @Ids Where id = @Id
    End

아니면...

  Declare @Id Integer = 0 -- assuming all Ids are > 0
  While exists (Select * From @Ids
                where id > @Id)
    Begin
      Select @Id = Min(id) 
      from @Ids Where id > @Id
      exec p_MyInnerProcedure @Id 
    End

위의 방법 중 하나는 커서보다 훨씬 빠릅니다(일반 사용자 테이블에 대해 선언됨).테이블 값 변수는 잘못 사용할 경우(행 수가 많은 매우 넓은 테이블의 경우) 성능이 저하되기 때문에 잘못된 평가를 받습니다.단, 키 값 또는 4바이트 정수를 유지하기 위해서만 사용하는 경우(이 경우처럼) 인덱스와 함께 사용하면 매우 빠릅니다.

정적 커서 변수와 분할 함수를 사용합니다.

declare @comma_delimited_list varchar(4000)
set @comma_delimited_list = '4,7,12,22,19'

declare @cursor cursor
set @cursor = cursor static for 
  select convert(int, Value) as Id from dbo.Split(@comma_delimited_list) a

declare @id int
open @cursor
while 1=1 begin
  fetch next from @cursor into @id
  if @@fetch_status <> 0 break
  ....do something....
end
-- not strictly necessary w/ cursor variables since they will go out of scope like a normal var
close @cursor
deallocate @cursor

사용자 테이블에 대해 선언된 기본 옵션이 많은 오버헤드를 발생시킬 수 있으므로 커서의 평판이 좋지 않습니다.

그러나 이 경우 오버헤드는 여기의 다른 어떤 방법보다 매우 적어집니다.STATIC은 SQL Server에 tempdb에서 결과를 구체화한 후 그 위에 반복하도록 지시합니다.이와 같은 작은 목록에는 최적의 솔루션입니다.

다음과 같이 시도할 수 있습니다.

declare @list varchar(MAX), @i int
select @i=0, @list ='4,7,12,22,19,'

while( @i < LEN(@list))
begin
    declare @item varchar(MAX)
    SELECT  @item = SUBSTRING(@list,  @i,CHARINDEX(',',@list,@i)-@i)
    select @item

     --do your stuff here with @item 
     exec p_MyInnerProcedure @item 

    set @i = CHARINDEX(',',@list,@i)+1
    if(@i = 0) set @i = LEN(@list) 
end

나는 보통 다음과 같은 접근방식을 사용한다.

DECLARE @calls TABLE (
    id INT IDENTITY(1,1)
    ,parameter INT
    )

INSERT INTO @calls
select parameter from some_table where some_condition -- here you populate your parameters

declare @i int
declare @n int
declare @myId int
select @i = min(id), @n = max(id) from @calls
while @i <= @n
begin
    select 
        @myId = parameter
    from 
        @calls
    where id = @i

        EXECUTE p_MyInnerProcedure @myId
    set @i = @i+1
end

CREATE TABLE #ListOfIDs (IDValue INT)

DECLARE @IDs VARCHAR(50), @ID VARCHAR(5)
SET @IDs = @OriginalListOfIDs + ','

WHILE LEN(@IDs) > 1
BEGIN
SET @ID = SUBSTRING(@IDs, 0, CHARINDEX(',', @IDs));
INSERT INTO #ListOfIDs (IDValue) VALUES(@ID);
SET @IDs = REPLACE(',' + @IDs, ',' + @ID + ',', '')
END

SELECT * 
FROM #ListOfIDs

절차 프로그래밍 언어(여기서는 Python)를 사용하여 DB에 연결하고 거기에서 루프를 수행합니다.이렇게 하면 복잡한 루프도 할 수 있습니다.

# make a connection to your db
import pyodbc
conn = pyodbc.connect('''
                        Driver={ODBC Driver 13 for SQL Server};
                        Server=serverName;
                        Database=DBname;
                        UID=userName;
                        PWD=password;
                      ''')
cursor = conn.cursor()

# run sql code
for id in [4, 7, 12, 22, 19]:
  cursor.execute('''
    exec p_MyInnerProcedure {}
  '''.format(id))

언급URL : https://stackoverflow.com/questions/1589214/t-sql-looping-through-an-array-of-known-values