oracle sql 笔记

分页sql:

select b.*  from (

       select a.*, rownum rn from t_dup  a where rownum< = maxRowNum

) b where b.rn > minRowNum

 maxRowNum:最大的数据条数

 minRowNum:最小的数据条数

 

dbms_random：

dbms_random是一个可以生成随机数值或者字符串的程序包
最常用的方法
dbms_random.value():返回的是number类型，并且返回的值介于1和0之间,并实现排序
dbms_random.random():返回的是BINARY_INTEGER类型

 

TRUNC：
对数字
TRUNC（89.985，2）=89.98
TRUNC（89.985）=89
TRUNC（89.985，-1）=80
对日期：
TRUNC(TO_DATE('1999-12-25','yyyy-mm-dd'),'yyyy')=  1999-1-1
TRUNC(TO_DATE('1999-12-25','yyyy-mm-dd'),'mm')=  1999-12-1   
TRUNC(TO_DATE('1999-12-25','yyyy-mm-dd'),'dd')=  1999-12-25

 

decode：
decode(a.q, 1, a.bal, 2,b.bal,0....) :如果a.q的值为1则显示a.bal，为2则显示b.bal,否则为0

 

row_number() over():
select username,mobile,row_number() over (order by mobile) as num from SYS_USER，其结果为
username mobile  num
tt             2               1
rr             4               2
ee           4               3
cc            8               4
tt 1          23              5
根据mobile的值排序,得到排序的行号
row_number() over (PARTITION BY userid order by mobile)根据主键userid分组。这样得到的结果

全部为1

 

rank() over():
该函数和row_number()over()函数的使用时相同的，主要区别为rank表示等级。举例

select username,mobile,rank() over (order by mobile) as num from SYS_USER，其结果为
username mobile  num
tt             2               1
rr             4               2
ee           4               2
cc            8               3
tt1          23              4

 

start with...connect by prior:
其基本语法是：
select ... from tablename start with 条件1
connect by 条件2
where 条件3;
条件1 是根结点的限定语句，当然可以放宽限定条件，以取得多个根结点，实际就是多棵树。
条件2 是连接条件，其中用PRIOR表示上一条记录，比如 CONNECT BY PRIOR org_id = parent_id就是

说上一条记录的org_id 是本条记录的parent_id，即本记录的父亲是上一条记录。
条件3 是过滤条件，用于对返回的所有记录进行过滤。

 

sys_connect_by_path（column,';'）:
该函数必须和connect by连用，该函数实现将根据父节点条件得到column内容，并以;的格式分开的形

式显示出来

 

substr:
substr('This is a test', 6, 2) would return 'is'
substr('This is a test', 6) would return 'is a test'
substr('TechOnTheNet', -6, 3) would return 'The'

 

instr:

我们经常这样使用：从一个字符串中查找指定子串的位置

select instr('yuechaotianyuechao','ao') position from dual;---6
从第7个字符开始搜索
select instr('yuechaotianyuechao','ao', 7) position from dual;--17
从第1个字符开始，搜索第2次出现子串的位置
select instr('yuechaotianyuechao','ao', 1, 2) position from dual;--17
从倒数第1个字符开始，搜索第2次出现子串的位置
select instr('yuechaotianyuechao','ao', -1, 2) position from dual;--6

 

insert all:

insert all
when 条件1 then
into 表 values(值)
when 条件2 then
into 表 values(值)
select col from 表
按条件插入不同的表中

 

merge into:

USING (SELECT 'the code' code, 'the name' NAME FROM dual) b
ON (a.code = b.code)
WHEN matched THEN   //如果匹配则更新
  UPDATE SET a.NAME = b.NAME
WHEN NOT matched THEN //否则插入数据
  INSERT (code, NAME) VALUES (b.code, b.NAME);
merge into 解决用B表跟新A表数据，如果A表中没有，则把B表的数据插入A表，使用与大数据量

 

Lag和Lead：

Lag和Lead函数如可以在一次查询中取出同一字段的前N行的数据和后N行的值

l如：

select card_code,bal,lead(bal,1) over(partition by card_code order by bal)  as change_val from  T_CHANGE_LC

card_code  bal  change_val

001             14              40

001             40              48

001             48              49

001             49

002             20              51 

002             51              58

002            58

 

select card_code,bal,lag(bal,1) over(partition by card_code order by bal)  as change_val from  T_CHANGE_LC

card_code  bal  change_val

001             14             

001             40              14

001             48              40

001             49             48

002             20 

002             51              20

002            58             51