CREATE OR REPLACE PROCEDURE zxb_fy(table_name in VARCHAR2, --需要分页的表名
summation_column in varchar2, --自定义字段合并的规则
groupby_column in varchar2, --分组字段合集
base_select_column in varchar2, --查询字段合集
character_byte in varchar2, --按字符还是按照字节区分
character_byte_length in number, --字符
presult out sys_refcursor) --输出的游标结果集
Authid Current_User is
fy_ys number(10) := 1;
is_exec_flag number(10) := 1;
base_table_count number(10);
v_createsql varchar2(4000);
v_sql varchar2(4000);
table_lj_name varchar2(200);
table_result_name varchar2(200);
table_base_name varchar2(200);
BEGIN
--0.1 输出参数内容
dbms_output.put_line(table_name||' '||summation_column||' '||groupby_column||' '||base_select_column||' '||character_byte||' '||character_byte_length);
--0.2 表名的变量命名
table_lj_name := table_name||'_lj';
table_result_name := table_name||'_result';
table_base_name := table_name||'_base';
dbms_output.put_line(table_lj_name||' '||table_result_name||' '||table_base_name);
--1.1 判断累加临时表是否存在,存在就删除
select count(*)
into base_table_count
from user_tables
where table_name = upper(table_lj_name);
if (base_table_count = 1) then
execute immediate 'drop table '||table_lj_name||'';
end if;
v_createsql := 'create table '||table_lj_name||' as select t.*,1 fund_lengthb,1 FUND_HJ_LENGTHB,1 ym from '||table_name||' t where 1 = 2';
dbms_output.put_line(v_createsql);
EXECUTE IMMEDIATE v_createsql;
--1.2 判断结果临时表是否存在,存在就删除
select count(*)
into base_table_count
from user_tables
where table_name = upper(table_result_name);
if (base_table_count = 1) then
execute immediate 'drop table '||table_result_name||'';
end if;
v_createsql := 'create table '||table_result_name||' as select t.*,1 fund_lengthb,1 FUND_HJ_LENGTHB,1 erro_ym,1 ym from '||table_name||' t where 1 = 2';
dbms_output.put_line(v_createsql);
EXECUTE IMMEDIATE v_createsql;
--1.3 判断基础数据临时表是否存在,存在就删除
select count(*)
into base_table_count
from user_tables
where table_name = upper(table_base_name);
if (base_table_count = 1) then
execute immediate 'drop table '||table_base_name||'';
end if;
--将临时表的数据插入测试基础表
v_createsql := 'create table '||table_base_name||' as
select * from '||table_name||' ';
dbms_output.put_line(v_createsql);
execute immediate v_createsql;
--1.4 输出基础临时表的数据条数
select count(*)
into base_table_count
from user_tables
where table_name = upper(table_base_name);
if (base_table_count = 1) then
v_sql := 'select count(1) from '||table_base_name||'';
execute immediate v_sql into base_table_count ;
dbms_output.put_line('原始数据总共条数:' || base_table_count);
end if;
--2. 进入判断循环体
while is_exec_flag > 0 loop
begin
--2.1.将基础数据按照规则累加,并插入到累加结果表
EXECUTE IMMEDIATE 'TRUNCATE TABLE '||table_lj_name||'';
v_sql := 'insert into '||table_lj_name||'
select t.*,
'||character_byte||'('||summation_column||') fund_lengthb,
SUM( '||character_byte||'('||summation_column||')) OVER(PARTITION BY '||groupby_column||' order by ROWNUM ASC) FUND_HJ_LENGTHB,
TRUNC((SUM( '||character_byte||'('||summation_column||'))
OVER(PARTITION BY '||groupby_column||' order by ROWNUM ASC)) / '||character_byte_length||' + 1) ym
from '||table_base_name||' t';
dbms_output.put_line('拼接出来的sql分页语句:' || v_sql);
execute immediate v_sql;
commit;
--2.2.将第一页的内容插入到结果临时表
dbms_output.put_line('本次插入的页码数:' || fy_ys);
v_sql := 'insert into '||table_result_name||'
select t.*,
'||fy_ys||'
from '||table_lj_name||' t
where ym = 1';
dbms_output.put_line('拼接出来的sql分页语句:' || v_sql);
execute immediate v_sql;
commit;
fy_ys := fy_ys + 1;
dbms_output.put_line('下一个需要插入的页码数量:' || fy_ys);
--2.3.将剩余基础数据插入到临时表进行下一步的累加计算
EXECUTE IMMEDIATE 'TRUNCATE TABLE '||table_base_name||'';
v_sql := '
insert into '||table_base_name||'
select '||base_select_column||'
from '||table_lj_name||'
where ym <> 1';
dbms_output.put_line('拼接出来的sql分页语句:' || v_sql);
execute immediate v_sql;
commit;
--2.4.查看基础数据是否还存在,用于循环体是否循环的标识
v_sql := 'select count(1) from '||table_base_name||'';
execute immediate v_sql into is_exec_flag;
dbms_output.put_line(''||table_base_name||'(基础表)数据剩余数据:' ||
is_exec_flag);
end;
end loop;
--3以游标形式循环输出结果
v_sql := 'select * from '||table_result_name||' where 1 = 1';
open presult for v_sql; --返回所有列
--4.存储过程完毕后,删除临时表
--4.1 判断累加临时表是否存在,存在就删除
select count(*)
into base_table_count
from user_tables
where table_name = upper(table_lj_name);
if (base_table_count = 1) then
execute immediate 'drop table '||table_lj_name||'';
end if;
--4.2 判断基础数据临时表是否存在,存在就删除
select count(*)
into base_table_count
from user_tables
where table_name = upper(table_base_name);
if (base_table_count = 1) then
execute immediate 'drop table '||table_base_name||'';
end if;
END zxb_fy;
Oracle动态分页sp
于 2023-04-26 17:19:16 首次发布
该存储过程创建了一个名为zxb_fy的函数,用于处理特定表的分页和自定义字段的累加计算。它首先检查和删除可能存在的临时表,然后创建并填充这些临时表,进行累加计算并将结果存储在结果表中。最后,以游标的形式返回结果,并在完成后删除临时表。
摘要由CSDN通过智能技术生成