CREATE OR REPLACE PACKAGE ABC
as
type VarTableType is table of varchar2(20000);
function str2varList2( p_string in varchar2 ) return VarTableType;
??????????????????????????
不知道该用哪种存储过程传入数值,然后调用str2varlist2函数
procedure straddtable(addstr in varchar2);
procedure straddtable2(addstr2 in vartabletype);????????????????????????????????
END ABC;--------------------------------------------------------------
CREATE OR REPLACE PACKAGE BODY ABC
AS
function str2varList2( p_string in varchar2 ) return VarTableType
as
v_str long default p_string || '$';
v_n varchar2(20000);
v_data VarTableType := VarTableType();
begin
loop
v_n :=instr( v_str, '$' );
exit when (nvl(v_n,0) = 0);
v_data.extend;
v_data( v_data.count ) := ltrim(rtrim(substr(v_str,1,v_n-1)));
v_str := substr( v_str, v_n+1 );
end loop;
return v_data;
end ;
???????存储过程不知道如何写,先调用str2varlist2函数,然后如何将数据提取出来???????
procedure straddtable(addstr in varchar2)
as
s1 varchar2(20000):='';
begin
end straddtable;
procedure straddtable2(addstr2 in vartabletype)
as begin
end straddtable2;END ABC;------------------------------------------------------------------
在SQLPLUS中测试STR2VARLIST2
传入数值如下
SQL> var str varchar2(4000);
SQL> /函数已创建。SQL> exec :str:='2008,01,00,20080112,5,收款,101,借,10000$2008,01,00,20080112,5,收款,20101,贷,2000$20
08,01,00,20080112,5,收款,20102,贷,2000$2008,01,00,20080112,5,收款,20103,贷,3000$2008,01,00,20080112,
5,收款,20104,贷,4000'PL/SQL 过程已成功完成。SQL> select str2varlist2(:str) from dual;STR2VARLIST2(:STR)
--------------------------------------------------------------------------------
VARTABLETYPE('2008,01,00,20080112,5,收款,101,借,10000', '2008,01,00,20080112,5,
收款,20101,贷,2000', '2008,01,00,20080112,5,收款,20102,贷,2000', '2008,01,00,200
80112,5,收款,20103,贷,3000', '2008,01,00,20080112,5,收款,20104,贷,4000')
----------------------------------------------------------------------------------------------------
在这里每一组数据都是一条记录,我在存储过程中如何将这5条记录取出,
然后再将数据按逗号分解出来.再运行SQL语句插入到表中.INSERT INTO A(KJND,KJQJ,PZZ,PZRQ,FJZS,ZY,KM,FX,JE)
VALUES ('2008','01','00','20080112',5,'收款','101','借',10000);INSERT INTO A(KJND,KJQJ,PZZ,PZRQ,FJZS,ZY,KM,FX,JE)
VALUES ('2008','01','00','20080112',5,'收款','20101','借',1000);INSERT INTO A(KJND,KJQJ,PZZ,PZRQ,FJZS,ZY,KM,FX,JE)
VALUES ('2008','01','00','20080112',5,'收款','20102','借',2000);INSERT INTO A(KJND,KJQJ,PZZ,PZRQ,FJZS,ZY,KM,FX,JE)
VALUES ('2008','01','00','20080112',5,'收款','20103','借',3000);INSERT INTO A(KJND,KJQJ,PZZ,PZRQ,FJZS,ZY,KM,FX,JE)
VALUES ('2008','01','00','20080112',5,'收款','20104','借',4000);
as
type VarTableType is table of varchar2(20000);
function str2varList2( p_string in varchar2 ) return VarTableType;
??????????????????????????
不知道该用哪种存储过程传入数值,然后调用str2varlist2函数
procedure straddtable(addstr in varchar2);
procedure straddtable2(addstr2 in vartabletype);????????????????????????????????
END ABC;--------------------------------------------------------------
CREATE OR REPLACE PACKAGE BODY ABC
AS
function str2varList2( p_string in varchar2 ) return VarTableType
as
v_str long default p_string || '$';
v_n varchar2(20000);
v_data VarTableType := VarTableType();
begin
loop
v_n :=instr( v_str, '$' );
exit when (nvl(v_n,0) = 0);
v_data.extend;
v_data( v_data.count ) := ltrim(rtrim(substr(v_str,1,v_n-1)));
v_str := substr( v_str, v_n+1 );
end loop;
return v_data;
end ;
???????存储过程不知道如何写,先调用str2varlist2函数,然后如何将数据提取出来???????
procedure straddtable(addstr in varchar2)
as
s1 varchar2(20000):='';
begin
end straddtable;
procedure straddtable2(addstr2 in vartabletype)
as begin
end straddtable2;END ABC;------------------------------------------------------------------
在SQLPLUS中测试STR2VARLIST2
传入数值如下
SQL> var str varchar2(4000);
SQL> /函数已创建。SQL> exec :str:='2008,01,00,20080112,5,收款,101,借,10000$2008,01,00,20080112,5,收款,20101,贷,2000$20
08,01,00,20080112,5,收款,20102,贷,2000$2008,01,00,20080112,5,收款,20103,贷,3000$2008,01,00,20080112,
5,收款,20104,贷,4000'PL/SQL 过程已成功完成。SQL> select str2varlist2(:str) from dual;STR2VARLIST2(:STR)
--------------------------------------------------------------------------------
VARTABLETYPE('2008,01,00,20080112,5,收款,101,借,10000', '2008,01,00,20080112,5,
收款,20101,贷,2000', '2008,01,00,20080112,5,收款,20102,贷,2000', '2008,01,00,200
80112,5,收款,20103,贷,3000', '2008,01,00,20080112,5,收款,20104,贷,4000')
----------------------------------------------------------------------------------------------------
在这里每一组数据都是一条记录,我在存储过程中如何将这5条记录取出,
然后再将数据按逗号分解出来.再运行SQL语句插入到表中.INSERT INTO A(KJND,KJQJ,PZZ,PZRQ,FJZS,ZY,KM,FX,JE)
VALUES ('2008','01','00','20080112',5,'收款','101','借',10000);INSERT INTO A(KJND,KJQJ,PZZ,PZRQ,FJZS,ZY,KM,FX,JE)
VALUES ('2008','01','00','20080112',5,'收款','20101','借',1000);INSERT INTO A(KJND,KJQJ,PZZ,PZRQ,FJZS,ZY,KM,FX,JE)
VALUES ('2008','01','00','20080112',5,'收款','20102','借',2000);INSERT INTO A(KJND,KJQJ,PZZ,PZRQ,FJZS,ZY,KM,FX,JE)
VALUES ('2008','01','00','20080112',5,'收款','20103','借',3000);INSERT INTO A(KJND,KJQJ,PZZ,PZRQ,FJZS,ZY,KM,FX,JE)
VALUES ('2008','01','00','20080112',5,'收款','20104','借',4000);
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货