ORACLE常用语句

暴躁老哥KK

已于 2024-03-20 13:57:50 修改

阅读量334

点赞数 8

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

于 2024-02-28 09:42:09 首次发布

本文链接：https://blog.csdn.net/weixin_47753395/article/details/136302101

版权

记录专栏收录该内容

3 篇文章 0 订阅

订阅专栏

持续更新中...

1、查询某段日期范围的每天日期

//查询2022-01-01至今的每天日期
select day_num from (
       SELECT ROWNUM, to_char(TRUNC(to_Date('2022-01-01','yyyy-mm-dd'), 'dd') + ROWNUM - 1,'yyyy-mm-dd') AS day_num
       FROM DUAL
                CONNECT BY ROWNUM < TRUNC(trunc(sysdate-1), 'dd') - TRUNC(to_Date('2022-01-01','yyyy-mm-dd'), 'dd') + 1
)where  day_num<TO_char(sysdate,'yyyy-mm-dd')

2、存储过程重复循环某段语句

create or replace procedure proc_xxxxxx( END_DATE varchar )
 as
 --每次更新近七天的数据 未确认的
  i Number :=0;
 begin
    loop
    delete from xxxxxx 
    where to_char(fact_date,'yyyy-mm-dd')=to_char(TO_DATE(END_DATE, 'yyyy-mm-dd')-1-i,'yyyy-mm-dd')
    and update_time is null;
    commit;

--业务代码

   i:=i+1;
   dbms_output.put_line(i);
   EXIT WHEN i > 6;
  END LOOP;
end;

3、周四到周三计算为一周

create or replace function xxxxx(p_date date)
 return varchar2
 is
   inspection_week varchar(10);
 begin

    select (case
         when to_char(p_date, 'yyyy') >
              to_char(p_date - 10, 'yyyy') and
              to_char(p_date, 'mm') = '01' and
              to_char(p_date - 3, 'IW') > 50 then
          to_char(to_number(to_char(p_date, 'yyyy')) - 1)
         else
          to_char(p_date, 'yyyy')
       end) || to_char(p_date - 3, 'IW') || '周' into  inspection_week
     
    from dual;
    return inspection_week;

 end;

4、计算第一行到当前行的累计值

select  t.day_num transaction_date,
       factory,
       t.floor,
       item_code, 
       transaction_quantity transaction_quantity1,
       sum(transaction_quantity )OVER (partition by factory,floor,item_code ORDER BY day_num ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) transaction_quantity
from xxx  t

5、触发器

create or replace trigger tr_xxx
  after /*insert or*/ update /*or delete*/ on table_xxx
  --for each row
begin
   --dbms_output.put_line('abc');
  PROC_xxxxx('');
end tr_xxx;

6、列乘积

  select 
         bu_name , 
         to_char(bus_date,'yyyy-mm-dd'),
         exp(sum(ln(case when in_num = 0 then 0 else 1-ng_num/in_num end)))  rate,
         sysdate
  from xx.xxx_xx t
  group by bu_name, 
           to_char(bus_date,'yyyy-mm-dd');

7、分析表

analyze table 表名 compute statistics;

8、回退或查询指定时间戳的数据

--查询
select * from 表名  as of timestamp to_timestamp('2023-12-05 08:35:00','yyyy-mm-dd hh24:mi:ss')

--回退
alter table 表名 enable row movement

flashback table  表名 to timestamp to_timestamp('2023-12-05 08:35:00','yyyy-mm-dd hh24:mi:ss')