oracle 导出页面grid数据的存储过程

来源：互联网发布：千寻网络位置服务编辑：程序博客网时间：2024/06/05 04:47

CREATE OR REPLACE PROCEDURE PR_PRINT(PLID       IN NUMBER, --导出流水号
                                     RESULTPAGE OUT SYS_REFCURSOR, --导出结果集
                                     DATANUMBER OUT NUMBER, --导出结果集行数
                                     ISONEPAGE NUMBER) --是否导出当前页 (1：导出当前 0：导出所有)
AS
PLSQL CLOB; --导出SQL
CURSOR PRINTLOGTABLE IS
    SELECT PL_SQL,
           PL_PAGEINDEX,
           PL_PAGESIZE,
           PL_SORTNAME,
           PL_SORTORDER,
           PL_PRINTCOLS
      FROM SYS_LOG_PRINT
     WHERE PL_ID = PLID; --导出记录数据
C_ROW PRINTLOGTABLE%ROWTYPE; --游标行
BEGIN
IF ISONEPAGE = 1 THEN
    FOR C_ROW IN PRINTLOGTABLE LOOP
      --PLSQL := C_ROW.PL_SQL;
      PLSQL := ' SELECT ' || C_ROW.PL_PRINTCOLS || ' FROM(' || C_ROW.PL_SQL || ') ';
      PAGE.PAGEPRO(PLSQL, --调用分页存储过程获取数据
                   C_ROW.PL_PAGEINDEX,
                   C_ROW.PL_PAGESIZE,
                   C_ROW.PL_SORTNAME,
                   RESULTPAGE,
                   DATANUMBER,
                   C_ROW.PL_SORTORDER);
      UPDATE SYS_LOG_PRINT SET PL_SUECCSS = 0 WHERE PL_ID = PLID;

    END LOOP;
ELSE
    FOR C_ROW IN PRINTLOGTABLE LOOP
      PLSQL := ' SELECT ROWNUM RN, ' || C_ROW.PL_PRINTCOLS || ' FROM(' ||
               C_ROW.PL_SQL || ') '; -- ROWNUM RN到程序中转为序号列，与存储过程保持一致
      --PLSQL := C_ROW.PL_SQL;
      OPEN RESULTPAGE FOR PLSQL;
      UPDATE SYS_LOG_PRINT SET PL_SUECCSS = 0 WHERE PL_ID = PLID;
    END LOOP;
END IF;
EXCEPTION
WHEN OTHERS THEN
    OPEN RESULTPAGE FOR ' SELECT NULL FROM DUAL ';
END;

-----------------------------------------------------------------------------------------------------------------------------------------------------------

-- Create table
create table SYS_LOG_PRINT
(
pl_id        NUMBER(10),
pl_date      DATE,
pl_operatper NUMBER,
pl_pageindex NUMBER,
pl_pagesize NUMBER,
pl_sortname VARCHAR2(20),
pl_sortorder VARCHAR2(4) default 'desc',
pl_printcols CLOB,
pl_sueccss   NUMBER default 1,
pl_sql       CLOB
) ;
-- Add comments to the table
comment on table SYS_LOG_PRINT
is '导出日志';
-- Add comments to the columns
comment on column SYS_LOG_PRINT.pl_id
is '导出流水号';
comment on column SYS_LOG_PRINT.pl_date
is '导出时间';
comment on column SYS_LOG_PRINT.pl_operatper
is '导出人';
comment on column SYS_LOG_PRINT.pl_pageindex
is '导出页';
comment on column SYS_LOG_PRINT.pl_pagesize
is '导出页行数';
comment on column SYS_LOG_PRINT.pl_sortname
is '导出排序列';
comment on column SYS_LOG_PRINT.pl_sortorder
is '导出排序方式';
comment on column SYS_LOG_PRINT.pl_printcols
is '导出显示列';
comment on column SYS_LOG_PRINT.pl_sueccss
is '导出是否成功（1：失败[导出初始化阶段] 0：成功）';
comment on column SYS_LOG_PRINT.pl_sql
is '导出SQL';

0 0