常用SQL

1 统计登陆信息SQL

create or replace function Get_StrArrayStrOfIndex
(
av_str varchar2, --要分割的字符串
av_split varchar2, --分隔符号
av_index number --取第几个元素
)
return varchar2
is
lv_str varchar2(1024);
lv_strOfIndex varchar2(1024);
lv_length number;
begin
lv_str:=ltrim(rtrim(av_str));
lv_str:=concat(lv_str,av_split);
lv_length:=av_index;
if lv_length=0 then
lv_strOfIndex:=substr(lv_str,1,instr(lv_str,av_split)-length(av_split));
else
lv_length:=av_index+1;
lv_strOfIndex:=substr(lv_str,instr(lv_str,av_split,1,av_index)+length(av_split),instr(lv_str,av_split,1,lv_length)-instr(lv_str,av_split,1,av_index)-length(av_split));
end if;
return lv_strOfIndex;
end Get_StrArrayStrOfIndex;

 select Get_StrArrayStrOfIndex(‘songguojun$@111111537‘,‘$‘,0) from dual 

-- Create table
create table AA_TEST
(
V1 VARCHAR2(500),
V2 VARCHAR2(500),
V3 VARCHAR2(500),
V4 VARCHAR2(500),
V5 VARCHAR2(500),
V6 VARCHAR2(500),
V7 VARCHAR2(500),
V8 VARCHAR2(500),
V9 VARCHAR2(500),
V10 VARCHAR2(500),
V11 VARCHAR2(500),
V12 VARCHAR2(500),
V13 VARCHAR2(500),
V14 VARCHAR2(500),
V15 VARCHAR2(500)
)

update aa_test t set 
t.v4=Get_StrArrayStrOfIndex(t.v3,‘,‘,0)
,t.v5=Get_StrArrayStrOfIndex(t.v3,‘,‘,1)
,t.v6=Get_StrArrayStrOfIndex(t.v3,‘,‘,2)
,t.v7=Get_StrArrayStrOfIndex(t.v3,‘,‘,3)
,t.v8=Get_StrArrayStrOfIndex(t.v3,‘,‘,4)
,t.v9=Get_StrArrayStrOfIndex(t.v3,‘,‘,5)
,t.v10=Get_StrArrayStrOfIndex(t.v3,‘,‘,6)
,t.v11=Get_StrArrayStrOfIndex(t.v3,‘,‘,7)
,t.v12=Get_StrArrayStrOfIndex(t.v3,‘,‘,8)
,t.v13=Get_StrArrayStrOfIndex(t.v3,‘,‘,9)

常用SQL,古老的榕树,5-wow.com