ORACLE开发技术一.pptx

ORACLE 开发技术(一)查询函数被使用-视图函数的内容以LONG类型存储，无法使用SQL语句进行模糊查询操作SELECT*fromuser_viewswhereview_namelikeCUX%andinstr(to_lob(text),STRACT)0在PL/SQL中通过referencedby查看被引用查询函数或存储过程被使用函数的内容以LONG类型存储，无法使用SQL语句进行模糊查询操作select*fromuser_sourceWHERENAMELIKECUX_BOM_NOTIN_PRC%andinstr(UPPER(text),INV_ORGCODE)0自定义函数：STRCAT的使用selectdescription,strcat(vendor_name)fromcux_employee_departmentwheredescriptionin(财务中心,人力资源部)groupbydescription正则函数：REGEXP_SUBSTR的使用functionREGEXP_SUBSTR(String,pattern,position,occurrence,modifier)_srcstr：需要进行正则处理的字符串_pattern：进行匹配的正则表达式_position：起始位置，从第几个字符开始正则表达式匹配（默认为1）_occurrence：标识第几个匹配组，默认为1_modifier：模式（i不区分大小写进行检索；c区分大小写进行检索。默认为c。）正则函数：REGEXP_SUBSTR的使用1、查询使用正则分割后的第一个值SELECTREGEXP_SUBSTR(17,20,23,+,1,1,i)ASSTRFROMDUAL2、查询使用正则分割后的最后一个值，也就是23SELECTREGEXP_SUBSTR(17,20,23,+,1,3,i)ASSTRFROMDUAL3、获取一个多个数值的列，从而能够让结果以多行的形式展示出来SELECTLEVELFROMDUALCONNECTBYLEVEL=74、将上面REGEXP_SUBSTR的occurrence关联SELECTNVL(REGEXP_SUBSTR(17,20,23,+,1,LEVEL,i),NULLL)ASSTRFROMDUALCONNECTBYLEVEL=7;正则函数：REGEXP_SUBSTR的使用5、优化上面的SQL语句，让生成的行的数量符合实际情况SELECTREGEXP_SUBSTR(17,20,23,+,1,LEVEL,i)ASSTRFROMDUALCONNECTBYLEVEL=LENGTH(17,20,23)-LENGTH(REGEXP_REPLACE(17,20,23,)+1正则函数：REGEXP_SUBSTR的使用6、运用实例SELECTREGEXP_SUBSTR(t.vendor_number,+,1,LEVEL,i)ASSTRFROM(selectdescription,strcat(vendor_number)vendor_numberfromcux_employee_departmentwheredescriptionin(财务中心)groupbydescription)tCONNECTBYLEVEL=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(orderbyyearmonth)quantity2,quantity-(lag(t.quantity,1,0)over(orderbyyearmonth)quantity3from(selectyearmonth,ou_name,item_number,sum(quantity)quantityfromcux_mtl_onhand_quantities_montwhereitem_number=900110-0411H01groupbyyearmonth,ou_name,item_number)t绑定变量Sql语句的执行要经过解析、执行、提取等几个阶段，其中解析最消耗资源，解析的过程中要进行语法、语义和权限的检查，如果这些检查都通过了，则进行执行，执行完成之后将sql语句的执行计划存储在共享池中，如果下一次有相同的sql语句要执行，则不需要解析，直接按照已经存在的执行计划进行执行，就可以节省资源当多个sql语句执行的时候大多数情况下是条件相同，只是条件里面的值不同。例如：A用户：select*fromtwhereID=1B用户：select*fromtwhereID=2绑定变量就是将条件谓词中不同的值保存在一个中间变量中，Oracle对用户每次发起的sql语句做hash运算时，都产生相同的hash值，使用相同的执行计划，作为一个sql语句来执行。Select*fromtwhereID=:X绑定变量createorreplaceproceduretestproc(p_namevarchar2)asv_sqlvarchar2(1000);v_deptvarchar2(100);beginv_sql:=selectdescriptionfromcux_employee_departmentwherevendor_name=:vendor_name;executeimmediatev_sqlintov_deptusingp_name;dbms_output.put_line(v_dept);end;