整理工作中常用的Oracle命令

咸鸭蛋豆腐

已于 2023-07-21 15:40:51 修改

阅读量1.2k

点赞数

分类专栏： Oracle数据库文章标签： oracle 数据库

于 2022-10-21 15:32:49 首次发布

本文链接：https://blog.csdn.net/dou_fu/article/details/126454689

版权

Oracle数据库专栏收录该内容

18 篇文章 2 订阅

订阅专栏

1、更新数据（update ）

① 把一个表的某个字段更新到另一张表中
要把（t_syd）B表 DM 的值update（更新）到（t_bzks）A表 SYDDM 字段，A与B表的连接条件是A.JGDM=B.JGDM。

update 被更新表a 
set (a.SYDDM) = (select b.DM from 数据来源表 b where  a.JGDM = b.JGDM) 
where exists
                      (select 1 from 数据来源表b where  a.JGDM = b.JGDM)
                      AND a.RXNJ='2022'
                      AND a.XJZT='注册';

② 本身更新数据字段

UPDATE table_name
SET 列名称 = 新值 
WHERE 列名称 = 条件值”

2、 exists用法

exists表示()内子查询语句，返回结果如果不为空，说明where条件成立，就会执行主sql语句。
如果为空，就表示where条件不成立，sql语句就不会执行。
STU数据量小，CLASS 数据量大时，使用exists速度更快。
STU数据量大，CLASS 数据量小时，使用in速度更快。

select * from STU
where exists(select 1 from CLASS where STU.sno = CLASS.sno) ;

select * from STU where STU.sno in (select CLASS.sno from CLASS ) ;

3、删除数据

① 删除一列（数据和列名）

alter table table_name drop column 列名

② 删除一整列数据

alter table table_name  drop column 列名;

③ 全表删除（truncate）

truncate table_name;

④ 删除部分行数据（delete）
直接使用DELETE FROM…WHERE…命令进行删除，在删除数据的过程中，会不断扩展回滚段，非常耗时，且如果回滚段有错误时，恢复非常麻烦。

delete from table_name where

4、删除空字符串

LTRIM(字串):将字串左边的空格移除。
RTRIM(字串): 将字串右边的空格移除。
TRIM(字串): 将字串首尾两端的空格移除,作用等于RTRIM和LTRIM两个函数共同的结果。
ALLTRIM(字串)：将字串左右边两边的空格移除。

5、截取或替换数字字符（regexp_replace）

regexp_replace (T_JZG.CSRQ, '[^0-9]+', '');

6、删除表/清空表数据

① 仍要保留该表，但要删除所有数据表记录时，用 truncate

truncate  table_name;

② 要删除部分记录或者有可能会后悔的话，用 delete

delete from table_name;

③ 不再需要该表时，用 drop

drop table  table_name;

7、在视图中增加数据（unoin）

在多条查询语句中添加union，查询出来的语句呈现逐行显示。

select t.sjqhdm wid, t.sjqhdm dm, t.sjmc mc,  t.pjqhdm ls,
 '2' cc,
 '1' SFSY
from t_xzqh t
group by t.sjqhdm , t.sjqhdm , t.sjmc ,  t.pjqhdm
union
select t.pjqhdm wid, t.pjqhdm dm, t.pjmc mc,
 ''  ls,
 '1' cc,
 '1' SFSY
from t_xzqh t
group by t.pjqhdm , t.pjqhdm , t.pjmc;

8、插入数据

少量数据插入多行数据库表
例：源表有10个数据列，只需要插入5个列的数据。

insert into 目标table_name (列名1,  列名2, 列名3, 列名4, 列名5)
select *  from 源table_name;

9、建立dblink报错：权限不足

在这里插入图片描述

10、查询数据库密码

① 查询数据库密码哪一天过期

select username, account_status, expiry_date, profile 
from user_name;

② 查询数据库密码有效期

select * 
from dba_profiles s 
where s.profile='DEFAULT' and resource_name='PASSWORD_LIFE_TIME';

③ 修改数据库密码永不过期

alter profile default limit password_life_time unlimited;

④ 修改数据库密码180天过期

alter profile default limit password_life_time 180;

⑤ 修改密码

alter user 用户名  identified by  原来的密码;

⑥ 可以解锁用户

alter user 被锁user_name account unlock;

11、表被锁

在执行数据库DDL操作时，有事会出现“Oracle 报 “ORA-00054 资源正忙, 但指定以 NOWAIT 方式获取资源, 或者超时失效” 的问题。

① 查找哪张表被锁，session_id 为会话 ID，object_name为表名

select l.session_id,o.owner,o.object_name
from v$locked_object l,dba_objects o
where l.object_id=o.object_id;

② 找出引发锁的会话，serial# 为会话序列号

select s.username,s.sid,s.serial#,s.logon_time
from  v$locked_object l,v$session s
where l.session_id=s.sid 
order by s.logon_time;

③ 杀死会话

alter system kill session 'sid, serial#';

④ 批量解锁语句

DECLARE CURSOR mycur IS 
    SELECT B.SID,B.SERIAL# FROM V$LOCKED_OBJECT A,V$SESSION B 
        WHERE A.SESSION_ID = B.SID GROUP BY B.SID,B.SERIAL#;
BEGION
    FOR cur IN mycur
      loop
      EXECUTE IMMEDIATE ('ALTER SYSTEM KILL SESSION '''||cur.sid||','||cur.serial#||''' ');
      end loop;
END;

12、查询前几天的数据（trunc）

需要查询近几天就改最后一个数字。

select * from '表名' where to_date('表单日期名','yy-mm-dd')>=trunc(sysdate -2)

13、数字始终保留多位小数（round）

select to_char(round(1.56666,2),'fm99999999999990.00') from dual;

14、正则表达式截取部分文字

正则表达式截取第一个%前面的数字（不论整数还是小数），然后用replace将%替换掉，如果要截取第一串数字23，可以将’[0-9\.\0-9]+%’ 这个%换成‘号’即可。

SELECT 
replace(regexp_substr('数字23号第二串数字98.65%第三串245.46%','[0-9\\.\\0-9]+%'),'%','') FROM DUAL;

15、不规则日期修改为yyyy-mm-dd

“28-5月 -18 12.00.00.000000000 上午”。

--1、先改整体为年月日格式
update 被修改表
set rq='20'||substr(rq,8,2)||'-'||replace(regexp_substr(rq,'[0-9\\.\\0-9]+月'),'月','')||'-'||substr(rq,0,2)||' '||substr(rq,11,2)||':'||substr(rq,14,2)||':'||substr(rq,17,2)
 where rqlike '%上午%';
 
update 被修改表
set rq='20'||substr(rq,8,2)||'-'||replace(regexp_substr(rq,'[0-9\\.\\0-9]+月'),'月','')||'-'||substr(rq,0,2)||' '||(substr(rq,11,2)+12)||':'||substr(rq,14,2)||':'||substr(rq,17,2)
 where rqlike '%下午%';
 
--2、根据长度修改月为两位
update 被修改表
set rq=substr(rq,0,5)||'0'||substr(rq,6,13)
where length(rq) ='18';

16、删除部分数据相同的一整列（rowid）

通过rowid查询不完全重复的一整列数据，查询max(rowid)或min(rowid)。

delete from 被修改表 a
where a.rowid<(
select max(b.rowid) from 同一张被修改表 b
where a.列名=b.列名
）

17、SQL server日期格式转到Oracle（convert）

（datetime，smalldatetime）与字符串类型（nchar，nvarchar，char，varchar）相互转换的时候才用到的函数的3个参数，第1个参数为转换后的大小，第2个为转换日期的字段或函数，第3个为转换的格式。

CONVERT(data_type,expression_r_r[,style]);

SELECT CONVERT(varchar(100), GETDATE(), 0);-- 04 16 2022 10:57AM
SELECT CONVERT(varchar(100), GETDATE(), 1);-- 04/16/22
SELECT CONVERT(varchar(100), GETDATE(), 2);-- 22.04.16
SELECT CONVERT(varchar(100), GETDATE(), 3);-- 16/04/22
SELECT CONVERT(varchar(100), GETDATE(), 4);-- 16.04.22
SELECT CONVERT(varchar(100), GETDATE(), 5);-- 16-04-22
SELECT CONVERT(varchar(100), GETDATE(), 6);-- 16 04 22
SELECT CONVERT(varchar(100), GETDATE(), 7);-- 04 16, 22
SELECT CONVERT(varchar(100), GETDATE(), 8);-- 10:57:46
SELECT CONVERT(varchar(100), GETDATE(), 9);-- 04 16 2022 10:57:46:827AM
SELECT CONVERT(varchar(100), GETDATE(), 10);-- 04-16-22
SELECT CONVERT(varchar(100), GETDATE(), 11);-- 22/04/16
SELECT CONVERT(varchar(100), GETDATE(), 12);-- 220416
SELECT CONVERT(varchar(100), GETDATE(), 13);--16 04 2022 10:57:46:937
SELECT CONVERT(varchar(100), GETDATE(), 14);-- 10:57:46:967
SELECT CONVERT(varchar(100), GETDATE(), 20);-- 2022-04-16 10:57:47
SELECT CONVERT(varchar(100), GETDATE(), 21);-- 2022-04-16 10:57:47.157
SELECT CONVERT(varchar(100), GETDATE(), 22);-- 04/16/22 10:57:47 AM
SELECT CONVERT(varchar(100), GETDATE(), 23);-- 2022-04-16
SELECT CONVERT(varchar(100), GETDATE(), 24);-- 10:57:47
SELECT CONVERT(varchar(100), GETDATE(), 25);-- 2022-04-16 10:57:47.250
SELECT CONVERT(varchar(100), GETDATE(), 100);-- 04 16 2022 10:57AM
SELECT CONVERT(varchar(100), GETDATE(), 101);-- 04/16/2022
SELECT CONVERT(varchar(100), GETDATE(), 102);-- 2022.04.16
SELECT CONVERT(varchar(100), GETDATE(), 103);-- 16/04/2022
SELECT CONVERT(varchar(100), GETDATE(), 104);-- 16.04.2022
SELECT CONVERT(varchar(100), GETDATE(), 105);-- 16-04-2022
SELECT CONVERT(varchar(100), GETDATE(), 106);-- 16 04 2022
SELECT CONVERT(varchar(100), GETDATE(), 107);-- 04 16, 2022
SELECT CONVERT(varchar(100), GETDATE(), 108);-- 10:57:49
SELECT CONVERT(varchar(100), GETDATE(), 109);-- 04 16 2022 10:57:49:437AM
SELECT CONVERT(varchar(100), GETDATE(), 110);-- 04-16-2022
SELECT CONVERT(varchar(100), GETDATE(), 111);--2022/04/16
SELECT CONVERT(varchar(100), GETDATE(), 112);-- 20220416
SELECT CONVERT(varchar(100), GETDATE(), 113);-- 16 04 2022 10:57:49:513
SELECT CONVERT(varchar(100), GETDATE(), 114);-- 10:57:49:547
SELECT CONVERT(varchar(100), GETDATE(), 120);-- 2022-04-16 10:57:49
SELECT CONVERT(varchar(100), GETDATE(), 121);-- 2022-04-16 10:57:49.700
SELECT CONVERT(varchar(100), GETDATE(), 126);--2022-04-16T10:57:49.827
SELECT CONVERT(varchar(100), GETDATE(), 130);-- 18 ???? ?????? 1427 10:57:49:907AM
SELECT CONVERT(varchar(100), GETDATE(), 131);-- 18/04/1427 10:57:49:920AM

18、sys_guid()函数

通过sys_guid()，生成32位的唯一编码。来生成唯一主键。
在ODI中直接写在映射中。
在这里插入图片描述

create table stu 
(id varchar2(32),
 name nvarchar2(20),
 primary key(id)
);

insert into stu(id, name) 
values(sys_guid(), '张三');

19、修改数据库密码

在plsql中使用命令窗口进行修改密码。

Alter user user_name identified by new_password replace old_password；

20、插入数据（insert）

要把B表 DM 的值insert（插入）到A表的多个字段。

insert into T_IT_KCB_KCBXX (WID,  KCH,  BH,  XM)
select  B.WID||B.SKZC, B.KCH, B.XH, B.XM
from  t_xskb B

21、MySQL日期格式转到Oracle

--把字符串转为日期格式
DATE_FORMAT(RQ , '%Y-%m-%d %H:%i:%S')

%a   缩写星期名（Sun....Sat） 
%b   缩写月名（00．．．．．31）
%c   月，数值（1．．．．．12）
%D   带有英文前缀的月中的天（1st，2nd，3rd，等等）
%d   月的天，数值（00—31）
%e   月的天，数值（0—31）
%f   微秒 
%H   小时（00-23） 
%h   小时（01—12）
%I   小时（01—12）
%i   分钟，数值（00—59）
%j   年的天（001—366）
%k   小时（0-23） 
%1   小时（1-12） 
%M   月名（January....December） 
%m   月，数值（00—12）
%p   AM或 PM
%r   时间，12-小时（hh：mm：ss AM或 PM） 
%S   秒（00—59）
%s    秒（00—59）
%T   时间，24-小时（hh：mm：ss）
%U   周（00—53）星期日是一周的第一天
%u   周（00—53）星期一是一周的第一天
%V   周（01—53）星期日是一周的第一天，与%X使用
%v   周（01—53）星期一是一周的第一天，与%x使用
%W   星期名（Sunday....Saturday） 
%w   周的天（θ＝星期日，6＝星期六）
%X   年，其中的星期日是周的第一天，4位，与%V 使用
%x   年，其中的星期一是周的第一天，4位，与%v 使用
%Y   年，4位
%y   年，2位 
%%   一个文字“%”