分区语句整理 5

begin

    /**

     * @description exc_error1 will be raised if iv_username is null and the input string have 4 split symbol

     * @description get the users' number which is from input parameter

     */

    if (iv_username is null) then

    raise exc_error1;

    elsif instr(iv_username,',',1,4)>0 then

    raise exc_error1;

    end if;

    vi_users_num:=((length(iv_username)-length(replace(iv_username,',')))/length(','))+1;

    /**

     * @description split the input parameter, get the exactly user name and check whether them are exist in the system,

                    raise the exc_error3 if some one is not exist.

     */

    vv_capi_usernam := upper(iv_username);

    if vi_users_num = 1 then

        vv_user1 := vv_capi_usernam;

        vv_user2 := null;

        vv_user3 := null;

        vv_user4 := null;

        execute immediate 'select count(*) from all_users t where t.username='''||vv_user1||'''' into vi_users_flag ;

        if vi_users_flag <> 1 then

            raise exc_error3;

        end if;

    elsif vi_users_num = 2 then

        vv_user1 := substr(vv_capi_usernam,1,instr(vv_capi_usernam,',',1,1)-1);

        vv_user2 := substr(vv_capi_usernam,instr(vv_capi_usernam,',',1,1)+1,(length(vv_capi_usernam)-instr(vv_capi_usernam,',',1,1)));

        vv_user3 := null;

        vv_user4 := null;

        execute immediate 'select count(*) from all_users t where t.username in ('''||vv_user1||''','''||vv_user2||''')'

                          into vi_users_flag ;