引言
- 在开发中经常出现需要将表的某个字段的值修改,又怕影响其关联表,这时需要对表中的字段值进行评估,由于表的数量很多,于是可以采用SQL语句将某个特定值在所有表中哪个字段出现的位置查出来,进行评估。这篇文章将讲解Oracle数据库如何实现该功能,如果您使用的数据库是MySQL可以查看这篇文章:MySQL–查出某个特定值在哪些表的哪些字段中出现,并将结果存入新表
具体方法
- 先创建一个表SEARCH_VALUE以保存查出的数据
CREATE TABLE OMS_NJ.SEARCH_VALUE (
ID VARCHAR2(100),
TABLE_NAME VARCHAR2(100) NULL,
COLUMN_NAME VARCHAR2(100) NULL,
SEARCH_VAL VARCHAR2(100) NULL,
CONSTRAINT SEARCH_VALUE_PK PRIMARY KEY (ID)
)
TABLESPACE USERS;
- 上述SQL语句中,id为表的主键,table_name为查询到的数据库表名,column_name为表中的列名,search_val为本次查询的值。
- 接下来是重头戏,采用SQL语句将某个特定值在所有表中哪个字段出现的位置查出来并存入上述的SEARCH_VALUE表中。
DECLARE
value varchar2(100);
insertSql varchar(200);
checkSql varchar(200);
checkNum number;
dbValue varchar(100) ;
BEGIN
DBMS_OUTPUT.ENABLE(buffer_size => null);
value := 'admin' ;
dbValue := 'myDB';
FOR tn IN (SELECT TABLE_NAME FROM all_tables WHERE OWNER = dbValue ) LOOP
IF instr(tn.TABLE_NAME,'ACT_') <> 1
THEN
DBMS_OUTPUT.PUT_LINE(instr(tn.TABLE_NAME,'ACT_'));
FOR cn IN (SELECT column_name FROM user_tab_columns WHERE table_name = tn.TABLE_NAME) LOOP
checkSql := 'SELECT COUNT(*) FROM ' || tn.TABLE_NAME || ' WHERE "' || cn.column_name || '"' || ' LIKE ' || '''%' || value || '%'' AND ROWNUM = 1';
EXECUTE IMMEDIATE (checkSql) INTO checkNum;
DBMS_OUTPUT.PUT_LINE(checkNum);
IF checkNum <> 0 THEN
BEGIN
insertSql := 'INSERT INTO SEARCH_VALUE(ID,TABLE_NAME,COLUMN_NAME,SEARCH_VAL) VALUES(''' || to_char(CURRENT_TIMESTAMP(),'YYYY-MM-DD HH24:MI:SS.ff3') || ''',''' || tn.TABLE_NAME || ''',''' || cn.column_name || ''',''' || value || ''')';
DBMS_OUTPUT.PUT_LINE(insertSql);
EXECUTE IMMEDIATE (insertSql);
END;
END IF;
END LOOP;
END IF;
END LOOP;
END;
- 上述SQL语句比较长,下面做要点分析
- 取出数据库中所有的表的名称
SELECT TABLE_NAME FROM all_tables WHERE OWNER = '数据库的名称'
- 取出某张表中的所有字段
SELECT column_name FROM user_tab_columns WHERE table_name = '表的名称'
- 通过两次for循环,利用count()聚合函数判断某表某字段是否存在要查询的特定值。将结果存入checkNum中,若查出checkNum不为零,即存在特定值,则将该条记录插入到SEARCH_VALUE表中,此处插入时,将插入时的时间作为SEARCH_VALUE表的id。
EXECUTE IMMEDIATE (checkSql) INTO checkNum;
结果