ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 利用dbms_metadata.get_ddl生成对应定义语句

利用dbms_metadata.get_ddl生成对应定义语句

原创 Linux操作系统 作者:regonly1 时间:2009-06-22 14:32:07 0 删除 编辑

这个话题不仅在网上已经被重复过不知道多少次了,在我的这个blog上也已
经是第二次来实现这个功能了。第一次的算法比较土,就是找到指定的关键字
就换行,如:tablespace/storage等。这种方式很大的缺点就是如果是表定义
语句,而且是有很多字段的(超过255字符数)的,就会出现错误。而且这个
错误还没有很好的办法来解决。
目前从dbms_metadata.get_ddl中获取ddl语句最麻烦的就是如何在合适的
位置换行。如果一次输出肯定会超出255的每行最大输出字符数,会抛出异常。
如果按照某个空格截断,其实也很不好判断到底在哪里截。而且截出来的效
果100%的情况是非常差,也没什么可读性了。

这次实现的方式应该说是灵机一动想出来的。也不用很多的步骤。
目前发现的除了对最大字符数有限制外(4000字符),其他都可正常使用。
我测试输出了前面的254个字符,发现其实他本身的语句是有换行的。这就
给我提示。何不按他自己的换行符来实现分段截取呢。
Oracle中的换行符可通过chr(10)来定位:
v_pos := instr(v_result, chr(10), i);
依次找到v_pos和之前的v_pos(v_tpos),就可以定位两个换行符之间的一行了
        if ( v_tpos <> v_pos ) then
            v_temp := lower(substr(v_result, v_tpos, v_pos - v_tpos));
            v_tpos := v_pos;
        end if;

最新版本,支持超出4000字符范围的大结构定义(如package等):
create or replace function w_func_getobjectddl_lob(
                            object_type varchar2,
                            object_name varchar2)
return tbl_varchar2 pipelined  as
/*Created by Lyon@20090622*/
    v_result    varchar2(4000);
    v_temp      varchar2(2000);
    v_pos       pls_integer := 0;
    v_tpos      pls_integer := 0;
   
    v_clob      clob;
    v_bufsize   pls_integer := 4000;
    v_cloblen   pls_integer := 0;
    v_nextpos   pls_integer := 1;
    v_prevpos   pls_integer := 1;
    v_initcount pls_integer := 50;
    v_nextcount pls_integer := v_initcount;
    v_offset    pls_integer := 1;
begin
    dbms_output.enable(9999999999999);
    v_clob := dbms_metadata.get_ddl(object_type, object_name);
    v_cloblen := dbms_lob.getlength(v_clob);
    v_bufsize := dbms_lob.instr(v_clob, chr(10), 1, v_initcount);

    while v_nextpos <> 0 loop
        v_nextpos := dbms_lob.instr(v_clob, chr(10), 1, v_nextcount);
        v_bufsize := (case when v_nextpos = 0 then v_cloblen else v_nextpos end) - v_prevpos;

        dbms_lob.read(v_clob, v_bufsize, v_offset, v_result);

        v_result := v_result || chr(10);
        v_tpos := 0;
        for j in 1..v_bufsize loop
            v_pos := instr(v_result, chr(10), j);
            if ( v_tpos <> v_pos ) then
                v_temp := lower(substr(v_result, v_tpos + 1, v_pos - v_tpos - 1));
                pipe row(v_temp);
                v_tpos := v_pos;
            end if;
        end loop;
        v_prevpos   := v_nextpos;
        v_offset    := v_offset + v_bufsize;
        v_nextcount := v_nextcount + v_initcount;
    end loop;

    return;
exception when others then
    pipe row(sqlerrm);
    return;
end w_func_getobjectddl_lob;


下面是实现的原始函数,通过管道来返回指定表的定义语句:
create or replace function w_func_getobjectddl(
                            object_type varchar2,
                            object_name varchar2)
return tbl_varchar2 pipelined  as
/*Created by Lyon@20090622*/

    v_result  varchar2(4000);
    v_temp    varchar2(255);
    v_pos     pls_integer := 0;
    v_tpos    pls_integer := 0;
begin
    dbms_output.enable(9999999999999);
    v_result := dbms_metadata.get_ddl(object_type, object_name);
    for i in 1..length(v_result) loop
        v_pos := instr(v_result, chr(10), i);
        if ( v_tpos <> v_pos ) then
            v_temp := lower(substr(v_result, v_tpos, v_pos - v_tpos));
            pipe row(v_temp);
            v_tpos := v_pos;
        end if;
    end loop;
    return;
end;
这里需要有个数组类型来支持管道的返回信息:
create or replace type tbl_varchar2 as table of varchar2(255);
函数编译成功后,即可通过下面的方式来获取对象定义语句:
Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0
Connected as customer
 
SQL> select * from table(w_func_getobjectddl('TYPE', 'REC_INPUT_ASW'));
 
COLUMN_VALUE
--------------------------------------------------------------------------------
 
  create or replace type "customer"."rec_input_asw" as object(
 
 
    c_userinfoid varchar2(32),
 
 
    c_answerid varchar2(32),
 
 
    c_subjectid varchar2(32),
 
 
    c_optionid varchar2(32),
 
 
    c_continuation varchar2(1000) --答题内容
 
 
)
 
 
11 rows selected
 
SQL>
SQL> select * from table(w_func_getobjectddl('TABLE', 'TEST'));
 
COLUMN_VALUE
--------------------------------------------------------------------------------
 
  create table "customer"."test"
 
 
   ( "name" varchar2(255),
 
 
 "id" number(18,0)
 
 
   ) pctfree 10 pctused 40 initrans 1 maxtrans 255 nocompress logging
 
 
  storage(initial 65536 next 1048576 minextents 1 maxextents 2147483645
 
 
6 rows selected
 
SQL>

后面考虑继续优化这个函数功能。
下面又做了部分优化:
Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0
Connected as customer
 
SQL> select * from table(w_func_getobjectddl('TABLE', 'TEST'));
 
COLUMN_VALUE
--------------------------------------------------------------------------------
  create table "customer"."test"
   ( "name" varchar2(255),
 "id" number(18,0)
   ) pctfree 10 pctused 40 initrans 1 maxtrans 255 nocompress logging
  storage(initial 65536 next 1048576 minextents 1 maxextents 2147483645
  pctincrease 0 freelists 1 freelist groups 1 buffer_pool default)
  tablespace "fund_table"
 
9 rows selected
 
SQL> select * from table(w_func_getobjectddl('TYPE', 'REC_INPUT_ASW'));
 
COLUMN_VALUE
--------------------------------------------------------------------------------
  create or replace type "customer"."rec_input_asw" as object(
    c_userinfoid varchar2(32),
    c_answerid varchar2(32),
    c_subjectid varchar2(32),
    c_optionid varchar2(32),
    c_continuation varchar2(1000) --答题内容
)
 
11 rows selected
 
SQL>

代码内容:
create or replace function w_func_getobjectddl(
                            object_type varchar2,
                            object_name varchar2)
return tbl_varchar2 pipelined  as
/*Created by Lyon@20090622*/
    v_result  varchar2(4000);
    v_temp    varchar2(255);
    v_pos     pls_integer := 0;
    v_tpos    pls_integer := 0;
begin
    dbms_output.enable(9999999999999);
    v_result := dbms_metadata.get_ddl(object_type, object_name);
    for i in 1..length(v_result) loop
        v_pos := instr(v_result, chr(10), i);
        if ( v_tpos <> v_pos ) then
            prinfo('v_tpos: ' || v_tpos || ' -> v_pos: ' || v_pos);
            v_temp := lower(substr(v_result, v_tpos + 1, v_pos - v_tpos - 1));
            pipe row(v_temp);
            v_tpos := v_pos;
        end if;
    end loop;
    return;
end;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12932950/viewspace-607149/,如需转载,请注明出处,否则将追究法律责任。

上一篇: statspack的id问题
请登录后发表评论 登录
全部评论

注册时间:2008-05-10

  • 博文量
    257
  • 访问量
    1022079