条件分支结构、循环控制结构和存储过程

条件分支结构、循环控制结构和存储过程--分页存储过程oracle条件分支结构案例1、编写一个过程，可以输入一个雇员姓名，如果该雇员的工资低于2000，就给该雇员工资增加10%。/*结构：IF条件THEN执行语句；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('未找到相关信息.')；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('未找到相关信息.')；ENDupdate_myemp_sal_prc;/3、编写一个过程，可以输入一个雇员姓名，如果该雇员的奖金不是0,就在原来的基础上增加100,如果奖金是0,就把该奖金设置为200；/*结构：IF条件THEN执行语句；ELSE执行语句；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，就在原来的基础上增加100，如果奖金是0，就把该奖金设置为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;/*结构：IF条件THEN执行语句；ELSIF条件THEN执行语句；ELSE执行语句；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;/扩展：如果是对所有雇员的工资进行修改，应该使用游标，请参考第2或第4题。循环语句1、循环语句LOOP案例，LOOP循环语句至少会被执行一次；/*结构：LOOP循环体；退出循环条件；ENDLOOP;LOOP循环至少会被执行一次*/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、循环语句while案例/*结构：while循环条件LOOP循环体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、循环语句for案例/*结构：for变量INnum1..num2LOOP循环体ENDLOOP;说明：num1和num2必须是大于0的整数，且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、循环语句GOTO案例一建议尽量不要使用/*结构：LOOPIF条件THENGOTO标识；ENDIF;改变条件的语句；其它执行语句；ENDLOOP;<<标识>>*/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('循环结束');END;/顺序控制语句1、顺序控制语句一NULL案例；/*说明：NULL语句不会执行任何操作，使用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、现有一张表books，表结构如下：--表booksDROPTABLEbooksPURGE;CREATETABLEbooks(book_idNUMBER(4),book_nameVARCHAR2(50),pressVARCHAR2(50),CONSTRAINTPK_bookIDPRIMARYKEY(book_id));请编写一个存储过程，可以向表books添加书，要求通过java程序调用该过程。存储过程：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;/输入内容过滤packageorg.lxh.addbook;importjava.io.BufferedReader;importjava.io.IOException;importjava.io.InputStreamReader;publicclassInputData{privateBufferedReaderbuf=null;publicInputData(){//将字节输入流转换为字符流存放在缓冲区中this.buf=newBufferedReader(newInputStreamReader(System.in));}publicStringgetString(Stringinfo){//读取输入的数据Stringstr=null;System.out.print(info);//打印提示输入的信息try{str=this.buf.readLine();//按行读取}catch(IOExceptione){System.out.println("读取数据失败！")；}returnstr;}//将判断输入的数据是否为整数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;}}调用过程函数，向数据表books中增加数据packageorg.lxh.addbook;importjava.sql.CallableStatement;importjava.sql.Connection;importjava.sql.DriverManager;publicclassAddBook{//加载驱动程序(之前在项目属性中配置的jdbc的驱动程序的jar包中)publicstaticfinalStringDBDRIVER="oracle.jdbc.driver.OracleDriver";//连接地址是由各个数据库生产商单独提供的，所以需要单独记住publicstaticfinalStringDBURL="jdbc:oracle:thin:@2:1521:mldn";//连接数据库的用户名publicstaticfinalStringDBUSER="scott";//连接数据库的用户名的密码publicstaticfinalStringDBPWD="tiger";publicstaticvoidmain(String[]args)throwsException{//加载oracle驱动Class.forName(DBDRIVER);//得到连接Connectioncon=DriverManager.getConnection(DBURL,DBUSER,DBPWD);//调用存储过程CallableStatementcs=con.prepareCall("{calladd_book_prc(?,?,?)}”);InputDatainput=newInputData();intbookID=input.getInt("请输入书本编号："，”书本编号必须是4位整数,”)；StringbookName=input.getString("请输入书本名称：”)；Stringpress=input.getString("请输入出版社：”)；//设置输入参数的值cs.setInt(1,bookID);cs.setString(2,bookName);cs.setString(3,press);//执行SQL操作cs.execute();//关闭数据库资源cs.close();con.close();}}有返回值的存储过程：1、案例：输入雇员的编号，返回该雇员的姓名一有输入输出的存储过程--有输入和输出的存储过程CREATEORREPLACEPROCEDUREget_name_prc(p_empnoININTEGER,p_nameOUTVARCHAR2)ISBEGINSELECTenameINTOp_nameFROMempWHEREempno=p_empno;ENDget_name_prc;调用回值的存储过程方法如下：--执行有输入和输出的存储过程DECLAREn_nameemp.ename%Type;BEGINget_name_prc(&empno,n_name);DBMS_OUTPUT.put_line(n_name);END;Java程序调用有输入和输出的存储过程输入内容过滤，同上。packageorg.lxh.getname;importjava.sql.CallableStatement;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.Types;importorg.lxh.addbook.InputData;publicclassGetName{//加载驱动程序(之前在项目属性中配置的jdbc的驱动程序的jar包中)publicstaticfinalStringDBDRIVER="oracle.jdbc.driver.OracleDriver";//连接地址是由各个数据库生产商单独提供的，所以需要单独记住publicstaticfinalStringDBURL="jdbc:oracle:thin:@2:1521:mldn";//连接数据库的用户名publicstaticfinalStringDBUSER="scott";//连接数据库的用户名的密码publicstaticfinalStringDBPWD="tiger";publicstaticvoidmain(String[]args)throwsException{//加载oracle驱动Class.forName(DBDRIVER);//得到连接Connectioncon=DriverManager.getConnection(DBURL,DBUSER,DBPWD);//调用存储过程CallableStatementcs=con.prepareCall("{callget_name_prc(?,?)}");cs.registerOutParameter(2,Types.VARCHAR);InputDatainput=newInputData();intempno=input.getInt("请输入雇员编号："，"雇员编号必须是4位整数,")；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程序输入内容过滤同上packageorg.lxh.getinfo;importjava.sql.CallableStatement;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.Types;importorg.lxh.addbook.InputData;publicclassGetInfo{//加载驱动程序(之前在项目属性中配置的jdbc的驱动程序的jar包中)publicstaticfinalStringDBDRIVER="oracle.jdbc.driver.OracleDriver";//连接地址是由各个数据库生产商单独提供的，所以需要单独记住publicstaticfinalStringDBURL="jdbc:oracle:thin:@2:1521:mldn";//连接数据库的用户名publicstaticfinalStringDBUSER="scott";//连接数据库的用户名的密码publicstaticfinalStringDBPWD="tiger";publicstaticvoidmain(String[]args)throwsException{//加载oracle驱动Class.forName(DBDRIVER);//得到连接Connectioncon=DriverManager.getConnection(DBURL,DBUSER,DBPWD);//调用存储过程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位整数,")；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+"职业："+job+”，工资：”+sal);}}有返回值的存储过程(列表［结果集］)由于oracle存储过程返回值是由OUT指定的参数代替，只能返回单值的内容。而对于列表、集合，不能用一般的参数，必须要使用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程序输入内容过滤packageorg.lxh.testcursor;importjava.io.BufferedReader;importjava.io.IOException;importjava.io.InputStreamReader;publicclassInputData{privateBufferedReaderbuf=null;publicInputData(){//将字节输入流转换为字符流存放在缓冲区中this.buf=newBufferedReader(newInputStreamReader(System.in));}publicStringgetString(Stringinfo){//读取输入的数据Stringstr=null;System.out.print(info);//打印提示输入的信息try{str=this.buf.readLine();//按行读取}catch(IOExceptione){System.out.println("读取数据失败！")；}returnstr;}//将判断输入的数据是否为整数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;}}通过设置存储过程的输出参数为OracleTypes.CURSOR类型，获取结果集packageorg.lxh.testcursor;importjava.sql.CallableStatement;importjava.sql.Connection;importjava.sql.DriverManager;importjava.sql.ResultSet;importoracle.jdbc.OracleTypes;publicclassTestCursor{//加载驱动程序(之前在项目属性中配置的jdbc的驱动程序的jar包中)publicstaticfinalStringDBDRIVER="oracle.jdbc.driver.OracleDriver";//连接地址是由各个数据库生产商单独提供的，所以需要单独记住publicstaticfinalStringDBURL="jdbc:oracle:thin:@2:1521:mldn";//连接数据库的用户名publicstaticfinalStringDBUSER="scott";//连接数据库的用户名的密码publicstaticfinalStringDBPWD="tiger";publicstaticvoidmain(String[]args)throwsException{//加载oracle驱动Class.forName(DBDRIVER);//得到连接Connectioncon=DriverManager.getConnection(DBURL,DBUSER,DBPWD);//调用存储过程CallableStatementcs=con.prepareCall("{calltest_prc(?,?)}”);//设置存储过程参数的类型cs.registerOutParameter(2,OracleTypes.CURSOR);//设置输入参数的值InputDatainput=newInputData();intdeptno=input.getInt("请输入部门编号：”，”部门编号必须是2位整数,”)；cs.setInt(1,deptno);//执行SQL操作cs.execute();//取得结果集ResultSetrs=(ResultSet)cs.getObject(2);//输出结果集的部分内容while(rs.next()){System.out.println("雇员编号："+rs.getInt(1)+”，姓名：”+rs.getString(2)+"，职业：”+rs.getString("JOB"));}//关闭数据库资源rs.close();cs.close();con.close();}}分页存储过程请编写一个存储过程，要求可以输入表名、每页显示记录数、当前页。返回总记录数，总页数、和返回的结果集。■分页存储过程/*输入参数：p_table_name:表名p_records_per_page:每页显示的记录p_row_amount:表中总的记录数p_page_amount:表示总页数c_test：REFCURSOR游标变量，noreturntypec_test属于以下的游标类型CREATEORREPLACEPACKAGEtest_pkgISTYPEtest_curtypeISREFCURSOR;ENDtest_pkg;v_sql:专门处理的SQL语句i_current_page_first_record:表示当前页的第一条记录EXECUTEIMMEDIATE'SQL语句'[INTO变量]:表示执行SQL语句，可以把查询结果存在变量中，该变量只能存储单行或单个数据，不能存储多行数据，如果确实要存储多行数据，请使用显示游标或REF游标。*/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;--取得总记录数EXECUTEIMMEDIATEv_sqlINTOp_row_amount;--取得总页数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