oracle技术之自定义数组及应用-CSDN博客

--------------------自定义
数组类型CREATE OR REPLACE TYPE VARCHAR2（40）的Varchar2Varray IS VARRAY（100）;
/

------------------以特定符号分割并存储到数组

CREATE OR REPLACE FUNCTION sf_split_string（string VARCHAR2，substring VARCHAR2）RETURN Varchar2Varray IS
len integer：= LENGTH（substring）;
lastpos integer：= 1 - len;
pos整数;
数字整数;
我整数：= 1;
ret Varchar2Varray：= Varchar2Varray（NULL）;
BEGIN
LOOP
    pos：= instr（string，substring，lastpos + len）;
    IF pos> 0 THEN --found
      num：= pos - （lastpos + len）;
    ELSE - 未找到
      num：= LENGTH（字符串）+ 1 - （lastpos + len）;
    万一;

    如果我> ret.LAST THEN
      ret.EXTEND;
    万一;

ret（i）：= SUBSTR（string，lastpos + len，num）;

    EXIT WHEN pos = 0;
    lastpos := pos;
    i := i + 1;
END LOOP;

RETURN ret;
END;
/

-----------------------------计算字符串内特定字符的总记录数

create or replace function str_cnt(aa varchar2,bb varchar2)
return number
is
num number(10):=0;
begin
for i in 1..length(aa) loop
if instr(aa,bb,1,i)>0 then
num:=num+1;
else
return num;
end if;
end loop;
return num;
end;
/

-------------------创建存储过程

-------------------v_ids ：输入的字符串

-------------------sub ：指定的分割字符串

-------------------rowCnt ：接收参数

CREATE OR REPLACE PROCEDURE pro_updateCount(
     v_ids in VARCHAR2,
     sub in VARCHAR2 ,
     rowCnt out Number
)
AS
cnt 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;
end if;
END LOOP;
rowCnt := cnt;
END;
/

----------------接收值

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_updateCount
go
create PROCEDURE pro_updateCount
@str nvarchar(200),
@count int OUTPUT,
@i int,
@index_len int ,
@substr nvarchar(20),
@sequence int
AS
select @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
begin
select @sequence = @sequence+ 1;
选择@substr = SUBSTRING（@str，@i，@ index_len- @ I）;
更新tbl_login_member set accountid = @ sequence where userid = @ substr ;
如果@@ ROWCOUNT <> 0
  开始
  选择@count = @count +1;
  结束
选择@ i = @ index_len + 1;
打印（@sequence）;
打印（@substr）;
结束
结束
   返回@count ;
走