您好,欢迎访问三七文档
当前位置:首页 > IT计算机/网络 > 数据库 > Oracle使用总结
一、常用功能代码1.1、游标----oracle游标练习createorreplaceprocedureget_count_xxyisavarchar2(20);cursorc_wap_user_enter_logisselectv_namefromxxy_test;beginopenc_wap_user_enter_log;loopfetchc_wap_user_enter_logintoa;ifc_wap_user_enter_log%notfoundthenexit;endif;dbms_output.put_line(a);endloop;closec_wap_user_enter_log;endget_count_xxy;1.2、数字格式化selectto_char(10000000000,'999,999,999,999')fromdual;结果10,000,000,000selectto_char(10000000000.1234,'9999999999999999D00')fromdual;结果10000000000.12selectto_char(0,'99999999999999990.00')afromdual结果0.00to_char(expression[,'format'][,nls_format])to_char(1.234,'9999.9')willreturn'1.2'to_char(1000.25,'9,999.99')willreturn'1,000.25'to_char(1000.25,'$9,999.00')willreturn'$1,000.25'to_char(25,'000099')willreturn'000025'to_char(-50,'PR999')willreturn'50'to_char(17,'RN99')willreturn'XVII'to_char(timestamp,'DD-MM-YYYYHH24:MI')willreturn31-12-200523.30to_date(char[,'format'[,nls_lang)to_date('29-Oct-05'),'DD-Mon-YYHH:MI:SS')to_date('Oct/29/05'),'Mon/DD/YYHH:MI:SS')to_date('October.29.2005'),'Month.DD.YYYYHH:MI:SS')1.3、使用包返回结果集OracleREFCURSORWiththeREF_CURSORyoucanreturnarecordset/cursorfromastoredprocedure.Thereare2basictypes:StrongrefcursorandweakrefcursorForthestrongrefcursorthereturningcolumnswithdatatypeandlengthneedtobeknownatcompiletime.Fortheweakrefcursorthestructuredoesnotneedtobeknownatcompiletime.Thestrongref_cursoranduntilOracle9ialsotheweak-typeneedtobedeclaredinapackagestructurelikthis:createorreplacepackageREFCURSOR_PKGasTYPEWEAK8i_REF_CURSORISREFCURSOR;TYPESTRONGREF_CURSORISREFCURSORRETURNEMP%ROWTYPE;endREFCURSOR_PKG;Thepl/sqlprocedurethatreturnsaref-cursorlookslikethis:/**untilOracle9*/createorreplaceproceduretest(p_deptnoINnumber,p_cursorOUTREFCURSOR_PKG.WEAK8i_REF_CURSOR)isbeginopenp_cursorFORselect*fromempwheredeptno=p_deptno;endtest;SinceOracle9iyoucanuseSYS_REFCURSORasthetypeforthereturningREF_CURSOR./**FromOracle9*/createorreplaceproceduretest(p_deptnoINnumber,p_cursorOUTSYS_REFCURSOR)isbeginopenp_cursorFORselect*fromempwheredeptno=p_deptno;endtest;/*Strongtype*/createorreplaceproceduretest(p_deptnoINnumber,p_cursorOUTREFCURSOR_PKG.STRONGREF_CURSOR)isbeginopenp_cursorFORselect*fromempwheredeptno=p_deptno;endtest;-----------------java中调用Selectingtheref_cursorfromJDBCTogetthecursorfromJavayoucanusethefollowingJDBC-code:publicvoidmethod()throwsSQLException{Connectionconn=getConnection();CallableStatementcstmt=null;ResultSetrs=null;intdeptno=10;Objecttemp;try{cstmt=conn.prepareCall(begintest(?,?);end;);cstmt.setInt(1,deptno);cstmt.registerOutParameter(2,OracleTypes.CURSOR);cstmt.execute();rs=(ResultSet)cstmt.getObject(2);ResultSetMetaDatarsm=rs.getMetaData();intcolumnCount=rsm.getColumnCount();while(rs.next()){for(intj=0;jcolumnCount;j++){temp=rs.getObject(j+1);}}}finally{if(!rs==null){rs.close();}if(!stmt==null){stmt.close();}if(!conn==null){conn.close();}}}Callingref-cursorfrompl/sqlcreateorreplaceproceduretest_callisc_cursorREFCURSOR_PKG.STRONGREF_CURSOR;r_empc_emp%rowtype;begintest(10,c_cursor);loopfetchc_cursorintor_emp;exitwhenc_cursor%notfound;dbms_output.put_line(r_emp.name);endloop;closec_cursor;endtest_call;-------------------练习使用包的例子-----1、定义包头createorreplacepackageREFCURSOR_PKGis--Author:ADMINISTRATOR--Created:2006-10-1216:38:08--Purpose:测试结果集--PublictypedeclarationsTYPEWEAK8i_REF_CURSORISREFCURSOR;--TYPEREF_CURSORISREFCURSORRETURNEMP%ROWTYPE;--Publicfunctionandproceduredeclarationsproceduretest(p_cursorOUTREFCURSOR_PKG.WEAK8i_REF_CURSOR);endREFCURSOR_PKG;-----2、定义包体CREATEORREPLACEPACKAGEBODYREFCURSOR_PKGISproceduretest(p_cursorOUTREFCURSOR_PKG.WEAK8i_REF_CURSOR)isbeginopenp_cursorFORselectv_id,v_namefromxxy_test;endtest;endREFCURSOR_PKG;-----3、用PL/SQL块进行测试:declarev_testrefcursor_pkg.WEAK8i_REF_CURSOR;v_IDxxy_test.v_id%TYPE;v_namexxy_test.v_name%TYPE;beginrefcursor_pkg.test(v_test);loopFETCHv_testINTOv_ID,v_name;ifv_test%notfoundthenexit;endif;DBMS_OUTPUT.PUT_LINE(v_ID);DBMS_OUTPUT.PUT_LINE(v_name);endloop;end;--------------------练习使用包的全过程-----1、定义包头CREATEORREPLACEPACKAGEPKG_TESTAS/*定义REFCURSOR类型不加RETURN类型,为弱类型,允许动态SQL查询,否则为强类型,无法使用动态SQL查询;*/TYPEMYRCTYPEISREFCURSOR;--函数申明FUNCTIONGET(INTIDNUMBER)RETURNMYRCTYPE;ENDPKG_TEST;-----2、定义包体CREATEORREPLACEPACKAGEBODYPKG_TESTAS--函数体FUNCTIONGET(INTIDINNUMBER)RETURNMYRCTYPEISRCMYRCTYPE;--定义REFCURSOR变量SQLSTRVARCHAR2(500);BEGINIFINTID=0THEN--静态测试,直接用SELECT语句直接返回结果OPENRCFORSELECTID,姓名FROM人员表;ELSE--动态SQL赋值,用:W_ID来申明该变量从外部获得SQLSTR:='SELECTID,姓名FROM人员表WHEREID=:W_ID';--动态测试,用SQLSTR字符串返回结果,用USING关键词传递参数OPENRCFORSQLSTRUSINGINTID;ENDIF;RETURNRC;ENDGET;ENDPKG_TEST;-----3、用PL/SQL块进行测试:DECLAREW_RCPKG_TEST.MYRCTYPE;--定义REFCURSOR型变量--定义临时变量,用于显示结果W_ID人员表.ID%TYPE;W_NAME人员表.姓名%TYPE;BEGIN--调用函数,获得记录集W_RC:=PKG_TEST.GET(133);--FETCH结果并显示FETCHW_RCINTOW_ID,W_NAME;DBMS_OUTPUT.PUT_LINE(W_NAME);END;1.4、使用rownum返回任意按一定方式排序的序列selectrownum,month,sellfrom(selectmonth,sellfromsalegroupbymonth,sell)whererownum13;ROWNUMMONTHSELL------------------------120000110002200002110032000031200420000413005200005140062000061500720000716008200008100092001011100102002
本文标题:Oracle使用总结
链接地址:https://www.777doc.com/doc-5099184 .html