Course (3-2) Oracle PL/SQL Part 1
- 1. Introduction to PL/SQL
- 1.1. What Is PL/SQL?
- 1.2. The Origins of PL/SQL
- 1.2.1. The Early Years of PL/SQL
- 1.2.2. Improved Application Portability
- 1.2.3. Improved Execution Authority and Transaction Integrity
- 1.2.4. Humble Beginnings, Steady Improvement
- 1.3. So This Is PL/SQL
- 1.3.1. Integration with SQL
- 1.3.2. Control and Conditional Logic
- 1.3.3. When Things Go Wrong
- 1.4. About PL/SQL Versions
- 1.4.1. Oracle Database 11g New Features
- 1.5. Resources for PL/SQL Developers
- 1.5.1. The O’Reilly PL/SQL Series
- 1.5.2. PL/SQL on the Internet
- 1.6. Some Words of Advice
- 1.6.1. Don’t Be in Such a Hurry!
- 1.6.2. Don’t Be Afraid to Ask for Help
- 1.6.3. Take a Creative, Even Radical Approach
- 2. Creating and Running PL/SQL Code
- 2.1. Navigating the Database
- 2.2. Creating and Editing Source Code
- 2.3. SQL*Plus
- 2.3.1. Starting Up SQL*Plus
- 2.3.2. Running a SQL Statement
- 2.3.3. Running a PL/SQL Program
- 2.3.4. Running a Script
- 2.3.5. What Is the “Current Directory”?
- 2.3.6. Other SQL*Plus Tasks
- 2.3.7. Error Handling in SQL*Plus
- 2.3.8. Why You Will Love and Hate SQL*Plus
- 2.4. Performing Essential PL/SQL Tasks
- 2.4.1. Creating a Stored Program
- 2.4.2. Executing a Stored Program
- 2.4.3. Showing Stored Programs
- 2.4.4. Managing Grants and Synonyms for Stored Programs
- 2.4.5. Dropping a Stored Program
- 2.4.6. Hiding the Source Code of a Stored Program
- 2.5. Editing Environments for PL/SQL
- 2.6. Calling PL/SQL from Other Languages
- 2.6.1. C: Using Oracle’s Precompiler (Pro*C)
- 2.6.2. Java: Using JDBC
- 2.6.3. Perl: Using Perl DBI and DBD::Oracle
- 2.6.4. PHP: Using Oracle Extensions
- 2.6.5. PL/SQL Server Pages
- 2.6.6. And Where Else?
- 3. Language Fundamentals
- 3.1. PL/SQL Block Structure
- 3.1.1. Anonymous Blocks
- 3.1.2. Named Blocks
- 3.1.3. Nested Blocks
- 3.1.4. Scope
- 3.1.5. Qualify all References to Variables and Columns in SQL Statements
- 3.1.6. Visibility
- 3.2. The PL/SQL Character Set
- 3.3. Identifiers
- 3.3.1. Reserved Words
- 3.3.2. Whitespace and Keywords
- 3.4. Literals
- 3.4.1. NULLs
- 3.4.2. Embedding Single Quotes Inside a Literal String
- 3.4.3. Numeric Literals
- 3.4.4. Boolean Literals
- 3.5. The Semicolon Delimiter
- 3.6. Comments
- 3.6.1. Single-Line Comment Syntax
- 3.6.2. Multiline Comment Syntax
- 3.1. PL/SQL Block Structure
- 4. Conditional and Sequential Control
- 4.1. IF Statements
- 4.1.1. The IF-THEN Combination
- 4.1.2. The IF-THEN-ELSE Combination
- 4.1.3. The IF-THEN-ELSIF Combination
- 4.1.4. Avoiding IF Syntax Gotchas
- 4.1.5. Nested IF Statements
- 4.1.6. Short-Circuit Evaluation
- 4.2. CASE Statements and Expressions
- 4.2.1. Simple CASE Statements
- 4.2.2. Searched CASE Statements
- 4.2.3. Nested CASE Statements
- 4.2.4. CASE Expressions
- 4.3. The GOTO Statement
- 4.4. The NULL Statement
- 4.4.1. Improving Program Readability
- 4.4.2. Using NULL After a Label
- 4.1. IF Statements
- 5. Iterative Processing with Loops
- 5.1. Loop Basics
- 5.1.1. Examples of Different Loops
- 5.1.2. Structure of PL/SQL Loops
- 5.2. The Simple Loop
- 5.2.1. Terminating a Simple Loop: EXIT and EXIT WHEN
- 5.2.2. Emulating a REPEAT UNTIL Loop
- 5.2.3. The Intentionally Infinite Loop
- 5.3. The WHILE Loop
- 5.4. The Numeric FOR Loop
- 5.4.1. Rules for Numeric FOR Loops
- 5.4.2. Examples of Numeric FOR Loops
- 5.4.3. Handling Nontrivial Increments
- 5.5. The Cursor FOR Loop
- 5.5.1. Example of Cursor FOR Loops
- 5.6. Loop Labels
- 5.7. The CONTINUE Statement
- 5.8. Tips for Iterative Processing
- 5.8.1. Use Understandable Names for Loop Indexes
- 5.8.2. The Proper Way to Say Goodbye
- 5.8.3. Obtaining Information About FOR Loop Execution
- 5.8.4. SQL Statement as Loop
- 5.1. Loop Basics
- 6. Exception Handlers
- 6.1. Exception-Handling Concepts and Terminology
- 6.2. Defining Exceptions
- 6.2.1. Declaring Named Exceptions
- 6.2.2. Associating Exception Names with Error Codes
- 6.2.3. About Named System Exceptions
- 6.2.4. Scope of an Exception
- 6.3. Raising Exceptions
- 6.3.1. The RAISE Statement
- 6.3.2. Using RAISE_APPLICATION_ERROR
- 6.4. Handling Exceptions
- 6.4.1. Built-in Error Functions
- 6.4.2. Combining Multiple Exceptions in a Single Handler
- 6.4.3. Unhandled Exceptions
- 6.4.4. Propagation of Unhandled Exceptions
- 6.4.5. Continuing Past Exceptions
- 6.4.6. Writing WHEN OTHERS Handling Code
- 6.5. Building an Effective Error Management Architecture
- 6.5.1. Decide on Your Error Management Strategy
- 6.5.2. Standardize Handling of Different Types of Exceptions
- 6.5.3. Organize Use of Application-Specific Error Codes
- 6.5.4. Use Standardized Error Management Programs
- 6.5.5. Work with Your Own Exception “Objects”
- 6.5.6. Create Standard Templates for Common Error Handling
- 6.6. Making the Most of PL/SQL Error Management
- 7. Working with Program Data
- 7.1. Naming Your Program Data
- 7.2. Overview of PL/SQL Datatypes
- 7.2.1. Character Data
- 7.2.2. Numbers
- 7.2.3. Dates, Timestamps, and Intervals
- 7.2.4. Booleans
- 7.2.5. Binary Data
- 7.2.6. ROWIDs
- 7.2.7. REF CURSORs
- 7.2.8. Internet Datatypes
- 7.2.9. “Any” Datatypes
- 7.2.10. User-Defined Datatypes
- 7.3. Declaring Program Data
- 7.3.1. Declaring a Variable
- 7.3.2. Declaring Constants
- 7.3.3. The NOT NULL Clause
- 7.3.4. Anchored Declarations
- 7.3.5. Anchoring to Cursors and Tables
- 7.3.6. Benefits of Anchored Declarations
- 7.3.7. Anchoring to NOT NULL Datatypes
- 7.4. Programmer-Defined Subtypes
- 7.5. Conversion Between Datatypes
- 7.5.1. Implicit Data Conversion
- 7.5.2. Explicit Datatype Conversion
- 8. Strings
- 8.1. String Datatypes
- 8.1.1. The VARCHAR2 Datatype
- 8.1.2. The CHAR Datatype
- 8.1.3. String Subtypes
- 8.2. Working with Strings
- 8.2.1. Specifying String Constants
- 8.2.2. Using Nonprintable Characters
- 8.2.3. Concatenating Strings
- 8.2.4. Dealing with Case
- 8.2.5. Traditional Searching, Extracting, and Replacing
- 8.2.6. Padding
- 8.2.7. Trimming
- 8.2.8. Regular Expression Searching, Extracting, and Replacing
- 8.2.9. Working with Empty Strings
- 8.2.10. Mixing CHAR and VARCHAR2 Values
- 8.3. String Function Quick Reference
- 8.1. String Datatypes
- 9. Numbers
- 9.1. Numeric Datatypes
- 9.1.1. The NUMBER Type
- 9.1.2. The PLS_INTEGER Type
- 9.1.3. The BINARY_INTEGER Type
- 9.1.4. The SIMPLE_INTEGER Type
- 9.1.5. The BINARY_FLOAT and BINARY_DOUBLE Types
- 9.1.6. The SIMPLE_FLOAT and SIMPLE_DOUBLE Types
- 9.1.7. Numeric Subtypes
- 9.2. Number Conversions
- 9.2.1. The TO_NUMBER Function
- 9.2.2. The TO_CHAR Function
- 9.2.3. The CAST Function
- 9.2.4. Implicit Conversions
- 9.3. Numeric Operators
- 9.4. Numeric Functions
- 9.4.1. Rounding and Truncation Functions
- 9.4.2. Trigonometric Functions
- 9.4.3. Numeric Function Quick Reference
- 9.1. Numeric Datatypes
- 10. Dates and Timestamps
- 10.1. Datetime Datatypes
- 10.1.1. Declaring Datetime Variables
- 10.1.2. Choosing a Datetime Datatype
- 10.2. Getting the Current Date and Time
- 10.3. Interval Datatypes
- 10.3.1. Declaring INTERVAL Variables
- 10.3.2. When to Use INTERVALs
- 10.4. Datetime Conversions
- 10.4.1. From Strings to Datetimes
- 10.4.2. From Datetimes to Strings
- 10.4.3. Working with Time Zones
- 10.4.4. Requiring a Format Mask to Match Exactly
- 10.4.5. Easing Up on Exact Matches
- 10.4.6. Interpreting Two-Digit Years in a Sliding Window
- 10.4.7. Converting Time Zones to Character Strings
- 10.4.8. Padding Output with Fill Mode
- 10.5. Date and Timestamp Literals
- 10.6. Interval Conversions
- 10.6.1. Converting from Numbers to Intervals
- 10.6.2. Converting Strings to Intervals
- 10.6.3. Formatting Intervals for Display
- 10.7. Interval Literals
- 10.8. CAST and EXTRACT
- 10.8.1. The CAST Function
- 10.8.2. The EXTRACT Function
- 10.9. Datetime Arithmetic
- 10.9.1. Date Arithmetic with Intervals and Datetimes
- 10.9.2. Date Arithmetic with DATE Datatypes
- 10.9.3. Computing the Interval Between Two Datetimes
- 10.9.4. Mixing DATEs and TIMESTAMPs
- 10.9.5. Adding and Subtracting Intervals
- 10.9.6. Multiplying and Dividing Intervals
- 10.9.7. Using Unconstrained INTERVAL Types
- 10.10. Date/Time Function Quick Reference
- 10.1. Datetime Datatypes
- 11. Records
- 11.1. Records in PL/SQL
- 11.1.1. Benefits of Using Records
- 11.1.2. Declaring Records
- 11.1.3. Programmer-Defined Records
- 11.1.4. Working with Records
- 11.1.5. Comparing Records
- 11.1.6. Trigger Pseudo-Records
- 11.1. Records in PL/SQL
- 12. Collections
- 12.1. Collections Overview
- 12.1.1. Collections Concepts and Terminology
- 12.1.2. Types of Collections
- 12.1.3. Collection Examples
- 12.1.4. Where You Can Use Collections
- 12.1.5. Choosing a Collection Type
- 12.2. Collection Methods (Built-ins)
- 12.2.1. The COUNT Method
- 12.2.2. The DELETE Method
- 12.2.3. The EXISTS Method
- 12.2.4. The EXTEND Method
- 12.2.5. The FIRST and LAST Methods
- 12.2.6. The LIMIT Method
- 12.2.7. The PRIOR and NEXT Methods
- 12.2.8. The TRIM Method
- 12.3. Working with Collections
- 12.3.1. Declaring Collection Types
- 12.3.2. Declaring and Initializing Collection Variables
- 12.3.3. Populating Collections with Data
- 12.3.4. Accessing Data Inside a Collection
- 12.3.5. Using String-Indexed Collections
- 12.3.6. Collections of Complex Datatypes
- 12.3.7. Multilevel Collections
- 12.3.8. Working with Collections in SQL
- 12.4. Nested Table Multiset Operations
- 12.4.1. Testing Equality and Membership of Nested Tables
- 12.4.2. Checking for Membership of an Element in a Nested Table
- 12.4.3. Performing High-Level Set Operations
- 12.4.4. Handling Duplicates in a Nested Table
- 12.5. Maintaining Schema-Level Collections
- 12.5.1. Necessary Privileges
- 12.5.2. Collections and the Data Dictionary
- 13. Miscellaneous Datatypes
- 13.1. The BOOLEAN Datatype
- 13.2. The RAW Datatype
- 13.3. The UROWID and ROWID Datatypes
- 13.3.1. Getting ROWIDs
- 13.3.2. Using ROWIDs
- 13.4. The LOB Datatypes
- 13.5. Working with LOBs
- 13.5.1. Understanding LOB Locators
- 13.5.2. Empty Versus NULL LOBs
- 13.5.3. Writing into a LOB
- 13.5.4. Reading from a LOB
- 13.5.5. BFILEs Are Different
- 13.5.6. SecureFiles Versus BasicFiles
- 13.5.7. Temporary LOBs
- 13.5.8. Native LOB Operations
- 13.5.9. LOB Conversion Functions
- 13.6. Predefined Object Types
- 13.6.1. The XMLType Type
- 13.6.2. The URI Types
- 13.6.3. The Any Types
- 12.1. Collections Overview
- 14. DML and Transaction Management
- 14.1. DML in PL/SQL
- 14.1.1. A Quick Introduction to DML
- 14.1.2. Cursor Attributes for DML Operations
- 14.1.3. RETURNING Information from DML Statements
- 14.1.4. DML and Exception Handling
- 14.1.5. DML and Records
- 14.2. Transaction Management
- 14.2.1. The COMMIT Statement
- 14.2.2. The ROLLBACK Statement
- 14.2.3. The SAVEPOINT Statement
- 14.2.4. The SET TRANSACTION Statement
- 14.2.5. The LOCK TABLE Statement
- 14.3. Autonomous Transactions
- 14.3.1. Defining Autonomous Transactions
- 14.3.2. Rules and Restrictions on Autonomous Transactions
- 14.3.3. Transaction Visibility
- 14.3.4. When to Use Autonomous Transactions
- 14.3.5. Building an Autonomous Logging Mechanism
- 14.1. DML in PL/SQL
Online LIVE Training Programming Course offered by INCAPROG ONLINE - www.incaprog.com - contacto@incaprog.com - Phone : (954) 727-3141