(1)Oracle正则匹配使用
- PS:这条SQL可以通过正则匹对查询一下，表A的字段a是否有非数字的数据，有时候数据表的一些字段是varchar类型的，如果sql里用to_number的话，就肯定会报无效数字的错误
select * from 表A where not regexp_like(字段a,'^[[:digit:]]+$');
Oracle表数据回滚语句
【表数据恢复回N分钟之前】
alter table 表名 enable row movement;
flashback table 表名 to timestamp systimestamp - interval 'N' minute;
oracle同义词
创建同义词：
create public synonym tablename for user.tablename;
synonyms
NVL函数和NVL2函数
-
NVL函数的格式如下：NVL(expr1,expr2)
含义是：如果oracle第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第一个参数本来的值。 -
NVL2函数的格式如下：NVL2(expr1,expr2, expr3)
含义是：如果该函数的第一个参数为空那么显示第二个参数的值，如果第一个参数的值不为空，则显示第三个参数的值。SQL> select ename,NVL2(comm,-1,1) from emp;
NULLIF(exp1,expr2)函数的作用是如果exp1和exp2相等则返回空(NULL)，否则返回第一个值。
Coalese函数的作用是的NVL的函数有点相似，其优势是有更多的选项。
格式如下：
Coalesce(expr1, expr2, expr3…… exprn)
表示可以指定多个表达式的占位符。所有表达式必须是相同类型，或者可以隐性转换为相同的类型。
例子，对于字段b查询出来为空的情况，就返回’’，否就返回vm_concat(a||’(’||b||’)’)，具体可以参考博客: https://blog.csdn.net/u014427391/article/details/84981114
select to_char(nvl2(b,vm_concat(a||'('||b||')'), '') from A group by id
递归查询
start with 子句：遍历起始条件，有个小技巧，如果要查父结点，这里可以用子结点的列，反之亦然。
connect by 子句：连接条件。关键词prior，prior跟父节点列parentid放在一起，就是往父结点方向遍历；prior跟子结点列subid放在一起，则往叶子结点方向遍历
ps:例子，递归查询一般用于递归查单位，地区信息等等什么的。start with后可以加一些开始条件，没有就写1=1或者不写也可以的。id和parent_id根据业务需要替换。
select * from 表格1 start with 1=1 connect by id = prior parent_id
条件sql
- case when then else end 可以理解为流程控制语句或条件控制语句。可以实现资料获取的时候，可以更多的条件和自定义逻辑
case when (select count(1) from t_unit_info
where para_unit_code = unitInfo.unit_code) > 0
then 'closed' else 'open' end as state
select 1 from mytable;与select anycol(目的表集合中的任意一行） from mytable;与select * from mytable 作用上来说是没有差别的，都是查看是否有记录，一般是作条件用的。select 1 from 中的1是一常量，查到的所有行的值都是它，但从效率上来说，1>anycol>*，因为不用查字典表
oracle数据库还原
- 将TESTDB.bmp放置在dpdump目录，如下路径：
E:\app\Administrator\admin\orcl\dpdump - 创建用户
create user testdb65 identified by “testdb65”
default tablespace users temporary tablespace temp;
- 用户授权
grant connect,dba to testdb65;
- 创建表空间
CREATE TABLESPACE HR_DATA01 DATAFILE ‘D:\nhsj\hr_data01.dbf’ SIZE 500M AUTOEXTEND ON NEXT 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K ; 【创建表空间】
- 导入 bgyhr2是来源库
impdp testdb65/testdb65@orcl schema=testdb65 dumpfile=TESTDB65.dmp
- 导出 scott 是要导出的库
expdp lfwtrain/a@orcl schema=当前用户 dumpfile=expdptest.dmp
- 重导dmp文件
Oracle保存数据时一般以DMP文件的形式进行保存
本博客记录，DMP文件的导入 先打开CMD
sqlplus /nolog
- 以sysdba连接
conn /as sysdba
- 这里创建表空间，因为表空间进行存储的话，可以提供系统性能
CREATE TABLESPACE DATA01 DATAFILE 'D:\tablespace\data01.dbf' SIZE 100M AUTOEXTEND ON NEXT 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K ;
- 创建用户
CREATE USER job IDENTIFIED BY job DEFAULT TABLESPACE DATA01 TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON DATA01
- 授权
grant connect,dba to job;
- 复制dmp文件到oracle安装目录里的app\admin\orce\dpdump里，然后用以下代码导入dmp文件，要顺便导入logfile的话，加上logfile=文件名
impdp job/job@orcl schemas=job dumpfile=EXPDP160810GZ.dmp
vm_concat函数
函数wm_concat(列名)，该函数可以把列值以","号分隔起来,并显示成一行
decode函数
含义解释：
decode(条件,值1,返回值1,值2,返回值2,…值n,返回值n,缺省值)
该函数的含义如下：
IF 条件=值1 THEN
RETURN(翻译值1)
ELSIF 条件=值2 THEN
RETURN(翻译值2)
…
ELSIF 条件=值n THEN
RETURN(翻译值n)
ELSE
RETURN(缺省值)
END IF
例子：下面的sql就可以加在where后面，sql意思就是t.project_Name为空的情况#{projectName}=#{projectName}，也即1=1，就是不做任何操作；不为空的情况，t.project_Name=#{projectName}
decode(t.project_Name,null,#{projectName},t.project_Name) = #{projectName}
-oracle开窗函数
分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是：对于每个组返回多行，而聚合函数对于每个组只返回一行。
开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化.
上面是开窗函数over(…)的简单介绍。
oracle开窗函数使用的话一般是和order、partition by、row_number()、rank()、dense_rank()几个函数一起使用
over函数和row_number一起使用：
开窗函数和row_number函数一起使用的话，就是返回一行，不过这里其实不适合用来统计，因为统计成绩的话，可以存在分数一样的两条数据，而row_number只返回一条数据。
select * from ( select stuName,row_number()over(partition by classId order by score desc) rnfrom t_score ) where rn=1；
over函数和rank一起使用：
rank函数功能也是排序，这里的话，假如有分数一样的两条数据的情况，是会返回两条的，但是假如第二名，就是从3开始计算了
select stuName,rank()over(partition by classId order by score desc) mm from t_score
over函数和dense_rank一起使用：
dense_rank函数是rank函数的补充，假如有分数一样的两条数据，是可以按照正常排序，第二名的情况，还是显示2
select stuName,dense_rank()over(partition by classId order by score desc) mm from t_score
网友评论