在sql Server里,可以通过存储过程返回记录集,如:
CREATE OR REPLACE PROCEDURE Pro_Test
Is
Begin
select * from tab_test;
End;
所以你这个方法在sql Serverr中可以。
但是在Oraclel里要怎么写呢?
CREATE OR REPLACE PROCEDURE Pro_Test
Is
Begin
select * from tab_test;
End;
所以你这个方法在sql Serverr中可以。
但是在Oraclel里要怎么写呢?
解决方案 »
- Oralce 存储过程Out数组、跟VARRAY(null,null)问题!
- oracle 10g RAC安装中遇到的问题,
- 在线等!!!11急! ORA-01400: cannot insert NULL into ("LIANG"."ORDER_DETAIL"."ORDER_ID")
- where跟中文字符条件为什么无效
- “ORA-01026L赋值列表中存在多个大小>4000的缓冲区"的错误
- 那本是oracle的经典教程?
- 求和,SQl语句,算法
- 急救oracle Imp 与Exp 问题???
- 这个decode怎么写的
- oracle:system收回了用户对表的查询权限,用户为什么还能查询
- pl-sql中定义表类型的问题
- 这样的查询语句如何写
办法1
输出一个查询字符串
办法2
输出一个查询游标给你个例子
create or replace procedure UP_IRRGuoGuanShuiQing_GetList
( --输出参数
out_Info out packname.rtnproc,
out_ErrorNbr out number
)
is
V_SelectQuery varchar2(1000);--sql 语句
begin
out_ErrorNbr:=0;
V_SelectQuery:='select x.ggsqid,x.clrq,y.jgname,z.ggqdname,n.dlcname,m.xlcmc,x.clsjy,x.lly,x.clsje,x.lle,x.pjll,x.sysl from irrguoguanshuiqing x ';
V_SelectQuery:= V_SelectQuery || 'left outer join irrguanlijigou y on x.gljid = y.jgid left outer join irrguoguanqudao z on x.ggqdid = z.ggqdid ';
V_SelectQuery:= V_SelectQuery || 'left outer join irrxiaolunci m on x.xlcid = m.xlcid left outer join irr_dalunci n on m.dlcid = n.dlcid ';
V_SelectQuery:= V_SelectQuery || ' order by x.clrq desc';
open out_Info for V_SelectQuery ;
exception when others then
out_ErrorNbr:=-1;
end UP_IRRGuoGuanShuiQing_GetList;
as
type page_result is ref cursor;
procedure pkg_query
(
p_tableid in varchar2,
p_page in out page_result
);
end;
/create or replace package body pkg_query
as
procedure pkg_query
(
p_tableid in varchar2,
p_page in out page_result
)
is
v_sql varchar2(4000);
begin
v_sql := 'select * from table';
open p_page for v_sql;
end;
end;
/