Oracle创建表、修改字段类型
1.创建表
1.创建表
create table SCM_PER( --SCM_PER表名 ID NUMBER(6) primary key,--主键ID USERID varchar2(30),--用户ID --Permission varchar2(11),--用户权限 --begin_date date, --end_date date, --def_date date, --def_usr_id varchar2(30), upd_date date, upd_usr_id varchar2(30), PRE_INV_005 number, PRE_CM_001 number(2), PRE_STK_039 number(2), PRE_INV_012 number(2), PRE_PRB_001 number(2), REM varchar2(200) ) SELECT * FROM SCM_PER
2.创建序列
create sequence SCM_PER_Seq --序列名 increment by 1 start with 1 maxvalue 999999999;
3.创建触发器
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
create or replace trigger SCM_PER_TRG before insert on SCM_PER for each row declare ID NUMBER; begin SELECT SCM_PER_Seq.Nextval INTO :NEW.ID FROM DUAL; end;
4.删除列
alter table tablename drop (column)
5.增加列
alter table tablename add (column datatype [default value][null/not null],….); --增加新字段 alter table TABLENAME add NAME VARCHAR2(200); --增加备注 comment on column TABLENAME.NAME is '注释';
2.修改字段类型
注:无法直接修改字段类型,方法是添加一个新列,将原数据更新到新列中
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
--PROJ为tablename,CONSTRUCTION_SEQ为原列名 --修改原表名 alter table PROJ rename column CONSTRUCTION_SEQ to CONSTRUCTION_SEQold; --增加新字段 alter table PROJ add CONSTRUCTION_SEQ VARCHAR2(200); --增加备注 comment on column PROJ.CONSTRUCTION_SEQ is '更改后的字段,建造序列';--to_char --将原字段数据给到新字段 update PROJ set CONSTRUCTION_SEQ=to_char(CONSTRUCTION_SEQold); --删除原字段 alter table PROJ drop column CONSTRUCTION_SEQold;
3.查询树形所有上下级数据
select * from std_cls start with std_cls_id='SABT' connect by prior p_std_cls_no=std_cls_no
union
(select * from std_cls start with std_cls_id='SABT' connect by prior std_cls_no=p_std_cls_no)
4.常用语句
1.将回车符去除
update TABLENAME t set t.column=replace(t.column,chr(13),'');--/r
2.转成日期格式
To_Date('2019-09-11 09:11:07', 'yyyy-mm-dd hh24:mi:ss')
3.找到某个字段有非数字的
select * from Non_Ship_Mat s where regexp_replace(substr(s.non_ship_mat_id, 9, 14),'^[-\+]?\d+(\.\d+)?$','') is not null;