커서를 사용하지 않고 각 행에 대한 SQL 호출 저장 프로 시저
커서 를 사용 하지 않고 행의 열이 sp에 대한 입력 매개 변수 인 테이블의 각 행에 대해 저장 프로 시저를 어떻게 호출 할 수 있습니까?
일반적으로 말하면 항상 세트 기반 접근 방식을 찾습니다 (때로는 스키마 변경 비용을 희생).
그러나이 스 니펫은 그 자리에 있습니다 ..
-- 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
예를 들어, AdventureWorks Sales.Customer샘플 테이블을 사용하여 CustomerID로 테이블을 주문 하고 WHILE 루프를 사용하여 해당 고객을 반복하십시오.
-- 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
ORDER BY어떤 열에 어떤 종류의 열을 정의 할 수 있다면 어떤 테이블에서도 작동합니다 .
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)
좋아, 그런 코드를 프로덕션에 넣지 않을 것이지만 요구 사항을 충족시킵니다.
마크의 대답은 좋다 (I 방법으로 해결할 수 있다면 그것에 대해 언급 것!)
그냥 내가 그래서는 루프를 변경하는 것이 더 나은 수 있음을 지적 줄 알았는데 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
저장 프로 시저를 테이블을 반환하는 함수로 변환 할 수 있으면 교차 적용을 사용할 수 있습니다.
예를 들어, 고객 테이블이 있고 주문 합계를 계산하려는 경우 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
)
분명히 위의 예는 단일 쿼리에서 사용자 정의 함수없이 수행 될 수 있습니다.
단점은 함수가 매우 제한되어 있다는 것입니다. 저장 프로 시저의 많은 기능은 사용자 정의 함수에서 사용할 수 없으며 저장 프로 시저를 함수로 변환하는 것이 항상 작동하지는 않습니다.
SQL Server 2005 이후 버전에서는 CROSS APPLY 및 테이블 반환 함수를 사용 하여이 작업을 수행 할 수 있습니다 .
명확히하기 위해 저장 프로 시저를 테이블 값 함수로 변환 할 수있는 경우를 언급하고 있습니다.
허용 된 답변을 사용하지만 다른 가능성은 테이블 변수를 사용하여 번호가 매겨진 값 세트 (이 경우 테이블의 ID 필드)를 보유하고 행 번호를 기준으로 테이블에 JOIN을 사용하여 반복합니다. 루프 내에서 필요한 조치를 검색하십시오.
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
이것은 위의 n3rds 솔루션의 변형입니다. MIN ()이 사용되므로 ORDER BY를 사용하여 정렬 할 필요가 없습니다.
CustomerID (또는 진행에 사용하는 다른 숫자 열)에는 고유 한 제약 조건이 있어야합니다. 또한 가능한 빨리 고객 ID를 색인화해야합니다.
-- 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에 사용합니다.
커서를 사용하지 않는 경우 외부에서 수행해야한다고 생각합니다 (테이블을 가져 와서 각 문마다 실행하고 sp를 호출 할 때마다) 커서를 사용하는 것과 동일하지만 외부에서만 SQL. 왜 커서를 사용하지 않습니까?
나는 꽤 많은 행이있을 때 일반적 으로이 방법을 수행합니다.
- SQL Management Studio를 사용하여 데이터 세트에서 모든 sproc 매개 변수를 선택하십시오.
- 마우스 오른쪽 버튼으로 클릭-> 복사
- 탁월하게 붙여 넣기
- 새 Excel 열에 '= "EXEC schema.mysproc @ param ="& A2'와 같은 수식을 사용하여 단일 행 SQL 문을 작성하십시오. (여기서 A2는 매개 변수를 포함하는 엑셀 열입니다)
- excel 문의 목록을 SQL Management Studio의 새 쿼리에 복사하고 실행하십시오.
- 끝난.
(더 큰 데이터 세트에서는 위에서 언급 한 솔루션 중 하나를 사용합니다).
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;
이것은 이미 제공된 답변의 변형이지만 ORDER BY, COUNT 또는 MIN / MAX가 필요하지 않기 때문에 성능이 더 좋습니다. 이 방법의 유일한 단점은 모든 ID를 보유 할 임시 테이블을 작성해야한다는 것입니다 (고객 ID 목록에 공백이 있다고 가정).
즉, @Mark Powell에 동의하지만 일반적으로 집합 기반 접근 방식이 여전히 낫습니다.
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
순서가 중요한 경우
--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
A better solution for this is to
- Copy/past code of Stored Procedure
- Join that code with the table for which you want to run it again (for each row)
This was you get a clean table-formatted output. While if you run SP for every row, you get a separate query result for each iteration which is ugly.
I had some production code that could only handle 20 employees at a time, below is the framework for the code. I just copied the production code and removed stuff below.
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
I like to do something similar to this (though it is still very similar to using a cursor)
[code]
-- 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
[/code]
Note that you don't need the identity or the isIterated column on your temp/variable table, i just prefer to do it this way so i don't have to delete the top record from the collection as i iterate through the loop.
'Programing' 카테고리의 다른 글
| 주어진 텍스트가 포함 된 파일 찾기 (0) | 2020.06.16 |
|---|---|
| Eclipse에서 다른 폴더로 프로젝트 이동 (0) | 2020.06.16 |
| 차이의 주장, 기대 그리고해야 할 것의 차이점은 무엇입니까? (0) | 2020.06.16 |
| PHP-연관 배열의 숫자 인덱스를 얻습니다 (0) | 2020.06.16 |
| SQL 스크립트에서“기존 연결 닫기”를 지정하는 방법 (0) | 2020.06.16 |