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%