Oracle的一些常用语法与函数（杂记）

__________________________________________________________________


nvl(列,结果为空时的默认值)

__________________________________________________________________


decode(列，值，匹配时执行语句，不匹配时执行语句) //相当于三目表达式

__________________________________________________________________


trunc(列，保留位数) //小数点保留

__________________________________________________________________

//merge into 可以起到 save or update的作用
//update的列和on不能重复，否则会报ORA-38104(无法更新ON子句中引用的列)错误
merge into temp_2010091011 L
using (select 'test' ss from dual) N
on (L.test = n.ss)
WHEN MATCHED THEN
  UPDATE set temp = 'tempif'
WHEN NOT MATCHED THEN
  INSERT (test,temp) values ('test','tempelse');

__________________________________________________________________


//查询树形或拓扑结构时用到
select DEPT_ID as value, DEPT_NAME as lable, '' as filter
  from RS_DEPT
where DEPT_LEVEL_FLAG in (1, 2, 3) //过滤遍历结果
start with DEPT_ID = 1000000001 //设置开始节点的值
connect by prior DEPT_ID = UPPER_DEPT_ID //设置遍历条件（上下级相反）
order by ORDER_CODE //结果集排序

__________________________________________________________________