学习网上写的存储过程和函数,不知道该如何调用了,请各位高手帮忙看一下,需要用到数组.

2008-03-20 09:54:09 83 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

IFIX往Mysql数据库同步数据方法通过IFix数据库向Mysql数据库同步数据阅读详情

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);

...全文