Oracle 函数返回calendar

最新推荐文章于 2022-07-22 23:33:49 发布
最新推荐文章于 2022-07-22 23:33:49 发布
阅读量175 收藏
点赞数
分类专栏： 数据库(Oracle/Postgresql/Hypertable等) 文章标签： Oracle VB SQL ASP Unix
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/lovejuan1314/article/details/83400521
版权
转自:[url]http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=515&lngWId=5[/url] 

--**************************************
--     
-- Name: calproc.sql
-- Description:Creates a 12 month calend
--     ar for any given year
-- By: Sergio Rueda
--
-- Inputs:year
--
-- Returns:12 month calendar
--
-- Assumes:nothing
--
--This code is copyrighted and has-- limited warranties.Please see http://
--     www.Planet-Source-Code.com/vb/scripts/Sh
--     owCode.asp?txtCodeId=515&lngWId=5--for details.--**************************************
--     

UNIX korn shell sample calling program:
##
# creates a calendar FOR a given year
#
# Input:Year
#
# Output: 12 month calendar
# stored IN a FILE called CALENDAR.year
#
##
clear 
echo " "
echo "Now creating file: calendar.$1"
echo " "
echo "
SET serveroutput ON size 20000 format WORD_WRAPPED
EXECUTE srmcal($1);
" | sqlplus -s gemsprod/gemsprod > calendar.$1
==================================================
PL/SQL PROCEDURE TO CREATE THE CALENDAR
-- Written by Sergio Rueda.
-- 07/10/2002
--
-- Procedure to create a 12 month calend
--     ar for any given year.
--
--
CREATE or replace PROCEDURE srmcal(ano IN number) AS
ldom number(02);
line varchar2(26);
mname varchar(10);
wday number(02);


    begin
    FOR mes IN 1 .. 12 loop
     SELECT substr(last_day(to_date('01-'||TO_CHAR(mes)||'-'||TO_CHAR(ano),'DD-MM-YYYY')),1,2) INTO ldom FROM dual;
     SELECT to_char(to_date('01-'||TO_CHAR(mes)||'-'||TO_CHAR(ano),'DD-MM-YYYY'), 'Month') INTO mname FROM dual;
     SELECT to_number( to_char(to_date('01-'||TO_CHAR(mes)||'-'||TO_CHAR(ano),'DD-MM-YYYY'),'D')) INTO wday FROM dual;
    --
    --
     dbms_output.put_line('.');
     dbms_output.put_line('.');
     dbms_output.put_line('. '||mname||' -- '|| ano);
     dbms_output.put_line('.');
     dbms_output.put_line('+ SUNMONTUEWEDTHUFRISAT +');
     dbms_output.put_line('+------------------------------------------------+');
    --
    --
     FOR dia IN 1 .. wday - 1 loop
     dbms_output.put( '|......');
 END loop;

--
--
 FOR dia IN 1 .. ldom loop
 IF ( dia < 10) THEN
 line := '|'||dia||' ';
 ELSE
 line := '| '||dia||' ';
 END if;

--
--
IF mod(dia + (wday - 1),7) = 0 THEN
dbms_output.put(line || '|');
dbms_output.new_line;
dbms_output.put_line('+------+------+------+------+------+------+------+ ');
else
dbms_output.put(line);
END if;

END loop;

dbms_output.new_line;
dbms_output.put_line('+------+------+------+------+------+------+------+ ');
 END loop;

end;

/


//执行结果:
SQL> set serveroutput ON size 20000 format WORD_WRAPPED ;
SQL> exec srmcal(2009);

.
.
. January   -- 2009
.
+ SUNMONTUEWEDTHUFRISAT +
+------------------------------------------------+
|......|......|......|......|1 |2 |3 |
+------+------+------+------+------+------+------+
|4 |5 |6 |7 |8 |9 | 10 |
+------+------+------+------+------+------+------+
| 11 | 12 | 13 | 14 | 15 | 16 | 17 |
+------+------+------+------+------+------+------+
| 18 | 19 | 20 | 21 | 22 | 23 | 24 |
+------+------+------+------+------+------+------+
| 25 | 26 | 27 | 28 | 29 | 30 | 31 |
+------+------+------+------+------+------+------+
+------+------+------+------+------+------+------+
.
.
. February  -- 2009
.
+ SUNMONTUEWEDTHUFRISAT +
+------------------------------------------------+
|1 |2 |3 |4 |5 |6 |7 |
+------+------+------+------+------+------+------+
|8 |9 | 10 | 11 | 12 | 13 | 14 |
+------+------+------+------+------+------+------+
| 15 | 16 | 17 | 18 | 19 | 20 | 21 |
+------+------+------+------+------+------+------+
| 22 | 23 | 24 | 25 | 26 | 27 | 28 |
+------+------+------+------+------+------+------+
+------+------+------+------+------+------+------+
.
.
. March     -- 2009
.
+ SUNMONTUEWEDTHUFRISAT +
+------------------------------------------------+
|1 |2 |3 |4 |5 |6 |7 |
+------+------+------+------+------+------+------+
|8 |9 | 10 | 11 | 12 | 13 | 14 |
+------+------+------+------+------+------+------+
| 15 | 16 | 17 | 18 | 19 | 20 | 21 |
+------+------+------+------+------+------+------+
| 22 | 23 | 24 | 25 | 26 | 27 | 28 |
+------+------+------+------+------+------+------+
| 29 | 30 | 31
+------+------+------+------+------+------+------+
.
.
. April     -- 2009
.
+ SUNMONTUEWEDTHUFRISAT +
+------------------------------------------------+
|......|......|......|1 |2 |3 |4 |
+------+------+------+------+------+------+------+
|5 |6 |7 |8 |9 | 10 | 11 |
+------+------+------+------+------+------+------+
| 12 | 13 | 14 | 15 | 16 | 17 | 18 |
+------+------+------+------+------+------+------+
| 19 | 20 | 21 | 22 | 23 | 24 | 25 |
+------+------+------+------+------+------+------+
| 26 | 27 | 28 | 29 | 30
+------+------+------+------+------+------+------+
.
.
. May	    -- 2009
.
+ SUNMONTUEWEDTHUFRISAT +
+------------------------------------------------+
|......|......|......|......|......|1 |2 |
+------+------+------+------+------+------+------+
|3 |4 |5 |6 |7 |8 |9 |
+------+------+------+------+------+------+------+
| 10 | 11 | 12 | 13 | 14 | 15 | 16 |
+------+------+------+------+------+------+------+
| 17 | 18 | 19 | 20 | 21 | 22 | 23 |
+------+------+------+------+------+------+------+
| 24 | 25 | 26 | 27 | 28 | 29 | 30 |
+------+------+------+------+------+------+------+
| 31
+------+------+------+------+------+------+------+
.
.
. June	    -- 2009
.
+ SUNMONTUEWEDTHUFRISAT +
+------------------------------------------------+
|......|1 |2 |3 |4 |5 |6 |
+------+------+------+------+------+------+------+
|7 |8 |9 | 10 | 11 | 12 | 13 |
+------+------+------+------+------+------+------+
| 14 | 15 | 16 | 17 | 18 | 19 | 20 |
+------+------+------+------+------+------+------+
| 21 | 22 | 23 | 24 | 25 | 26 | 27 |
+------+------+------+------+------+------+------+
| 28 | 29 | 30
+------+------+------+------+------+------+------+
.
.
. July	    -- 2009
.
+ SUNMONTUEWEDTHUFRISAT +
+------------------------------------------------+
|......|......|......|1 |2 |3 |4 |
+------+------+------+------+------+------+------+
|5 |6 |7 |8 |9 | 10 | 11 |
+------+------+------+------+------+------+------+
| 12 | 13 | 14 | 15 | 16 | 17 | 18 |
+------+------+------+------+------+------+------+
| 19 | 20 | 21 | 22 | 23 | 24 | 25 |
+------+------+------+------+------+------+------+
| 26 | 27 | 28 | 29 | 30 | 31
+------+------+------+------+------+------+------+
.
.
. August    -- 2009
.
+ SUNMONTUEWEDTHUFRISAT +
+------------------------------------------------+
|......|......|......|......|......|......|1 |
+------+------+------+------+------+------+------+
|2 |3 |4 |5 |6 |7 |8 |
+------+------+------+------+------+------+------+
|9 | 10 | 11 | 12 | 13 | 14 | 15 |
+------+------+------+------+------+------+------+
| 16 | 17 | 18 | 19 | 20 | 21 | 22 |
+------+------+------+------+------+------+------+
| 23 | 24 | 25 | 26 | 27 | 28 | 29 |
+------+------+------+------+------+------+------+
| 30 | 31
+------+------+------+------+------+------+------+
.
.
. September -- 2009
.
+ SUNMONTUEWEDTHUFRISAT +
+------------------------------------------------+
|......|......|1 |2 |3 |4 |5 |
+------+------+------+------+------+------+------+
|6 |7 |8 |9 | 10 | 11 | 12 |
+------+------+------+------+------+------+------+
| 13 | 14 | 15 | 16 | 17 | 18 | 19 |
+------+------+------+------+------+------+------+
| 20 | 21 | 22 | 23 | 24 | 25 | 26 |
+------+------+------+------+------+------+------+
| 27 | 28 | 29 | 30
+------+------+------+------+------+------+------+
.
.
. October   -- 2009
.
+ SUNMONTUEWEDTHUFRISAT +
+------------------------------------------------+
|......|......|......|......|1 |2 |3 |
+------+------+------+------+------+------+------+
|4 |5 |6 |7 |8 |9 | 10 |
+------+------+------+------+------+------+------+
| 11 | 12 | 13 | 14 | 15 | 16 | 17 |
+------+------+------+------+------+------+------+
| 18 | 19 | 20 | 21 | 22 | 23 | 24 |
+------+------+------+------+------+------+------+
| 25 | 26 | 27 | 28 | 29 | 30 | 31 |
+------+------+------+------+------+------+------+
+------+------+------+------+------+------+------+
.
.
. November  -- 2009
.
+ SUNMONTUEWEDTHUFRISAT +
+------------------------------------------------+
|1 |2 |3 |4 |5 |6 |7 |
+------+------+------+------+------+------+------+
|8 |9 | 10 | 11 | 12 | 13 | 14 |
+------+------+------+------+------+------+------+
| 15 | 16 | 17 | 18 | 19 | 20 | 21 |
+------+------+------+------+------+------+------+
| 22 | 23 | 24 | 25 | 26 | 27 | 28 |
+------+------+------+------+------+------+------+
| 29 | 30
+------+------+------+------+------+------+------+
.
.
. December  -- 2009
.
+ SUNMONTUEWEDTHUFRISAT +
+------------------------------------------------+
|......|......|1 |2 |3 |4 |5 |
+------+------+------+------+------+------+------+
|6 |7 |8 |9 | 10 | 11 | 12 |
+------+------+------+------+------+------+------+
| 13 | 14 | 15 | 16 | 17 | 18 | 19 |
+------+------+------+------+------+------+------+
| 20 | 21 | 22 | 23 | 24 | 25 | 26 |
+------+------+------+------+------+------+------+
| 27 | 28 | 29 | 30 | 31
+------+------+------+------+------+------+------+

PL/SQL procedure successfully completed.




好玩!
lovejuan1314
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Oracle数据库中日期的操作、主键自增与分页查询
胡云峰的博客
05-08 414
Oracle数据库中日期的操作
oracle calendar,Date Picker Calendar For Oracle Forms 6i
weixin_32298151的博客
04-12 88
Giving date picker calendar option to user for date type fields in Oracle Forms. I am providing you the form (FoxCal.Fmx) and two libraries (General.plx and Calendar.pll). You can download these files...
Oracle Calendar
weixin_30817749的博客
07-17 266
声明: 本文为转载,如果有侵犯知识版本，请通知本人，本人将即刻停止侵权行为: 参考网址:http://code.geekinterview.com/oracle/oracle-calendar-sql.html 1 SELECT Initcap(TRIM(To_Char(Dat, 'month'))) || ', ' || 2 To_Char(SY...
Oracle 日历表详解（含节假日）
鱼丸丶粗面
01-07 5511
文章目录1 概述2 示例2.1 判断双休日2.2 判断节假日 1 概述 #mermaid-svg-RiNPbI034v08DBcc .label{font-family:'trebuchet ms', verdana, arial;font-family:var(--mermaid-font-family);fill:#333;color:#333}#mermaid-svg-RiNPbI034v08DBcc .label text{fill:#333}#mermaid-svg-RiNPbI034v08DBc
【Oracle】获取最近工作日及前N个工作日
Jared_Cui的博客
07-22 2404
SQL日期处理
oracle详解
03-25
·有一些对象，如物化视图，基于函数的索引等不能被传输 可以用以下的方法来检测一个表空间或一套表空间是否符合传输标准： exec sys.dbms_tts.transport_set_check(‘tablespace_name’,true); select * from sys....
PHP函数参考手册大全
11-26
Calendar 日历函数 X. CCVS API Functions [deprecated] XI. Character Type Functions XII. Classes/Objects 类／对象函数 XIII. Classkit Functions XIV. ClibPDF Functions XV. COM 和 .Net（Windows）函数 XVI....
Oracle9i的init.ora参数中文说明
11-07
说明: (如果值为 TRUE) 当在隐式转换中丢失数据时返回错误的参数。 值范围: FALSE | TRUE 默认值: TRUE nls_numeric_characters: 说明: 指定将用作组分隔符和小数位的字符。组分隔符就是用来分隔整数位组 (如千, ...
SQL计算timestamp的差值的方法
12-15
SQL计算timestamp的差值的方法 概述 有时候我们需要按照时间找出某些记录，比如说：算出离销售时间前1个小时的记录。 ... Calendar calendar = Calendar.getInstance(); calendar.setTime(new Dat
PHP5中文参考手册
11-30
Calendar — Calendar 日历函数 CCVS — CCVS API 函数（已废弃） Classes/Objects — Classes/Objects 类／对象函数 Classkit — Classkit 函数 ClibPDF — ClibPDF Functions [deprecated] COM — COM 和 .Net...
oracle 数据库中生成日历的方法
qq_39354340的博客
08-06 3800
直接上代码：    SELECT to_date('20180101','yyyymmdd') + level-1 as everyDay from dual  connect by level &lt;=  (last_day(to_date('20181201','yyyymmdd')) - to_date('20180101','yyyymmdd') +1);---这是日历   下面是...
Oracle 日历
xhfbx的博客
10-25 785
此方法只是用于Oracle数据库。 直接看例子吧： select ceil((cdate+(to_char(to_date('20100101','yyyymmdd'),'d'))-2)/7) as week, sum(decode(to_char(everyday,'d'),'2',to_char(everyday,'dd'))) as 星期一, sum(decode(to_char...
oracle修改日历,oracle 日历
weixin_32161925的博客
04-09 116
以下是从网上找到的相应的 oracle日历的sql并进行了改造可以生成任意一段时间内的日历。 create table calendar ( statis_month number(6), -- 统计月份 statis_week number(6), -- 周 sunday number(6), -- 星期日 monday number(6), -- 星期一 tuesday number(6), ...
oracle字符串连接和时间格式处理（附加Calendar简单使用）
caisenbinbeida2009的专栏
01-24 1582
1.和其他数据库系统类似，Oracle字符串连接使用“||”进行字符串拼接，其使用方式和MSSQLServer中的加号“+”一样。 2.oracle对对时间格式的数据存贮比较特殊，经常报ora-01861文字与格式字符串不匹配错误，只需在时间类型字段前加to_char进行转换： 3.Calendar在程序开发中使用还是很广泛的，下面只粘贴曾经使用过程序的Calendar应用： (
oracle写一本日历
俊BU见GAO堂MING镜
05-05 1022
根据oracle的select，做出一年的日历，代码如下：select 月份, 周次, decode(sum(nvl(星期日,0)),0,null,sum(nvl(星期日,0))) 星期日, decode(sum(nvl(星期一,0)),0,null,sum(nvl(星期一,0))) 星期一, decode(sum(nvl(星期二,0)),0,null,sum(nvl(星期二,0))) 星期二
Java: util.Date to sql.Date
lan_13217的专栏
05-15 233
http://snippets.dzone.com/posts/show/5662 [code="java"] /* This snippet shows how to convert a java util Date into a sql Date for use in databases */ java.util.Date utilDate = new java.util.Dat...
oracle 制作日历表,oracle 用sql生成日历的方法
weixin_33895274的博客
04-03 558
BI分析中，经常需要将事实表与时间维度表关联起来，按年/月/日来逐层展示，常用的做法是创建一张日历表，结构类似如下：createtableT_BAS_CALENDAR(d_yearNUMBER(4)notnull,d_monthNUMBER(2)notnull,d_dayNUMBER(2)notnull);commentontableT_BAS_CALENDAR...
【EBS FORM】FORM中的日历
wangcheng8699的专栏
08-21 1943
【EBS FORM】FORM中的日历 Calendar是Template提供给我们的standard object.可以使我们方便的为日期型字段提供日期的选择列表. 为项指定值列表 KEY-LISTVAL触发器 calendar.show; 相关函数 calendar.show(first_date datedefaultnull) calendar.setu...
oracle函数返回多个值
最新发布
05-18
在 Oracle 中，函数可以返回一个值，但是不能直接返回多个值。不过，可以通过以下两种方式来模拟返回多个值： 1. 使用 OUT 参数：定义一个包含多个 OUT 参数的函数，每个 OUT 参数都可以存储一个返回值。例如： ``...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值