admin 管理员组文章数量: 887021
2024年1月15日发(作者：怎样看一本编程专业书)
1、RETURNING语句用法TheRETURNINGClauseIncludetheRETURNINGclausewithanINSERT,UPDATE,customersSETcredit_limit=credit_limit*1.10WHEREcustomer_id=p_in_idRETURNINGcredit_limitINTOo_credit;2、自定义数据类型Typet_ic07ISTableOFic07%rowtypeIndexByBinary_Integer;tab_ic07t_ic07;Typecur_ic07ISREFCursorReturnic07%rowtype;Typerec_treatmentISRecord(str_001%type,str_001%type,str_002%type,str_234%type,n_142%type,str_201%type,str_145%type);Typet_pertreatISTABLEOFrec_treatmentINDEXBYBINARY_INTEGER;tab_treatmentt_pertreat;tab_PerTreatt_pertreat;Typecur_treatmentISREFCURSORReturnrec_treatment;3、批绑定用法DECLARETYPEReplistISVARRAY(100)OFNUMBER;RepidsREPLIST:=REPLIST(153,155,156,161);_total%TYPE;TotlistNUMLIST;big_totalCONSTANTNUMBER:=60000;..DATEordersSETorder_total=.95*order_totalWHEREsales_rep_id=Repids(i)ANDorder_total>big_total
RETURNINGorder_totalBULKCOLLECTINTOTotlist;END;/4、用自定义记录集作为参数（UseUser-DefinedRecordsasParameters）DECLARETYPECustRecISRECORD(customer_er_id%TYPE,cust_last_nameVARCHAR2(20),cust_emailVARCHAR2(30),credit_limitNUMBER(9,2));...PROCEDUREraise_credit(cust_infoCustRec);5、用集合作为参数（UseCollectionsasArguments）PACKAGEcust__last_name%TYPEINDEXBYBINARY_INTEGER;_limit%TYPEINDEXBYBINARY_INTEGER;...PROCEDUREcredit_batch(name_tabINNameTabTyp,credit_tabINCreditTabTyp,...);PROCEDURElog_names(name_tabINNameTabTyp);ENDcust_actions;6、动态SQL执行CREATEPROCEDUREinsert_into_table(table_nameVARCHAR2,p_prod_idNUMBER,p_wh_idNUMBER,p_quantityNUMBER)ISv_stmt_strVARCHAR2(200);BEGINv_stmt_str:='INSERTINTO'||table_name||'values(:prod_id,:wh_id,:qoh)';EXECUTEIMMEDIATEv_stmt_strUSINGp_prod_id,p_wh_id,p_quantity;ENDinsert_into_table;/7、动态SQL执行2
n_cursor:=dbms__cursor;dbms_(n_cursor,str_sql,dbms_);n_rows:=dbms_e(n_cursor);dbms__cursor(n_cursor);8、分析PL/SQL(ProfilingPL/SQL)ingPL/ingthedatacollected.例如：CREATEORREPLACEPROCEDUREmy_profiler(p_comment1INVARCHAR2,p_comment2INVARCHAR2)ISv_return_codeNUMBER;BEGIN--starttheprofilerv_return_code:=DBMS__PROFILER(p_comment1,p_comment2);dbms__line('ResultfromSTART:'||v_return_code);--aise_list_price(102099,.025);--flushthecollecteddatatothedictionarytablesv_return_code:=DBMS__DATA;dbms__line('ResultfromFLUSH:'||v_return_code);--stopprofilingv_return_code:=DBMS__PROFILER;dbms__line('ResultfromSTOP:'||v_return_code);END;/查询plsql_profiler_runs表数据可以获知执行情况。SELECTrunid,run_owner,run_date,run_comment,run_comment1,run_total_timeFROMplsql_profiler_runs;SELECTrunid,unit_number,unit_type,unit_owner,unit_nameFROMplsql_profiler_unitsinnerJOINplsql_profiler_runsUSING(runid);SELECTline#,total_occur,total_time,min_time,max_time
FROMplsql_profiler_dataWHERErunid=1ANDunit_number=2;9、常用的ORACLE包YoucannotuseDDLcommandsinaPL/_sessi_sessionSubprograms•close_database_link•is_role_enabled•reset_package•set_sql_trace•unique_session_id•set_close_cached_open_cursors•free_unused_user_memory•is_session_alive•set_nls•set_role•set_contextdbms_lockPrograms•allocate_unique•request•convert•release•sleeputl_smtp•Functions:–helo–mail–rcpt–data–open_data–write_data–close_data–rset–quitRecordTypes:–connection–reply–replies•
10、UsingCollectionMethods•EXISTS•COUNT•LIMIT•FIRSTandLAST•PRIORandNEXT•EXTEND•TRIM•DELETE
版权声明：本文标题：PLSQL开发总结 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.freenas.com.cn/free/1705321027h480937.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论