1 / 352

Introducing the Oracle Database 11 g SQL and PL/SQL New Features

Introducing the Oracle Database 11 g SQL and PL/SQL New Features. Objectives. After completing this lesson, you should be able to: Describe the organization of the course Review the schemas that are used in this course

lamar-bruce
Download Presentation

Introducing the Oracle Database 11 g SQL and PL/SQL New Features

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. Introducing the Oracle Database 11g SQL and PL/SQL New Features

  2. Objectives • After completing this lesson, you should be able to: • Describe the organization of the course • Review the schemas that are used in this course • Review the SQL*Plus environment that you can optionally use in this course • Find additional information about Oracle Database 11g on the Oracle Technology Network

  3. Course Objectives • After completing this course, you should be able to: • Use the SQL Developer interface with the latest enhancements • Write SQL statements that include the new functions added to enhance regular expression support functionality • Monitor dependency tracking and change notification • List the changes to locking that enable you to specify the maximum number of seconds the statement should wait to obtain a DML lock on the table • Practice the performance improvements

  4. Course Objectives • Use the enhancements added to native dynamic SQL and to DBMS_SQL, which enable more interoperability between the two methodologies • Write compound triggers and use the enhancements made to the triggers • Use SecureFile LOBS • Write SQL and PL/SQL calls to sequences that are simpler • Use the new CONTINUE statement to control loops • Explore the data warehousing improvements

  5. Course Agenda • Day 1: • Introducing Oracle Database 11g SQL and PL/SQL enhancements • Using the SQL Developer enhancements • Using the language functionality enhancements • Executing dynamic SQL in PL/SQL with the 11g enhancements • Implementing the performance improvements

  6. Course Agenda • Day 2: • Practicing the language usability enhancements • Developing triggers that utilize the new enhancements • Administering SecureFile LOBs • Using the data warehousing usability enhancements

  7. Lesson Agenda • Appendixes and tables used in this course • Overview of SQL*Plus • Oracle Database 11g documentation and additional resources

  8. Appendixes Used in This Course • Appendix A: Practice Solutions • Appendix B: Table Descriptions • Appendix C: Using Oracle SQL Developer • Appendix D: SQL*Plus • Appendix E: Working with Collections • Appendix F: Exploring the Data Warehousing Performance Enhancements

  9. Tables Used in This Course • The sample schemas that are used in this course are: • The OrderEntry (OE) schema • The SalesHistory(SH) schema

  10. OrderEntry(OE) Schema CUSTOMERS customer_id cust_first_name cust_ last_name cust_ address_typ phone_numbersnls_language nls_territory credit_limitcust_ email account_mgr_id date_of_birth marital_status gender Income_level ORDERS order_id order_date order_mode customer_id order_status order_total sales_rep_id promotion_id ORDER_ITEMS order_id line_item_id product_id unit_price quantity street_address postal_code city state_province country_id PRODUCT_INFORMATIONproduct_id product_name product_description category_id weight_class warranty_period supplier_id product_status list_price min_price catalog_url PRODUCT_DESCRIPTIONS product_id language_id product_name product_description WAREHOUSES warehouse_id warehouse_name location_id INVENTORIES product_id warehouse_id quantity_on_hand

  11. SalesHistory(SH) Schema TIMES time_id day_name day_number_in_week day_number_in_month calendar_week_number fiscal_week_number week_ending_day week_ending_day_id calendar_month_number fiscal_month_number calendar_month_desc calendar_month_id fiscal_month_id days_in_cal_month days_in_fis_month end_of_cal_ month end_of_fis_month calendar _month _name fiscal _month _name calendar _quarter _desc calendar_quarter_id fiscal _quarter _desc fiscal _quarter _id days_in_cal_quarter days_in_fis_quarter end_of_cal_quarter end_of_fis_quarter calendar_quarter_number fiscal_quarter_number calendar_year calendar_year_id fiscal_year fiscal_year_id days_in_cal_year days_in_fis_year end_of_cal_year end_of_fis_year PROMOTIONS promo_id promo_name promo_subcategory promo_subcategory_id promo_category promo_category_id promo_cost promo_begin_date promo_end_date promo_total promo_total_id SALES prod_id cust_id time_id channel_id promo_id quantity_sold amount_sold COSTS prod_id time_id promo_id channel_id unit_cost unit_price CHANNELS channel_id channel_desc channel_class channel_class_id channel_total channel_total_id PRODUCTS

  12. SalesHistory(SH) Schema COSTS SALES SALES CUSTOMERS cust_id cust_first_name cust_last_name cust_gender cust_year_of_birth cust_marital_status cust_street_address cust_postal_code cust_city cust_city_id cust_state_province cust_state_province_id country_id cust_main_phone_number cust_income_level cust_credit_limit cust_email cust_total cust_total_id cust_src_id cust_eff_from cust_eff_to cust_valid PRODUCTS prod_id prod_name prod_desc prod_subcategory prod_subcategory_id prod_subcategory_desc prod_category prod_category_id prod_category_desc prod_weight_class prod_unit_of_measure prod_pack_size supplier_id prod_status prod_list_price prod_min_price prod_total prod_total_id prod_src_id prod_eff_from prod_eff_to prod_valid COUNTRIEScountry_id country_iso_code country_name country_subregion country_subregion_id country_region country_region_id country_total country_total_id Country_name_hist

  13. Class Account Information • Cloned OE account IDs are set up for you. • Your account IDs are OE1 – OE20. • The password matches your account ID. • Each machine is assigned one account. • All OE account IDs have SELECT status on the SH schema. • The instructor has a separate ID.

  14. Lesson Agenda • Appendixes and tables used in this course • Overview of SQL*Plus • Oracle Database 11g documentation and additional resources

  15. Overview of SQL*Plus Used in This Course • Logging in to SQL*Plus • Describing the table structure • Executing SQL from SQL*Plus • Reviewing SQL*Plus file commands

  16. Logging In to SQL*Plus 1 sqlplus [username[/password[@database]]] 2

  17. Displaying Table Structure DESCRIBE sh.customers Name Null? Type ------------------------------- -------- ----------------- CUST_ID NOT NULL NUMBER CUST_FIRST_NAME NOT NULL VARCHAR2(20) CUST_LAST_NAME NOT NULL VARCHAR2(40) CUST_GENDER NOT NULL CHAR(1) CUST_YEAR_OF_BIRTH NOT NULL NUMBER(4) CUST_MARITAL_STATUS VARCHAR2(20) CUST_STREET_ADDRESS NOT NULL VARCHAR2(40) CUST_POSTAL_CODE NOT NULL VARCHAR2(10) CUST_CITY NOT NULL VARCHAR2(30) CUST_CITY_ID NOT NULL NUMBER CUST_STATE_PROVINCE NOT NULL VARCHAR2(40) CUST_STATE_PROVINCE_ID NOT NULL NUMBER COUNTRY_ID NOT NULL NUMBER CUST_MAIN_PHONE_NUMBER NOT NULL VARCHAR2(25) CUST_INCOME_LEVEL VARCHAR2(30) CUST_CREDIT_LIMIT NUMBER CUST_EMAIL VARCHAR2(30) CUST_TOTAL NOT NULL VARCHAR2(14) CUST_TOTAL_ID NOT NULL NUMBER ...

  18. Executing SQL from SQL*Plus SELECT cust_last_name, cust_gender, cust_income_level FROM sh.customers; CUST_LAST_NAME G INCOME_LEVEL -------------------- - -------------------- Kinski M D: 70,000 - 89,999 Garcia F I: 170,000 - 189,999 Olin F F: 110,000 - 129,999 Altman F F: 110,000 - 129,999 de Funes F D: 70,000 - 89,999 Chapman F F: 110,000 - 129,999 Gielgud F E: 90,000 - 109,999 Prashant F C: 50,000 - 69,999 Welles M D: 70,000 - 89,999 Rampling M F: 110,000 - 129,999 ... 319 rows selected.

  19. SQL*Plus File Commands • SAVE filename • GET filename • START filename • @filename • EDIT filename • SPOOL filename • EXIT

  20. Lesson Agenda • Appendixes and tables used in this course • Overview of SQL*Plus • Overview of Oracle SQL Developer • Oracle Database 11g documentation and additional resources

  21. Oracle Database 11g SQL and PL/SQL Documentation • Navigate to http://www.oracle.com/pls/db111/homepage, then click the Books tab: • Oracle Database Advanced Application Developer’s Guide 11g, Release 1 (11.1) • Oracle Database Concepts 11g, Release 1 (11.1) • Oracle Database 2 Day Developer’s Guide 11g, Release 1 (11.1) • Oracle Database Security Guide 11g, Release 1 (11.1)

  22. Oracle Database 11g SQL and PL/SQL Documentation • Oracle Database SQL Language Reference 11g, Release 1 • Oracle Database PL/SQL Language Reference 11g, Release 1 • Oracle Database PL/SQL Packages and Types Reference 11g, Release 1 • Oracle Database Large Objects Developer’s Guide • SQL*Plus User’s Guide and Reference • Oracle Database SQL Developer User’s Guide, Release 1.2

  23. Additional Resources • For additional information about the new Oracle 11g SQL and PL/SQL new features, refer to the following: • Oracle Database 11g: New Features eStudies • Oracle by Example series (OBE): Oracle Database 11g • http://www.oracle.com/technology/obe/11gr1_db/admin/11gr1db.html • What’s New in PL/SQL in Oracle Database 11g on the Oracle Technology Network (OTN): • http://www.oracle.com/technology/tech/pl_sql/

  24. Summary • In this lesson, you should have learned how to: • Describe the organization of the course • Review the schemas that are used in this course • Review the SQL*Plus environment that you can optionally use in this course • Find additional information about Oracle Database 11g from the Oracle Technology Network

  25. Practice 1 Overview: Getting Started • This practice covers the following topics: • Reviewing the schemas for this course • Using SQL*Plus • Accessing Oracle Database 11g resources

  26. Using SQL Developer

  27. Objectives • After completing this lesson, you should be able to: • List the key features of Oracle SQL Developer • Install Oracle SQL Developer • Create a database connection • Navigate through the object navigator • Use the SQL Worksheet • Create, save, and use scripts • Develop, compile, and debug PL/SQL • Browse through the available search engines • Change preferences • Create reports • Describe migration

  28. What Is Oracle SQL Developer? • Oracle SQL Developer is a free graphical tool that enhances productivity and simplifies database development tasks. • You can connect to any target Oracle database schema using standard Oracle database authentication. SQL Developer

  29. Installing SQL Developer • Download the Oracle SQL Developer kit and unzip it into any directory on your machine.

  30. 4 6 2 5 7 1 3 Menus for SQL Developer

  31. Creating a Database Connection • You must have at least one database connection to use SQL Developer. • You can create and test connections for: • Multiple databases • Multiple schemas • SQL Developer automatically reads any connections defined in the tnsnames.ora file on your system. • You can export connections to an XML file. • Each additional database connection created is listed in the Connections navigator hierarchy.

  32. Creating a Database Connection 2 1 3 4

  33. Browsing Database Objects • Use the Database navigator to: • Browse through many objects in a database schema • Review the definitions of objects at a glance

  34. Exporting Database Objects Enter the file name destination, and select the Connection. Select the Objects to export. Click Apply.

  35. Exporting Database Objects The resulting file contains the object definitions you exported.

  36. Exporting and Importing Data

  37. Using SQL Worksheet • Use SQL Worksheet to enter and execute SQL, PL/SQL, and SQL*Plus statements. • Specify any actions that can be processed by the database connection associated with the worksheet.

  38. 2 4 6 8 9 5 7 1 3 Using SQL Worksheet

  39. Executing SQL Statements Use the Enter SQL Statement box to enter single or multiple SQL statements. View the results on the Script Output tabbed page.

  40. Saving SQL Scripts Click the Save icon to save your SQL statement to a file. Enter a file name and identify a location to save the file in the Windows Save dialog box. The contents of the saved file are visible and editable in your SQL Worksheet window.

  41. Executing Saved SQL Scripts Use the @ command followed by the location and name of the file you want to execute. Then click the Run Script icon. The output from the script is displayed on the Script Output tabbed page.

  42. Using PL/SQL in SQL Developer Right-click the Procedures node and select New Procedure.

  43. 1 2 3 4 5 Using PL/SQL in SQL Developer Enter your code. Enter the header information for the procedure, and then click OK.

  44. Using PL/SQL in SQL Developer Click Compile. Compilation messages are displayed on Messages – Log.

  45. Using PL/SQL in SQL Developer Click Run. The Run dialog box appears with a call to your code wrapped within an anonymous block. Enter the parameter values, and then click OK.

  46. Using PL/SQL in SQL Developer Enter the parameter values, and then click OK. The results are displayed on the Running – Log tabbed page.

  47. Browsing Through the Available Search Engines Enter a search word, and then press [Enter]. The results are displayed in your browser. Select a search engine.

  48. Changing Preferences From the Tools menu, select Preferences. The Preferences dialog box appears.

  49. Creating Reports • SQL Developer provides you with a number of predefined reports about your database and objects. • The reports are organized into categories. • You can create your own customized reports too.

  50. Creating Reports

More Related