Oracle中几种情景的SQL写法(更新ing)

SELECT city,listagg(upper(trim(tele_no)),',') within group(order by ID) tele_no_str
FROM CITY_TELE_NO
WHERE tele_no between 1519870 AND 1519874
GROUP BY city;
复制

SELECT city, REGEXP_SUBSTR(tele_no_str, '[^,]+', 1, LEVEL, 'i') AS tele_no
FROM (SELECT * FROM CITY_TELE_NO WHERE tele_no_str LIKE '%,%' )
CONNECT BY prior rowid = rowid
and prior dbms_random.value is not null
and LEVEL <=regexp_count(tele_no_str, '[^,]+');
复制

select distinct transname,level grade,connect_by_root(source)sou --记录每条记录的根节点
from transdatamap_design 
--where ...
start with source in('99165','99154') --指定根节点
--nocycle:忽略环,上条transname作为本条source递归查询
connect by nocycle prior transname=source 
order by grade;
--调整关联可向上或向下查询
复制

--删除表不进回收站
DROP TABLE 'table_name' PURGE;
--查看回收站中表
select * from recyclebin;
--恢复表
flashback table test_drop to before drop;
or
flashback table "BIN$b+XkkO1RS5K10uKo9BfmuA==$0" to before drop;
--清除回收站中表
PURGE TABLE 'table_name';
or 
PURGE TABLE "BIN$b+XkkO1RS5K10uKo9BfmuA==$0";
--清空回收站
purge recyclebin;
复制

--建表语句
select dbms_metadata.get_ddl('TABLE','TELE_NO_NEW','ODS') from dual;
--存储过程
select dbms_metadata.get_ddl('PROCEDURE','EX_TELE_NO','DW') from dual;
复制

--date精确到秒(yyyy-mm-dd hh24:mi:ss),相减得到的时间差单位为“天”
--分钟差(小时差、秒差以此类推)：
select ROUND(TO_NUMBER(to_date('2021-07-24 18:12:00','yyyy-mm-dd hh24:mi:ss') - sysdate)*24*60) from dual;


--timestamp是DATE类型的扩展，可以精确到小数秒，可以是0-9，缺省是６(yyyy-mm-dd hh24:mi:ss:ff3)
--两个timestamp相减的话，得到的是，多少天，多少小时，多少秒等
select to_timestamp('2021-07-24 18:12:00','yyyy-mm-dd hh24:mi:ss')-systimestamp from dual;
--结果：+000000001 02:41:28.032000000 （1天2小时42分钟28.032秒）
复制