按字符截取字符串，常用语特殊格式字符串中寻找需要子串：instr。下面为对应API

instr( string1, string2, start_position,nth_appearance )

string1 - 源字符串，要在此字符串中查找

string2 - 匹配字符串，要在string1中查找的字符串

start_position - [可选参数] 从string1的哪个位置开始查找。若省略，默认为1。若参数为正，从左到右检索。若为负，从右到左，返回要查找的字符串在源字符串中的开始索引

nth_appearance - [可选参数] 需要查找第几次出现的sting2。若省略，默认为1。若为负，系统会报错。

判空函数 nvl(string1,string2)。 如果sting1空，则返回string2

to_date()、to_char(string,’YYYY-MM-DD’)、to_timestamp()、to_number()、trim()、lower()、upper()、ascii()、chr()

保留两位小数：cast(colume as number(10,2))

分支判断函数 decode(value,if1,then,if2,then,….,else)。表示如果value 等于if1时，DECODE函数的结果返回then1,…,如果不等于任何一个if值，则返回else。

转意函数 escape,当转义符置于通配符之前时，该通配符就解释为普通字符。

ESCAPE ‘escape_character’ 允许在字符串中搜索通配符而不是将其作为通配符使用。escape_character 是放在通配符前表示此特殊用途的字符。
select * from a WHERE name LIKE ‘%aa/%bb%’ ESCAPE ‘/‘

P.S:第一个和最后一个%作为通配符，中间的%作为普通字符。

q操作符，原样输出。 示例： select q’#Oracle’s quote operator#’ from dual;

ORACLE中null的问题

• oracle中null的定义：表示未知的值，需要定性判断而非定值。
• 重点 子查询结果中有null，父查询 not in 的该查询值时结果为空。原因：任何数跟null比较都是null。
• 对于null在查询中的位置可以特殊指定：null first 、null last 。在null fist \ last 查询中，同为null条目的排序按照rowid排序，数据块在内存中的未知。
• ‘ ‘就是null。
• 主键不可以为空，但唯一索引允许有多个null。因为null跟null不一样。

ORACEL中常用语法糖

• merge:insertOrUpdate 存在就update否则insert,或者放弃

• with as做一个临时表，然后再去细化查询。抽出公共部分，内存表。不会持久化，但是用的爽。

• in 和 exists 区别 9i开始 in和exsit一样了 oracle内部自己转

  1 2	select * from t1 where exists (select 1 from t2 where t1.a=t2.2); select * from t1 where t1.a in (select ta2. from t2);

• between用法： select * from test where id between 2 and 12 ;

• 树形查询。目录领导层级。父子关系数据。start with connect by prior pid = id (或者是id=pid)

• union all 和union 指令的目的是将两个 SQL 语句的结果集合并起来,得到你所需要的查询结果

  Union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序；

  Union All：对两个结果集进行并集操作，包括重复行，不进行排序；

  1 2 3

  select * from student where id < 4 union select * from student where id > 2 and id < 6

  结果为：1、2、3、4、5。

  而用 union all则是 1、2、3、3、4、5。

• oracel窗口函数：

  窗口函数的名字是over()函数，常用的有两个属性partition by和order by，partition by类似于group by，我们通常将group by叫做分组，而partition by称作分区，分组返回通常是一行，partition by返回的是多行。

  窗口函数的威力是与其他函数联合使用,窗口函数主要和统计函数,排名函数,错行的函数联合使用.。
  例如：和统计函数(max,min,avg,sum等)连用。

  1 2 3 4 5 6

  | student_id | subject_id | score | | :--------: | :--------: | :---: | | 1 | 1 | 90 | | 1 | 2 | 85 | | 2 | 1 | 80 | | 2 | 2 | 95 |

  1 2 3 4 5 6 7 8 9 10

  --传统的group by 来查出每门功课的平均分,然后查出差值 select t1.*, (t1.score - t3.avgs) as gaps from test_student_score t1, (select t2.subject_id, avg(t2.score) as avgs from test_student_score t2 group by t2.subject_id) t3 where t1.subject_id = t3.subject_id; --使用over 窗口函数 select t.*, (t.score-avg(t.score) over( partition by t.subject_id)) as gaps from test_student_score t

  ORACLE存储过程常用语法

  • 初识存储过程

    在PL/SQL工具的Procedures包中可以看到当前数据库中所有的存储过程；右键可以查看或者编辑。也可以新建自己的存储过程；在新建存储输入名称和入参出参后。可以先用dbms_output.put_line(‘hello world’)进行文字打印，体验整个存过的编译，测试执行等流程。

  • 存储过程中的FOR循环，IF分支判断等。

    1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

    ------------------------------循环----------------------------------- ---------------while循环 while 条件 loop begin --需要执行的语句; end; end loop; ----------------for循环 for DATA in (select语句) loop begin --该循环体中可以使用 DATA.变量 来操作select中查出的内容 end; end loop; ------------------------------判断---------------------------------- if 条件 then --满足条件执行 else --不满足执行 end if;

    ORACLE中常见错误

    ORA-06550 ：Statement ignored，ORA-00306 参数或者类型错误

    • 首先是因为调用存储过程时，调用失败引起的。造成该问题原因：可能你的存过没编译？存过的拥有者与连接数据库用户不是一个？或者是传参个数，类型不符合出入参要求。

    ORA-00428: an into clausee is expected in this select statement

    • 解决方案：在select 查询值后加into。

    ORA-01403：未找到任何数据

    • 通常都是select into 语句查询结构为空的原因。解决方案：先select count(1) 去判断有没有值再去进行select into。

    ORA-06502:数字或值错误：字符串缓冲区太小

    • 通常都是因为想要把超长的字符串赋给一个限定长度的字符串变量。（官方解释：想存入数据库的数据不符合该字段的定义[比如：长度，约束等]）。解决方案：改变数据的类型，或长度。

      But，如果定义一个变量varchar2(4000)，在赋值的时候超长怎么办呢？

    • 首先存过中的varchar2(32767)，可以定义32767个字符长
    • 其次可以采用CLOB来存储你的大文本

    ORA-01704:文字字符串过长

    • 感觉和上一次错挺像的，通常都是使用length()、substr()这些ORACLE自带函数时，入参过长造成的。

      ORACLE默认会把这些函数入参的字符串装换成varchar2类型，而入参的这个字符串的长度又比4000大，所以出错。

      ​ 解决方案：

      1 2 3 4 5

      declare v_clob clob = '一个长字符串'; begin select length(v_clob) from dual; end;