oracle 记录

 to_char

select to_char(sysdate,'YYYY"年"MM"月"DD"日"') tochar from dual;
select to_char(sysdate,'yyyy-MM-dd HH24:mi:ss') tochar from dual;

oracle查询与MySQL不一样要切记时间格式的指定

to_date

select to_date('2018-02-23 15:33:21','yyyy-MM-dd HH24:mi:ss') todate from dual;

切记时间格式，插入操作是也可使用

lpad 函数与java中的trim类似：将字符串补足到指定长度

select lpad(to_char(id),4,'0') from dept

上图输出：0001  0002 ....。与此类似的函数还有to_number等等

listagg  行转列

有时候会碰到将多个结果拼接到一起作为一个查询列值，而wm_concat（）函数实在是不好用，如果是单个的查询拼接还好，如果在视图中使用就会报：group by exception,,,,,,,,。很难受是吧，所以在11g之后就提出了listagg函数，经过本人确认之后在不考虑性能的时候实用性比wm_concat()好用，语法：

SELECT distinct listagg(拼接的列名, '连接符') within group(order by '分组列') CONCAT
  FROM 表明/子查询

SELECT distinct listagg(k.billcode, '-') within group(order by k.contractid) CONCAT
  FROM (select distinct la.billcode as billCode,la.contractid as contractId
          from sett_transacceptnouse a
        
          left join loan_acceptcontractbill la
            on la.contractid = a.contractid ) k

如上图的SQL，将子查询K的两个查询列分别作为拼接和排序分组，子查询都能看明白：联合查询去重

decode

这个函数可是很强大的，下面做用法含义解释

含义解释：
decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)
 
该函数的含义如下：
IF 条件=值1 THEN
　　　　RETURN(翻译值1)
ELSIF 条件=值2 THEN
　　　　RETURN(翻译值2)
　　　　......
ELSIF 条件=值n THEN
　　　　RETURN(翻译值n)
ELSE
　　　　RETURN(缺省值)
END IF

如果只判断两个值，如：decode(lp.amount,null,la.amount,lp.amount)，这么写其实与nvl(lp.amount,la.amount)是一样的结果，decode的强大之处在于多值的判断筛选，如果有三个值要进行判断可以与nvl函数混用，例如：

nvl(decode(lp.amount,null,la.amount,lp.amount),lo.amount) as v_amount

select ......into    from table_name;

select f1,f2,f3 into v1,v2,v3 from tab1

切记：一个select语句只能有一个into关键字，如果多列赋值参考上面的SQL

row_number() over 函数用法

去重之前数据：

2，添加标记，请注意row_number()over函数用法

select a.id            as "accountId",
       a.accountno     as "accountNo",
       a.clientid      as "clientId",
       sat.accounttype as "accountType",
       a.originid      as "originId",
       vc.CLIENTCODE       as "clientCode",
       sat.accountgroup    as "accountGroup",
       sat.accounttypecode as "accountTypeCode",
       a.status            as "accountStatus",
       
       row_number() OVER(PARTITION BY a.accountno ORDER BY a.modifydate desc) as row_flg

  from sett_account a
  left join sett_accounttype sat
    on a.accounttypeid = sat.id
  left join sett_accountproperty aty
    on aty.accounttypeid = sat.id
  left join v_clientinfo4sett vc
    on vc.clientid = a.clientid
   and vc.statusId != 5
 where vc.CLIENTID = 1180
   and a.originid is not null

红框中为标记

3，过滤取最新一条（按照时间排序）

select  temp.id as "accountId",
      temp.accountno as "accountNo",
       temp.clientid as "clientId",
       temp.accounttype as "accountType",
       temp.originid as "originId",
       
       temp.CLIENTCODE       as "clientCode",
       temp.accountgroup    as "accountGroup",
       temp.accounttypecode as "accountTypeCode",
       temp.status            as "accountStatus",
       temp.officeid          as "officeId",
       
       temp.currencyid as "currencyId",
       temp.acountname as "accountName"
  from (select a.id,
               row_number() OVER(PARTITION BY a.accountno ORDER BY a.modifydate desc) as row_flg ， a.accountno,
               a.clientid,
               sat.accounttype,
               a.originid,
               
               vc.CLIENTCODE,
               sat.accountgroup,
               sat.accounttypecode,
               a.status,
               a.officeid,
               
               a.currencyid,
               a.acountname
          from sett_account a
          left join sett_accounttype sat
            on a.accounttypeid = sat.id
          left join sett_accountproperty aty
            on aty.accounttypeid = sat.id
          left join v_clientinfo4sett vc
            on vc.clientid = a.clientid
           and vc.statusId != 5
         where vc.CLIENTID = 1180
           and a.originid is not null) temp
 where temp.row_flg = '1'
order by temp.id 

表结构查看或者导出（需借用PL/SQL）

SELECT t.table_name,--表名称

       t.colUMN_NAME,--列名
       t.DATA_TYPE || '(' || t.DATA_LENGTH || ')',--字段类型
       t.NULLABLE,--是否可为null
       t1.COMMENTS--列注释
  FROM User_Tab_Cols t, User_Col_Comments t1
WHERE t.table_name = t1.table_name
    AND t.column_name = t1.column_name 
    
    
    and t.TABLE_NAME='表名称'

查询结果如下就是表结构