カスタムフィールド関連資料

create or replace procedure sp_alter_table(i_tablename in varchar2,
                                           i_fieldname in varchar2,
                                           i_fieldtype in varchar2) authid current_user is
  v_sql varchar2(2000);
begin
  v_sql := 'alter table ' || i_tablename || ' add ' || i_fieldname || ' ' ||
           i_fieldtype;
  execute immediate v_sql;
end sp_alter_table;

  --               Account : 
CREATE TABLE t_Account(Id int NOT NULL, name varchar2(255) NOT NULL);

  -- Fields:          : 
  /*  Account  TableId=1*/
CREATE TABLE t_Fields(Id int NOT NULL, FieldName varchar2(50), FieldTypeId int, TableId int);
--     Depart   :          :
  --A,    Account     Depart           :
insert into t_Fields
  (id, FieldName, FieldTypeId, TableId)
values
  (1, 'Depart ', 1, 1);

ALTER TABLE t_Account ADD Depart VARCHAR(20);
--  Account   :
Insert into t_Account (id, name, Depart) values (1, 'test A', 'depart1 ');
--   :
select name, Depart from t_Account;

  --B.   Depart          AccountData   AccountData     :
CREATE TABLE t_AccountData(Id int NOT NULL, value varchar2(255), FieldsName varchar2(255), FieldsId int, AccountId int NOT NULL);
--         :
insert into t_Fields
  (id, FieldName, FieldTypeId, TableId)
values
  (1, 'Depart ', 1, 1);
--  Account   :
Insert into t_Account (id, name) values (2, 'test B');
Insert into t_AccountData
  (id, value, FieldsName, FieldsId, AccountId)
values
  (1, 'depart2 ', 'depart ', 1, 2);
--   :
select name,
       (select t_AccountData.value
          from t_AccountData
         where t_AccountData.FieldsId = 1
           and t_AccountData.AccountId = t.id) as Depart
  from t_Account t;





--      

create table T_FORMGUIDE
(
  formid    NUMBER,
  name      VARCHAR2(30) not null,
  tablename VARCHAR2(30) not null,
  introduce VARCHAR2(255) not null,
  setting   VARCHAR2(4000) not null,
  addtime   NUMBER(10) default 0 not null,
  template  VARCHAR2(50) not null,
  disabled  NUMBER(1) default 1 not null
);

create table T_FORMGUIDE_FIELDS
(
  fieldid      NUMBER(8) not null,
  formid       NUMBER(8) default 0 not null,
  field        VARCHAR2(20) not null,
  name         VARCHAR2(30) not null,
  tips         VARCHAR2(4000) not null,
  css          VARCHAR2(30) not null,
  pattern      VARCHAR2(255) not null,
  formtype     VARCHAR2(20) not null,
  setting      VARCHAR2(4000) not null,
  issystem     NUMBER(1) default 0 not null,
  isbackground NUMBER(1) default 0 not null,
  isunique     NUMBER(1) default 0 not null,
  issearch     NUMBER(1) default 0 not null,
  isselect     NUMBER(1) default 0 not null,
  islist       NUMBER(1) default 0 not null,
  isshow       NUMBER(1) default 0 not null,
  listorder    NUMBER(8) default 0 not null,
  disabled     NUMBER(1) default 1 not null
)