create or replace package body gzga_common_pg is
PROCEDURE gzga_list_calculate_sp(p_total_records IN NUMBER,
p_total_pages OUT NUMBER,
p_start_record OUT NUMBER,
p_end_record OUT NUMBER,
p_error_code OUT VARCHAR2,
p_page_number IN OUT NUMBER)
IS
-- The number of records per page
n_record_per_page NUMBER;
-- The maximum records for displaying
n_max_record_display NUMBER;
BEGIN
-- error code initialization
p_error_code := 'SUCCESS';
-- parameters initialization
SELECT TO_NUMBER(parametervalue) into n_record_per_page
FROM parameter
WHERE parameterid = 'REC_PER_PAGE'; SELECT TO_NUMBER(parametervalue) into n_max_record_display
FROM parameter
WHERE parameterid = 'MAX_REC_DISPLAY';
IF(p_total_records = 0) THEN
p_start_record := 0;
p_end_record := 0;
p_total_pages := 1;
p_page_number := 1;
ELSIF (p_total_records > n_max_record_display) THEN
p_start_record := 0;
p_end_record := 0;
p_total_pages := 1;
p_page_number := 1;
p_error_code := 'EXCEED';
ELSE
-- Calcalate the number of pages for displaying all records
p_total_pages := p_total_records / n_record_per_page;
IF (ROUND(p_total_pages) > p_total_pages OR
ROUND(p_total_pages) = p_total_pages) THEN
p_total_pages := ROUND(p_total_pages);
ELSE
p_total_pages := ROUND(p_total_pages) + 1;
END IF;
p_start_record := p_page_number * n_record_per_page - n_record_per_page+1;
p_end_record := p_page_number * n_record_per_page;
IF(p_end_record > p_total_records) THEN
p_end_record := p_total_records;
p_start_record := p_total_pages * n_record_per_page - n_record_per_page + 1;
p_page_number := p_total_pages;
END IF;
IF(p_start_record < 1) THEN
p_start_record := 1;
p_page_number := 1;
IF(p_total_records >= n_record_per_page) THEN
p_end_record := p_start_record + n_record_per_page - 1;
ELSE
p_end_record := p_total_records;
END IF;
END IF;
END IF;
END gzga_list_calculate_sp;
end gzga_common_pg;
现在我想把它移植到sqlserver2000(个人版)上来使用,但是在查询分析器里面没有运行成功,请大家帮忙啦,将其改成sqlserver2000下的存储过程,向你们学习.....谢谢啦!!
PROCEDURE gzga_list_calculate_sp(p_total_records IN NUMBER,
p_total_pages OUT NUMBER,
p_start_record OUT NUMBER,
p_end_record OUT NUMBER,
p_error_code OUT VARCHAR2,
p_page_number IN OUT NUMBER)
IS
-- The number of records per page
n_record_per_page NUMBER;
-- The maximum records for displaying
n_max_record_display NUMBER;
BEGIN
-- error code initialization
p_error_code := 'SUCCESS';
-- parameters initialization
SELECT TO_NUMBER(parametervalue) into n_record_per_page
FROM parameter
WHERE parameterid = 'REC_PER_PAGE'; SELECT TO_NUMBER(parametervalue) into n_max_record_display
FROM parameter
WHERE parameterid = 'MAX_REC_DISPLAY';
IF(p_total_records = 0) THEN
p_start_record := 0;
p_end_record := 0;
p_total_pages := 1;
p_page_number := 1;
ELSIF (p_total_records > n_max_record_display) THEN
p_start_record := 0;
p_end_record := 0;
p_total_pages := 1;
p_page_number := 1;
p_error_code := 'EXCEED';
ELSE
-- Calcalate the number of pages for displaying all records
p_total_pages := p_total_records / n_record_per_page;
IF (ROUND(p_total_pages) > p_total_pages OR
ROUND(p_total_pages) = p_total_pages) THEN
p_total_pages := ROUND(p_total_pages);
ELSE
p_total_pages := ROUND(p_total_pages) + 1;
END IF;
p_start_record := p_page_number * n_record_per_page - n_record_per_page+1;
p_end_record := p_page_number * n_record_per_page;
IF(p_end_record > p_total_records) THEN
p_end_record := p_total_records;
p_start_record := p_total_pages * n_record_per_page - n_record_per_page + 1;
p_page_number := p_total_pages;
END IF;
IF(p_start_record < 1) THEN
p_start_record := 1;
p_page_number := 1;
IF(p_total_records >= n_record_per_page) THEN
p_end_record := p_start_record + n_record_per_page - 1;
ELSE
p_end_record := p_total_records;
END IF;
END IF;
END IF;
END gzga_list_calculate_sp;
end gzga_common_pg;
现在我想把它移植到sqlserver2000(个人版)上来使用,但是在查询分析器里面没有运行成功,请大家帮忙啦,将其改成sqlserver2000下的存储过程,向你们学习.....谢谢啦!!
@p_total_pages OUTPUT int,
@p_start_record OUTPUT int,
@p_end_record OUTPUT int,
@p_error_code OUTPUT VARCHAR(20),
@p_page_number OUTPUT int) --好像只有output类型
AS
GO--太长了,没兴趣改了
变量定义前面要加 @
类型转换: cast,convert函数代替 to_number
select赋值: select 变量=列 from ...
IF...ELSE
在执行 Transact-SQL 语句时强加条件。如果条件满足(布尔表达式返回 TRUE 时),则在 IF 关键字及其条件之后执行 Transact-SQL 语句。可选的 ELSE 关键字引入备用的 Transact-SQL 语句,当不满足 IF 条件时(布尔表达式返回 FALSE),就执行这个语句。语法
IF Boolean_expression
{ sql_statement | statement_block } --如果不是一个语句,要加begin end
[ ELSE
{ sql_statement | statement_block } ]
n_record_per_page NUMBER;-- The maximum records for displaying
n_max_record_display NUMBER;--
内部变量定义加declare
@p_total_records INT,
@p_total_pages INT OUTPUT ,
@p_start_record INT OUTPUT,
@p_end_record INT OUTPUT,
@p_error_code VARCHAR(1000) OUTPUT,
@p_page_number INT OUTPUT)ASBEGIN
DECLARE @n_record_per_page INT
DECLARE @n_max_record_display INT
SET @p_error_code = 'SUCCESS'
SELECT @n_record_per_page = CAST(parametervalue AS INT) FROM parameter
WHERE parameterid = 'REC_PER_PAGE' SELECT n_max_record_display = CAST(parametervalue AS INT) FROM parameter
WHERE parameterid = 'MAX_REC_DISPLAY'
IF (@p_total_records = 0)
BEGIN
SET @p_start_record = 0
SET @p_end_record = 0
SET @p_total_pages = 1
SET @p_page_number = 1
END
ELSE IF (@p_total_records > @n_max_record_display)
BEGIN
SET @p_start_record = 0
SET @p_end_record = 0
SET @p_total_pages = 1
SET @p_page_number = 1
SET @p_error_code = 'EXCEED'
END
ELSE
BEGIN
SET @p_total_pages = @p_total_records/@n_record_per_page
IF ((ROUND(@p_total_pages,0) > @p_total_pages) OR (ROUND(@p_total_pages,0) = @p_total_pages))
SET @p_total_pages = ROUND(p_total_pages,0)
ELSE
SET @p_total_pages = ROUND(@p_total_pages,0) + 1
SET @p_start_record = @p_page_number * @n_record_per_page - @n_record_per_page+1
SET @p_end_record = @p_page_number * @n_record_per_page IF(@p_end_record > @p_total_records)
BEGIN
SET @p_end_record = @p_total_records
SET @p_start_record = @p_total_pages * @n_record_per_page - @n_record_per_page + 1
SET @p_page_number = @p_total_pages
END IF (@p_start_record < 1)
BEGIN
SET @p_start_record = 1
SET @p_page_number = 1
IF(@p_total_records >= @n_record_per_page)
SET @p_end_record = @p_start_record + @n_record_per_page - 1
ELSE
SET @p_end_record = @p_total_records
END
END
END
CREATE PROCEDURE gzga_list_calculate_sp(
@p_total_records INT,
@p_total_pages INT OUTPUT ,
@p_start_record INT OUTPUT,
@p_end_record INT OUTPUT,
@p_error_code VARCHAR(1000) OUTPUT,
@p_page_number INT OUTPUT)ASBEGIN
DECLARE @n_record_per_page INT
DECLARE @n_max_record_display INT
SET @p_error_code = 'SUCCESS'
SELECT @n_record_per_page = CAST(parametervalue AS INT) FROM parameter
WHERE parameterid = 'REC_PER_PAGE' SELECT @n_max_record_display = CAST(parametervalue AS INT) FROM parameter
WHERE parameterid = 'MAX_REC_DISPLAY'
IF (@p_total_records = 0)
BEGIN
SET @p_start_record = 0
SET @p_end_record = 0
SET @p_total_pages = 1
SET @p_page_number = 1
END
ELSE IF (@p_total_records > @n_max_record_display)
BEGIN
SET @p_start_record = 0
SET @p_end_record = 0
SET @p_total_pages = 1
SET @p_page_number = 1
SET @p_error_code = 'EXCEED'
END
ELSE
BEGIN
SET @p_total_pages = @p_total_records/@n_record_per_page
IF ((ROUND(@p_total_pages,0) > @p_total_pages) OR (ROUND(@p_total_pages,0) = @p_total_pages))
SET @p_total_pages = ROUND(p_total_pages,0)
ELSE
SET @p_total_pages = ROUND(@p_total_pages,0) + 1
SET @p_start_record = @p_page_number * @n_record_per_page - @n_record_per_page+1
SET @p_end_record = @p_page_number * @n_record_per_page IF(@p_end_record > @p_total_records)
BEGIN
SET @p_end_record = @p_total_records
SET @p_start_record = @p_total_pages * @n_record_per_page - @n_record_per_page + 1
SET @p_page_number = @p_total_pages
END IF (@p_start_record < 1)
BEGIN
SET @p_start_record = 1
SET @p_page_number = 1
IF(@p_total_records >= @n_record_per_page)
SET @p_end_record = @p_start_record + @n_record_per_page - 1
ELSE
SET @p_end_record = @p_total_records
END
END
END