Oracle部分特殊写法

Oracle特殊写法

前n行

• 使用ROWNUM关键字，同mysql里的limit

  #获取前十行
  select * from table where ROWNUM<=10;
  

时间字段筛选

• Oracle中比较时间需要使用自带的to_date(str,fmt)函数

  #使用两个时间参数筛选结果
  SELECT WELL_ID,SPUD_DATE FROM CD_WELL_SOURCE Where SPUD_DATE >= TO_DATE('1987-12-10 00:00:00', 'yyyy-MM-dd hh24:mi:ss') AND SPUD_DATE < TO_DATE('1999-12-10 00:00:00','yyyy-MM-dd hh24:mi:ss') AND ROWNUM<=10;
  

oracle设置主键自增列

• 新建测试表

  create table member(
  memberId number primary key,
  memberMail varchar2(20)not null,
  memberName varchar2(20) not null,
  memberPassword varchar2(20)
  );
  

• 需要一个自定义的sequence（类似自增函数）

  CREATE SEQUENCE test_sequence
  INCREMENT BY 1 -- 每次加几个
  START WITH 1 -- 从1开始计数
  NOMAXVALUE -- 不设置最大值
  NOCYCLE -- 一直累加，不循环
  NOCACHE -- 不建缓冲区
  

• 最后需要一个触发器来执行它

  create trigger emp_trig（触发器名称） before
  insert on member（表名） for each row when (new.memberId is null)
  begin
  
   select emp_sequence（序列名称）.nextval into:new.memberId（自增字段） from dual;
  
   end;
  

oracle函数

• 介绍常用的oracle函数

dual表介绍

• 不同于mysql中直接获取常数的sql写法。oracle中必须带from表名，如果为常数，则需要写特定的dual表

  SQL> select 3 from dual;
  
  	 3
  ----------
  	 3
  SQL> select 3;
  select 3
  	   *
  ERROR at line 1:
  ORA-00923: FROM keyword not found where expected
  
  

• Dual 是 Oracle中的一个实际存在的一行一列的表，任何用户均可读取，常用在没有目标表的Select语句块中

decode()函数

• decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值)相当于case when 多重选择函数

  # 示例为user表中sex性别这一列，为0输出男，为1输出女，都不满足或为空输出未知
  select id, username, age, decode(sex,0,'男',1,'女','未知') from users;
  

(+)号用法

• 对于外连接， 也可以使用“(+) ”来表示。 关于使用(+)的一些注意事项：
  (+)操作符只能出现在WHERE子句中，并且不能与OUTER JOIN语法同时使用。
  当使用(+)操作符执行外连接时，如果在WHERE子句中包含有多个条件，则必须在所有条件中都包含（+）操作符。
  (+)操作符只适用于列，而不能用在表达式上。
  (+)操作符不能与OR和IN操作符一起使用。
  (+)操作符只能用于实现左外连接和右外连接，而不能用于实现完全外连接。

  select * from t_A a left join t_B b on a.id = b.id 等价于 Select * from t_A a,t_B b where a.id=b.id(+);
   同理   Select * from t_A a,t_B b where a.id(+)=b.id; 是右连接
  

|| 的用法

• 相当于contact(),将前后两个字符串拼接起来

  # 可以拼接常数，也可以拼接列
  SQL> select 'aaa' || ',' || 'bbb' || ',' || 'ccc' from dual;
  
  'AAA'||','|
  -----------
  aaa,bbb,ccc
  
  

nvl()函数

• 非空函数，NVL(eExpression1, eExpression2)，如果表达式1为空，则返回表达式2。 一般用于数值类型，为空返回0

  # 学生表id为空返回0
  select NVL(id, 0) id1 from student;
  

tochar()函数

• 功能是将数值型或者日期型转化为字符型 所有格式化函数的第二个参数是用于转换的模板。
• 详见tochar大全

substr()函数

• 字符串截取函数，应该很熟悉了

  SQL> select substr('adbdfg',1,4) from dual;
  
  SUBS
  ----
  adbd
  
  SQL> select substr('adbdfg',2) from dual;
  
  SUBST
  -----
  dbdfg
  
  

• 格式1： substr(string string, int a, int b);
  1. string 需要截取的字符串
  2. 截取字符串的开始位置（注：当a等于0或1时，都是从第一位开始截取）
  3. b 要截取的字符串的长度
• 格式2：substr(string string, int a) ;
  1. string 需要截取的字符串
  2. a 可以理解为从第a个字符开始截取后面所有的字符串。

instr()函数

• 字符型查找函数

• 格式一：instr( string1, string2 ) // instr(源字符串, 目标字符串)

• 格式二：instr( string1, string2 [, start_position [, nth_appearance ] ] ) // instr(源字符串, 目标字符串, 起始位置, 匹配序号)
  解析：string2 的值要在string1中查找，是从start_position给出的数值（即：位置）开始在string1检索，检索第nth_appearance（几）次出现string2。
  注：在Oracle/PLSQL中，instr函数返回要截取的字符串在源字符串中的位置。只检索一次，也就是说从字符的开始到字符的结尾就结束。

  1 select instr('helloworld','l') from dual; --返回结果：3    默认第一次出现“l”的位置
  2 select instr('helloworld','lo') from dual; --返回结果：4    即“lo”同时出现，第一个字母“l”出现的位置
  3 select instr('helloworld','wo') from dual; --返回结果：6    即“wo”同时出现，第一个字母“w”出现的位置
  

Round函数用法

• 截取数字函数

• 格式如下：ROUND（number[,decimals]）

• 其中：number 待做截取处理的数值

• decimals 指明需保留小数点后面的位数。可选项，忽略它则截去所有的小数部分，并四舍五入。如果为负数则表示从小数点开始左边的位数，相应整数数字用0填充，小数被去掉。需要注意的是，和trunc函数不同，对截取的数字要四舍五入。

  SQL> select round(73.2456,2) from dual;
  
  ROUND(73.2456,2)
  ----------------
  	   73.25
  
  

floor函数用法

• 作用:返回小于等于n的最大整数

  SQL> SELECT FLOOR(0.1) FROM dual;
  
  FLOOR(0.1)
  ----------
  	 0
  
  

replace函数用法

• REPLACE 函数是用另外一个值来替代串中的某个值。
  例如，可以用一个匹配数字来替代字母的每一次出现。REPLACE 的格式如下所示：
  REPLACE (char, search_string [, replace_string])
  如果没有指定replace_string 变量的值，那么当发现search_string 变量的值时，就将其删除。输入可以为任何字符数据类型——CHAR、VARCHAR2、NCHAR、NVARCHAR2、CLOB或NCLOB。

  # 实例：
  select replace('0123456789','0','a') from dual;--a123456789
  select replace('0123456789','0','') from dual;--123456789
  select replace('0123456789','0') from dual;--123456789
  
  

trunc()函数

• 日期用法

  1.select trunc(sysdate) from dual --2013-01-06 今天的日期为2013-01-06
  2.select trunc(sysdate, 'mm') from dual --2013-01-01 返回当月第一天.
  3.select trunc(sysdate,'yy') from dual --2013-01-01 返回当年第一天
  4.select trunc(sysdate,'dd') from dual --2013-01-06 返回当前年月日
  5.select trunc(sysdate,'yyyy') from dual --2013-01-01 返回当年第一天
  6.select trunc(sysdate,'d') from dual --2013-01-06 (星期天)返回当前星期的第一天
  7.select trunc(sysdate, 'hh') from dual --2013-01-06 17:00:00 当前时间为17:35 
  8.select trunc(sysdate, 'mi') from dual --2013-01-06 17:35:00 TRUNC()函数没有秒的精确
  

• 数字用法
  TRUNC（number,num_digits）
  Number 需要截尾取整的数字。
  Num_digits 用于指定取整精度的数字。Num_digits 的默认值为 0。
  TRUNC()函数截取时不进行四舍五入

  # 示例
  select trunc(123.458,1) from dual --123.4