时间:2021-07-01 10:21:17 帮助过:59人阅读
2. date +-1 天, date +- 1/24, date - date 差别天数
3. 比较重要函数, substr, concat, length, lpad|rpad, trim, replace, round, trunc, mod, nvl, nvl2
4. case, decode 注意: decode 中可以有 select 语句
用法:
简单case
select
case sex --列名字
when ‘1‘ then ‘男‘
when ‘2‘ then ‘女‘
else ‘其他‘
end
搜索case
select
case -- 等同于 (case true)
when sex = ‘1‘ then ‘男‘
when sex = ‘2‘ then ‘女‘
else ‘其他‘
end
case 语句不同位置:
在 where 条件中~
select t2.*, t1.*
from t1, t2
where (case
when t2.compare_type = ‘a‘ and t1.some_type like ‘nothing%‘ then 1
when t2.compare_type !=‘a‘ and t1.some_type not like ‘nothing%‘ then 1
else 0
end) = 1
case还可以出现在group by语句中来作为分组的条件
5. timestamp, timestamp with local time zone, timestamp with timezone 等 类型的使用场合
6. 各种 object 的创建方法, db link, constraint, view, sequence, index, synonym 等等
7. 高级 group 函数分组 (rollup, cube, grouping)
8. 子查询相关 (嵌套子查询, 相关子查询, exists)
9. 树型结构查询语句(start with connecty by prior condition)
10.insert 多行语句
11.blog for plsql 的总结
12.book: plsql programming
13.with语句
with home as (select value home from v$diag_info where name = ‘ADR Home‘ ) select name, case when value <> home.home then replace(value, home.home, ‘$home$‘) else value end value from v$diag_info, home /
SQL 总结
标签: