o2o网站做推广公司,9158个人空间登录,google chrome浏览器,唐山公司网站建设1.熟悉、梳理、总结下oracle相关知识体系。 2.日常研发过程中使用较少#xff0c;随着时间的推移#xff0c;很快就忘得一干二净#xff0c;所以梳理总结下#xff0c;以备日常使用参考 3.欢迎批评指正#xff0c;跪谢一键三连#xff01; 总结源文件资源下载地址#x… 1.熟悉、梳理、总结下oracle相关知识体系。 2.日常研发过程中使用较少随着时间的推移很快就忘得一干二净所以梳理总结下以备日常使用参考 3.欢迎批评指正跪谢一键三连 总结源文件资源下载地址 Oracle常用函数实战总结.xlsx、Oracle 11g数据库管理员指南.pdf、Oracle开发实战经典.pdf 总结源文件资源下载地址 Oracle常用函数实战总结.xlsx、Oracle 11g数据库管理员指南.pdf、Oracle开发实战经典.pdf 文章目录 1.快速搭建SQL Server环境实操2.基础函数及使用总结2.1 Oracle函数分类2.2 Oracle常用数据类型2.3 Oracle常用函数清单2.4 日期格式化标记清单2.5 格式化数字标记清单 3.oracle部分函数测试样例SQL4.参考文章 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 格式化数字标记清单 序号转换格式描述19表示一位数字20显示前导03$将货币的符号显示为美元符号4L根据语言环境不同自动选择货币符号5.显示小数位6,显示千位符
3.oracle部分函数测试样例SQL select sysdate from dual;select sysdate 今天, sysdate3 三天之后的日期,sysdate-3 三天之前的日期 from dual;select systimestamp from dual;alter session set nls_date_formatyyyy-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 ;select0919 加法计算, 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文件已更新
