Oracle数据库常用sql语句

ORCAL中常见语句(个人工作中用的比较多的)：

• 1.按时间查询将空时间字符串排到后面(一般排序会用到将空的时间排到后面去

• select * from T_test order by Uplinetime desc nulls last;

• 2.将数据库表中某个字段中某个部分替换 例如将字段 PROB_TYPE 中的(1)(2)替换成(1)

update TH_YW_TS set PROB_TYPE=replace(PROB_TYPE,'(1)','')

• 3.新增的时候获取表中最大的ID避免非序列(新增比较频繁可以使用序列化)

SELECT NVL(MAX(ID), 0)+1 as id FROM TH_DATA_SOURCES

• 4 添加序列 对于存在线程安全性考虑可以用序列(建立序列化 TH_SYS_DICT_ID_SEQ)

<selectKey resultType="java.lang.Integer" order="BEFORE" keyProperty="id">
　　　　　　　　select TH_SYS_DICT_ID_SEQ.Nextval as id from DUAL
　　</selectKey> 

• 5 查询列表中将时间戳转换为时间格式 24H制 （时间戳为10位不需要除以1000）

TO_CHAR(r.REG_TIME / (1000 * 60 * 60 * 24) + TO_DATE('1970-01-01 08:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'YYYY-MM-DD HH24:MI:SS') as regTimeToChar

• 6 不同的sql语句查询不同的值的结果综合为一个sql语句执行 （orcal中使用with tablea as 可以查询不同的数据综合起来 避免多个sql语句在xml和mapper文件太多）

　　　with tablea as (select sum(NUM_ROWS) as dataCount from table_name),
　　　　　　tableb as (select name from tables) ,
　　　　　　tablec as (select count(1) as dataSource from TH_SYS_DATA_TYPE),
　　　　　　tabled as ( select count(1) as dataFill from TH_DATA_INPUT_LOG where type = 'write')
　　　　　　select * from tablea,tableb,tablec,tabled

　　查询出不同表中的字段,综合为一句sql语句

• 7 查询使用转换

　(case when a.STATUS='live' then '使用' when a.STATUS='dead' then '冻结' else '' end) as status (判断是null使用 is null不能使用==null)

• 8 ORCAL 批量新增数据

        insert into TH_DATA_EXCEL_INPUT_FIELDS(ID, INFOID, EXCELNAME)
　　　　　　select ROLE_CONTROLLER_ACTION_SEQ.Nextval as id, infoId, excelName
　　　　　　from (
　　　　　　　　<foreach collection="list" item="item" index="index" separator="union all">
　　　　　　　　　　select '${item.infoid}' as infoId,
　　　　　　　　'${item.excelname}' as excelName
　　　　　　　　from dual
　　　　　　　　</foreach>
　　　　　　　　)

　　　　　　　　<= mabatis中的小于号

• 9 ORCAL分页　　　　　

　select * from th_data_keyword where rownum >  #{from,jdbcType=DECIMAL}  and rownum <= #{to,jdbcType=DECIMAL}
　　　　　　order by name asc,id desc //（多个字段排序都要加上asc或者desc否在按照默认升序）

• 10 数据中为时间戳查询当天的数据 （#{currentTime}为当天的时间戳传入即可获取当天的数据）

　　　SELECT * FROM th_yw_operation_log a where
　　　　　　　　　　TO_CHAR(a.CREATETIME/(1000*60*60*24) +TO_DATE('1970-01-01 08:00:00', 'YYYY-MM-DD HH24:MI:SS'),　'YYYY-MM-DD')= 
　　　　　　　　　　TO_CHAR(1565170234000/(1000*60*60*24) +TO_DATE('1970-01-01 08:00:00', 'YYYY-MM-DD HH24:MI:SS'),　'YYYY-MM-DD')

• 11 新增或者修改的时候校验某个数值是否重复(修改的时候要排除自身与其它数据比较,新增不需要)(xml)

　　　select count(*) from th_sys_data_type where NAME = #{name}
　　　　　　<if test="id!=null and id !=''">
　　　　　　　　and id <>  (#{id})
　　　　　　　　 或者
　　　　　　　　and id !=  (#{id}) 
　　　　　　</if>

• 12 时间段与时间段有交集(xml)

　　select * from ( select * from TH_YW_ZLHT where use_for = #{condition})
　　　　　　<where>
　　　　　　　　<if test="startTime!=null and endTime!=null and startTime!='' and endTime!=''">
　　　　　　　　　　　　　and (BILLING_CYCLE >= #{startTime} AND BILLING_CYCLE <= #{endTime}) OR
　　　　　　　　　　　　　　(BILLING_CYCLE <= #{startTime} AND BILLING_CYCLE_END >= #{endTime}) OR
　　　　　　　　　　　　　　(BILLING_CYCLE_END >= #{startTime} AND BILLING_CYCLE_END <= #{endTime})
　　　　　　　　</if>
　　　　　　　　<if test="startTime!=null and endTime == null " >
　　　　　　　　　　　　and #{startTime} BETWEEN BILLING_CYCLE and BILLING_CYCLE_END
　　　　　　　　</if>
　　　　　　　　<if test=" endTime !=null and startTime==null" >
　　　　　　　　　　　　and #{endTime} BETWEEN BILLING_CYCLE and BILLING_CYCLE_END
　　　　　　　　</if>
　　　　　　</where>

• 13 ORCAL中查询最近31天 年月日字符串

select SUBSTR(time,1,10) from (　select to_char(trunc(sysdate + rownum-31,'dd'),'yyyy-mm-dd') time from dual connect by rownum <= 31　)

• 14 mabatis中动态新增和修改　

　　        <selectKey resultType="java.lang.Integer" order="BEFORE" keyProperty="id">
  　　　　　　select TH_SYS_TOKEN_SEQ.Nextval as id from DUAL
　　　　    </selectKey>
　　　　　　    insert into th_sys_controller_action
　　　　　　　　<trim prefix="(" suffix=")" suffixOverrides=",">
　　　　　　　　　　　　<if test="id != null">
　　　　　　　　　　　　　　id,
　　　　　　　　　　　　</if>
　　　　　　　　</trim>
　　　　　　　　<trim prefix="values (" suffix=")" suffixOverrides=",">
　　　　　　　　　　<if test="id != null">
　　　　　　　　　　　　#{id,jdbcType=INTEGER},
　　　　　　　　　　</if>
　　　　　　　　</trim>

　　　　　　　　update th_sys_controller_action
　　　　　　　　　　<set>
　　　　　　　　　　　　<if test="controller != null">
　　　　　　　　　　　　　　controller = #{controller,jdbcType=VARCHAR},
　　　　　　　　　　　　</if>
　　　　　　　　　　</set>

• 15 ORCAL中替换某个字段中的部分字符串（http://192.168.0.13 替换为 http://192.168.0.51）　

update TH_CHART_DOM set DATA_URL = replace(DATA_URL,'http://192.168.0.13','http://192.168.0.51')

• 16.备份数据库命令(找到orcal的oci环境 oci.dll文件最底层 在上一层即可) 经常备份有利于开发 不然数据被删了都不太好，本人就遇到过,测试无意将开发环境的数据库清空了还没备份当天是之前5天 然后各种重新弄

1 2 3 4 5

进入 E:\app\A\product\11.2.0\dbhome_1\BIN 下打开cmd命令 导出数据库 exp.exe HSBIGDATA2/th2019@192.168.0.243/ORCL file=E:\export\HSBIGDATA2_0806.dmp log=E:\export\HSBIGDATA2_0806.log OWNER=HSBIGDATA2 rows=y indexes=n compress=n buffer=65536 feedback=100000 导入数据库 imp.exe HSBIGDATA2/th2019@192.168.0.243/ORCL file=D:\user.dmp fromuser=HSBIGDATA2 touser=HSBIGDATA2 rows=y commit=y feedback=10000 buffer=10240000

　　

以上是个人在遇到orcal中比较常用的语句,要是有更多的语句可以交流留言