精华内容
下载资源
问答
  • oracle 高级函数

    千次阅读 多人点赞 2017-08-17 16:44:19
    工作两年了,一直使用的oracle,最近经常使用不常用的sql语句,索性就自己整理一下,发出来 供大家参考,后续会不断添加、整理,本文章不做详细的功能解释,主要集合描述目前接触过的一些函数 复制表结构和数据 ...

    ORACLE常用SQL

    工作两年了,一直使用的oracle,最近经常使用不常用的sql语句,索性就自己整理一下,发出来 供大家参考,后续会不断添加、整理,本文章不做详细的功能解释,主要集合描述目前接触过的一些函数

    复制表结构和数据

    CREATE TABLE table_name AS SELECT * FROM old_table_name;

    只复制表结构

    CREATE TABLE table_name AS SELECT * FROM old_table_name WHERE 1=2

    只复制表数据

    两个表结构一毛一样:

    INSERT INTO  table_name select * from old_table_name;

    结构不完全一致:

    Insert into table_name(column1,column2...) select column1,column2 from old_table_name;

    递归查询

    从根节点开始查询:

    Select t.*,level  from table_name t  start with t.id=0 connect by prior t.id=t.pid order by level

    从子节点开始查询:

    把上面的开始节点换成开始的子节点,并且prior 放在子节点前面

    Select t.*,level  from table_name t  start with t.id=10 connect by t.id= prior t.pid order by level

     

    合并字符串(简单的行专列)

    Select to_char(wm_concat(column1 || ‘-’ || column2))  from table group by column1

    拆分字符串

    select REGEXP_SUBSTR('01#02#03#04', '[^#]+', 1, rownum) as newport from dual connect by rownum <= REGEXP_COUNT('01#02#03#04', '[^#]+');

    With as 临时表

    With t as (select 1 id,2 pid from dual),

    t2 as (select 1 id,22 pid from dual)

    Select t2.* from t,t2 where t.id=t2.id

    行专列查询

    with t as (

    Select ‘桃子’ name, 300 nums ,1 jidu from dual

    Union

    Select ‘苹果’ name, 200 nums ,1 jidu from dual

    Union

    Select ‘西瓜’ name, 100 nums ,1 jidu from dual

    Union

    Select ‘桃子’ name, 111 nums ,2 jidu from dual

    Union

    Select ‘西瓜’ name, 222 nums ,2 jidu from dual

    Union

    Select ‘苹果’ name, 333 nums ,2 jidu from dual

    )

    Select * from t pivot(sum(nums)  for name in (桃子,西瓜,苹果)) order by jidu

    列转行查询

    With t as (

    Select 1 id,’桃子’ name,100 q1,200 q2,300 q3,400 q4 from dual

    Union

    Select 2 id,’苹果’ name,111 q1,222 q2,333 q3 444 q4 from dual

    Union

    Select 3 id,’西瓜’ name,123 q1,234 q2,345 q3,456 q4 from dual

    )

    Select * from t unpivot(nums for jidu in (q1,q2,q3,q4)) order by id

    排序函数

    这里复制的别人的

    原文链接:http://www.cnblogs.com/wuyisky/archive/2010/02/24/oracle_rank.html

    1     select region_id, customer_id, sum(customer_sales) total,
      2         rank() over(order by sum(customer_sales) desc) rank,
      3         dense_rank() over(order by sum(customer_sales) desc) dense_rank,
      4         row_number() over(order by sum(customer_sales) desc) row_number
      5    from user_order
      6   group by region_id, customer_id;

     REGION_ID CUSTOMER_ID      TOTAL       RANK DENSE_RANK ROW_NUMBER
    ---------- ----------- ---------- ---------- ---------- ----------
                
             8          18                1253840         11         11         11
             5           2                 1224992         12         12         12
             9          23                1224992         12         12         13
             9          24                1224992         12         12         14
            10          30               1216858         15           13            15

    请注意上面的绿色高亮部分,这里生动的演示了3种不同的排名策略:

    ①对于第一条相同的记录,3种函数的排名都是一样的:12

    ②当出现第二条相同的记录时,RankDense_rank依然给出同样的排名12;而row_number则顺延递增为13,依次类推至第三条相同的记录

    ③当排名进行到下一条不同的记录时,可以看到Rank函数在1215之间空出了13,14的排名,因为这2个排名实际上已经被第二、三条相同的记录占了。而Dense_rank则顺序递增。row_number函数也是顺序递增

     

    使用分析函数为记录进行分组排名

    上面的排名是按订单总额来进行排列的,现在跟进一步:假如是为各个地区的订单总额进行排名呢?这意味着又多了一次分组操作:对记录按地区分组然后进行排名。幸亏Oracle也提供了这样的支持,我们所要做的仅仅是在over函数中order by的前面增加一个分组子句:partition by region_id

    select region_id, customer_id, sum(customer_sales) total,
     rank() over(partition by region_id
        order by sum(customer_sales) desc) rank,
     dense_rank() over(partition by region_id
        order by sum(customer_sales) desc) dense_rank,
     row_number() over(partition by region_id
       order by sum(customer_sales) desc) row_number

    from user_order
     group by region_id, customer_id;

    REGION_ID CUSTOMER_ID      TOTAL       RANK DENSE_RANK ROW_NUMBER
    ---------- ----------- ---------- ---------- ---------- ----------
             5           4                1878275          1          1          1
             5           2                1224992          2          2          2
             5           5                1169926          3          3          3
             6           6                1788836          1          1          1
             6           9                1208959          2          2          2
             6          10               1196748          3          3          3       

    现在我们看到的排名将是基于各个地区的,而非所有区域的了!Partition by 子句在排列函数中的作用是将一个结果集划分成几个部分,这样排列函数就能够应用于这各个子集。

     

    存在则修改,不存在则新增

    判断表里面是否有满足条件的数据,存在则修改,不存在则新增

    MERGE INTO T T1

    USING (SELECT '1001' AS a,2 AS b FROM dual) T2ON ( T1.a=T2.a)

    WHEN MATCHED THEN

    UPDATE SET T1.b = T2.b

    WHEN NOT MATCHED THEN

        INSERT (a,b) VALUES(T2.a,T2.b);

    全连接

     

    select * from dept full join employee on dept.deptid=employee.deptid

     

    Nulls last/first

    空值排最前面或者最后面

    Select * from table_name order by id nulls first/last

    top N条数据

    SELECT NO FROM (

        SELECT ROW_NUMBER() OVER (ORDER BY NO) RNO, NO FROM ROWNUM_TEST

    )WHERE RNO <= 5  ORDER BY NO ;

    分组函数(rollup,cube,grouping sets

    1 rollup

    假设有一个表test,有ABCDE5列。

    如果使用group by rollup(A,B,C),首先会对(ABC)进行GROUP BY,然后对(AB)进行GROUP BY,然后是(A)进行GROUP BY,最后对全表进行GROUP BY操作。roll up的意思是“卷起”,这也可以帮助我们理解group by rollup就是对选择的列从右到左以一次少一列的方式进行grouping直到所有列都去掉后的grouping(也就是全表grouping),对于n个参数的rollup,有n+1次的grouping。以下2sql的结果集是一样的:

    Select A,B,C,sum(E) from test group by rollup(A,B,C)

    Select A,B,C,sum(E) from test group by A,B,C

    union all

    Select A,B,null,sum(E) from test group by A,B

    union all

    Select A,null,null,sum(E) from test group by A

    union all

    Select null,null,null,sum(E) from test

    2 cube

    cube的意思是立方,对cube的每个参数,都可以理解为取值为参与grouping和不参与grouping两个值的一个维度,然后所有维度取值组合的集合就是grouping的集合,对于n个参数的cube,有2^n次的grouping。如果使用group by cube(A,B,C),,则首先会对(ABC)进行GROUP BY,然后依次是(AB)(AC)(A)(BC)(B)(C),最后对全表进行GROUP BY操作,一共是2^3=8grouping。同rollup一样,也可以用基本的group by加上结果集的union all写出一个与group by cube结果集相同的sql

    Select A,B,C,sum(E) from test group by cube(A,B,C);

    Select A,B,C,sum(E) from test group by A,B,C

    union all

    Select A,B,null,sum(E) from test group by A,B

    union all

    Select A,null,C,sum(E) from test group by A,C

    union all

    Select A,null,null,sum(E) from test group by A

    union all

    Select null,B,C,sum(E) from test group by B,C

    union all

    Select null,B,null,sum(E) from test group by B

    union all

    Select null,null,C,sum(E) from test group by C

    union all

    Select null,null,null,sum(E) from test;

    3 grouping sets

    grouping sets就是对参数中的每个参数做grouping,也就是有几个参数做几次grouping,例如使用group by grouping sets(A,B,C),则对(A),(B),(C)进行group by,如果使用group by grouping sets((A,B),C),则对(A,B),(C)进行group by。甚至grouping by grouping set(A,A)都是语法允许的,也就是对(A)进行2group by,grouping sets的参数允许重复

     

    Keep

    ID MC SL
    -------------------- -------------------- -------------------
    1 111 1
    1 222 1
    1 333 2
    1 555 3
    1 666 3
    2 111 1
    2 222 1
    2 333 2
    2 555 2

    9
     rows selected

    SQL> 
    SQL> select id,mc,sl,
    2 min(mc) keep (DENSE_RANK first ORDER BY sl) over(partition by id),
    3 max(mc) keep (DENSE_RANK last ORDER BY sl) over(partition by id)
    4 from test
    5 ;

    ID MC SL MIN(MC)KEEP(DENSE_RANKFIRSTORD MAX(MC)KEEP(DENSE_RANKLASTORDE
    -------------------- -------------------- ------------------- ------------------------------ ------------------------------
    1 111 1 111 666
    1 222 1 111 666
    1 333 2 111 666
    1 555 3 111 666
    1 666 3 111 666
    2 111 1 111 555
    2 222 1 111 555
    2 333 2 111 555
    2 555 2 111 555

    ratio_to_report 占比函数

    SELECT  

       empno,ename,ename,hiredate,sal,deptno,  

        ratio_to_report(sal) over () as pct1,    

        ratio_to_report(sal) over (partition by deptno) as pct2  

       FROM emp;  

    Pct1 是求sal在所有sal中的比率

    Pct2是求sql在按deptno分组后 所占的比率

     

    NVL2

    Select nvl2(1,2,3) FROM DUAL 相当于三目运算符

    如果第一个参数为null 返回3,否则返回2

    COALESC

    从左到右返回第1个非null值,若所有的列表元素都为null,则返回null.它有短路计算功能,

    Select COALESC(null,null,null,2null) FROM DUAL

    translate函数

    translatex, from_string, to_string)函数在x中查找from_string中的字符,并将其转换成to_string中对应的字符。

    from_stringto_string中的字符是一一对应的,然后根据这种对应关系,将原字符串进行转换;

    select translate('ab123', 'abcdefg123', '3456789abc') from dual;

    结果是: 3b5abc

     

    展开全文
  • oracle字符查找函数instr()

    万次阅读 2018-07-30 17:14:03
    1、instr()函数的格式 (俗称:字符查找函数) 格式一:instr( string1, string2 ) / instr(源字符串, 目标字符串) instr(v_zh_claim_stutas_pro,'c')&gt;0 //v_zh_claim_stutas_pro→string1,'c'→string2...

    1、instr()函数的格式 (俗称:字符查找函数)

    格式一:instr( string1, string2 ) / instr(源字符串, 目标字符串)

    instr(v_zh_claim_stutas_pro,'c')>0
    //v_zh_claim_stutas_pro→string1,'c'→string2

    格式二:instr( string1, string2 [, start_position [, nth_appearance ] ] ) / instr(源字符串, 目标字符串, 起始位置, 匹配序号)

    解析:string2 的值要在string1中查找,是从start_position给出的数值(即:位置)开始在string1检索,检索第nth_appearance(几)次出现string2。

    select instr('helloworld','l',2,2) from dual;  --返回结果:4    也就是说:在"helloworld"的第2(e)号位置开始,查找第二次出现的“l”的位置
    select instr('helloworld','l',3,2) from dual;  --返回结果:4    也就是说:在"helloworld"的第3(l)号位置开始,查找第二次出现的“l”的位置
    select instr('helloworld','l',4,2) from dual;  --返回结果:9    也就是说:在"helloworld"的第4(l)号位置开始,查找第二次出现的“l”的位置
    select instr('helloworld','l',-1,1) from dual;  --返回结果:9    也就是说:在"helloworld"的倒数第1(d)号位置开始,往回查找第一次出现的“l”的位置
    select instr('helloworld','l',-2,2) from dual;  --返回结果:4    也就是说:在"helloworld"的倒数第1(d)号位置开始,往回查找第二次出现的“l”的位置
    select instr('helloworld','l',2,3) from dual;  --返回结果:9    也就是说:在"helloworld"的第2(e)号位置开始,查找第三次出现的“l”的位置
    select instr('helloworld','l',-2,3) from dual; --返回结果:3    也就是说:在"helloworld"的倒数第2(l)号位置开始,往回查找第三次出现的“l”的位置
    展开全文
  • 里面包含oracle所有函数介绍,可以随时随地查找里面包含oracle所有函数介绍,可以随时随地查找
  • 如何查找oracle的存储过程和函数

    万次阅读 2019-06-25 17:12:35
    那么如何利用sql迅速查找出所有的procedure和function select distinct name From user_source where type = ‘FUNCTION’ ; select distinct name From user_source where type = ‘PROCEDURE’ ; 注意...

    最近在做数据库转移,需要整理procedure和function
    那么如何利用sql迅速查找出所有的procedure和function
    select distinct name From user_source where type = ‘FUNCTION’ ;
    select distinct name From user_source where type = ‘PROCEDURE’ ;
    注意function 和 procedure一定要大写。

    展开全文
  • oracle中instr函数的格式,俗称:字符查找函数 1、instr()函数的格式 (俗称:字符查找函数) 格式一: instr( string1, string2 ) / instr(源字符串, 目标字符串) 格式二: instr( string1, string2 [, start_...

    oracle中instr函数的格式,俗称:字符查找函数

    1、instr()函数的格式 (俗称:字符查找函数)

    格式一:

    instr( string1, string2 )    /   instr(源字符串, 目标字符串)
    

    格式二:

    instr( string1, string2 [, start_position [, nth_appearance ] ] )   /   instr(源字符串, 目标字符串, 起始位置, 匹配序号)
    
    • 注:在Oracle/PLSQL中,instr函数返回要截取的字符串在源字符串中的位置。只检索一次,也就是说从字符的开始到字符的结尾就结束。

    2、实例

    格式一

    1 select instr('helloworld','l') from dual; --返回结果:3    默认第一次出现“l”的位置
    2 select instr('helloworld','lo') from dual; --返回结果:4    即:在“lo”中,“l”开始出现的位置
    3 select instr('helloworld','wo') from dual; --返回结果:6    即“w”开始出现的位置
    

    格式二

    select instr('helloworld','l',2,2) from dual;  --返回结果:4    也就是说:在"helloworld"的第2(e)号位置开始,查找第二次出现的“l”的位置
    select instr('helloworld','l',3,2) from dual;  --返回结果:4    也就是说:在"helloworld"的第3(l)号位置开始,查找第二次出现的“l”的位置
    select instr('helloworld','l',4,2) from dual;  --返回结果:9    也就是说:在"helloworld"的第4(l)号位置开始,查找第二次出现的“l”的位置
    select instr('helloworld','l',-1,1) from dual;  --返回结果:9    也就是说:在"helloworld"的倒数第1(d)号位置开始,往回查找第一次出现的“l”的位置
    select instr('helloworld','l',-2,2) from dual;  --返回结果:4    也就是说:在"helloworld"的倒数第2(l)号位置开始,往回查找第二次出现的“l”的位置
    select instr('helloworld','l',2,3) from dual;  --返回结果:9    也就是说:在"helloworld"的第2(e)号位置开始,查找第三次出现的“l”的位置
    select instr('helloworld','l',-2,3) from dual; --返回结果:3    也就是说:在"helloworld"的倒数第2(l)号位置开始,往回查找第三次出现的“l”的位置
    

    注:MySQL中的模糊查询 like 和 Oracle中的 instr() 函数有同样的查询效果; 如下所示:

    MySQL: select * from tableName where name like '%helloworld%';
    Oracle:select * from tableName where instr(name,'helloworld')>0;  --这两条语句的效果是一样的
    

    sql语句
    sql语句

    3、实例截图

    1、

    截图

    2、

    截图

    3、

    截图

    4、

    在这里插入图片描述

    5、

    在这里插入图片描述

    6、

    在这里插入图片描述

    7、

    在这里插入图片描述

    8、

    在这里插入图片描述

    9、

    在这里插入图片描述

    展开全文
  • Oracle函数主要分为单行函数和多行函数 一、特点: 单行函数可以多层嵌套,多行函数(组函数)只能嵌套两层(多层嵌套没有意义)。 多行函数通常用于整表或分组统计查询中 每次处理完一条记录返回一个结果 二...
  • Oracle函数之单值函数

    2017-07-20 14:09:15
    Oracle中单值函数有四种:字符函数,数字函数,日期函数,转换函数 字符函数Oracle中对字符类型的数据进行处理的函数 将字符类型的数据全部转为小写(lower) 比如:select lower(name) from test 查找出test表中...
  • instr()函数的格式 (俗称:字符查找函数) 格式一:instr( string1, string2 ) / instr(源字符串, 目标字符串) 格式二:instr( string1, string2 [, start_position [, nth_appearance ] ] ) / instr(源字符串, ...
  • 参考资料:Oracle截取字符串和查找字符串 oracle自定义函数学习和连接运算符(||) oracle 截取字符(substr),检索字符位置(instr) case when then else end语句使用 收藏常用函数:substr和instr1.SUBSTR(string...
  • Oracle日期函数和转换函数

    万次阅读 多人点赞 2019-04-25 20:54:03
    一、日期函数 日期函数用于处理date类型的数据,两个日期相减返回日期之间相差的天数。日期不允许做加法运算,无意义。 常见代表符号:yyyy 年,mm 月,dd 日,hh 小时,mi 分钟,ss 秒,day 星期 默认情况下日期...
  • Oracle函数

    万次阅读 2015-11-22 16:55:30
    Oracle函数 函数的分类: 单行函数:对每行输入值进行计算,得到相应的计算结果,返回给用户,也就是说,每行作为一个输入参数,经过函数计算得到每行的计算结果。比如length 多行函数:对多行输入值进行计算,...
  • SELECT name, type, referenced_owner, referenced_name, referenced_type  FROM user_dependencies WHERE referenced_name = 'TABLENAME' AND  referenced_type='TABLE' AND  type = 'PROCEDURE';
  • 原文地址:http://blog.csdn.net/nshk/archive/2008/03/21/2202088.aspx 参考文献: 《expert one-on-one》、《Oracle 9i reference》 oracle函数分两类:单行函数、多行函数 。多行函数也成为聚合函数、组合函数,...
  • oracle简单查找知识总结 字符串函数 日期函数 各种简单查找技术
  • Oracle SQL 提供了用于执行特定操作(超级便捷~)的专用函数。这些函数大大增强了(岂止是大大的~) SQL 语言的功能。函数可以接受零个或者多个输入参数,并返回一个输出结果。
  • Oracle 如何查找并删除重复记录

    千次阅读 2020-08-24 13:50:23
    本文介绍如何在 Oracle 数据库中查找并删除重复记录,包括使用聚合函数加上子查询或者连接查询、使用分析函数等方法查找单个字段或者多个字段中的重复数据,以及使用子查询中分析函数等方法实现重复数据的删除。
  • INSTR (源字符串, 目标字符串, 起始位置, 匹配序号) 在Oracle/PLSQL中,instr函数返回要截取的字符串在源字符串中的位置。只检索一次,就是说从字符的开始 到字符的结尾就结束。 语法如下: instr( string1, ...
  • Oracle单行函数和多行函数

    万次阅读 2013-10-10 14:39:20
    单行函数分为五种类型:字符函数、数值函数、日期函数、转换函数、通用函数 单行函数: [sql] view plaincopy --大小写控制函数 select lower('Hello World') 转小写, upper('Hello World') 转大写 ...
  • ORACLE 函数

    千次阅读 2017-03-04 07:43:44
    张哥同步视频:https://edu.csdn.net/course/play/7940字符函数字符函数参数全部是字符类作为参数,字符函数返回值有的是varchar2类型的值,有的是number类型的值,返回number类型时,没有定义任何精度和刻度范围 表...
  • Oracle分析函数一——函数列表

    千次阅读 2009-10-22 11:24:00
    原创于2009年08月02日,2009年10月... Oracle分析函数 Oracle分析函数——函数列表 SUM :该函数计算组中表达式的累积和MIN :在一个组中的数据窗口中查找表达式的最小值MAX :在一个组中的数据窗口中查找表达式
  • Oracle SQL提供了用于执行特定操作的专用函数。这些函数大大增强了SQL语言的功能。 SQL函数的分类: 单行函数 对每一个函数应用在表的记录中时,只能输入一行结果,返回一个结果,可以出现在 SELECT 子句中和 ...
  • @Oracle聚合函数/分析函数

    万次阅读 2008-03-21 10:44:00
    参考文献: 《expert one-on-one》、《Oracle 9i reference》oracle函数分两类:单行函数、多行函数 。多行函数也成为聚合函数、组合函数,参数为数组,数据大小为记录数,这种数组不是普通高级语言的数组,是一种...
  • Oracle单行函数

    千次阅读 2019-09-30 10:33:07
    Oracle的单行函数包括: 字符串函数、数值函数、日期函数、转换函数、通用函数 字符串函数 UPPER(列或字符串):转成大写函数 LOWER(列或字符串):转成小写函数 INITCAP(列或字符串):把字符串的第一个字母转换成大写...
  • 1)instr()函数的格式 (俗称:字符串查找函数) 格式一:instr( string1, string2 ) / instr(源字符串, 目标字符串) 格式二:instr( string1, string2 [, start_position [, nth_appearance ] ] ) / instr(源字符...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 70,805
精华内容 28,322
关键字:

oracle里面查找中文的高函数