一个取用户数据的sqlserver存储过程,要转成oracle的,有下面几点我不知道该怎么做:
1、SELECT
*,
IsModerator = (SELECT Count(*) FROM forums_Moderators WHERE UserID = U.UserID)
FROM
forums_Users U (nolock),
forums_UserProfile P,
#PageIndexForUsers这种语句在oracle里怎么表示?oracle可以“select *,变量,常量 from 表”吗?
2、序列要每次从1开始计数,可以create or replace 吗?我试了,好像不行,但不知道自己有没写错语法。
3、INSERT INTO #PageIndexForUsers (UserID)
SELECT U.UserID FROM forums_Users U……
如果用了序列,假设为seq_Mine,我该这样写吗:
INSERT INTO #PageIndexForUsers (IndexID,UserID)
SELECT seq_Mine.nextval, U.UserID FROM forums_Users U……create PROCEDURE forums_UsersInRole_Get
(
@PageIndex int,
@PageSize int,
@SortBy int = 0,
@SortOrder int = 0,
@RoleID int,
@UserAccountStatus smallint = 1,
@ReturnRecordCount bit = 0
)
AS
BEGIN
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @RowsToReturn int
DECLARE @TotalUsers int-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1-- First set the rowcount
SET @RowsToReturn = @PageSize * (@PageIndex + 1)
SET ROWCOUNT @RowsToReturn-- Create a temp table to store the select results
CREATE TABLE #PageIndexForUsers
(
IndexID int IDENTITY (1, 1) NOT NULL,
UserID int
) -- Do we need to return a record count?
-- *************************************
IF (@ReturnRecordCount = 1)
SET @TotalUsers = (SELECT count(R.UserID) FROM forums_Users U, forums_UsersInRoles R, forums_UserProfile P WHERE R.UserID = U.UserID AND R.UserID = P.UserID AND U.UserAccountStatus = @UserAccountStatus AND EnableDisplayInMemberList = 1 AND RoleID = @RoleID)-- Special case depending on what the user wants and how they want it ordered by
-- *************************************-- Sort by Date Joined
IF @SortBy = 0 AND @SortOrder = 1
INSERT INTO #PageIndexForUsers (UserID)
SELECT U.UserID FROM forums_Users U, forums_UsersInRoles R, forums_UserProfile P WHERE R.UserID = U.UserID AND R.UserID = P.UserID AND U.UserAccountStatus = @UserAccountStatus AND EnableDisplayInMemberList = 1 AND RoleID = @RoleID ORDER BY DateCreated
ELSE IF @SortBy = 0 AND @SortOrder = 0
INSERT INTO #PageIndexForUsers (UserID)
SELECT U.UserID FROM forums_Users U, forums_UsersInRoles R, forums_UserProfile P WHERE R.UserID = U.UserID AND R.UserID = P.UserID AND U.UserAccountStatus = @UserAccountStatus AND EnableDisplayInMemberList = 1 AND RoleID = @RoleID ORDER BY DateCreated DESC-- Get the user details
SELECT
*,
IsModerator = (SELECT Count(*) FROM forums_Moderators WHERE UserID = U.UserID)
FROM
forums_Users U (nolock),
forums_UserProfile P,
#PageIndexForUsers
WHERE
U.UserID = #PageIndexForUsers.UserID AND
U.UserID = P.UserID AND
#PageIndexForUsers.IndexID > @PageLowerBound AND
#PageIndexForUsers.IndexID < @PageUpperBound
ORDER BY
#PageIndexForUsers.IndexID
END
1、SELECT
*,
IsModerator = (SELECT Count(*) FROM forums_Moderators WHERE UserID = U.UserID)
FROM
forums_Users U (nolock),
forums_UserProfile P,
#PageIndexForUsers这种语句在oracle里怎么表示?oracle可以“select *,变量,常量 from 表”吗?
2、序列要每次从1开始计数,可以create or replace 吗?我试了,好像不行,但不知道自己有没写错语法。
3、INSERT INTO #PageIndexForUsers (UserID)
SELECT U.UserID FROM forums_Users U……
如果用了序列,假设为seq_Mine,我该这样写吗:
INSERT INTO #PageIndexForUsers (IndexID,UserID)
SELECT seq_Mine.nextval, U.UserID FROM forums_Users U……create PROCEDURE forums_UsersInRole_Get
(
@PageIndex int,
@PageSize int,
@SortBy int = 0,
@SortOrder int = 0,
@RoleID int,
@UserAccountStatus smallint = 1,
@ReturnRecordCount bit = 0
)
AS
BEGIN
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @RowsToReturn int
DECLARE @TotalUsers int-- Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1-- First set the rowcount
SET @RowsToReturn = @PageSize * (@PageIndex + 1)
SET ROWCOUNT @RowsToReturn-- Create a temp table to store the select results
CREATE TABLE #PageIndexForUsers
(
IndexID int IDENTITY (1, 1) NOT NULL,
UserID int
) -- Do we need to return a record count?
-- *************************************
IF (@ReturnRecordCount = 1)
SET @TotalUsers = (SELECT count(R.UserID) FROM forums_Users U, forums_UsersInRoles R, forums_UserProfile P WHERE R.UserID = U.UserID AND R.UserID = P.UserID AND U.UserAccountStatus = @UserAccountStatus AND EnableDisplayInMemberList = 1 AND RoleID = @RoleID)-- Special case depending on what the user wants and how they want it ordered by
-- *************************************-- Sort by Date Joined
IF @SortBy = 0 AND @SortOrder = 1
INSERT INTO #PageIndexForUsers (UserID)
SELECT U.UserID FROM forums_Users U, forums_UsersInRoles R, forums_UserProfile P WHERE R.UserID = U.UserID AND R.UserID = P.UserID AND U.UserAccountStatus = @UserAccountStatus AND EnableDisplayInMemberList = 1 AND RoleID = @RoleID ORDER BY DateCreated
ELSE IF @SortBy = 0 AND @SortOrder = 0
INSERT INTO #PageIndexForUsers (UserID)
SELECT U.UserID FROM forums_Users U, forums_UsersInRoles R, forums_UserProfile P WHERE R.UserID = U.UserID AND R.UserID = P.UserID AND U.UserAccountStatus = @UserAccountStatus AND EnableDisplayInMemberList = 1 AND RoleID = @RoleID ORDER BY DateCreated DESC-- Get the user details
SELECT
*,
IsModerator = (SELECT Count(*) FROM forums_Moderators WHERE UserID = U.UserID)
FROM
forums_Users U (nolock),
forums_UserProfile P,
#PageIndexForUsers
WHERE
U.UserID = #PageIndexForUsers.UserID AND
U.UserID = P.UserID AND
#PageIndexForUsers.IndexID > @PageLowerBound AND
#PageIndexForUsers.IndexID < @PageUpperBound
ORDER BY
#PageIndexForUsers.IndexID
END
解决方案 »
- 求助!!刚写完的一个分页 编译器通过 程序调用的时候 报标识符无效 代码如下:
- 袁勤勇 太不负责了!
- 关于:ORACLE数据类型
- 安装了Oracle 8.1.7后电脑开机很慢
- 请教各位oracle DB和爱好者高手们,由于我的数据库产生的归档日志太大,我要如何处理可以把以前产生的归档文件删除掉让它重新开始产生,但又不影响备以后数据库出问题后来恢复它。
- ORACLE 遍历字符串
- 请教怎样交换分区,即怎样使用exchange命令?
- java中,如何实现系统当前时间和oracle数据库中类型为date型数据的比较求出差的月份?
- 紧急请教高手:oracle中如何设置自增字段???
- 再送分!!急、急、急PL/SQL问题,高手请进
- 安装oracle8.17时出现" 写入文件:d:\oracle\ora81\jis\lib\full_orb.jar时出现错误,"
- 本表的一列用另一张表的一列进行更新的SQL语句
t.*,
(SELECT Count(*) FROM forums_Moderators WHERE UserID = U.UserID) IsModerator
FROM
forums_Users U,
forums_UserProfile P
where ...
create sequence SP_TP_SGJKXH
minvalue 1
maxvalue 9999999
start with 1
increment by 1
cache 20
cycle;
使用序列你的写法是对的.
不太懂sqlserver
试试我改的这个
CREATE OR REPLACE PROCEDURE FORUMS_USERSINROLE_GET(PAGEINDEX INTEGER,
PAGESIZE INTEGER,
SORTBY INTEGER,
SORTORDER INTEGER,
ROLEID INTEGER,
USERACCOUNTSTATUS INTEGER,
RETURNRECORDCOUNT INTEGER) AS
PAGELOWERBOUND INTEGER;
PAGEUPPERBOUND INTEGER;
ROWSTORETURN INTEGER;
TOTALUSERS INTEGER;
BEGIN PAGELOWERBOUND := PAGESIZE * PAGEINDEX;
PAGEUPPERBOUND := PAGELOWERBOUND + PAGESIZE + 1; ROWSTORETURN := PAGESIZE * (PAGEINDEX + 1);
v_sql :='CREATE TABLE #PAGEINDEXFORUSERS(INDEXID number(10) NOT NULL, USERID number(10))';
EXECUTE IMMEDIATE v_sql;
IF (RETURNRECORDCOUNT = 1) THEN
SELECT COUNT(R.USERID)
INTO TOTALUSERS
FROM FORUMS_USERS U, FORUMS_USERSINROLES R, FORUMS_USERPROFILE P
WHERE R.USERID = U.USERID
AND R.USERID = P.USERID
AND U.USERACCOUNTSTATUS = USERACCOUNTSTATUS
AND ENABLEDISPLAYINMEMBERLIST = 1
AND ROLEID = ROLEID;
END IF;IF (SORTBY = 0) AND (SORTORDER = 1) THEN INSERT INTO #PAGEINDEXFORUSERS(USERID) SELECT U.USERID FROM FORUMS_USERS U, FORUMS_USERSINROLES R, FORUMS_USERPROFILE P WHERE R.USERID = U.USERID AND R.USERID = P.USERID AND U.USERACCOUNTSTATUS = USERACCOUNTSTATUS AND ENABLEDISPLAYINMEMBERLIST = 1 AND ROLEID = ROLEID ORDER BY DATECREATED; ELSIF(SORTBY = 0) AND (SORTORDER = 0) THEN INSERT INTO #PAGEINDEXFORUSERS(USERID) SELECT U.USERID FROM FORUMS_USERS U, FORUMS_USERSINROLES R, FORUMS_USERPROFILE P WHERE R.USERID = U.USERID AND R.USERID = P.USERID AND U.USERACCOUNTSTATUS = USERA; CCOUNTSTATUS AND ENABLEDISPLAYINMEMBERLIST = 1 AND ROLEID = ROLEID ORDER BY DATECREATED DESC
END IF;
SELECT(SELECT COUNT(*) FROM FORUMS_MODERATORS WHERE USERID = U.USERID) INTO ISMODERATOR FROM FORUMS_USERS U(NOLOCK), FORUMS_USERPROFILE P, #PAGEINDEXFORUSERS WHERE U.USERID = #PAGEINDEXFORUSERS.USERID AND U.USERID = P.USERID AND #PAGEINDEXFORUSERS.INDEXID > @PAGELOWERBOUND AND #PAGEINDEXFORUSERS.INDEXID < PAGEUPPERBOUND ORDER BY #PAGEINDEXFORUSERS.INDEXID;
END FORUMS_USERSINROLE_GET;
CREATE OR REPLACE PROCEDURE FORUMS_USERSINROLE_GET(PAGEINDEX INTEGER,
PAGESIZE INTEGER,
SORTBY INTEGER,
SORTORDER INTEGER,
ROLEID INTEGER,
USERACCOUNTSTATUS INTEGER,
RETURNRECORDCOUNT INTEGER) AS
PAGELOWERBOUND INTEGER:=0;
PAGEUPPERBOUND INTEGER:=0;
ROWSTORETURN INTEGER:=0;
TOTALUSERS INTEGER:=0;
不过你的这几张表大不大呀 目前看基本向上面写的就是转成了ORACLE PROCEDURE
其实,如果几张表都比较大的话,你这么连的效率就太低了
就算在这几列上作了索引也一样 笛卡尔乘积会惊人的大的
我曾遇到过三张表都不是很大 但10万*1万*1万 有多大 自己算去吧 四个CPU的SUN SERVER 跑了半个多小时 都没跑完