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内部自己转

    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;
0%