Oracle符号

​ || 字符串拼接 例如：

sql
1
'abc' || 'def' = 'abcdef'

​

sql
1
row_number() over (partition by a order by b)

Oracle中DUAL用法

sql
1
2
SELECT SYSDATE FROM DUAL	//查询当前系统时间
SELECT USER FROM DUAL		//查询当前用户

Oracle中NVL用法

sql
1
nvl(str1,str2)

​ 如果str1为null，就返回str2，否则返回str1

Oracle中NVL2用法

sql
1
nvl2(str1,str2,str3)

​ 如果str1为null，就返回str3，若str1不为null，则返回str2

Oracle中CASE用法

sql
1
2
3
4
5
6
SELECT u.*,
		(CASE
			WHEN u.isCredit='Y' THEN '是'
			WHEN u.isCredit='N' THEN '否'
			ELSE ''
		END) as isExist FROM users u WHERE u.id=100026

​ 注意： 等于 =
​ 不等于 <>
​
​

Oracle中DECODE用法

sql
1
DECODE(value,if1,then1,if2,then2,if3,then3,......,else)

​

sql
1
2
3
4
SELECT DECODE(u.isCredit,'Y','是','否') AS isExist 
	FROM users u WHERE u.id = 100026

select decode(sign(1-2),-1,1,2) from dual	//比较大小（获得小值）

Oracle中SIGN用法

​ sign函数返回一个数字的正负标志
​ 语法如下：sign( number )
​ number 要测试标志的数字.

sql
1
2
3
4
5
6
7
8
9
If number < 0, then sign returns -1.
If number = 0, then sign returns 0.
If number > 0, then sign returns 1.
sign(-23)	return -1
sign(-0.001)return -1
sign(0)		return 0
sign(0.001)	return 1
sign(23)	return 1
sig(23.601)	return 1

​

Oracle分页

sql
1
2
3
4
5
6
7
8
select * from 
(
	select A.*,ROWNUM rn from 
	(
		select * from tableName
	) A 
	where ROWNUM <=20
)where rn >=10

​

sql
1
2
3
4
5
6
7
select * from 
(
  select a.*,rownum rn from
  (
	select * from USERS order by id desc
  )a
)b where b.rn >= 1 and b.rn <=100;

Oracle左联接

sql
1
2
3
4
SELECT u.name AS UserName 
		FROM application a 
		LEFT JOIN users u ON u.id = a.followup_user_id
		WHERE a.application_no = '10512248'

Oracle批处理

sql
1
2
3
4
insert into students 
	   select id,name ,email ,sdate from dual union 
	   select id,name ,email ,sdate from dual union 
	   select id,name ,email ,sdate from dual