删除字段
alter table AKEP_TEMPTICKET drop column CUS;
declare
v_col int;v_col2 int;v_col3 int;
begin
select count(*) into v_col from all_Tab_Columns where table_name = upper('AK_IDC_PASSORDER') and column_name = upper('SIGNNER');
select count(*) into v_col2 from all_Tab_Columns where table_name = upper('AK_IDC_WORKORDER') and column_name = upper('SIGNNER');
select count(*) into v_col3 from all_Tab_Columns where table_name = upper('AK_DEVICEORDER') and column_name = upper('SIGNNER');
if v_col=0 then
execute immediate 'alter table AK_IDC_PASSORDER add(signner VARCHAR2(20))';
end if;
if v_col2=0 then
execute immediate 'alter table AK_IDC_WORKORDER add(signner VARCHAR2(20))';
end if;
if v_col3=0 then
execute immediate 'alter table AK_DEVICEORDER add(signner VARCHAR2(20))';
end if;
end;
修改字段大小
alter table AKEP_ANNOUNCEMENT modify subject varchar2(100)
修改字段可空
ALTER TABLE AK_TEMPTICKET MODIFY CONTACT VARCHAR2(30) NULL
--timamp转换为年月日时分秒,将表中receivets字段都替换
select (to_date(to_char(receivets,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss'))from AK_DEVICEORDER;
--long转换为年月日时分秒，将1289927493131转换为年月日时分秒
select to_date('1970-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')+1289927493131/1000/24/60/60 from dual
demo：联合查询出没有关系的两张表，并按照时间倒序排序
select * from (select t.id,t.customer_name,to_date('1970-01-01 00:00:00','yyyy-mm-dd hh24:mi:ss')+t.receivedtime/1000/24/60/60 as receivedtime from AK_IDC_WORKORDER t
where receivedtime is not null
union all
select b.id,b.customername,(to_date(to_char(receivets,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss')) as receivedtime from AK_DEVICEORDER b
where receivets is not null) r
order by r.receivedtime desc