Oracle SQL

用于记录工作中用到的oracle sql语句

表新增字段

表结构变动： 新增字段并指定字段类型

copy
-- 新增一个字段,多个字段用小括号括起来,逗号分隔
alter table 表名 add 新增字段名(类型+长度);

-- demo  TSALES_APPLY_COST表中新增一个字段(CAPITAL) 类型为: NUMBER(15, 2)
alter table TSALES_APPLY_COST add CAPITAL NUMBER(15, 2);
-- 记得加注释
COMMENT ON COLUMN TSALES_APPLY_COST.CAPITAL is '剩余本金';

表修改字段类型

表结构变动：修改字段类型

copy
ALTER TABLE TSALES_APPLY_MAIN
    MODIFY (
        ASQQD VARCHAR2(6),
        SALESNAME VARCHAR2(128)
        );
COMMENT ON COLUMN TSALES_APPLY_MAIN.ASQQD IS '建立渠道';
COMMENT ON COLUMN TSALES_APPLY_MAIN.SALESNAME IS '客户经理';

表修改字段名

表结构变动： 修改表中的字段名

copy
-- 修改 TSALES_OTHER_DEBTORS 表中 INF_OLD_TYPE 字段名为 INFO_ID_TYPE。
alter table TSALES_OTHER_DEBTORS rename column INF_OLD_TYPE to INFO_ID_TYPE;

表删除字段

copy
ALTER TABLE 表名 DROP COLUMN 列名;

查询sql-日期加减月份

日期字段新增几个月或者减去几个月

copy
-- dual是虚拟表,用来构成select的语法规则
-- 用虚拟表测试, 获取当前日期和当前日期减去十二月的日期
select sysdate,add_months(sysdate,-12) from dual;
-- DZZRQ新增一个月的日期
update TSALES_REPAY_PLAN set DZZRQ=add_months(DZZRQ, 1) where ASQBH='2020-A000000-009-00017';

按时间倒序,取60条数据

按时间倒序排列，取60条数据

copy
select CREATE_DATE,asqbh, transfer_no, status, notice_status, file_status
from (
         select CREATE_DATE, asqbh, transfer_no, status, notice_status, file_status
         from T_MAIN_INFO
         where FILE_STATUS = '0'
           and STATUS in ('1', '2', '5')
    order by CREATE_DATE desc
     )
where rownum<61;

trunc函数

trunc函数使用

copy
-- demo1： 对dual测试表中的当前日期进行截取
select trunc(sysdate, 'DDD') from dual;

-- 开发中的实例
select SRCBILLCODE from TPAYMENT_POOL
where ZANCUNPICIHAO = #{batchNo,jdbcType=VARCHAR}
    and AGREED_LOAN_DATE is not null
    and trunc(AGREED_LOAN_DATE, 'DDD') - trunc(#{loanDate,jdbcType=DATE}, 'DDD') <> 0

ps: &lt; &gt; 是小于大于号 == <>

to_date() 函数

to_date() 函数 , 当前日期转换，sysdate可以替换成别的日期

copy
select to_date(sysdate) from dual;

merge into用法

以dual为中间表用来存数据, 进行后期的更新和插入操作

copy
merge into GUOYIN_FILE_LOG_INFO info
        using (select 'orderNo' as ORDER_NO from DUAL) du on (info.ORDER_NO = du.ORDER_NO)
        when matched then
            update set AA='aa', BB='bb', UPDATE_DATE=sysdate
        when not matched then
            insert(ORDER_NO,ALL_SUCCESS,REGIST_CERTIFICATE,UPDATE_DATE)
            values (#{orderNo,jdbcType=VARCHAR}, #{allSuccess,jdbcType=VARCHAR},  sysdate)

list集合遍历查询，返回满足条件的集合

copy
mapper:
 /**
 *获取满足集合的数据
 *
 * @param fileNameList
 * @return
 */
List<String> queryByFileNameList(@Param("fileNameList") List<String> fileNameList, @Param("orderNo") String orderNo);

 <!--查询满足数组的数据-->
 <select id="queryByFileNameList" resultType="string">
     select
       FILE_NAME
     from GUOYIN_GET_FILE_LOG_INFO
     <where>
         ORDER_NO=#{orderNo}
         <if test="fileNameList != null">
             and FILE_NAME in
             <foreach collection="fileNameList" item="fileName" separator="," open="(" close=")">
                 #{fileName}
             </foreach>
         </if>
     </where>
 </select>