oracle学习记录

爱被打了一巴掌 2022-06-04 08:15 225阅读 0赞

1、oracle函数

1.1、row\_number() over(partition by customerid order by currentdate) （解释：按照customerid 字段进行分类，然后按照currentdate字段进行排序，最后给返回的行数进行编号）

create table test1(
    customerid varchar2(10) not null,
    currentdate varchar2(20) not null
    )
    select * from  test1 for update; 
    select row_number() over(partition by customerid order by currentdate),
           customerid,
           currentdate
      from test1;
    drop table test;

效果图如下：

2、oracle输出异常信息

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;"><code>DBMS_OUTPUT.put_line(</code><code>'sqlcode : '</code>&nbsp;<code>||sqlcode);</code></td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td style="vertical-align:top;"><code>DBMS_OUTPUT.put_line(</code><code>'sqlerrm : '</code>&nbsp;<code>||sqlerrm);</code></td> 
  </tr> 
 </tbody> 
</table>

3、关于oracle的行转列

行转列包括行转列、列转行、多列转换成字符串、多行转换成字符串、字符串转换成多行和字符串转换成多列六种情况。

SELECT REGEXP\_SUBSTR('1,2,3,4,5,6', '\[^,\]+', 1, LEVEL, 'i') AS order\_id  
  FROM DUAL  
CONNECT BY LEVEL <= 6;  
SELECT REGEXP\_SUBSTR('17,20,23', '\[^,\]+', 1, 1, 'i') AS STR FROM DUAL;

SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 3;

SELECT COLUMN\_VALUE FROM TABLE(SYS.ODCIVARCHAR2LIST(1, 2, 3, 4, 5));

4、关于oracle的去除空格

SELECT TRIM(' dd df ') FROM dual; 去除前后空格  
SELECT TRIM(leading '\#' FROM '\#123\#') FROM dual;去除前面的‘\#’字符  
SELECT TRIM(trailing '\#' FROM '\#123\#') FROM dual;去除后面的‘\#’字符  
SELECT TRIM(both '\#' FROM '\#123\#') FROM dual;去除前后面的‘\#’字符

![Center][]

![Center 1][]

[Center]: /images/20220604/3ba25fb8bc3b489299e4326dcf7db4cc.png
[Center 1]: /images/20220604/8202b64da96048308c48612eace68b09.png