有一段数据
zs,100@li,200@df,300
有数据表AAA,字段为下
name count求一段代码,能把数据插入到AAA中,形成表格
name count
zs 100
li 200
df 300
zs,100@li,200@df,300
有数据表AAA,字段为下
name count求一段代码,能把数据插入到AAA中,形成表格
name count
zs 100
li 200
df 300
调试欢乐多
1.数据文件主在名为c:\load.txt文件中,并建好表
2.写一个控制文件load.ctl
load data
infile c:\load.txt "str '@'"
into table aaa
fields terminated by ','
(name,
count)
3.使用sqlldr导入
sqlldr <用户名>/<密码>@<连接串名> control=load.ctl
CREATE OR REPLACE FUNCTION V_splitstr_2(p_string IN VARCHAR2, p_delimiter IN VARCHAR2)
RETURN str_split
PIPELINED
AS
v_length NUMBER := LENGTH(p_string);
v_start NUMBER := 1;
v_index NUMBER;
BEGIN
WHILE(v_start <= v_length)
LOOP
v_index := INSTR(p_string, p_delimiter, v_start); IF v_index = 0
THEN
PIPE ROW(SUBSTR(p_string, v_start));
v_start := v_length + 1;
ELSE
PIPE ROW(SUBSTR(p_string, v_start, v_index - v_start));
v_start := v_index + 1;
END IF;
END LOOP; RETURN;
END V_splitstr_2;
num1 number := 1;
tmp test%rowtype;
str varchar2(200) := 'zs,100@li,200@df,300';
strLen number;
begin
strLen := length(str);
for i in 1 .. strLen loop
if substr(str, i, 1) = ',' then
tmp.name := substr(str, num1, i - num1);
num1 := i + 1;
elsif substr(str, i, 1) = '@' then
tmp.count := substr(str, num1, i - num1);
num1 := i + 1;
insert into test values tmp;
elsif i = strLen then
tmp.count := substr(str, num1, i - num1 + 1);
insert into test values tmp;
end if;
end loop;
end;
有点复杂 表名test
2楼的实验没有成功 可否说明下 谢谢
--下面这个是主过程v_sms_history 的形式:
"tom,13810000000,内容,行政部,成功@jerry,13810000001,内容2,人事部,成功"
我是在一个包里写了这些,希望能对大家有所帮助,先不揭帖,以供拍砖!!!!
procedure saveSmsHistory(v_sms_history in varchar2)is
v_username varchar2(100);
v_phone varchar2(100);
v_sms_content varchar2(100);
v_deptid varchar2(100);
v_sendstate varchar2(100);
v_a varchar2(2000);--记录分离@后的每一个字符串
v_pos_a int;
begin
v_pos_a:=0;
loop
v_a := Sf_Token_Str(v_sms_history, v_Pos_a);
v_username:=Get_StrArrayStrOfIndex(v_a,',',0);
v_phone:=Get_StrArrayStrOfIndex(v_a,',',1);
v_sms_content:=Get_StrArrayStrOfIndex(v_a,',',2);
v_deptid:=Get_StrArrayStrOfIndex(v_a,',',3);
v_sendstate:=Get_StrArrayStrOfIndex(v_a,',',4);
exit when v_a is null;
insert into EPARE_SMS_HISTORY values(v_username,v_phone,v_sms_content,v_deptid,v_sendstate);
end loop;
end;
/**2010-4-16*/
FUNCTION sf_token_str (i_data IN VARCHAR2, io_pos IN OUT NUMBER)
RETURN VARCHAR2
IS
v_start_pos NUMBER;
v_end_pos NUMBER;
v_len NUMBER;
BEGIN
IF io_pos = 0
THEN
v_start_pos := 0;
v_end_pos := INSTR (i_data, '@', v_start_pos
+ 1);
v_len := v_end_pos
- v_start_pos; IF v_end_pos = 0
THEN
v_end_pos := LENGTH (i_data)
+ 1;
END IF; io_pos := 1;
RETURN SUBSTR (i_data, v_start_pos, v_len
- 1);
ELSE
v_start_pos := INSTR (i_data, '@', io_pos
+ 1); IF v_start_pos = LENGTH (i_data)
OR v_start_pos = 0
THEN
RETURN NULL;
ELSE
v_end_pos := INSTR (i_data, '@', v_start_pos
+ 1); IF v_end_pos = 0
THEN
v_end_pos := LENGTH (i_data)
+ 1;
END IF; v_len := v_end_pos
- v_start_pos;
io_pos := v_start_pos;
RETURN RTRIM (
LTRIM (SUBSTR (i_data, v_start_pos
+ 1, v_len
- 1))
);
END IF;
END IF;
END sf_token_str;
/*2010-4-16
av_str in varchar2, --要分割的字符串
av_split in varchar2, --分隔符号
av_index in number --取第几个元素*/
function Get_StrArrayStrOfIndex(av_str in varchar2,av_split in varchar2,av_index in 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 ;