Oracle中像sql server 中这样的功能实现真的很麻烦
create procedure
begin
select * from table
end我在网上搜了一些资料,试着做了.但是还是实现不了
我是这样做的
create or replace package Pkg_TestDS is
type curType is ref cursor;
procedure SelectDS(sBegin in varchar2,sEnd in varchar2, ORF out curType);
end Pkg_TestDS;create or replace package body Pkg_TestDS is
procedure SelectDS(sBegin in varchar2,sEnd in varchar2, ORF out curType)
is
begin
open ORF for
select tt.* from
(select
orderhist.orderid 订单编号,
orderhist.contactid 客户编号,
orderhist.crusr 创建人,
orderhist.totalprice 订单金额
from orderhist where crdt >=to_date(sBegin,'yyyy-mm-dd hh24:mi:ss')
and crdt<=to_date(sEnd,'yyyy-mm-dd hh24:mi:ss') and ordertype='1'
and rownum <= 100
) tt
where 1=1;
end;end Pkg_TestDS;在pl/sql中如下调用
SQL> var a cursor exec Pkg_TestDS.SelectDS('2007-01-01 00:00:00','2007-01-21 00:00:00',:a);
CURSOREXEC unknown datatypeSQL> var a ref cursor exec Pkg_TestDS.SelectDS('2007-01-01 00:00:00','2007-01-21 00:00:00',:a);
REFCURSOR not supportedSQL> var a Pkg_TestDS.curType exec Pkg_TestDS.SelectDS('2007-01-01 00:00:00','2007-01-21 00:00:00',:a);
PKG_TESTDS.CURTYPEEXEC unknown datatype
这样都不行,请高手帮忙!!
create procedure
begin
select * from table
end我在网上搜了一些资料,试着做了.但是还是实现不了
我是这样做的
create or replace package Pkg_TestDS is
type curType is ref cursor;
procedure SelectDS(sBegin in varchar2,sEnd in varchar2, ORF out curType);
end Pkg_TestDS;create or replace package body Pkg_TestDS is
procedure SelectDS(sBegin in varchar2,sEnd in varchar2, ORF out curType)
is
begin
open ORF for
select tt.* from
(select
orderhist.orderid 订单编号,
orderhist.contactid 客户编号,
orderhist.crusr 创建人,
orderhist.totalprice 订单金额
from orderhist where crdt >=to_date(sBegin,'yyyy-mm-dd hh24:mi:ss')
and crdt<=to_date(sEnd,'yyyy-mm-dd hh24:mi:ss') and ordertype='1'
and rownum <= 100
) tt
where 1=1;
end;end Pkg_TestDS;在pl/sql中如下调用
SQL> var a cursor exec Pkg_TestDS.SelectDS('2007-01-01 00:00:00','2007-01-21 00:00:00',:a);
CURSOREXEC unknown datatypeSQL> var a ref cursor exec Pkg_TestDS.SelectDS('2007-01-01 00:00:00','2007-01-21 00:00:00',:a);
REFCURSOR not supportedSQL> var a Pkg_TestDS.curType exec Pkg_TestDS.SelectDS('2007-01-01 00:00:00','2007-01-21 00:00:00',:a);
PKG_TESTDS.CURTYPEEXEC unknown datatype
这样都不行,请高手帮忙!!
2 type curType is ref cursor;
3 procedure SelectDS(sBegin in varchar2,sEnd in varchar2, ORF out curType);
4 end Pkg_TestDS;
5 /程序包已创建。SQL> create or replace package body Pkg_TestDS is
2 procedure SelectDS(sBegin in varchar2,sEnd in varchar2, ORF out curType)
3 is
4 begin
5 open ORF for
6 select table_name from user_tables;
7 end;
8 end;
9 /程序包主体已创建。SQL> var a refcursor
SQL> exec Pkg_TestDS.SelectDS('1','2',:a)PL/SQL 过程已成功完成。SQL> print aTABLE_NAME
------------------------------
ADMIN
ADMININFO
CAKEYINFO
CAORG
CONFIG
CURRENTKEY
CURRENTKEYNOTFORCERT
KEYESCROWOPER
KMCROLE
OPERATIONLOG
PLAN_TABLETABLE_NAME
------------------------------
SYSINFO
TASKSCHEDULE
T_TEST1
T_TEST2已选择15行。SQL>
var a refcursor
报这个错
REFCURSOR not supported
SQL> exec Pkg_TestDS.SelectDS('2007-01-01 00:00:00','2007-01-21 00:00:00',:a);PL/SQL 过程已成功完成。SQL> print a;订单编号 客户编号 创建人 订单金额
---------------- ---------------- ---------- ----------
2068524 13761632 8064 598
2068525 13761631 8044 598
2068526 13761630 8046 598
2068527 13761635 8076 598
2068528 13761634 8051 598
2068529 13761637 8044 598
2068530 13761626 8084 1196
2068531 13761626 8046 1196
2068532 13761638 8064 598
2068533 13761639 8039 598
2068534 13761640 8076 598订单编号 客户编号 创建人 订单金额
---------------- ---------------- ---------- ----------
2068535 13761641 8086 598
2068536 13761642 8086 598
2068537 13761644 8039 598
2068538 13761643 8044 598
2068539 13761645 8076 598
2068540 13761606 8039 598
2068541 13761646 8086 598
2068542 13761579 8073 0
2068543 13761647 8086 598
2068544 13761648 8076 598
2068545 13733164 8049 0
....
....
返回的邮标 变量 a,我用select * from a;是执行不了的,那怎么把a中的数据集返回给客户端程序呢?用print是不可以的吧,