oracle技术之自定义数组及应用

Bertha 。 2023-10-18 17:04 23阅读 0赞

\--------------------自定义  
数组类型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][str] nvarchar(200),  
[@count][count] int OUTPUT,  
[@i][i] int,  
@index\_len int ,  
@substr nvarchar(20),  
@sequence int  
AS  
select [@count][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][str] ,@i)  
if(@index\_len=0)  
break;  
else  
begin  
select @sequence = @sequence+ 1;  
选择@substr = SUBSTRING（[@str][str]，[@i][i]，@ index\_len- @ I）;  
更新tbl\_login\_member set accountid [= @ sequence][_ sequence] where userid [= @ substr][_ substr] ;  
如果@@ ROWCOUNT <> 0  
开始  
选择[@count][count] = [@count][count] \+1;  
结束  
选择@ i = @ index\_len + 1;  
打印（@sequence）;  
打印（@substr）;  
结束  
结束  
返回[@count][count] ;  
走

[str]: http://my.oschina.net/u/145945
[count]: http://my.oschina.net/Count
[i]: http://my.oschina.net/izhuchao
[_ sequence]: mailto:=@sequence
[_ substr]: mailto:=@substr