1.创建测试用表:
create table aaclass(CID VARCHAR2(50), CNAME VARCHAR2(50), pnumber NUMBER(10,0) );
/
INSERT INTO aaclass values('c1', 'cn1', 10 ) ;
/
INSERT INTO aaclass values('c2', 'cn2', 40 ) ;
/
INSERT INTO aaclass values('c1', 'cn3', 30 ) ;
2.创建包:
CREATE OR REPLACE PACKAGE SCOTT.PKG_JCCTEST1
AS
type rc_class is ref cursor;
--求p1,p2的和与差,返回的多个值通过游标返回
procedure GetSubAndSum2(p1 number,p2 number,ResultCursor out rc_class);
--查询满足条件的数据集,返回数据集通过游标返回
procedure GetClass2(a in number,ResultCursor out rc_class ) ;
--往表中插一条记录,不返回结果集时,本人用AdoQuery调用(adodataset好象要求必须返回结果集)
procedure InsertClass( p_cid varchar2 ,p_cname varchar2 ,p_pnumber number) ;
end PKG_JCCTEST1;
3.创建包体:
CREATE OR REPLACE PACKAGE BODY SCOTT.PKG_JCCTEST1
AS
procedure GetSubAndSum2(p1 number,p2 number,
ResultCursor out rc_class)
IS
BEGIN
open ResultCursor for
select p1-p2 as "sum", p1+p2 as "sub" from dual;
END ;
procedure GetClass2(a in number,ResultCursor out rc_class )
is
begin
open ResultCursor for
select * from aaclass where pnumber >a;
end ;
procedure InsertClass( p_cid varchar2 ,p_cname varchar2 ,
p_pnumber number)
is
begin
insert into aaclass values(p_cid,p_cname,p_pnumber) ;
-- commit;
end;
end;
/
4.在delphi中对oracle存储过程的调用,首先在窗体上放置一个ADOConnection1,请注意连接字符串为:Provider=MSDAORA.1;Password=tiger;Persist Security Info=True;User ID=scott;Data Source=oracledb,主要是Povider不能错,如果是ORAOLEDB.oracle.1则出错。然后在窗体上再放置一个ADOStoredProc1和一个ADOQuery1,设置它们的conn为ADOConnection1。
(1) 添加按钮button1,调用过程GetSubAndSum2,事件代码为:
procedure TForm1.button1Click(Sender: TObject);
var
AResult, BResult: integer;
begin
with ADOStoredProc1 do
begin
ProcedureName := 'PKG_JCCTEST1.GetSubAndSum2';
Parameters.Clear;
Parameters.CreateParameter('p1', ftinteger, pdinput, 10, 45);
Parameters.CreateParameter('p2', ftinteger, pdinput, 10, 4);
Open;
end;
AResult := ADOStoredProc1.Fields.FieldByName('sub').Value;
BResult := ADOStoredProc1.Fields.FieldByName('sum').Value;
//显示结果
showmessage(inttostr(AResult));
showmessage(inttostr(BResult));
end;
(2) 添加按钮button2,调用过程GetClass2,事件代码为:
procedure TForm1.button2Click(Sender: TObject);
begin
with ADOStoredProc1 do
begin
ProcedureName := 'PKG_JCCTEST1.GetClass2';
Parameters.Clear;
Parameters.CreateParameter('p1', ftinteger, pdinput, 10, 20);
Open;
end;
while not ADOStoredProc1.Eof do
begin
showmessage('CID : ' + string(ADOStoredProc1.FieldByName('CID').Value) +
'--CNAME :' + string(ADOStoredProc1.FieldByName('CNAME').Value) +
'--PNUMBER :' + string(ADOStoredProc1.FieldByName('PNUMBER').Value)
);
ADOStoredProc1.Next;
end;
end;
(3) 添加按钮button3,调用过程InsertClass,事件代码为:
procedure TForm1.button3Click(Sender: TObject);
begin
with ADOQuery1 do
begin
Close;
Parameters.Clear;
SQL.Clear;
SQL.Add('{call PKG_JCCTEST1.InsertClass(?,?,?)}');
Parameters.CreateParameter('P1', ftstring, pdinput, 50, 'c11');
Parameters.CreateParameter('P2', ftstring, pdinput, 50, 'cn11');
Parameters.CreateParameter('P3', ftinteger, pdinput, 50, 26);
ExecSQL;
end;
ShowMessage('执行成功!');
end;