oracle存储过程及常用语法糖

小鱼儿 2024-04-18 10:49 80阅读 0赞

# 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等)连用。
    
    <table> 
     <thead> 
      <tr> 
       <th align="center">student_id</th> 
       <th align="center">subject_id</th> 
       <th align="center">score</th> 
      </tr> 
     </thead> 
     <tbody> 
      <tr> 
       <td align="center">1</td> 
       <td align="center">1</td> 
       <td align="center">90</td> 
      </tr> 
      <tr> 
       <td align="center">1</td> 
       <td align="center">2</td> 
       <td align="center">85</td> 
      </tr> 
      <tr> 
       <td align="center">2</td> 
       <td align="center">1</td> 
       <td align="center">80</td> 
      </tr> 
      <tr> 
       <td align="center">2</td> 
       <td align="center">2</td> 
       <td align="center">95</td> 
      </tr> 
     </tbody> 
    </table>
    
        --传统的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;