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文件（已更新）