You are previewing Oracle Database 11g & MySQL 5.6 Developer Handbook.
O'Reilly logo
Oracle Database 11g & MySQL 5.6 Developer Handbook

Book Description

Master Application Development in a Mixed-Platform Environment

Build powerful database applications in a mixed environment using the detailed information in this Oracle Press guide. Oracle Database 11g & MySQL 5.6 Developer Handbook lays out programming strategies and best practices for seamlessly operating between the two platforms. Find out how to migrate databases, port SQL dialects, work with Oracle MySQL databases, and configure effective queries. Security, monitoring, and tuning techniques are also covered in this comprehensive volume.

• Understand Oracle Database 11g and MySQL 5.6 architecture

• Convert databases between platforms and ensure transactional integrity

• Create tables, sequences, indexes, views, and user accounts

• Build and debug PL/SQL, SQL*Plus, SQL/PSM, and MySQL Monitor scripts

• Execute complex queries and handle numeric and date mathematics

• Merge data from source tables and set up virtual directories

Table of Contents

  1. Cover
    1. Title page
    2. Copyright page
    3. Dedication
  2. Contents at a Glance
  3. Contents
  4. Acknowledgments
  5. Introduction
  6. PART I Development Components
    1. 1 Architecture
      1. General Client-Server Computing Model
      2. Oracle Database 11g
      3. Client Software: SQL*Plus
      4. Oracle 11g Server Software
      5. Oracle Data Dictionary
      6. Oracle MySQL 5.6
      7. Client Software: The MySQL Monitor
      8. MySQL Server Software
      9. MySQL Data Dictionary
      10. Summary
      11. Mastery Check
    2. 2 Client Interface
      1. SQL*Plus
      2. Connecting to and Disconnecting from SQL*Plus
      3. Working in the SQL*Plus Environment
      4. Writing SQL Statements with SQL*Plus
      5. Saving SQL Statements with SQL*Plus
      6. Editing SQL Statements with SQL*Plus
      7. Rerunning SQL*Plus SQL Statements from the Buffer
      8. Aborting Entry of SQL Statements in SQL*Plus
      9. Calling and Running SQL*Plus Script Files
      10. Passing Parameters to SQL*Plus Script Files
      11. Calling PL/SQL Programs
      12. Writing SQL*Plus Log Files
      13. MySQL Monitor
      14. Connecting to and Disconnecting from MySQL Monitor
      15. Writing MySQL SQL Statements
      16. Saving MySQL SQL Statements
      17. Editing MySQL SQL Statements
      18. Aborting MySQL SQL Statements
      19. Calling and Running MySQL Script Files
      20. Setting a Session Variable
      21. Calling SQL/PSM Programs
      22. Writing MySQL Log Files
      23. Summary
      24. Mastery Check
    3. 3 Security Models
      1. Security Barriers
      2. Securing the Network
      3. Securing the Operating System
      4. Securing the Database
      5. Security Privileges
      6. Data Control Language
      7. Definer and Invoker Rights
      8. Definer Rights
      9. Invoker Rights
      10. Summary
      11. Mastery Check
    4. 4 Transaction Models
      1. Data Transactions
      2. ACID-compliant SQL Statements
      3. INSERT Statement
      4. UPDATE Statement
      5. DELETE Statement
      6. Stored Programs
      7. Triggers
      8. Summary
      9. Mastery Check
    5. 5 Constraints
      1. NOT NULL Constraints
      2. Oracle NOT NULL Constraints
      3. MySQL NOT NULL Constraints
      4. UNIQUE Constraints
      5. Oracle UNIQUE Constraints
      6. MySQL UNIQUE Constraints
      7. Unique Indexes
      8. Primary Key Constraints
      9. Oracle Primary Key Constraints
      10. MySQL Primary Key Constraints
      11. Foreign Key Constraints
      12. Oracle Foreign Key Constraints
      13. MySQL Foreign Key Constraints
      14. CHECK Constraints
      15. Oracle CHECK Constraints
      16. MySQL CHECK Constraints
      17. Trigger Constraints
      18. Summary
      19. Mastery Check
  7. PART II SQL Development
    1. 6 Creating Users and Structures
      1. Users
      2. Oracle Users
      3. MySQL Users
      4. Databases
      5. Oracle Schemas
      6. MySQL Databases
      7. Tables
      8. Oracle Tables
      9. MySQL Tables
      10. Indexes
      11. Oracle Indexes
      12. MySQL Indexes
      13. Summary
      14. Mastery Check
    2. 7 Modifying Users and Structures
      1. Users
      2. Oracle Users
      3. MySQL Users
      4. Database
      5. Sessions
      6. Enable SQL Tracing
      7. Enable Conditional Compilation
      8. Tables
      9. Data Catalog Table Definitions
      10. Adding, Modifying, and Dropping Columns
      11. Dropping Tables
      12. Indexes
      13. Oracle Index Maintenance
      14. MySQL Index Maintenance
      15. Views
      16. Oracle Drop Views
      17. MySQL Drop Views
      18. Summary
      19. Mastery Check
    3. 8 Inserting Data
      1. Insert by Values
      2. Oracle Insert by Values
      3. MySQL Insert by Values
      4. Insert by Queries
      5. Oracle Insert by Queries
      6. MySQL Insert by Queries
      7. Summary
      8. Mastery Check
    4. 9 Updating Data
      1. Update by Values and Queries
      2. Oracle Update by Values and Queries
      3. MySQL Update by Values
      4. Update by Correlated Queries
      5. Oracle Correlated Queries
      6. MySQL Correlated Queries
      7. Summary
      8. Mastery Check
    5. 10 Deleting Data
      1. Delete by Value Matches
      2. Delete Nested Table Row Elements
      3. Delete by Correlated Queries
      4. MySQL Delete by Multiple-Table Statement
      5. Summary
      6. Mastery Check
    6. 11 Querying
      1. Query Results
      2. Queries that Return Columns or Results from Columns
      3. Queries that Aggregate
      4. Queries that Return Columns or Results Selectively
      5. Join Results
      6. Joins that Splice Together Rows
      7. Joins that Splice Collections
      8. Views: Stored Queries
      9. Creating Oracle Views
      10. Creating MySQL Views
      11. Summary
      12. Mastery Check
    7. 12 Merging Data
      1. Merging Data in Oracle
      2. Step 1: Create a Virtual Directory
      3. Step 2: Position Your Physical CSV File
      4. Step 3: Create Example Tables
      5. Step 4: Test Configuration
      6. Step 5: Merge the Import Source
      7. Merging Data in MySQL
      8. Import and Export Data Processes
      9. Merging with the INSERT statement
      10. Merging with the REPLACE INTO Statement
      11. Summary
      12. Mastery Check
    8. 13 PL/SQL Programming Language
      1. PL/SQL Blocks
      2. Anonymous Blocks
      3. Named Blocks
      4. PL/SQL Variables: Types, Assignments, and Operators
      5. Text Data Types
      6. Date Types
      7. Number Types
      8. Composite Variable Types
      9. PL/SQL Control Structures
      10. If Statement
      11. CASE Statement
      12. Iterative Structures
      13. Bulk Operations
      14. Native Dynamic SQL
      15. NDS Statements Without Bind Variables
      16. NDS Statements with Bind Variables
      17. Exception Handling
      18. User-Defined Exceptions
      19. Dynamic User-Defined Exceptions
      20. Summary
      21. Mastery Check
    9. 14 SQL/PSM Basics
      1. SQL/PSM Stored Programs
      2. Function and Procedure Coding Rules
      3. SQL/PSM Functions
      4. SQL/PSM Procedures
      5. SQL/PSM Variables
      6. SQL/PSM Local Variables
      7. SQL/PSM Cursors
      8. SQL/PSM Handlers
      9. SQL/PSM Control Structures
      10. If Statements
      11. CASE Statements
      12. Loop Statements
      13. Prepared Statements
      14. Prepared Statements at the Command Line
      15. Prepared Statements in Stored Programs
      16. Summary
      17. Mastery Check
    10. 15 Triggers
      1. Trigger Architecture
      2. Oracle Trigger Architecture
      3. MySQL Trigger Architecture
      4. DDL Triggers
      5. Oracle DDL Triggers
      6. MySQL DDL Triggers
      7. DML Triggers
      8. Oracle DML Triggers
      9. MySQL DML Triggers
      10. Compound Triggers
      11. Oracle Compound Triggers
      12. MySQL Compound Triggers
      13. INSTEAD OF Triggers
      14. Oracle INSTEAD OF Triggers
      15. MySQL Instead-of Triggers
      16. System Triggers
      17. Oracle System Triggers
      18. MySQL System Triggers
      19. Summary
      20. Mastery Check
  8. Mastery Check Answers
  9. Index