oracle SQL生成一些常用数据

 生成A到Z的字符

select chr(ascii('A') + level - 1) from dual connect by rownum <= 26

 

自动生成日期

with datas as (
SELECT TO_CHAR(TRUNC(TO_DATE('20170410', 'yyyymmdd') - LEVEL + 1),
               'yyyymmdd') AS DATES
  FROM DUAL
CONNECT BY LEVEL <= (TO_DATE('20170410', 'yyyymmdd') -
           TO_DATE('20170401', 'yyyymmdd') + 1)
 ORDER BY DATES
 )

 

生成随机字母数字随机6位码

create or replace function spreadnoCode return varchar2 is
  sRand varchar2(6);
  str varchar2(36):='ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
  v_i number;
  char1 char;
begin
   v_i :=0;
  while(v_i <6 ) loop
  
   char1:=substr(str,TRUNC(1+36*dbms_random.value),1);
  
   sRand:=sRand||char1;
  v_i:=v_i+1;
  
  end loop; 
  
  return(sRand);
end spreadnoCode;