oracle：基本几个函数

已于 2024-02-06 13:44:26 修改
阅读量300 收藏 1
点赞数
分类专栏： Oracle # 数据库 文章标签： Oracle
于 2019-02-28 00:27:44 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/yubin1285570923/article/details/87999211
版权

oracle中 

--merge into

MERGE INTO 目标表 a
USING 源表 b
ON (a.字段1 = b.字段2 and a.字段n = b.字段n)
WHEN MATCHED THEN
    UPDATE SET a.新字段 = b.字段
WHERE 限制条件
WHEN NOT MATCHED THEN
         INSERT (a.字段名1，a.字段名n) VALUES(b.字段值1, b.字段值n)
WHERE 限制条件123456789

-- with workflog as (...),buyerlist as (... workflog p) select * from buyerlist;  制作临时表

with a as (select * from cafs_org c),b as (select * from a) select * from b;

**********************************************

1. //按照笔划排序 

   2. select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_STROKE_M'); 

   3. //按照部首排序 

   4. select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_RADICAL_M'); 

   5. //按照拼音排序，此为系统的默认排序方式 

   6. select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_PINYIN_M'); 

***************************************************************************************

1 nulls first / nulls last   排序如果遇到null就放在最前面或者最后面

2 new PageRequest(page-1,size,new Sort(Sort.Direction.DESC,"net_amount"));

************************************************************************************************

start with connect by prior 递归查询

这个子句主要是用于B树结构类型的数据递归查询，给出B树结构类型中的任意一个结点，遍历其最终父结点或者子结点。

先看原始数据

create table a_test
( parentid varchar2(10),
  subid    varchar2(10));

insert into a_test values ( '1', '2' );
insert into a_test values ( '1', '3' );
insert into a_test values ( '2', '4' );
insert into a_test values ( '2', '5' );
insert into a_test values ( '3', '6' );
insert into a_test values ( '3', '7' );
insert into a_test values ( '5', '8' );
insert into a_test values ( '5', '9' );
insert into a_test values ( '7', '10' );
insert into a_test values ( '7', '11' );
insert into a_test values ( '10', '12' );
insert into a_test values ( '10', '13' );

commit;

select * from a_test;

要求给出其中一个结点值，求其最终父结点。以7为例，看一下代码

start with 子句：遍历起始条件，有个小技巧，如果要查父结点，这里可以用子结点的列，反之亦然。

connect by 子句：连接条件。关键词prior，prior跟父节点列parentid放在一起，就是往父结点方向遍历；prior跟子结点列subid放在一起，则往叶子结点方向遍历，

                         parentid、subid两列谁放在“=”前都无所谓，关键是prior跟谁在一起。

order by 子句：排序，不用多说。

这里start with 子句用了parentid列，具体区别后面举例说明。

connect by 子句中，prior跟subid在同一边，就是往叶子结点方向遍历去了。因为7有两个子结点，所以第一级中有两个结果（10和11），10有两个子结点（12,13），11无，所以第二级也有两个结果（12，13）。即12,13就是叶子结点。

下面看下start with子句中选择不同的列的区别：

以查询叶子结点（往下遍历）为例

结果很明显，原意是要以7为父结点，遍历其子结点，左图取的是父结点列的值，结果符合原意；右图取的是子结点列的值，结果多余的显示了7 的父结点3.

---------------------------------------

关于where条件的语句，以后验证后再记录。先留个疑问

参考：https://www.cnblogs.com/benbenduo/p/4588612.html

***********************************************************************************************

listagg函数:列转行函数LISTAGG() WITHIN GROUP ()的使用方法

(select listagg(branch_code,',') within group (order by t.branch_code) branch_code from cafs_policy t where t.policy_no = c.policy_no )  as  branch_all

select c.rowid,c.* from cafs_dict c ;  锁行

listagg() within GROUP () over （） 显示多行

SELECT
	T .DEPTNO,
	listagg (T .ENAME, ',') WITHIN GROUP (ORDER BY T .ENAME)  over(PARTITION BY T .DEPTNO)
FROM
	SCOTT.EMP T
WHERE
	T .DEPTNO = '20'

mysql相同效果的实现  https://blog.csdn.net/sinat_36257389/article/details/95052001

PostgreSQL 相同效果的实现 https://blog.csdn.net/sinat_36257389/article/details/95611686
mysql 数据库的group_concat()使用方法

.使用  group_concat()  将多行合并成一行(比较常用)

语法：group_concat( [distinct] 要连接的字段 [order by 排序字段 asc/desc ] [separator '分隔符'] )

SELECT 
  T.DEPTNO,
	group_concat ( T.ENAME ORDER BY DEPTNO separator ',' ) 
FROM
	EMP T 
WHERE
	T.DEPTNO = '20' 
GROUP BY
	T.DEPTNO;

***********************************************************************************

over()函数写法over(partition by expr2 order by expr3),根据expr2进行分区，在各个分区内按照expr3排序。
不能单独使用，需要与row_number(),rank()和dense_rank()，lag()和lead(),sum() ，count()，wm_concat等配合使用

partition by对结果集按照指定字段进行排列，结果集不会收缩，会发现结果集中同一部们的人会连续排列。
group by 会将结果集按照指定字段进行聚合，结果集会收缩，

这里连续求和:此处的sum不是求整个分区的和，而是按照order by字段进行分层后，求出分区中小于等于自身所在分层等级的和
over()与sum()一起使用不能省略order by 

多个sum over连用使用，分区只按照顺序出现第一个orderby进行排序。每个sum计算的值按照自身
ordrby进行分区后计算，类似于使用子查询进行计算。

------------------------------------------------------------------------------------------
主要是用于树查询（层次查询）以及多列转行。
select   ... sys_connect_by_path(column_name,'connect_symbol') from table 
          start with   ...connect by prior 这是指递归条件...  
          
eg：

 select sys_connect_by_path(m.ann_name,'-')from (
        select rownum rn ,t.* from cafs_announcement t 
 ) m start with rn = 1 connect by  prior rn = rn-1 ;
 
 select sys_connect_by_path(c.org_name,'>') from cas_org c start with c.org_code ='3010100' connect by prior c.id =c.parent_id;

---------------------------------------------------------------------------------------------
 
 wm_concat(列名),可以吧列值以逗号‘ ，’分隔开并显示成一行，也可以把逗号换成其他的
 可以后面跟over()
 eg：
 select wm_concat(ann_type) ann_type from cafs_announcement;
 

1.wm_cancat函数行转列后，不会按照原有查询结果排序。listagg 函数行转列后，会按照原有查询结果顺序排列。
如果考虑到需要行转列，并且保持分组后顺序不变可以使用listagg来完成。
 --------------------------------------------------------------------------------------------------
 
 --lead   lag
分析函数可以在同一次查询中取出同一个字段的前n行数据（lag）和后n行数据（lead）作为独立得列。

这种操作可以代替表自连接，并且效率更高

函数语法如下：

lag(exp_str,offset,defval) over(partion by ..order by …)

lead(exp_str,offset,defval) over(partion by ..order by …)

语法
         lag(exp_str，offset,defval) over()  
         exp_str:要取出的列
         offset:取偏移后的第几行数据
         defval:，默认值
         
eg：
select lag(c.ann_name,1,'0') over(partition by c.ann_type order by c.delete_flag),c.* from cafs_announcement c;        
    

lead(列名,n,m):  当前记录后面第n行记录的<列名>的值，没有则默认值为m；如果不带参数n,m，则查找当前记录后面第一行的记录<列名>的值，没有则默认值为null。

lag(列名,n,m):  当前记录前面第n行记录的<列名>的值，没有则默认值为m；如果不带参数n,m，则查找当前记录前面第一行的记录<列名>的值，没有则默认值为null。

eg：

对SAL列：取上一个SAL列作为单独的列，若不指定默认值，则默认值为NULL

对SAL列：取上一个SAL列作为单独的列，指定默认值为0


-----------------------------

decode(job,
    'Manage',sal*1.2,
    'Analyst',sal*1.1
    ,'salesman',sal*1.05
    sal)默认，不提供则返回null

-------------------------
select ename,job ,sal
case job when 'Manager' then sal*1.2
    when 'Analyst' then sal*1.1
    when 'salesman' then sal*1.05
    else sal end
     **************************************************************************

select 语句：
     可以用*    某个列column，或者表达式
     别名：紧跟列，或者用as关键字，别名可以用双引号（可以用空格或者下划线之类的）
     distinct: 去除重复行
desc 表名：可以查看表结构
describe emplement 可以用desc emplement

DML：insert
    update
    delete
    select
    
DDL
    create
    alter
    drop
    create index
    drop index

    
DCL
    grant:授权
    revoke：撤销访问权限
    commit：提交
    rollabck：回滚
    savepoint：设置保存点
    lock：    对数据库中部分锁定
    
    
比较运算：赋值使用 := 符号
回避特殊字符：escape

select jot_id from jobs where job_id like'IT\_%' escape '\';

-------------------
创建表:oracle没有create or replace table
create table (column 数据类型（size）)

create table emp1 as select  * from employees;
创建空表
create table emp1 as select  * from employees where 1=2;

--alter table语句
alter table emp1 ADD(column datatype [,default expre] [column datatype]);
alter table emp1 MODIFY(column datatype[default expre] [,column datatype]);
alter table emp1 DROP COLUMN column_name;或者alter table table drop (column);
alter table table_name RENAME COLUMN old_column_name  TO new_column_name;

************************总结oracle和mysql区别***************************************************************************

谨记：

oracle：都是加括号并且没有column

mysql：都是不加扩招，可以有也可以没有column

--oracle语法：只需要记住都是加括号的可以用，没有关键字
alter table tb_employee add  d_num number(11);-- 不可以添加column
alter table tb_employee add(d_num number(11));-- 不可以添加column
alter table tb_employee modify d_num number(12);-- 不可以添加column
alter table tb_employee modify(d_num number(11));-- 不可以添加column
alter table tb_employee drop column d_num; --必须添加column
alter table tb_employee drop( d_num);-- 不可以添加column

--mysql语法：只需要记住是不加扩招的可以用，（可加可不加）
alter table tb_test add  d_num int(11); -- 可加可不加关键字column
alter table tb_test add( d_num int(11)); -- 不可添加关键字column
alter table tb_test modify  d_num int(11); -- 可加可不加
--alter table tb_test modify( d_num int(12)); -- 方法不可用
alter table tb_test drop  d_num;  -- 可加可不加
--alter table tb_test drop( d_num); -- 方法不可用

以后谨记：比如示例

oracle：

alter table tb_employee add(d_num number(11));

alter table tb_employee modify(d_num number(11));

alter table tb_employee drop( d_num);

mysql：

alter table tb_test add  d_num int(11);

alter table tb_test modify  d_num int(11);

alter table tb_test drop  d_num;

*************************************************************************************************


删除表结构
drop table dept80;


清空表：
truncate table detail_dept;不能回滚


改变对象的名称
RENAME  dept TO detail_dept;

修改对象名称
alter table employee rename to tb_employee;

alter table tableName rename column oldCName to newCName; -- 修改字段名
alter table tableName modify (cloumnName 数据类型); -- 修改数据类型

设某个列不可用
alter table emps set unuseed column name;

创建索引

CREATE INDEX index_nameON table_name (column_name)
create 【unique】index index_name on tablename(columnname(length));
alter mytable add [unique] index [indexname] on (columnname(length));
drop index [indexname] on mytable;
show  index from  table_name;

--单行函数：
    大小写控制
    lower: lower("ABD")
    upper:
    initcap:    initcap("abc ded")   返回字符串并将字符串的第一个字母变为大写;
    字符控制函数
    concat
    substr
    length
    instr：    instr("HelloWorld","W"):结果为6
    lpad
    rpad
    trim
    replace
    数字函数：
    round:四舍五入  round(45.926,2) 结果:45.93
    trunc:截断        trunc(45.926,2) 结果:45.92
    mod:求余        mod(1600,300) 结果:100
    
    
类型转换：隐式和显式
隐式转换：自动完成
varchar2 或者char -> number
varchar2 或者char -> date
 number -> varchar2
 date -> varchar2
 
 显式：to_char  ,to_date ,to_number
 通用函数：nvl（e1,e1）
            nvl2(e1,e2,e3)
            nullif(e1,e2):相等就返回null，不等就返回e1
            coalesce(e1,e2...en):如果第一个表达式为空，则返回第二个表达式，对其他的参数进行 coalesce
            
条件表达式：
    case when
    decode（）
    
嵌套函数：执行时从内到外


SELECT table1.column ,table2.column FROM table1
    [CROSS JOIN table2] | 
    [NATURAL JOIN table2] | 
    [JOIN table2 USING(column_name)] |
    [JOIN table2 ON (table1.column_name = table2.column_name)] |
    [LEFT|RIGHT|FULL OUTER JOIN table2 ON (table1.column_name=table2.column_name)]
            
    cross join会产生叉集，跟笛卡尔积一样
    natural join 自然连接以两个表中具有相同名字的列为条件创建等值连接，如果列名相同而数据类型不同会产生
    using 在natural join子句创建等值连接可以使用using子句指定连接需要的列，可以有多个满足条件的进行选择，不要给选中的列中加表名前缀或别名
    
    
    我们常用的是用on创建连接
    select employee_id,city,department_name from employees e
    join department d 
    on d.department_id = e.department_id
    join locationis l 
    on d.location_id = l.location_id;
    
    
    
--分组函数
where 用在分组函数前面用作过滤

select avg(sal),deptno,name from emp where id <5000 group by deptno ,name having avg(sal) > 2000;
在select列表中所有未包含在组函数中的列都应该包含在group by 子句中

--子查询语法
子查询（内查询）在主查询之前一次执行完成
子查询的结果被主查询（外查询）使用
单行操作符对应单行子查询，多行操作符对应多行子查询

-单行子查询
    只返回银行结果
    单行操作符：= , >=, > ,< ,<= ,<>
-多行子查询
    多行子查询操作符：in ，any，all
    
插入数据：
    insert into table [(column [,column])]values (value [,value]);
    字符和日期应该包含在单引号中
    
    从其他表中拷贝数据
    insert into emp2 select * from employees where department_id = 90;不用写values子句
    
更新数据
    update table set column = value [,column = value] [where condition];
    在update中使用子查询，使更新基于另一张表中的数据
    upate copy_emp set department_id = (select department_id from employees where employee_id = 90)
    where job_id =(select job_id from employees where employee_id =200)

删除数据
        delete from table [where condition]

控制事务：
    commit提交
    rollback:回滚
    savepoint A 
    rollback to savepoint A :回滚到保存点A
    
    eg：
    update...
    savepoint update_dome
    
    insert...
    rollback to savepoint update_dome;
    
    
表级约束和列级约束
列级约束必须跟在列定义后面，表级约束不跟列一起，而是单独定义。
非空（not null）约束只能定义在列上

定义约束：
    列级：column [constraint constraint_name] constraint_type,
    表级：column,...[constraint constraint_name] constraint_type (conlumn,...)，
    
    create table emp(
        empl_id number(6),
        last_name varchar2(20) not null,
        salary number(16,2),
        hire_date date
        constraint emp_hire_date_nn not null,
        ...
    )
    
    create table emp(
        empl_id number(6),
        last_name varchar2(20) unique,
        email varchar2(15),
        salary number(16,2),
        hire_date date,
        ...
        constraint emp_email_uk unique(email);
    )可以声明在email后面，也可以声明在末尾
    
    crete table departments(
        department_id number(4),
        department_name varchar2(30) constraint depart_name_nn not null,
        manager_id number(6),
        location_id number(4),
        constraint dept_id_pk primary key(department_id);
    )
    
    create table employee(
        employee_id number(6),
        last_name varchar2(25) not null,
        email varchar2(25),
        salary number(8,2),
        commission_pct number(2,2),
        hire_date date not null,
        ...
        department_id number(4),
        constraint emp_dept_fk foreign key (department_id) references departments(department_id),--在后面添加on..
        constraint emp_email_uk unique(email);
    ) 
    foreign key 指定字表中的列
    references：表示父表中的列
    on delete cascade：级联删除
    on delete set null:级联置空
    
    check约束:定义每一行都必须满足条件
    ...,salary number(2) constraint emp_salary_min check(salary>0),...
    
    
    -----添加约束:alter table
        添加和删除
        有效化和无效化约束
        添加not null 要用modify
        
        alter table emp add [CONSTRAINT constraint_name] type (column);
        
        alter table emp modify name varchar2(22) null/not null 添加或者删除非空约束
        alter table emp add constraint pk_emp primary key (id);
        alter table emp drop constraint pk_emp;
        
        alter table emp add constraint u_emp unique(name);
        alter table emp drop constraint u_emp;
        
        alter table employees add constraint emp_manager_fk foreign key (manager_id) references employees(employees_id);
        alter table employees drop constraint emp_manager_fk;


        --使某一个约束失效:此约束还存在于表中，只是不起作用
        alter table employee disabled constraint emp_emp_id_pk;

--使某一个约束激活：激活以后，此约束具有约束力
        alter table employee enable constraint emp_emp_id_pk;

木槿:
create table a as select  * from b ;

木槿:
insert into b select * from a;

木槿:
insert into b (acode,aname) select acode,aname from a

木槿:
创建表结构与数据 

木槿:
同字段仅数据

木槿:
不同字段仅数据
 

yubin1285570923
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 1
    评论
专栏目录
oracle最全函数大全
11-08
oracle最全函数大全(分析函数-聚合函数-转换函数-日期型函数-字符型函数-数值型函数-其他函数
Oracle -- 几个函数汇总
叶的博客
02-27 167
1.select into2.insert into select 来源：http://blog.jobbole.com/94913/
ORACLE的几个函数
lim90719的博客
03-24 247
开发过程遇到几个ORACLE的函数：搜索了下     1、round（）函数传入一个值，该数值是按照指定的小数位元数进行四舍五入运算的结果。     语法：ROUND(number, decimal_places)，number是传入的数据，decimal_places是精度，即保留几位。   2、sign()和decode()函数   sign(n)函数，根据数字n的符号来判断，大于0返
Oracle常用函数
weixin_45873444的博客
08-02 612
Oracle常用函数 官方文档 函数 说明 示例 ABS ABS(num) 返回绝对值 SELECT ABS(-12) FROM dual; – 12 MOD MOD(num1, num2) 返回余数 注意：返回值的正负仅由第一个参数决定 SELECT MOD(10, 3) FROM DUAL; – 1SELECT MOD(10, -3) FROM DUAL; – 1SELECT MOD(-10, 3) FROM DUAL; – -1SELECT MOD(-10, -3) F
ORACLE函数库大全
hanfox的专栏
08-18 432
函数是预定义的命令集，对一行或一组行进行操作。函数主要针对以下对象进行计算或处理： 列字面字符串常量一个值或一组值 当 SQL*Plus 命令集内需要表达式或条件时可以使用函数。 它们通常使用于 SELECT 、 WHERE 、 GROUP BY 和 ORDER BY 子句中。 点击下载>>
oracle 的几个分析函数
04-19
这篇内容我们将深入探讨Oracle的几个重要分析函数，以及它们在实际工作中的应用。 一、ROW_NUMBER() ROW_NUMBER()函数为每一行分配一个唯一的整数，通常用于排序后的结果集。这个数字从1开始递增，对于每个新的分组...
Oracle计算时间差常用函数
09-09
本文将详细介绍如何使用Oracle的内置函数来计算两个Date类型字段之间的差异，例如START_DATE和END_DATE。 首先，我们来看如何计算天数差。Oracle提供了简单的减法运算符 `-` 来计算两个日期之间的差值，这个差值是...
简单的几个oracle函数
12-20
根据提供的文件信息，我们可以归纳出以下几个Oracle数据库中的关键函数及其用法： ### 1. MONTHS_BETWEEN函数 **函数定义与用途：** `MONTHS_BETWEEN(date1, date2)` 函数用于计算两个日期之间的月份数。这个函数...
oracle常见函数整理
最新发布
04-25
5. 根据给出的星期几返回：NEXT_DAY()函数返回第一个日期后第一个星期几的日期，例如NEXT_DAY('01-SEP-05','FRIDAY')返回第一个日期后第一个星期五的日期。 6. 月份的最后一天：LAST_DAY()函数返回指定日期的月份的...
Oracle自定义脱敏函数的代码详解
09-09
函数内部定义了几个变量，如`V_STR_LENGTH`用于存储输入字符串的长度，`V_NAME`用于存储脱敏后的字符串，`V_N`用于计数，`V_HID`用于构建星号(*)脱敏的部分，`V_SQL`用于执行SQL查询，以及`V_NUM_FLAG`用于判断是否...
oracle sql like多个条件函数
HH猿的博客
07-25 1万+
select * from table where REGEXP_LIKE(字段名, '(匹配串1|匹配串2|...)') ;//全模糊匹配 select * from table where REGEXP_LIKE(字段名, '^(匹配串1|匹配串2|...)') ;//右模糊匹配 select * from table where REGEXP_LIKE(字段名, '(匹配串1|匹配...
oracle的column命令详解,Oracle的column命令详解
weixin_36216860的博客
04-11 2055
Oracle column命令可以对列的现实效果，包括对列标题和列数据，进行格式化，该命令的语法如下：COL[UMN] [{column | expr} [option ...] ]column指定列名options指定用于格式化列或别名的一个或者多个选项。其中的option选项如下：ALI[AS] alias 指定要格式化的列的别名CLE[AR] 清楚列的格式化ENTMA...
Oracle常用函数大全
长安诗社社长@-梁瀚文- https://github.com/HevnChin https://gitee.com/HevnChin
09-15 3978
SELECT TRANSLATE('2abc2234','01234abcde','99999XXXXX') tra from dual --结果：9XXX9999 --查找字符串',01234,2342,2,'中逗号出现次数 select length(translate(',01234,2342,2,', 'a0123456789', ' ')) from dual;
Oracle基本函数
08-06 1815
1、数值型常用函数　　函数　　返回值　　　　　　　　　　　　样例　　　　　　　　　　　显示ceil(n) 大于或等于数值n的最小整数　　select ceil(10.6) from dual; 11floor(n) 小于等于数值n的最大整数　 select ceil(10.6) from dual; 10mod(m,n) m除以n的余数,若n=0,则返回m select mod
Oracle函数
热门推荐
小工匠
05-16 3万+
概述ORACLE函数系列：Oracle常见函数大全Oracle-分析函数之连续求和sum(…) over(…)Oracle-分析函数之排序值rank()和dense_rank()Oracle-分析函数之排序后顺序号row_number()Oracle-分析函数之取上下行数据lag()和lead()数值型函数返回绝对值 abs(x)ABS(X) 【功能】返回x的绝对值 【参数】x，数字型表达式 【返回
Oracle分析函数汇总（超全）
Hjchidaozhe的博客
09-13 1万+
前半部分数据源为Oracle自带HR用户下的员工信息表：EMPLOYEES，若创建库的时候未勾选创建示例，可以在这下载sql文件，创建相关表及数据源。（百度网盘链接：https://pan.baidu.com/s/1axlyRjfEGi0pOi8xmRewrA 密码：t3yy） 中间部分的数据源为Oracle自带SCOTT用户下的员工表&部门表：EMP、DEPT 后半部分属于转载，...
Oracle表空间数据文件个数和块数量限制
喝醉酒的小白
02-20 3065
Oracle表空间数据文件
Oracle函数详解：SQL单记录函数指南
本篇将详细介绍几个基础且常用的Oracle函数。 1. ASCII 函数 ASCII函数返回一个字符的ASCII码，即其对应的十进制数字。例如，ASCII('A')返回65，表示字符'A'在ASCII表中的位置。同样，ASCII('a')、ASCII('0')和...
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值