环境:ORACLE 10G R2
要求:从系统表 USER_TAB_COLUMNS 里面可以取出 表名以及对应的字段名对每个字段做如下计算 LENGTH(COLUMN_NAME)-AVG(LENGTH(COLUMN_NAME)) INTO V_LENGTH_MORE
IF V_LENGTH_MORE >0
THEN
输出大于0的这一部分数据
特殊说明:这个不需要测试数据呵呵。只要有ORACLE 环境就可以写。我自己写的有问题。所以想请教一下高手们。
真诚求教。
麻烦高手写下完整语句。谢谢!
调试欢乐多
可以用
select length(column_name)-avg(length(column_name)over(partition by table_name) into ..
我框架写好了。麻烦您给改下。
CREATE OR REPLACE PROCEDURE CX_TAB_COL_LENGTH_AVG IS V_NOTNULLID_F T_SYS_SHUJUZLFX.NOTNULLID%TYPE;
V_TABLENAME_F T_SYS_SHUJUZLFX.TABLE_NAME%TYPE;
V_NOTNULLID_S T_SYS_SHUJUZLFX.NOTNULLID%TYPE;
V_TABLENAME_S T_SYS_SHUJUZLFX.TABLE_NAME%TYPE;
V_COLUMN_LENGTH VARCHAR2(100);
V_AVGLENGTH VARCHAR2(100);
V_AVG_MINUS_LENGTH NUMBER;
V_SQLSUBSTR VARCHAR2(4000); CURSOR GETCOLUMN_AVGLENGTH IS
SELECT NOTNULLID, TABLE_NAME, AVG(LENGTH(COLUMN_NAME))
FROM T_SYS_SHUJUZLFX
WHERE YXL <> 0
AND COLUMN_NAME IS NOT NULL
GROUP BY NOTNULLID, TABLE_NAME; CURSOR GETCOLUMN_LENGTH IS
SELECT NOTNULLID, TABLE_NAME, LENGTH(COLUMN_NAME)
FROM T_SYS_SHUJUZLFX
WHERE YXL <> 0
AND COLUMN_NAME IS NOT NULL;
--GROUP BY NOTNULLID, TABLE_NAME;BEGIN open GETCOLUMN_AVGLENGTH; loop
fetch GETCOLUMN_AVGLENGTH
into V_NOTNULLID_F, V_TABLENAME_F, V_AVGLENGTH;
exit when GETCOLUMN_AVGLENGTH%notfound;
open GETCOLUMN_LENGTH;
loop
fetch GETCOLUMN_LENGTH
into V_NOTNULLID_S, V_TABLENAME_S, V_COLUMN_LENGTH;
exit when GETCOLUMN_LENGTH%notfound;
SELECT V_COLUMN_LENGTH - V_AVGLENGTH
INTO V_AVG_MINUS_LENGTH
FROM DUAL;
IF V_AVG_MINUS_LENGTH < 0 THEN
EXIT;
ELSE
V_SQLSUBSTR := 'SELECT * FROM ' || V_TABLENAME_S || ';';
EXECUTE IMMEDIATE V_SQLSUBSTR;
END IF;
end loop;
close GETCOLUMN_LENGTH;
end loop;
close GETCOLUMN_AVGLENGTH;END CX_TAB_COL_LENGTH_AVG;
希望不要受我的思路的影响。最终实现就可以。
结果插入一张表里 或者 SELECT出来都可以。
麻烦贴下完整代码。
from (
select t.*,avg(t.DATA_LENGTH)over(partition by t.COLUMN_NAME ) avglength
from user_tab_columns t
-- where t.COLUMN_NAME=upper('colname')
)t
where t.DATA_LENGTH<>round(avglength)
from
(
select t.*,max(cc)over() maxcc
from (
select t.*,count(length(t.COLUMN_NAME))over(partition by length(t.COLUMN_NAME) ) cc
from user_tab_columns t
)t
)t
where t.cc<>maxcc把user_tab_columns换成你要处理的表,COLUMN_NAME换成你要处理的字段名
from (
select t.*,avg(length(t.COLUMN_NAME))over() avglen
from user_tab_columns t
)t
where length(t.COLUMN_NAME)>avglen
你的思路和我想的一样呵呵。如果值 length(t.COLUMN_NAME)>avglen
满足这个情况 输出这一行数据。