常用SQL

CREATE DEFINER=`root`@`%` PROCEDURE `tangc`.`p_for_create_customer`()
begin
declare ii int default 0;
declare i int default 1;
declare minss int default 0;
declare idd int;
declare start_d datetime;
declare channel_code_ VARCHAR(255);
declare end_d datetime;
declare minutes int;
select id,countt,start_date,end_date,channel_code into idd,ii,start_d,end_d,channel_code_ from schedu_create_info where is_effective='true' limit 1;
select timestampdiff(minute,start_d,end_d) into minss from dual;
select idd from dual;
loop_example : loop
update schedu_create_info set is_effective='false' where id = idd;
set i = i + 1;
insert into crm_t_customer (mobile ,name ,idcard,channel_code,create_date)
value(CONCAT('1*********',rand_num(0,9)),'***','***',channel_code_,DATE_ADD(start_d,INTERVAL rand_num(0,minss) MINUTE) );
if i >ii then
leave loop_example;
end if;
end loop;
end

 

 

 

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)