Scribd
Upload
21 views

oracle 10g syllabus

Uploaded by

sreeni.thummana
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
21 views

oracle 10g syllabus

Uploaded by

sreeni.thummana
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
You are on page 1/ 5

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

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
 Using iSQL*Plus

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
 The LIKE Operator
 Creating Some Order

Scalar Functions

 SQL Functions
 Using SQL Functions
 String Functions
 Numeric Functions
 Date Functions
 Conversion Functions
 Date Formats
 Oracle Pseudocolumns

SQL Queries - Joins


 Selecting from Multiple Tables
 Joining Tables
 Self Joins
 Outer Joins
 Types of Outer Joins

Aggregate Functions and Advanced Techniques

 Subqueries
 Correlated Subqueries
 The EXISTS Operator
 The Aggregate Functions
 Grouping Rows
 Combining SELECT Statements

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

Data Definition and Control Statements

 Standard Datatypes
 Defining Tables
 Constraints
 Inline Constraints
 Modifying Table Definitions
 Deleting a Table Definition
 Controlling Access to Your Tables

Other Database Objects

 Views
 Creating Views
 Updateable Views
 Sequences
 Synonyms

Triggers
 Beyond Declarative Integrity
 Triggers
 Types of Triggers
 Row-Level Triggers
 Cascading Triggers and Mutating Tables
 Generating an Error
 Triggers on Views
 System Triggers
 Maintaining Triggers

PL/SQL Variables and Datatypes

 Declaring Variables
 Datatypes
 Subtypes
 Character Data
 Dates and Timestamps
 Date Intervals
 Anchored Types
 Assignment and Conversions
 Selecting into a Variable

PL/SQL Syntax and Logic

 PL/SQL Blocks and Programs


 Conditional Statements – IF/THEN
 Conditional Statements – CASE
 Comments and Labels
 Loops
 WHILE and FOR Loops

Stored Procedures and Functions

 Stored Subprograms
 Procedures and Functions
 Creating a Stored Procedure
 Calling a Stored Procedure
 Passing Parameters and Default Arguments
 Parameter Modes
 Creating a Stored Function
 Calling a Stored Function
 Stored Functions and SQL
 Local Procedures and Functions

Exception Handling
 SQLCODE and SQLERRM
 Exception Handlers
 Nesting Blocks
 Scope and Name Resolution
 User-Defined Exceptions
 Compile-Time Warnings

Records and Collections

 Record Variables
 Using the %ROWTYPE Attribute
 VARRAY and Nested TABLE Collections
 Using Nested TABLEs
 Using VARRAYs
 Associative Array Collections
 Collection Methods
 Iterating Through Collections
 FORALL Statement
 BULK COLLECT Clause

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

Using Packages

 Packages
 Oracle-Supplied Packages
 The DBMS_OUTPUT Package
 The DBMS_UTILITY Package
 The UTL_FILE Package
 The DBMS_METADATA Package
 XML Packages
 Networking Packages
 Other Supplied Packages

Creating Packages

 Structure of a Package
 The Package Interface and Implementation
 Package Variables
 Overloading Package Functions and Procedures
 Named Parameter Notation
 REF CURSOR Variables

Working with LOBs

 Large Object Types


 Oracle Directories
 LOB Locators
 Internal LOBs
 External LOBs
 Temporary LOBs
 The DBMS_LOB Package

Maintaining PL/SQL Code

 Privileges for Stored Programs


 PL/SQL Stored Program Compilation
 The PL/SQL Execution Environment
 Dependencies and Validation
 Maintaining Stored Programs

Appendix A - The Data Dictionary

 Introducing the Data Dictionary


 DBA, ALL, and USER Data Dictionary Views
 Some Useful Data Dictionary Queries

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

You might also like