创建Oracle存储过程报PLS-00320等错误问题咨询

原存储过程代码 #

CREATE OR REPLACE PROCEDURE INSERT_TIER_INFO(in_lp_code IN VARCHAR2 , in_tier IN VARCHAR2 , in_pts_reqd IN VARCHAR2 , in_multiplier IN VARCHAR2 , in_no_tiers NUMBER)
AS
    low_bound NUMBER := 0 ;
    current_tier VARCHAR2(32) ;
    current_points NUMBER ;
    current_multiplier NUMBER ;
    upper_bound NUMBER := in_no_tiers ;
    curr_level NUMBER := 0 ;
    seperator VARCHAR2(2) := "," ;

BEGIN
    FOR j IN low_bound..upper_bound LOOP
    current_tier  := split(in_tier , seperator , j) ;
    current_points := TO_NUMBER(split(in_pts_reqd , seperator , j)) ;
    current_multiplier := TO_NUMBER(split(in_pts_reqd , seperator , j)) ;

    INSERT INTO TIERED_LOYALTY_PROGRAM(LP_CODE, STATE,TIER1,POINTS_REQUIRED, MULTIPLIER, LEVEL1)
    VALUES (in_lp_code , 0 , current_tier  , current_points , current_multiplier , j) ;

    END LOOP;
END;
/

报错信息 #

Errors: PROCEDURE INSERT_TIER_INFO
Line/Col: 9/12 PL/SQL: Item ignored
Line/Col: 9/28 PLS-00201: identifier ',' must be declared
Line/Col: 13/2 PL/SQL: Statement ignored
Line/Col: 13/35 PLS-00320: the declaration of the type of this expression is incomplete or malformed
Line/Col: 14/3 PL/SQL: Statement ignored
Line/Col: 14/51 PLS-00320: the declaration of the type of this expression is incomplete or malformed
Line/Col: 15/2 PL/SQL: Statement ignored
Line/Col: 15/54 PLS-00320: the declaration of the type of this expression is incomplete or malformed

报错原因 #

• 字符串常量引号使用错误：Oracle PL/SQL中，字符串字面量必须使用单引号'包裹，原代码中分隔符赋值使用了双引号","，Oracle会将双引号内容识别为数据库对象标识符（如表名、字段名、变量名），因此抛出,标识符未定义的错误，这是后续所有赋值语句报错的根因。
• 变量赋值逻辑错误：current_multiplier赋值时错误使用了in_pts_reqd参数的拆分结果，应该拆分in_multiplier参数。
• 循环边界错误：自定义split函数的索引从0开始，最多取到in_no_tiers - 1，原代码循环上限设为in_no_tiers会多循环一次，导致split函数返回空值或执行异常。

修复后代码 #

CREATE OR REPLACE PROCEDURE INSERT_TIER_INFO(in_lp_code IN VARCHAR2 , in_tier IN VARCHAR2 , in_pts_reqd IN VARCHAR2 , in_multiplier IN VARCHAR2 , in_no_tiers NUMBER)
AS
    low_bound NUMBER := 0 ;
    current_tier VARCHAR2(32) ;
    current_points NUMBER ;
    current_multiplier NUMBER ;
    upper_bound NUMBER := in_no_tiers - 1; -- 修正循环上限
    curr_level NUMBER := 0 ;
    seperator VARCHAR2(2) := ',' ; -- 修正为单引号包裹字符串
BEGIN
    FOR j IN low_bound..upper_bound LOOP
        current_tier  := split(in_tier , seperator , j) ;
        current_points := TO_NUMBER(split(in_pts_reqd , seperator , j)) ;
        current_multiplier := TO_NUMBER(split(in_multiplier , seperator , j)) ; -- 修正为拆分in_multiplier参数

        INSERT INTO TIERED_LOYALTY_PROGRAM(LP_CODE, STATE,TIER1,POINTS_REQUIRED, MULTIPLIER, LEVEL1)
        VALUES (in_lp_code , 0 , current_tier  , current_points , current_multiplier , j) ;
    END LOOP;
END;
/

如果split函数不属于当前用户，需要确保当前用户有该函数的执行权限，也可以在函数前加上所属用户前缀，比如user_name.split(xxx)。

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