oracle使用总结（汇总）

有些特殊情况，需要手动修改数据库中的数据，在此总结！
日期格式转换：

select * from DACU.TB_SPSQD_BACKUP where to_char(to_date(create_date,'yyyy-mm-dd'),'yyyymmdd')='20201120' and '743'||w_no ='74308170'

Oracle中新建表：

create table TM_SERIES_TEST
(
  series_id         NUMBER(10) not null,
  brand_id          NUMBER(10) not null,
  model_year_id     NUMBER(10) not null,
  body_style_id     NUMBER(10) not null,
  series_code       CHAR(3) not null,
  identity_code     CHAR(4) not null,
  series_name_zh    VARCHAR2(100) not null,
  series_name_en    VARCHAR2(100) not null,
  special_flag      INTEGER default 1 not null,
  create_by         NUMBER(10),
  create_date       DATE,
  update_by         NUMBER(10),
  update_date       DATE,
  mark_for_delete   CHAR(1) default '0'
);

插入日期类型数据：

insert into dacu.tm_series_test (SERIES_ID, BRAND_ID, MODEL_YEAR_ID, BODY_STYLE_ID, SERIES_CODE, IDENTITY_CODE, SERIES_NAME_ZH, SERIES_NAME_EN, SPECIAL_FLAG, CREATE_BY, CREATE_DATE, UPDATE_BY, UPDATE_DATE, MARK_FOR_DELETE)
values (363, 200, 291, 323, 'NG2', 'NG22', '2017款', 'Rapid', 1, 7777, to_char('2013-03-14 15:32:51','yyyy-mm-dd hh24:mi:ss'), 7777, to_date('2020-03-30 16:50:35','yyyy-mm-dd hh24:mi:ss') );

查询前10行数据&手动更新

select * from sbpopt.tt_repair_part WHERE ROWNUM <= 10 for update;
commit;

截取某个字段前两位（所有行）

select * from tt_presale_sales_repair
update tt_presale_sales_repair set series_code=substr(series_code,1,2)
commit;

根据日期字段查询：最大值、最小值

select max(to_char(retail_time,'yyyy-mm-dd hh24:mi:ss')),min(to_char(retail_time,'yyyy-mm-dd hh24:mi:ss')) from tt_test where brand_id='200'

Oracle存储过程

create or replace procedure p1--无参数列表时，不需要写()
as
begin
  dbms_output.put_line('hello world');
end;

--执行存储过程方式1
set serveroutput on;
begin
  p1();
end;
--执行存储过程方式2
set serveroutput on;
execute p1();

示例：

CREATE OR REPLACE PACKAGE DACU.ASMP_REPORT AS
       procedure DELETE_DATA;
END ASMP_REPORT;
/

CREATE OR REPLACE PACKAGE BODY DACU.ASMP_REPORT
AS
       procedure DELETE_DATA
       IS
       BEGIN
	delete from DACU.TT_PRESALE_SALES_VOLUME where create_date >=  ADD_MONTHS(TRUNC(SYSDATE - 1, 'MM'), -1);
    delete from DACU.TT_PRESALE_SALES_REPAIR where type='1' and create_date >= ADD_MONTHS(TRUNC(SYSDATE - 1, 'MM'), -1);
    delete from DACU.TT_PRESALE_SALES_REPAIR where type='2' and create_date >= TRUNC(SYSDATE - 1, 'YYYY');
    
    delete from DACU.TT_PRESALE_SALES_VOLUME where DATE_YM >= to_char(ADD_MONTHS(TRUNC(SYSDATE - 1, 'MM'), -1),'yyyymm');
    delete from DACU.TT_PRESALE_SALES_REPAIR where type='2' and DATE_YM >= to_char(ADD_MONTHS(TRUNC(SYSDATE - 1, 'MM'), -1),'yyyymm');
    delete from DACU.TT_PRESALE_SALES_REPAIR where type='1' and DATE_YM >= to_char(TRUNC(SYSDATE - 1, 'YYYY'),'yyyy');
       COMMIT;
       END DELETE_DATA;

END ASMP_REPORT;
/  

declare
  job number;
begin
SYS.DBMS_IJOB.SUBMIT(102,'DACU','DACU','DACU',
to_date('2020-07-08 02:00:00','YYYY-MM-DD HH24:MI:SS'),'TRUNC(SYSDATE+1))+1/12',false,'DACU.ASMP_REPORT.DELETE_DATA;',
'NLS_LANGUAGE="SIMPLIFIED CHINESE" NLS_TERRITORY="CHINA" NLS_CURRENCY="￥" NLS_ISO_CURRENCY="CHINA" NLS_NUMERIC_CHARACTERS=".," NLS_DATE_FORMAT="yyyy-mm-dd hh24:mi:ss" NLS_DATE_LANGUAGE="SIMPLIFIED CHINESE" NLS_SORT="BINARY"',
'0102000200000000');
commit;
end;
/

Oracle函数

create or replace function f1 return varchar--必须有返回值
as
  msg varchar(50);
begin
   msg := 'hello world';
   return msg;
end;

--执行函数方式1
select f1() from dual;
--执行函数方式2
set serveroutput on;
begin 
  dbms_output.put_line(f1());
end;

备注：存储过程和函数区别：
存储过程不能使用return返回值
函数一般倾向于逻辑判断，存储过程倾向于事务处理

oracle的pivot函数和unpivot函数

select nation,city,ranking from temp 
pivot (max(city) for ranking in ('第一' as 第一,'第二' AS 第二,'第三' AS 第三));

select nation,name,title from temp 
unpivot (name for title in (第一,第二,第三,第四))t

查看定时任务 DBMS_Jobs