oracle_pl_sql部分语法

内容提要•并行关键词•游标语法•循环语法•Update技巧•动态sql技巧•分析的理解•Shell调用数据库操作•Job工作方式-3-并行关键词建表.txtUpdate.txtDelete.txt•并行操作以消耗资源换取效率；•在非数据处理时，不建议使用并行；•并行不是万能的，使用时要慎重；•使用完成后，切记将表和索引的并行参数去掉；-4-游标语法•declare•inumber:=0;•V_ERR_SYS_CODEVARCHAR2(100);--系统错误代码•V_ERR_SYS_TEXTVARCHAR2(500);--系统错误原因•V_NUMNUMBER(10):=0;•V_PROCE_NUMNUMBER(10):=0;--处理条数•CURSORCUR_ORDERIS•selectrowidfromwservorderinterface200909whereop_notelike'%实时停开机进程%'•andlength(interface_data)2;•TYPET_ORDERISTABLEOFCUR_ORDER%ROWTYPEINDEXBYPLS_INTEGER;•V_ORDERT_ORDER;•begin•OPENCUR_ORDER;•LOOP•FETCHCUR_ORDERBULKCOLLECTINTOV_ORDERLIMIT5000;•IFV_ORDER.COUNT=0THEN•EXIT;•ENDIF;•V_PROCE_NUM:=V_ORDER.COUNT;•FORIINV_ORDER.FIRST..V_ORDER.LASTLOOP•BEGIN•Updatewservorderinterface200909•SETinterface_data=''WHEREROWID=V_ORDER(I).ROWID;•EXCEPTIONWHENOTHERSTHEN•V_ERR_SYS_CODE:=SUBSTR(TO_CHAR(SQLCODE),1,50);•V_ERR_SYS_TEXT:=SUBSTR(TO_CHAR(SQLERRM),1,150);•DBMS_OUTPUT.PUT_LINE(V_ORDER(I).ROWID||'-'||V_ERR_SYS_CODE||'-'||V_ERR_SYS_TEXT);•END;•ENDLOOP;•COMMIT;•V_NUM:=V_NUM+V_PROCE_NUM;•ENDLOOP;•CLOSECUR_order;•COMMIT;•DBMS_OUTPUT.PUT_LINE('全部处理共：'||TO_CHAR(V_NUM));•end;-5-•declare•v_order_numvarchar(30);•v_deal_dtdate;•inumber:=0;•j_wrongnumber:=0;•V_ERR_SYS_CODEvarchar2(500);•V_ERR_SYS_TEXTvarchar2(500);•begin•forrecin(selectrowidfromfan_20091022_1whereFAVOUR_CODE='a040_aa')loop•begin•deletefan_20091022_1WHEREROWID=V_ORDER(I).ROWID;•i:=i+1;•EXCEPTIONWHENOTHERSTHEN•V_ERR_SYS_CODE:=SUBSTR(TO_CHAR(SQLCODE),1,500);•V_ERR_SYS_TEXT:=SUBSTR(TO_CHAR(SQLERRM),1,500);•j_wrong:=j_wrong+1;•END;••commit;•endloop;•dbms_output.put_line('修改：'||to_char(i));•dbms_output.put_line('错误：'||to_char(j_wrong)||V_ERR_SYS_TEXT);•end;游标语法-6-游标语法注意事项•可持续调用执行；•每次处理一定的量进行提交（5000效率最好）;•将每次处理的数据放在数组中执行，效率会提高；•保持良好的习惯，增加异常处理；•适当增加输出信息，便于查看；•合理的增加注释，为了别人看懂程序；-7-数组的应用•createorreplaceprocedurePRO_HOST_MONITOR•/***************************************************•过程名称：PRO_PRE_UPDTE_FIRST•输入：•in_type--输入标识，1检查文件空间2服务监控•作者：fanxian•***************************************************/•(•in_typeINVARCHAR2,•in_msgINVARCHAR2•)•is•PRAGMAAUTONOMOUS_TRANSACTION;--自治事务为插入日志信息表•ll_tempINT;•ll_countnumber;•TYPEvar_base_01ISVARRAY(50)OFVARCHAR2(20);•lc_o_telnumvar_base_01;•begin•ll_temp:=0;••--需要发送的手机号码列表•lc_o_telnum:=var_base_01('13209980120','13209980025','13209980027','13201356006');•FORiIN1..lc_o_telnum.COUNTloop•iftrim(lc_o_telnum(i))isnotnullthen•InsertintoTINEXWORK_SEND•(workshtsn,•exworksn,•mainsvctype,busitype,optcode,workcode,priflag,hlrcode,svcnum,•applydate,finishdate,instcode,•instinfo,•pri,defeatcode,defeatreason,dealflag,nextmainsvctype,resend,stand)•Values•(to_char(sysdate,'dd')||lc_o_telnum(i)||to_char(sysdate,'yyyymmddhh24miss')||to_char(''),•to_char(sysdate,'dd')||lc_o_telnum(i)||to_char(sysdate,'yyyymmddhh24miss')||to_char(''),•'M','M','00001','1302','0','MS0',lc_o_telnum(i),•sysdate,sysdate,'FDX',•'86'||lc_o_telnum(i)||'$'||trim(in_type||in_msg),•'0','F','F','0','F',1,'F');•endif;•ENDLOOP;••COMMIT;--自治事物，只提交本存储过程内的数据•EXCEPTIONWHENOTHERSTHEN•DBMS_OUTPUT.PUT_LINE(SQLERRM);•COMMIT;--自治事物，只提交本存储过程内的数据•RETURN;•endPRO_HOST_MONITOR;-8-数组的应用•CREATEORREPLACEPROCEDUREctxj_cdma_patch_v106(in_area_idINVARCHAR2)•IS•v_proc_idNUMBER(10);•v_numNUMBER(10);••CURSORcur_patchIS•SELECTb.row_id,b.prod_idFROMtemp_asset_numa,siebel.s_assetb•WHEREa.area_id=in_area_id•ANDa.user_id=b.x_prod_inst_num;•TYPEt_patchISTABLEOFcur_patch%ROWTYPEINDEXBYPLS_INTEGER;•v_patcht_patch;••TYPErec_asset_prodISRECORD•(•row_idVARCHAR2(30),•prod_idVARCHAR2(30),•par_rel_idVARCHAR2(30),•orig_idVARCHAR2(30)•);•TYPEt_asset_prodISTABLEOFrec_asset_prodINDEXBYPLS_INTEGER;•v_asset_prodt_asset_prod;•BEGIN•SELECTseq_proc_id.nextvalINTOv_proc_idFROMdual;••OPENcur_patch;•LOOP•FETCHcur_patchBULKCOLLECTINTOv_patchLIMIT5000;•IFv_patch.count=0THEN•EXIT;•ENDIF;•v_num:=v_patch.count;•FORiINv_patch.first..v_patch.lastLOOP•SELECTasset.row_id,asset.prod_id,obj.par_rel_id,obj.orig_id•BULKCOLLECTINTOv_asset_prod•FROMsiebel.s_assetasset,siebel.s_prod_intprod,siebel.s_vodvod,siebel.s_iss_sub_objobj•WHEREasset.root_asset_id=v_patch(i).row_id•ANDasset.x_product_type_cd='产品'•ANDprod.cfg_model_id=vod.object_num•ANDvod.row_id=obj.vod_id•ANDprod.row_id=v_patch(i).prod_id•ANDobj.sub_obj_id=asset.prod_id•ANDobj.last_vers=0;•IFv_asset_prod.count0THEN•FORjINv_asset_prod.first..v_asset_prod.lastLOOP•UPDATEsiebel.s_assetNOLOGGINGSETassembly_port_id=v_asset_prod(j).par_rel_id,port_valid_prod_id=v_asset_prod(j).orig_id•WHERErow_id=v_asset_prod(j).row_id;•ENDLOOP;•ENDIF;•ENDLOOP;•UPDATEproc_crm_logNOLOGGINGSETnum=num+v_num,end_date=SYSDATE•WHEREproc_id=v_proc_id;•COMMIT;•ENDLOOP;•CLOSEcur_patch;•UPDATEproc_crm_logNOLOGGINGSETstatus_cd='完成',upd_flg=1,end_date=SYSDATEWHEREproc_id=v_proc_id;•COMMIT;•END;•/-9-数组的应用注意事项•自主事务的应用；•数组的声明方式；•数组的循环；•异常处理机制；•动态数组的使用；-10-Update小技巧•declare•v_row_idvarchar(40);•inumber:=0;•begin--修改帐户实例•forrecin(selectto_char(a.t_value)t_value,to_char(a.new_value)new_value•fromC_M_MAPPING_ID_PATCH24awherea.s_tname='C_ACCT')lo