1 / 48

Chapter 8

Chapter 8. Advanced SQL Database Systems: Design, Implementation, and Management, Seventh Edition, Rob and Coronel. Numeric Functions. String Functions. String Functions (continued). Conversion Functions. Conversion Functions (continued). Oracle Sequences. Oracle Sequences (continued).

denise-calhoun
Download Presentation

Chapter 8

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 8 Advanced SQL Database Systems: Design, Implementation, and Management, Seventh Edition, Rob and Coronel

  2. Numeric Functions Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  3. String Functions Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  4. String Functions (continued) Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  5. Conversion Functions Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  6. Conversion Functions (continued) Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  7. Oracle Sequences Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  8. Oracle Sequences (continued) Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  9. Updatable Views Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  10. Updatable Views (continued) Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  11. Updatable Views (continued) Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  12. Updatable Views (continued) Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  13. Procedural SQL Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  14. Procedural SQL (continued) • SHOW ERRORS • Can help diagnose errors found in PL/SQL blocks • Yields additional debugging information whenever error is generated after creating or executing a PL/SQL block Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  15. Procedural SQL (continued) Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  16. Procedural SQL (continued) Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  17. Triggers Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  18. Triggers (continued) Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  19. Triggers (continued) Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  20. Triggers (continued) Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  21. Triggers (continued) Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  22. Triggers (continued) Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  23. Triggers (continued) Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  24. Triggers (continued) Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  25. Triggers (continued) Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  26. Triggers (continued) Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  27. Triggers (continued) Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  28. Stored Procedures • Advantages • Substantially reduce network traffic and increase performance • No transmission of individual SQL statements over network • Help reduce code duplication by means of code isolation and code sharing • Minimize chance of errors and cost of application development and maintenance Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  29. Stored Procedures (continued) Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  30. Stored Procedures (continued) Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  31. Stored Procedures (continued) Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  32. Stored Procedures (continued) Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  33. Stored Procedures (continued) Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  34. Stored Procedures (continued) Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  35. Stored Procedures (continued) Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  36. PL/SQL Processing with Cursors Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  37. PL/SQL Processing with Cursors (continued) Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  38. PL/SQL Processing with Cursors (continued) Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  39. PL/SQL Stored Functions • Syntax: • CREATE FUNCTION function_name (argument IN data-type, …) RETURN data- type [IS]BEGIN PL/SQL statements; … RETURN (value or expression);END; Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  40. Embedded SQL • Key differences between SQL and procedural languages are: • Run-time mismatch • SQL executed one instruction at a time • Host language typically runs at client side in its own memory space • Processing mismatch • Host language processes one data element at a time • Data type mismatch • Data types may not match Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  41. Embedded SQL (continued) • Embedded SQL framework defines: • Standard syntax to identify embedded SQL code within host language • Standard syntax to identify host variables • Communication area used to exchange status and error information between SQL and host language Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  42. Embedded SQL (continued) Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  43. Embedded SQL (continued) • Static SQL • Embedded SQL in which programmer used predefined SQL statements and parameters • End users of programs are limited to actions that were specified in application programs • SQL statements will not change while application is running Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  44. Embedded SQL (continued) • Dynamic SQL • SQL statement is not known in advance, but instead is generated at run time • Program can generate SQL statements at run time that are required to respond to ad hoc queries • Attribute list and condition are not known until end user specifies them • Tends to be much slower than static SQL • Requires more computer resources Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  45. Summary • SQL provides relational set operators to combine output of two queries to generate new relation • Operations that join tables can be classified as inner joins and outer joins • Natural join returns all rows with matching values in the matching columns and eliminates duplicate columns Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  46. Summary (continued) • Joins may use keywords, such as USING and ON • Subqueries and correlated queries are used when it is necessary to process data based on other processed data • Most subqueries are executed in serial fashion Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  47. Summary (continued) • SQL functions are used to extract or transform data • Oracle sequences may be used to generate values to be assigned to a record • PL/SQL can be used to create triggers, stored procedures, and PL/SQL functions • A stored procedure is a named collection of SQL statements Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  48. Summary (continued) • When SQL statements are designed to return more than one value inside the PL/SQL code, cursor is needed • Embedded SQL refers to use of SQL statements within an application programming language Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

More Related