你可以把表的字段信息全部取出(从表col),
逐个字段循环:
if null then
if data_type=字符 then
更新为空格
end if;
if data_type=数字 then
...
end if;
if data_type=日期 then
...
end if;
end if;
大致是如此思路。
逐个字段循环:
if null then
if data_type=字符 then
更新为空格
end if;
if data_type=数字 then
...
end if;
if data_type=日期 then
...
end if;
end if;
大致是如此思路。
CREATE OR REPLACE PROCEDURE SCOTT.tabnonull (tablename IN varchar2)
as
type v_tempcursor is ref cursor;
v_cursor v_tempcursor;
colname varchar2(30);
coltype varchar2(50);
begin
open v_cursor for 'select column_name,data_type from user_tab_columns where table_name=''' || upper(tablename) || '''';
loop
fetch v_cursor into colname,coltype;
exit when v_cursor%notfound;
if coltype='NUMBER' THEN
execute immediate 'update ' || tablename || ' set ' || COLNAME || '=0 WHERE ' || COLNAME || ' IS NULL';
elsif coltype='VARCHAR2' then
execute immediate 'update ' || tablename || ' set ' || COLNAME || '='' '' WHERE ' || COLNAME || ' IS NULL';
elsif COLTYPE='DATE' then
execute immediate 'update ' || tablename || ' set ' || COLNAME || '=SYSDATE WHERE ' || COLNAME || ' IS NULL';
END IF;
end loop;
commit;
end;
/
CREATE OR REPLACE PROCEDURE settabnonull (var_ownername in varchar2,var_tablename IN varchar2)
as
type v_tempcur is ref cursor;
v_cursor v_tempcur;
colname varchar2(30);
coltype varchar2(30);
sSqlexe varchar2(200);
begin
open v_cursor for 'select column_name,data_type from sys.all_tab_columns where owner = ''' || upper(var_ownername) || '''' || ' and ' || 'table_name=''' || upper(var_tablename) || '''';
loop
fetch v_cursor into colname,coltype;
exit when v_cursor%notfound;
if coltype='NUMBER' THEN
sSqlexe := 'update ' || var_ownername || '.' || var_tablename || ' set ' || COLNAME || '=0 WHERE ' || COLNAME || ' IS NULL';
elsif coltype='VARCHAR2' then
sSqlexe := 'update ' || var_ownername || '.' || var_tablename || ' set ' || COLNAME || '='' '' WHERE ' || COLNAME || ' IS NULL';
elsif COLTYPE='DATE' then
sSqlexe := 'update ' || var_ownername || '.' || var_tablename || ' set ' || COLNAME || '=SYSDATE WHERE ' || COLNAME || ' IS NULL';
END IF;
execute immediate sSqlexe;
commit;
end loop;
end;
/
open v_cursor for 'select column_name,data_type from user_tab_columns where table_name=''' || upper(tablename) || data_type 这个是字段名啦
用哪个函数可以求字段的类型拉,
名称 空? 类型
----------------------------------------- -------- ---------------
TABLE_NAME NOT NULL VARCHAR2(30)
COLUMN_NAME NOT NULL VARCHAR2(30)
DATA_TYPE VARCHAR2(106)
DATA_TYPE_MOD VARCHAR2(3)
DATA_TYPE_OWNER VARCHAR2(30)
DATA_LENGTH NOT NULL NUMBER
DATA_PRECISION NUMBER
DATA_SCALE NUMBER
NULLABLE VARCHAR2(1)
COLUMN_ID NOT NULL NUMBER
DEFAULT_LENGTH NUMBER
DATA_DEFAULT LONG
NUM_DISTINCT NUMBER
LOW_VALUE RAW(32)
HIGH_VALUE RAW(32)
DENSITY NUMBER
NUM_NULLS NUMBER
NUM_BUCKETS NUMBER
LAST_ANALYZED DATE
SAMPLE_SIZE NUMBER
CHARACTER_SET_NAME VARCHAR2(44)
CHAR_COL_DECL_LENGTH NUMBER
GLOBAL_STATS VARCHAR2(3)
USER_STATS VARCHAR2(3)
AVG_COL_LEN NUMBER