oracle 多数值录入校验(分隔符“/”)
2020-11-11 16:54 sunice 阅读(277) 评论(0) 编辑 收藏 举报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
RAISE_APPLICATION_ERROR( -20001 , '多数值文本框不允许录入单个数值' ); END IF; END IF; IF(length(multivalue_sub_)>length(trim(multivalue_sub_))) THEN RAISE_APPLICATION_ERROR( -20001 , '不允许录入空格' ); 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 RAISE_APPLICATION_ERROR( -20001 , '只能用一个/分隔' );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 RAISE_APPLICATION_ERROR( -20001 , '不允许录入空格' );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 RAISE_APPLICATION_ERROR( -20001 , '多数值部分用来填写多个数字或选择值列表中的字符,多个数字用/隔开' );
END Check_New_Multivalue;