create or replace package PKG_GENERAL is
TYPE ROW_CURSOR IS REF CURSOR;
–返回指定日期的月份中有多少天
function daysInMonth(rq date) return number;
–返回指定日期离月底还有多少天
function daysLeft(rq date) return number;
–返回指定日期离年底还有多少天
function daysLeftOfYear(rq date) return number;
–返回指定日期当年有多少天
function daysOfYear(rq date) return number;
end PKG_GENERAL;
create or replace package body PKG_GENERAL is
–select extract(day from sysdate) year from dual 返回日期中指定的年月日 如:年2006 月7 日30
function daysInMonth
(rq in date)
return number
as
daysOfMonth number(2);
begin
select (last_day(trunc(rq))-trunc(rq,‘Month’)+1) into daysOfMonth from dual;
return daysOfMonth;
end daysInMonth;
–返回指定日期离月底还有多少天
function daysLeft
(rq in date)
return number
as
dl number(2);
begin
select last_day(trunc(rq))-trunc(rq) into dl from dual;
return dl;
end daysLeft;
–返回指定日期离年底还有多少天
function daysLeftOfYear
(rq date)
return number
as
dys number(3);
tempDate date :=add_months(rq,1);
begin
dys:=daysLeft(rq);
while to_char(tempDate,‘yyyy’)=to_char(rq,‘yyyy’) loop
dys:=dys+daysInMonth(tempDate);
tempDate:=add_months(tempDate,1);
end loop;
return dys;
end daysLeftOfYear;
–返回指定日期当年的天数
function daysOfYear(rq date)
return number
as
dys number(3);
tempDate date :=add_months(trunc(rq,‘yyyy’),1);
begin
dys:=daysLeft(trunc(rq,‘yyyy’));
while to_char(tempDate,‘yyyy’)=to_char(rq,‘yyyy’) loop
dys:=dys+daysInMonth(tempDate);
tempDate:=add_months(tempDate,1);
end loop;
return dys+1;
end daysOfYear;
end PKG_GENERAL;
本文地址:https://blog.csdn.net/xyx_0300/article/details/107673077
如对本文有疑问, 点击进行留言回复!!
Oracle Dataguard故障转移(failover)操作
Oracle之 LISTAGG () OVER(PARTITION BY)函数
Oracle查询用户,表结构,表索引,存储过程,触发器等信息整理
oracle的自连接、内连接、外连接、交叉连接、自然连接、等值连接、不等值连接
Oracle数据库使用replace函数批量删除数据中的双引号
网友评论