Oracle查询使用积累
１. 取当前日期
select SYSDATE from dual
2. 数值取整
select trunc(3.5) from dual //return 3
select trunc(-3.5) from dual //return -3
3. 分析函数RANK的使用
有这么一个表table1
username subject score
张三 语文 80
张三 数学 90
张三 英语 88
李四 语文 90
李四 数学 92
李四 英语 94
王五 语文 62
王五 数学 96
王五 英语 76
现在要按科目排名次，得到如下结果：
username subject score level
张三 语文 80 2
张三 数学 90 3
张三 英语 88 2
李四 语文 90 1
李四 数学 92 2
李四 英语 94 1
王五 语文 62 3
王五 数学 96 1
王五 英语 76 3
……
那么sql要这么写：select username,subject,score,RANK() OVER (PARTITION BY subject ORDER BY score desc) level from table1
只取每门科目头3名的记录：select * from (select username,subject,score,RANK() OVER (PARTITION BY subject ORDER BY score desc) level from table1) where level<=3
RANK() OVER (PARTITION BY subject ORDER BY score desc) 的意思是按照subject分组对score进行排序
和RANK类似的还有DENSE_RANK，只是遇到排名相同的情况，RANK有跳跃，DENSE_RANK没有跳跃
4.和NULL有关的函数
NVL(Expr1,Expr2)如果Expr1为NULL，返回Expr2的值，否则返回Expr1的值(注：这个类似于Sql Server的isnull)
NVL2(Expr1,Expr2,Expr3)如果Expr1为NULL，返回Expr2的值，否则返回Expr3的值
NULLIF(Expr1,Expr2)如果Expr1和Expr2的值相等，返回NULL，否则返回Expr1的值
5.Oracle 10g里取当前会话的Session ID(SID)
SELECT USERENV('SID') FROM DUAL
6.Oracle里blob和bfile的区别
blob和bfile都是存取二进制内容的类型，它们的区别是：
BLOB是把数据存到数据库,然后建个指针指向它
BFILE不把数据存到数据库,直接创建一个文件指针,指向要保存的文件
7.随机取头N条纪录
select * from (select * from tablename order by dbms_random.value) where rownum<=n
8.分析函数FIRST_VALUE、LAST_VALUE
FIRST_VALUE、LAST_VALUE是两个分析函数，返回结果集中排在第一位和最后一位的值。语法是：FIRST_VALUE (expr) OVER ( analytic_clause)
如：select t.*,first_value(username) over(partition BY organid order by userid asc) from tbuser t
select t.*,first_value(username) over(order by userid asc) from tbuser t
9.查询Oracle的版本
select * from product_component_version
返回结果：
| BANNER |
|---|
| Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod |
| PL/SQL Release 10.1.0.2.0 - Production |
| CORE 10.1.0.2.0 Production |
| TNS for 32-bit Windows: Version 10.1.0.2.0 - Production |
| NLSRTL Version 10.1.0.2.0 - Production |
10.rpad和lpad函数
格式：
RPAD( String,length [,'set'])
LPAD( String,length [,'set'])
用途：
RPAD是允许在列的右边填充一组字符set(不设置set的话，默认填充空格)，使列String的长度达到length。LPAD功能类似，不同的是，LPAD是填充到列的左边。
举例：
select rpad(field1,35,'.'),code from table1
18.intersect运算
返回查询结果中相同的部分
exp:各个部门中有哪些相同的工种
select job from account
intersect
select job from research
intersect
select job from sales;
19.minus运算
返回在第一个查询结果中与第二个查询结果不相同的那部分行记录。
有哪些工种在财会部中有，而在销售部中没有？
exp:
select job from account
minus
select job from sales;
内容不断增加中……
| PRODUCT | VERSION | STATUS |
|---|---|---|
| NLSRTL | 10.1.0.2.0 | Production |
| Oracle Database 10g Enterprise Edition | 10.1.0.2.0 | Prod |
| PL/SQL | 10.1.0.2.0 | Production |
| TNS for 32-bit Windows: | 10.1.0.2.0 | Production |
或
SELECT * FROM V$VERSION
返回结果：
20 查询日期字段大于某个日期值
select * from mytable where date_field > to_date('2014-01-01','yyyy-mm-dd');
21 varchar转换成number类型
TO_NUMBER(VARCHAR2 String)
22.clob转string
select dbms_lob.substr(field1) from table1;
select dbms_lob.substr(field1,4000,1) from table1;
