oracle 使用笔记

1.查看用户信息

查看oracle用户信息：id oracle

2.查看、编辑、复制、创建文件

查看：cat /etc/sysctl.conf
编辑：vi /etc/sysctl.conf
复制： cp /home/oracle/database/response/* /home/oracle/etc/
创建：mkdir /home/oracle/etc

3.加压文件安装及解压

安装：[root@CentOS ~]# yum install unzip –y
解压：[root@CentOS ~]# cd /tmp
[root@CentOS tmp]# unzip linux.x64_11gR2_database_1of2.zip && unzip linux.x64_11gR2_database_2of2.zip

4.vi/vim使用

https://blog.csdn.net/twotwo22222/article/details/127818678
安装vim： yum install vim -y

[root@VM-0-8-centos ~]# vim /etc/selinux/config
输入 i 进入编辑模式

序号	命令	作用
1	i	进入编辑模式
2	esc	退出编辑模式
3	:w	保存
4	:q	退出
5	:wq	保存并退出
6	:q!	不保存强制退出
7	/ 要查找的词	n往下查找，N往上查找

5.字符串分隔

https://www.dbs724.com/288275.html

6.条件判断

select
	case
		when a.medical_type = '1' then '健康证'
		when a.medical_type = '2' then '健康体检'
		when a.medical_type = '3' then '职业病'
		when a.medical_type = '4' then '企业家健康体检'
		when a.medical_type = '5' then '干部健康体检'
		when a.medical_type = '6' then '转向体检'
		when a.medical_type = '7' then '从业人员健康体检'
		when a.medical_type = '8' then '入伍体检'
		else '入学体检'
	end 体检类型,
from
	man_record_card a

参考：https://www.dbs724.com/273932.html

7.计算这两个日期的时间差

两个Date类型字段：START_DATE，END_DATE，计算这两个日期的时间差（分别以天，小时，分钟，秒，毫秒）：

天： ROUND(TO_NUMBER(END_DATE - START_DATE))

小时： ROUND(TO_NUMBER(END_DATE - START_DATE) * 24)

分钟： ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60)

秒： ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60 * 60)

毫秒： ROUND(TO_NUMBER(END_DATE - START_DATE) * 24 * 60 * 60 * 1000)

8.oracle模糊查询

SELECT name FROM table WHERE INSTR(name, '李')>0

SELECT * FROM REP_REPORT_ORG WHERE '632314949602902016,633314365008773120' LIKE CONCAT(CONCAT('%', TASK_CODE), '%');

参考：https://www.jb51.net/database/2926251op.htm

9.根据身份证计算年龄

select to_char(sysdate, ‘yyyy’) - substr(t.useridcardnum, 7, 4) as age from uip_ca_userinfo t

10.关联表更新

update man_record a set org_id = ( select org_id from vip_org b where a.org_name = b.org_name );
参考：https://www.dbs724.com/278117.html

11.Cause: java.sql.SQLSyntaxErrorException: ORA-00904: “ORG_ID”: invalid identifier 报错

可能原因：
（1）表数据库字段没有加

12.查询获取第一条，最新的一条，前几条

（1）第一条

SELECT * FROM PHY_PORTFOLIO_JOB_DISEASE WHERE CARD_CODE = '695846' ROWNUM = 1

（2）最新一条

SELECT
	* 
FROM
	( SELECT * FROM PHY_PORTFOLIO_JOB_DISEASE WHERE CARD_CODE = '695846' ORDER BY CREATE_TIME DESC ) a 
WHERE
	ROWNUM = 1

（3）前几条

SELECT * FROM PHY_PORTFOLIO_JOB_DISEASE WHERE CARD_CODE = '695846' ROWNUM <= 20

13. Oracle的NVL函数用法

NVL(eExpression1, eExpression2)
如果 eExpression1 的计算结果为 null 值，则 NVL( ) 返回 eExpression2。如果 eExpression1 的计算结果不是 null 值，则返回 eExpression1。eExpression1 和 eExpression2 可以是任意一种数据类型。如果 eExpression1 与 eExpression2 的结果皆为 null 值，则 NVL( ) 返回 .NULL.。
参考:https://www.cnblogs.com/goldcc/p/14630168.html

14.PLSQL：[1]plsql中文乱码,显示问号

https://jingyan.baidu.com/article/a3aad71aa9bfefb1fa00964d.html

15.pl/sql修改数据之后必须提交事务

16.Oracle中Case When的用法

https://blog.csdn.net/xiaochenXIHUA/article/details/121215294

17.Oracle 列转行或行转列

参考：https://blog.csdn.net/m0_71406734/article/details/130527543

（1）列转行

方式一、unpiovt include nulls

SELECT
	* 
FROM
	( SELECT 22 别名1, 11 别名2, 55 别名3, 66 别名4, 99 别名5 FROM dual ) t 
	unpivot include nulls ( value FOR name IN ( 别名1,别名2,别名3,别名4,别名5 ) )

说明：value FOR name 中的value是显示值，name是字段名，value和name可以任意命名，for前面的是字段值，后面的是字段名；IN ( 别名1,别名2,别名3,别名4,别名5 ) 是要转换的列。
转换效果如下：

IN ( 别名5,别名1,别名2)字段数量可以任意，顺序表示查询的顺序

方式二、regexp_substr

select regexp_substr('bbb,aaa,ccc', '[^,]+', 1, level) as split_result
  from dual
connect by level <=
           length('bbb,aaa,ccc') - length(replace('bbb,aaa,ccc', ',')) + 1;

（2）行专列

方式一、Oracle 行转列函数 LIST

参考：https://www.python100.com/html/80204.html

Oracle 行转列函数 LIST 可以将一组数据转换为以列形式存储的数据，并且可以将多行数据合并为一行。与 WM 函数类似，LIST 函数也能够方便地进行数据处理，提高数据处理效率。

以下是 LIST 函数的基本语法：

SELECT LISTAGG(expression, ',' ) WITHIN GROUP (ORDER BY column_name)
FROM table_name;

其中，expression 表示待处理的列名或者表达式，table_name 表示待处理的表名，column_name 用来指定按照哪一列来进行排序。需要注意的是，在使用 LIST 函数时，要先使用 ORDER BY 子句进行排序。
示例1：

SELECT
	(SELECT LISTAGG(name, ',' ) WITHIN GROUP (ORDER BY name) FROM BASE_WORKTYPE WHERE mr.work_type like CONCAT(CONCAT('%', CODE), '%')) work_type
FROM
	MAN_RECORD mr
GROUP BY
	WORK_TYPE

示例2：对查询出来的字段去重

SELECT
	LISTAGG (
	CONCAT( CASE WHEN REPORT_TYPE = '1' THEN '普通体检' WHEN REPORT_TYPE = '2' THEN '职业病' WHEN REPORT_TYPE = '3' THEN '健康证' END, all_opinion ),
	'' 
	) WITHIN GROUP ( ORDER BY CONCAT( REPORT_TYPE, all_opinion ) ) ,
	LISTAGG(DISTINCT CARD_CODE, ',' ) WITHIN GROUP (ORDER BY CARD_CODE)
FROM
	phy_medical_opinion 
WHERE
	CARD_CODE = '779830' 
	AND DEL_FLAG = '0';

18.oracle中计算两个日期的相差天数、月数、年数、小时数、分钟数、秒数等

参考：https://www.cnblogs.com/caiyt/p/12893661.html

相差小时数：

select TO_NUMBER((TO_DATE('2018-6-5 16:36:00','yyyy-mm-dd hh24:mi:ss')- TO_DATE('2018-6-5 15:56:00','yyyy-mm-dd hh24:mi:ss'))*24)
AS 相差小时数 from dual;