大型數(shù)據(jù)庫系統(tǒng)分析與設(shè)計：Less08-Data

1、Managing DataObjectivesAfter completing this lesson, you should be able to do the following:Manage data through the use of SQLIdentify and administer PL/SQL objectsDescribe triggers and triggering eventsMonitor and resolve locking conflictsManipulating Data Through SQLSQL INSERT INTO employees VALUE

2、S 2 (9999,Bob,Builder,NULL,SYSDATE, 3 IT_PROG,NULL,NULL,100,90);1 row created.SQL UPDATE employees SET SALARY=6000 2 WHERE EMPLOYEE_ID = 9999;1 row updated.SQL DELETE from employees 2 WHERE EMPLOYEE_ID = 9999;1 row deleted.The INSERT CommandCreate one row at a time.Insert many rows from another tabl

3、e.The UPDATE CommandUse the UPDATE command to change zero or more rows of a table.The DELETE CommandUse the DELETE command to remove zero or more rows from a table.The MERGE CommandUse the MERGE command to perform both INSERT and UPDATE in a single command.The MERGE CommandFull Notes PageThe COMMIT

4、and ROLLBACK CommandsThese are used to finish a transaction:COMMIT: Makes the change permanent ROLLBACK: Undoes the changeIntegrity Constraints and DMLPL/SQLProcedural language/structured query language (PL/SQL) is a fourth-generation programming language (4GL). It provides:Procedural extensions to

5、SQLPortability across platforms and productsHigher level of security and data integrity protectionSupport for object-oriented programmingPL/SQLFull Notes PageAdministering PL/SQL ObjectsDatabase administrators should be able to:Identify problem PL/SQL objectsRecommend the appropriate use of PL/SQLLo

6、ad PL/SQL objects into the databaseAssist PL/SQL developers in troubleshootingPL/SQL ObjectsThere are many types of PL/SQL database objects:PackagePackage bodyType bodyProcedureFunctionTriggerFunctionsProceduresProcedures are used to perform a specific action. They: Pass values in and out by using a

7、n argument listAre called with the CALL commandPackagesPackages are collections of functions and procedures. Each package should consist of two objects:Package specificationPackage body Package specificationPackage Specification and BodyBuilt-in PackagesThe Oracle database comes with over 350 built-

8、in PL/SQL packages, which provide:Administration and maintenance utilitiesExtended functionalityUse the DESCRIBE command to view subprograms.TriggersTriggering EventsEvent TypeExamples of EventsDMLINSERT, UPDATE, DELETEDDLCREATE, DROP, ALTER, GRANT, REVOKE, RENAME,DatabaseLOGON, LOGOFF, STARTUP, SHU

9、TDOWN, SERVERERRORLocksLocks prevent multiple sessions from changing the same data at the same time.They are automatically obtained at the lowest possible level for a given statement.They do not escalate.Transaction 1SQL UPDATE employees 2 SET salary=salary*1.1 3 WHERE employee_id=100;SQL UPDATE emp

10、loyees 2 SET salary=salary+100 3 WHERE employee_id=100;Transaction 2Locking MechanismHigh level of data concurrency:Row-level locks for inserts, updates, and deletesNo locks required for queriesAutomatic queue managementLocks held until the transaction ends (with the COMMIT or ROLLBACK operation)Tra

11、nsaction 1SQL UPDATE employees 2 SET salary=salary*1.1 3 WHERE employee_id=101;SQL UPDATE employees 2 SET salary=salary+100 3 WHERE employee_id=100;Transaction 2Data ConcurrencyTime:09:00:00Transaction 1UPDATE hr.employees SET salary=salary+100 WHERE employee_id=100;Transaction 2UPDATE hr.employees

12、SET salary=salary+100 WHERE employee_id=101;Transaction 3UPDATE hr.employees SET salary=salary+100 WHERE employee_id=102;.Transaction xUPDATE hr.employees SET salary=salary+100 WHERE employee_id=xxx;Data ConcurrencyFull Notes PageDML LocksTransaction 1SQL UPDATE employees 2 SET salary=salary*1.1 3 W

13、HERE employee_id= 106;1 row updated.SQL UPDATE employees 2 SET salary=salary*1.1 3 WHERE employee_id= 107;1 row updated.Transaction 2Each DML transaction must acquire two locks:Exclusive row lock for the row or rows being updatedRow-exclusive table-level lock for the table containing the rowsEnqueue

14、 MechanismThe enqueue mechanism keeps track of:Sessions waiting for locksThe requested lock modeThe order in which sessions requested the lockLock ConflictsUPDATE employees SET salary=salary+100 WHERE employee_id=100;1 row updated.9:00:00UPDATE employees SET salary=salary+100 WHERE employee_id=101;1

15、 row updated.UPDATE employees SETCOMMISION_PCT=2 WHERE employee_id=101;Session waits enqueued due to lock conflict.9:00:05SELECT sum(salary) FROM employees;SUM(SALARY) 692634Session still waiting!16:30:00Many selects, inserts, updates, and deletes during the last 7.5 hours, but no commits or rollbac

16、ks!1 row updated.Session continues.16:30:01commit;Transaction 1Transaction 2TimePossible Causes of Lock ConflictsUncommitted changesLong-running transactions Unnecessarily high locking levelsDetecting Lock ConflictsSelect Blocking Sessions from the Performance page.Click the Session ID link to view

17、information about the locking session, including the actual SQL statement.Resolving Lock ConflictsTo resolve a lock conflict:Have the session holding the lock commit or roll backTerminate the session holding the lock as a last resortResolving Lock Conflicts Using SQLSQL statements can be used to det

18、ermine the blocking session and kill it.SQL alter system kill session 144,8982 immediate;SQL select sid, serial#, usernamefrom v$session where sid in(select blocking_session from v$session)Result:12DeadlocksTransaction 2Transaction 1UPDATE employeesSET salary = salary x 1.1WHERE employee_id = 1000;UPDATE emplo