Topic summary

This section from Introduction to Oracle SQL and SQL*Plus   (Jump to PL/SQL Topics)

• Welcome to Introduction to Oracle9i: SQL and SQL*Plus Workshop
  • Target Audience
  • Course Objectives
  • Course Book Layout
  • Conventions Used
  • Preparing for Certification
  • Getting Help
  • Additional Learning
• Introduction to Oracle9i
  • What is the Oracle Database?
  • Principal Features: SQL Support
  • Principal Features: Oracle Objects
  • Principal Features: Concurrency & Transaction Support
  • Principal Features: Security
  • Principal Features: High Availability
  • More Principal Features
  • Principal Features for XML and Large Objects
  • Principal Features: Utilities
  • DDL Statements
  • DML Statements
  • Enterprise Edition
  • Standard & Personal Editions
  • Oracle8 i Releases
  • Oracle9i Releases
  • Oracle9i New Features
  • Oracle 10g
  • Oracle 10g New Features
  • Related Products
  • Intro to Oracle9i Workshop A
  • Oracle Architecture
  • Common Objects
  • Intro to Oracle9i Workshop B
• Introduction to SQL*Plus
  • Development Environment
  • Connect to SQL*Plus
  • SQL*Plus Describe Command
  • SQL*Plus Connect Command
  • SQL*Plus Host Command
  • SQL SELECT Statement
  • Dual: The Oracle Work Table
  • Editing in SQL*Plus
  • Listing the Buffer Contents
  • Editing the Buffer Contents
  • Editor Commands
  • SQL*Plus Edit Command
  • Related SQL*Plus Commands
  • Editing: A Better Way
  • Running SQL*Plus Scripts
  • Exit from SQL*Plus
  • SQL*Plus Workshop
• Data Access Basics
  • Simplified SELECT Statement
  • SELECT Column List
  • SELECT DISTINCT
  • Calculated Columns
  • Column Aliases
  • Concatenated Columns
  • Sorting: Order By
  • Sorting by Calculated Columns
  • Sorting by Column Alias
  • Sorting by Multiple Columns
  • Comparison Operator =
  • Comparison Operator >
  • Available Comparison Operators
  • Logical Operator AND
  • Available Logical Operators
  • Data Access Basics Workshop
• Single Row Functions & Pseudo Columns
  • Intro to Oracle Functions
  • Analytic Functions
  • Single-Row Functions
  • Character Functions
  • LOWER Function
  • CONCAT Function
  • TRIM Function
  • SUBSTR/INSTR Functions
  • SOUNDEX Function
  • TRANSLATE Function
  • Numeric Functions
  • ROUND Function
  • TRUNC Function
  • Datetime Functions
  • Time Zones
  • CURRENT_x Functions
  • TRUNC Function
  • Date Arithmetic
  • DATE +/- NUMBER
  • DATE
  • INTERVAL Types
  • ADD_MONTHS Function
  • Conversion Functions
  • TO_CHAR Function
  • Datetime Conversion
  • TO_CHAR Function Example
  • TO_CHAR Example
  • TO_DATE Function
  • TO_TIMESTAMP Function
  • USER and SYSDATE
  • NULLIF Function
  • NVL Function
  • DECODE Function
  • Pseudo Columns
  • Functions Workshop
• Advanced Data Access
  • Conditional Operators
  • Search Lists: IN
  • Search Range : BETWEEN
  • Search Patterns: LIKE
  • LIKE Operator Using Multiple Wildcards
  • LIKE Operator with Escape
  • Introduction to Nulls
  • Selecting Rows with Null Values
  • IS NULL Operator
  • CASE Expression
  • Joining Tables
  • Join Diagram
  • Join Example
  • Unqualified Names in Joins
  • Table Aliases in Joins
  • 4-Table Join
  • Outer Joins
  • ANSI Compliant Joins
  • ANSI Natural Join
  • ANSI Inner Join
  • ANSI 4-Table Join
  • ANSI outer joins
  • Full outer joins
  • ANSI cross join
  • Subquery
  • Subquery Example
  • Subquery Restrictions
  • Subquery Rules
  • Subquery IN Operator
  • Subquery EXISTS Operator
  • NOT EXISTS Example
  • Subquery in FROM Clause
  • Extended Subquery Support
  • UNION , INTERSECT and MINUS Operators
  • UNION Example
  • INTERSECT Example
  • MINUS Example
  • Advanced Data Access Workshop
• SQL*Plus Reporting
  • Creating SQL*Plus Reports
  • SQL*Plus Sample Report Script
  • Executing SQL*Plus Reports
  • Report Output
  • Spooling Output
  • Controlling the SQL*Plus Environment
  • Set Commands
  • Customizing Your SQL*Plus Environment
  • Page Header: Ttitle
  • Header and Footer System Variables
  • Multi-Line ttitle
  • Reformatting Columns
  • COLUMN Example
  • Displaying Current Column Settings
  • Numeric Format Patterns
  • SQL*Plus BREAK Command
  • SQL*Plus COMPUTE Command
  • SQL*Plus Reporting Workshop
• Data Manipulation & Transaction Control
  • Inserting Rows
  • Rounding on Insert
  • Returning Values from DML
  • Inserting Multiple Rows
  • 9i Multi-Table INSERT
  • Deleting Rows
  • TRUNCATE Command
  • Updating a Single Row
  • Updating Multiple Rows
  • MERGE Statement
  • Transaction Control
  • COMMIT & ROLLBACK
  • Transaction Control Who Sees What?
  • Transaction Control Locking Mechanisms
  • Savepoints
  • Commits in SQL*Plus
  • Data Manipulation Workshop
• Tables and Constraints
  • Object Names
  • Naming Rules
  • Creating Tables: Syntax
  • Creating Tables: Example
  • Creating Tables: Dictionary
  • Creating Tables: DESCRIBE
  • Built-In Datatypes
  • CREATE TABLE AS SELECT
  • CTAS Options
  • Introduction to Constraints
  • Constraint Names
  • Constraints Example
  • Disabling Constraints
  • Enabling Constraints
  • Constraints and the Data Dictionary
  • Altering Table Structure
  • Adding Columns
  • Adding Constraints
  • DROP COLUMN
  • Dropping Tables
  • Tables and Constraints Workshop
• Indexes
  • Introduction to Indexes
  • B-Tree Indexes
  • B-Tree Structure
  • Index Creation
  • Function-Based Indexes
  • Descending Indexes
  • Cost Based Optimization
  • Indexes Workshop
• Grouping Data
  • Group Functions
  • MIN, MAX, SUM, COUNT, AVG
  • Group Functions with Nulls
  • Summary Grouping
  • GROUP BY Clause
  • Grouping Multiple Columns
  • Golden GROUP BY Rule
  • WHERE Clause Restrictions
  • HAVING Clause
  • HAVING Clause Example
  • ROLLUP
  • ROLLUP Example
  • CUBE
  • CUBE Example
  • GROUPING Function
  • GROUPING Example
  • DECODE and GROUPING
  • Grouping Workshop
• Views and Synonyms
  • What is a View?
  • Views Concept Diagram
  • What Are Views Used For?
  • Creating Views: Hiding Sensitive Columns
  • Creating Views: Simplify Table Access
  • Creating Views: Using Column Aliases
  • Updateable Views
  • Read Only Views
  • WITH CHECK OPTION
  • WITH CHECK OPTION Example
  • Views & The Data Dictionary
  • What is a Synonym?
  • Private Synonyms
  • Public Synonyms
  • CREATE SYNONYM Examples
  • Synonym Search Sequence
  • Synonyms & The Data Dictionary
  • Views and Synonyms Workshop
• Other Database Objects
  • Privileges
  • Roles - Concepts
  • Roles
  • Determining Privileges
  • Sequences
  • Caching of Sequences
  • Sequences - Uses
  • Referencing Sequences
  • Using a Sequence to Generate a Primary Key
  • Sequences & The Data Dictionary
  • Other Database Objects Workshop A
  • Objects
  • Object Example
  • What are Collections?
  • What is a VARRAY?
  • VARRAY Example
  • Multi-level collections
  • What is a Nested Table?
  • Nested Table Example
  • Temporary Table Concepts
  • Creating Temporary Tables
  • Temporary Table Limitations
  • Other Database Objects Workshop B
  • External Tables - Concepts
  • Using External Tables
  • Other Database Objects Workshop C
• Appendix A - Course workshop Table Descriptions
• Appendix B - The Oracle Data Dictionary
  • The Oracle Data Dictionary
  • DICT Table
  • Finding Objects in the Data Dictionary
  • Frequently Used Dictionary Views
• Appendix C - Online Only - Intro to Relational Concepts
  • Relational Model of Data
  • Key Concepts
  • Data Structure: Two dimensional tables
  • What is a join?
  • Data Integrity
  • Entity Integrity
  • Primary Keys
  • Referential Integrity
  • Domain Integrity
  • A Word About Nulls
  • SQL Concepts
  • SQL Examples
  • SQL Terminology
  • Partial List of DDL Statements
  • Partial List of DML Statements
  • Relational Database Design
  • Entities Defined
  • Attributes Defined
  • Relationships Defined
  • Many-to-Many Relationship
  • Normalizing Data
  • Normal Forms
  • Schema
  • Intro to Relational Concepts Workshop
• Appendix D - Online Only - Advanced SQL*Plus Scripting
  • Scripts and Parameters
  • Positional Substitution Variables Example
  • Using &&
  • Executing Scripts
  • Naming Substitution Variables
  • Named Substitution Variables Example
  • Define/Undefine Substitution Variables
  • Creating Substitution Variables
  • Displaying Substitution Variables
  • Talking to Your Scripts
  • Accept & Prompt Script Example
  • Executing a Script Using Accept & Prompt
  • SQL*Plus COPY Command
  • COPY Command Options
  • COPY Command Transaction Size
  • COPY Example
  • Batch Processing
  • Creating SQL*Plus Batch Scripts
  • Creating a Windows Batch Program
  • SQL Script for UNIX
  • UNIX Shell Script
  • Using SQL*Plus as a Code Generator
  • 5 Steps to Generate Code Using SQL*Plus
  • Advanced SQL*Plus Scripting Workshop

Topic summary from PL/SQL Programming      (Jump back to to Intro Topics)

• Introduction to PL/SQL
  • What is PL/SQL?
  • Why Use PL/SQL?
  • PL/SQL Program Structure
  • Anonymous Blocks
  • Compile Errors
  • Output to SQL*Plus
  • Procedures
  • Procedure Compile Errors
  • Functions
  • Packages
  • Data Dictionary
  • Triggers
  • Tools for Development
  • Working in SQL*Plus
  • Introduction to PL/SQL Workshops
• Language Fundamentals
  • Statements
  • PL/SQL Symbols
  • Common PL/SQL Datatypes
  • Declaring Variables
  • %TYPE
  • PL/SQL Records
  • Records: %ROWTYPE
  • Programmer Defined Records
  • Language Fundamentals Workshop A
  • Variable Scope
  • Nested Blocks
  • Functions
  • IF Statement
  • CASE
  • Simple CASE
  • Searched CASE
  • Simple Loops
  • Nested Loops
  • Numeric FOR Loop
  • PL/SQL Arrays
  • Simple Array Example
  • Array Methods
  • Language Fundamentals Workshop B
• PL/SQL and SQL: Basics
  • SELECT INTO Statement
  • Implicit Cursor Loops
  • Basics Workshop A (Cursor Loop)
  • DML in PL/SQL
  • Cursor Attributes
  • Embedding DDL
  • Basics Workshop B (NDS)
• PL/SQL and SQL: Cursors
  • What is a Cursor?
  • Cursor Processing
  • Cursor Attributes
  • Cursors Workshop A
  • Dynamic Cursors
  • Dynamic Cursor Example
  • Variable Scope & Cursors
  • Problem Example
  • Cursor Parameters
  • Cursor Record
  • Cursor FOR LOOP
  • FOR LOOP Example
  • Cursors Optional Workshop B
  • Referencing the Current Row
  • FOR UPDATE Example
  • Cursors Optional Workshop C
• PL/SQL and SQL: Bulk Processing
  • Bulk Processing
  • Bulk Collect
  • Bulk Collect: SELECT INTO
  • Bulk Collect: Multiple Arrays
  • Bulk Collect: Array of Records
  • Bulk Collect: FETCH with LIMIT
  • Bulk Processing Workshop A
  • Bulk DELETE
  • Bulk INSERT
  • Returning into Arrays
  • Summary
  • Bulk Processing Workshop B
• Procedures
  • What is a Procedure?
  • Abbreviated Syntax
  • Simple Example
  • Review: Compile Errors
  • Procedure Signatures
  • Calling Procedures
  • Procedure Synonyms
  • Referencing Parameters by Name
  • Returning Sets: REF CURSOR
  • Dropping Procedures
  • Dependencies
  • ALTER COMPILE
  • Privileges
  • Execute Privilege
  • The Data Dictionary
  • Procedures Workshop
• Functions
  • What is a Function?
  • Abbreviated Syntax
  • Simple Example
  • Calling Functions
  • The Data Dictionary
  • Functions Workshop A
  • Table Functions
  • Building a Table Function
  • Using a Table Function
  • Pipelined Functions
  • Pipelined Example
  • Test Data Generator
  • Functions Workshop B
•  Error Handling
  • Types of Errors
  • Runtime Errors
  • Exception Concepts
  • Predefined Named Exceptions
  • Syntax
  • Handling Exceptions
  • Recovering from Errors
  • Logging Exceptions
  • RAISE_APPLICATION_ERROR
  • RAE Example
  • Error Handling Workshop
  • Raising Exceptions
  • Raising User Exceptions
  • EXCEPTION_INIT
  • Summary
• Packages
  • Concepts
  • Package Benefits
  • Package Benefits Diagram
  • Package Contents
  • Package Specification Syntax
  • Package Specification Example
  • Package Body Syntax
  • Package Body Example
  • Privileges
  • Calling Packaged Objects
  • Initialization Code
  • Session Variables
  • Package Body Variables
  • Packages Workshop A
  • Package Overloading
  • Overloading Example
  • Packages Workshop B
  • Compiling Packages
  • DROP Package
  • Packages Workshop C
  • Supplied Packages
  • Summary
• Triggers
  • Trigger Concepts
  • Trigger Execution
  • Create Trigger Syntax
  • Row Trigger
  • :OLD Reference Variable
  • :NEW Reference Variable
  • Trigger Attributes
  • Audit Trigger
  • Derived Value Trigger
  • Restrictions
  • Security Privileges
  • Triggers Workshop A
  • Statement Level Trigger
  • Triggers Workshop B
  • Autonomous Transactions
  • Autonomous Trigger
  • Triggers Workshop C
  • INSTEAD OF Triggers
  • Sybase / MS SQL Server vs. Oracle Triggers
  • Triggers Workshop D
• File I/O Using UTL_FILE
  • UTL_FILE Concepts
  • Setup
  • Unix File Permissions
  • Opening, closing, reading and Writing Files
  • Writing Files - PUT
  • Writing Files - PUT_LINE
  • Writing Files - NEW_LINE
  • Writing Files - PUTF
  • Writing Files Example
  • UTL_FILE Workshop
• Using DBMS_JOB
  • What is DBMS_JOB?
  • Setup
  • DBMS_JOB.SUBMIT
  • SUBMIT Example
  • Running a Job
  • Cleaning Up Broken Jobs
  • Removing a Job
  • Changing Job Parameters
  • Viewing Job Information
  • Using DBMS_JOB Workshop
• Profile and Tune
  • Introduction to Tuning PL/SQL
  • DBMS_PROFILER
  • Profiler Setup
  • Using Profiler
  • Testing
  • Reporting
  • Profile & Tune PLSQL Workshop
• Optional Appendices: Dynamic SQL
  • What is Dynamic SQL?
  • NDS vs. DBMS_SQL
  • NDS Statement Summary
  • NDS Single-Row Query Syntax
  • NDS Single-Row INSERT Example
  • NDS Multi-Row Query Syntax
  • NDS Multi-Row Query Example
  • NDS PL/SQL Example
  • DBMS_SQL Procedures Summary
  • DBMS_SQL Example
  • Privileges
  • Dynamic SQL Workshop