Oracle Date segment data statistics

Oracle date functions:
There are many date functions in oracle, such:
1. add_months () is used to increase or decrease the number of months from a date value.
Date_value: = add_months (date_value, number_of_months)
Example:
SQL> select add_months (sysdate, 12) "Next Year" from dual;

Next Year
----------
April 11-04

SQL> select add_months (sysdate, 112) "Last Year" from dual;

Last Year
----------
13-3-13

SQL>

2. current_date () returns the current date in the current time zone.
Date_value: = current_date
SQL> column sessiontimezone for a15
SQL> select sessiontimezone, current_date from dual;

SESSIONTIMEZONE CURRENT_DA
-------------------------
+-11-03

SQL> alter session set time_zone = '-11: 00'
2/

The session has been changed.

SQL> select sessiontimezone, current_timestamp from dual;

SESSIONTIMEZONE CURRENT_TIMESTAMP
---------------------------------------------------
--03 04.59.13.668000 pm-11:
00

SQL>

3. current_timestamp () returns the current date in the current time zone as the timestamp with time zone Data Type
Timestamp_with_time_zone_value: = current_timestamp ([timestamp_precision])
SQL> column sessiontimezone for a15
SQL> column current_timestamp format a36
SQL> select sessiontimezone, current_timestamp from dual;

SESSIONTIMEZONE CURRENT_TIMESTAMP
---------------------------------------------------
+-03 11.56.28.160000 AM + 08:
00

SQL> alter session set time_zone = '-11: 00'
2/

The session has been changed.

SQL> select sessiontimezone, current_timestamp from dual;

SESSIONTIMEZONE CURRENT_TIMESTAMP
---------------------------------------------------
--03 04.58.00.243000 pm-11:
00

SQL>

4. dbtimezone () returns the time zone
Varchar_value: = dbtimezone
SQL> select dbtimezone from dual;

DBTIME
------
-07:00

SQL>

5. extract () identifies the field value of the date or interval value
Date_value: = extract (date_field from [datetime_value | interval_value])
SQL> select extract (month from sysdate) "This Month" from dual;

This Month
----------
11

SQL> select extract (year from add_months (sysdate, 36) "3 Years Out" from dual;

3 Years Out
-----------
2006

SQL>

6. last_day () returns the date of the last day of the month that contains the date parameter.
Date_value: = last_day (date_value)
SQL> select last_day (date '2017-02-01 ') "Leap Yr? "From dual;

Leap Yr?
----------
29-2-00

SQL> select last_day (sysdate) "Last day of this month" from dual;

Last day o
----------
30-11-03

SQL>

7. localtimestamp () returns the date and time in the session.
Timestamp_value: = localtimestamp
SQL> column localtimestamp format a28
SQL> select localtimestamp from dual;

LOCALTIMESTAMP
----------------------------
13-11-03 12.09.15.433000
Afternoon

SQL> select localtimestamp, current_timestamp from dual;

LOCALTIMESTAMP CURRENT_TIMESTAMP
----------------------------------------------------------------
13-11-03 12.09.31.006000 13-11-03 12.09.31.006000 PM + 08:
00 pm

Calculate the week by date:

// Calculate the week
Public int GetWeekOfCurrDate (DateTime dt)
{
Int Week = 1;
Int nYear = dt. Year;
System. DateTime FirstDayInYear = new DateTime (nYear, 1, 1 );
System. DateTime LastDayInYear = new DateTime (nYear, 12, 31 );
Int DaysOfYear = Convert. ToInt32 (LastDayInYear. DayOfYear );
Int WeekNow = Convert. ToInt32 (FirstDayInYear. DayOfWeek)-1;
If (WeekNow <0) WeekNow = 6;
Int DayAdd = 6-WeekNow;
System. DateTime BeginDayOfWeek = new DateTime (nYear, 1, 1 );
System. DateTime EndDayOfWeek = BeginDayOfWeek. AddDays (DayAdd );
Week = 2;
For (int I = DayAdd + 1; I <= DaysOfYear; I ++)
{
BeginDayOfWeek = FirstDayInYear. AddDays (I );
If (I + 6> DaysOfYear)
{
EndDayOfWeek = BeginDayOfWeek. AddDays (DaysOfYear-I-1 );
}
Else
{
EndDayOfWeek = BeginDayOfWeek. AddDays (6 );
}

If (dt. Month = EndDayOfWeek. Month & dt. Day <= EndDayOfWeek. Day)
{
Break;
}
Week ++;
I = I + 6;
}
Return Week;
}

 

// The week of the year
Private int DatePart (System. DateTime dt)
{
Int weeknow = Convert. ToInt32 (dt. DayOfWeek); // today's day of the week
Int daydiff = (-1) * (weeknow + 1); // The day difference between today and last weekend
Int days = System. DateTime. Now. AddDays (daydiff). DayOfYear; // the last day of the year.
Int weeks = days/7;
If (days % 7! = 0)
{
Weeks ++;
}
// At this time, weeks is the week of the current year.
Return (weeks + 1 );
}
// Start and end dates of the week
Private string WeekRange (System. DateTime dt)
{
Int weeknow = Convert. ToInt32 (dt. DayOfWeek );
Int daydiff = (-1) * weeknow;
Int dayadd = 6-weeknow;
String dateBegin = System. DateTime. Now. AddDays (daydiff). Date. ToString ("MM dd ");
String dateEnd = System. DateTime. Now. AddDays (dayadd). Date. ToString ("MM dd ");
Return dateBegin + "-" + dateEnd;
}

 

To obtain the start and end dates of a specified week in a year:

Obtain the start and end dates of a specified week in a year.
/** // <Summary>
/// Obtain the start and end dates of the specified week in a year. The start date follows ISO 8601, that is, Monday.
/// </Summary>
/// <Remarks> Write by vrhero </remarks>
/// <Param name = "year"> year (1 to 9999) </param>
/// <Param name = "weeks"> Week (1 to 53) </param>
/// <Param name = "weekrule"> determine the rule for the first week </param>
/// <Param name = "first"> when this method is returned, it contains the System of the week's start date specified by year and weeks. dateTime value; if it fails, it is System. dateTime. minValue. If the year or weeks parameter exceeds the valid range, the operation fails. This parameter is passed without initialization. </Param>
/// <Param name = "last"> when this method is returned, it contains the System of the end date of the week specified by year and weeks. dateTime value; if it fails, it is System. dateTime. minValue. If the year or weeks parameter exceeds the valid range, the operation fails. This parameter is passed without initialization. </Param>
/// <Returns> success returns true; otherwise, false. </Returns>
Public static bool GetDaysOfWeeks (int year, int weeks, CalendarWeekRule weekrule, out DateTime first, out DateTime last)
{
// Initialize the out Parameter
First = DateTime. MinValue;
Last = DateTime. MinValue;

// No need to explain
If (year <1 | year> 9999)
Return false;

// Everyone on Earth knows this in a year for a maximum of 53 weeks.
If (weeks <1 | weeks> 53)
Return false;

// Why is the first day of the current year the benchmark? Easy
DateTime firstCurr = new DateTime (year, 1, 1 );
// Remove the first day of a year for Calculation
DateTime firstNext = new DateTime (year + 1, 1, 1 );

// Convert the day of the day on the first day of the current year to the digital Sunday special processing ISO 8601 Standard
Int dayOfWeekFirst = (int) firstCurr. DayOfWeek;
If (dayOfWeekFirst = 0) dayOfWeekFirst = 7;

// First day of unverified week
First = firstCurr. AddDays (weeks-1) * 7-dayOfWeekFirst + 1 );

// The first day of the week is the date of the previous year
If (first. Year <year)
{
Switch (weekrule)
{
Case CalendarWeekRule. FirstDay:
// No need to explain
First = firstCurr;
Break;
Case CalendarWeekRule. FirstFullWeek:
// Extend by one week
First = first. AddDays (7 );
Break;
Case CalendarWeekRule. FirstFourDayWeek:
// The first day of the week is extended by one week if less than four days from the first day of the year
If (firstCurr. Subtract (first). Days> 3)
{
First = first. AddDays (7 );
}
Break;
Default:
Break;
}
}

// Get unverified weekend days
Last = first. AddDays (7). AddSeconds (-1 );

// When the weekend day is the date of the next year
If (last. Year> year)
{
Switch (weekrule)
{
Case CalendarWeekRule. FirstDay:
Last = firstNext. AddSeconds (-1 );
Break;
Case CalendarWeekRule. FirstFullWeek:
// No need to handle
Break;
Case CalendarWeekRule. FirstFourDayWeek:
// One week before the first day of the next year.
If (firstNext. Subtract (first). Days <4)
{
First = first. AddDays (-7 );
Last = last. AddDays (-7 );
}
Break;
Default:
Break;
}
}
Return true;
}

 

Weekly Statistics:

Weekly Statistics (Oracle)
Method 1: Use the to_char Function

Select sum (sal ),
To_char (HIREDATE, 'yyyy') | ':' | to_char (HIREDATE, 'iw') week_sn
From scott. emp
Group by to_char (HIREDATE, 'yyyy') | ':' | to_char (HIREDATE, 'iw ');

The format 'aw' returns the week number of the current year.

Method 2: Use the next_day () function

Select sum (sal ),
NEXT_DAY (trunc (HIREDATE), 'monday')-7 weekstart,
NEXT_DAY (trunc (HIREDATE), 'monday') weekend
From scott. emp
Group by NEXT_DAY (trunc (HIREDATE), 'monday ');

The above example is used in the Chinese character set. If the English character set is used:

Select sum (sal ),
NEXT_DAY (trunc (HIREDATE), 'monday')-7 weekstart,
NEXT_DAY (trunc (HIREDATE), 'monday') weekend
From scott. emp
Group by NEXT_DAY (trunc (HIREDATE), 'monday ');

The two methods are compared. The second method can easily give the week start and end dates, which is more convenient.

 

Temporary table:

You need to create a temporary table. For example, thank you!
---------------------------------------------------------------

Yestemporary
Create global temporary table flight_schedule (
Startdate DATE,
Enddate DATE,
Cost NUMBER)

---------------------------------------------------------------

Create proecdure name_pro
As
Str varchar2 (100 );
Begin
Str: = 'Create global temporary table tablename on commit preserve rows as select * from others_table ';
Execute immediate str;
End;
/



You can specify a temporary table as transaction-related (default) or session-related:
On commit delete rows: specifies that the temporary table is transaction-related. Oracle truncates the table after each COMMIT.
On commit preserve rows: specifies that the temporary table is session-related. Oracle truncates the table after the session is terminated.

========================
You can create two types of temporary tables:
1. Session-specific temporary table
Create global temporary <TABLE_NAME> (<column specification>)
On commit preserve rows;
==========
Summary of global temporary tables

Operations on temporary tables are faster than those on normal tables. Because:
1. When creating a temporary table, you do not need to insert entries into the catalog table. You do not need to access the catalog table to use the temporary table. Therefore, there is no competition for the catalog table.
2. Only the app that creates a temporary table can access the temporary table, so there is no lock when processing the temporary table.
3. If the not logged option is specified, logs are NOT recorded when processing temporary tables. Therefore, if a large amount of temporary data is used in only one session of the database, saving the data into the temporary table can greatly improve the performance.
Declare global temporary table tt (C1 INT, C2 CHAR (20 ));
After the connect reset command, the temporary table no longer exists.
Temporary tables are dynamically compiled. Therefore, the use of temporary tables must be placed after declare curser.
Create procedure INSTT2 (P1 INT, P2 CHAR (20 ))
BEGIN
Declare global temporary table tt (C1 INT, C2 CHAR (20) %
Insert into session. tt values (P1, P2 );
BEGIN
DECLARE C1 cursor with return for select * from session. TT;
END;
END %

2. Temporary tables specific to transactions
Create global temporary <TABLE_NAME> (<column specification>)
On commit delete rows;

In Oracle, the global temporary table is not deleted. In fact, you only need to create it once and then apply it directly. This is different from MS and Sybase. In fact, when the database is disconnected, the data in the temporary table is automatically cleared. Different sessions are isolated from each other. Do not be careful about mutual impact. However, if a connection is used for sharing, you must use On Commit delete rows to make the data valid only within the transaction.

3. Create a temporary table
The definition of a temporary table is visible to all sessions, but the data in the table is only valid for the current SESSION or transaction.
Creation method:
1) on commit delete rows defines how to create a transaction-level temporary table.
Create global temporary table admin_work_area
(Startdate DATE,
Enddate DATE,
Class CHAR (20 ))
On commit delete rows;
EXAMPLE:
SQL> CREATE GLOBAL TEMPORARY TABLE admin_work_area
2 (startdate DATE,
3 enddate DATE,
4 class CHAR (20 ))
5 on commit delete rows;
SQL> create table permernate (a number );
SQL> insert into admin_work_area values (sysdate, sysdate, 'temperary table ');
SQL> insert into permernate values (1 );
SQL> commit;
SQL> select * from admin_work_area;
SQL> select * from permernate;
A
1
2) on commit preserve rows defines how to create a session-level temporary table.
Create global temporary table admin_work_area
(Startdate DATE,
Enddate DATE,
Class CHAR (20 ))
On commit preserve rows;
EXAMPLE:

Session 1:
SQL> drop table admin_work_area;
SQL> CREATE GLOBAL TEMPORARY TABLE admin_work_area
2 (startdate DATE,
3 enddate DATE,
4 class CHAR (20 ))
5 on commit preserve rows;
SQL> insert into permernate values (2 );
SQL> insert into admin_work_area values (sysdate, sysdate, 'session temperary ');
SQL> commit;
SQL> select * from permernate;

A
----------
1
2

SQL> select * from admin_work_area;

STARTDATE ENDDATE CLASS
----------------------------------------
17-1 & Ocirc; & Acirc;-03 17-1 & Ocirc; & Acirc;-03 session temperary

Session 2:

SQL> select * from permernate;

A
----------
1
2

SQL> select * from admin_work_area;

Row not selected.

Session 2 cannot see the data in the temporary table in session 1.

 

Implement the stored procedure based on the above information:

Create or replace package body TodayALL_zjz IS

-- Homepage statistics on the first layer -- Data
PROCEDURE GetIndexTj (Index_OUT out mycursor)
BEGIN
-- Single-Dimension Array
DECLARE
--
Fa integer;
--
Pa integer;
--
Zhtf integer;
--
Djclry integer;
--
Kywp integer;
--
Blzz integer;
--
Lgzs integer;

-- Mydate date;
Strdate varchar (8 );
Strdate1 varchar (12 );
Strdate2 varchar (12 );

Begin
Strdate: = TO_CHAR (SYSDATE, 'yyyymmdd ');
Strdate1: = TO_CHAR (SYSDATE, 'yyyymmdd') + '123 ';
Strdate2: = TO_CHAR (SYSDATE + 1, 'yyyymmdd') + '123 ';

-- Result 1
SELECT count (*) into blzz from zzrk. JB_TAB where fzrq = strdate;

-- Result 2
SELECT count (*)
Into lgzs
FROM lgy. lgy_gnlkjbxx
Where rzsj> = strdate1
And RZSJ <strdate2;

OPEN Index_OUT
SELECT blzz, lgzs
END;
END;
End;

 

Call the stored procedure:

/// <Summary>
/// Obtain the statistical data set
/// </Summary>
/// <Param name = "plain text"> </param>
/// <Param name = "Stime"> </param>
/// <Param name = "Etime"> </param>
/// <Param name = "Type"> </param>
Private DataSet GetTjData (string plain text, string Stime, string Etime, string Type)
{
DataSet ds = new DataSet ();
OracleConnection Con = new System. Data. OracleClient. OracleConnection (AppConfig. ZHCX );
Con. Open ();
OracleCommand cmd = new OracleCommand (plain text, Con );
Cmd. CommandType = CommandType. StoredProcedure;
OracleParameter [] parm = {
New OracleParameter ("Stime", OracleType. DateTime ),
New OracleParameter ("Etime", OracleType. DateTime ),
New OracleParameter ("Type", OracleType. Int16 ),
New OracleParameter ("cur_OUT", OracleType. Cursor )};
Parm [0]. Value = DateTime. Parse (Stime );
Parm [1]. Value = DateTime. Parse (Etime). AddDays (1 );
Parm [2]. Value = int. Parse (Type );
Parm [3]. Direction = ParameterDirection. Output;

Foreach (OracleParameter op in parm)
{
Cmd. Parameters. Add (op );
}
OracleDataAdapter oda = new OracleDataAdapter (cmd );
Oda. Fill (ds );
Con. Close ();
Return ds;
}