文档章节

Oracle中碰到的函数和关键字收集

风吹过的绿洲
 风吹过的绿洲
发布于 2017/05/05 10:34
字数 1105
阅读 7
收藏 0

一、时间处理函数

trunc(sysdate) 返回日期

to_date()

to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')

to_number() 转为数字

 

二、字符串处理函数

substr(bag.serialid, 0, 13) 截取字符串

regexp_substr(i_sBloodSubType, '[^,]+', 1, level) 正则

regexp_replace(i_sBloodSubType, '[^,]+', '')

regexp_like()

instr(b.fullname, '新鲜') 返回要截取的字符串在源字符串中的位置 参数:(源字符串, 目标字符串, 起始位置, 匹配序号)

length()

 

 

三、判断

=

!=

>

<

and

or

not

IS NULL

in ('血浆类', '低温沉淀物类')

exists()

coalesce(表达式1,表达式2,...,表达式n),n>=2,此表达式的功能为返回第一个不为空的表达式,如果都为空则返回空值。

nvl(表达式1,表达式2) 为空则取表达式2的值

decode() DECODE(value,if1,then1,if2,then2,if3,then3,...,else)

case when...then.... end 表达式

CASE语句寻找when的优先级是从上到下。再多的when,也只有一个出口,即其中有一个满足了expr就马上退出case,这点需要牢记

 

四、整合

cursor() 返回{字段名=值,.....} 整个结果集作为某一个字段 仅Oracle可用

 

五、去重

distinct

 

六、聚集查询

count()

sum()

max()

min()

avg()

查询聚集函数得到的结果和其他字段,该字段一定要被group by,否则会报错

wm_concat(column)  将字段合并

row_number() over(partition by 列 order by 列 desc) 为什么要用它?其实每次查询都会有一个伪列rownum,只是伪列的顺序是乱的,解决伪列顺序的问题还得使用子查询。所以这里直接用row_number()

group by rollup() grouping() 参考http://blog.itpub.net/519536/viewspace-610995/

实例:

group_id进行普通的group by操作---按照小组进行分组

SQL> select group_id,sum(salary) from group_test group by group_id;

 

GROUP_ID SUM(SALARY)

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

30 12000

20 8000

40 16000

10 4000

group_id进行普通的roolup操作---按照小组进行分组,同时求总计

SQL> select group_id,sum(salary) from group_test group by rollup(group_id);

 

GROUP_ID SUM(SALARY)

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

10 4000

20 8000

30 12000

40 16000

40000

以上rollup语句可以翻译成group by union all 语句,为:

select group_id,sum(salary) from group_test group by group_id

union all

select null, sum(salary) from group_test

order by 1;

 

GROUP_ID SUM(SALARY)

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

10 4000

20 8000

30 12000

40 16000

40000

如果显示“1”表示GROUPING函数对应的列(例如JOB字段)是由于ROLLUP函数所产生的空值对应的信息,即对此列进行汇总计算后的结果。

如果显示“0”表示此行对应的这列参未与ROLLUP函数分组汇总活动。

having子句 一般是同group by一起使用的,用来筛选聚合后的结果

 

 

七、结果联合

union 可能有重复值,且需要去重的时候使用,否则会浪费去重的查询资源

union all 不需要去重的时候使用

with as 把一大堆重复用到的sql语句放在with as里面,取一个别名,后面的查询就可以用它

 

八、左右连接另一种写法

h.operator=op.id(+) 右连接

h.operator(+)=op.id 左连接

 

九、将结果集转为xml形式返回

dbms_xmlgen 用户处理xml格式 sys_refcursor 用来接收结果集 sysdate 系统时间

oracle 10g中,有两个包,用于处理xml格式: dbms_xmlgen 和 dbms_xmlstore.

更多dbms参考 http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_xmlgen.htm#i1010859

2.使用dbms_xmlgen生成数据表的xml格式

declare

  xmlhdl dbms_xmlgen.ctxtype;

  line varchar2(200);

  xmltext varchar2(32767);

begin

  -- create ctxhandle

  xmlhdl := dbms_xmlgen.newcontext('select * fromxmldemo');

  --generate xml format data into clob

  xmltext :=dbms_xmlgen.getxml(xmlhdl);

  --display the xml content

loop

  exit when xmltext is null;

  line :=substr(xmltext,1,instr(xmltext,chr(10))-1);

 dbms_output.put_line(line); 

  xmltext :=substr(xmltext,instr(xmltext,chr(10))+1);

end loop;

  --close ctxhandle

  dbms_xmlgen.closecontext(xmlhdl);

end;

3.dbms_xmlgen其它函数

  getnumrowsprocessed(xmlhandle):=getxml所处理的实际行数

 dbms_xmlgen.setMaxRows(xmlhdl,10):=允许处理最大行数

 dbms_xmlgen.setrowsettag(xmlhdl,'Packet'):=重置行集标签rowset

 dbms_xmlgen.setrowtag(xmlhdl,'Record'):=重置行标签row

 dbms_xmlgen.setnullhandling(xmlhdl,2):=列空值如何表示,如:<A/>

 

实例:

function queryBloodIn(i_sHandoverId in varchar2) return clob as

res clob;

-- 和 dbms_xmlgen.ctxtype 结果一样

ctx dbms_xmlgen.ctxHandle;

-- declare result set 申明结果集

refcur sys_refcursor;

begin

-- 将查询结果保存到结果集

OPEN refCur FOR

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as time,

substr(bag.serialid, 0, 13) as serialid,

b.shortname as bloodname,

bst.name as bloodsubtype

from pm_handover h,pm_handoverdetail hd,bloodbag bag,d_blood b,d_bloodsubtype bst

where h.id=i_sHandoverId

and h.type in (2, 102, 152)

and hd.handoverid=h.id

and (h.accepttime > trunc(sysdate) or exists(select null from pm_store s where s.bloodbagid = hd.bloodbagid and s.status in ('Processing', 'Producing')))

and hd.bloodbagid=bag.id

and bag.bloodid=b.id

and b.subtype=bst.id;

-- get Handle 得到处理对象

ctx:=dbms_xmlgen.newContext(refCur);

-- 空值处理

dbms_xmlgen.setNullHandling(ctx,dbms_xmlgen.EMPTY_TAG);

-- 设置列名作为标签名

dbms_xmlgen.useItemTagsForColl(ctx);

-- 得到xml结果

res:=dbms_xmlgen.getXML(ctx);

-- 关闭上下文

dbms_xmlgen.closeContext(ctx);

RETURN res;

end;

本文转载自:http://www.cnblogs.com/jay763190097/p/6429395.html

风吹过的绿洲

风吹过的绿洲

粉丝 4
博文 11
码字总数 0
作品 0
株洲
私信 提问
小朋友学C++(19):函数模板

先看一段微软实现math.h中求幂的源码 这里template表示模板。 在了解模板之前,咱们先来求一下两个 int型的和,两个float型的和,两个double型的和 运行结果: 分析:这里定义了三种类型的s...

海天一树X
2018/06/06
0
0
循规蹈矩:快速读懂SQL执行计划的套路与工具

作者介绍 前两章给大家介绍了发现问题后的整体解决思路,接下来进入SQL优化的局部性思路,这一章我们将学习SQL优化的重要知识:执行计划。 执行计划是什么?如何读懂执行计划?读懂执行计划对...

梁敬彬
2017/07/31
0
0
循规蹈矩:快速读懂SQL执行计划的套路与工具

作者介绍 前两章给大家介绍了发现问题后的整体解决思路,接下来进入SQL优化的局部性思路,这一章我们将学习SQL优化的重要知识:执行计划。 执行计划是什么?如何读懂执行计划?读懂执行计划对...

梁敬彬
2017/07/31
0
0
求不使用服务器后台的搜索字符串转码js方法

聚合搜索网站一找一大把,不过几乎都是使用了后台技术的。自己研究了一下,发现核心原理其实真TMD的简单,所以想做一个通用的聚合搜索插件,目标是不依靠后台技术,纯js方法实现。谁想用了拷...

lzsong
2013/02/22
238
2
警惕!自定义函数索引的那些陷阱及避坑术

作者介绍 丁俊,DBAplus社群联合发起人,新炬网络专家团成员,性能优化专家,Oracle ACEA,ITPUB开发版资深版主。十年电信行业从业经验,从事过系统开发与维护、业务架构和数据分析、系统优化...

丁俊
2016/10/09
0
0

没有更多内容

加载失败,请刷新页面

加载更多

编程作业20190210900169

1编写一个程序,提示用户输入名和姓,然后以“名,姓”的格式打印出来。 #include <stdio.h>#include <stdlib.h> int main(){ char firstName[20]; char lastName[20]; print......

1李嘉焘1
17分钟前
2
0
补码的优点及原理分析

只讨论整数 1.计算机内部为什么没有减法器? 减法运算本身其实就是加法,如x - y即x +(-y),所以只需要将负数成功表示出来并可以参加加法运算,那加法器就可同时实现“+”和“-”的运算。这...

清自以敬
33分钟前
59
0
Docker 可视化管理 portainer

官网安装指南: https://portainer.readthedocs.io/en/latest/deployment.html docker-compose.yml 位置,下载地址:https://downloads.portainer.io/docker-compose.yml...

Moks角木
今天
5
0
Spring Security 实战干货:必须掌握的一些内置 Filter

1. 前言 上一文我们使用 Spring Security 实现了各种登录聚合的场面。其中我们是通过在 UsernamePasswordAuthenticationFilter 之前一个自定义的过滤器实现的。我怎么知道自定义过滤器要加在...

码农小胖哥
今天
8
0
常见分布式事务解决方案

1 微服务的发展 微服务倡导将复杂的单体应用拆分为若干个功能简单、松耦合的服务,这样可以降低开发难度、增强扩展性、便于敏捷开发。当前被越来越多的开发者推崇,很多互联网行业巨头、开源...

asdf08442a
今天
5
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部