Skip to content

Latest commit

 

History

History

OrcalBO

Folders and files

NameName
Last commit message
Last commit date

parent directory

..
picture
picture
 
 
README.md
README.md
 
 

README.md

Orcal 常用函数

目录

测试表 

select t.name as 姓名,
       t.study_date as 入学日期,
       to_char(t.study_date, 'yyyy-MM-dd') as 入学年月,
       to_char(t.study_date, 'HH24:MI:SS') as 入学时间
  from ORCAL_TEST t;

执行结果

toChar

select t.name as 姓名,
       t.birth_date as 出生日期,
       to_date(t.birth_date, 'yyyy-MM-dd') as 出生年月
  from ORCAL_TEST t;

执行结果

toDate

-- 参数：1，列名，2.匹配模式，3.替代成什么，
--       4.开始位置，5.第几个匹配
select regexp_replace(t.name, '.', '*', 2, 1) as 姓名,
       t.birth_date,
       t.study_date
  from ORCAL_TEST t;

执行结果

regexpReplace

select sysdate as 当前时间 from grades_test;

执行结果

getCurrentTime

-- floor()函数，取整数
-- months_between()函数，获取两个日期之间的月份数

SELECT t.name as 姓名,
       floor(months_between(sysdate, to_date(t.birth_date, 'yyyy-mm-dd')) / 12) as 年龄
  from orcal_test t

执行结果

floorAndMonths_between

-- rank() over函数，显示并列排名
-- rownum 用于获取前n行数据

select *
  from (SELECT t.name as 姓名,
               t.birth_date as 出生日期,
               rank() over(order by t.birth_date desc) as 排名
          from orcal_test t)
 where rownum < 4 -- 获取前三行数据

执行结果

rankOver

-- UNION 会去重

SELECT t.name 字段联合
  FROM orcal_test t
UNION 
  (SELECT t.birth_date FROM orcal_test t);

执行结果

union

-- UNION ALL 不去重

SELECT t.name 字段联合
  FROM orcal_test t
UNION ALL
  (SELECT t.birth_date FROM orcal_test t);

执行结果

unionAll

-- Create table
create table ORCAL_TEST
(
  name       VARCHAR2(10) not null,
  birth_date VARCHAR2(30) not null,
  study_date DATE
)
tablespace DATASERVER
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Add comments to the table 
comment on table ORCAL_TEST
  is '测试用表';
-- Add comments to the columns 
comment on column ORCAL_TEST.name
  is '姓名';
comment on column ORCAL_TEST.birth_date
  is '出生日期';
comment on column ORCAL_TEST.study_date
  is '入学日期';

 -- insert data
insert into ORCAL_TEST (name, birth_date, study_date)
values ('小绿','1999-08-09',to_date('2017-09-01 11:11:03','yyyy-mm-dd HH24:MI:SS'));
insert into ORCAL_TEST (name, birth_date, study_date)
values ('小强','1998-04-19',to_date('2017-09-02 10:31:11','yyyy-mm-dd HH24:MI:SS'));
insert into ORCAL_TEST (name, birth_date, study_date)
values ('小刀','1999-11-23',to_date('2017-09-05 13:45:34','yyyy-mm-dd HH24:MI:SS'));
insert into ORCAL_TEST (name, birth_date, study_date)
values ('小红','2000-01-01',to_date('2017-09-03 12:01:06','yyyy-mm-dd HH24:MI:SS'));

完成后显示结果

测试表1