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下的存储过程,向你们学习.....谢谢啦!!
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 numeric(12,2) output,
@p_start_record INT output,
@p_end_record INT output,
@p_error_code VARCHAR(100) output,
@p_page_number INT output)
as begin
-- The number of records per page
declare @n_record_per_page INT-- The maximum records for displaying
declare @n_max_record_display INT-- error code initialization
SET @p_error_code = 'SUCCESS'-- parameters initialization
SELECT @n_record_per_page=convert(int,parametervalue)
FROM parameter
WHERE parameterid = 'REC_PER_PAGE'SELECT @n_max_record_display=convert(int,parametervalue)
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
-- Calcalate the number of pages for displaying all records
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) begin
SET @p_total_pages = ROUND(@p_total_pages,0)
end
ELSE begin
SET @p_total_pages = ROUND(@p_total_pages,0) + 1
end
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_pageIF(@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
ENDIF(@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