【oracle004】oracle内置函数手册总结（已更新）

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";selectsysdate, 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 ;selectsysdate 当前系统时间,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 ;selectto_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 dualselect 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文件（已更新）