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;
/