4.primarykeys-update_v001.sql

CRM源码This file describes some issues that should be implemented in future and how it should be imple

-- create table qx_maxkeys
create table qx_maxkeys (
  tablename  varchar2(255)  not null,
  keyvalue   number         not null,
constraint pk_qx_maxkeys primary key (tablename)
);


set serveroutput on size 1000000;


declare
  currentKeyValue number;
  incr number;
begin
  -- select all tables max pkey values
  for pKeys in (select lower(cc.table_name) as table_name, cc.column_name
                  from user_constraints c, user_cons_columns cc, user_tab_columns tc
                  where (c.constraint_name = cc.constraint_name)
                    and (c.table_name = cc.table_name)
                    and (cc.table_name = tc.table_name)
                    and (cc.column_name = tc.column_name)
                    and (c.constraint_type = 'P')
                    and (c.table_name <> 'QX_MAXKEYS')
                    and (cc.position = 1)
                    and (tc.data_type = 'NUMBER')
                  order by cc.table_name, cc.position)
  loop
    begin
      execute immediate 'INSERT INTO qx_maxkeys (tablename, keyvalue)' ||
                        ' VALUES (''' || pKeys.table_name || ''', (SELECT NVL(MAX(' || pKeys.column_name || '), 0)' ||
                                                                 ' FROM ' || pKeys.table_name || ') )';
    exception
      when others then
        raise_application_error(-20001, 'Error inserting objects from ' || pKeys.table_name || ' into qx_maxkeys, search procedure aborted.');
    end;
  end loop;
  
  for pKeys in (select tablename, keyvalue
                  from qx_maxkeys
                  order by tablename)
  loop
    begin
      select keyvalue
        into currentKeyValue
        from qx_keys
        where lower(tablename) = lower(pKeys.tablename);
    exception
      when no_data_found then
        currentKeyValue := 0;
    end;
    
    dbms_output.put_line('Check table: ' || pKeys.tablename || ', key: ' || pKeys.keyvalue || ', current key: ' || currentKeyValue);
    
    incr := pKeys.keyvalue - currentKeyValue;
    
    if (incr > 0) then
      dbms_output.put_line('Set up max key to ' || incr);
      qx_next_key (pKeys.tablename, incr, currentKeyValue);
    end if;
  end loop;
end;
/


commit;


-- drop table qx_maxkeys
drop table qx_maxkeys cascade constraints;


set serveroutput off;