Oracle学习笔记

Oracle提高

oracle 11g OCP  sql 题库解析汇总链接

https://www.cnblogs.com/niyuelong/p/7189877.html

创建序列

 

-- Create sequence
create sequence ISEQ$$_104784
minvalue 1
maxvalue 9999999999999999999999999999
start with 1
increment by 1
cache 20;

oracle的 listagg() WITHIN GROUP ()和wm_concat()函数使用

查询PackSn绑定的电芯

 --查询PackSn条码绑定的电芯条码
 select a.packsn,
        c.cell_sn
  from xwd_pack_cell_group_link a, xwd_mes_group_cell c
 where a.cell_group_id = c.group_id
   and a.packsn='PACKSN000002'

--使用  listagg() within group ()  将多行合并成一行

select a.packsn,
       listagg(c.cell_sn, ',') within group(order by cell_sn) as cell_sn
  from xwd_pack_cell_group_link a, xwd_mes_group_cell c
 where a.packsn = 'PACKSN000002'
   and a.cell_group_id = c.group_id
 group by packsn

 select a.packsn,
       wm_concat(c.cell_sn) cell_sn
  from xwd_pack_cell_group_link a, xwd_mes_group_cell c
 where a.cell_group_id = c.group_id
   and a.packsn='F8Y639600AHH1YD5S'
   group by a.packsn

 

select * from v$session_longops where start_time>sysdate-1/24

该视图记录了执行时间长于6秒的某个操作(这些操作可能是备份，恢复，收集统计信息，Hash Join,Sort ,Nested loop，Table Scan, Index Scan 等等)，这个视图通常用来分析SQL运行缓慢的原因，配合V$SESSION视图。

1.必须将初始化参数 timed_statistics设置为true或者开启sql_trace

2.必须用ANALYZE或者DBMS_STATS对对象收集过统计信息

 

两次测试数据查询

with t1 as (
        select product_sn,test_value,load_time,row_number () over(partition by product_sn order by load_time ) cnt
        from t_wip_device_interface t
        where t.mo_no = '226-MO1903141144-1402'
        and t.test_item = 'IR'
        and t.test_result='0'
        order by t.product_sn
        ),
  t2 as ( select  product_sn,
                  max(case when cnt=1 then  test_value else null end ) ir1 ,
                  max(case when cnt=2 then  test_value else null end ) ir2
          from   t1 
          group by product_sn
   having count(*)=2)
   select t2.*,ir1-ir2 ir_diff from t2

 oracle唯一主键SYS_GUID()

select sys_guid() from dual

是Oracle 8i 后提供的函数。SYS_GUID产生并返回一个全球唯一的标识符(原始值)由16个字节组成。

Oracle中的MD5加密

MD5为不可逆散列算法，可用于存储用户密码，存储后不需要永远不需要知道明文。密码比较时只需将用户输入的密码再次转成MD5码与存储的相比较即可得知用户输入密码是否正确。

select utl_raw.cast_to_raw(dbms_obfuscation_toolkit.md5(input_string =>'123456')) as password from dual;

Oracle中的instr()函数

instr函数返回要截取的字符串在源字符串中的位置。只检索一次，也就是说从字符的开始到字符的结尾就结束

第一种用法：
--instr(源字符串, 目标字符串)
select instr('helloword','word') from dual  --返回结果：6 默认第一次出现'word'的位置
select instr('helloword','o') from dual     --返回结果：5 默认第一次出现'o'的位置
第二种用法
--instr(源字符串, 目标字符串, 起始位置, 匹配序号)
select instr('helloword','o',3) from dual  --返回结果：5 从位置3开始出现'o' 的位置
select instr('helloword','o',3,2) from dual   --返回结果：7 从位置3开始到结尾第2次出现字符'o'的位置

 Oracle中的字符替换

--replace 字符串级别的代替  
select replace('helloword', 'hello', '') from dual -- 返回结果：word
--translate 字符串级别的代替
select translate（'helloword','lo','ab'）from dual  --返回结果：heaabwbrd

 Oracle regexp_substr 函数

function regexp_substr(String, pattern, position, occurrence, modifier)
--srcstr     ：需要进行正则处理的字符串

--pattern    ：进行匹配的正则表达式

--position   ：起始位置，从第几个字符开始正则表达式匹配（默认为1）

--occurrence ：标识第几个匹配组，默认为1

--modifier   ：模式（'i'不区分大小写进行检索；'c'区分大小写进行检索。默认为'c'。）

select regexp_substr('helloZworldZ','[A-Z]+') from dual --返回结果：Z

truncate 和delete 区别是什么？

     delete 命令是从表中删除一行或者多行，truncate是从表中永远的删除每一行，truncate是将整个表删除后重新创建当前表结构

--Oracle中两个日期相差小时数--
select TO_NUMBER((TO_DATE('2018-6-5','yyyy-mm-dd hh24:mi:ss')- TO_DATE('2018-5-31','yyyy-mm-dd hh24:mi:ss'))*24)
AS 相差小时数 from dual;

--Oracle中两个日期相差分钟数--
select TO_NUMBER((TO_DATE('2018-6-5','yyyy-mm-dd hh24:mi:ss')- TO_DATE('2018-5-31','yyyy-mm-dd hh24:mi:ss'))*24*60)
AS 相差分钟数 from dual;

--Oracle中两个日期相差秒数--
select TO_NUMBER((TO_DATE('2018-6-5','yyyy-mm-dd hh24:mi:ss')- TO_DATE('2018-5-31','yyyy-mm-dd hh24:mi:ss'))*24*60*60)
AS 相差秒数 from dual;

 

去年周日对应关系

select '2020' c_year, to_date('2020' || '0101', 'yyyy-MM-dd') + rownum - 1 celldate, to_char(to_date('2020' || '0101', 'yyyy-MM-dd') + rownum - 1,'YWWD') wk from dual connect by rownum <= 365