这个是我在MSSQL当中的一个存储过程 调用它是返回的一个结果集 我想转到ORACLE当中来 就是不知道ORACLE当中返回结果集的存储过程怎么创建 新人出来乍道 大家帮帮忙啊
USE [XAJ_PMS]
GO
/****** Object: StoredProcedure [dbo].[P_createDataGridColumns] Script Date: 12/09/2010 13:40:26 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GOALTER PROCEDURE [dbo].[P_createDataGridColumns] @pageId varchar(50),
@userid int AS
declare @count int --根据userID 和 PageID 到 SetPage_ColumnSelect 表找记录
select @count=count(1) from SetPage_ColumnSelect where Userid=@userid and PageId=@pageId
--有记录取值
if @count <> 0
begin
select a.* from dataGrid_msg a,SetPage_ColumnSelect b where a.pageID=@pageId and b.pageID=@pageId and a.DataField=b.DataField
and b.userid=@userid
union
select * from datagrid_msg where pageid=@pageId and system=1
order by showpos
end
--没值 取默认值
else
select * from datagrid_msg where pageid=@pageId and ishide=1
order by showpos
USE [XAJ_PMS]
GO
/****** Object: StoredProcedure [dbo].[P_createDataGridColumns] Script Date: 12/09/2010 13:40:26 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GOALTER PROCEDURE [dbo].[P_createDataGridColumns] @pageId varchar(50),
@userid int AS
declare @count int --根据userID 和 PageID 到 SetPage_ColumnSelect 表找记录
select @count=count(1) from SetPage_ColumnSelect where Userid=@userid and PageId=@pageId
--有记录取值
if @count <> 0
begin
select a.* from dataGrid_msg a,SetPage_ColumnSelect b where a.pageID=@pageId and b.pageID=@pageId and a.DataField=b.DataField
and b.userid=@userid
union
select * from datagrid_msg where pageid=@pageId and system=1
order by showpos
end
--没值 取默认值
else
select * from datagrid_msg where pageid=@pageId and ishide=1
order by showpos
---'select * from 不要以这种形式 写出列名 ,下面的你自己改
create or replace procedure P_createDataGridColumns(v_pageId varchar2,v_userid number,cur out sys_refcursor)
as
cnt number;
str varchar2(4000);
begin
select count(*) into cnt from SetPage_ColumnSelect where Userid=v_userid and PageId=v_pageId;
if cnt>0 then
open cur for select *
str:='select a.* from dataGrid_msg a,SetPage_ColumnSelect b where (a.pageID=v_pageId and b.pageID=v_pageId and a.DataField=b.DataField and b.userid=v_userid) or system=1';
else
str:='select * from datagrid_msg where pageid=v_pageId and ishide=1';
end if;
open cur for str;
exception
when others then
dbms_output.put_line(sqlcode||' '||sqlerrm);
end;
http://blog.csdn.net/wkc168/archive/2010/07/04/5712213.aspx
create or replace procedure P_createDataGridColumns(v_pageId varchar2,v_userid number,cur out sys_refcursor)
as
cnt number;
str varchar2(4000);
begin
select count(*) into cnt from SetPage_ColumnSelect where Userid=v_userid and PageId=v_pageId;
if cnt>0 then
--wkc这里忘删除了(select * from
str:='select a.* from dataGrid_msg a,SetPage_ColumnSelect b where (a.pageID=v_pageId and b.pageID=v_pageId and a.DataField=b.DataField and b.userid=v_userid) or system=1';
else
str:='select * from datagrid_msg where pageid=v_pageId and ishide=1';
end if;
open cur for str;
exception
when others then
dbms_output.put_line(sqlcode||' '||sqlerrm);
end;
as
cnt number;
str varchar2(4000);
begin
select count(*) into cnt from SetPage_ColumnSelect where Userid=v_userid and PageId=v_pageId;
if cnt>0 then
-----open cur for select * 多余 写的时候感觉你union 可以合并 忘记了
str:='select a.* from dataGrid_msg a,SetPage_ColumnSelect b where (a.pageID=v_pageId and b.pageID=v_pageId and a.DataField=b.DataField and b.userid=v_userid) or system=1';
else
str:='select * from datagrid_msg where pageid=v_pageId and ishide=1';
end if;
open cur for str;
exception
when others then
dbms_output.put_line(sqlcode||' '||sqlerrm);
end;
aspen 该叫你犀利大侠