Oracle创表操作记录

Oracle表操作

---

--主键，复合主键
create table example (id number primary key, name varchar2(20));
create table example1 (id number, name varchar2(20),primary key(id));

create table example2 (id number, name varchar2(20));
ALTER TABLE example2 ADD PRIMARY KEY (ID)

create table example3 (id number,name varchar2(20) ,primary key (id,name));

create table example4 (id number, name varchar2(20));
ALTER TABLE example4 ADD PRIMARY KEY (ID, NAME)
--主键上创建索引，升序，降序
CREATE INDEX example_index_ASC ON example ('ID' ASC);
CREATE INDEX example1_index_DESC ON example ('ID' DESC);
--联合主键

--基于单列的外键
 create table tb_supplier
(
  supplier_id number not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT pk_supplier PRIMARY KEY (supplier_id)
);
 
create table tb_products
(
  product_id number not null,
  product_name varchar2(100),
  supplier_id number not null,
  constraint fk_products_supplier foreign key (supplier_id) references tb_supplier(supplier_id)
);
create table tb_products_1
(
  product_id number not null,
  product_name varchar2(100),
  supplier_id number not null
);
ALTER TABLE tb_products_1 ADD CONSTRAINT tb_products_1_fk1 FOREIGN KEY (supplier_id) REFERENCES tb_supplier
///
--建立自增主键(主键、序列、触发器)
Create Table stu(  
uerId NUMBER(4) Not Null Primary Key,  
username Varchar(20),  
userpass Varchar(20),  
email varchar2(30),  
grade NUMBER(5)  
);  
Commit;  

Select * From STU;  

Create Sequence emp_sequence  
Increment By 1  
Start With 1  
Nomaxvalue  
Nocycle  
Nocache;  
Commit;  

create or replace trigger "trigger_on_STU" Before  
Insert On STU For Each Row When (New.uerId Is Null)  
Begin  
Select emp_sequence.Nextval Into :New.'uerId' From dual;  
end
Insert Into STU(username,userpass,email,grade) Values('admin','admin','admin@admin.com',1);  
Insert Into STU(username,userpass,email,grade)  Values('dhy','dhy','dhy@admin.com',1);  
Insert Into STU(username,userpass,email,grade)  Values('nsr','nsr','nsr@admin.com',1);  
Insert Into STU(username,userpass,email,grade)  Values('adamin','addmin','admdin@admin.com',5);  
Insert Into STU(username,userpass,email,grade)  Values('dhay','dhay','dhay@admin.com',3);  
Insert Into STU(username,userpass,email,grade)  Values('ansr','ansr','ansr@admin.com',4);  
Commit;  
触发器无效且未通过重新验证
///还不行
基于多列的外键
drop table TB_PRODUCTS1;
drop table TB_SUPPLIER1;
 
create table tb_supplier1
(
  supplier_id number not null,
  supplier_name varchar2(50) not null,
  contact_name varchar2(50),
  CONSTRAINT pk_supplier1 PRIMARY KEY (supplier_id,supplier_name)
);
 
create table tb_products1
(
  product_id number not null,
  product_name varchar2(100),
  supplier_name varchar2(50),
  supplier_id number not null,
  constraint fk_products_supplier1 foreign key (supplier_id,supplier_name) references tb_supplier1(supplier_id,supplier_name)
);
create table tb_products1_1
(
  product_id number not null,
  product_name varchar2(100),
  supplier_name varchar2(50),
  supplier_id number not null
);
ALTER TABLE tb_products1_1 ADD CONSTRAINT tb_products1_1_fk1 FOREIGN KEY (supplier_id,supplier_name) REFERENCES tb_supplier1

-- create a table with a table-level primary key constraint
-- and a table-level foreign key constraint 
CREATE TABLE FLIGHTS
	(
	FLIGHT_ID CHAR(6) NOT NULL ,
	SEGMENT_NUMBER INTEGER NOT NULL ,
	ORIG_AIRPORT CHAR(3),
	DEPART_TIME DATE,  --time无效的数据类型
	DEST_AIRPORT CHAR(3),
	ARRIVE_TIME DATE,
	MEAL CHAR(1) CONSTRAINT MEAL_CONSTRAINT 
	CHECK (MEAL IN ('B', 'L', 'D', 'S')),
	PRIMARY KEY (FLIGHT_ID, SEGMENT_NUMBER)
	);

CREATE TABLE FLTAVAIL
	(
	FLIGHT_ID CHAR(6) NOT NULL, 
	SEGMENT_NUMBER INT NOT NULL, 
	FLIGHT_DATE DATE NOT NULL, 
	ECONOMY_SEATS_TAKEN INT,
	BUSINESS_SEATS_TAKEN INT,
	FIRSTCLASS_SEATS_TAKEN INT, 
	CONSTRAINT FLTAVAIL_PK PRIMARY KEY (FLIGHT_ID, SEGMENT_NUMBER), 
	CONSTRAINT FLTS_FK
	FOREIGN KEY (FLIGHT_ID, SEGMENT_NUMBER)
	REFERENCES Flights (FLIGHT_ID, SEGMENT_NUMBER)
	);

-- create a table whose city_id column references the
-- primary key in the Cities table
-- using a column-level foreign key constraint  
CREATE TABLE Cities
(
city_id INT not null primary key
);
CREATE TABLE CONDOS
	(
	CONDO_ID INT NOT NULL CONSTRAINT hotels_PK PRIMARY KEY,
	CONDO_NAME VARCHAR(40) NOT NULL,
	CITY_ID INT CONSTRAINT city_foreign_key
	REFERENCES Cities ON DELETE CASCADE [ON UPDATE RESTRICT]
	);
	CREATE TABLE CONDOS1
	(
	CONDO_ID INT NOT NULL CONSTRAINT hotels_PK1 PRIMARY KEY,
	CONDO_NAME VARCHAR(40) NOT NULL,
	CITY_ID INT CONSTRAINT city_foreign_key1
	REFERENCES Cities ON DELETE set null
	);
	CREATE TABLE CONDOS2
	(
	CONDO_ID INT NOT NULL CONSTRAINT hotels_PK2 PRIMARY KEY,
	CONDO_NAME VARCHAR(40) NOT NULL,
	CITY_ID INT CONSTRAINT city_foreign_key2
	REFERENCES Cities  DEFERRABLE   INITIALLY DEFERRED
	);
	
--唯一约束

基于单列的唯一性约束示例
create table tb_supplier2
(
  supplier_id          number not null,
  supplier_name        varchar2(50),
  contact_name         varchar2(50),
  CONSTRAINT tb_supplier_u1 UNIQUE (supplier_id)--创建表时创建唯一性约束
);
create table tb_supplier2_1
(
  supplier_id          number not null,
  supplier_name        varchar2(50),
  contact_name         varchar2(50)
)
ALTER TABLE tb_supplier2_1 ADD CONSTRAINT tb_supplier2_1_ix1 UNIQUE (supplier_id)
基于多列的唯一性约束示例
create table tb_products2
(
  product_id        number not null,
  product_name      number not null,
  product_type      varchar2(50),
  supplier_id       number,
  CONSTRAINT tb_products_u1 UNIQUE (product_id, product_name) --定义复合唯一性约束
);
create table tb_products2_1
(
  product_id        number not null,
  product_name      number not null,
  product_type      varchar2(50),
  supplier_id       number
);


--列级约束
create table u1(id number(10) constraint u1_id_u unique,
name varchar2(20),
company varchar2(20));
--表级约束
create table u2(id number(10),
name varchar2(20),
company varchar2(20),
constraint u2_id_u unique(id));

--检查约束 
--表中包含列级、行级约束
CREATE TABLE EMP 
	(
	EMPNO CHAR(6) NOT NULL CONSTRAINT EMP_PK PRIMARY KEY,
	FIRSTNME CHAR(12) NOT NULL,
	MIDINIT vARCHAR(12) NOT NULL,
	LASTNAME VARCHAR(15) NOT NULL,
	SALARY DECIMAL(9,2) CONSTRAINT SAL_CK CHECK (SALARY >= 10000),
	BONUS DECIMAL(9,2), 
	TAX DECIMAL(9,2),
	CONSTRAINT BONUS_CK CHECK (BONUS > TAX)
	);
ALTER TABLE EMP ADD CONSTRAINT EMP_ck7 CHECK (FIRSTNME IS NOT NULL)

--属于列级约束
create table aa1(id number(10) constraint aa1_id_c check(id>=1),
name varchar2(20),
company varchar2(20));

--属于表级约束。
create table aa2(id number(10),
 name varchar2(20),
 company varchar2(20),
 constraint aa2_id_c check(id>=1)
 );


--非空约束 注：约束中，非空约束必须是列约束。其他类型的约束都即可以是列约束，也可以创建为表约束。
drop table aa3;
create table aa3(
    id number(10),
    name varchar2(20),
    company varchar2(20));
alter table aa3 modify(id constraint aa3_nn not null);
alter table aa3 drop constraint aa3_nn;
create table aa4(
    id number(10) constraint aa4_nn not null ,
    name varchar2(20),
    company varchar2(20));

--default约束
--列级约束
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)
CREATE TABLE Persons1
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
ALTER TABLE Persons1
MODIFY City DEFAULT 'SANDNES'
--表级约束

//
--使用类似 GETDATE() 这样的函数，DEFAULT 约束也可以用于插入系统值：
CREATE TABLE Orders
(
Id_O int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
OrderDate date DEFAULT GETDATE() 
)    //ORA-04044: 此处不允许过程, 函数, 程序包或类型

--2中方法创建约束
create table product (
 
Pname varchar2(20) unique,
 
Pdate date not null,
 
PID number(5) primary key,check(PID>10000 AND PID<=99999),
 
Ploc char(30) default '北京')

--索引列的升序降序
CREATE TABLE
    INDEX_TABLE_ASC
    (
        ID INTEGER NOT NULL,
        NAME VARCHAR2(25) NOT NULL,
        NUMB INTEGER NOT NULL,
        EMAIL VARCHAR2(20) NOT NULL
    );
  CREATE INDEX INDEX_TABLE_ASC1 ON INDEX_TABLE_ASC ('ID' ASC);
  
  CREATE TABLE
    INDEX_TABLE_DESC
    (
        ID INTEGER NOT NULL,
        NAME VARCHAR2(25) NOT NULL,
        NUMB INTEGER NOT NULL,
        EMAIL VARCHAR2(20) NOT NULL
    );
  CREATE INDEX INDEX_TABLE_DESC1 ON INDEX_TABLE_ASC ('ID' DESC);

--创建索引 一个索引最多有16列，long列、long raw列不能建索引列
--单列索引
create table index_table(
id int not null,
name varchar(25) not null,
number int  not null,
email varchar(20) not null
)
CREATE INDEX index_table_1 on index_table(id)
--多列索引(复合索引)
CREATE TABLE index_table1 (
    id         INT NOT NULL,
    last_name  CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
    PRIMARY KEY (id));
CREATE INDEX index_table_2 on index_table1(last_name,first_name);

CREATE TABLE index_table2 (
    id         INT NOT NULL,
    last_name  CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
	nuber char(20) not null,
	addr varchar(40) not null,
	emaill varchar(30) not null,
	sex varchar(4) not null,
	co1 int  null,
	co11 int  null,
    co12 int  null,	
	co13 int  null,
	co14 int  null,
	co15 int  null,
	co16 int  null,
	co17 int  null,
	co18 int  null,
    PRIMARY KEY (id));
CREATE INDEX index_table_3 on index_table2(id,last_name,first_name,nuber,addr,emaill,sex,co1,co11,co12,co13,co14,co15,co16,co17,co18);
--唯一索引

--单列
 create table test(
  id int,
  name varchar2(20));
create unique index index_test on test(id ASC);
insert into test values(1, 'Sally');
insert into test values(null, 'Tony'); 

 create table test1(
  id int not null,
  name varchar2(20) not null);

create unique index index_test1 on test1(id DESC);
--多列
create table test3(
  id         INT NOT NULL,
    last_name  CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL,
	nuber char(20) not null,
	addr varchar(40) not null,
	emaill varchar(30) not null,
	sex varchar(4) not null,
	co1 int  null,
	co11 int  null,
    co12 int  null,	
	co13 int  null,
	co14 int  null,
	co15 int  null,
	co16 int  null,
	co17 int  null,
	co18 int  null,
    PRIMARY KEY (id))

create unique index index_test2 on test2(id,last_name,first_name,nuber,addr,emaill,sex,co1,co11,co12,co13,co14,co15,co16,co17,co18)

--查询索引
select * from user_indexes where table_name='表名';
select * from user_ind_columns  where index_name='索引名';   //dbvisualizer中查不到，到sqlplus下查找//

--函数索引
create table fun_index(
  id  INT NOT NULL PRIMARY KEY,
    last_name  CHAR(30) NOT NULL,
    first_name CHAR(30) NOT NULL);
	
CREATE INDEX func_index ON fun_index(SUBSTR(id,0,1));
create index index_fun on fun_index(upper(last_name));
insert into  fun_index values(0,'jason','mike');
insert into  fun_index values(1,'last_name','first_name');

--全局索引
--全局索引以整个表的数据为对象建立索引，索引分区中的索引条目既可能是基于相同的键值但是来自不同的分区，也可能是多个不同键值的组合。
--全局索引既允许索引分区的键值和表分区键值相同，也可以不相同。全局索引和表之间没有直接的联系，这一点和本地索引不同。
--全局索引，就是索引跨越分区表，在做跨分区查询优势快；
--局部索引，分区表某个分区的索引，速度比全局快，而且维护更方便 ；

create table orders (
     order_no      number,
     part_no       varchar2(40),
     ord_date      date
     )
     partition by range (ord_date)
      (partition Q1 values less than (TO_DATE('01-APR-1999','DD-MON-YYYY')),
       partition Q2 values less than (TO_DATE('01-JUL-1999','DD-MON-YYYY')),
       partition Q3 values less than (TO_DATE('01-OCT-1999','DD-MON-YYYY')),
       partition Q4 values less than (TO_DATE('01-JAN-2000','DD-MON-YYYY'))
      )
     ;
	 
create index orders_global_1_idx
     on orders(ord_date)
      global partition by range (ord_date)
       (partition GLOBAL1 values less than (TO_DATE('01-APR-1999','DD-MON-YYYY')),
        partition GLOBAL2 values less than (TO_DATE('01-JUL-1999','DD-MON-YYYY')),
        partition GLOBAL3 values less than (TO_DATE('01-OCT-1999','DD-MON-YYYY')),
        partition GLOBAL4 values less than (MAXVALUE)
       )
     ;
create index orders_global_2_idx
     on orders(part_no)
      global partition by range (part_no)
       (partition IND1 values less than (555555),
        partition IND2 values less than (MAXVALUE)
       )
     ;
--假如表中新加了分区，不会在全局索引中自动增加新的分区，必须手工添加相应的分区。
 alter table orders add partition Q5 values less than (TO_DATE('01-APR-2000','DD-MON-YYYY'));
 
 select TABLE_NAME, PARTITION_NAME from dba_tab_partitions where table_name='ORDERS';
 select INDEX_NAME, PARTITION_NAME from dba_ind_partitions where index_name=upper('orders_global_1_idx');
 --使用全局索引，索引键值必须和分区键值相同，这就是所谓的前缀索引
 
 --全局HASH分区索引
 //在10g以前，Oracle的全局索引分区方法只有一种，即范围分区
CREATE TABLE T_PART_INDEX (ID NUMBER, NAME VARCHAR2(30), CREATE_DATE DATE);
INSERT INTO T_PART_INDEX SELECT ROWNUM, OBJECT_NAME, CREATED FROM DBA_OBJECTS;
COMMIT;
CREATE INDEX IND_PART_INDEX_CREATE 
ON T_PART_INDEX (CREATE_DATE) GLOBAL
PARTITION BY RANGE (CREATE_DATE)
(PARTITION P1 VALUES LESS THAN (TO_DATE('2008-1-1', 'YYYY-MM-DD')),
PARTITION P2 VALUES LESS THAN (TO_DATE('2009-1-1', 'YYYY-MM-DD')), 
PARTITION PMAX VALUES LESS THAN (MAXVALUE));
//而在10g中，Oracle新增了一种全局索引的分区方式，用户可以选项HASH分区方式对全局索引进行分区：
CREATE INDEX IND_PART_INDEX_ID
  ON T_PART_INDEX (ID) GLOBAL
 PARTITION BY HASH (ID)
 PARTITIONS 16;
 
 --创建本地索引不必显式的指定maxvalue值，因为为表新添加表分区时，会自动添加相应的索引分区。
 create table orders1(
     order_no      number,
     part_no       varchar2(40),
     ord_date      date
     )
     partition by range (ord_date)
      (partition Q1 values less than (TO_DATE('01-APR-1999','DD-MON-YYYY')),
       partition Q2 values less than (TO_DATE('01-JUL-1999','DD-MON-YYYY')),
       partition Q3 values less than (TO_DATE('01-OCT-1999','DD-MON-YYYY')),
       partition Q4 values less than (TO_DATE('01-JAN-2000','DD-MON-YYYY'))
      )
     ;
 create index orders_local_1_idx
on orders1(ord_date)
 local
  (partition LOCAL1,
   partition LOCAL2,
   partition LOCAL3,
   partition LOCAL4
  )
;
select INDEX_NAME, PARTITION_NAME from dba_ind_partitions where index_name=upper('orders_local_1_idx');
alter table orders1 add partition Q5 values less than (TO_DATE('01-APR-2000','DD-MON-YYYY'));

--这里系统已经自动以和表分区相同的名字自动创建了一个索引分区。同理，删除表分区时相对应的索引分区也自动被删除。

--本地索引和全局索引还有一个显著的差别，本地索引可以创建成本地非前缀型，而全局索引只能是前缀型。
--本地非前缀索引
create table orders2(
     order_no      number,
     part_no       varchar2(40),
     ord_date      date
     )
     partition by range (ord_date)
      (partition Q1 values less than (TO_DATE('01-APR-1999','DD-MON-YYYY')),
       partition Q2 values less than (TO_DATE('01-JUL-1999','DD-MON-YYYY')),
       partition Q3 values less than (TO_DATE('01-OCT-1999','DD-MON-YYYY')),
       partition Q4 values less than (TO_DATE('01-JAN-2000','DD-MON-YYYY'))
      )
     ;
create index orders_local_2_idx
     on orders2(part_no)
      local
       (partition LOCAL1,
        partition LOCAL2,
        partition LOCAL3,
        partition LOCAL4)
     ;
select INDEX_NAME, PARTITION_NAME, HIGH_VALUE from dba_ind_partitions
     where index_name=upper('orders_local_2_idx');

--B-tree聚集索引	 
--Creating a Cluster

CREATE CLUSTER personnel
   (department NUMBER(4))
SIZE 512 
STORAGE (initial 100K next 50K);

--Cluster上创建索引
CREATE INDEX idx_personnel ON CLUSTER personnel;
--Adding Tables to a Cluster、
CREATE TABLE employees(
department NUMBER(4) 
);

CREATE TABLE dept_10
   CLUSTER personnel (department)
   AS SELECT * FROM employees WHERE department = 10;   

CREATE TABLE dept_20
   CLUSTER personnel (department)
   AS SELECT * FROM employees WHERE department = 20;    
   
--B-tree聚集索引
CREATE CLUSTER 
personnel1( department_number NUMBER(2) )
SIZE 512 STORAGE (INITIAL 100K NEXT 50K); 
CREATE TABLE emp_a (
empno NUMBER PRIMARY KEY,
ename VARCHAR2(10) NOT NULL 
CHECK (ename = UPPER(ename)),
job VARCHAR2(9), 
mgr NUMBER ,
comm NUMBER(9,0) DEFAULT NULL, 
deptno NUMBER(2) NOT NULL )
CLUSTER personnel1 (deptno);

CREATE TABLE dept_a (
deptno NUMBER(2),
dname VARCHAR2(9),
loc VARCHAR2(9))
CLUSTER personnel1 (deptno);

CREATE INDEX idx_personnel1 ON CLUSTER personnel1;
--Hash Clusters

--建立使用数据库默认hash函数的聚集language
CREATE CLUSTER language (cust_language VARCHAR2(3))
   SIZE 512 HASHKEYS 10
   STORAGE (INITIAL 100k next 50k);
--建立使用自定hash函数的聚集:
CREATE CLUSTER address
   (postal_code NUMBER, country_id CHAR(2))
   HASHKEYS 20
   HASH IS MOD(postal_code + country_id, 101);
 
--建立聚集表和索引：   
CREATE CLUSTER cust_orders (customer_id NUMBER(6))
   SIZE 512 SINGLE TABLE HASHKEYS 100;
create table address1
(postal_code NUMBER, country_id CHAR(2))
CLUSTER address(postal_code NUMBER, country_id CHAR(2))
   HASHKEYS 20
   HASH IS MOD(postal_code + country_id, 101);

--反向索引（Reverse key indexes）

--创建一个实验表T
create table t (x int);
--创建反向索引，在正常创建索引的语句后面简单的加上“reverse”关键字即可。
create index idx_t on t(x) reverse;
--使用user_indexes视图查看一下索引的类型，“NORMAL/REV”表示该索引类型为反向索引。
col TABLE_NAME for a10
 col INDEX_NAME for a10
 select table_name, index_name, index_type from user_indexes where table_name = 'T';
--修改反向索引为正常索引
alter index idx_t rebuild noreverse;
--确认一下，该索引已经修改为正常的索引。
select table_name, index_name, index_type from user_indexes where table_name = 'T';

--位图索引
create table idx_bitmap_test(gendar varchar(10),id number);

create bitmap index idx_bitmap_test_gendar on idx_bitmap_test(gendar);

另个回话同时插入数据:

插入bitmap

插入相同的位图值

insert into idx_bitmap_test values ('男', 1);

insert into idx_bitmap_test values ('男', 2);  会话2会等待会话1；


插入不同的会话值

insert into idx_bitmap_test values ('男', 3);

insert into idx_bitmap_test values ('女', 4);  不会等待；


删除也是同样的效果


插入index

insert into idx_bitmap_test values ('男', 1);

insert into idx_bitmap_test values ('男', 2);  不会等待


--位图连接索引（Bitmap join indexes）// Bitmap Index 建立在单个 table 上不同、Bitmap Join Index 是基于多表连接的
create bitmap index FACT_DIM_COL_IDX
on FACT(DIM.Descr_Col)
from FACT, DIM
where FACT.JoinCol = DIM.JoinCol;

create table fact(JoinCol char(2) primary key,Descr_Col decimal );
create table DIM(JoinCol char(2) primary key,Descr_Col decimal );
//
create bitmap index SALES_CUST_NAME_IDX
on SALES(CUSTOMER.Customer_Name)
from SALES, CUSTOMER
where SALES.Customer_ID=CUSTOMER.Customer_ID;
//

--域索引
--布骤一 检查和设置数据库角色首先检查数据库中是否有CTXSYS用户和CTXAPP脚色
--步骤二 赋权 在ctxsys用户下把ctx_ddl的执行权限赋于要使用全文索引的用户，例：
grant execute on ctx_ddl to hcf;

--步骤三 设置词法分析器(lexer)
--第一． 当前用户下下建立一个preference(例：在hcf用户下执行以下语句)
 exec ctx_ddl.create_preference('my_lexer','CHINESE_VGRAM_LEXER') ;
--第二． 在建立全文索引时，指明所用的lexer:
CREATE INDEX myindex ON mytable(mycolumn) indextype is ctxsys.context 
parameters('lexer my_lexer');
create table mytable(mycolumn varchar(30));

--步骤四 建立索引
create table pubmenu(pubmenu varchar(30),menuname varchar(30));
CREATE INDEX ctx_idx_menuname ON pubmenu(pubmenu) 
indextype is ctxsys.context parameters('lexer my_lexer');
--步骤五 使用索引
select * from pubmenu where contains(menuname,'上传图片')>0;

--对多字段建立全文索引
--建多字段索引的preference
EXEC ctx_ddl.create_preference(' ctx_idx_subject_pref','MULTI_COLUMN_DATASTORE');
--建立preference对应的字段值(以ctxsys登录)
EXEC ctx_ddl.set_attribute(' ctx_idx_subject_pref ','columns','subjectname,briefintro');
--建立全文索引
CREATE INDEX ctx_idx_subject ON pmhsubjects(subjectname) 
INDEXTYPE IS ctxsys.CONTEXT PARAMETERS('DATASTORE ctxsys.ctx_idx_subject_pref lexer my_lexer');//error
create table pmhsubjects(subjectname varchar(30),columns varchar(30),briefintro  varchar(255));

http://blog.csdn.net/nciasd/article/details/51752604