oracle 值用分隔符,oracle 多数值录入校验(分隔符“/”)

PROCEDURE Check_New_Multivalue (

c_multivalued_ IN VARCHAR2 )

IS

multivalue_temp_ NUMBER;

lov_temp_ NUMBER;

multivalue_start_ NUMBER;

multivalue_end_ NUMBER;

multivalue_sub_ VARCHAR2(10);

multivalue_trim_ VARCHAR2(10);

multivalue_index_ NUMBER;

multivalue_len_ NUMBER;

multivalue_minus_ NUMBER;

separator_number_ NUMBER;

CURSOR get_lov(c_multivalued_sub_ VARCHAR2) IS

SELECT count(1) FROM C_MULTIVALUED_LOV WHERE multivalued_text=c_multivalued_sub_;

BEGIN

OPEN get_lov(c_multivalued_);

FETCH get_lov INTO lov_temp_;

CLOSE get_lov;

IF (lov_temp_ = 0) THEN

multivalue_start_ := instr(c_multivalued_,'/');

multivalue_len_ := length(c_multivalued_);

multivalue_sub_ := substr(c_multivalued_, 0, multivalue_start_-1);

multivalue_trim_ := trim(multivalue_sub_);

IF( multivalue_start_ = 0 OR multivalue_start_=multivalue_len_ ) THEN

OPEN get_lov(multivalue_trim_);

FETCH get_lov INTO separator_number_;

CLOSE get_lov;

IF(separator_number_=0) THEN

Error_SYS.Record_General(lu_name_, 'CHECKLOT: 多数值文本框不允许录入单个数值');

END IF;

END IF;

IF(length(multivalue_sub_)>length(trim(multivalue_sub_))) THEN

Error_SYS.Record_General(lu_name_, 'CHECKLOT: 不允许录入空格');

END IF;

OPEN get_lov(multivalue_trim_);

FETCH get_lov INTO separator_number_;

CLOSE get_lov;

IF(separator_number_=0) THEN

multivalue_temp_ := CAST(multivalue_sub_ AS NUMBER);

END IF;

WHILE ( multivalue_start_ > 0 ) LOOP

multivalue_index_ := multivalue_start_ + 1;

multivalue_end_ := instr(c_multivalued_,'/',multivalue_index_);

IF(multivalue_index_ = multivalue_end_) THEN

Error_SYS.Record_General(lu_name_, 'CHECKLOT: 只能用一个/分隔');

END IF;

IF(multivalue_end_ = 0) THEN

multivalue_len_ := length(c_multivalued_);

multivalue_minus_ := multivalue_len_ + 1 - multivalue_index_;

ELSE

multivalue_minus_ := multivalue_end_-multivalue_index_;

END IF;

multivalue_sub_ := substr(c_multivalued_, multivalue_index_, multivalue_minus_);

IF(length(multivalue_sub_)>length(trim(multivalue_sub_))) THEN

Error_SYS.Record_General(lu_name_, 'CHECKLOT: 不允许录入空格');

END IF;

multivalue_trim_ := trim(multivalue_sub_);

OPEN get_lov(multivalue_trim_);

FETCH get_lov INTO separator_number_;

CLOSE get_lov;

IF(separator_number_=0) THEN

multivalue_temp_ := CAST(multivalue_sub_ AS NUMBER);

END IF;

multivalue_start_ := multivalue_end_ ;

END LOOP;

END IF;

EXCEPTION

WHEN value_error THEN

Error_SYS.Record_General(lu_name_, 'CHECKLOT: 多数值部分用来填写多个数字或选择值列表中的字符，多个数字用/隔开');

END Check_New_Multivalue;

标签：END,sub,get,校验,lov,multivalued,分隔符,oracle,multivalue

来源： https://www.cnblogs.com/sunice/p/13959533.html