Oracle使用笔记

冷不防 2022-05-27 04:39 555阅读 0赞

### SQL语言共分为四大类: ###

**1. 数据查询语言DQL(查)**

**2 .数据操纵语言DML(增删改)**

**3. 数据定义语言DDL(**用来创建数据库中的各种对象-----表、视图、索引、同义词、聚簇**)**

**4. 数据控制语言DCL(**数据控制语言DCL用来授予或回收访问数据库的某种特权，并控制  
数据库操纵事务发生的时间及效果，对数据库实行监视等**)**

### 安装 ###

### oralce安装网上有很多教程，随便百度一下就行，这里记录以下要注意的地方 ###

1.首次登陆：登陆名：sys as sysdba  密码：任意字符，他对应的是默认的数据库：SYS，

登录进去之后可以自己设置密码

删除用户: drop user user\_name cascade;    \#级联删除该用户下所有的数据对象

查看用户列表：select username from dba\_users

修改用户名和密码：alter user newUser identified by newPassword

手动启动和关闭：[https://blog.csdn.net/pengpengpeng85/article/details/78756914][https_blog.csdn.net_pengpengpeng85_article_details_78756914]

### Oracle获取当天，当月，上月，当年的数据 ###

### 1. 当天 createdtime 参数 ###

### select \* from tablename t where trunc(sysdate) = trunc(t.createdtime); ###

--本周

select \* from 表 where time > sysdate - (to\_char(sysdate-1,'D'))

### 2.当月 createdtime 参数 ###

select \* from tablename t where t.createdtime >= trunc(SYSDATE, 'MM') and t.createdtime < trunc(last\_day(sysdate), 'MM') + 1

### 2.1 获取上一个月的数据 ###

select \* from SOFTWARE\_KPI\_MONTH t where t.STATISTIC\_TIME >= trunc(ADD\_MONTHS(SYSDATE, -1), 'MM')  and t.STATISTIC\_TIME < last\_day( ADD\_MONTHS(SYSDATE, -1)) + 1

### 3.获取季度的数据（可以根据年） ###

select \* from SOFTWARE\_KPI\_QUARTER t where to\_char(t.STATISTIC\_TIME,'yyyy-q')=to\_char(TO\_DATE('2018-03', 'YYYY-MM'),'yyyy-q')

### 4.当年 createdtime 参数   select \* from tablename t where t.createtime >= trunc(sysdate, 'YYYY') and t.createtime < add\_month(trunc(sysdate,'YYYY'), 12)  ###

### 查询FAULT\_MONITOR\_ALARM\_TAB\_C表有多少列： ###

select count(column\_name) from all\_col\_comments  where table\_name='FAULT\_MONITOR\_ALARM\_TAB\_C'

### 添加一列为：EXTENSION1 ###

alter table FAULT\_MONITOR\_ALARM\_TAB\_C  add(EXTENSION1 VARCHAR2(255));

### 给表或某一列添加说明：即EXTENSION1列代表重保组 ###

comment on table test is '资源表';

comment on column test.EXTENSION1 is '重保组';

查看表的comment：

select   \*   from   all\_tab\_comments   where   table\_name= upper('test') ;  
查看列的comment：

select   \*   from   all\_col\_comments where   table\_name= upper('test') ;

## 常见的函数： ##

### decode函数：相当于if else if \[else\]，会短路，即匹配上了就结束函数 ###

select decode(6, 3, 'yes', 4, 'noyes', 'hello') from dual 如果为6等于3，则为yes，等于4，则为noyes，else，为hello。

### FIRST\_VALUE和LAST\_VALUE函数 ###

FIRST\_VALUE 返回组中数据窗口的第一个scalar\_expression 值 FIRST\_VALUE ( \[scalar\_expression IGNORE NULLS)OVER ( \[ partition\_by\_clause \] order\_by\_clause )  
LAST\_VALUE   返回组中数据窗口的最后一个scalar\_expression 值  LAST\_VALUE ( \[scalar\_expression IGNORE NULLS)OVER ( \[ partition\_by\_clause order\_by\_clause )  // IGNORE NULLS 表示忽略为null的数据

over（order by salary） 按照salary排序进行累计，order by是个默认的开窗函数  
   over（partition by deptno）按照部门分区

select  STATISTIC\_TIME ,LAST\_VALUE (GPRS\_LINE\_NUM IGNORE NULLS) OVER (PARTITION BY NE\_ID ORDER BY  
            STATISTIC\_TIME) AS GPRS\_LINE\_NUM from  PK\_ENTERBUSS\_PROVINCE\_1440

ORDER BY  
            STATISTIC\_TIME ->时间从小到大， last\_value去最后一个，则是开始获取最大的time。

GPRS\_LINE\_NUM 为 STATISTIC\_TIME最大的那个 GPRS\_LINE\_NUM 列的值，如果当前STATISTIC\_TIME的GPRS\_LINE\_NUM 列的值为空，则取上一个STATISTIC\_TIME的 GPRS\_LINE\_NUM 列的值，取到为止

### Case when 的用法： ###

两种用法，第一这个弄直接在when后面写条件，x满足这个条件（is null）即为1，否则为2，可以添加多个条件

第二是表达式，即case后面的1等于when后面的1，则为1，否为2，可以添加多个when

如果连个条件都不满足，则为default\_result

select case when x is null then 1 else 2 \[end\] from dual

select  case 1 when 1 then 1 else 2 \[end\] from dual

### replace（'将要更改的字符串','被替换掉的字符串'\[,'替换字符串'\]） ###

如果\[,'替换字符串'\]没有，则为默认替换为空字符

## **（数据库连接工具）查看sid：** ##

select instance\_name from v$instance;

select global\_name from global\_name;

## 输出小数: ##

正常输出小数：.3 前面的0不会显示出来，需用regexp\_replace函数

SELECT regexp\_replace(0.3, '^\\.', '0.') FROM dual

### lob数据格式： ###

参考:[https://www.cnblogs.com/Grand-Jon/p/7389427.html][https_www.cnblogs.com_Grand-Jon_p_7389427.html]

一 Orlce中VARCHAR2

在使用数据库的时候字符数据一般使用VARCHAR2, VARCHAR2既分PL/SQL Data Types中的变量类型，也分Oracle Database中的字段类型，不同场景的最大长度不同。在Oracle Database中，VARCHAR2 字段类型，最大值为4000；PL/SQL中 VARCHAR2 变量类型，最大字节长度为32767。当 VARCHAR2 容纳不下我们需要存储的信息时，就出来的Oracle的大数据类型LOB( Large Object，大型对象)。

项目中遇到的问题:在协议签署时,因为要请求其他服务的库,传输报文入库留存协议记录,有些协议配置过多的时候入库字段很长会出问题,最后决定将varchar2改成CLOB(字符型大型对象);

二 Orlce中的LOB类型

·  在Oracle中，LOB（Large Object，大型对象）类型的字段现在用得越来越多了。因为这种类型的字段，容量大（最多能容纳4GB的数据），且一个表中可以有多个这种类型的字段，很灵活，适用于数据量非常大的业务领域（如图象、档案等）。

·  LOB类型分为BLOB和CLOB两种：BLOB即二进制大型对象（Binary Large Object），适用于存贮非文本的字节流数据（如程序、图象、影音等）。

·  而CLOB，即字符型大型对象（Character Large Object），则与字符集相关，适于存贮文本型的数据（如历史档案、大部头著作等）。

将varchar2转为clob

alter table tfkb_alarm_tab modify(PRERELATIONCHILDIDS long)
    alter table tfkb_alarm_tab modify(PRERELATIONCHILDIDS CLOB)

普通查询clob字段是查询不出来，可以使用函数 DBMS\_LOB.substr(PRERELATIONCHILDIDS, 结束下标(最大字节数为4000，开始下标(从1开始))

select DBMS_LOB.substr(PRERELATIONCHILDIDS, 4000, 1)  a from tfkb_alarm_tab  where PRERELATIONCHILDIDS2 is not null

但有个问题，如果PRERELATIONCHILDIDS某条数据数据大于4000字节时，这样查出来的数据不完整，就需要使用使用pl/sql的函数来查询出来

--/
    -- 基于游标记录
    DECLARE
        CURSOR c_ciqorgcode is
          SELECT PRERELATIONCHILDIDS2 FROM tfkb_alarm_tab;
       customer_rec c_ciqorgcode%rowtype;
    BEGIN
       dbms_output.put_line('-------基于游标记录-------');
       OPEN c_ciqorgcode;
       LOOP
          FETCH c_ciqorgcode into customer_rec;
          EXIT WHEN c_ciqorgcode%notfound;
          dbms_output.put_line(customer_rec.PRERELATIONCHILDIDS2);
       END LOOP;
    END;
    /

插入数据的时候也一样，普通插入，oracle会隐式的转化为varchar，即可保存的最大字节为4000，当大于4000字节数据时，还是会抛出错误，即需要用使用pl/sql的函数来插入数据

-- 增加
    DECLARE 
           V_LANG CLOB := '-2067588949890550735';  
    BEGIN
      INSERT INTO tfkb_alarm_tab(PRERELATIONCHILDIDS2) VALUES (V_LANG);
      COMMIT;
    END;
    /

### 用户过期 ###

参考：[https://www.jb51.net/article/55807.htm][https_www.jb51.net_article_55807.htm]

一、由于Oracle中默认在default概要文件中设置了“PASSWORD\_LIFE\_TIME=180天”所导致。

二、由于Oracle中默认在default概要文件中设置了“FAILED\_LOGIN\_ATTEMPTS=10次”，当输入密码错误次数达到设置值将导致此问题。

主要命令：

#查看各用户的profile的信息，一般是default
    
    SELECT username,PROFILE FROM dba_users;
    
    #查看指定概要文件（如default）的密码有效期设置：
    
    Select * FROM dba_profiles s Where s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME'; 
    
    #如果是UNLIMITED，则不需要改为无期限了，一般为180天，如果不无期限，则修改一下
    
    ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED; 
    
    #修改后，还没有被提示ORA-28002警告的帐户不会再碰到同样的提示；已经被提示的帐户必须再改一次密#码 alter user cps_ln_nei identified by <原来的密码> ----不用换新密码 
    
    alter user cps_ln_nei identified by cps_ln_nei

### oracle创建用户 ###

参考：[https://www.cnblogs.com/roger112/p/7685307.html][https_www.cnblogs.com_roger112_p_7685307.html]

一、创建用户

oracle内部有两个建好的用户：system和sys。用户可直接登录到system用户以创建其他用户，因为system具有创建别 的用户的 权限。 在安装oracle时，用户或系统管理员首先可以为自己建立一个用户。

语法\[创建用户\]： create user 用户名 identified by 口令\[即密码\]；

例子： create user test identified by test;

语法\[更改用户\]: alter user 用户名 identified by 口令\[改变的口令\];

例子： alter user test identified by 123456;

给予权限命令：grant all privileges to test

\--创建用户  
create user USER\_DC\_BACK identified by USER\_DC\_BACK;    
\--查询结果  
grant UNLIMITED TABLESPACE to USER\_DC\_BACK;  
\--查询结果  
grant CONNECT to USER\_DC\_BACK;  
grant DBA to USER\_DC\_BACK;  
grant RESOURCE to USER\_DC\_BACK;

二、删除用户

语法：drop user 用户名;

例子：drop user test;

若用户拥有对象，则不能直接删除，否则将返回一个错误值。指定关键字cascade,可删除用户所有的对象，然后再删除用户。

语法： drop user 用户名 cascade;

例子： drop user test cascade;

查看最大连接数：

SELECT   resource\_name,    
         current\_utilization,    
         max\_utilization,    
         LIMIT,    
         ROUND (max\_utilization / LIMIT \* 100) || '%' rate    
  FROM   (SELECT   resource\_name,    
                   current\_utilization,    
                   max\_utilization,    
                   TO\_NUMBER (initial\_allocation) LIMIT    
            FROM   v$resource\_limit    
           WHERE   resource\_name IN ('processes', 'sessions')    
                   AND max\_utilization > 0);

### WIN7上的oracle数据迁移到linux上： ###

**exp/imp逻辑备份与恢复：**

11G中新特性，当表无数据时，不分配segment，以节省空间。而使用exp命令时，无Segment的表不会被导出，

所以先要避免这个问题：

1.先查找空表， select 'alter table '||table\_name||' allocate extent;' from user\_tables   
 where num\_rows is null or num\_rows = 0，结果复制出来

用在win+r输入sqlplus登录，把复制出来的语句运行一遍即可为空表分配空间

2.在win+r输入cmd，运行exp "'sys/oracle as sysdba'" owner=CPS\_CQ file=d:CPS\_CQ.dmp，获取dmp文件

3.在linux上的oracle创建cps\_cq用户

4.用oralce用户进入linux中，然后放把dmp文件传过来，然后执行：

imp "'sys/oracle as sysdba'" fromuser=ULTRA\_SCREEN touser=ULTRA\_SCREEN

即完成。

函数：UNION 和 UNION ALL 的区别

select name from city1 union select  name from city2

得到的结果 是第一条sql的结果 加 第二条sql的结果，但是会去重，即两个结果有相同的时候，会去掉一个

如果用 union all 表示，则不会去重，说明最大的区别就是一个会对结果去重，一个不会去重。

# Oracle 查询结果多行数据合并为一行     #

使用：listagg(OID ,',') within GROUP (order by (OID))

使用函数 WM\_CONCAT()，这个函数不稳定，有些版本可用，有些oracle版本不可用

例：SELECT OID FROM AGENT;结果为：

![2019040211564489.png][]

SELECT WM\_CONCAT(OID) FROM AGENT;结果为：

![20190402115744284.png][]

JOB定时任务

JOB主要用来定义 定时执行的存储过程，一般现场录入数据都是采用这种方法：

简单的scheduler 由3部分组成，JOB NAME，PROGRAM NAME， SCHEDULER NAME。  
可以随意更换。JOB 的JOB ID  由系统生成，没有PROGRAM NAME，运行时间由  
NEXT\_DATE 和 INTERVAL 组成，比起scheduler，JOB 的时间表达太弱了，SCHEDULER  
在运行时间上可以说，只要想得到，就能做得到。  
SCHEDULER 记录历史运行情况，JOB没有。

这里讲解scheduler job的使用

参考：[https://blog.csdn.net/haogexiaole/article/details/80835868][https_blog.csdn.net_haogexiaole_article_details_80835868]

[https://blog.csdn.net/iteye\_4639/article/details/82572324][https_blog.csdn.net_iteye_4639_article_details_82572324]

普通的jobs使用：在工具中创建失败，其他工具可以，格式规范是这样写，可参考，不建议使用，推荐使用
    scheduler jobs
    格式：
    DECLARE
        JN NUMBER;
    BEGIN
        dbms_job.submit(job => JN,what => '',next_date => to_date('30/08/2019 09:17:52',
        'DD/MM/YYYY HH24:MI:SS'),interval => '',no_parse => FALSE);
    END;
    job代表job序号，what要执行的存储过程，next_date:下一次要执行的时间interval：定时执行的时间规则
    
    例如：
    创建一个插入的存储过程：
    --/
    CREATE OR REPLACE PROCEDURE  INSERT_PK  AS
    current_sql VARCHAR2(200);
    current_value  NUMBER(38,2);
    BEGIN
          current_sql := 'SELECT COUNT(*) current_value FROM C_CITY2 ';
          execute immediate current_sql INTO current_value;
          insert into test_job(num) values (current_value);
            dbms_output.put_line('插入成功：'  || current_value );
    END INSERT_PK;
    /
        
    --/
    DECLARE
        JN NUMBER;
    BEGIN
        dbms_job.submit(JN, 'INSERT_PK;',to_date('30/08/2019 09:12:29',
        'DD/MM/YYYY HH24:MI:SS'), 'TRUNC(SYSDATE+1)');
    END;
    /
    在dbvisualizer中，what参数格式为：存储过程;  一定要加 ; 符号
    其他的工具中暂时还不清楚

Dbvisualizer中Scheduler Jobs的创建：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RvZGF5X190b21vcnJvdw_size_16_color_FFFFFF_t_70][]启动和关闭JOBS，选择创建好的job，右键，enable scheduler job -启动job  disable scheduler job-关闭job

run scheduler job 表示执行一次   stop scheduler job 应该是表示run那一次还没执行完，可以停止，否则会提示未启动

## exists 和 in 的区别： ##

exists：

select  count(distinct a.ALARM\_OBJECT\_ID) num from pc\_csw\_alarm\_tab a  
  where EXISTS  (select \* from C\_CSW\_condition\_02 b where a.ALARM\_OBJECT\_ID=b.资源ID)

in：

select  count(distinct ALARM\_OBJECT\_ID) num from pc\_csw\_alarm\_tab where  ALARM\_OBJECT\_ID in (select 资源ID from C\_CSW\_condition\_02)

当B表比A表数据大时适合使用exists()，因为它没有那么多遍历操作，只需要再执行一次查询就行。  
如：A表有10000条记录，B表有1000000条记录，那么exists()会执行10000次去判断A表中的id是否与B表中的id相等。  
如：A表有10000条记录，B表有100000000条记录，那么exists()还是执行10000次，因为它只执行A.length次，可见B表数据越多，越适合exists()发挥效果。  
再如：A表有10000条记录，B表有100条记录，那么exists()还是执行10000次，还不如使用in()遍历10000\*100次，因为in()是在内存里遍历比较，而exists()需要查询数据库，我们都知道查询数据库所消耗的性能更高，而内存比较很快。  
**结论：exists()适合B表比A表数据大的情况**

[https_blog.csdn.net_pengpengpeng85_article_details_78756914]: https://blog.csdn.net/pengpengpeng85/article/details/78756914
[https_www.cnblogs.com_Grand-Jon_p_7389427.html]: https://www.cnblogs.com/Grand-Jon/p/7389427.html
[https_www.jb51.net_article_55807.htm]: https://www.jb51.net/article/55807.htm
[https_www.cnblogs.com_roger112_p_7685307.html]: https://www.cnblogs.com/roger112/p/7685307.html
[2019040211564489.png]: /images/20220527/5928d4fd48b842a98cb36cfbca866b1d.png
[20190402115744284.png]: /images/20220527/81a78d7b358949cb95bbe153bf35d5cf.png
[https_blog.csdn.net_haogexiaole_article_details_80835868]: https://blog.csdn.net/haogexiaole/article/details/80835868
[https_blog.csdn.net_iteye_4639_article_details_82572324]: https://blog.csdn.net/iteye_4639/article/details/82572324
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RvZGF5X190b21vcnJvdw_size_16_color_FFFFFF_t_70]: /images/20220527/0a8eb238822f43e6b0aafd24587197f9.png