**已关闭。**此问题需要debugging details。它目前不接受回答。
编辑问题以包括desired behavior, a specific problem or error, and the shortest code necessary to reproduce the problem。这将帮助其他人回答这个问题。
关闭7天前。
Improve this question
我在Oracle中有一个主过程，它调用其他较小的过程，即使它们中的每一个在单独运行时都能正常工作，但当我试图运行主过程时，什么也没有发生。没有错误，但没有结果枯萎。我想知道我是否在代码中遗漏了一些东西：
create or replace PROCEDURE ADMIN.sp_league_pool_users_master AS
v_SEASON_NUMBER_DERIVED NUMBER;
v_LEAGUE_COUNTRY VARCHAR2(100);
v_LEAGUE_LEVEL NUMBER;
v_ALPHA2CODE_DERIVED VARCHAR2(2);
v_count NUMBER;
BEGIN
-- Step 1: Derive the Season Number
ADMIN.sp_deriveSeasonNumber(v_SEASON_NUMBER_DERIVED);
-- Step 2: Scan the LEAGUE_POOL table
FOR r IN (SELECT lp.LEAGUE_POOL_ID, lp.LEAGUE_COUNTRY, lp.LEAGUE_LEVEL FROM LEAGUE_POOL lp) LOOP
v_LEAGUE_COUNTRY := r.LEAGUE_COUNTRY;
v_LEAGUE_LEVEL := r.LEAGUE_LEVEL;
-- Step 3: Derive ALPHA2CODE_DERIVED and check for existing records
CASE r.LEAGUE_COUNTRY
WHEN 'Rest of Africa' THEN v_ALPHA2CODE_DERIVED := 'BJ';
WHEN 'Rest of Asia and Oceania' THEN v_ALPHA2CODE_DERIVED := 'AF';
WHEN 'Rest of North America' THEN v_ALPHA2CODE_DERIVED := 'AG';
WHEN 'Rest of South America' THEN v_ALPHA2CODE_DERIVED := 'FK';
ELSE
-- Fetch from the COUNTRIES table
SELECT ct.ALPHA2CODE INTO v_ALPHA2CODE_DERIVED
FROM COUNTRIES ct WHERE ct.LEAGUE_COUNTRY = r.LEAGUE_COUNTRY;
END CASE;
SELECT COUNT(*) INTO v_count FROM LEAGUE_POOL_USERS lpu
WHERE lpu.LEAGUE_POOL_ID = r.LEAGUE_POOL_ID AND lpu.SEASON_NUMBER = v_SEASON_NUMBER_DERIVED;
IF v_count > 0 THEN
SELECT COUNT(*) INTO v_count
FROM LEAGUE_POOL_USERS
WHERE LEAGUE_POOL_ID = r.LEAGUE_POOL_ID AND SEASON_NUMBER = v_SEASON_NUMBER_DERIVED + 1;
IF v_count = 0 THEN
-- Step 4: Call the appropriate level procedure based on LEAGUE_LEVEL
CASE r.LEAGUE_LEVEL
WHEN 1 THEN
ADMIN.sp_handleLeagueLevel1(v_SEASON_NUMBER_DERIVED, v_LEAGUE_COUNTRY);
WHEN 2 THEN
ADMIN.sp_handleLeagueLevel2(v_SEASON_NUMBER_DERIVED, v_LEAGUE_COUNTRY);
WHEN 3 THEN
ADMIN.sp_handleLeagueLevel3(v_SEASON_NUMBER_DERIVED, v_LEAGUE_COUNTRY);
WHEN 4 THEN
ADMIN.sp_handleLeagueLevel4(v_SEASON_NUMBER_DERIVED, v_LEAGUE_COUNTRY);
END CASE;
END IF;
ELSE
-- If the LEAGUE_POOL_ID doesn't exist in LEAGUE_POOL_USERS, call the insert procedure
ADMIN.sp_insertLeaguePoolUsers(v_SEASON_NUMBER_DERIVED, v_LEAGUE_COUNTRY, v_LEAGUE_LEVEL, r.LEAGUE_POOL_ID);
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('An error occurred in sp_league_pool_users_master: ' || SQLERRM);
END sp_league_pool_users_master;
1条答案
按热度按时间mctunoxg1#
正如在对您的问题的评论中提到的，很难帮助您，因为我们没有任何样本数据来运行上述过程，所以我们不能说为什么它不像您期望的那样工作。也许问题出在其他地方，在它所要求的程序中？
但是，您可以使调试过程更加容易（也许同时还可以提高性能！），方法是将循环内的select语句合并到游标中。这样，您就可以单独运行查询（适当地修改季节细节），以查看该过程应该在单个数据集中执行什么操作，而不必逐行调试该过程。
我花时间重写了你的程序，虽然它显然是未经测试的！它应该给给予你一个如何继续的想法：
注意：是否需要围绕alpha2code_derived国家代码的逻辑？在你的手术中似乎没有用到它！