Oracle random generation date

Source: Internet
Author: User

Oracle random generation date

When developing an Oracle testing environment, companies sometimes need fake data to test the functions and performance of applications. It is really troublesome to manually enter a bunch of SQL statements every time. Just recently, I am learning the stored procedure of oracle, which improves the efficiency a lot.

The specific stored procedure will not be pasted. Here, only part of the content that generates the random date will be pasted:

-- Create a stored procedure

Create or replace procedure test_pro is

M_temp varchar (30 );

M_temp1 varchar (30 );

M_temp2 varchar (30 );

M_birthdate TIMESTAMP;

M_id varchar2 (30 );

Begin

-- Generate a numeric value based on the current date

Select to_char (sysdate, 'J') into m_temp from dual;

Dbms_output.put_line (m_temp );

-- Get the integer according to the user-defined random number range. trunc is different from round rounding, but directly truncated.

Select trunc (dbms_random.value (2456127,245 6127 + 364) into m_temp1 from dual;

Dbms_output.put_line (m_temp1 );

-- Convert the random number generated in the previous step to the date

Select to_date (m_temp1, 'J') into m_temp2 from dual;

Dbms_output.put_line (m_temp2 );

-- Combine various functions to integer a random number and convert it to a date.

Select to_date (trunc (dbms_random.value (2452641,245 2641 + 364), 'J') into m_birthdate from dual;

Dbms_output.put_line (m_birthdate );

-- Convert the date generated in the previous step to a numeric value

Select to_char (m_birthdate, 'yyyymmdd') into m_id from dual;

Dbms_output.put_line (m_id );

End;

-- The execution result in sqlplus is as follows:

SQL> exec test_pro;

2456127

2456224

23-10-12

12-12-03 12.00.00.000000 AM

20031212

PL/SQL procedure successfully completed

-------------------------------------- Split line --------------------------------------

Install Oracle 11gR2 (x64) in CentOS 6.4)

Steps for installing Oracle 11gR2 in vmwarevm

Install Oracle 11g XE R2 In Debian

-------------------------------------- Split line --------------------------------------

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.