select sptm,spm,lsj,sum(xssl),sum(xsje),sum(hsjjje) into a1,a2,a3,a4,a5,a6 from TABLE where rq between to_date(rq1,'YYYY-MM-DD') and to_date(rq2,'YYYY-MM-DD') group by sptm,spm,lsj; 多条纪录的使用游标进行类似处理即可
SQL> select * from aa; ID NAME ---------- -------- 1 猪八戒 2 孙悟空 CREATE OR REPLACE PACKAGE pkg_test AS TYPE myrctype IS REF CURSOR; END pkg_test; / create or replace PROCEDURE get(p_rc OUT pkg_test.myrctype) IS sqlstr VARCHAR2 (50); BEGIN sqlstr:='select * from aa'; OPEN p_rc FOR sqlstr; END get; / 以下是delphi调用过程返回查询: with ADOStoredProc1 do begin Close; procedurename:='get'; Parameters.CreateParameter('p_id',ftstring,Pdinput,20,'1'); parameters[0].Name:='p_id'; Parameters[0].Value:='3'; execproc; Open; end;
select sptm,spm,lsj,sum(xssl),sum(xsje),sum(hsjjje) from TABLE where rq between to_date(rq1,'YYYY-MM-DD') and to_date(rq2,'YYYY-MM-DD') group by sptm,spm,lsj;----返回的是数据集,这里要用游标CREATE OR REPLACE PROCEDURE cc_rxshz(rq1 IN CHAR,rq2 IN CHAR) as CURSOR C_CONS IS select sptm,spm,lsj,sum(xssl),sum(xsje),sum(hsjjje) from TABLE where rq between to_date(rq1,'YYYY-MM-DD') and to_date(rq2,'YYYY-MM-DD') group by sptm,spm,lsj; begin for ... in C_CONS ... END cc_rxshz;
多条纪录的使用游标进行类似处理即可
---------- --------
1 猪八戒
2 孙悟空
CREATE OR REPLACE PACKAGE pkg_test
AS
TYPE myrctype IS REF CURSOR;
END pkg_test;
/ create or replace PROCEDURE get(p_rc OUT pkg_test.myrctype)
IS
sqlstr VARCHAR2 (50);
BEGIN
sqlstr:='select * from aa';
OPEN p_rc FOR sqlstr;
END get;
/ 以下是delphi调用过程返回查询:
with ADOStoredProc1 do
begin
Close;
procedurename:='get';
Parameters.CreateParameter('p_id',ftstring,Pdinput,20,'1');
parameters[0].Name:='p_id';
Parameters[0].Value:='3';
execproc;
Open;
end;
where rq between to_date(rq1,'YYYY-MM-DD') and to_date(rq2,'YYYY-MM-DD')
group by sptm,spm,lsj;----返回的是数据集,这里要用游标CREATE OR REPLACE PROCEDURE
cc_rxshz(rq1 IN CHAR,rq2 IN CHAR)
as
CURSOR C_CONS IS select sptm,spm,lsj,sum(xssl),sum(xsje),sum(hsjjje) from TABLE
where rq between to_date(rq1,'YYYY-MM-DD') and to_date(rq2,'YYYY-MM-DD')
group by sptm,spm,lsj;
begin
for ... in C_CONS ...
END cc_rxshz;