一个简单的分页存储过程demo
?
--定义一个包和游标用于查询使用create or replace package sp_nms_getNmslist_page_packageAuthid Current_UserasTYPE page_cursor is ref cursor;end sp_nms_getNmslist_page_package;--主题分页查询逻辑CREATE OR REPLACE PROCEDURE SP_TABLE_PAGEMODEL(--PAGE_NOW IN NUMBER,--当前页码--PAGE_SIZE IN NUMBER,--每页需要显示的条数 V_BEGIN IN NUMBER,--起始条数 PAGE_SIZE IN NUMBER,--每页需要显示的条数 TABLE_NAME IN VARCHAR2,--查询的表名 WHERESQL IN VARCHAR2, --查询需要的where条件 eg. ORDERBYSQL IN VARCHAR2,--排序需要的排序条件 eg. ROW_COUNT OUT NUMBER, PAGE_COUNT OUT NUMBER, RS_LIST OUT SP_NMS_GETNMSLIST_PAGE_PACKAGE.PAGE_CURSOR) ISV_SQL VARCHAR2(1000);V_END NUMBER:=V_BEGIN+PAGE_SIZE-1;--V_BEGIN NUMBER:=(PAGE_NOW-1)*PAGE_SIZE+1;--V_END NUMBER:=PAGE_SIZE*PAGE_NOW;BEGIN V_SQL:='SELECT * FROM (SELECT ROWNUM R,T.* FROM (SELECT * FROM '||TABLE_NAME||') T WHERE ROWNUM<='||V_END||') WHERE R>='||V_BEGIN ; dbms_output.put_line(V_SQL); IF WHERESQL IS NOT NULL THEN V_SQL:=V_SQL || ' ' || WHERESQL; END IF; IF ORDERBYSQL IS NOT NULL THEN V_SQL:=V_SQL || ' ' ||ORDERBYSQL; END IF; dbms_output.put_line(V_SQL); OPEN RS_LIST FOR V_SQL; V_SQL:='SELECT COUNT(*) FROM '||TABLE_NAME ||' WHERE 1=1' ; IF WHERESQL IS NOT NULL THEN V_SQL:=V_SQL || ' ' ||WHERESQL; END IF; IF ORDERBYSQL IS NOT NULL THEN V_SQL:=V_SQL || ' ' ||ORDERBYSQL; END IF; dbms_output.put_line(V_SQL); EXECUTE IMMEDIATE V_SQL INTO ROW_COUNT;--立即执行此SQL语句,并将结果赋给ROW_COUNT IF MOD(ROW_COUNT,PAGE_SIZE)=0 THEN PAGE_COUNT:=ROW_COUNT/PAGE_SIZE; ELSE PAGE_COUNT:=FLOOR(ROW_COUNT/PAGE_SIZE)+1; END IF; --将异常情况插入到异常表 EXCEPTION WHEN OTHERS THEN SP_NM_EXCEPTION_LOG_INSERT('SP_TABLE_PAGEMODEL',SQLCODE,SP_EXCEPTION_LOG_OPERATIONTYPE.C_SELECT);END;------创建一个用于存储异常的表 -- Create tablecreate table MMS_NM_EXCEPTION_LOGS( ID VARCHAR2(19) not null, PROCEDURE_NAME VARCHAR2(50) not null, EXCEPTION_TYPE VARCHAR2(500), OPERATION_TYPE NUMBER not null, OCCUR_TIME DATE not null)tablespace MMSALBUM_SERVICE_DAT pctfree 10 initrans 1 maxtrans 255 storage ( initial 64K next 1M minextents 1 maxextents unlimited pctincrease 0 );-- Add comments to the table comment on table MMS_NM_EXCEPTION_LOGS is '存储过程异常日志记录表';-- Add comments to the columns comment on column MMS_NM_EXCEPTION_LOGS.PROCEDURE_NAME is '发生异常的存储过程的名称';comment on column MMS_NM_EXCEPTION_LOGS.EXCEPTION_TYPE is '发生的异常的名称';comment on column MMS_NM_EXCEPTION_LOGS.OPERATION_TYPE is '1:insert,2:update,3:delete';comment on column MMS_NM_EXCEPTION_LOGS.OCCUR_TIME is '异常发生时间';--统一管理异常的存储过程CREATE OR REPLACE PACKAGE SP_EXCEPTION_LOG_OPERATIONTYPEAuthid Current_User--创建异常操作的常量集合,对应字段:P_OPERATION_TYPEASC_SELECT CONSTANT NUMBER := 1;C_INSERT CONSTANT NUMBER := 2;C_UPDATE CONSTANT NUMBER := 3;C_DELETE CONSTANT NUMBER := 4;END SP_EXCEPTION_LOG_OPERATIONTYPE;--创建异常入库的存储过程CREATE OR REPLACE PROCEDURE SP_NM_EXCEPTION_LOG_INSERT(P_PROCEDURE_NAME IN MMS_NM_EXCEPTION_LOGS.PROCEDURE_NAME%TYPE,P_EXCEPTION_CODE IN NUMBER,P_OPERATION_TYPE IN MMS_NM_EXCEPTION_LOGS.OPERATION_TYPE%TYPE)Authid Current_User--异常日志添加存储过程--meachlyang--params: P_PROCEDURE_NAME:存储过程名称,P_EXCEPTION_CODE:异常代码,--P_OPERATION_TYPE:操作类型(1:select,2:insert,3:update,4:delete)ASv_exception_type MMS_NM_EXCEPTION_LOGS.EXCEPTION_TYPE%TYPE;BEGIN -- 保存回滚点SAVEPOINT DO_INSERT;v_exception_type := SQLERRM(P_EXCEPTION_CODE);-- 保存异常信息到异常记录表中INSERT INTO MMS_NM_EXCEPTION_LOGS VALUES(EXCEPTION_LOG_SEQUENCE.NEXTVAL,P_PROCEDURE_NAME,v_exception_type,P_OPERATION_TYPE,sysdate); COMMIT;EXCEPTIONWHEN OTHERS THEN--此处异常不进行log记录，否则可能会出现死循环的现象ROLLBACK TO DO_INSERT; END SP_NM_EXCEPTION_LOG_INSERT;