Oracle QuickSQL和标签框架

APEX QuickSQL实用程序具有 “标签框架” 标志。

任何人都有关于何时以及如何使用它的详细说明？

标签框架是一种机制，用于支持您的应用程序记录对表的 “标签” 的需求。

例如，如果您正在为您的应用程序构建一个bug数据库供开发人员使用，因为他们记录了一个bug，他们可能会记录一些有用的标签。该框架为您实现该框架提供了坚实的基础。它将生成以下内容

-- tag framework
create table tags (
    id                    number not null primary key,
    tag                   varchar2(255) not null enable,
    content_pk            number,
    content_table         varchar2(128),
    created               timestamp with local time zone not null,
    created_by            varchar2(255) not null,
    updated               timestamp with local time zone,
    updated_by            varchar2(255) )
;

create or replace trigger tags_biu
before insert or update on tags
for each row
begin
   if inserting then 
      if :new.id is null then 
        :new.id := to_number(sys_guid(),'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
      end if;
      :new.created := localtimestamp;
      :new.created_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
   end if; 
   if updating then 
      :new.created := localtimestamp; 
      :new.created_by := coalesce(sys_context('APEX$SESSION','APP_USER'),user);
   end if; 
end tags_biu; 
/

create table tags_tsum (
    tag                    varchar2(255),
    content_table          varchar2(128),
    tag_count              number,
    constraint tags_tspk primary key (tag,content_table) )
;

create table tags_sum (
    tag                    varchar2(255),
    tag_count              number,
    constraint tags_spk primary key (tag) )
;

create or replace procedure tags_sync (
    p_new_tags          in varchar2,
    p_old_tags          in varchar2,
    p_content_table     in varchar2,
    p_content_pk        in number )
as
    type tags is table of varchar2(255) index by varchar2(255);
    type tag_values is table of varchar2(32767) index by binary_integer;
    l_new_tags_a    tags;
    l_old_tags_a    tags;
    l_new_tags      tag_values;
    l_old_tags      tag_values;
    l_merge_tags    tag_values;
    l_dummy_tag     varchar2(255);
    i               integer;
    function string_to_table (
        str    in varchar2,
        sep    in varchar2 default ':')
        return tag_values
    is
        temp         tag_values;
        l_str        varchar2(32767) := str;
        pos          pls_integer;
        i            pls_integer := 1;
        l_sep_length pls_integer := length(sep);
    begin
        if str is null or sep is null then
            return temp;
        end if;
        if substr( l_str, 1, l_sep_length ) = sep then
            l_str := substr( l_str, l_sep_length + 1 );
        end if;
        if substr( l_str, length( l_str ) - l_sep_length + 1 ) = sep then
            l_str := substr( l_str, 1, length( l_str ) - l_sep_length );
        end if;
        loop
            pos := instr( l_str, sep );
            exit when nvl(pos,0) = 0;
            temp(i) := substr( l_str, 1, pos-1 );
            l_str := substr( l_str, pos + l_sep_length );
            i := i + 1;
        end loop;
        temp(i) := trim(l_str);
        return temp;
    exception when others then return temp;
    end;
begin
    l_old_tags := string_to_table(p_old_tags,',');
    l_new_tags := string_to_table(p_new_tags,',');
    if l_old_tags.count > 0 then --do inserts and deletes
        --build the associative arrays
        for i in 1..l_old_tags.count loop
            l_old_tags_a(l_old_tags(i)) := l_old_tags(i);
        end loop;
        for i in 1..l_new_tags.count loop
            l_new_tags_a(l_new_tags(i)) := l_new_tags(i);
        end loop;
        --do the inserts
        for i in 1..l_new_tags.count loop
            begin
                l_dummy_tag := l_old_tags_a(l_new_tags(i));
            exception when no_data_found then
                insert into tags (tag, content_pk, content_table )
                values (trim(l_new_tags(i)), p_content_pk, p_content_table );
                l_merge_tags(l_merge_tags.count + 1) := l_new_tags(i);
            end;
        end loop;
        --do the deletes
        for i in 1..l_old_tags.count loop
            begin
                l_dummy_tag := l_new_tags_a(l_old_tags(i));
            exception when no_data_found then
                delete from tags where content_pk = p_content_pk and tag = l_old_tags(i);
                l_merge_tags(l_merge_tags.count + 1) := l_old_tags(i);
            end;
        end loop;
    else --just do inserts
        if l_new_tags.exists(1) then
          for i in 1..l_new_tags.count loop
              insert into tags (tag, content_pk, content_table )
              values (trim(l_new_tags(i)), p_content_pk, p_content_table );
              l_merge_tags(l_merge_tags.count + 1) := l_new_tags(i);
          end loop;
        end if;
    end if;
    for i in 1..l_merge_tags.count loop
        merge into tags_tsum s
        using (select count(*) tag_count
                 from tags
                where tag = l_merge_tags(i) and content_table = p_content_table ) t
        on (s.tag = l_merge_tags(i) and s.content_table = p_content_table )
        when not matched then insert (tag, content_table, tag_count)
                              values (trim(l_merge_tags(i)), p_content_table, t.tag_count)
        when matched then update set s.tag_count = t.tag_count;
        merge into tags_sum s
        using (select sum(tag_count) tag_count
                 from tags_tsum
                where tag = l_merge_tags(i) ) t
        on (s.tag = l_merge_tags(i) )
        when not matched then insert (tag, tag_count)
                              values (trim(l_merge_tags(i)), t.tag_count)
        when matched then update set s.tag_count = t.tag_count;
    end loop; 
end tags_sync;
/

请注意content_pk/content_table列-基本上，我们将从要添加概念的任何应用程序表中集中标记管理。