Oracle语法学习

1.Oracle以管理员身份登录

sqlplus system as sysdba

sqlplus system as sysdba

2.正则拆分字段

select REGEXP_SUBSTR(org_code, ‘[^,]+’, 1, 1),REGEXP_SUBSTR(org_code, ‘[^,]+’, 1, 2) from (
select ‘1002405001,his’ as org_code from dual) aa;

select REGEXP_SUBSTR(org_code, '[^,]+', 1, 1),REGEXP_SUBSTR(org_code, '[^,]+', 1, 2) from (
select '1002405001,his'  as org_code from dual)  aa;

3.oracle 查询字符集

SELECT VALUE
FROM NLS_DATABASE_PARAMETERS
WHERE PARAMETER = ‘NLS_CHARACTERSET’;

SELECT VALUE   
FROM NLS_DATABASE_PARAMETERS   
WHERE PARAMETER = 'NLS_CHARACTERSET';

4.sqlserver 查看数据库版本

SELECT SERVERPROPERTY(‘ProductVersion’) AS ProductVersion,
SERVERPROPERTY(‘ProductLevel’) AS ProductLevel,
SERVERPROPERTY(‘Edition’) AS Edition,
SERVERPROPERTY(‘EngineEdition’) AS EngineEdition;

SELECT SERVERPROPERTY('ProductVersion') AS ProductVersion,   
       SERVERPROPERTY('ProductLevel') AS ProductLevel,   
       SERVERPROPERTY('Edition') AS Edition,  
       SERVERPROPERTY('EngineEdition') AS EngineEdition;

备注：
ProductVersion：SQL Server 的版本号。
ProductLevel：SQL Server 的补丁级别（例如，RTM、SP1、CUx 等）。
Edition：SQL Server 的版本类型（例如，Enterprise Edition、Standard Edition 等）。
EngineEdition：SQL Server 的引擎版本编号。

5.SQLSERVER时间加一天

SELECT DATEADD(DAY, 1, GETDATE()) AS NextDay;

SELECT DATEADD(DAY, 1, GETDATE()) AS NextDay;

备注：
DAY 是你想要添加的时间单位。
1 是你想要添加的数量。
GETDATE() 是获取当前日期和时间的函数。

6.Oracle修改字符集

修改字符集可能会导致数据丢失或损坏，因此在进行此操作之前，请务必备份你的数据库。
在生产环境中更改字符集之前，最好在测试环境中进行充分的测试。
始终参考 Oracle 的官方文档以获取最准确和最新的信息。

7.Oracle时间加一天

select current_timestamp+1 from dual;—获取完整的日期和时间信息，包括时区,oracle 服务器的时间

select current_timestamp+1 from dual;

select current_date+1 from dual;—只需要日期部分，不需要具体的时间，oracle 服务器的时间

select current_date+1 from dual;

select sysdate+1 from dual;–返回当前系统时间（包括年、月、日、时、分和秒）

select sysdate+1 from dual;

备注：
Oracle服务器时间可能与实际时间有差异，一般会差±8H
select sysdate +1/(24*60) from dual;–当前时间加1分钟

8.oracle关联更新字段

UPDATE table2 t2
SET t2.some_column = (SELECT some_value FROM table1 t1 WHERE t1.some_condition = t2.some_common_column)
WHERE EXISTS (SELECT 1 FROM table1 t1 WHERE t1.some_condition = t2.some_common_column);

UPDATE table2 t2  
SET t2.some_column = (SELECT some_value FROM table1 t1 WHERE t1.some_condition = t2.some_common_column)  
WHERE EXISTS (SELECT 1 FROM table1 t1 WHERE t1.some_condition = t2.some_common_column);

备注：
把table1换成sql语句也可以

9.查看死锁（需要足够的权限，如DBA权限）

select sess.sid,
sess.serial#,
lo.oracle_username,
lo.os_user_name,
ao.object_name,
lo.locked_mode
from v$locke{d}_{o}bjectlo,db{a}_{o}bjectsao,v$session sess
where ao.object_id = lo.object_id and lo.session_id = sess.sid;

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; 

10. 处理死锁

将上面查询出来的sid以及serial放入到下面的sql中进行替换就可以了

alter system kill session ‘sid,serial’;

alter system kill session 'sid,serial';

select CLIENT_IDENTIFIER,v.inst_id,v.status,‘alter system kill session ‘’’ || v.sid || ‘,’ || v.serial# || ‘’’ immediate;',
v.USERNAME, v.CLIENT_INFO,v.SQL_HASH_VALUE,v.SQL_ADDRESS,v.MACHINE,v.TERMINAL from gv$session v;

select CLIENT_IDENTIFIER,v.inst_id,v.status,'alter system kill session ''' || v.sid || ',' || v.serial# || ''' immediate;', 
    v.USERNAME, v.CLIENT_INFO,v.SQL_HASH_VALUE,v.SQL_ADDRESS,v.MACHINE,v.TERMINAL  from gv$session v;

11.时分秒转换成逻辑数字以及逻辑数字转换会时分秒，各个数据库原理均一致

69952实际上=时X60X60+分X60+秒 =19X60X60+25X60+52
-----19：25：52 除以3600，取整得小时 除以3600取余数，余数再除以60取整得分钟 除以60取余的 秒
select round(69952/60/60,0) as hour,round(mod(69952,6060)/60,0) as min,mod(69952,60) as sec,
(round(69952/60/60,0)||‘:’||round(mod(69952,6060)/60,0)||‘:’||mod(69952,60))::time as time

select  round(69952/60/60,0) as hour,round(mod(69952,60*60)/60,0) as min,mod(69952,60) as sec,
(round(69952/60/60,0)||':'||round(mod(69952,60*60)/60,0)||':'||mod(69952,60))::time as time

12.创建虚拟字段（虚拟列）

ALTER TABLE table_name add (source_txt AS ("id" || '_' || "code" || '_' || "name"));

备注：table_name是表名字 source_txt是虚拟字典名称 id、code、name是table_name表中现有的字段

13.dump文件上传（部署数据库）

登录DBA账户

sqlplus 用户名/密码@ip:端口号/服务号as sysdba

创建数据库目录

CREATE DIRECTORY dmp_dir1 AS '/home/oracle/dmp_test/dqm';
--dmp文件路径  /home/oracle/dmp_test/dqm  根据实际改成自己的

--创建用户
create user 用户名identified by 密码;

给定权限

grant create session, connect, resource to 用户名;

1）.GRANT：这是SQL语句的关键字，用于授予权限。
2）.CREATE SESSION：这个权限允许用户连接到数据库实例。每个用户至少需要这个权限才能连接到数据库。
3）.CONNECT：虽然这个权限在Oracle 12c及更高版本中不再单独存在（因为它已经被CREATE SESSION隐式包含），但在旧版本的Oracle中，CONNECT权限允许用户连接到数据库，但它不提供任何资源或创建对象的权限。在较新的版本中，你可以安全地忽略它，因为它已经被CREATE SESSION替代。
4）.RESOURCE：RESOURCE角色是一个预定义的角色，它包含了一组权限，允许用户创建表、序列、触发器、过程、函数等。这通常是一个“通用”的权限集，适用于大多数数据库用户。

–执行命令

impdp 用户名/密码@ip:1521/服务名 DIRECTORY=dmp_dir1 DUMPFILE=dump文件 REMAP_TABLESPACE=dump文件表空间名字:新表空间名字  REMAP_SCHEMA=dump文件SCHEMA名字:新SCHEMA名字 logfile=yhpt_dqm_`date +%Y%m%d%H%M%S`.log FULL=y
--如果还有错误，根据日志进行修改

再根据实际情况给定该用户其他权限，如增删改查权限、创建视图、函数、存储过程等权限

14 特殊join说明

1）如果只写了join，则系统默认是内连接，比如：

select * from table_1 a 
join table_2 b on a.id=b.id;
--等同于
select * from table_1 a 
inner join table_2 b on a.id=b.id;
--或等同于
select * from table_1 a, table_2 b  where a.id=b.id;

2. (+)一般在旧版oracle常用，举例

select * from table_1 a, table_2 b  where a.id=b.id(+);
--等同于右连接
select * from table_1 a 
right join table_2 b on a.id=b.id;

说明：本文是用来本人自己学习与查阅用的，会不定时扩展，可能存在一些语法或其他问题，欢迎大家指正。