SQL Server 分页过程
-----------------------------------------------------
-- Export file for user SA --
-- Created by Administrator on 2005-1-30, 18:05:12 --
-----------------------------------------------------
spool asdsd.log
prompt
prompt Creating table TEST
prompt ===================
prompt
create table TEST
(
NAME VARCHAR2(20),
PASSWD VARCHAR2(20)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
minextents 1
maxextents unlimited
);
prompt
prompt Creating package DOTNET
prompt =======================
prompt
create or replace package DotNet as
TYPE type_cur IS REF CURSOR; --定义游标变量用于返回记录集
PROCEDURE DotNetPagination(
Pindex in varchar2, --分页索引
Psize in varchar2, --页面大小
Psql in varchar2, --产生dataset的sql语句
Pcount out number, --返回分页总数
v_cur out type_cur --返回当前页数据记录
);
PROCEDURE DotNetPageRecordsCount(
Psqlcount in varchar2,
Prcount out number
);
end DotNet;
/
prompt
prompt Creating package PKG_TEST
prompt =========================
prompt
CREATE OR REPLACE PACKAGE pkg_test
AS
TYPE myrctype IS REF CURSOR;
PROCEDURE get (p_id in NUMBER, p_rc OUT myrctype);
END pkg_test;
/
prompt
prompt Creating package PKG_TEST_FUNCTION
prompt ==================================
prompt
create or replace package pkg_test_function as
/* 定义ref cursor类型
不加return类型，为弱类型，允许动态sql查询，
否则为强类型，无法使用动态sql查询;
*/
type myrctype is ref cursor;
--函数申明
function get(intID number) return myrctype;
end pkg_test_function;
/
prompt
prompt Creating procedure DOTNETPAGINATION
prompt ===================================
prompt
CREATE OR REPLACE PROCEDURE dotnetpagination (
pindex IN number,
psize IN number,
psql IN VARCHAR2,
pcount OUT NUMBER
)
IS
v_sql VARCHAR2 (1000);
v_count NUMBER;
v_plow NUMBER;
v_phei NUMBER;
BEGIN
------------------------------------------------------------取分页总数
v_sql := 'select count(*) from (' || psql || ')';
EXECUTE IMMEDIATE v_sql
INTO v_count;
pcount := CEIL (v_count / psize);
------------------------------------------------------------显示任意页内容
v_phei := pindex * psize + psize;
v_plow := v_phei - psize + 1;
END dotnetpagination;
/
prompt
prompt Creating package body DOTNET
prompt ============================
prompt
CREATE OR REPLACE PACKAGE BODY dotnet
AS
--***************************************************************************************
PROCEDURE dotnetpagination (
pindex IN VARCHAR2,
psize IN VARCHAR2,
psql IN VARCHAR2,
pcount OUT NUMBER,
v_cur OUT type_cur
)
IS
v_sql VARCHAR2 (1000);
v_count NUMBER;
v_plow NUMBER;
v_phei NUMBER;
BEGIN
------------------------------------------------------------取分页总数
v_sql := 'select count(*) from (' || psql || ')';
EXECUTE IMMEDIATE v_sql
INTO v_count;
pcount := CEIL (v_count / psize);
------------------------------------------------------------显示任意页内容
v_phei := pindex * psize + psize;
v_plow := v_phei - psize + 1;
--Psql := 'select rownum rn,t.* from cd_ssxl t' ; --要求必须包含rownum字段
v_sql :='select * from ('|| psql || ') where rownum between '|| v_plow || ' and ' || v_phei;
OPEN v_cur FOR v_sql;
END dotnetpagination;
--**************************************************************************************
procedure DotNetPageRecordsCount(
Psqlcount in varchar2,
Prcount out number
)
as
v_sql varchar2(1000);
v_prcount number;
begin
v_sql := 'select count(*) from (' || Psqlcount || ')';
execute immediate v_sql into v_prcount;
Prcount := v_prcount; --返回记录总数
end DotNetPageRecordsCount;
--**************************************************************************************
END dotnet;
/
prompt
prompt Creating package body PKG_TEST
prompt ==============================
prompt
CREATE OR REPLACE PACKAGE BODY pkg_test
AS
PROCEDURE get (p_id in NUMBER, p_rc OUT myrctype)
IS
sqlstr VARCHAR2 (500);
BEGIN
IF p_id = 0
THEN
OPEN p_rc FOR
SELECT phone, msg_content, gateid
FROM wwchat_del_log
WHERE ROWNUM < 100;
ELSE
sqlstr :=
'SELECT phone,msg_content,gateid FROM wwchat_del_log where rownum<100';
OPEN p_rc FOR sqlstr USING p_id;
END IF;
END get;
END pkg_test;
/
prompt
prompt Creating package body PKG_TEST_FUNCTION
prompt =======================================
prompt
CREATE OR REPLACE PACKAGE BODY pkg_test_function
AS
--函数体
FUNCTION get (intid NUMBER)
RETURN myrctype
IS
rc myrctype; --定义ref cursor变量
sqlstr VARCHAR2 (500);
BEGIN
IF intid = 0
THEN
--静态测试，直接用select语句直接返回结果
OPEN rc FOR
SELECT phone, msg_content, gateid
FROM wwchat_del_log
WHERE ROWNUM < 100;
ELSE
--动态sql赋值，用:w_id来申明该变量从外部获得
sqlstr :=
'select id,name,sex,address,postcode,birthday from student where id=:w_id';
--动态测试，用sqlstr字符串返回结果，用using关键词传递参数
OPEN rc FOR sqlstr USING intid;
END IF;
RETURN rc;
END get;
END pkg_test_function;
/