Oracle Database functions for making reports or data statistics

Introduction:During the development process, there are often modules that need to prepare reports or data statistics. Here we will record the oralce functions that have used to prepare reports during the development process.

Several principles for creating a report:

• All complex query statements are saved in a new view.

For example:

1. Create a view

Create or replace view v_period as select to_char (sysdate, 'yyyy-MM-DD ') today from dual

2. query view statements

select v.* from V_PERIOD v

Oracle statements used to create reports

1. Obtain the date of the week and the day of the week from Monday to Friday of the next week and the day of the week Based on the date.

1. Use to_char with the decode and to_number functions to obtain the date from Monday to Friday.

/*** Obtain the date from Monday to Sunday * provide the date from Monday to Sunday and the current date is the day of the week * 1. to_char (sysdate, 'D'): returns the day of the week for the current date X 2. to_number (char): This function converts the data contained in a string to number type data * 3. decode (value, if1, then1, if2, then2, if3, then3 ,... else): In logical programming, if-then-else is often used for logical judgment * @ author luoxinyu * @ Version 1.0, */select to_char (sysdate, 'yyyy-MM-DD ') Today, to_char (sysdate-to_number (to_char (sysdate, 'd) + 2, 'yyyy-MM-DD') Monday, to_char (sysdate-to_number (to_char (sysdate, 'D') + 3, 'yyyy-MM-DD ') Tuesday, to_char (sysdate-to_number (to_char (sysdate, 'D ') + 4, 'yyyy-MM-DD') Wednesday, to_char (sysdate-to_number (to_char (sysdate, 'D') + 5, 'yyyy-MM-DD ') thursday, to_char (sysdate-to_number (to_char (sysdate, 'D') + 6, 'yyyy-MM-DD ') Friday, to_char (sysdate-to_number (to_char (sysdate, 'd) + 7, 'yyyy-MM-DD ') Saturday, to_char (sysdate-to_number (to_char (sysdate, 'D') + 1, 'yyyy-MM-DD ') sunday, decode (to_char (sysdate, 'd), '1', 'sunday', '2', 'monday', '3', 'tues ', '4', 'weday', '5', 'thurs', '6', 'Friday', '7', 'satur') from dual

2. next_day combined with the substr function to obtain the next Monday to Friday date

/*** Get the date from the next Monday to Sunday * provide the date from the next Monday to Sunday and the current date is the day of the week * 1. next_day: used to calculate the time of Y in the first week after X time. X is a time, and Y is one from Monday to Sunday. It may also be replaced by numbers 1-7, but when numbers are used, 1 indicates Sunday 2 indicates Monday * 2. substr: String truncation function, substr (string, start_postion, [lenght]) * @ author luoxinyu * @ Version 1.0, 2013-05-28 */select to_char (sysdate, 'yyyy-MM-DD ') today, 'Week' | substr ('may 25, 1234 five or six ', to_number (to_char (sysdate, 'D'), 1) day of the week, to_char (next_day (sysdate, 1), 'yyyy-MM-DD ') Sunday, to_char (next_day (sysdate, 2), 'yyyy-MM-DD') Monday, to_char (next_day (sysdate, 3 ), 'yyyy-MM-DD ') Tuesday, to_char (next_day (sysdate, 4), 'yyyy-MM-DD') Wednesday, to_char (next_day (sysdate, 5), 'yyyy-MM-DD ') thursday, to_char (next_day (sysdate, 6), 'yyyy-MM-DD ') Friday, to_char (next_day (sysdate, 7), 'yyyy-MM-DD') Saturday from dual

References: