oracle存储过程及常用语法糖

oracle存储过程及常用语法糖

oracle常见函数与处理

• 字符串切割：substr 、substrb 。下面为对应API

  substr(string,start,length)

  string - 指定的要截取的字符串；

  start - 必需，规定在字符串的何处开始 正数 - 在字符串的指定位置开始 负数 - 在从字符串结尾的指定位置开始 0 - 在字符串中的第一个字符处开始，

  length - 可选，指定要截取的字符串长度，缺省时返回字符表达式的值结束前的全部字符。

  ！substrb用法与substr相同，但是为按位截取

• 从左拼接字符串或从右拼接字符到指定长，常用为序列号生成：lpad、rpad。下面为对应API

  lpad(string,n,[pad_string])

  string - 原字符串；

  n - 返回字符串的总长度，如果这个数量比原字符串的长度要短，lpad函数将会把字符串截取成从左到右的n个字符。如果比原字符串长，那么在string左边添加[pad_string]字符；

  pad_string - [可选参数] 这个字符串是要粘贴到string的左边。若省略，lpad将会在string的左边粘贴空格。

  示例：lpad(‘tech’, 2); 将返回’te’ 、 pad(‘tech’, 8, ‘0’); 将返回’0000tech’

• 按字符截取字符串，常用语特殊格式字符串中寻找需要子串：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内部自己转

  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：对两个结果集进行并集操作，包括重复行，不进行排序；

  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等)连用。

  student_id	subject_id	score
  1	1	90
  1	2	85
  2	1	80
  2	2	95

  --传统的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分支判断等。

  ------------------------------循环-----------------------------------
  ---------------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大，所以出错。

​ 解决方案：

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