A first course in database systems(8).ppt

8SystemAspectsofSQL,SQLinaprogrammingenvironmentTransactionsinSQLSecurityandUserAuthorization,8.1SQLinaProgrammingEnvironment,Whydoweneedotherprogramminglanguagesindatabaseapplicationsystems?Adatabasejustprovidessharingdatasourceforaspecialenvironment.ManyuserscannotuseSQLtomanipulatedatabaseinclient(客户端)directly.Manyconventionalprogramminglanguagesallowconnectionandmanipulationofdatabase.Java,PowerBuilder,Delphi,C/C+,VB,8.1SQLinaProgrammingEnvironment,Javaprogram1,JDBC,database,JDBC,ODBC,JDBC,Javaclass,WebServer,Browser,DBA,Javaprogram2,Delphiprogram1,8.1SQLinaProgrammingEnvironment,8.1SQLinaProgrammingEnvironment,ThedifferenceofSQLandconventional(传统的)programminglanguage:SQLusestherelationaldatamodel.Conventionalprogramminglanguagesuseadatamodelwithintegers,reals,characters,andsoon.impedancemismatch(匹配失衡)Howtomanipulatedatabasebyconventionalprogramminglanguages?connecttodatabase,buildasession.sendasequenceofSQLstatements,receiveanddealwiththeresult.closethesession.,8.1SQLinaProgrammingEnvironment,TheSQL/HostLanguageInterface：DeclareSharedVariables。EXECSQLBEGINDECLARESECTION;charstudioName50,studioAddr256;charSQLSTATE6;EXECSQLENDDECLARESECTION;UseEmbedSQL。EXECSQLINSERTINTOStudio(name,address)VALUES(:studioName,:studioAddr);UsePrecompilerstatement,EXECSQL。UseSQLSTATE。UseCursor(游标,光标)。,8.1SQLinaProgrammingEnvironment,建立和使用游标的步骤：游标说明DECLARESCROLLCURSORFORSELECT打开游标OPEN推进FETCHNEXT|PRIOR|FIRST|LAST|RELATIVEn|ABSOLUTEnFROMINTO变量名,FETCH_STATUS=0表示正常。更新数据UPDATE|DELETEtableWHERECURRENTOF关闭游标CLOSE,8.2模式中的存储过程,存储过程(storedprocedure)与函数：在数据库中存储的一组SQL指令，有唯一命名，供应用程序调用。过程的优势：标准化；高效率；高安全性。创建过程：,Createprocedure()LocaldeclarationsProcedurebody;,Createfunction()returnLocaldeclarationsFunctionbody;,8.2模式中的存储过程,例如：SQLServer中的存储过程Createprocedurenew_dept(idinteger,dnamechar(40),headchar(10)Asbegininsertintodepartment(deptid,deptname,headerid)values(id,dname,head)end过程中的参数默认是输入型,可以指定OUTPUT表示输入输出型。函数中的参数只可采用输入型。局部变量说明：DECLAREvar扩充的SQL语句Transact-SQL，包括变量定义，条件IFELSE，循环WHILE等流程控制语句。函数中使用RETURN语句返回函数值。,8.2模式中的存储过程,如何调用过程？EXEC参数=值例如：EXECnew_deptid=3,dname=北京代理,head=null或EXECnew_dept3,北京代理,null如何修改过程？Alterprocedurenew_dept如何删除过程？Dropprocedurenew_dept;过程中如何返回计算结果？单个结果值可用OUTPUT参量返回。函数中使用Return子句返回一个或一组结果值。,8.2模式中的存储过程,例如：给定某种商品货号prodid，计算其最高销售单价maxp(OUTPUT)。CreateProceduremaxPrice(pidchar(20),maxpnumeric(12,2)OUTPUT)ASbeginSelectmaxp=max(singlecost)FromsalesitemWhereprodid=pidend下面语句在SQL中完成调用。DeclaremaxpNumeric(12,2);ExecmaxPriceTV9475,maxp;Selectmaxp;,8.2模式中的存储过程,例如：给定某种商品货号prodid，检索销售该商品的订单号orderno及销售单价singlecost。CreateFunctionorderbyprod(pidchar(20)RETURNSTABLEASReturn(Selectorderno,singlecostfromsalesitemWhereprodid=pid)下面语句调用该函数并显示结果。Select*Fromorderbyprod(TV9475);,8.3Delphi数据库连接,在Delphi中连接数据库.doc,8.4Java数据库连接,如何用Java建立数据库应用：以数据库SybaseASA7为例.必要的构件：JDK；JDBC(一个类库文件jar，其中包含某个驱动器Driver，如com.sybase.jdbc.SybDriver)JDBC由两部分构成：接口规范和实现。接口规范是统一的；而实现由数据库厂商提供。JDBC仅负责连接数据库，转发SQL指令和结果。连接前的准备：确认数据库服务器已启动。JDBC类库文件(如jdbcdrv5.jar)设在合适的类路径CLASSPATH,8.4Java数据库连接,如何连接数据库：连接数据库需要4个参数：数据库URL(UniformResourceLocator)如：jdbc:sybase:Tds:localhost:2638/SALES?JCONNECT_VERSION=5注意：每种DBMS有不同的URL。应参考相关文档。驱动器Driver如“com.sybase.jdbc.SybDriver”注意：每种DBMS使用不同的驱动器。应参考相关文档。用户名如“dba”口令如“sql”,8.4Java数据库连接,importjava.sql.*;.privateConnectioncon;publicvoidconnectDriverManager(Stringurl,Stringdrv,Stringuser,Stringpwd)tryClass.forName(drv).newInstance();con=DriverManager.getConnection(url,user,pwd);catch(SQLExceptionex)printSQLException(ex);catch(Exceptionex)ex.printStackTrace();以上方法用于建立数据库连接。,8.4Java数据库连接,下面方法执行一个Select语句，加载某客户信息并初始化当前对象。publicClassCustomer.publicintloadCustomer(intcustid)throwsExceptionStringselect=selectcustomer.custid,name,prov,city,phone,unit,count(distinctsalesorder.orderno),sum(quantity),sum(singlecost*quantity)+fromcustomerNaturalLeftOuterJoin(salesorderkeyjoinsalesitem)+wherecustomer.custid=?+groupbycustomer.custid,name,prov,city,phone,unit;tryPreparedStatementprepSt=con.prepareStatement(select);prepSt.setInt(1,custid);ResultSetrs=prepSt.executeQuery();,8.4Java数据库连接,if(rs.next()this.custid=rs.getInt(1);name=rs.getString(2);prov=rs.getString(3);city=rs.getString(4);phone=rs.getString(5);unit=rs.getString(6);ordernum=rs.getInt(7);quantity=rs.getInt(8);amount=rs.getDouble(9);prepSt.close();elseprepSt.close();thrownewException(客户：+custid+不存在);catch(SQLExceptionex)dbcon.printSQLException(ex);returncustid;,8.5TransactionsinSQL,一组操作作为一个单元，按次序全部执行，称为事务Transaction。事务的特性：ACIDAtomicity：原子性，一个事务中的一组操作，要么全部执行，要么一点也不执行。Consistency：一致性，保持数据库的正确状态。Isolation：隔离性，多个事务并发运行时，作用效果相互分开。Durability：持久性，事务完成后，即使系统发生故障，事务的结果不丢失。通过加锁(locking)，日志(logging)和提交(commit)保持事务特性,8.5TransactionsinSQL,Howtocontrolatransactionexecution:TheSQLstatementcommitcausesthetransactiontoendsuccessfully;allrowupdatesmadeduringthetransactionbecomepermanentinthedatabaseandvisibletoconcurrent(并发)users.TheSQLstatementrollbackcausesthetransactiontoabort,orterminateunsuccessfully;allrowupdatesmadeduringthetransactionarereversedandthepriorversionsoftheserowareputinplaceandbecomevisibleagaintoconcurrentusers.Commitandrollbackbothcausethetransactiontoend.,8.5TransactionsinSQL,Problemscausedbyparalleltransactions:同时更新同一数据脏读dirtyread不可重复读non-repeatableread虚行(幻象元组)phantomrows例如：飞机售票系统A售票点B售票点航班信息,8.5TransactionsinSQL,Writelock:IfadataisupdatedbytransactionA,transactionBcannotupdateordeletethisdatabeforecommitorrollbackofA.,8.5TransactionsinSQL,Dirtydata:Dirtydataisacommontermfordatathathasbeenwrittenbyatransactionthathasnotyetcommitted.Dirtyread:Adirtyreadisareadofdirtydata.,8.5TransactionsinSQL,non-repeatableread:TransactionAperformsaread,buttransactionBmodifiesordeletesthatdatalater.IfAreadsthesamerowagain,differentdatawillbegot.,8.5TransactionsinSQL,phantom(幻象)rows:TransactionAdoesaqueryonasetofrowstoperformanoperation.TransactionBmodifiesthetablesuchthataqueryofAwouldhavegivenadifferentresult.Thetablemaybeinconsistent.,8.5TransactionsinSQL,读锁(readlock)：当数据被一个事务施加写锁时，其他事务不可施加对该数据的读锁或写锁。当数据被一个事务施加读锁时，其他事务可以施加对该数据的读锁，但不可施加写锁。,8.5TransactionsinSQL,Typesofisolationlevels:L0read-uncommitted:Dirtyreads,non-repeatablereadsandphantomreadscanoccur.L1read-committed:Dirtyreadsareprevented;non-repeatablereadsandphantomreadscanoccur.L2repeatable-read:Dirtyreadsandnon-repeatablereadsareprevented;phantomreadscanoccur.L3Serializable:Dirtyreads,non-repeatablereads,andphantomreadsareprevented.,8.5TransactionsinSQL,事务不论通过Commit还是Rollback结束，均释放自己所施加的写锁和读锁。当客户程序连接某个数据库时，可设置某个隔离级别。服务器可为所有用户设置一个缺省的隔离级别。,8.6SecurityandUserAuthorization,Akeyproblemofdatabasesecurity:AccessControl(访问控制)Protectedobjectsandoperationsindatabase:Table：AlterReferencesInsert/Delete/Update/SelectAllView：Delete/Insert/Select/UpdateProcedure：ExecuteUsersGrantGroupTocommonuser;Howtograntauthoritytoagroup?Grantinsert,delete,update,selectOndba.customertocommonuser;WITHGRANTOPTION,8.6SecurityandUserAuthorization,Howtobuildauserinagroup?GrantConnectTozhanghuiIdentifiedByaabb;Buildauserzhanghui,passwordisaabb.GrantmembershipinGroupcommonuserTozhanghui;Letzhanghuiincommonuser.Theuserownallpowerofgroupcommonuser.,8.6SecurityandUserAuthorization,Howtograntauthoritytoauser?Grantinsert,updateOndba.departmenttozhanghui;WITHGRANTOPTIONGrantin