Oracle中碰到的函数和关键字收集
|
一、时间处理函数 trunc(sysdate) 返回日期 to_date() to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') to_number() 转为数字 二、字符串处理函数 substr(bag.serialid,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,instr(xmltext,chr(10))-1); dbms_output.put_line(line); xmltext :=substr(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 申明结果集 refcursys_refcursor; begin --将查询结果保存到结果集 OPEN refCur FOR select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as time, substr(bag.serialid,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; （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |