oracle语句杂项

最新推荐文章于 2024-02-05 18:48:54 发布

原创最新推荐文章于 2024-02-05 18:48:54 发布 · 214 阅读

0 ·

CC 4.0 BY-SA版权

本文深入探讨了SQL查询的高级技巧，包括复杂查询构造、数据聚合、日期操作、MD5加密应用、正则表达式筛选、多条件模糊搜索、字段合并、循环与存储过程的运用。通过实际案例，读者将掌握如何优化查询效率，解决特定业务问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

--从数据库中找字段名是customer的表

select distinct(table_name) from dba_tab_columns where column_name='customer'

--按照FMaterialID 分组，FBaseConvsRate 排序即相同的FMaterialID 按从大到小排序

select FMaterialID,FMeasureUnitID,FBaseConvsRate,row_number() over(partition by FMaterialID order by FBaseConvsRate desc) rn from T_BD_MultiMeasureUnit

--求本月第一天和最后一天

 select last_day(add_months(sysdate,-1))+1,last_day(sysdate) from dual

--MD5加密

declare
v2 varchar2(32);
begin
v2 := Utl_Raw.Cast_To_Raw(sys.dbms_obfuscation_toolkit.md5(input_string => '131106'));--调用MD加密
dbms_output.put_line(lower(v2));--转换成小写输出   (服务器端使用MD5默认加密是小写)
end;

正则表达式取字符串的相关字符

select regexp_replace('(300mL*6罐)*4组','[^0-9,m,L,*]') from dual;

模糊查询

--查询FieldName中以1开头45结束的记录并且长度是5位(_或者.表示位数)

select * from fzq where FieldName like '1__45';
select * from fzq where regexp_like(FieldName,'1..45');

--查询FieldName中以1开头45结束的记录并且长度是5位并且全部是数字的记录。
select * from fzq where regexp_like(FieldName,'1[0-9]{2}45');

---多条件模糊查询
regexp_like(字段名, '(中国|美国|...)') //全模糊匹配
regexp_like(字段名, '^(中国|美国|...)') ";//右模糊匹配
regexp_like(字段名, '(中国|美国|...)$') ";//左模糊匹配

把不同行相同列的不同字段合并为一个字段

select wmsys.wm_concat(distinct to_char(cus.fname_l2)) from CT_SD_SaleOrderBill bill 
left join T_BD_Customer cus on bill.CFOrderCustomerID=cus.fid 
where bill.fbizdate>=to_timestamp('2020-05-28 00:00:00','yyyy-mm-dd hh24:mi:ss.ff')

listagg(字段，拼接符号) WITHIN GROUP (order by 字段) 将多行合并成一行

listagg(flot,' ') within group(order by flot) --以空格合并每个flot字段

循环执行

declare
sql_tem Varchar2(100);
str varchar2(1000);
begin
   for i in 1..67 Loop
     sql_tem :='select newbosid('||' '''||'E646C338'||''''||') from dual';
     EXECUTE IMMEDIATE sql_tem into str;
     dbms_output.put_line(str);
  end loop;
end;

游标

declare
  --定义游标
  cursor cur_xsjbxx is
select * from T_ORG_BaseUnit where FIsCU=1 and length(fname_l2)>3 and length(fnumber)>4;
  --定义记录变量  
  ls_curinfo cur_xsjbxx%rowtype;
begin
  open cur_xsjbxx;--打开游标
  loop
    FETCH cur_xsjbxx INTO ls_curinfo;--获取记录值
    EXIT WHEN cur_xsjbxx%NOTFOUND;
    dbms_output.put_line(ls_curinfo.fid);
  end loop;
  close cur_xsjbxx;--关闭游标
end;

存储过程

create or replace procedure PROC_purCalculation as
begin
  DELETE FROM ct_pur_purCalculation;
  INSERT INTO ct_pur_purCalculation VALUE (deptid, supid, amt)
    select deptid,supid,sum(aramount) + sum(unverifyamt) - sum(camt) + sum(damt) amt from a;
  INSERT INTO ct_pur_purCalculation VALUE (deptid, BRANDID, amt, cost)
    select ob.fcompanyid,
           mat.cfonebrandid,
           Sum(obe.frecievepayamount) As amt,
           Sum(obe.FQuantity * sie.FUnitActualCost) As cost
      From t_ar_otherbill ob;
end PROC_purCalculation;

带游标及参数的存储过程

create or replace procedure pro_distributorInitByDeptDate(in_code     in VARCHAR2,
                                                          in_date     in VARCHAR2,
                                                          in_userId   in VARCHAR2,
                                                          in_userName in VARCHAR2) is
maxDate varchar(20);
begin
    select to_char(max(createdate), 'yyyy-mm-dd') into maxDate 
    from TAB_COURIER_CAR where createleveldeptcode = in_code;
  declare cursor cur_courier is
      select * from TAB_COURIER_CAR where createleveldeptcode = in_code 
      and to_char(createdate,'yyyy-mm-dd')=maxDate;
  courier_info cur_courier%rowtype;
  begin
    open cur_courier;
    loop
      fetch cur_courier into courier_info;
      exit when cur_courier%notfound;
      insert into TAB_COURIER_CAR
        values
          (commonsequence.nextval,
           courier_info.carnum,
           to_date(in_date, 'yyyy-mm-dd'),
           to_date(in_date, 'yyyy-mm-dd'),
           in_userId,
           in_userId,
           in_userName,
           in_userName,
           courier_info.createdeptid,
           courier_info.createleveldeptcode,
           courier_info.createdeptcode,
           courier_info.id);
      declare cursor cur_courierdet is
           select a.*, b.id oid from TAB_COURIER_CLASSES a
           left join TAB_COURIER_CAR b on a.tabcouriercarid = b.oid 
           where b.oid = courier_info.id;
      courierdet_info cur_courierdet%rowtype;
      begin
        open cur_courierdet;
        loop
          fetch cur_courierdet into courierdet_info;
          exit when cur_courierdet%notfound;
          insert into TAB_COURIER_CLASSES
          values
            (commonsequence.nextval,
             courierdet_info.delivername,
             courierdet_info.delivercode,
             courierdet_info.delresource,
             '',
             '',
             to_date(in_date, 'yyyy-mm-dd'),
             to_date(in_date, 'yyyy-mm-dd'),
             in_userId,
             in_userId,
             in_userName,
             in_userName,
             courierdet_info.createdeptid,
             courierdet_info.createleveldeptcode,
             '',
             courierdet_info.oid);
          end loop;
          close cur_courierdet 
      end;    
    end loop;
    close cur_courier;
  end;
  commit;
end;
call pro_distributorInitByDeptDate('010102','2020-08-20','34939074','你是谁')