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 --------------------------------------