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

@H_301_0@一、时间处理函数

@H_301_0@trunc(sysdate) 返回日期

@H_301_0@to_date()

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

@H_301_0@to_number() 转为数字

@H_301_0@二、字符串处理函数

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

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

@H_301_0@regexp_replace(i_sBloodSubType,'')

@H_301_0@regexp_like()

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

@H_301_0@length()

@H_301_0@三、判断

@H_301_0@=

@H_301_0@!=

@H_301_0@>

@H_301_0@<

@H_301_0@and

@H_301_0@or

@H_301_0@not

@H_301_0@IS NULL

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

@H_301_0@exists()

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

@H_301_0@nvl(表达式1，表达式2) 为空则取表达式2的值

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

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

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

@H_301_0@四、整合

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

@H_301_0@五、去重

@H_301_0@distinct

@H_301_0@六、聚集查询

@H_301_0@count()

@H_301_0@sum()

@H_301_0@max()

@H_301_0@min()

@H_301_0@avg()

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

@H_301_0@wm_concat(column) 将字段合并

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

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

@H_301_0@实例：

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

@H_301_0@sql> select group_id,sum(salary) from group_test group by group_id;

@H_301_0@GROUP_ID SUM(SALARY)

@H_301_0@---------- -----------

@H_301_0@30 12000

@H_301_0@20 8000

@H_301_0@40 16000

@H_301_0@10 4000

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

@H_301_0@sql> select group_id,sum(salary) from group_test group by rollup(group_id);

@H_301_0@GROUP_ID SUM(SALARY)

@H_301_0@---------- -----------

@H_301_0@10 4000

@H_301_0@20 8000

@H_301_0@30 12000

@H_301_0@40 16000

@H_301_0@40000

@H_301_0@以上rollup语句可以翻译成group by union all 语句，为：

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

@H_301_0@union all

@H_301_0@select null,sum(salary) from group_test

@H_301_0@order by 1;

@H_301_0@GROUP_ID SUM(SALARY)

@H_301_0@---------- -----------

@H_301_0@10 4000

@H_301_0@20 8000

@H_301_0@30 12000

@H_301_0@40 16000

@H_301_0@40000

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

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

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

@H_301_0@七、结果联合

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

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

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

@H_301_0@八、左右连接另一种写法

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

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

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

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

@H_301_0@oracle 10g中，有两个包，用于处理xml格式：dbms_xmlgen和dbms_xmlstore.

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

@H_301_0@declare

@H_301_0@xmlhdl dbms_xmlgen.ctxtype;

@H_301_0@line varchar2(200);

@H_301_0@xmltext varchar2(32767);

@H_301_0@begin

@H_301_0@-- create ctxhandle

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

@H_301_0@--generate xml format data into clob

@H_301_0@xmltext :=dbms_xmlgen.getxml(xmlhdl);

@H_301_0@--display the xml content

@H_301_0@loop

@H_301_0@exit when xmltext is null;

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

@H_301_0@dbms_output.put_line(line);

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

@H_301_0@end loop;

@H_301_0@--close ctxhandle

@H_301_0@dbms_xmlgen.closecontext(xmlhdl);

@H_301_0@end;

@H_301_0@3.dbms_xmlgen其它函数

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

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

@H_301_0@dbms_xmlgen.setrowsettag(xmlhdl,'Packet')：＝重置行集标签rowset

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

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

@H_301_0@实例：

@H_301_0@function queryBloodIn(i_sHandoverId in varchar2) return clob as

@H_301_0@res clob;

@H_301_0@--和dbms_xmlgen.ctxtype结果一样

@H_301_0@ctx dbms_xmlgen.ctxHandle;

@H_301_0@-- declare result set 申明结果集

@H_301_0@refcursys_refcursor;

@H_301_0@begin

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

@H_301_0@OPEN refCur FOR

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

@H_301_0@substr(bag.serialid,13) as serialid,

@H_301_0@b.shortname as bloodname,

@H_301_0@bst.name as bloodsubtype

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

@H_301_0@where h.id=i_sHandoverId

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

@H_301_0@and hd.handoverid=h.id

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

@H_301_0@and hd.bloodbagid=bag.id

@H_301_0@and bag.bloodid=b.id

@H_301_0@and b.subtype=bst.id;

@H_301_0@-- get Handle 得到处理对象

@H_301_0@ctx:=dbms_xmlgen.newContext(refCur);

@H_301_0@-- 空值处理

@H_301_0@dbms_xmlgen.setNullHandling(ctx,dbms_xmlgen.EMPTY_TAG);

@H_301_0@-- 设置列名作为标签名

@H_301_0@dbms_xmlgen.useItemTagsForColl(ctx);

@H_301_0@--得到xml结果

@H_301_0@res:=dbms_xmlgen.getXML(ctx);

@H_301_0@-- 关闭上下文

@H_301_0@dbms_xmlgen.closeContext(ctx);

@H_301_0@RETURN res;

@H_301_0@end;

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

猜你在找的Oracle相关文章