请编写一存储过程:
1、过程名:proc_data_count;
2、输入参数 table_name(表名);
3、实现功能:统计某张表的总行数、空值数量、数据字段的(最大值、最小值、平均值、正数合计
负数合计、合计),并将统计结果输出到表 Data_count_table 中。
存储过程1
create or replace procedure proc_data_count1(table_name in varchar2) is
--过程名:proc_data_count;
--输入参数 table_name(表名);
--实现功能:统计某张表的总行数、空值数量、数据字段的(最大值、最小值、平均值、正数合计
--负数合计、合计),并将统计结果输出到表 Data_count_table 中。
--假设Data_count_table表中的字段与查询出的字段完全相符
--以学生成绩表为例,其中学号为char且为主键,课程号为char且为主键,成绩为number且有正负数,学分为number且null
strSQL varchar2(100);
countNum Data_count_table.total%type := 0;
nullNum Data_count_table.null_num%type := 0;
maxNum Data_count_table.max%type := 0;
minNum Data_count_table.min%type := 0;
avgNum Data_count_table.avg%type := 0;
plusNum Data_count_table.pos_num%type := 0;
minusNum Data_count_table.neg_num%type := 0;
totalNum Data_count_table.sum%type := 0;
tempNum number := 0;
tempName varchar2(20);
cursor cur_column_name(tname varchar2) is
select column_name from all_tab_columns where table_name = upper(tname);
cursor cur_data_type(tname varchar2) is
select column_name,data_type from all_tab_columns where table_name = upper(tname);
begin
--清空data_count_table中的数据
execute immediate 'delete from data_count_table';
--计算表中的总行数,结果放入Data_count_table
strSQL := 'select count(*) from ' || table_name;
--dbms_output.put_line(strSQL);
--open stu_cv for strSQL;
execute immediate strSQL into countNum;
--fetch stu_cv into countNum;
--dbms_output.put_line(countNum);
--insert into data_count_table(countNum) values(countNum);
--------------------------------------------
--计算表中的null数,结果放入Data_count_table
for rec in cur_column_name(table_name) loop
strsql := 'select count(*) from ' || upper(table_name) || ' where ' || rec.column_name || ' is null';
--dbms_output.put_line(strsql);
execute immediate strsql into tempNum;
--dbms_output.put_line(tempNum);
nullNum := nullNum + tempNum;
--dbms_output.put_line(nullNum);
end loop;
--insert into data_count_table(nullNum) values(nullNum);
--------------------------------------------------------------------------------------
--数据字段的(最大值、最小值、平均值、正数合计
--负数合计、合计),统计结果放入Data_count_table。
for rec in cur_data_type(table_name) loop
if rec.data_type = 'NUMBER' then
tempName := rec.column_name;
--计算表中数据字段的最大值,结果放入Data_count_table
strsql := 'select max(' || upper(tempName) || ') from ' || upper(table_name);
--dbms_output.put_line(strsql);
execute immediate strSQL into maxNum;
--dbms_output.put_line(maxNum);
--dbms_output.put_line(rec.data_type);
--计算表中数据字段的最小值,结果放入Data_count_table
strSQL := 'select min(' || upper(tempName) || ') from ' || upper(table_name);
execute immediate strSQL into minNum;
--dbms_output.put_line(minNum);
--计算表中数据字段的平均值,结果放入Data_count_table
strSQL := 'select avg(' || upper(tempName) || ') from ' || upper(table_name);
execute immediate strSQL into avgNum;
--dbms_output.put_line(avgNum);
--计算表中数据字段的合计,结果放入Data_count_table
strSQL := 'select sum(' || upper(tempName) || ') from ' || upper(table_name);
execute immediate strSQL into totalNum;
--dbms_output.put_line(totalNum);
--计算表中数据字段的正数合计,结果放入Data_count_table
strSQL := 'select count(*) from ' || upper(table_name) || ' where nvl(' || rec.column_name || ',0) >= 0';
--dbms_output.put_line(strSQL);
execute immediate strSQL into tempNum;
plusNum := plusNum + tempNum;
--dbms_output.put_line(plusNum);
--计算表中数据字段的负数合计,结果放入Data_count_table
strSQL := 'select count(*) from ' || upper(table_name) || ' where nvl(' || rec.column_name || ',0) < 0';
execute immediate strSQL into tempNum;
minusNum := minusNum + tempNum;
--dbms_output.put_line(minusNum);
end if;
end loop;
--插入数据到data_count_table
insert into data_count_table values(countNum,maxNum,minNum,avgNum,totalNum,plusNum,minusNum,nullNum);
end proc_data_count1;
存储过程2
create or replace procedure proc_data_count(ptable varchar2)
as
type temp_cur is ref cursor;
temp temp_cur;
num_null number;
num_total number;
total varchar2(500);
fname varchar2(500);
filename varchar2(500);
v_total data_count_table.total%type;
v_max data_count_table.max%type;
v_min data_count_table.min%type;
v_avg data_count_table.avg%type;
v_sum data_count_table.sum%type;
v_pos_num data_count_table.pos_num%type;
v_neg_num data_count_table.neg_num%type;
v_null_num data_count_table.null_num%type;
cursor cur(tname varchar2) is
select column_name, data_type
from all_tab_columns
where table_name = upper(tname);
begin
execute immediate 'truncate table data_count_table';
total := 'select count(1) from ' || ptable;
execute immediate total
into v_total;
--计算表中数据的总行数
for rec in cur(ptable) loop
--取字段
num_null := 0;
num_total := 0;
v_max := 0;
v_min := 0;
v_avg := 0;
v_sum := 0;
v_pos_num := 0;
v_neg_num := 0;
v_null_num := 0;
filename := rec.column_name;
open temp for 'select ' || filename || ' from ' || ptable;
loop
fetch temp
into fname;
exit when temp%notfound;
--取字段中数据
if fname is null then
v_null_num := v_null_num + 1;
end if;
--计算该表中NULL个数
if rec.data_type <> 'NUMBER' then
goto output;
end if;
--当类型为number时,计算表中的数据数据字段的数据的正数和负数的个数、总和、最大值和最小值
------------------------------------
if fname is null then
num_null := num_null + 1;
end if;
num_total := num_total + 1;
if num_total = 1 then
v_max := fname;
v_min := fname;
end if;
if fname is not null then
v_sum := v_sum + fname;
case
when fname > 0 then
v_pos_num := v_pos_num + 1;
when fname < 0 then
v_neg_num := v_neg_num + 1;
else
null;
end case;
case
when v_max < fname then
v_max := fname;
when v_min > fname then
v_min := fname;
else
null;
end case;
end if;
--------------------------------------
end loop;
v_avg := v_sum / (num_total - num_null);
--计算平均值
insert into data_count_table
values
(v_total,
v_max,
v_min,
v_avg,
v_sum,
v_pos_num,
v_neg_num,
v_null_num);
close temp;
<<output>>
null;
end loop;
end proc_data_count;
小子,谢谢大家了!
1、过程名:proc_data_count;
2、输入参数 table_name(表名);
3、实现功能:统计某张表的总行数、空值数量、数据字段的(最大值、最小值、平均值、正数合计
负数合计、合计),并将统计结果输出到表 Data_count_table 中。
存储过程1
create or replace procedure proc_data_count1(table_name in varchar2) is
--过程名:proc_data_count;
--输入参数 table_name(表名);
--实现功能:统计某张表的总行数、空值数量、数据字段的(最大值、最小值、平均值、正数合计
--负数合计、合计),并将统计结果输出到表 Data_count_table 中。
--假设Data_count_table表中的字段与查询出的字段完全相符
--以学生成绩表为例,其中学号为char且为主键,课程号为char且为主键,成绩为number且有正负数,学分为number且null
strSQL varchar2(100);
countNum Data_count_table.total%type := 0;
nullNum Data_count_table.null_num%type := 0;
maxNum Data_count_table.max%type := 0;
minNum Data_count_table.min%type := 0;
avgNum Data_count_table.avg%type := 0;
plusNum Data_count_table.pos_num%type := 0;
minusNum Data_count_table.neg_num%type := 0;
totalNum Data_count_table.sum%type := 0;
tempNum number := 0;
tempName varchar2(20);
cursor cur_column_name(tname varchar2) is
select column_name from all_tab_columns where table_name = upper(tname);
cursor cur_data_type(tname varchar2) is
select column_name,data_type from all_tab_columns where table_name = upper(tname);
begin
--清空data_count_table中的数据
execute immediate 'delete from data_count_table';
--计算表中的总行数,结果放入Data_count_table
strSQL := 'select count(*) from ' || table_name;
--dbms_output.put_line(strSQL);
--open stu_cv for strSQL;
execute immediate strSQL into countNum;
--fetch stu_cv into countNum;
--dbms_output.put_line(countNum);
--insert into data_count_table(countNum) values(countNum);
--------------------------------------------
--计算表中的null数,结果放入Data_count_table
for rec in cur_column_name(table_name) loop
strsql := 'select count(*) from ' || upper(table_name) || ' where ' || rec.column_name || ' is null';
--dbms_output.put_line(strsql);
execute immediate strsql into tempNum;
--dbms_output.put_line(tempNum);
nullNum := nullNum + tempNum;
--dbms_output.put_line(nullNum);
end loop;
--insert into data_count_table(nullNum) values(nullNum);
--------------------------------------------------------------------------------------
--数据字段的(最大值、最小值、平均值、正数合计
--负数合计、合计),统计结果放入Data_count_table。
for rec in cur_data_type(table_name) loop
if rec.data_type = 'NUMBER' then
tempName := rec.column_name;
--计算表中数据字段的最大值,结果放入Data_count_table
strsql := 'select max(' || upper(tempName) || ') from ' || upper(table_name);
--dbms_output.put_line(strsql);
execute immediate strSQL into maxNum;
--dbms_output.put_line(maxNum);
--dbms_output.put_line(rec.data_type);
--计算表中数据字段的最小值,结果放入Data_count_table
strSQL := 'select min(' || upper(tempName) || ') from ' || upper(table_name);
execute immediate strSQL into minNum;
--dbms_output.put_line(minNum);
--计算表中数据字段的平均值,结果放入Data_count_table
strSQL := 'select avg(' || upper(tempName) || ') from ' || upper(table_name);
execute immediate strSQL into avgNum;
--dbms_output.put_line(avgNum);
--计算表中数据字段的合计,结果放入Data_count_table
strSQL := 'select sum(' || upper(tempName) || ') from ' || upper(table_name);
execute immediate strSQL into totalNum;
--dbms_output.put_line(totalNum);
--计算表中数据字段的正数合计,结果放入Data_count_table
strSQL := 'select count(*) from ' || upper(table_name) || ' where nvl(' || rec.column_name || ',0) >= 0';
--dbms_output.put_line(strSQL);
execute immediate strSQL into tempNum;
plusNum := plusNum + tempNum;
--dbms_output.put_line(plusNum);
--计算表中数据字段的负数合计,结果放入Data_count_table
strSQL := 'select count(*) from ' || upper(table_name) || ' where nvl(' || rec.column_name || ',0) < 0';
execute immediate strSQL into tempNum;
minusNum := minusNum + tempNum;
--dbms_output.put_line(minusNum);
end if;
end loop;
--插入数据到data_count_table
insert into data_count_table values(countNum,maxNum,minNum,avgNum,totalNum,plusNum,minusNum,nullNum);
end proc_data_count1;
存储过程2
create or replace procedure proc_data_count(ptable varchar2)
as
type temp_cur is ref cursor;
temp temp_cur;
num_null number;
num_total number;
total varchar2(500);
fname varchar2(500);
filename varchar2(500);
v_total data_count_table.total%type;
v_max data_count_table.max%type;
v_min data_count_table.min%type;
v_avg data_count_table.avg%type;
v_sum data_count_table.sum%type;
v_pos_num data_count_table.pos_num%type;
v_neg_num data_count_table.neg_num%type;
v_null_num data_count_table.null_num%type;
cursor cur(tname varchar2) is
select column_name, data_type
from all_tab_columns
where table_name = upper(tname);
begin
execute immediate 'truncate table data_count_table';
total := 'select count(1) from ' || ptable;
execute immediate total
into v_total;
--计算表中数据的总行数
for rec in cur(ptable) loop
--取字段
num_null := 0;
num_total := 0;
v_max := 0;
v_min := 0;
v_avg := 0;
v_sum := 0;
v_pos_num := 0;
v_neg_num := 0;
v_null_num := 0;
filename := rec.column_name;
open temp for 'select ' || filename || ' from ' || ptable;
loop
fetch temp
into fname;
exit when temp%notfound;
--取字段中数据
if fname is null then
v_null_num := v_null_num + 1;
end if;
--计算该表中NULL个数
if rec.data_type <> 'NUMBER' then
goto output;
end if;
--当类型为number时,计算表中的数据数据字段的数据的正数和负数的个数、总和、最大值和最小值
------------------------------------
if fname is null then
num_null := num_null + 1;
end if;
num_total := num_total + 1;
if num_total = 1 then
v_max := fname;
v_min := fname;
end if;
if fname is not null then
v_sum := v_sum + fname;
case
when fname > 0 then
v_pos_num := v_pos_num + 1;
when fname < 0 then
v_neg_num := v_neg_num + 1;
else
null;
end case;
case
when v_max < fname then
v_max := fname;
when v_min > fname then
v_min := fname;
else
null;
end case;
end if;
--------------------------------------
end loop;
v_avg := v_sum / (num_total - num_null);
--计算平均值
insert into data_count_table
values
(v_total,
v_max,
v_min,
v_avg,
v_sum,
v_pos_num,
v_neg_num,
v_null_num);
close temp;
<<output>>
null;
end loop;
end proc_data_count;
小子,谢谢大家了!
if rec.data_type = 'NUMBER' then
tempName := rec.column_name;
--计算表中数据字段的最大值,结果放入Data_count_table
strsql := 'select Max(' || upper(tempName) || ') nMax, ' || -- 最大
'Min(' || upper(tempName) || ') nMin, ' || -- 最小
'Avg(' || upper(tempName) || ') nAvg, ' || -- 平均
'Sum(' || upper(tempName) || ') nSum, ' || -- 合计
'Sum(Case When Nvl(' || rec.column_name || ', 0) >= 0 then 1 else 0 End) nPositive, ' || -- 正数记录数
'Sum(Case When Nvl(' || rec.column_name || ', 0) >= 0 then 0 else 1 End) nNegative, ' || -- 负数记录数
'Sum(Case When ' || rec.column_name || ' is null then 1 else 0 End) nNull, ' || -- 空值记录数
'Sum(Case When Nvl(' || rec.column_name || ', 0) >= 0 then ' || ' Nvl(' || rec.column_name || ', 0)' || ' else 0 End) nPositiveValue, ' || -- 正数合计
'Sum(Case When Nvl(' || rec.column_name || ', 0) < 0 then ' || ' Nvl(' || rec.column_name || ', 0)' || ' else 0 End) nNegativeValue , ' || -- 负数数合计
'Count(1) nTotal ' || -- 总记录数
' from ' || upper(table_name);
execute immediate strSQL into nMax, nMin, nAvg, nSum, nPositive, nNegtive, nNull, nnPositiveValue, nNegativeValue, nTotal; end if;
--可以将所有计算放入一个语句,避免多次扫描
--试试下面这个与第二种方式的对比
create or replace procedure proc_data_count3(table_name in varchar2) is
strSQL varchar2(4000);
countNum Data_count_table.total%type := 0;
nullNum Data_count_table.null_num%type := 0;
maxNum Data_count_table.max%type := 0;
minNum Data_count_table.min%type := 0;
avgNum Data_count_table.avg%type := 0;
plusNum Data_count_table.pos_num%type := 0;
minusNum Data_count_table.neg_num%type := 0;
totalNum Data_count_table.sum%type := 0;
tempName varchar2(20);
cursor cur_data_type(tname varchar2) is
select column_name,data_type from all_tab_columns where table_name = upper(tname);
begin
--清空data_count_table中的数据
execute immediate 'delete from data_count_table';
--计算表中的总行数,结果放入Data_count_table
strSQL := 'select count(*) from ' || table_name;
--dbms_output.put_line(strSQL);
execute immediate strSQL into countNum;
for rec in cur_data_type(table_name) loop
if rec.data_type = 'NUMBER' then
tempName := rec.column_name; --计算表中数据字段的最大值,结果放入Data_count_table
strsql := 'select count(case when '||rec.column_name||' is null then 1 end), max('||
upper(tempName) || '),min(' || upper(tempName) || '),avg(' ||
upper(tempName) || '),sum(' || upper(tempName) || '),count(case when '||
rec.column_name||' >=0 then 1 end),'||
'count(case when '||rec.column_name||' <=0 then 1 end)'
||' from ' || upper(table_name);
execute immediate strSQL into nullNum,maxNum,minNum,avgNum,totalNum,plusNum,minusNum;
insert into data_count_table
values(countNum,maxNum,minNum,avgNum,totalNum,plusNum,minusNum,nullNum);
end if;
end loop;
commit;
end proc_data_count3;
/
--测试
SQL> exec proc_data_count('dba_objects');PL/SQL procedure successfully completed
Executed in 3.375 seconds
SQL> exec proc_data_count3('dba_objects');
PL/SQL procedure successfully completed
Executed in 0.344 seconds
SQL>
'select count(*) from ' || table_name;
这个也可以合并到下面的语句中。data_count_table 表应该加个columnname字段,用来区分多条记录
临时变量可以不要,动态语句前面直接加上'insert into .. '即可
多使用类似如下 的动态语句
execute immediate 'insert into data_count_table
values
(:1,
:2,
:3,
:4,
:5,
:6,
:7,
:8)' using v_total,
v_max,
v_min,
v_avg,
v_sum,
v_pos_num,
v_neg_num,
v_null_num;