1.取任意20位随机数
select substr(cast(dbms_random.value as varchar2(38)),3,20) from dual
2.取当前时间戳
秒级:
SELECT (SYSDATE - TO_DATE('1970-1-1 8', 'YYYY-MM-DD HH24')) * 86400 FROM DUAL;
用当前的时间减去1970年1月1日8时,得到的天数乘以24小时乘以3600秒,得到的结果就是系统时间戳。这里用8时的原因时系统所处时区为东8区。
毫秒级:
SELECT (SYSDATE - TO_DATE('1970-1-1 8', 'YYYY-MM-DD HH24')) * 86400000 + TO_NUMBER(TO_CHAR(SYSTIMESTAMP(3), 'FF')) AS MILLIONS FROM DUAL;
3.字符串拼接
SELECT 1 || 2 FROM DUAL; //数字之间能行
SELECT concat(1,'str') FROM DUAL; //字母与数字之间能行
-- 两种方法
4.将timestamp类型转成date类型
select to_date(to_char(原timestamp类型,'yyyy-mm-dd hh:mi:ss'),'yyyy-mm-dd hh:mi:ss') from dual
5.序列
//查看序列
select wl_customer_id_SEQ.NEXTVAL from dual
//删除序列
drop sequence wl_customer_id_SEQ;
//新建序列(从10开始,每次加1,最大值999999999)
create sequence wl_customer_id_SEQ increment by 1 start with 10 minvalue 999999999;
//修改序列自增数(序列自增100)
alter sequence wl_customer_id_SEQ increment by 100;
6.判断
//某一列如果为a返回1 否则为2
select case 列 when 'a' then 1 else 2 end from 表名
7.有则修改无则添加
merge into emp a
using (select '001' as id,'王帅' as name from dual) b
on (a.id = b.id)
when matched then
update set a.name = b.name
when not matched then
insert (id,name) values (b.id,b.name);