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!
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 |
No comments:
Post a Comment