数据库CH--(5)

CHAPTER5AdvancedSQLInthischapterweaddresstheissueofhowtoaccessSQLfromageneral-purposeprogramminglanguage,whichisveryimportantforbuildingapplicationsthatuseadatabasetostoreandretrievedata.Wedescribehowproceduralcodecanbeexecutedwithinthedatabase,eitherbyextendingtheSQLlanguagetosupportproceduralactions,orbyallowingfunctionsdefinedinprocedurallanguagestobeexecutedwithinthedatabase.Wedescribetriggers,whichcanbeusedtospecifyactionsthataretobecarriedoutautomaticallyoncertaineventssuchasinsertion,deletion,orupdateoftuplesinaspecifiedrelation.WediscussrecursivequeriesandadvancedaggregationfeaturessupportedbySQL.Finally,wedescribeonlineanalyticprocessing(OLAP)systems,whichsupportinteractiveanalysisofverylargedatasets.GiventhefactthattheJDBCandODBCprotocols(andvariantssuchasADO.NET)arehavebecometheprimarymeansofaccessingdatabases,wehavesignificantlyextendedourcoverageofthesetwoprotocols,includingsomeex-amples.However,ourcoverageisonlyintroductory,andomitsmanydetailsthatareusefulinpractise.Onlinetutorials/manualsortextbookscoveringtheseprotocolsshouldbeusedassupplements,tohelpstudentsmakefulluseoftheprotocols.Exercises5.12Considerthefollowingrelationsforacompanydatabase:•emp(ename,dname,salary)•mgr(ename,mname)andtheJavacodeinFigure5.26,whichusestheJDBCAPI.Assumethattheuserid,password,machinename,etc.areallokay.DescribeinconciseEnglishwhattheJavaprogramdoes.(Thatis,produceanEnglishsen-tencelike“Itfindsthemanagerofthetoydepartment,”notaline-by-linedescriptionofwhateachJavastatementdoes.)3132Chapter5AdvancedSQLimportjava.sql.*;publicclassMystery{publicstaticvoidmain(String[]args){try{Connectioncon=null;Class.forName(oracle.jdbc.driver.OracleDriver);con=DriverManager.getConnection(jdbc:oracle:thin:star/X@//edgar.cse.lehigh.edu:1521/XE);Statements=con.createStatement();Stringq;StringempName=dog;booleanmore;ResultSetresult;do{q=selectmnamefrommgrwhereename=’+empName+’;result=s.executeQuery(q);more=result.next();if(more){empName=result.getString(mname);System.out.println(empName);}}while(more);s.close();con.close();}catch(Exceptione){e.printStackTrace();}}}Figure5.26JavacodeforExercise5.12.Answer:Itprintsoutthemanagerof“dog.”thatmanager’smanager,etc.untilwereachamanagerwhohasnomanager(presumably,theCEO,whomostcertainlyisacat.)NOTE:ifyoutrytorunthis,useyourOWNOracleIDandpassword,sinceStar,craftycatthatsheis,changesherpassword.5.13SupposeyouwereaskedtodefineaclassMetaDisplayinJava,containingamethodstaticvoidprintTable(Stringr);themethodtakesarelationnamerasinput,executesthequery“select*fromr”,andprintstheresultoutinnicetabularformat,withtheattributenamesdisplayedintheheaderofthetable.a.Whatdoyouneedtoknowaboutrelationrtobeabletoprinttheresultinthespecifiedtabularformat.b.WhatJDBCmethods(s)cangetyoutherequiredinformation?c.WritethemethodprintTable(Stringr)usingtheJDBCAPI.Answer:Exercises33a.Weneedtoknowthenumberofattributesandnamesofattributesofrtodecidethenumberandnamesofcolumnsinthetable.b.WecanusetheJDBCmethodsgetColumnCount()andgetColumn-Name(int)togettherequiredinformation.c.Themethodisshownbelow.staticvoidprintTable(Stringr){try{Class.forName(oraclejdbc.driver.OracleDriver);Connectionconn=DriverManager.getConnection(jdbc:oracle:thin:@db.yale.edu:2000:univdb,user,passwd);Statementstmt=conn.createStatement();ResultSetrs=stmt.ExecuteQuery(r);ResultSetMetaDatarsmd=rs.getMetaData();intcount=rsmd.getColumnCount();System.out.println(tr);for(inti=1;i=count;i++){System.out.println(td+rsmd.getColumnName(i)+/td);}System.out.println(/tr);while(rs.next(){System.out.println(tr);for(inti=1;i=count;i++){System.out.println(td+rs.getString(i)+/td);}System.out.println(/tr);}stmt.close();conn.close();}catch(SQLExceptionsqle){System.out.println(SQLException:+sqle);}}5.14RepeatExercise5.13usingODBC,definingvoidprintTable(char*r)asafunctioninsteadofamethod.Answer:a.SameasforJDBC.34Chapter5AdvancedSQLb.ThefunctionSQLNumResultCols(hstmt,&numColumn)canbeusedtofindthenumberofcolumnsinastatement,whilethefunctionSQLColAttribute()canbeusedtofindthename,typeandotherin-formationaboutanycolumnofaresultset.set,andthenamesc.TheODBCcodeissimilartotheJDBCcode,butsignificantlylonger.ODBCcodethatcarriesoutthistaskmaybefoundonlineattheURL(lookatthebottomofthepage).5.15Consideranemployeedatabasewithtworelationsemployee(employeename,street,city)works(employeename,companyname,salary)wheretheprimarykeysareunderlined.Writeaquerytofindcompanieswhoseemployeesearnahighersalary,onaverage,thantheaveragesalaryat“FirstBankCorporation”.a.UsingSQLfunctionsasappropriate.b.WithoutusingSQLfunctions.Answer:a.createfunctionavgsalary(cnamevarchar(15))returnsintegerdeclareresultinteger;selectavg(salary)intoresultfromworkswhereworks.companyname=cnamereturnresult;endselectcompanynamefromworkswhereavgsalary(companyname)avgsalary(FirstBankCorporation)b.selectcompanynamefromworksgroupbycompanynamehavingavg(salary)(selectavg(salary)fromworkswherecompanyname=FirstBankCorporation)5.16RewritethequeryinSection5.2.1thatreturnsthe