Custom array and Application of oracle technology

Custom array and Application of oracle technology ------------------ create or replace type Varchar2Varray is varray (100) of VARCHAR2 (40 ); /---------------- separated by specific symbols and stored in the array create or replace function sf_split_string (string VARCHAR2, substring VARCHAR2) RETURN Varchar2Varray IS len integer: = LENGTH (substring); lastpos integer: = 1-len; pos integer; num integer; I integer: = 1; ret Varchar2Varray: = Varchar2Varray (NULL); BEGIN LOOP pos: = instr (string, substring, lastpos + len); IF pos> 0 THEN -- found num: = pos-(lastpos + len); ELSE -- not found num: = LENGTH (string) + 1-(lastpos + len); end if; IF I> ret. last then ret. EXTEND; end if; ret (I): = SUBSTR (string, lastpos + len, num); exit when pos = 0; lastpos: = pos; I: = I + 1; end loop; RETURN ret; END;/----------------------------- calculate the total number of records for specific characters in a string. create or replace function str_cnt (aa varchar2, bb varchar2) return numberisnum number (10 ): = 0; beginfor I in 1 .. length (aa) loopif instr (aa, bb, 1, I)> 0 then num: = num + 1; elsereturn num; end if; end loop; return num; end; /configure the Stored PROCEDURE ------------------- v_ids: input string ------------------- sub: the specified delimiter rowCnt: receives the parameter create or replace procedure pro_updateCount (v_ids in VARCHAR2, sub in VARCHAR2, rowCnt out Number) AScnt Number: = 0; ret Varchar2Varray: = Varchar2Varray (NULL); BEGIN ret: = sf_split_string (v_ids, sub); FOR I in 1 .. str_cnt (v_ids, sub) LOOP update tbl_login_member t set t. accountid = SEQ_PRO_UPDATECOUNT.Nextval where t. userid = ret (I); dbms_output.put_line (ret (I); if (SQL % rowcount = 1) then begin cnt: = cnt + 1; end if; END LOOP; rowCnt: = cnt; END;/---------------- receive value declare cout number; begin pro_updateCount ('ms006, MS005, ms004, ', cout); dbms_output.put_line (cout ); end;/if exists (select * from sysobjects where name = 'Pro _ updateCount ') drop proc pro_updateCountgocreate PROCEDURE pro_updateCount @ str nvarchar (200), @ count int OUTPUT, @ I int, @ index_len int, @ substr nvarchar (20), @ sequence intASselect @ count = 0 select @ I = 1 select @ sequence = (select MAX (t. accountID) from tbl_login_member t) while 1 = 1 begin select @ index_len = charindex (',', @ str, @ I) if (@ index_len = 0) break; else beginselect @ sequence = @ sequence + 1; select @ substr = substring (@ str, @ I, @ index_len-@ I ); update tbl_login_member set accountid = @ sequence where userid = @ substr; if @ ROWCOUNT <> 0 begin select @ count = @ count + 1; endselect @ I = @ index_len + 1; print (@ sequence); print (@ substr); endend return @ count; go