1.熟悉、梳理、总结下oracle相关知识体系。
2.日常研发过程中使用较少，随着时间的推移，很快就忘得一干二净，所以梳理总结下，以备日常使用参考
3.欢迎批评指正，跪谢一键三连！

• 总结源文件资源下载地址： Oracle常用函数实战总结.xlsx、Oracle 11g数据库管理员指南.pdf、Oracle开发实战经典.pdf
• 总结源文件资源下载地址： Oracle常用函数实战总结.xlsx、Oracle 11g数据库管理员指南.pdf、Oracle开发实战经典.pdf

1.快速搭建SQL Server环境实操

• 【kettle005】kettle访问Oracle数据库并处理数据至execl文件（已更新）
  
• 【kettle005】kettle访问Oracle数据库并处理数据至execl文件（已更新）
  

2.基础函数及使用总结

2.1 Oracle函数分类

• 

2.2 Oracle常用数据类型

• 

2.3 Oracle常用函数清单

• 

2.4 日期格式化标记清单

• 

2.5 格式化数字标记清单

• 序号	转换格式	描述
  1	9	表示一位数字
  2	0	显示前导0
  3	$	将货币的符号显示为美元符号
  4	L	根据语言环境不同，自动选择货币符号
  5	.	显示小数位
  6	,	显示千位符

3.oracle部分函数测试样例（SQL）

• select sysdate from dual;
  
  select sysdate 今天, sysdate+3 三天之后的日期,sysdate-3 三天之前的日期 from dual;
  
  select systimestamp from dual;
  
  alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'
  select sysdate from dual;
  
  select 
  	sysdate,add_months(sysdate,3) 三个月之后的日期,
  	add_months(sysdate,-3) 三个月之前的日期,add_months(sysdate,60) 六十个月之后的日期
  from dual ;
  
  select "id", "name", "email", "age", "sex", "id_card", "mobile_phone", "visa_card", "officer_card", "address", "hiredate",
  	trunc(months_between(sysdate, "hiredate")) 雇佣总月数,
  	trunc(months_between(sysdate, "hiredate")/12) 雇佣总年份
  from YOULI."youli_testtable";
  
  select
  	sysdate, next_day(sysdate,'星期日') 下一个星期日, next_day(sysdate,'星期一') 下一个星期一
  from dual;
  
  select sysdate, last_day(sysdate) from dual ;
  
  select 
  	extract(year from date '2024-05-19') years,
  	extract(month from date '2024-05-19') months,
  	extract(day from date '2024-05-19') days
  from dual ;
  
  select 
  	extract(year from systimestamp) years, extract(month from systimestamp) months,
  	extract(day from systimestamp) days, extract(hour from systimestamp) hours,
  	extract(minute from systimestamp) minutes, extract(second from systimestamp) seconds
  from dual ;
  
  select
  	sysdate 当前系统时间,
  	to_char(sysdate, 'YYYY-MM-DD') 格式化日期,
  	to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') 格式化日期时间,
  	to_char(sysdate, 'FMYYYY-MM-DD HH24:MI:SS') 去掉前导0的日期时间
  from dual ;
  
  select
  	to_char(987654321.789, '999,999,999,999.99999') 格式化数字,
  	to_char(987654321.789, '000,000,000,000.00000') 格式化数字
  from dual ;
  
  select to_date('2023-09-19','YYYY-MM-DD') from dual
  
  select to_timestamp('2023-09-27 18:07:10', 'YYYY-MM-DD HH24:MI:SS') datetime from dual ;
  
  select 
  	to_number('09') + to_number('019') 加法计算,
  	to_number('39') * to_number('29') 乘法计算
  from dual ;
  
  select
  	'09'+'19' 加法计算, '09'*'19' 乘法计算
  from dual;
  
  select "id", "name", "email", "age", "sex", "id_card", "mobile_phone", "visa_card", "officer_card", "address", "hiredate"
      ,"score", sum( "score" ) over (partition by "name") sum
  from YOULI."youli_testtable";
  
  select "id", "name", "email", "age", "sex", "id_card", "mobile_phone", "visa_card", "officer_card", "address", "hiredate"
      ,"score", RANK() over (partition by "name" ORDER BY "score" desc) rk
  from YOULI."youli_testtable";
  
  CREATE TABLE YOULI."youli_testtable" (
  	ID NUMBER(38,0),
  	"name" VARCHAR2(100),
  	"email" VARCHAR2(100),
  	"age" NUMBER(38,0),
  	"sex" VARCHAR2(100),
  	"id_card" VARCHAR2(100),
  	"mobile_phone" VARCHAR2(100),
  	"visa_card" VARCHAR2(100),
  	"officer_card" VARCHAR2(255),
  	"address" VARCHAR2(255),
  	"hiredate" VARCHAR2(100),
  	"score" FLOAT,
  	CONSTRAINT SYS_C006999 CHECK ("ID" IS NOT NULL),
  	CONSTRAINT SYS_C007000 CHECK ("name" IS NOT NULL),
  	CONSTRAINT SYS_C007001 CHECK ("email" IS NOT NULL),
  	CONSTRAINT SYS_C007002 CHECK ("age" IS NOT NULL),
  	CONSTRAINT SYS_C007003 CHECK ("sex" IS NOT NULL),
  	CONSTRAINT SYS_C007004 CHECK ("id_card" IS NOT NULL),
  	CONSTRAINT SYS_C007005 CHECK ("mobile_phone" IS NOT NULL),
  	CONSTRAINT SYS_C007006 CHECK ("visa_card" IS NOT NULL),
  	CONSTRAINT SYS_C007007 PRIMARY KEY (ID)
  );
  
  create sequence YOULI_TESTTABLE_AINC 
  minvalue 1  
  maxvalue 999999999999999999999999999 
  start with 8
  increment by 1
  nocycle
  nocache;
  
  create or replace trigger insert_for_autoinc   
  	before insert on YOULI."youli_testtable"   
  	for each row
  declare
  begin    
  	select YOULI_TESTTABLE_AINC.nextval into :new.ID from dual; 
  end insert_for_autoinc;
  
  INSERT INTO YOULI."youli_testtable"
  ("name", "email", "age", "sex", "id_card", "mobile_phone", "visa_card", "officer_card", "address", "hiredate", "score")
  VALUES('youli', 'youli', 0, 'youli', 'youli', 'youli', 'youli', 'youli', 'youli', 'youli', 99);
  

4.参考文章

• 【kettle005】kettle访问Oracle数据库并处理数据至execl文件（已更新）
  
• 【kettle005】kettle访问Oracle数据库并处理数据至execl文件（已更新）