我把sqlserver下的存储过程转为oracle下,其他的存储过程都没问题,就只是tablename_GetPaged这样的编译报错,
CREATE OR REPLACE PROCEDURE BANK_RES_INFO_GetPaged
(
WhereClause IN VARCHAR2 DEFAULT NULL,
OrderBy IN VARCHAR2 DEFAULT NULL,
PageIndex IN INT DEFAULT NULL,
PageSize IN INT DEFAULT NULL,
RCT1 IN OUT GLOBALPKG.RCT1,
RCT2 IN OUT GLOBALPKG.RCT1
)
AS
PageLowerBound INT;
PageUpperBound INT;
RowsToReturn INT;
-- Insert into the temp table
SQL_ADV VARCHAR2(3000);
BEGIN
-- First set the rowcount
RowsToReturn := PageSize * ( PageIndex + 1 );
--SET ROWCOUNT @RowsToReturn
-- Set the page bounds
PageLowerBound := PageSize * PageIndex;
PageUpperBound := PageLowerBound + PageSize;
/* Create a temp table to store the select results*/ EXECUTE IMMEDIATE 'TRUNCATE TABLE PageIndex';
SQL_ADV := 'INSERT INTO #PageIndex (ID)';
SQL_ADV := SQL_ADV || ' SELECT [ID]';
SQL_ADV := SQL_ADV || ' FROM GZ.[BANK_RES_INFO]';
IF LENGTH(RTRIM(WhereClause)) > 0 THEN
BEGIN
SQL_ADV := SQL_ADV || ' WHERE ' || WhereClause;
NULL;
END;
END IF;
IF LENGTH(RTRIM(OrderBy)) > 0 THEN
BEGIN
SQL_ADV := SQL_ADV || ' ORDER BY ' || OrderBy;
NULL;
END;
END IF;
-- Populate the temp table
EXECUTE IMMEDIATE( SQL_ADV);
/* Return total count*/ OPEN RCT1 FOR
SELECT "SQL%ROWCOUNT"
FROM SYS.DUAL;
/* Return paged results*/ OPEN RCT2 FOR
SELECT
ID,
RES,
RES_CODE,
RES_TYPE,
XCOORDINATE,
YCOORDINATE,
REMARK
FROM
(SELECT
O.ID,
O.RES,
O.RES_CODE,
O.RES_TYPE,
O.XCOORDINATE,
O.YCOORDINATE,
O.REMARK
FROM GZ.BANK_RES_INFO O,
PageIndex PageIndex
WHERE O.ID = PageIndex.ID
AND PageIndex.IndexID > BANK_RES_INFO_GetPaged.PageLowerBound
AND PageIndex.IndexID <= BANK_RES_INFO_GetPaged.PageUpperBound
ORDER BY PageIndex.IndexID
)WHERE ROWNUM < RowsToReturn+1;
END;取行数的SELECT "SQL%ROWCOUNT"
FROM SYS.DUAL;
47/3 PL/SQL: SQL Statement ignored
47/10 PL/SQL: ORA-00904: "SQL%ROWCOUNT": invalid identifier
请求帮助,谢谢
CREATE OR REPLACE PROCEDURE BANK_RES_INFO_GetPaged
(
WhereClause IN VARCHAR2 DEFAULT NULL,
OrderBy IN VARCHAR2 DEFAULT NULL,
PageIndex IN INT DEFAULT NULL,
PageSize IN INT DEFAULT NULL,
RCT1 IN OUT GLOBALPKG.RCT1,
RCT2 IN OUT GLOBALPKG.RCT1
)
AS
PageLowerBound INT;
PageUpperBound INT;
RowsToReturn INT;
-- Insert into the temp table
SQL_ADV VARCHAR2(3000);
BEGIN
-- First set the rowcount
RowsToReturn := PageSize * ( PageIndex + 1 );
--SET ROWCOUNT @RowsToReturn
-- Set the page bounds
PageLowerBound := PageSize * PageIndex;
PageUpperBound := PageLowerBound + PageSize;
/* Create a temp table to store the select results*/ EXECUTE IMMEDIATE 'TRUNCATE TABLE PageIndex';
SQL_ADV := 'INSERT INTO #PageIndex (ID)';
SQL_ADV := SQL_ADV || ' SELECT [ID]';
SQL_ADV := SQL_ADV || ' FROM GZ.[BANK_RES_INFO]';
IF LENGTH(RTRIM(WhereClause)) > 0 THEN
BEGIN
SQL_ADV := SQL_ADV || ' WHERE ' || WhereClause;
NULL;
END;
END IF;
IF LENGTH(RTRIM(OrderBy)) > 0 THEN
BEGIN
SQL_ADV := SQL_ADV || ' ORDER BY ' || OrderBy;
NULL;
END;
END IF;
-- Populate the temp table
EXECUTE IMMEDIATE( SQL_ADV);
/* Return total count*/ OPEN RCT1 FOR
SELECT "SQL%ROWCOUNT"
FROM SYS.DUAL;
/* Return paged results*/ OPEN RCT2 FOR
SELECT
ID,
RES,
RES_CODE,
RES_TYPE,
XCOORDINATE,
YCOORDINATE,
REMARK
FROM
(SELECT
O.ID,
O.RES,
O.RES_CODE,
O.RES_TYPE,
O.XCOORDINATE,
O.YCOORDINATE,
O.REMARK
FROM GZ.BANK_RES_INFO O,
PageIndex PageIndex
WHERE O.ID = PageIndex.ID
AND PageIndex.IndexID > BANK_RES_INFO_GetPaged.PageLowerBound
AND PageIndex.IndexID <= BANK_RES_INFO_GetPaged.PageUpperBound
ORDER BY PageIndex.IndexID
)WHERE ROWNUM < RowsToReturn+1;
END;取行数的SELECT "SQL%ROWCOUNT"
FROM SYS.DUAL;
47/3 PL/SQL: SQL Statement ignored
47/10 PL/SQL: ORA-00904: "SQL%ROWCOUNT": invalid identifier
请求帮助,谢谢
SELECT "SQL%ROWCOUNT"
FROM SYS.DUAL; 什么意思??
OPEN RCT1 FOR SELECT COUNT(*) FROM PageIndex;
SELECT "SQL%ROWCOUNT"
FROM SYS.DUAL;
获得游标rct1打开的总行数吧