我写了一个存储过程,
create or replace type flowlist as varray(12) of varchar2(20);
////////////
create or replace procedure flowset_pro(
flowstr flowlist,
flowname varchar2)
as
i number;
flow_type pflow_tbl.work_type%type;
begin
select work_type into flow_type from work_name where work_name.work_name=flowname;
delete from pflow_tbl where work_type=flow_type;
i:=1;
while i <flowstr.count loop
insert into pflow_tbl values(flow_type,flowname,flowstr(i),flowstr(i+1));
i:=i+1;
end loop;
exception
when others then
rollback;
end flowset_pro;
然后我在delphi6里面调用这个flowset_pro,但是老是包错,参数类型不匹配,我问在delphi中怎么传递这个数组的参数阿??
fcdm.ADOStoredProc_flow.Parameters.ParamByName('flowstr').Value:=???怎么写??
create or replace type flowlist as varray(12) of varchar2(20);
////////////
create or replace procedure flowset_pro(
flowstr flowlist,
flowname varchar2)
as
i number;
flow_type pflow_tbl.work_type%type;
begin
select work_type into flow_type from work_name where work_name.work_name=flowname;
delete from pflow_tbl where work_type=flow_type;
i:=1;
while i <flowstr.count loop
insert into pflow_tbl values(flow_type,flowname,flowstr(i),flowstr(i+1));
i:=i+1;
end loop;
exception
when others then
rollback;
end flowset_pro;
然后我在delphi6里面调用这个flowset_pro,但是老是包错,参数类型不匹配,我问在delphi中怎么传递这个数组的参数阿??
fcdm.ADOStoredProc_flow.Parameters.ParamByName('flowstr').Value:=???怎么写??
i:integer;sql_str:string;
workname:string;
begin
//frm_flow.Close;sh_dx:=VarArrayCreate([0, length(flowid) -1], varVariant);
for i:=0 to length(flowid) -1 do
begin
sh_dx[i] :=flowid[i];
end;
workname:=ListBox_flow.Items[ListBox_flow.ItemIndex];
sql_str :='flowset_pro(:sh_dx,:workname);';
with OracleQuery1 do
begin
//设置SQL属性
Clear;
SQL.Add('begin');
SQL.Add(' ' + sql_str );
SQL.Add('end;');
//定义参数
DeleteVariables;
DeclareVariable('sh_dx', otString);
DeclareVariable('workname',otString);
DimPLSQLTable('sh_dx', 2000, 15);
//参数赋值
SetVariable(':sh_dx', sh_dx);
setVariable(':workname',workname);
//执行存储过程
Execute;
Free;
end;
OracleSession1.Connected:= False;
OracleSession1.Free;
end;
setVariable('workname',workname);
按照你的还是没有反应?编译也没有问题??
declare
flowstr flowlist :=flowlist('A','B','C','D','E');
flowname varchar2(10):='初始登记';
begin
flowset_pro(flowstr,flowname);
end;
是可以的,结果是正确的!所以我在怀疑delphi中调用的时候参数传的不正确
说是变量类型不正确,与其他类型有冲突等,好像不能设置为ftarray这个数组类型,是不是我的存储过程
的参数flowstr的类型有问题?因为我这个flowstr数组类型是我自己定义的一个类型:
create or replace type flowlist as varray(12) of varchar2(20);
我不知道这个有没有关系的?再有如果用ADOStoredProc能不能用动态数据为参数的存储过程的?
fcdm.ADOQuery2.Close;
fcdm.ADOQuery2.SQL.Clear;
fcdm.ADOQuery2.SQL.Add('insert into pflow_tbl values(:a,:a,:flow1,:flow2)');
fcdm.ADOQuery2.Parameters.ParamByName('a').Value:='a';
fcdm.ADOQuery2.Parameters.ParamByName('flow1').Value:=flowid[1];
fcdm.ADOQuery2.Parameters.ParamByName('flow2').Value:=flowid[2];
fcdm.ADOQuery2.ExecSQL;
难道delphi中数组这么难处理阿?/