USE [Pica2.0]
GO
/****** Object: StoredProcedure [dbo].[GetSitePV] Script Date: 05/07/2013 14:43:19 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[GetSitePV]
-- Add the parameters for the stored procedure here
@NodeId int
AS
begin
Create Table #Temp1(NewsId int, NodeId int, Title nvarchar(200), SubTitle varchar(200), Author nvarchar(50), Editor nvarchar(50), Sources nvarchar(50), Photographer nvarchar(50),
BriefContent nvarchar(1000), Content text, State nvarchar(10), Keyword nvarchar(200),
Deliveries nvarchar(200), CreateTime date, PublishTime date, NewsTop bit, NewsOrder int, NewsSecurity int)
insert into #temp1
exec [LatestNews1]
@nodeId=@NodeId,
@outCount = 999999
select * from PageView where PageView.NewsId in (select #Temp1.newsid from #Temp1) endUSE [Pica2.0]
GO
/****** Object: StoredProcedure [dbo].[LatestNews1] Script Date: 05/07/2013 17:11:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,标题相同数据>
-- =============================================
ALTER PROCEDURE [dbo].[LatestNews1]
@nodeId int,
@outCount int
AS
BEGIN
SET NOCOUNT ON;
create table #temp(nodeid int, url nvarchar(2000))
insert into #temp select @nodeId, dbo.getnodeurl(@nodeId)
--统计以缓存表为父级的子集node条数
declare @two int
select @two=COUNT(nodeid) from Nodes where NodeID not in (select nodeid from #temp) and FatherId in (select NodeID from #temp)
while(@two <> 0)
begin
insert into #temp select nodeid,dbo.getnodeurl(NodeID) from nodes where NodeID not in (select nodeid from #temp) and FatherId in (select NodeID from #temp)
select @two=COUNT(nodeid) from Nodes where NodeID not in (select nodeid from #temp) and FatherId in (select NodeID from #temp)
end
declare @sql nvarchar(2000)
--select * from #temp
set @sql = 'select top ' + Convert(nvarchar(20), @outCount)+ 'News.* from News,#temp where News.NodeId = #temp.nodeId and news.State = ''发布'' order by News.PublishTime desc'
print @sql
exec(@sql)
drop table #temp
END
求大神帮忙把这2个Sql存储改成Oracle存储。万分感谢
解决方案 »
- Oracle中的一个字段检索,判断它是否是NULL、半角空格或者全角空格
- 两个数据库的相同表数据查询,求大神解决
- 创建分区表,老报错
- 使用spool导出到txt 多出一个口
- 求教如何快速删除大量数据,大家都来看看,也许你也遇到过同样的问题。
- oracle报错了 怎么回事?
- 一个存储过程里有多个begin和end,这是怎么回事啊?
- 在Solaris下用什么命令来开启Oracle的各项服务???
- SQLPlus Worksheet不能进入
- Oracle 疑难杂症,高手请帮忙。
- flush buffer cache后如何恢复
- oracle 建表时,出现oracle建表时,出现SP2-0042:未知命令“>”-其余行忽略,求解?
你把你现在的改成Oracle存储过程的写法不 就可以了?
如果你要别人写那你以后还是不会。。你自己试着写吧。。
这个非常容易。。
insert into #temp1
exec [LatestNews1]
@nodeId=@NodeId,
@outCount = 999999这句可能会比较复杂,个人有两种建议:
1、把GetSitePV和LatestNews1整合在一起形成一个存储过程,这样就不必要调用存储过程返回结果集直接INSERT到表中,而是直接在存储过程中直接INSERT到表中。这样做的前提是看LatestNews1过程是否需要通用。2、ORACLE不能使用这样的语法来实现直接返回存储过程的结果集插入表中,但可以插入数组中,然后遍历数组,插入表中,这样就不必改动你的存储过程结构。仅供参考,还有更好的建议也可提出来。
(
newsid INTEGER,
nodeid INTEGER,
title VARCHAR2(200),
subtitle VARCHAR2(200),
author VARCHAR2(50),
editor VARCHAR2(50),
sources VARCHAR2(50),
photographer VARCHAR2(50),
briefcontent VARCHAR2(1000),
content CLOB,
state VARCHAR2(10),
keyword VARCHAR2(200),
deliveries VARCHAR2(200),
createtime DATE,
publishtime DATE,
newstop INTEGER,
newsorder INTEGER,
newssecurity INTEGER
)create table TEMP
(
nodeid INTEGER,
url VARCHAR2(2000)
)
create or replace procedure GetSitePV(i_nodeid varchar2,
o_cur out sys_refcursor) is v_count number;
begin
execute immediate 'truncate table temp';
execute immediate 'truncate table temp1'; insert into temp
select i_nodeid, dbo.getnodeurl(i_nodeid); select COUNT(nodeid)
into v_count
from Nodes
where NodeID not in (select nodeid from temp)
and FatherId in (select NodeID from temp); if (v_count <> 0) then
insert into temp
select nodeid, dbo.getnodeurl(NodeID)
from nodes
where NodeID not in (select nodeid from temp)
and FatherId in (select NodeID from temp);
select COUNT(nodeid)
into v_count
from Nodes
where NodeID not in (select nodeid from temp)
and FatherId in (select NodeID from temp);
end if; insert into temp1
select *
from (select News.*, rownum rn
from News, temp
where News.NodeId = temp.nodeId
and news.State = ' 发布 '
order by News.PublishTime desc) t
where t.rn <= 999999; commit;
open o_cur for
select *
from PageView
where PageView.NewsId in (select Temp1.newsid from Temp1);end;
select *
from (select News.*, rownum rn
from News, temp
where News.NodeId = temp.nodeId
and news.State = ' 发布 '
order by News.PublishTime desc) t
where t.rn <= 999999;上面这里会出错的,因为多select 出来了一个字段rn,我省事没搞这些细节了
厄,不是很明白- -能详细些吗
create global temporary table TEMP1
(
newsid INTEGER,
nodeid INTEGER,
title VARCHAR2(200),
subtitle VARCHAR2(200),
author VARCHAR2(50),
editor VARCHAR2(50),
sources VARCHAR2(50),
photographer VARCHAR2(50),
briefcontent VARCHAR2(1000),
content CLOB,
state VARCHAR2(10),
keyword VARCHAR2(200),
deliveries VARCHAR2(200),
createtime DATE,
publishtime DATE,
newstop INTEGER,
newsorder INTEGER,
newssecurity INTEGER
) on commit preserve rows;
oracle不是不能使用临时表,是不能像sqlserver那样直接在一个过程里面定义临时表。。
其实oracle的临时表 跟 普通表没啥区别。。就跟12楼一样,还是得在外面建。我在过程里面的开始每次都清空了数据,其实跟oracle的临时表的效果一样了
execute immediate 'truncate table temp1';所以 数据不会膨胀。。
execute immediate 'truncate table temp1';所以 数据不会膨胀。。
我又头大了- -,这里面有个函数,USE [Pica2.0]
GO
/****** Object: UserDefinedFunction [dbo].[GetNodeUrl] Script Date: 05/08/2013 10:58:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[GetNodeUrl]
(
@nodeId int
)
RETURNS nvarchar(2000)
AS
BEGIN
declare @url nvarchar(2000)
set @url = ''
declare @count int
--select @url = NodeUrl from Nodes where NodeID = @nodeId
select @count = count(nodeid) from Nodes where NodeID= @nodeId
while(@count <> 0)
begin
select @url = NodeUrl + '/' + @url from Nodes where NodeID = @nodeId
select @nodeid = fatherid from nodes where nodeid = @nodeid
select @count = count(nodeid) from Nodes where NodeID = @nodeid
end
return @url
END这个怎么写到Oracle里面啊?
select i_nodeid,getnodeurl(i_nodeid) from table;(table替换成你的实际的表,好像是NODES吧)