條件分支結(jié)構(gòu)、循環(huán)控制結(jié)構(gòu)和存儲過程

條件分支結(jié)構(gòu)、循環(huán)控制結(jié)構(gòu)和存儲過程--分頁存儲過程oracle條件分支結(jié)構(gòu)案例1、編寫一個過程，可以輸入一個雇員姓名，如果該雇員的工資低于2000，就給該雇員工資增加10%。/*結(jié)構(gòu)：IF條件THEN執(zhí)行語句；ENDIF;*/CREATEORREPLACEPROCEDUREadd_sal_prc(p_nameINVARCHAR2)ISn_salmyemp.sal%TYPE;BEGINSELECTsalINTOn_salFROMmyempWHEREename=p_name;IFn_sal<2000THENUPDATEmyempSETsal=sal*(1+0.1)WHEREename=p_name;ENDIF;EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.put_line('未找到相關(guān)信息.')；ENDadd_sal_prc;/2、編寫一個過程，在myemp表中，如果某個雇員的工資低于2000，就給該雇員工資增加10%。CREATEORREPLACEPROCEDUREupdate_myemp_sal_prcISCURSORc_testISSELECT*FROMmyempFORUPDATE;BEGINFORvar_dataINc_testLOOPIFvar_data.sal<2000THENUPDATEmyempSETsal=sal*1.1WHERECURRENTOFc_test;ENDIF;ENDLOOP;EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.put_line('未找到相關(guān)信息.')；ENDupdate_myemp_sal_prc;/3、編寫一個過程，可以輸入一個雇員姓名，如果該雇員的獎金不是0,就在原來的基礎(chǔ)上增加100,如果獎金是0,就把該獎金設(shè)置為200；/*結(jié)構(gòu)：IF條件THEN執(zhí)行語句；ELSE執(zhí)行語句；ENDIF;*/CREATEORREPLACEPROCEDUREupdate_myemp_user_comm_prc(p_nameINVARCHAR2)IScomm_m%TYPE;BEGINSELECTNVL(comm,0)commINTOcomm_tmpFROMmyempWHEREename=p_name;IFcomm_tmp!=0THENUPDATEmyempSETcomm=comm+100WHEREename=p_name;ELSEUPDATEmyempSETcomm=200WHEREename=p_name;ENDIF;ENDupdate_myemp_user_comm_prc;/4、編寫一個過程，在myemp表中，如果該雇員的獎金不是0，就在原來的基礎(chǔ)上增加100，如果獎金是0，就把該獎金設(shè)置為200；CREATEORREPLACEPROCEDUREupdate_myemp_comm_prcISCURSORc_testISSELECTename,sal,NVL(comm,0)commFROMmyempFORUPDATE;BEGINFORvar_dataINc_testLOOPIFvar_m!=0THENUPDATEmyempSETcomm=comm+100WHERECURRENTOFc_test;ELSEUPDATEmyempSETcomm=200WHERECURRENTOFc_test;ENDIF;ENDLOOP;ENDupdate_myemp_comm_prc;/5、編寫一個過程，可以輸入一個雇員編號，如果該雇員的職位是PRESIDENT就給他的工資增加1000，如果該雇員的職位是MANAGER就給他的工資的增加500,其它職位的雇員的工資增加200;/*結(jié)構(gòu)：IF條件THEN執(zhí)行語句；ELSIF條件THEN執(zhí)行語句；ELSE執(zhí)行語句；ENDIF;*/CREATEORREPLACEPROCEDUREmodify_sal_prc(p_empnoININTEGER)ISv_jobmyemp.job%TYPE;BEGINSELECTjobINTOv_jobFROMmyempWHEREempno=p_empno;IFv_job='PRESIDENT'THENUPDATEmyempSETsal=sal+1000WHEREempno=p_empno;ELSIFv_job='MANAGER'THENUPDATEmyempSETsal=sal+500WHEREempno=p_empno;ELSEUPDATEmyempSETsal=sal+200WHEREempno=p_empno;ENDIF;ENDmodify_sal_prc;/擴展：如果是對所有雇員的工資進(jìn)行修改，應(yīng)該使用游標(biāo)，請參考第2或第4題。循環(huán)語句1、循環(huán)語句LOOP案例，LOOP循環(huán)語句至少會被執(zhí)行一次；/*結(jié)構(gòu)：LOOP循環(huán)體；退出循環(huán)條件；ENDLOOP;LOOP循環(huán)至少會被執(zhí)行一次*/CREATEORREPLACEPROCEDUREloop_demo_prc(p_nameINVARCHAR2)ISi_idINTEGER:=1;BEGINLOOPINSERTINTOtb_loginVALUES(i_id,p_name);i_id:=i_id+1;EXITWHENi_id=11;ENDLOOP;ENDloop_demo_prc;2、循環(huán)語句while案例/*結(jié)構(gòu)：while循環(huán)條件LOOP循環(huán)體ENDLOOP;*/CREATEORREPLACEPROCEDUREwhile_demo_prc(p_nameINVARCHAR2)ISi_idINTEGER:=11;BEGINwhilei_id<21LOOPINSERTINTOtb_loginVALUES(i_id,p_name);i_id:=i_id+1;ENDLOOP;ENDwhile_demo_prc;3、循環(huán)語句for案例/*結(jié)構(gòu)：for變量INnum1..num2LOOP循環(huán)體ENDLOOP;說明：num1和num2必須是大于0的整數(shù)，且num2>num1*/CREATEORREPLACEPROCEDUREfor_demo_prc(p_nameINVARCHAR2)ISi_idINTEGER:=21;BEGINFORiIN21..31LOOPINSERTINTOtb_loginVALUES(i_id,p_name);i_id:=i_id+1;ENDLOOP;ENDfor_demo_prc;/4、循環(huán)語句GOTO案例一建議盡量不要使用/*結(jié)構(gòu)：LOOPIF條件THENGOTO標(biāo)識；ENDIF;改變條件的語句；其它執(zhí)行語句；ENDLOOP;<<標(biāo)識>>*/DECLAREi_numINT:=1;BEGINLOOPIFi_num>10THENGOTOflg;ENDIF;DBMS_OUTPUT.put_line('i_num='||i_num);i_num:=i_num+1;ENDLOOP;<<flg>>DBMS_OUTPUT.put_line('循環(huán)結(jié)束');END;/順序控制語句1、順序控制語句一NULL案例；/*說明：NULL語句不會執(zhí)行任何操作，使用NULL語句的好處主要是提高PL/SQL的可讀性*/DECLAREv_namemyemp.ename%TYPE;n_salmyemp.sal%TYPE;BEGINSELECTename,salINTOv_name,n_salFROMmyempWHEREempno=&empno;IFn_sal<3000THENUPDATEmyempSETcomm=sal*0.1WHEREename=v_name;ELSENULL;ENDIF;END;/分頁儲存過程無返回值的存儲過程：1、現(xiàn)有一張表books，表結(jié)構(gòu)如下：--表booksDROPTABLEbooksPURGE;CREATETABLEbooks(book_idNUMBER(4),book_nameVARCHAR2(50),pressVARCHAR2(50),CONSTRAINTPK_bookIDPRIMARYKEY(book_id));請編寫一個存儲過程，可以向表books添加書，要求通過java程序調(diào)用該過程。存儲過程：add_book_prc()--存儲過程add_book_prcDROPPROCEDUREadd_book_prc;CREATEPROCEDUREadd_book_prc(p_bookIDINNUMBER,p_bookNameINVARCHAR2,p_pressINVARCHAR2)ISBEGININSERTINTObooksVALUES(p_bookID,p_bookName,p_press);ENDadd_book_prc;/輸入內(nèi)容過濾packageorg.lxh.addbook;importjava.io.BufferedReader;importjava.io.IOException;importjava.io.InputStreamReader;publicclassInputData{privateBufferedReaderbuf=null;publicInputData(){//將字節(jié)輸入流轉(zhuǎn)換為字符流存放在緩沖區(qū)中this.buf=newBufferedReader(newInputStreamReader(System.in));}publicStringgetString(Stringinfo){//讀取輸入的數(shù)據(jù)Stringstr=null;System.out.print(info);//打印提示輸入的信息try{str=this.buf.readLine();//按行讀取}catch(IOExceptione){System.out.println("讀取數(shù)據(jù)失敗！")；}returnstr;}//將判斷輸入的數(shù)據(jù)是否為整數(shù)publicintgetInt(Stringinfo,Stringerr){booleanflag=true;inttemp=0;while(flag){Stringstr=this.getString(info);if(str.matches("}")){flag=false;temp=Integer.parseInt(str);}else{System.out.print(err);}}returntemp;}}調(diào)用過程函數(shù)，向數(shù)據(jù)表books中增加數(shù)據(jù)packageorg.lxh.addbook;importjava.sql.CallableStatement;importjava.sql.Connection;importjava.sql.DriverManager;publicclassAddBook{//加載驅(qū)動程序(之前在項目屬性中配置的jdbc的驅(qū)動程序的jar包中)publicstaticfinalStringDBDRIVER="oracle.jdbc.driver.OracleDriver";//連接地址是由各個數(shù)據(jù)庫生產(chǎn)商單獨提供的，所以需要單獨記住publicstaticfinalStringDBURL="jdbc:oracle:thin:@2:1521:mldn";//連接數(shù)據(jù)庫的用戶名publicstaticfinalStringDBUSER="scott";//連接數(shù)據(jù)庫的用戶名的密碼publicstaticfinalStringDBPWD="tiger";publicstaticvoidmain(String[]args)throwsException{//加載oracle驅(qū)動Class.forName(DBDRIVER);//得到連接Connectioncon=DriverManager.getConnection(DBURL,DBUSER,DBPWD);//調(diào)用存儲過程CallableStatementcs=con.prepareCall("{calladd_book_prc(?,?,?)}”);InputDatainput=newInputData();intbookID=input.getInt("請輸入書本編號："，”書本編號必須是4位整數(shù),”)；StringbookName=input.getString("請輸入書本名稱：”)；Stringpress=input.getString("請輸入出版社：”)；//設(shè)置輸入?yún)?shù)的值cs.setInt(1,bookID);cs.setString(2,bookName);cs.setString(3,press);//執(zhí)行SQL操作cs.execute();//關(guān)閉數(shù)據(jù)庫資源cs.close();con.close();}}有返回值的存儲過程：1、案例：輸入雇員的編號，返回該雇員的姓名一有輸入輸出的存儲過程--有輸入和輸出的存儲過程CREATEORREPLACEPROCEDUREget_name_prc(p_empnoININTEGER,p_nameOUTVARCHAR2)ISBEGINSELECTenameINTOp_nameFROMempWHEREempno=p_empno;ENDget_name_prc;調(diào)用回值的存儲過程方法如下：--執(zhí)行有輸入和輸出的存儲過程DECLAREn_nameemp.ename%Type;BEGINget_name_prc(&empno,n_name);DBMS_OUTPUT.put_line(n_name);END;Java程序調(diào)用有輸入和輸出的存儲過程輸入內(nèi)容過濾，同上。packageorg.lxh.getname;importjava.sql.CallableStatement;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.Types;importorg.lxh.addbook.InputData;publicclassGetName{//加載驅(qū)動程序(之前在項目屬性中配置的jdbc的驅(qū)動程序的jar包中)publicstaticfinalStringDBDRIVER="oracle.jdbc.driver.OracleDriver";//連接地址是由各個數(shù)據(jù)庫生產(chǎn)商單獨提供的，所以需要單獨記住publicstaticfinalStringDBURL="jdbc:oracle:thin:@2:1521:mldn";//連接數(shù)據(jù)庫的用戶名publicstaticfinalStringDBUSER="scott";//連接數(shù)據(jù)庫的用戶名的密碼publicstaticfinalStringDBPWD="tiger";publicstaticvoidmain(String[]args)throwsException{//加載oracle驅(qū)動Class.forName(DBDRIVER);//得到連接Connectioncon=DriverManager.getConnection(DBURL,DBUSER,DBPWD);//調(diào)用存儲過程CallableStatementcs=con.prepareCall("{callget_name_prc(?,?)}");cs.registerOutParameter(2,Types.VARCHAR);InputDatainput=newInputData();intempno=input.getInt("請輸入雇員編號："，"雇員編號必須是4位整數(shù),")；cs.setInt(1,empno);cs.execute();Stringstr=cs.getString(2);cs.close();con.close();System.out.println("雇員編號"+empno+"的姓名是："+str);}}存儲過程返回多個值2、編寫一個過程，可以輸入雇員的編號，返回該雇員的姓名、工資和職位。CREATEORREPLACEPROCEDUREget_info_prc(p_empnoININTEGER,p_nameOUTVARCHAR2,p_salOUTNUMBER,p_jobOUTVARCHAR2)ISBEGINSELECTename,job,salINTOp_name,p_job,p_salFROMempWHEREempno=p_empno;ENDget_info_prc;Java程序輸入內(nèi)容過濾同上packageorg.lxh.getinfo;importjava.sql.CallableStatement;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.Types;importorg.lxh.addbook.InputData;publicclassGetInfo{//加載驅(qū)動程序(之前在項目屬性中配置的jdbc的驅(qū)動程序的jar包中)publicstaticfinalStringDBDRIVER="oracle.jdbc.driver.OracleDriver";//連接地址是由各個數(shù)據(jù)庫生產(chǎn)商單獨提供的，所以需要單獨記住publicstaticfinalStringDBURL="jdbc:oracle:thin:@2:1521:mldn";//連接數(shù)據(jù)庫的用戶名publicstaticfinalStringDBUSER="scott";//連接數(shù)據(jù)庫的用戶名的密碼publicstaticfinalStringDBPWD="tiger";publicstaticvoidmain(String[]args)throwsException{//加載oracle驅(qū)動Class.forName(DBDRIVER);//得到連接Connectioncon=DriverManager.getConnection(DBURL,DBUSER,DBPWD);//調(diào)用存儲過程CallableStatementcs=con.prepareCall("{callget_info_prc(?,?,?,?)}");cs.registerOutParameter(2,Types.VARCHAR);cs.registerOutParameter(3,Types.NUMERIC);cs.registerOutParameter(4,Types.VARCHAR);InputDatainput=newInputData();intempno=input.getInt("請輸入雇員編號："，"雇員編號必須是4位整數(shù),")；cs.setInt(1,empno);cs.execute();Stringname=cs.getString(2);doublesal=cs.getDouble(3);Stringjob=cs.getString(4);cs.close();con.close();System.out.println("雇員編號"+empno+"的姓名是："+name+"職業(yè)："+job+”，工資：”+sal);}}有返回值的存儲過程(列表［結(jié)果集］)由于oracle存儲過程返回值是由OUT指定的參數(shù)代替，只能返回單值的內(nèi)容。而對于列表、集合，不能用一般的參數(shù)，必須要使用package包。3、案例：編寫一個存儲過程，輸入部門編號，返回該部門所有雇員信息。建立包test_pkgCREATEORREPLACEPACKAGEtest_pkgISTYPEtest_curtypeISREFCURSOR;ENDtest_pkg;存儲過程：test_prcCREATEORREPLACEPROCEDUREtest_prc(p_deptnoININTEGER,c_testOUTtest_pkg.test_curtype)ISBEGINOPENc_testFORSELECT*FROMempWHEREdeptno=p_deptno;ENDtest_prc;用oracle塊測試集合DECLAREc_testprctest_pkg.test_curtype;v_dataemp%ROWTYPE;BEGINtest_prc(&deptno,c_testprc);LOOPFETCHc_testprcINTOv_data;EXITWHENc_testprc%NOTFOUND;DBMS_OUTPUT.put_line(v_data.empno||'/'||v_data.ename);ENDLOOP;CLOSEc_testprc;END;Java程序輸入內(nèi)容過濾packageorg.lxh.testcursor;importjava.io.BufferedReader;importjava.io.IOException;importjava.io.InputStreamReader;publicclassInputData{privateBufferedReaderbuf=null;publicInputData(){//將字節(jié)輸入流轉(zhuǎn)換為字符流存放在緩沖區(qū)中this.buf=newBufferedReader(newInputStreamReader(System.in));}publicStringgetString(Stringinfo){//讀取輸入的數(shù)據(jù)Stringstr=null;System.out.print(info);//打印提示輸入的信息try{str=this.buf.readLine();//按行讀取}catch(IOExceptione){System.out.println("讀取數(shù)據(jù)失??！")；}returnstr;}//將判斷輸入的數(shù)據(jù)是否為整數(shù)publicintgetInt(Stringinfo,Stringerr){booleanflag=true;inttemp=0;while(flag){Stringstr=this.getString(info);if(str.matches("}")){flag=false;temp=Integer.parseInt(str);}else{System.out.print(err);}}returntemp;}}通過設(shè)置存儲過程的輸出參數(shù)為OracleTypes.CURSOR類型，獲取結(jié)果集packageorg.lxh.testcursor;importjava.sql.CallableStatement;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.ResultSet;importoracle.jdbc.OracleTypes;publicclassTestCursor{//加載驅(qū)動程序(之前在項目屬性中配置的jdbc的驅(qū)動程序的jar包中)publicstaticfinalStringDBDRIVER="oracle.jdbc.driver.OracleDriver";//連接地址是由各個數(shù)據(jù)庫生產(chǎn)商單獨提供的，所以需要單獨記住publicstaticfinalStringDBURL="jdbc:oracle:thin:@2:1521:mldn";//連接數(shù)據(jù)庫的用戶名publicstaticfinalStringDBUSER="scott";//連接數(shù)據(jù)庫的用戶名的密碼publicstaticfinalStringDBPWD="tiger";publicstaticvoidmain(String[]args)throwsException{//加載oracle驅(qū)動Class.forName(DBDRIVER);//得到連接Connectioncon=DriverManager.getConnection(DBURL,DBUSER,DBPWD);//調(diào)用存儲過程CallableStatementcs=con.prepareCall("{calltest_prc(?,?)}”);//設(shè)置存儲過程參數(shù)的類型cs.registerOutParameter(2,OracleTypes.CURSOR);//設(shè)置輸入?yún)?shù)的值InputDatainput=newInputData();intdeptno=input.getInt("請輸入部門編號：”，”部門編號必須是2位整數(shù),”)；cs.setInt(1,deptno);//執(zhí)行SQL操作cs.execute();//取得結(jié)果集ResultSetrs=(ResultSet)cs.getObject(2);//輸出結(jié)果集的部分內(nèi)容while(rs.next()){System.out.println("雇員編號："+rs.getInt(1)+”，姓名：”+rs.getString(2)+"，職業(yè)：”+rs.getString("JOB"));}//關(guān)閉數(shù)據(jù)庫資源rs.close();cs.close();con.close();}}分頁存儲過程請編寫一個存儲過程，要求可以輸入表名、每頁顯示記錄數(shù)、當(dāng)前頁。返回總記錄數(shù)，總頁數(shù)、和返回的結(jié)果集?！龇猪摯鎯^程/*輸入?yún)?shù)：p_table_name:表名p_records_per_page:每頁顯示的記錄p_row_amount:表中總的記錄數(shù)p_page_amount:表示總頁數(shù)c_test：REFCURSOR游標(biāo)變量，noreturntypec_test屬于以下的游標(biāo)類型CREATEORREPLACEPACKAGEtest_pkgISTYPEtest_curtypeISREFCURSOR;ENDtest_pkg;v_sql:專門處理的SQL語句i_current_page_first_record:表示當(dāng)前頁的第一條記錄EXECUTEIMMEDIATE'SQL語句'[INTO變量]:表示執(zhí)行SQL語句，可以把查詢結(jié)果存在變量中，該變量只能存儲單行或單個數(shù)據(jù)，不能存儲多行數(shù)據(jù)，如果確實要存儲多行數(shù)據(jù)，請使用顯示游標(biāo)或REF游標(biāo)。*/CREATEORREPLACEPROCEDUREtest_paging_prc(p_table_nameINVARCHAR2,p_records_per_pageININTEGER,p_current_pageININTEGER,p_row_amountOUTINTEGER,p_page_amountOUTINTEGER,c_testOUTtest_pkg.test_curtype)ISv_sqlVARCHAR2(1000);i_current_page_first_recordINTEGER;BEGINi_current_page_first_record:=(p_current_1)*p_records_per_page+1;v_sql:='SELECTCOUNT(*)FROM'||p_table_name;--取得總記錄數(shù)EXECUTEIMMEDIATEv_sqlINTOp_row_amount;--取得總頁數(shù)IF(MOD(p_row_amount,p_records_per_page)=0)THENp_page_amount:=p_row_amount/p_records_per_page;ELSEp_page_amount:=trunc(p_row_amount/p_records_per_page)+1;ENDIF;IFp_current_page=p_page_amountTHENv_sql:='SELECT*FROM(SELECTROWNUMi_rowid,temp.*FROM(SELECT*FROM'||p_table_name||')tempWHEREROWNUM<='||p_row_amount||')tmpWHEREtmp.i_rowid>='||i_current_page_first_record;OPENc_testFORv_sql;ELSEv_sql:='SELECT*FROM(SELECTROWNUMi_rowid,temp.*FROM(SELECT*FROM'||p_table_name||')tempWHEREROWNUM<='||p_current_page*p_reco