Oracle常用函数及视图

字符操作函数

--1.向左补齐
select name,lpad(name,4,'0')from kgc;
--向右补齐
select name,rpad(name,4,'0')from kgc;
--大小写转换
select name,lower(name) from kgc;
select name,upper(name) from kgc;
--首字母大写
select name,initcap(name) from kgc;

--求长度
select name,length(name) from kgc;
--截取
select name,substr(name,0,2) from kgc;
--第几个位置包含
select name,instr(name,'B') from kgc;
select name,instr(name,'A') from kgc;
--去空格
select trim(' AAA ') from dual;
select ltrim(' aaa ') from dual;
select rtrim('aaa ') from dual;
--字符串拼接
select CONCAT('hello', name) from kgc;
--反转
select reverse(name) from kgc;

数值函数

--小数点取值,取几位
select round(123.456,2) from dual;
select round(123.456,1) from dual;
--向上/下取整
select ceil(123.456) from dual;
select floor(123.456) from dual;
--取模
select mod(32,7) from dual;
--判断正负
select sign(-12) from dual;
select sign(12) from dual;
sleect sign(0) from dual;
select sign(1900-12843) from dual;
--数学函数次方
select power(3,3) from dual;
select power(2,-3) from dual;
--开方
select sqrt(9) from dual;
--保留位数负的为小数点左边几位用0填充
select trunc(1234.6789,3) from daul;
select trunc(1234.6789,0) from dual;
select trunc(1234.6789,-2) from dual;
--转为字符串
select to_char(123.45) from dual;
select to_char(123.45,'0000.000') from dual;
select to_char(12345.45012,,'000000.00000') from dual;
select to_char(123.45,,'9999.99') from dual;
select to_char(123456789,'999,999,999,99') from dual;

日期

准备表

创建表

create table tb_num(
id number,
n1 number(6,3),
n2 number(6,-3)
)

插入数据

insert into tb_num values(1, 123.345, 123456)
insert into tb_num values(1, 123.3453434, 123456)
insert into tb_num values(1, 123.3453434, 123456)
select * from tb_num;

日期

select SYSDATE from DUAL;
SELECT SYSTIMESTAMP FROM DUAL;

CREATE TABLE T_DATE(
ID NUMBER,
D1 DATE,
D2 TIMESTAMP
)

INSERT INTO T_DATE VALUES(1, SYSDATE, SYSTIMESTAMP);

oracle内置函数大全点击此：https://www.cnblogs.com/min-yu/p/11137902.html

视图

视图创建调用

create or replace view v_tbook_log 
as select * from t_book_oplog where actionname='update' 
WITH read ONLY; --只读
select * from v_tbook_log;