一些oracle关键词应用整理

[,str] :代表可选。

1：decode

用法：  decode(value,search_value,result,defaultValue)

类似JAVA中的三目表达式 value==search_value?result:defaultValue ;

 

2 ： translate

用法 ： translate(x,exist_string,to_string)

例子

select translate('very good','abcdefghijklmnopqrstuvwxyz','ABCDEFGHIJKLMNOPQRSTUVWXYZ') from dual;

 

输出的结果 ： VERY GOOD；

 

就是把x 里面 有exist_string存在的字符转成to_string对应的字符，比方说存在e，e在exist_string第五位，那么取to_string的第五位字符。

 

3 : union all ： 返回查询所检索出的所有行，包括重复的行。

select id,name from student union all select id,name from more_student;

 

4: union ： 返回查询所检索出的所有非重复行。

select id,name from student union  select id,name from more_student;

 

5:intersect : 返回两个查询所检索出的共有行 .

select id,name from student intersect select id ,name from more_student;

 

6: minus  : 返回将第二个查询检索出的行从第一个查询检索出的行中减去之后剩余的行。

select id,name from student minusselect id,name from more_student;

 

7: case :

case

    when condition then result

    else default_resut

8:树查询：

 select level,employee_id,manager_id from employees start with employee_id=0 connect by prior employee_id =manager_id order by level ;

 a : start with  ...connect by prior ... 通过改查询获取一颗树的列表数据。

 b ： level ：伪列 ，代表节点在树中的层次。

 

9:分组(group by)：

grouy by的一些注意事项：如果查询中包含一个聚合函数，而所选择的列并不在聚合函数中，那么这些列就必须在group by 字句中。

rollup :

例子：select sex,count(studentid) from Student group by rollup(sex) ;

按照sex返回学生数，并在末尾返回总的学生数。

cube :和rollup类似，但是结果放在非空的sex之前，空的sex之后。

 

10：ltrim(),rtrim()和trim() :

    ltrim(x,[,trim_string]) : 从x的左边截去一些字符，可以用trim_string来指定要截去的字符，如果没有，默认截去左边的空格

   rtrim(x,[,trim_string]) : 和ltrim()类似，默认截去右边的空格。

   trim(x,[,trim_string])  ： 默认截取两边的空格。

   是java中string.trim ()的加强版

 

11：instr():

   instr(x,find_string[,strart][,occurrence])函数用于在x中查找find_string 。instr()函数返回find_string 所在得位置，改函数可以用可选参数start来说明从 x中得哪个位置开始查找，还可以使用可选参数occurrence说明应该返回find_string第几次出现得位置。

 

 12:  Oracke 10 新增的MODEL字句用来进行行间运算。MODEL字句允许像访问数组中的元素那样访问记录中的某个列。这就提供了诸如电子表格计算之类的计算能力。

 

  例子 ： 下面这个查询获取2003年内有员工#21 完成的产品类型为#1和#2的销量，并根据2003年的销售数计算出2004年一月，二月，三月的销量预测值：

 

Sql代码

 

select prd_type_id, year, month, sales_amount
  from all_sales
 where prd_type_id between 1 and 2
   and emp_id = 21 model partition by(prd_type_id) dimension
 by(month, year) measures(sales_amount)
 (sales_amount[1, 2004] = sales_amount[1, 2003], 
  sales_amount[2,2004] = sales_amount[2, 2003] + sales_amount[3, 2003], 
  sales_amount[3,2004] = round(sales_amount[3, 2003] * 1.25, 2)
 )
 order by prd_type_id, year, month;

 

partition by(prd_type_id)指定结果是根据prd_type_id分区的。

 

dimension by(month,year)定义数组的维数是month和year。这就意味着必须提供月份和年份才能访问某个列。

 

measures(sales_amount):指定做为数组的列。

 

sales_amount[月，年]，按照dimension by(month,year)的要求。

  

13：initcap(x)  : 用于将 x中的每个单词的首字母转换为大写。

 

14 :length(x) ;用于获得x中字符的个数。

 

15 ： concat(x,y) ：用于将y附加在x之后，该函数会返回得到的字符串。

 

16： lower(x) :将x中的每个字符转换为小写。

 

17： upper(x):将x中的每个字符转换为大写。

 

18：lpad(x,width [,pad_string])用于在x的左边补齐空格，使x的总长度达到width个字符，如果在pad_string参数中指定了一个字符串，那么就用这个字符串反复填充x左边的空位

 

19：rpad(x,width [,pad_string]) 和lpad(x,width [,pad_string])类似，当时在x右边填充，right。

 

20 ：nul(x,value) 如果x为空，则返回value，否则返回null.

  if(tmp==null)

{

   return value;

}

else

{

   return x;

}

21: nul2(x,value1,value2):非空判断的增强版，如果x为空，返回value2，否则返回value1；

 

22 ： substr(x,start[,length]) 和java 的substring()方法一样;

 

23 : nanvl(x,value) : oracle10g新增的一个函数。如果x匹配NaN这个特殊值(非数字)，返回value，否则返回x

 

24 ：replace(x,search_string,replace_string) :在x中查找search_string,并将其替换为replace_string