如何循环执行Sp_Racf_Test存储过程直至tem_joins表无空RACF_ID（不修改存储过程）

Got it, here's how you can set up a loop to repeatedly run your Sp_Racf_Test stored procedure without touching its internal code—stopping only when there are no NULL values left in the RACF_ID column of the tem_joins table:

Solution for Oracle (PL/SQL) #

This uses PL/SQL's block structure to handle the loop and null-check logic:

DECLARE
  v_null_count NUMBER;
BEGIN
  -- Grab the initial number of NULL RACF_ID entries
  SELECT COUNT(*)
  INTO v_null_count
  FROM tem_joins
  WHERE RACF_ID IS NULL;

  -- Keep running the proc until no NULLs are left
  WHILE v_null_count > 0 LOOP
    Sp_Racf_Test; -- Execute your existing stored procedure

    -- Re-check the NULL count after each run
    SELECT COUNT(*)
    INTO v_null_count
    FROM tem_joins
    WHERE RACF_ID IS NULL;
  END LOOP;
END;
/

Solution for SQL Server (T-SQL) #

If you're working with SQL Server, use this T-SQL variant:

DECLARE @NullCount INT;

-- Get initial count of NULL RACF_ID values
SELECT @NullCount = COUNT(*)
FROM tem_joins
WHERE RACF_ID IS NULL;

-- Loop until all RACF_ID values are non-null
WHILE @NullCount > 0
BEGIN
    EXEC Sp_Racf_Test; -- Run your stored procedure

    -- Update the count to check if we can exit the loop
    SELECT @NullCount = COUNT(*)
    FROM tem_joins
    WHERE RACF_ID IS NULL;
END;

Quick Notes #

• Make sure you have the necessary permissions to execute Sp_Racf_Test and query the tem_joins table.
• If the stored procedure takes a while to run or your table is large, consider adding logging to track progress. For example, in Oracle you could add DBMS_OUTPUT.PUT_LINE('Remaining NULLs: ' || v_null_count); inside the loop, or in SQL Server use PRINT 'Remaining NULLs: ' + CAST(@NullCount AS VARCHAR);.

内容的提问来源于stack exchange，提问作者Pratik Fouzdar