1/17
Contents
List of Examples
List of Tables
Title and Copyright Information
Preface
Audience
Documentation Accessibility
Related Documents
Conventions
Changes in This Release for Oracle Database Express Edition 2 Day Developer's Guide
Changes in Oracle Database 11
g
Release 2 (11.2.0.2)
1
Introduction
About Oracle Database Express Edition Developers
About This Document
About Oracle Database Express Edition
About Schema Objects
About Oracle Database Express Edition Access
About SQL*Plus
About SQL Developer
About Structured Query Language (SQL)
About Procedural Language/SQL (PL/SQL)
About Other Client Programs, Languages, and Development Tools
About Sample Schema HR
2
Connecting to Oracle Database Express Edition and Exploring It
Connecting to Oracle Database Express Edition from SQL*Plus
Connecting to Oracle Database Express Edition from SQL Developer
Connecting to Oracle Database Express Edition as User HR
Unlocking the HR Account
Connecting to Oracle Database Express Edition as User HR from SQL*Plus
Connecting to Oracle Database Express Edition as User HR from SQL Developer
Exploring Oracle Database Express Edition with SQL*Plus
Viewing HR Schema Objects with SQL*Plus
Viewing EMPLOYEES Table Properties and Data with SQL*Plus
Exploring Oracle Database Express Edition with SQL Developer
Tutorial: Viewing HR Schema Objects with SQL Developer
Tutorial: Viewing EMPLOYEES Table Properties and Data with SQL Developer
Selecting Table Data
About Queries
Running Queries in SQL Developer
Tutorial: Selecting All Columns of a Table
Tutorial: Selecting Specific Columns of a Table
Displaying Selected Columns Under New Headings
Selecting Data that Satisfies Specified Conditions
Sorting Selected Data
Selecting Data from Multiple Tables
Using Operators and Functions in Queries
Using Arithmetic Operators in Queries
Using Numeric Functions in Queries
Using the Concatenation Operator in Queries
Using Character Functions in Queries
Using Datetime Functions in Queries
Using Conversion Functions in Queries
Using Aggregate Functions in Queries
Using NULL-Related Functions in Queries
Using CASE Expressions in Queries
Using the DECODE Function in Queries
3
About DML Statements and Transactions
About Data Manipulation Language (DML) Statements
About the INSERT Statement
About the UPDATE Statement
About the DELETE Statement
About Transaction Control Statements
Committing Transactions
Rolling Back Transactions
Setting Savepoints in Transactions
4
Creating and Managing Schema Objects
About Data Definition Language (DDL) Statements
Creating and Managing Tables
About SQL Data Types
Creating Tables
Tutorial: Creating a Table with the Create Table Tool
Creating Tables with the CREATE TABLE Statement
Ensuring Data Integrity in Tables
About Constraint Types
Tutorial: Adding Constraints to Existing Tables
Tutorial: Adding Rows to Tables with the Insert Row Tool
Tutorial: Changing Data in Tables in the Data Pane
Tutorial: Deleting Rows from Tables with the Delete Selected Row(s) Tool
Managing Indexes
Tutorial: Adding an Index with the Create Index Tool
Tutorial: Changing an Index with the Edit Index Tool
Tutorial: Dropping an Index
Dropping Tables
Creating and Managing Views
Creating Views
Tutorial: Creating a View with the Create View Tool
Creating Views with the CREATE VIEW Statement
Changing Queries in Views
Tutorial: Changing View Names with the Rename Tool
Dropping Views
Creating and Managing Sequences
Tutorial: Creating a Sequence
Dropping Sequences
Creating and Managing Synonyms
Creating Synonyms
Dropping Synonyms
5
Developing Stored Subprograms and Packages
About Stored Subprograms
About Packages
About PL/SQL Identifiers
About PL/SQL Data Types
Creating and Managing Standalone Subprograms
About Subprogram Structure
Tutorial: Creating a Standalone Procedure
Tutorial: Creating a Standalone Function
Changing Standalone Subprograms
Tutorial: Testing a Standalone Function
Dropping Standalone Subprograms
Creating and Managing Packages
About Package Structure
Tutorial: Creating a Package Specification
Tutorial: Changing a Package Specification
Tutorial: Creating a Package Body
Dropping a Package
Declaring and Assigning Values to Variables and Constants
Tutorial: Declaring Variables and Constants in a Subprogram
Ensuring that Variables, Constants, and Parameters Have Correct Data Types
Tutorial: Changing Declarations to Use the %TYPE Attribute
Assigning Values to Variables
Assigning Values to Variables with the Assignment Operator
Assigning Values to Variables with the SELECT INTO Statement
Controlling Program Flow
About Control Statements
Using the IF Statement
Using the CASE Statement
Using the FOR LOOP Statement
Using the WHILE LOOP Statement
Using the Basic LOOP and EXIT WHEN Statements
Using Records and Cursors
About Records
Tutorial: Declaring a RECORD Type
Tutorial: Creating and Invoking a Subprogram with a Record Parameter
About Cursors
Using a Declared Cursor to Retrieve Result Set Rows One at a Time
Tutorial: Using a Declared Cursor to Retrieve Result Set Rows One at a Time
About Cursor Variables
Using a Cursor Variable to Retrieve Result Set Rows One at a Time
Tutorial: Using a Cursor Variable to Retrieve Result Set Rows One at a Time
Using Associative Arrays
About Collections
About Associative Arrays
Declaring Associative Arrays
Populating Associative Arrays
Traversing Dense Associative Arrays
Traversing Sparse Associative Arrays
Handling Exceptions (Runtime Errors)
About Exceptions and Exception Handlers
When to Use Exception Handlers
Handling Predefined Exceptions
Declaring and Handling User-Defined Exceptions
6
Using Triggers
About Triggers
Creating Triggers
About OLD and NEW Pseudorecords
Tutorial: Creating a Trigger that Logs Table Changes
Tutorial: Creating a Trigger that Generates a Primary Key for a Row Before It Is Inserted
Creating an INSTEAD OF Trigger
Tutorial: Creating Triggers that Log LOGON and LOGOFF Events
Changing Triggers
Disabling and Enabling Triggers
Disabling or Enabling a Single Trigger
Disabling or Enabling All Triggers on a Single Table
About Trigger Compilation and Dependencies
Dropping Triggers
7
Working in a Global Environment
About Globalization Support Features
About Language Support
About Territory Support
About Date and Time Formats
About Calendar Formats
About Numeric and Monetary Formats
About Linguistic Sorting and String Searching
About Length Semantics
About Unicode and SQL National Character Data Types
About Initial NLS Parameter Values
Viewing NLS Parameter Values
Changing NLS Parameter Values
Changing NLS Parameter Values for All SQL Developer Connections
Changing NLS Parameter Values for the Current SQL Function Invocation
About Individual NLS Parameters
About Locale and the NLS_LANG Parameter
About the NLS_LANGUAGE Parameter
About the NLS_TERRITORY Parameter
About the NLS_DATE_FORMAT Parameter
About the NLS_DATE_LANGUAGE Parameter
About NLS_TIMESTAMP_FORMAT and NLS_TIMESTAMP_TZ_FORMAT Parameters
About the NLS_CALENDAR Parameter
About the NLS_NUMERIC_CHARACTERS Parameter
About the NLS_CURRENCY Parameter
About the NLS_ISO_CURRENCY Parameter
About the NLS_DUAL_CURRENCY Parameter
About the NLS_SORT Parameter
About the NLS_COMP Parameter
About the NLS_LENGTH_SEMANTICS Parameter
Using Unicode in Globalized Applications
Representing Unicode String Literals in SQL and PL/SQL
Avoiding Data Loss During Character-Set Conversion
8
Building Effective Applications
Building Scalable Applications
About Scalable Applications
Using Bind Arguments to Improve Scalability
Using PL/SQL to Improve Scalability
How PL/SQL Minimizes Parsing
About the EXECUTE IMMEDIATE Statement
About OPEN FOR Statements
About the DBMS_SQL Package
About Bulk SQL
About Concurrency and Scalability
About Sequences and Concurrency
About Latches and Concurrency
About Nonblocking Reads and Writes and Concurrency
About Shared SQL and Concurrency
Limiting the Number of Concurrent Sessions
Comparing Programming Techniques with Runstats
About Runstats
Setting Up Runstats
Using Runstats
Recommended Programming Practices
Use Instrumentation Packages
Statistics Gathering and Application Tracing
Use Existing Functionality
Cover Database Tables with Editioning Views
Recommended Security Practices
9
Developing a Simple Oracle Database Express Edition Application
About the Application
Purpose of the Application
Structure of the Application
Schema Objects of the Application
Schemas for the Application
Naming Conventions in the Application
Creating the Schemas for the Application
Granting Privileges to the Schemas
Granting Privileges to the app_data Schema
Granting Privileges to the app_code Schema
Granting Privileges to the app_admin Schema
Granting Privileges to the app_user and app_admin_user Schemas
Creating the Schema Objects and Loading the Data
Creating the Tables
Creating the Editioning Views
Creating the Triggers
Creating the Trigger to Enforce the First Business Rule
Creating the Trigger to Enforce the Second Business Rule
Creating the Sequences
Loading the Data
Adding the Foreign Key Constraint
Granting Privileges on the Schema Objects to Users
Creating the employees_pkg Package
Creating the Package Specification for employees_pkg
Creating the Package Body for employees_pkg
Tutorial: Showing How the employees_pkg Subprograms Work
Granting the Execute Privilege to app_user and app_user_admin
Tutorial: Invoking get_job_history as app_user or app_admin_user
Creating the admin_pkg Package
Creating the Package Specification for admin_pkg
Creating the Package Body for admin_pkg
Tutorial: Showing How the admin_pkg Subprograms Work
Granting the Execute Privilege to app_user_admin
Tutorial: Invoking add_department as app_admin_user
10
Deploying an Oracle Database Express Edition Application
About Development and Deployment Environments
About Installation Scripts
About DDL Statements and Schema Object Dependencies
About INSERT Statements and Constraints
Creating Installation Scripts
Creating Installation Scripts with the Cart
Creating an Installation Script with the Database Export Wizard
Editing Installation Scripts that Create Sequences
Editing Installation Scripts that Create Triggers
Creating Installation Scripts for the Sample Application
Creating Installation Script schemas.sql
Creating Installation Script objects.sql
Creating Installation Script employees.sql
Creating Installation Script admin.sql
Creating Master Installation Script create_app.sql
Deploying the Sample Application
Checking the Validity of an Installation
Archiving the Installation Scripts
Index
Scripting on this page enhances content navigation, but does not change the content in any way.