1 / 55

Database Systems Implementation Guide

Learn advanced SQL functions, triggers, and stored procedures for efficient database design and management. Includes practical examples and tips.

ccobbs
Download Presentation

Database Systems Implementation Guide

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. Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

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

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

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

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

  10. SQL Server Identity (Oracle Sequences) ALTER TABLE CUSTOMER ADD CUS_CODE_SEQ INT IDENTITY(20010,1); ALTER TABLE INVOICE ADD INV_NUMBER_SEQ INT IDENTITY(4010,1); ALTER TABLE LINE ADD INV_NUMBER_SEQ INT IDENTITY(4010,1); Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  11. BEGIN TRANSACTION; INSERT INTO CUSTOMER VALUES (10020, 'Connery', 'Sean', NULL, '615', '898-2007', 0.00); INSERT INTO INVOICE VALUES (1009, 10010, CURRENT_TIMESTAMP); INSERT INTO LINE VALUES (1009, 1,'13-Q2/P2', 1, 14.99); INSERT INTO LINE VALUES (1009, 2,'23109-HB', 1, 9.95); COMMIT; Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

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

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

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

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

  16. Procedural SQL 請參考 SQL Server 版 Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  17. 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

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

  19. Procedural SQL (continued) 請參考 SQL Server 版 Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

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

  21. Triggers (continued) 請參考 SQL Server 版 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) 請參考 SQL Server 版 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) 請參考 SQL Server 版 Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

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

  29. Triggers (continued) 請參考 SQL Server 版 Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  30. Triggers (continued) 請參考 SQL Server 版 Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  31. 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

  32. Stored Procedures (continued) 請參考 SQL Server 版 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) 請參考 SQL Server 版 Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

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

  36. Stored Procedures (continued) 請參考 SQL Server 版 Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  37. Stored Procedures (continued) 請參考 SQL Server 版 Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

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

  39. PL/SQL Processing with Cursors • A cursor is a special construct used in procedural SQL to hold the data rows returned by an SQL query • Implicit cursor – automatically created when the SQL query returns only one value • Explicit cursor CURSOR cursor_name IS select-query; Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

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

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

  42. PL/SQL Processing with Cursors 請參考 SQL Server 版 Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  43. 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

  44. 8.8 Embedded SQL (以下跳過) 專題會用到 • Key differences between SQL and procedural languages are: • Run-time mismatch • SQL executed one instruction at a time at the server side • 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

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

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

  47. Embedded SQL (continued) • General steps to create and run an executable program with embedded SQL: • Write embedded SQL code within the host language instructions EXEC SQL SQL statement END-EXEC • A preprocessor is used to transform the embedded SQL into specialized procedure calls that are DBMS-and-language-specific. • Compile the program using the host language compiler • The object code is linked to the respective library modules and generates the executable program • The executable is run and the embedded SQL statement retrieves data from the database Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  48. SQLCODE + COBOL EXEC SQL SELECT EMP_FNAME, EMP_LNAME INTO :W_EMP_FNAME, :W_EMP_LNAME FROM EMPLOYEE WHERE EMP_NUM=:W_EMP_NUM; END-EXEC IF SQLCODE = 0 THEN PERFORM DATA_ROUTINE ELSE PERFORM ERROR_ROUTINE END-IF Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  49. CURSOR 1. EXEC SQL DECLARE PROD_CURSOR FOR SELECT P_CODE, P_DESC FROM PRODUCT WHERE P_ON_HAND > (SELECT AVG(P_ON_HAND) FROM PRODUCT); 2. EXEC SQL OPEN PROD_CURSOR; END-EXEC 3. EXEC SQL FETCH PRODUCT_CURSOR INTO :W_P_CODE, :W_P_DESC; END-EXEC IF SQLCODE = 0 THEN …. 4. EXEC SQL CLOSE PROD_CURSOR; END-EXEC Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

  50. 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 EXEC SQL DELETE FROM EMPLOYEE WHERE EMP_NUM = :W_EMP_NUM; END-EXEC • SQL statements will not change while application is running Database Systems: Design, Implementation, & Management, 7th Edition, Rob & Coronel

More Related