oracle数据库语法及问题总结

工作中用到的oracle语法问题

前言:以下是自己在工作中遇见的关于oracle的使用中遇见的一些语法问题。

merge into
在oracle中无法用到mysql中的关联表批量更新，插入的方式。
而oracle中有自己独有的方法

MERGE INTO 表1 t1
USING ( 表2 ) t2
ON ( t1.id = t2.id AND t1.age = t2.age ...)
WHEN MATCHED THEN UPDATE SET t1.name = t2.name, t1.sex = t2.sex ...
WHEN NOT MATCHED THEN INSERT (t1.name,t1.sex...) values (t2.name,t2.sex...)

MERGE INTO 目标表 t1
USING ( 源表 ) t2 --其中表2可以是一个表也可以是一个子查询结果集。
ON ( t1.条件字段1 = t2.条件字段1 AND t1.条件字段2 = t2.条件字段2 ...) 
WHEN MATCHED THEN UPDATE SET t1.字段1= t2.字段1, t1.字段2= t2.字段2... 
--when matched then 后面的语句是关联成功后将对目标表中对用数据进行的修改。
WHEN NOT MATCHED THEN INSERT (t1.条件字段1,t1.字段1...) values (t2.条件字段1,t2.字段1...)
--when not matched then 后面的语句是在源表中并未关联到目标表的相关数据，通常在这种情况我们可以不进行任何操作或者进行新数据的插入。

注：
1.在 merge into 的使用过程中作为 on 中的条件的字段将不允许在下面被关联到时使用，未被关联到可以是可以使用条件字段进行赋值等操作。
2.在使用过程中我们不要尽量将表的别名标注清楚。
3.merge into 不可以对 t2 (源表) 中的数进行修改。
4.on中的条件，作为两张表的关联关系可以用多个and连接，但是不能用or进行连接。（工作中遇见的坑）若用or连接两个条件只能进行更新，如果进行插入将会导致sql插入大量数据，sql会跑很久很久！！！

for update

SELECT * FROM 表1 FOR UPDATE 

for update 作为查询的行级锁。

以下是个人理解：
在使用这句话时将对查询的数据上锁，这些数据将不允许被修改，直到这次查询的事务提交。
（这个语法在PL/SQL中使用时生效，但是在我电脑中的SQL Developer 中未生效，原因不明。）

greatest( expr1, expr2, … expr_n ) --最大值
least( expr1, expr2, … expr_n ) -最小值
查询一行数据多个列中的最大，最小值。

另需注意参数可能具有混合数据类型。具体规则如下：参考博客连接

1.如果任何参数为null，则两个函数都将立即返回 null ，而不进行任何比较。
2.如果为int或real 上下文中使用函数，或者所有参数都是整数值或real值，那么他们将分别为int和real来比较。
3.如果参数由数字和字符串组成，则函数将它们作成数字进行比较。
4.如果至少一个参数是非二进制(字符)字符串，则函数将将参数作为非二进制字符串进行比较。
5.在所有其他情况下，函数将参数作为二进制字符串进行比较

查询当前的数据库连接并杀死

select sid,serial# 
from v$session where username='登陆的用户名'; --查询当前连接的session
alter system kill session 'sid,serial#' ; --杀死这个session

当执行一个错误sql，是用这种方式可以杀死这个连接。

sql中的非空判断
关于数据库中字段值为空时，用这个值做判断将都返回为空，
解决方法通过使用nvl的方法进行避免。

在工作中我遇见了一个问题，我写了一个判断

select name
from teacher
where age != '20'

如果数据库中某一列中age这个字段中没存相应的值，那么这条数据就不会被查询出来。
当age 值为 null 那么不管 age != ‘20’ 还是 age == ‘20’ 都会返回false

如果遇见这个问题要不然就建表的时候赋给默认值，或者在使用这个字段是外面包裹nvl()方法。

数据脱敏操作

update 表 set 字段1 = regexp_replace(字段1, '[^\x00-\xft]','*',2), --字段1内容为汉字
字段2 = regexp_replace(字段2, '[a-zA-Z0-9]','*',4), --字段1内容为数字、字母

查询所有数据库
参考链接
由于Oralce没有库名,只有表空间,所以Oracle没有提供数据库名称查询支持，只提供了表空间名称查询。

select * from v$tablespace;    --查询表空间(需要一定权限)

查询当前数据库中所有表名

select * from user_tables;

查询指定表中的所有字段名

select column_name from user_tab_columns where table_name = 'table_name';    
--表名要全大写

查询指定表中的所有字段名和字段类型

select column_name, data_type from user_tab_columns where table_name = 'table_name';   
 --表名要全大写

oracle分组后排序取第一条

select t2.* 
from (
	select 
		t.*, 
		ROW_NUMBER() OVER(PARTITION BY 分组字段 ORDER BY 排序字段 ASC ) AS rNum 
	from 目标表 t
	where 条件 )
where rNum = 1

注意其中rNum是代表每组的第一行，
以此类推rNum <= 2就代表着每组的前两行
特别注意 rNum这个over()结果代表行数的字段的别名千万别写rowNum，rowNumber这类可能会被认为是关键字。（大坑！！）over函数里的目标表一定要起别名！！！！

---

清空表内容、还原索引

truncate table 表名;

查询数据库所有表的表结构

select substr(table_name,6), column_name from user_tab_columns where table_name like '%%' order by table_name, column_id;

查询一张表的建表语句

select dbms_metadata.get_ddl('TABLE','表名') from dual

distinct
字段去重

select distinct 字段名 from 表名

关于oracle中的字段名别名问题
在sql编写过程中存在一些情况需要给字段起别名来便于区分，
这个时候就无法在where的条件中通过字段的别名进行条件判断，
因为在sql的执行过程中是会按照from - where - select 顺序进行执行。
先进行where条件的查询，而这时还没有对select中的字段进行处理，
所以别名也就无法获取到，因此无法使用别名进行where条件查询。

select id 
from user_table
where id = '1'; -- 正确无问题

select id, nvl(age, 0) as new_age -- 需要对age做非空处理
from user_table
where new_age = 18; -- 错误情况，提示 “new_age” 标识符无效

oracle字段按固定字符分割

select regexp_substr(字段名, '[^用来分割的字符]+', 1, 2) as S -- 2 就代表取分割后的第二个字符串
from table_name
where ...

查询表名以及表的注释

select * from user_tab_comments where table_name = '';

查询表字段以及字段备注

select * from user_col_comments;

查询数据库中表结构

select t.table_name, t.column_name, t.data_type || '(' ||t.data_length|| ')' as datetype, t.nullable, tt.comments 
from user_tab_columns t, user_tab_comments tt where t.table_name = tt.table_name  
order by t.table_name, t.column_id;

树形结构条件查询 connet by prior

SELECT
    *
FROM
    cn_inst_level
START WITH
    brno = '0000'
CONNECT BY PRIOR brno = up1brno
           AND brsts = '1';

1、以brno = '0000’的数据开始。 结果集1
2、加上所有up1brno等于brno的数据。 结果集2
3、再加上所有up1brno等于结果集2中brno的数据。 结果集3
…
以此类推直到在没有符合条件的数据位置。
最终结果集 = 结果集1 + 结果集2 + 结果集3 …

列转行
两种方式将多条数据中的一列数据转换到一个字段并用逗号分隔

SELECT LISTAGG(字段名,',') WITHIN GROUP(ORDER BY 排序条件) FROM 表名 WHERE 条件;


SELECT wm_concat(字段名)  FROM 表名 WHERE 条件 ORDER BY 排序条件;

数据库语法及问题总结持续更新中