一个简单的分页存储过程demo

 

--定义一个包和游标用于查询使用
create or replace package sp_nms_getNmslist_page_package
Authid Current_User
as
TYPE 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
) IS
V_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 table
create 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_OPERATIONTYPE
Authid Current_User
--	创建异常操作的常量集合,对应字段:P_OPERATION_TYPE
AS
	C_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)
AS
	v_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;
EXCEPTION
	WHEN OTHERS THEN
--	此处异常不进行log记录，否则可能会出现死循环的现象
	ROLLBACK TO DO_INSERT;
  
END SP_NM_EXCEPTION_LOG_INSERT;