破解以“机器码”命名数据库表字段的维护困局

破解以“机器码”命名数据库表字段的维护困局

按照数据库设计规范，数据库表、字段名称都应当以有特征含义的单词或缩写组成，但是有些软件开发商会由于种种原因不遵循这个规范，更有甚者竟然以“AA01”、“AK000”诸如此类的“机器码”来命名，使用信息系统的维护人员编写一条查询语句必需将字典放在旁边反复查看才能完成，给信息系统维护人员，特别是数据库管理人员带来很大的不方便。笔者所在单位的一个社会保障信息管理系统数据库表及字段就是以这种“机器码”来命名的。

1 基本情况

信息系统数据保存在市社保局，在市社保局有一个Oracle 10G的数据库（下文称市级数据库），下属各县（市）社保局通过另一个用户名建立的Synonyms（同义词）来访问市级数据库对应的表。此用户权限有限，无足够权限创建访问相应表视图的权限。

2 具体破解方法

2.1 在县（市）社保局能访问该数据库的服务器或普通电脑上安装Oracle 11G数据库（10G应该也行，笔者是以11G版本进行测试），如果已安装则跳过。注意要确保此数据库（下文称本地数据库）所在服务器或电脑应当能访问市数据库。

2.2 在本地数据库中新建一个用户，如Test，并授予DBA权限。

2.3 在本地数据库服务器上创建一个能访问市级数据库的服务命名，如：extServer。

2.4 在PL/SQLDeveloper中以新建的用户名登录本地数据库，创建一个新的Database link，这样就能通过本地数据库访问市级数据库了。创建语句：

create database link myDblink--Database link名称，可以自己改

connect to extUser --extUser为登录市级数据库的用户名

identified by password --password为登录市级数据库的密码

using 'extServer' --上一步创建能连接市级数据库的服务命名

Database link创建成功后，就可以实现跨数据库访问了，如果要访问市级数据库，只需在市级数据库的同义词或表名后加上@myDblink就行了，如查询市级数据库的“AA00”表，就是：select *from AA00@myDblink。

2.5 在本地数据库创建三个表：qzsb_table（市级数据库表信息）、qzsb_table_col（市级数据库字段信息）、qzsb_view_sql（访问市级数据库的视图创建语句表），创建方法有二种，一种是通过以下语句直接创建：

create table QZSB_TABLE

(

TABLE_NAME VARCHAR2(30) not null, --市级数据库表名称

COMMENTS  VARCHAR2(4000), --市级数据库表注释，要进行必要的修改，作为新视图名称的一部分

ALIASTABLE VARCHAR2(30) not null, --市级数据库表按自己的习惯命名

COMMENTOLD       VARCHAR2(4000)--市级数据库表注释，供以后查询

)；

create tableQZSB_TABLE_COL

(

TABLE_NAME VARCHAR2(30) not null, --市级数据库表名称

COLUMN_NAME VARCHAR2(30) not null, --市级数据库字段名称

COMMENTS   VARCHAR2(4000), --市级数据库字段注释，要进行必要的修改，作为新视图字段名称的一部分

COMMENTOLD       VARCHAR2(4000)--市级数据库字段注释，供以后查询

);

create tableQZSB_VIEW_SQL

(

SQL CLOB

);

另外一种方法是通过查询市级数据库相关视图来创建：

create table qzsb_tableas

select table_name,comments, table_name aliasTable, comments commentOld

from all_tab_comments@ myDblink

where owner = '拥有表的用户名' and table_type ='TABLE'

order by table_type,table_name；

create tableqzsb_table_col as

select table_name,column_name, comments, comments commentOld

from all_col_comments@ myDblink

where owner = '拥有表的用户名'

order by table_name,column_name；

采用此方法的前提是开发商对表及字段做了注释，如果没有注释，可通过将开发商提供（这个一般会提供）或自己整理的电子表格导入到对应的表中。作者就是通过查询相关视图来创建市级数据库表、字段信息表。

2.6 对注释进行必要的处理。如果对系统表结构比较了解，或者想将表、字段完全按照你自己的习惯命名，那么可以手工对市级数据库表信息表aliasTable字段和市级数据库字段信息表aliasColumn字段按你的命名进行修改，这种方法一般工作量较大。另外一种方法就是用原来“机器码”字段名加中文注释作为新的字段名，这样有个好处，就是将用原来“机器码”写的查询语句翻译成新的查询语句非常容易。对注释的处理主要是删除一些特殊字符，如标点符号、空格等字符，这个在你实际应用时可能还会有其他的特殊字符存在，可以用类似的方法进行处理，处理SQL语句如下：

update qzsb_table

set comments =regexp_replace(comments, '[=＝+-\/\\\,，;；“”""''、-－:：\(\) ]');

update qzsb_table_col

set comments =regexp_replace(comments, '[=＝+-\/\\\,，;；“”""''、-－:：\(\) ]');

2.7 编写存储过程，自动生成创建我们需要的视图创建语句。

create or replaceprocedure CreateView IS

sqlStr VARCHAR2(5000);

dh char(1);

v_CLOB clob;

amount number;

offset number;

BEGIN

BEGIN

FOR rows IN (SELECT * FROM qzsb_table wherecomments is not null) LOOP

sqlStr := 'create or replace view ' ||

'v_' || rows.table_name ||

substr(trim(nvl(rows.comments, '')), 1,

(30 - length(rows.table_name)- 2)/2)

|| ' as select ';

dh := ' ';

for rowsCol in (select * fromqzsb_table_col where

qzsb_table_col.table_name =rows.table_name) loop

sqlStr := sqlStr || dh ||rowsCol.Column_Name || ' ' ||

rowsCol.Column_Name ||

substr(trim(nvl(rowsCol.Comments, '')), 1,

          (30-length(rowsCol.Column_Name))/2);

dh := ',';

end loop;

sqlStr := sqlStr || ' from ' ||rows.table_name || '@ myDblink ' || ';' || chr(10);

--更新CLOB

select sql into v_CLOB from  qzsb_view_sql where rownum = 1 for update;

offset :=dbms_lob.getlength(v_CLOB)+1;

amount :=length(sqlStr);

dbms_lob.write(v_CLOB,amount,offset,sqlStr);

commit;

END LOOP;

EXCEPTION

WHEN OTHERS THEN

RAISE_APPLICATION_ERROR(-20000,  SQLERRM);

END;

end CreateView;

2.8 为访问市级数据库的视图创建语句表插入一行数据：

insert intoqzsb_view_sql values ('--创建视图SQL语句')

2.9 执行存储过程

在PL/SQL Developer中，找到上面创建的存储过程：CREATEVIEW，在右键弹出菜单中选择：Test，再执行一下，在qzsb_view_sql的sql字段中就生成了所有的创建语句。接下来就是将自动生成的创建语句拷贝、粘贴到Sql窗口中执行了。

为什么不直接在存储过程中创建视图，而只是生成创建视图的语句呢，这是因为在存储过程中直接创建需要显式授权，所以会出现没有足够权限的错误而无法完成。

2.10 再提供一个删除当前用户所有视图的存储过程，这在你想删除新建视图并重新生成时会有用。

create or replaceprocedure DeleteAllView IS

sqlStr VARCHAR2(5000);

--删除所有视图

BEGIN

BEGIN

return;

FOR rows IN (select view_name fromuser_views) LOOP

sqlStr := 'drop view ' || rows.view_name;

EXECUTE IMMEDIATE sqlStr;

END LOOP;

EXCEPTION

WHEN OTHERS THEN

RAISE_APPLICATION_ERROR(-20000, sqlStr ||'  ' || SQLERRM);

END;

end DeleteAllView;

3 结果                                

视图创建成功后，我们可以在PL/SQL Developer的参数设置中把Code Assistant（代码助手）打开，这样在编写查询语句时，就会自动弹出窗口供用户选取了。

  

（

表选择窗口，视图名称格式为： v_ + 市级数据库中的表名 + 中文注释）

（字段选择窗口，字段名称格式为：市级数据库中的表字段名 + 中文注释）

至此，就实现了把以“机器码”命名的数据库表、字段转换成以更加友好方式呈现给管理人员的要求，大大提高了编写查询语句的效率，维护人员会更加容易和乐于接受。

4 建议

实际上，这种以“机器码”来命名数据库表和字段的系统应当说是“凤毛麟角”，至少笔者是第一次碰到，不幸碰上的话，肯定是一种悲哀，这种系统对于后续的维护难度非常大。强烈建议有关部门在做信息化建设决策时，把数据库的命名方式是否符合相应的规范作为一个重要的内容，为以后的维护工作减少不必要的麻烦。