Oracle SQL 基础

发表于 2020-04-24 更新于 2021-02-26 分类于 Oracle 本文字数： 16k 阅读时长 ≈ 15 分钟

第一份工作主要和数据库打交道, 使用的是 Oracle 数据库, 由于自己对数据库也不是很熟悉, 于是就抽空整理了一下基础知识, 让自己的知识更加系统化.

Oracle 中常用的功能语句

--查询特定表中不允许为 null 的列
select distinct nullable, column_name
  from dba_tab_columns
 where table_name = 'DDB'
   and nullable = 'N';

--获取全部带有 "指定列名" 的表
select table_name from user_tab_cols where column_name = 'SAASDM';

--快速备份
create table [备份表的名称] as select * from [表名];
create table person_backup_20201031 as select * from person;

--查询数据库中所有用户的信息 (用户名, ID, 创建日期)
select * from all_users;

--查询当前登录用户的信息
select * from user_users;

--查询最近执行过的语句
select sql_text, sql_fulltext, first_load_time, last_active_time
  from v$sql
 where sql_fulltext like '%筛选条件%'
 order by last_active_time desc;

--查看数据库的位数
select case length(address) when 8 then '32 位数据库' when 16 then '64 位数据库' end as 数据库版本 from v$sql where rownum = 1;

Oracle 触发器相关

--查看本账户下的所有触发器
SELECT * FROM USER_TRIGGERS;
--管理员查看数据库实例下所有的触发器
SELECT * FROM ALL_TRIGGERS;
SELECT * FROM DBA_TRIGGERS;
--启用或禁用触发器
ALTER TRIGGER trigger_name DISABLE;
ALTER TRIGGER trigger_name ENABLE;

Oracle 锁相关

--查看死锁1
select sess.sid,
       sess.serial#,
       lo.oracle_username,
       lo.os_user_name,
       ao.object_name,
       lo.locked_mode
  from v$locked_object lo, dba_objects ao, v$session sess
 where ao.object_id = lo.object_id
   and lo.session_id = sess.sid;

--查看死锁2
select 'alter system kill session ''' || a.sid || ',' || a.serial# || ''';'
  from v$session a
 where sid in (select sid from v$enqueue_lock t where t.type ='TO');

--解除死锁
alter system kill session '68,4221'; -- "sess.sid,sess.serial#"

--杀死进程
select spid, osuser, s.program
     from v$session s,v$process p
     where s.paddr=p.addr and s.sid=68 --"sess.sid"

--CMD 中执行, spid: 实例名 thread: spid
orakill spid thread

Oracle 时间格式相关语句

--查看当前的默认时间格式 (必须以管理员登录)
select * from props$ where name = 'NLS_DATE_FORMAT';--DD-MON-RR

--更新当前的默认时间格式 (必须以管理员登录, 重启数据库后生效)
update props$ set value$ = 'YYYY-MM-DD HH24:MI:SS' where name = 'NLS_DATE_FORMAT';

Oracle 用户相关的操作语句

--创建新用户
create user scott identified by scott123;

--修改用户密码
alter user scott identified by scott333;

--删除用户 (用户拥有对象时, 无法直接删除用户, 必须先删除对象)
drop user scott;

--删除用户对象
drop user scott cascade;

--给用户授权
grant connect, resource, dba to scott;

--撤销授权
revoke connect, resource, dba from scott;

Oracle 表空间操作语句

--创建新的表空间
create tablespace assets datafile 'f:\tablespace\tablespacefolder\assets.dbf' size 30000m; -- 30G

--删除空表空间, 包含物理文件
drop tablespace tablespace_name including contents;

--查看所有表空间的总大小
select tablespace_name, (sum(bytes) / 1024 / 1024) as spacesize from dba_data_files group by tablespace_name;

--查看表空间的使用情况
select tablespace_name as "表空间名称", spacesize as "总空间 MB", usedsize "已使用空间 MB", trunc(usedsize/spacesize*100, 2) as "已使用百分比 %" from (
select tablespace_name, (sum(bytes) / 1024 / 1024) as spacesize,
(select (sum(bytes) / 1024 / 1024) from dba_segments where tablespace_name = A.tablespace_name) as usedsize
from dba_data_files A group by tablespace_name);

--扩充特定表空间原本的数据文件 (只能扩大, 不能缩小, 如原大小为 2G, 现在需要将其扩大 1G, 则这里需要写 3G)
alter database datafile 'f:\tablespace\tablespacefolder\assets.dbf' resize 300m;

--向特定表空间追加新的数据文件
alter tablespace assets add datafile 'f:\tablespace\tablespacefolder\assets1.dbf' size 30000m; -- 30G
alter tablespace assets add datafile 'f:\tablespace\tablespacefolder\assets2.dbf' size 30000m; -- 30G

--设置表空间自动扩充
alter database datafile 'f:\tablespace\tablespacefolder\assets1.dbf' autoextend on next 200m maxsize unlimited;
alter database datafile 'f:\tablespace\tablespacefolder\assets2.dbf' autoextend on next 200m maxsize unlimited;

--查看所有表空间文件路径
select * from dba_data_files;

--查看特定用户的默认表空间
select username, default_tablespace from dba_users where username = 'SCOTT';

--设置特定用户的默认表空间
alter user scott default tablespace assets;

Oracle impdp 和 expdp 的使用

--创建自定义路径
create or replace directory dir_dp as 'f:/dmp';

--授权自定义路径
grant read, write on directory dir_dp to scott;

--查看目录设置及相应权限设置
select privilege, directory_name, directory_path from user_tab_privs t, all_directories d
where t.table_name(+) = d.directory_name order by 2, 1;

--使用路径参数执行导出与导入
expdp scott/scott123@数据库服务名 directory=dir_dp dumpfile=001.dmp logfile=001.log full=y version=10.2.0.1.0;
impdp scott/scott123@数据库服务名 directory=dir_dp dumpfile=001.dmp logfile=001.log full=y table_exists_action=replace;

[注]

有的时候也会使用 imp, exp 进行少量数据的导入导出.

- imp scott/scott123@数据库服务名 file=f:/001.dmp log=f:/001.log full=y ignore=y
- exp scott/scott123@数据库服务名 file=f:/001.dmp log=f:/001.log full=y

exp 不能指定版本号, expdp 可以.
imp 和 exp 可以加路径, impdp 和 expdp 不可以直接加路径, 只能通过自定义路径来更改导入导出的位置.
imp 导入时, 如果出现表已经存在的错误, 可以加入 ignore=y 参数, 加入此参数后, 已存在的表会进行数据追加, 不加此参数, 新导入的数据会被跳过!
table_exists_action 参数用于指定导入操作对表的处理行为, 一共有四种:

- SKIP: 这是默认行为, 如果表已存在则直接跳过, 同时报错!
- APPEND: 如果表已存在则向表中追加数据.
- REPLACE: 如果表已存在则 DROP 表, 然后创建表, 最后导入数据.
- TRUNCATE: 如果表已存在则 TRUNCATE 表, 然后导入数据.

SQL 的大小写

sql 中的关键字, 字段名, 表名都是大小写不敏感的.
sql 中的字符串是大小写敏感的, like 'a%' 和 like 'A%' 是完全不一样的.

SQL 中的运算符

>, <, >=, <= 这些运算符是进行范围比较的, 因此规定实际使用时, 尽量只用作数值类型的比较. 数值类型比较的时候不能加引号. where a < 3 and a > 1 而不是 where a < '3' and a > '1'
=, <>, != 相等和不相等这两种运算符既可以运算字符串, 也可以运算数值. <> 比 != 用得更多一些. sql 中等于运算符是 = 不是 ==
between and 运算符本质上也是范围比较运算符, 因此规定实际使用时, 也尽量只用作数值类型的比较. 语法格式是: a between 1 and 2
比较运算符也适用于其他类型的比较, 比如字母, 日期. 比如: where between 'A' and 'H' where between '2019-01-01' and '2019-12-31'

[注意] 在不同的数据库中, between 操作符会产生不同的结果! 在某些数据库中, between 选取介于两个值之间但不包括两个测试值的字段. 在某些数据库中, between 选取介于两个值之间且包括两个测试值的字段. 在某些数据库中, between 选取介于两个值之间且包括第一个测试值但不包括最后一个测试值的字段. 因此, 请检查您的数据库是如何处理 between 操作符!

SQL 中的 order by

order by 作用是将结果表中的数据进行排序.
order by asc 为升序排序, 即第1条数据最小, 向后数据会越来越大. asc 可以省略.
order by desc 为降序排序, 即第1条数据最大, 向后数据越来越小.
order by 可以同时对多列进行排序, 但是实际使用时不要超过两列. 如 order by a,b, 数据会先根据 a 进行升序排序, 之后对具有相同 a 值的数据进行按照 b 的升序排序.

SQL 中的 insert into

insert into 的作用是向表中插入一行数据, 每执行一次 insert into 只能插入一行数据.
insert into 的语法格式是 insert into tablename (columnname1, columnname2, ...) values (value1, value2, ...), 前面的括号写要赋值的字段名, 后面写对应的值. 要对一行的所有字段赋值的时候, 前面的括号可以省略, 此时后面的括号必须写上全部的数据.
insert into tablename 后面也可以直接跟一个 select 语句, 作用是将查询出的数据保存到一个已存在的表中, 但是这里的 tablename 必须是已存在的表.
使用 select 的时候语法格式是 insert into tablename (columnname1, columnname2, ...) (select * from table), 不能加 values 关键字, 否则报错.

SQL 中的 update set

update set 的作用是更新表中已经存在的数据.
update set 可以一次更新多行, 这取决于 where 条件.
update set 可以一次更新多个字段, 只要使用 , 隔开即可.
update set 的语法格式是: update table set a=1, b=2, c=3 where (), set 后面可以跟多个赋值语句, 中间使用逗号隔开, 更新语句的数量使用 where 来决定, 实际使用时必须加 where 条件.

SQL 中的 delete from

delete from 的作用是删除表中数据.
delete from 可以一次删除多行, 这取决于 where 条件.
delete from 的语法格式是: delete from table where (), delete 的语法格式基本是固定的, 只是后面的 where 条件不同.

SQL 中三种删除 (drop, delete, truncate) 的不同之处

delete: 删除表内容, 不删除表结构, 不释放空间, 可以回滚.
truncate: 删除表内容, 不删除表结构, 释放空间, 默认不能回滚.
drop: 删除表内容, 同时删除表结构, 释放空间, 不能回滚.
执行速度: drop > truncate > delete

SQL 中的各种 join on

join on 不是用来单纯取交集并集的, 它更多的是用来做数据拼接的.
inner join on 可以省略写作 join on
inner join on 是在两个表之间取交集. 只有符合 on 条件的数据才会被拼接并返回. from tableA join tableB on tableA.a = tableB.b 只有符合 tableA.a = tableB.b 的数据才会被拼接并返回.
left join on 是在两个表之前取左集, 符合on 条件的会被拼接并返回, 不符合 on 条件的左表数据也会返回, 当然这些数据是没有办法作拼接的.
right join on 是在两个表之前取右集, 符合on条件的会被拼接并返回, 不符合 on 条件的右表数据也会返回, 当然这些数据是没有办法作拼接的.

sql-join

SQL 中的 union 和 union all

join on 可以理解为横向拼接, union all 可以理解为纵向拼接. 2X2 的两个表使用 join on 拼接后是 2X4, union all 拼接后是 4X2.
union 拼接自带去重, 如果想要将全部的数据保留下来, 则需要使用 union all.
union all 返回表的列名使用的是第一个 select 语句的列名.

SQL 中的 select into

select into 的作用是将查询出来的数据保存到一个新的临时表中.
select into 必须跟新表, 不允许跟已存在的表.
语法格式是 select () into table_new from table_old where ().
Oracle 和 PL/SQL 中不支持此语句! 可以使用 create table as select 进行代替.
在 Sql Server 中这条语句可以正常使用!

SQL 中的 create

create database 用来创建一个新数据库.
create table 用来创建一个新表.

create table 的语法格式:

create table table_name
(
    column_name_1 data_type(size),
    column_name_2 data_type(size),
    ......
    column_name_n data_type(size)
); --创建新表, 字段使用 () 括起来, 字段和类型之间使用空格隔开, 最后一个字段后没有 , 号, 语句最后加 ; 号.

SQL 中 select 时只返回前 n 条数据

为了达到这个效果, Oracle 中可以使用 rownum, SQL Server 中可以使用 select top, My SQL 中可以使用 limit. 在这一方面, Oracle 中有一个很大的缺点, rownum 不能直接直接写 rownum = 2, 返回第二条数据, rownum 必须是从 1 开始的一个序列.

select () from table where () and rownum <= 2; -- Oracle, 返回前 2 条数据
select () from table where () and rownum <= 10; -- Oracle, 只返回前 10 条数据
select top 2 () from table where (); --Sql Server, 返回前 2 条数据
select top 50 percent () from table where (); --Sql Server, 返回前 1/2 的数据
select () from table () limit 2; --My Sql, 返回前 2 条数据

SQL 中的约束

SQL 中的约束用来规范列中的数据, 如果有违反约束的数据行为, 一般为 insert 和 update, 那么这个行为会被禁止.

SQL 中主要的约束有: not null, unique, primary key, foreign key, check, default.

not null

not null 约束是强制某一列不接受空值.
在进行 insert 或者 update 的时候如果没有给这一列赋值, 语句就会执行失败.

unique

unique 约束是将某一列或者某些列作为表中数据的唯一标识.
unique 约束可以在一张表中设置多个.
unique 约束中, null 值是允许存在的.

primary key

primary key 约束被称为主键. 唯一标识表中的每一条数据.
primary key 约束的列在一张表中只能有一个.
primary key 中不允许出现 null 值.
每一张表中都应该有一个主键.

foreign key

foreign key 约束被称为外键.
一个表中的 foreign key 指向另一个表中的 primary key.
一个表中的 foreign key 也可以指向另一个表中的 unique, 但是实际使用中不要这样使用. 即外键指向主键.
foreign key 所在的表称为子表, 被指向的 primary key 所在的表称为父表.
foreign key 同样可以约束 insert, update 行为. 当要插入的值或者更新后的值在父表被指向的列中不存在时, 语句执行失败.

check

check 约束用来限制列中值的范围.

default

default 约束用来设置列的默认值.

SQL 中的自动增加 (auto_increment)

Oracle 中并没有直接提供 auto_increment, 而是使用序列 sequence 实现了自动增加.
先创建一个新的序列 sequence. 最后的循环一般不要设置, 因为自动增加多用于主键, 而主键是唯一标识的, 不允许重复. maxvalue 一般也不用设置, 创建序列时默认会设置一个非常非常大的数字, 就是这个: 9999999999999999999999999999, 够大了吧?
1
2
3
4
5
6
7
create sequence sequence_new
minvalue 1 --最小值
--maxvalue 5 --最大值
start with 1 --开始值
increment by 1 --增量
cache 2 --缓存大小
--cycle; --循环
使用序列中的 nextval 属性实现自动增加. 每次调用 nextval 序列中的值就会自动增加.
1
2
select sequence_new.nextval from dual;
insert into tableA (ID) values (sequence_new.nextval);

SQL 中常用的日期操作

获取系统的当前日期: select sysdate from dual;
获取月的开始: select trunc(sysdate, 'MM') from dual;
获取月的结束: select last_day(sysdate) from dual;
获取周日: select trunc(sysdate, 'd') from dual;

[注] 上面这条语句其实是获取一周的开始, 但是西方国家中 "周日" 是一周的开始😅
获取周一: select trunc(sysdate, 'd') + 1 from dual;

[注] trunc() 函数返回的是 "date" 类型, 可以直接做加减运算. 加减运算的单位是 "天".
获取小时的开始: select trunc(sysdate, 'hh') from dual;
获取分钟的开始: select trunc(sysdate, 'mi') from dual;
增加一个月: select add_months(sysdate, 1) from dual;
减少一个月: select add_months(sysdate, -1) from dual;
增加一年: select add_months(sysdate, 12) from dual;
减少一年: select add_months(sysdate, -12) from dual;

trunc 方法也可以截取数字:

select trunc(123.458    ) from dual; --123
select trunc(123.458,  0) from dual; --123
select trunc(123.458,  1) from dual; --123.4
select trunc(123.458, -1) from dual; --120
select trunc(123.458, -4) from dual; --0
select trunc(123.458,  4) from dual; --123.458

SQL 中常用的函数

avg() 返回数值列的平均值. 括号中的列必须是数值列. 多用于比较条件中的参数.

select student_id from table_student group by student_id having avg(student_score) > 80 查出平均分大于 80 分的学生的 id.
count() 有多种用法.
- count(*) 和 count(1) 等效, 获取查询数据的条数.
- count(column_name) 获取 column_name 这一列中非空的值. (null 不计算, 重复值全部计算在内)
- count(distinct column_name) 获取 column_name 这一列中非空的且唯一的值的数目. (null 不计算, 重复值只计算一次)
max() 用于取出列中数据的最大值. min() 用于取出列中数据的最小值.
- max() 函数不仅可以取出数值列的最大值, 还可以用于字符串, 日期列.
- select max(student_age) from table_student; 年龄 (数值列)
- select max(student_id) from table_student; 学号 (字符串列)
- select max(student_birth) from table_student; 出生日期 (日期列)
- min() 函数同样具有以上性质.
sum() 返回数值列的和.

select sum(score) from table_student group by student_id; 求出每个学生的总分.
group by 根据一个或多个列, 对查询出来的结果集进行分组.
- where 关键字无法对分组后的结果集进行筛选, 分组之前可以.
- group by 常常搭配 avg(), count(), max(), min(), sum(), having 一起使用.
having 关键字的出现就是为了解决 "where 不能处理分组后的结果集" 这个问题.

having 可以对 group by 分组后的数据进行筛选.
exists() 对括号中的子句进行判断, 如果子句中有数据, 返回 true, 没有数据返回 false.
substr(parame1, parame2, parame3) 截取字符串
- parame1: 待处理字符串
- parame2: 起始位置, 0 1 都代表第一个字符
- parame3: 截取长度
- substr(parame1, parame2) : 取出 parame1 中 parame2 位置之后的整个字符串
instr(parame1, parame2) 检索字符串
- parame1: 元字符串
- parame2: 待查找字符串
- parame3 (可选) : 查找的起始位置, 正数为从左向右检索, 负数为从右向左检索
- parame4 (可选) : 返回第几次的检索位置
replace(parame1, parame2, parame3) 替换字符串

replace(parame1, parame2, parame3): 将 parame1 中所有的 parame2 全部替换成 parame3.
replace(parame1, parame2) : 将 parame1 中所有的 parame2 全部删除.

1 2	--举例: 取出 name 字段中 '/' 后的字符串 select substr(name, instr(name, '/') + 1) from dmb where saasdm = 'test' order by dmxh;

SQL 中的大小写转换

upper() 方法可以将查询出来的字符串数据全部转为大写字母.
lower() 方法可以将查询出来的字符串数据全部转为小写字母.
上面两个方式是将查询结果集中的数据转换大小写, 并不会修改数据库中真正的数据.

Oracle 中的字符串操作函数

substr(column_name, startPositon, length) 方法是 Oracle 数据库中的字符串截取函数.
- column_name: 是要操作的列名.
- startPosition: 是要截取的开始位置, 其中 0 和 1 都表示从第一个字符开始截取.
- length: 是要截取的字符串的长度.
length() 函数返回字符串数据的长度.

Oracle 中的保留位数操作函数

trunc() 函数, 可以截取数字, 达到控制保留位数的效果, 但是不会对数据进行四舍五入.
round() 函数, 可以对数字进行四舍五入, 达到控制保留位数的效果.

select round(3456.3456, -4) from dual; --0
select round(3456.3456, -3) from dual; --3000
select round(3456.3456, -2) from dual; --3500
select round(3456.3456, -1) from dual; --3460
select round(3456.3456,  0) from dual; --3456
select round(3456.3456    ) from dual; --3456
select round(3456.3456,  1) from dual; --3456.3
select round(3456.3456,  2) from dual; --3456.35
select round(3456.3456,  3) from dual; --3456.346
select round(3456.3456,  4) from dual; --3456.3456

Oracle 查看最近执行的语句

1	select * from v$sql where sql_fulltext like '% %' order by last_active_time desc;

Oracle 拼接整列数据为一个以逗号分割的字符串

wm_concat() 方法可以将整列数据为一个以 逗号 分割的字符串.
chr(39) 表示单引号, 这样可以将单引号拼接到字符串中.

在外层套用一个 replace() 方法, 可以将 逗号 替换为想要使用的分隔符.

1	select '(' \|\| chr(39) \|\| replace(wm_concat(distinct mkbh), ',', chr(39)\|\|','\|\|chr(39)) \|\| chr(39) \|\| ')' from mkb where systype='dwb';

Windows 删除无用的 Oracle 服务

在 Power Shell 中输入 sc delete 服务名 命令可以删除 Windows 服务. 如:

1	sc delete OracleDBConsolesdzc

Oracle 还原数据库包

由于经常要还原数据库包, 所以记录一下常用的语句.

--创建新用户
create user scott identified by scott123;

--给新用户授权
grant connect, resource, dba to scott;

--查看表空间文件地址
select * from dba_data_files;

--创建新的表空间
create tablespace assets datafile 'd:\oracle\product\11.2.0\dbhome_1\database\assets.dbf' size 2048m;

--设置表空间自动扩充
alter database datafile 'd:\oracle\product\11.2.0\dbhome_1\database\assets2.dbf' autoextend on next 200m maxsize unlimited;

--设置默认表空间
alter user scott default tablespace assets;

--新建数据泵路径
create or replace directory dir_dp as 'f:/dmp';

--使用数据库导出导入
expdp scott/scott123@数据库实例名 directory=dir_dp dumpfile=001.dmp logfile=001.log full=y version=10.2.0.1.0
impdp scott/scott123@数据库实例名 directory=dir_dp dumpfile=001.dmp logfile=001.log table_exists_action=replace full=y

Oracle 中递归查询

Oracle 中的递归查询使用 START WITH 和 CONNECT BY PRIOR 实现.

START WITH 后面跟开始条件.
CONNECT BY PRIOR 后面跟递归条件.

--递归查询
SELECT DISTINCT dwbh
  FROM (SELECT *
          FROM dwb@ZCXT a
         WHERE a.saasdm = '495543161'
         START WITH a.DWBH = '000007'
        CONNECT BY PRIOR a.SJDW = a.DWBH);

第一次查询为: 上级单位是 '000007' 的单位, 比如结果为: '7000001' 和 '7000002';

第二次查询为: 上级单位是 '7000001' 的单位, 查询结果为空, 则继续查询上级单位是 '7000002' 的单位, 若查询结果依然为空, 则终止查询.

使用的是 广度优先遍历.

SQL Server 中递归查询

SQL Server 中的递归查询使用 WITH 实现. WITH 后面跟树形结构的创建语句, 之后对树形结构查询即可.

--递归查询 (bjdw: 本级单位, sjdw: 上级单位)
WITH DwTree (bjdw, mc, sjdw, Tree)
AS
(
   SELECT bjdw, mc, sjdw , 0 AS Tree
   FROM DWB a
   WHERE a.bjdw in (select bh from cggl_SHQXB where bjdw='110017') -- 开始条件
   UNION ALL
   SELECT b.bjdw, b.mc, b.sjdw , DwTree.Tree + 1
   FROM DWB b
   JOIN DwTree ON b.sjdw = DwTree.bjdw -- 递归条件
)
SELECT bjdw FROM DwTree OPTION (MAXRECURSION 10); -- OPTION: 设置最大遍历深度, 防止无限循环

PL/SQL 中 DECODE() 方法的使用

基础用法

语法: DECODE(条件, 值1, 返回值1, 值2, 返回值2, ... 值n, 返回值n, 缺省值);

1
2
3

SELECT ztbz, DECODE(ztbz, '55', '未提交', '00', '提交', '92', '审核中', '91', '退回', '99', '审核通过', '缺省状态') AS ztmc
  FROM zjb
 GROUP BY ztbz;

搭配 SIGN() 函数

SIGN(value) 函数: value 为 0 返回 0, 大于 0 返回 1, 小于 0 返回 -1;

1 2	--判断一列和固定值的大小关系 SELECT DECODE(SIGN(dj - 10000), '1', '大于一万', '0', '等于一万', '小于一万') AS jezt FROM zjb;

实现类三目运算符

DECODE() 函数如果只写 4 个参数, DECODE(value, 值1, 值2, 值3), 此时的含义就可以解释为: 计算 value 的值, 如果返回值等于值1, 则返回值2, 否则返回值3. (当然这其实还是 DECODE() 的本质用法)

1	SELECT DECODE(dj, 100, '等于 100', '不等于 100') FROM zjb;

搭配 SUM() 函数, 一次求出多个数量

SELECT SUM(DECODE(INSTR(saasdm, '1'), 0, 0, 1)) AS "saasdm 中含有 1",
       SUM(DECODE(INSTR(saasdm, '2'), 0, 0, 1)) AS "saasdm 中含有 2",
       SUM(DECODE(INSTR(saasdm, 'H'), 0, 0, 1)) AS "saasdm 中含有 H",
       SUM(DECODE(INSTR(saasdm, '9'), 0, 0, 1)) AS "saasdm 中含有 9"
  FROM dwjgb;

SELECT SUM(DECODE(SIGN(dj - 100), -1, 0, 1)) AS "单价大于一百",
       SUM(DECODE(SIGN(dj - 1000), -1, 0, 1)) AS "单价大于一千",
       SUM(DECODE(SIGN(dj - 10000), -1, 0, 1)) AS "单价大于一万",
       SUM(DECODE(SIGN(dj - 100000), -1, 0, 1)) AS "单价大于十万",
       SUM(DECODE(SIGN(dj - 1000000), -1, 0, 1)) AS "单价大于一百万",
       SUM(DECODE(SIGN(dj - 10000000), -1, 0, 1)) AS "单价大于一千万"
  FROM zjb;

搭配 ORDER BY, 对字符列进行特定的排序

将资产按照 "未提交, 已提交, 审核中, 审核完成, 其他" 的顺序进行排序.

SELECT *
  FROM zjb
 WHERE ztbz IN ('55', '00', '92', '99')
 ORDER BY DECODE(ztbz, '55', 1, '00', 2, '92', 3, '99', 4, 5);

Oracle 中简单的分页查询实现方式

使用 MINUS 实现. MINUS 为差集, 返回左侧集合中在右侧集合中不存在的部分.


--返回第 101 - 200 行数据 (循环查询中建议使用 <= 和 <= 搭配)
SELECT yqbh FROM zjb where yqbh in (
   SELECT yqbh
     FROM (SELECT DISTINCT yqbh FROM zjb)
    WHERE ROWNUM <= 200
   MINUS
   SELECT yqbh
     FROM (SELECT DISTINCT yqbh FROM zjb)
    WHERE ROWNUM <= 100
);

--返回第 n - m 行数据 (单次查询中建议使用 <= 和 < 搭配)
SELECT yqbh FROM zjb where yqbh in (
   SELECT yqbh
     FROM (SELECT DISTINCT yqbh FROM zjb)
    WHERE ROWNUM <= m
   MINUS
   SELECT yqbh
     FROM (SELECT DISTINCT yqbh FROM zjb)
    WHERE ROWNUM < n
);