oracle生成随机时间函数,ORACLE 函数 随机生成手机号,时间

随机生成时间

create or replace function getRoundDate(begindate in varchar2,enddate in varchar2)

return date

is

v_date date;

v_b varchar2(8);

v_e varchar2(8);

begin

v_b:=begindate;

v_e:=enddate;

SELECT to_date(TRUNC(DBMS_RANDOM.VALUE(

to_number(to_char(to_date(v_b,'yyyymmdd'),'J')),

to_number(to_char(to_date(v_e,'yyyymmdd')+1,'J')))),'J')+

DBMS_RANDOM.VALUE(1,3600)/3600

into v_date

FROM dual;

return v_date;

end;

随机生成手机号码

create or replace function my_mobile_num

return varchar2

is

v_mobile varchar2(50);

TYPE segnum IS VARRAY (35) OF VARCHAR2 (3);

segnum_set   segnum := segnum ('133',

'153',

'180',

'181',

'189',

'177',

'130',

'131',

'132',

'155',

'156',

'145',

'185',

'186',

'176',

'134',

'135',

'136',

'137',

'138',

'139',

'150',

'151',

'152',

'158',

'159',

'182',

'183',

'184',

'157',

'187',

'188',

'147',

'178',

'170');

v_name1 nvarchar2(20);

v_name2 nvarchar2(20);

v_name3 nvarchar2(20);

begin

select segnum_set(trunc(dbms_random.value(1,35))) into v_name1 from dual;

select substr(cast(dbms_random.value as varchar2(38)),3,4)  into v_name2  from dual;

select substr(cast(dbms_random.value as varchar2(38)),3,4)  into v_name3  from dual ;

v_mobile:=v_name1||v_name2||v_name3;         return v_mobile;      end;