条件分支结构、循环控制结构和存储过程_第1页
条件分支结构、循环控制结构和存储过程_第2页
条件分支结构、循环控制结构和存储过程_第3页
条件分支结构、循环控制结构和存储过程_第4页
条件分支结构、循环控制结构和存储过程_第5页
已阅读5页,还剩1页未读 继续免费阅读

下载本文档

版权说明:本文档由用户提供并上传,收益归属内容提供方,若内容存在侵权,请进行举报或认领

文档简介

条件分支结构、循环控制结构和存储过程--分页存储过程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

温馨提示

  • 1. 本站所有资源如无特殊说明,都需要本地电脑安装OFFICE2007和PDF阅读器。图纸软件为CAD,CAXA,PROE,UG,SolidWorks等.压缩文件请下载最新的WinRAR软件解压。
  • 2. 本站的文档不包含任何第三方提供的附件图纸等,如果需要附件,请联系上传者。文件的所有权益归上传用户所有。
  • 3. 本站RAR压缩包中若带图纸,网页内容里面会有图纸预览,若没有图纸预览就没有图纸。
  • 4. 未经权益所有人同意不得将文件中的内容挪作商业或盈利用途。
  • 5. 人人文库网仅提供信息存储空间,仅对用户上传内容的表现方式做保护处理,对用户上传分享的文档内容本身不做任何修改或编辑,并不能对任何下载内容负责。
  • 6. 下载文件中如有侵权或不适当内容,请与我们联系,我们立即纠正。
  • 7. 本站不保证下载资源的准确性、安全性和完整性, 同时也不承担用户因使用这些下载资源对自己和他人造成任何形式的伤害或损失。

评论

0/150

提交评论