oracle 日期介绍:
在oracle中有很多有关日期如:
1、add_months用于从个日期值增加或减少些月份
date_value:=add_months(date_value,number_of_months)
例:
SQL> select add_months(sysdate,12) "Next Year" from dual;

Next Year
----------
13-11月-04

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

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

SQL>

2、current_date返回当前会放时区中当前日期
date_value:=current_date
SQL> column sessiontimezone for a15
SQL> select sessiontimezone,current_date from dual;

SESSIONTIMEZONE CURRENT_DA
--------------- ----------
+08:00 13-11月-03

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

会话已更改

SQL> select sessiontimezone,current_timestamp from dual;

SESSIONTIMEZONE CURRENT_TIMESTAMP
--------------- ------------------------------------
-11:00 12-11月-03 04.59.13.668000 下午 -11:
00

SQL>

3、current_timestamp以timestamp with time zone数据类型返回当前会放时区中当前日期
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
--------------- ------------------------------------
+08:00 13-11月-03 11.56.28.160000 上午 +08:
00

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

会话已更改

SQL> select sessiontimezone,current_timestamp from dual;

SESSIONTIMEZONE CURRENT_TIMESTAMP
--------------- ------------------------------------
-11:00 12-11月-03 04.58.00.243000 下午 -11:
00

SQL>

4、dbtimezone返回时区
varchar_value:=dbtimezone
SQL> select dbtimezone from dual;

DBTIME
------
-07:00

SQL>

5、extract找出日期或间隔值字段值
date_value:=extract(date_field from [datetime_value|erval_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返回包含了日期参数月份最后天日期
date_value:=last_day(date_value)
SQL> select last_day(date'2000-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返回会话中日期和时间
timestamp_value:=localtimestamp
SQL> column localtimestamp format a28
SQL> select localtimestamp from dual;

LOCALTIMESTAMP
----------------------------
13-11月-03 12.09.15.433000
下午

SQL> select localtimestamp,current_timestamp from dual;

LOCALTIMESTAMP CURRENT_TIMESTAMP
---------------------------- ------------------------------------
13-11月-03 12.09.31.006000 13-11月-03 12.09.31.006000 下午 +08:
下午 00


根据日期计算第几周:

//计算第几周
public GetWeekOfCurrDate(DateTime dt)
{
Week = 1;
nYear = dt.Year;
.DateTime FirstDayInYear = DateTime(nYear, 1, 1);
.DateTime LastDayInYear = DateTime(nYear, 12, 31);
DaysOfYear = Convert.ToInt32(LastDayInYear.DayOfYear);
WeekNow = Convert.ToInt32(FirstDayInYear.DayOfWeek) - 1;
(WeekNow < 0) WeekNow = 6;
DayAdd = 6 - WeekNow;
.DateTime BeginDayOfWeek = DateTime(nYear, 1, 1);
.DateTime EndDayOfWeek = BeginDayOfWeek.AddDays(DayAdd);
Week = 2;
for ( i = DayAdd + 1; i <= DaysOfYear; i)
{
BeginDayOfWeek = FirstDayInYear.AddDays(i);
(i + 6 > DaysOfYear)
{
EndDayOfWeek = BeginDayOfWeek.AddDays(DaysOfYear - i - 1);
}

{
EndDayOfWeek = BeginDayOfWeek.AddDays(6);
}

(dt.Month EndDayOfWeek.Month && dt.Day <= EndDayOfWeek.Day)
{
;
}
Week;
i = i + 6;
}
Week;
}



//本周是本年第几周
private DatePart(.DateTime dt)
{
weeknow = Convert.ToInt32(dt.DayOfWeek);//今天星期几
daydf = (-1) * (weeknow+1);//今日和上周末天数差
days = .DateTime.Now.AddDays(daydf).DayOfYear;//上周末是本年第几天
weeks = days/7;
(days%7 != 0)
{
weeks;
}
//此时weeks为上周是本年第几周
(weeks+1);


}
//本周起止日期
private WeekRange(.DateTime dt)
{
weeknow = Convert.ToInt32(dt.DayOfWeek);
daydf = (-1) * weeknow;
dayadd = 6-weeknow;
dateBegin = .DateTime.Now.AddDays(daydf).Date.("MM月dd日");
dateEnd = .DateTime.Now.AddDays(dayadd).Date.("MM月dd日");
dateBegin + " - " +dateEnd;
}



获取某年指定周开始日期和结束日期通用思路方法:

获取某年指定周开始日期和结束日期通用思路方法
/**//// <summary>
/// 获取年中指定周开始日期和结束日期开始日期遵循ISO 8601即星期
/// </summary>
/// <remarks>Write by vrhero</remarks>
/// <param name="year">年(1 到 9999)</param>
/// <param name="weeks">周(1 到 53)</param>
/// <param name="weekrule">确定首周规则</param>
/// <param name="first">当此思路方法返回时则包含参数 year 和 weeks 指定周开始日期 .DateTime 值；如果失败则为 .DateTime.MinValue如果参数 year 或 weeks 超出有效范围则操作失败该参数未经化即被传递</param>
/// <param name="last">当此思路方法返回时则包含参数 year 和 weeks 指定周结束日期 .DateTime 值；如果失败则为 .DateTime.MinValue如果参数 year 或 weeks 超出有效范围则操作失败该参数未经化即被传递</param>
/// <s>成功返回 true否则为 false</s>
public bool GetDaysOfWeeks( year, weeks, CalendarWeekRule weekrule, out DateTime first, out DateTime last)
{
//化 out 参数
first = DateTime.MinValue;
last = DateTime.MinValue;

//不用解释了吧
(year < 1 | year > 9999)
false;

//年最多53周地球人都知道
(weeks < 1 | weeks > 53)
false;

//取当年首日为基准为什么？容易得呗
DateTime firstCurr = DateTime(year, 1, 1);
//取下年首日用于计算
DateTime firstNext = DateTime(year + 1, 1, 1);

//将当年首日星期几转换为数字星期日特别处理ISO 8601 标准
dayOfWeekFirst = ()firstCurr.DayOfWeek;
(dayOfWeekFirst 0) dayOfWeekFirst = 7;

//得到未经验证周首日
first = firstCurr.AddDays((weeks - 1) * 7 - dayOfWeekFirst + 1);

//周首日是上年日期情况
(first.Year < year)
{
switch (weekrule)
{
CalendarWeekRule.FirstDay:
//不用解释了吧
first = firstCurr;
;
CalendarWeekRule.FirstFullWeek:
//顺延周
first = first.AddDays(7);
;
CalendarWeekRule.FirstFourDayWeek:
//周首日距年首日不足4天则顺延周
(firstCurr.Subtract(first).Days > 3)
{
first = first.AddDays(7);
}
;
default:
;
}
}

//得到未经验证周末日
last = first.AddDays(7).AddSeconds(-1);

//周末日是下年日期情况
(last.Year > year)
{
switch (weekrule)
{
CalendarWeekRule.FirstDay:
last = firstNext.AddSeconds(-1);
;
CalendarWeekRule.FirstFullWeek:
//不用处理
;
CalendarWeekRule.FirstFourDayWeek:
//周末日距下年首日不足4天则提前周
(firstNext.Subtract(first).Days < 4)
{
first = first.AddDays(-7);
last = last.AddDays(-7);
}
;
default:
;
}
}
true;
}



有关按周统计数据:

有关按周统计数据(Oracle)
思路方法1:使用to_char

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');

格式'IW'返回当前日期是当年第几周


思路方法2:使用next_day

select sum(sal) ,
NEXT_DAY(trunc(HIREDATE),'星期')-7 weekstart,
NEXT_DAY(trunc(HIREDATE),'星期') weekend
from scott.emp
group by NEXT_DAY(trunc(HIREDATE),'星期');

上面这个例子是在中文集下使用如果在英文集下则使用:

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');

两种思路方法比较第 2种思路方法能够方便给出周开始和周结束日期更方便些



临时表:

需要创建个临时表请举例介绍说明谢谢！
---------------------------------------------------------------

是TEMPORARY
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 _disibledevent=>


execute immediate str;
end;
/



可以把临时表指定为事务相关(默认)或者是会话相关:
ON COMMIT DELETE ROWS:指定临时表是事务相关Oracle在每次提交后截断表
ON COMMIT PRESERVE ROWS:指定临时表是会话相关Oracle在会话中止后截断表

=
可以创建以下两种临时表:
1会话特有临时表
CREATE GLOBAL TEMPORARY <TABLE_NAME> (<column specication>)
ON COMMIT PRESERVE ROWS；

对全局临时表整理总结

在临时表上操作比在般表上操作要快:
1创建临时表不需要往编目表中插入条目临时表使用也不需要访问编目表因此也没有对编目表争用
2仅有创建临时表app才可存取临时表所以在处理临时表时没有锁
3如果指定NOT LOGGED选项在处理临时表时不记日志所以如果有仅在数据库个会话中使用大量临时数据把这些数据存入临时表能大大提高性能
DECLARE GLOBAL TEMPORARY TABLE TT(C1 INT, C2 CHAR(20))；
在CONNECT RESET命令后临时表不再存在
建临时表是动态编译所以对临时表使用也必须放在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事务特有临时表
CREATE GLOBAL TEMPORARY <TABLE_NAME> (<column specication>)
ON COMMIT DELETE ROWS；

在Oracle中全局临时表并不会删除实际上你只需要建立次以后直接应用就行了这和MS和Sybase不样实际上在断开数据库连接时临时表中数据自动清空区别Session的间是隔离不许要当心相互影响不过如果起用了连接共享话你要用On Commit delete rows使数据仅在事物内部有效

3建立临时表
临时表定义对所有会话SESSION都是可见,但是表中数据只对当前会话或者事务有效.
建立思路方法:
1) _disibledevent=>strdate1 := TO_CHAR(SYSDATE, 'YYYYMMDD') + '0000';
strdate2 := TO_CHAR(SYSDATE + 1, 'YYYYMMDD') + '0000';

--结果1
SELECT count(*) o blzz FROM ZZRK.JB_TAB WHERE FZRQ = strdate;

--结果2
SELECT count(*)
o lgzs
FROM lgy.lgy_gnlkjbxx
WHERE RZSJ >= strdate1
and RZSJ < strdate2;

OPEN Index_OUT FOR
SELECT blzz,lgzs
END;
END;
end;



存储过程:

/// <summary>
/// 得到统计数据集
/// </summary>
/// <param name="cmdText"></param>
/// <param name="Stime"></param>
/// <param name="Etime"></param>


/// <param name="Type"></param>
private DataSet GetTjData( cmdText, Stime, Etime, Type)
{
DataSet ds = DataSet;
OracleConnection Con = .Data.OracleClient.OracleConnection(AppConfig.ZHCX);
Con.Open;
OracleCommand cmd = OracleCommand(cmdText, Con);
cmd.CommandType = CommandType.StoredProcedure;
OracleParameter parm ={
OracleParameter("Stime", OracleType.DateTime),
OracleParameter("Etime", OracleType.DateTime),
OracleParameter("Type", OracleType.Int16),
OracleParameter("cur_OUT", OracleType.Cursor) };
parm[0].Value = DateTime.Parse(Stime);
parm[1].Value = DateTime.Parse(Etime).AddDays(1);
parm[2].Value = .Parse(Type);
parm[3].Direction = ParameterDirection.Output;

foreach (OracleParameter op in parm)
{
cmd.Parameters.Add(op);
}
OracleDataAdapter oda = OracleDataAdapter(cmd);
oda.Fill(ds);
Con.Close;
ds;
}