,单击此处编辑母版标题样式,2019/12/19,#,单击此处编辑母版文本样式,第二级,ORACLE,开发技术,(,一,),查询函数被使用,-,视图,函数的内容以,LONG,类型存储，无法使用,SQL,语句进行模糊查询操作,SELECT*from user_views,where view_name like CUX%and instr(to_lob(text),STRACT)0,在,PL/SQL,中通过,referenced by,查看被引用,查询函数或存储过程被使用,函数的内容以,LONG,类型存储，无法使用,SQL,语句进行模糊查询操作,select*from user_source,WHERE NAME LIKE CUX_BOM_NOTIN_PRC%,and instr(UPPER(text),INV_ORGCODE)0,自定义函数：,STRCAT,的使用,select description,strcat(vendor_name),from cux_employee_department,where description in(,财务中心,人力资源部,),group by description,正则函数：,REGEXP_SUBSTR,的使用,function REGEXP_SUBSTR(String,pattern,position,occurrence,modifier),_srcstr,：需要进行正则处理的字符串,_pattern,：进行匹配的正则表达式,_position,：起始位置，从第几个字符开始正则表达式匹配（默认为,1,）,_occurrence,：标识第几个匹配组，默认为,1,_modifier,：模式（,i,不区分大小写进行检索；,c,区分大小写进行检索。默认为,c,。）,正则函数：,REGEXP_SUBSTR,的使用,1,、查询使用正则分割后的第一个值,SELECT REGEXP_SUBSTR(17,20,23,+,1,1,i)AS STR FROM DUAL,2,、查询使用正则分割后的最后一个值，也就是,23,SELECT REGEXP_SUBSTR(17,20,23,+,1,3,i)AS STR FROM DUAL,3,、获取一个多个数值的列，从而能够让结果以多行的形式展示出来,SELECT LEVEL FROM DUAL CONNECT BY LEVEL=7,4,、将上面,REGEXP_SUBSTR,的,occurrence,关联,SELECT NVL(REGEXP_SUBSTR(17,20,23,+,1,LEVEL,i),NULLL)AS STR,FROM DUAL CONNECT BY LEVEL=7;,正则函数：,REGEXP_SUBSTR,的使用,5,、优化上面的,SQL,语句，让生成的行的数量符合实际情况,SELECT REGEXP_SUBSTR(17,20,23,+,1,LEVEL,i)AS STR,FROM DUAL,CONNECT BY LEVEL=,LENGTH(17,20,23)-LENGTH(REGEXP_REPLACE(17,20,23,)+1,正则函数：,REGEXP_SUBSTR,的使用,6,、运用实例,SELECT REGEXP_SUBSTR(t.vendor_number,+,1,LEVEL,i)AS STR,FROM,(select description,strcat(vendor_number)vendor_number,from cux_employee_department,where description in(,财务中心,),group by description)t,CONNECT BY LEVEL=,LENGTH(t.vendor_number)-LENGTH(REGEXP_REPLACE(t.vendor_number,)+1,正则函数：,REGEXP_SUBSTR,的使用,分析函数：,LAG,和,LEAD,的使用,Lag,和,Lead,分析函数可以在同一次查询中取出同一字段的前,N,行的数据,(Lag),和后,N,行的数据,(Lead),作为独立的列。,这种操作可以代替表的自联接，并且,LAG,和,LEAD,有更高的效率,语法：,lag(exp_str,offset,defval)over(),Lead(exp_str,offset,defval)over(),-exp_str,要取的列,-offset,取偏移后的第几行数据,-defval:,没有符合条件的默认值,分析函数：,LAG,和,LEAD,的使用,select yearmonth,ou_name,item_number,quantity,lag(t.quantity,1,0)over(order by yearmonth)quantity1,lead(t.quantity,1,0)over(order by yearmonth)quantity2,quantity-(lag(t.quantity,1,0)over(order by yearmonth)quantity3,from(,select yearmonth,ou_name,item_number,sum(quantity)quantity,from cux_mtl_onhand_quantities_mont,where item_number=900110-0411H01,group by yearmonth,ou_name,item_number,)t,绑定变量,Sql,语句的执行要经过解析、执行、提取等几个阶段，其中解析最消耗资源，解析的过程中要进行语法、语义和权限的检查，如果这些检查都通过了，则进行执行，执行完成之后将,sql,语句的执行计划存储在共享池中，如果下一次有相同的,sql,语句要执行，则不需要解析，直接按照已经存在的执行计划进行执行，就可以节省资源当多个,sql,语句执行的时候大多数情况下是条件相同，只是条件里面的值不同。,例如：,A,用户：,select*fromtwhereID=1,B,用户：,select*fromt,where,ID=2,绑定变量就是将条件谓词中不同的值保存在一个中间变量中，,Oracle,对用户每次发起的,sql,语句做,hash,运算时，都产生相同的,hash,值，使用相同的执行计划，作为一个,sql,语句来执行。,Select*fromtwhereID=:X,绑定变量,create or replace procedure testproc(p_name varchar2)as,v_sql varchar2(1000);,v_dept varchar2(100);,begin,v_sql:=select description from cux_employee_department where vendor_name=:vendor_name;,execute immediate v_sql into v_dept using p_name;,dbms_output.put_line(v_dept);,end;,