Introduction to Oracle 10g Programming Training in Freiburg, Germany
We offer private customized training for groups of 3 or more attendees.
|
||
Course Description |
||
In this class, students will learn how to create, retrieve, and manipulate objects in Oracle 10g Structured Query Language (SQL). Students will also be introduced to Oracle 10g database features and tools. The Oracle 10g release has greatly enhanced the features and functionality of PL/SQL. Students will learn the fundamentals of the PL/SQL programming language. Students will write stored procedures, functions, packages, and triggers, and implement complex business rules in Oracle. Students
will learn programming, management, and security issues of working with PL/SQL program units.
Programming topics will include the built-in packages that come with Oracle, the creation of triggers, and stored procedure features.
Course Length: 5 Days
Course Tuition: $2090 (US) |
Prerequisites |
|
A solid understanding of 3GL programming is required. |
Course Outline |
1. Relational Database and SQL Overview Review of Relational Database Terminology Relational Database Management Systems Introduction to SQL Oracle Versioning and History Logical and Physical Storage Structures Connecting to a SQL Database Datatypes Sample Database 2. Using Oracle SQL*Plus SQL*Plus The SQL Buffer Buffer Manipulation Commands Running SQL*Plus Scripts Tailoring Your SQL*Plus Environment Viewing Table Characteristics SQL*Plus Substitution Variables Interactive SQL*Plus Scripts SQL*Plus LOB Support Using iSQL*Plus Graphical Clients 3. SQL Queries - The SELECT Statement The SELECT Statement The CASE...WHEN Statement Choosing Rows with the WHERE Clause NULL Values Compound Expressions IN and BETWEEN Pattern Matching: LIKE and REGEXP_LIKE Creating Some Order 4. Scalar Functions SQL Functions Using SQL Functions String Functions Numeric Functions Date Functions Date Formats Conversion Functions Literal Values Intervals Oracle Pseudocolumns 5. SQL Queries - Joins Selecting from Multiple Tables Joining Tables Self Joins Outer Joins 6. Aggregate Functions and Advanced Techniques Subqueries Correlated Subqueries The EXISTS Operator The Aggregate Functions Nulls and DISTINCT Grouping Rows Combining SELECT Statements 7. Data Manipulation and Transactions The INSERT Statement The UPDATE Statement The DELETE Statement Transaction Management Concurrency Explicit Locking Data Inconsistencies Loading Tables from External Sources 8. Data Definition and Control Statements Datatypes Defining Tables Constraints Inline Constraints Modifying Table Definitions Deleting a Table Definition Controlling Access to Your Tables 9. Other Database Objects Views Creating Views Updatable Views Sequences Synonyms 10. Triggers Beyond Declarative Integrity Triggers Types of Triggers Row-Level Triggers Trigger Predicates Trigger Conditions Using SEQUENCEs Cascading Triggers and Mutating Tables Generating an Error Maintaining Triggers 11. PL/SQL Variables and Datatypes Anonymous Blocks Declaring Variables Datatypes Subtypes Character Data Dates and Timestamps Date Intervals Anchored Types Assignment and Conversions Selecting into a Variable Returning into a Variable 12. PL/SQL Syntax and Logic Conditional Statements – IF/THEN Conditional Statements – CASE Comments and Labels Loops WHILE and FOR Loops SQL in PL/SQL Local Procedures and Functions 13. Stored Procedures and Functions Stored Subprograms Creating a Stored Procedure Procedure Calls and Parameters Parameter Modes Creating a Stored Function Stored Functions and SQL Invoker’s Rights 14. Exception Handling SQLCODE and SQLERRM Exception Handlers Nesting Blocks Scope and Name Resolution Declaring and Raising Named Exceptions User-Defined Exceptions 15. Records, Collections, and User-Defined Types Record Variables Using the %ROWTYPE Attribute VARRAY and Nested TABLE Collections Using Nested TABLEs Using VARRAYs Collection in Database Tables Associative Array Collections Collection Methods Iterating Through Collections 16. Cursors Multi-Row Queries Declaring and Opening Cursors Fetching Rows Closing Cursors The Cursor FOR Loop FOR UPDATE Cursors Cursor Parameters The Implicit (SQL) Cursor 17. Bulk Operations Bulk Binding BULK COLLECT Clause FORALL Statement FORALL Variations Bulk Returns Bulk Fetching with Cursors 18. Using Packages Packages Oracle-Supplied Packages The DBMS_OUTPUT Package The DBMS_UTILITY Package The UTL_FILE Package Creating Pipes with DBMS_PIPE Writing to and Reading from a Pipe The DBMS_METADATA Package XML Packages Networking Packages Other Supplied Packages 19. Creating Packages Structure of a Package The Package Interface and Implementation Package Variables and Package State Overloading Package Functions and Procedures Forward Declarations Strong REF CURSOR Variables Weak REF CURSOR Variables 20. Working with LOBs Large Object Types Oracle Directories LOB Locators Internal LOBs External LOBs Temporary LOBs The DBMS_LOB Package 21. Maintaining PL/SQL Code Privileges for Stored Programs Data Dictionary PL/SQL Stored Program Compilation Conditional Compilation Compile-Time Warnings The PL/SQL Execution Environment Dependencies and Validation Maintaining Stored Programs 22. Appendix A - The Data Dictionary Introducing the Data Dictionary DBA, ALL, and USER Data Dictionary Views Some Useful Data Dictionary Queries 23. Appendix B - Dynamic SQL Generating SQL at Runtime Native Dynamic SQL vs. DBMS_SQL Package The EXECUTE IMMEDIATE Statement Using Bind Variables Multi-row Dynamic Queries Bulk Operations with Dynamic SQL Using DBMS_SQL DBMS_SQL Subprograms 24. Appendix C - PL/SQL Versions, Datatypes, and Language Limits 25. Appendix D – Oracle 10g Supplied Packages |
Course Directory [training on all levels]
- .NET Classes
- Agile/Scrum Classes
- Ajax Classes
- Android and iPhone Programming Classes
- Blaze Advisor Classes
- C Programming Classes
- C# Programming Classes
- C++ Programming Classes
- Cisco Classes
- Cloud Classes
- CompTIA Classes
- Crystal Reports Classes
- Design Patterns Classes
- DevOps Classes
- Foundations of Web Design & Web Authoring Classes
- Git, Jira, Wicket, Gradle, Tableau Classes
- IBM Classes
- Java Programming Classes
- JBoss Administration Classes
- JUnit, TDD, CPTC, Web Penetration Classes
- Linux Unix Classes
- Machine Learning Classes
- Microsoft Classes
- Microsoft Development Classes
- Microsoft SQL Server Classes
- Microsoft Team Foundation Server Classes
- Microsoft Windows Server Classes
- Oracle, MySQL, Cassandra, Hadoop Database Classes
- Perl Programming Classes
- Python Programming Classes
- Ruby Programming Classes
- Security Classes
- SharePoint Classes
- SOA Classes
- Tcl, Awk, Bash, Shell Classes
- UML Classes
- VMWare Classes
- Web Development Classes
- Web Services Classes
- Weblogic Administration Classes
- XML Classes