自定义字段相关资料
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)
