在delphi中怎样调用oracle中的procedure?(10分)

  • 主题发起人 主题发起人 zzychb
  • 开始时间 开始时间
Z

zzychb

Unregistered / Unconfirmed
GUEST, unregistred user!
我在用控件Tstoredproc调用Oracle中的procedure时出现错误提示为
“operation not applicable”.程式设计思路如下:
Oracle的Procedure有一个输入参数,我把edit1.text值赋给此参数,
执行代码为:
storedproc1.Prepare;
storedproc1.Params[0].AsString:=trim(edit1.Text);
storedproc1.ExecProc;
[8D]我用debug查没有错误,请各位高手告知应怎样设置控件Tstoredproc
 
storedproc1.parambyname('DATEBEGIN').value := FormatDateTime('yyyy-mm-dd',
dtPickStart.Date);
这是我的用法,没问题的。

在DFM中设计好storedproc1后,没有必要使用prepare。
 
to armyjing:
照你的try后,还是出现相同的提示。
在storedproc1.parambyname('DATEBEGIN').value :
= FormatDateTime('yyyy-mm-dd',dtPickStart.Date)中
参数DATEBEGIN是ORACLE中PROCEDURE中的输入参数吗?
你的是怎样设置的?
 
object procCljl2Receipt: TStoredProc
DatabaseName = 'dbReturnReceipt'
StoredProcName = 'CLJL2RECEIPT'
Left = 8
Top = 88
ParamData = <
item
DataType = ftString
Name = 'DATEBEGIN'
ParamType = ptInput
end
item
DataType = ftString
Name = 'DATEEND'
ParamType = ptInput
end
item
DataType = ftString
Name = 'DELREC'
ParamType = ptInput
end
item
DataType = ftFloat
Name = 'RETURNVAL'
ParamType = ptOutput
end>
end
上面是我在dfm中的定义,然后在pas中如前所述对输入参数进行赋值,执行后取存储过程
的输出参数returnval以掌握存储过程的运行状况。
 
照你的修改后还是一样的提示,
我把dfm,pas及oracle 中过程写出如下, 请告知正确的设置代码,谢谢。

object Button1: TButton
Left = 296
Top = 56
Width = 75
Height = 25
Caption = 'Button1'
TabOrder = 0
OnClick = Button1Click
end
object Edit1: TEdit
Left = 168
Top = 112
Width = 121
Height = 21
TabOrder = 1
Text = 'Edit1'
end
object DataSource1: TDataSource
Left = 88
Top = 48
end
object StoredProc1: TStoredProc
DatabaseName = 'TEST'
StoredProcName = 'ITEM_EXPLOSION'
Left = 168
Top = 56
ParamData = <
item
DataType = ftString
Name = 'V_ITEM'
ParamType = ptInput
end>
end
上面是我在dfm中三个控件的定义,下面是pas中对输入参数进行赋值和执行存储过程代码
procedure TForm1.Button1Click(Sender: TObject);
begin
//StoredProc1.Prepare;
StoredProc1.ParamByName('V_ITEM').Value:=edit1.Text;
StoredProc1.ExecProc;
end;
再下面是oracle存储过程'ITEM_EXPLOSION代码只有一个参数:
CREATE OR REPLACE PROCEDURE ITEM_EXPLOSION(V_ITEM IN CHAR)
AS
v_current CHAR(20) ;
v_level NUMBER(3) ;
v_sub_part CHAR(20) ;
v_rows1 NUMBER(5) ;
v_order NUMBER(4):=1;
v_layer NUMBER(4);
vv_layer NUMBER(4):=1;
v_part_flag CHAR(1);
v_this_item temp_item.part_no%TYPE;
v_host_part temp_item.sub_part%TYPE;
j temp_item.sort_id%TYPE;

CURSOR cur_items IS SELECT current_item,part_level,sub_part,part_flag,layer
FROM stack WHERE part_level=v_level ORDER BY current_item ASC;

CURSOR cur_sub IS SELECT part_no,sub_part,sort_id,layer
FROM temp_item WHERE (ltrim(rtrim(sub_part,' '),' ') IS NOT NULL) ORDER BY part_no ASC;

BEGIN --BEGIN OF PROCEDURE BODY

DELETE FROM stack;
DELETE FROM temp_item;

v_current:=v_item; --put the bom or assy id for query
v_level:=1;

INSERT INTO temp_item(structure_level,part_no,sub_part,sort_id,part_flag,part_nic,layer)
VALUES(v_level,v_current,NULL,v_order,NULL,0,0);

INSERT INTO stack(current_item,part_level,sub_part,part_flag,layer)
VALUES(v_current,1,NULL,NULL,0);

WHILE v_level > 0 LOOP

SELECT count(*) INTO v_rows1 FROM stack WHERE part_level=v_level;

IF v_rows1>0 THEN
OPEN cur_items;
FETCH cur_items INTO v_current,v_level,v_sub_part,v_part_flag,v_layer;
CLOSE cur_items;
IF V_LEVEL<>1 THEN
v_order:=v_order+1;
INSERT INTO temp_item(structure_level,part_no,sub_part,sort_id,part_flag,layer)
VALUES(v_level,v_current,v_sub_part,v_order,v_part_flag,v_layer);
END IF;

IF v_sub_part IS NULL THEN
DELETE FROM stack WHERE (part_level=v_level) AND (current_item=v_current) AND (sub_part IS NULL);
ELSE
DELETE FROM stack WHERE (part_level=v_level) AND (current_item=v_current) AND (sub_part=v_sub_part);
END IF;

END LOOP; --END WHILE

OPEN cur_sub;
FETCH cur_sub INTO v_this_item,v_host_part,v_order,vv_layer;
WHILE cur_sub%FOUND LOOP
IF v_this_item<>v_host_part THEN
SELECT count(*) INTO v_rows1 FROM temp_item WHERE (layer=vv_layer) AND (part_no=v_host_part) AND (ltrim(rtrim(sub_part,' '),' ') IS NULL);
IF v_rows1>0 THEN
SELECT sort_id INTO j FROM temp_item WHERE (part_flag='N') AND (layer=vv_layer) AND (part_no=v_host_part) AND (ltrim(rtrim(sub_part,' '),' ') IS NULL);
UPDATE temp_item SET sort_id=sort_id+1 WHERE sort_id>j;
UPDATE temp_item SET sort_id=j+1 WHERE (layer=vv_layer) AND (part_no=v_this_item) AND (sub_part=v_host_part);
END IF;
END IF;
FETCH cur_sub INTO v_this_item,v_host_part,v_order,vv_layer;
END LOOP;
CLOSE cur_sub;

COMMIT;

END ITEM_EXPLOSION; --END OF PROCEDURE
 
对于存储过程实在是没办法去分析它的正确性,一个字:懒。
你的procedure在oracle中有无经过测试?
我的一般做法:
创建procedure,在oracle集成环境下测试procedure,在delphi中使用procedure。
我不清楚你的问题究竟是出在哪里!我在应用中从来没有这种现象。

如果可能,你将数据库中有关脚本发给我,我建立一个可以使用的project,你再根据我
这边的project去分析你的应用。
 
我的oracle8.0.5版
在sql pluse中,exec procedure_name(参数) 也通不过,
但procedure肯定没有问题, 因为在pb中运行通过。
 
后退
顶部