totn Oracle / PLSQL

Oracle Tutorial

Oracle is a relational database technology developed by Oracle.

PLSQL stands for "Procedural Language extensions to SQL", and is an extension of SQL that is used in Oracle. PLSQL is closely integrated into the SQL language, yet it adds programming constructs that are not native to SQL.

Our tutorial will start with the basics of Oracle such as how to retrieve and manipulate data. Then we will move to the more advanced topics such as how to create tables, functions, procedures, triggers, tablespaces, and schemas. We will conclude with a review of the functions that are proprietary to Oracle.

With this tutorial, you should be on your way to becoming proficient in Oracle and PLSQL.

Prerequisites

There are no prequisities for this Oracle tutorial. You should be able to easily understand this tutorial and learn the basic concepts of Oracle as you progress to the more advanced topics.

Now, let's get started!

Start Tutorial

Or jump directly to a topic in Oracle/PLSQL:

Oracle/PLSQL Functions

Functions - Alphabetical Oracle/PLSQL Functions listed alphabetically
Functions - Category Oracle/PLSQL Functions listed by category

Oracle Keys, Constraints and Indexes

Primary Keys Create, alter, drop, enable and disable primary keys
Foreign Keys Create, drop, enable and disable foreign keys
Unique Constraints Create, alter, drop, enable and disable unique constraints
Check Constraints Create, alter, drop, enable and disable check constraints
Indexes Create, rename and drop indexes (performance tuning)

Oracle Privileges, Synonyms, Roles and Passwords

Grant/Revoke Privileges Grant or revoke privileges
Synonyms (create, drop) Create, replace and drop synonyms
Roles Set of privileges
Change Password Change a user's password

Oracle Database Administration

Tablespaces Allocated space where schema objects are created
Users Database accounts
Schemas Logically group objects with schemas

Oracle Programming

Functions Create and drop functions
Procedures Create and drop procedures
Triggers Create, drop, enable, and disable triggers
Cursors Create cursors within stored programs
Literals (Constants) Text, integer and number literals
Declaring Variables Declare variables and constants
Exception Handling Exception handling in code
Loops and Conditional Statements FOR Loop, WHILE Loop, IF-THEN-ELSE and others
Sequences (Autonumber) Create and drop sequences
Transactions Commits and rollbacks
Comments within SQL How to create comments within your SQL statement

Oracle Error Messages

Oracle Error Messages Oracle error message explanations and resolutions

Oracle System Tables

Oracle System Tables Oracle system tables and descriptions

Oracle Comparison Operators

Comparison Operators Operators such as =, <>, !=, >, <, and so on

Oracle Query Types

SELECT Statement Retrieve records from a table
INSERT Statement Insert records into a table
INSERT ALL Statement Insert multiple records into one or more tables
UPDATE Statement Update records in a table
DELETE Statement Delete records from a table
TRUNCATE TABLE Statement Delete all records from a table (no rollback)
UNION Operator Combine 2 result sets (removes duplicates)
UNION ALL Operator Combine 2 result sets (includes duplicates)
INTERSECT Operator Intersection of 2 result sets
MINUS Operator Result set of one minus the result set of another
Subqueries A query within a query
PIVOT Clause Create a cross-tabulation query

Oracle Joins

JOIN Tables Inner and Outer joins

Oracle Aliases

ALIASES Create a temporary name for a column or table

Oracle Clauses

DISTINCT Clause Retrieve unique records
FROM Clause List tables and join information
WHERE Clause Filter results
ORDER BY Clause Sort query results
GROUP BY Clause Group by one or more columns
HAVING Clause Restrict the groups of returned rows

Oracle SQL Functions

COUNT Function Return the count of an expression
SUM Function Return the sum of an expression
MIN Function Return the min of an expression
MAX Function Return the max of an expression
AVG Function Return the average of an expression

Oracle Conditions

AND Condition 2 or more conditions to be met
OR Condition Any one of the conditions are met
AND and OR Combine AND and OR conditions
LIKE Condition Use simple pattern matching (wildcards) in a WHERE clause
REGEXP_LIKE Condition Use regular expression matching in a WHERE clause
IN Condition Alternative to multiple OR conditions
NOT Condition Negate a condition
IS NULL Condition Test for a NULL value
IS NOT NULL Condition Test for a NOT NULL value
BETWEEN Condition Retrieve within a range (inclusive)
EXISTS Condition Condition is met if subquery returns at least one row

Oracle Tables and Views

CREATE TABLE Create a table
CREATE TABLE AS Create a table from another table's definition and data
ALTER TABLE Add, modify or delete columns in a table; rename a table
DROP TABLE Delete a table
GLOBAL TEMP Tables Tables that are distinct within SQL session
LOCAL TEMP Tables Tables that are distinct within modules and embedded SQL program
VIEW Virtual tables (views of other tables)

Oracle Data Types

Data Types Data Types in Oracle/PLSQL

Oracle Question and Answer

Question & Answer Various questions and answers