oracle技术之自定义数组及应用
oracle技术之自定义数组及应用
--------------------自定义数组类型
CREATE OR REPLACE TYPE Varchar2Varray IS VARRAY(100) of VARCHAR2(40);
/
------------------以特定符号分割并存储到数组
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;
/
-----------------------------计算字符串内特定字符的总记录数
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;
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;
end
select @i=@index_len+1;
print(@sequence);
print(@substr);
end
end
return @count ;
go
