# Oracle Database/SQL Cheatsheet

This "cheat sheet" covers most of the basic functionality that an Oracle DBA needs to run basic queries and perform basic tasks. It also contains information that a PL/SQL programmer frequently uses to write stored procedures. The resource is useful as a primer for individuals who are new to Oracle, or as a reference for those who are experienced at using Oracle.
A great deal of information about Oracle exists throughout the net. We developed this resource to make it easier for programmers and DBAs to find most of the basics in one place. Topics beyond the scope of a "cheatsheet" generally provide a link to further research.
Other Oracle References

### SELECT INTO

Select into takes the values name, address and phone number out of the table employee, and places them into the variables v_employee_name, v_employee_address, and v_employee_phone_number.
This only works if the query matches a single item. If the query returns no rows it raises the NO_DATA_FOUND built-in exception. If your query returns more than one row, Oracle raises the exception TOO_MANY_ROWS.

### INSERT

The INSERT statement adds one or more new rows of data to a database table.
insert using the VALUES keyword
insert using a SELECT statement

### DELETE

The DELETE statement is used to delete rows in a table.
deletes rows that match the criteria

### UPDATE

The UPDATE statement is used to update rows in a table.
updates the entire column of that table
updates the specific record of the table eg:
updates the column invoice as paid when paid column has more than zero.

### SEQUENCES

Sequences are database objects that multiple users can use to generate unique integers. The sequence generator generates sequential numbers, which can help automatically generate unique primary keys, and coordinate keys across multiple rows or tables.

#### CREATE SEQUENCE

The syntax for a sequence is:
For example:

#### ALTER SEQUENCE

Increment a sequence by a certain amount:
Change the maximum value of a sequence:
Set the sequence to cycle or not cycle:
Configure the sequence to cache a value:
Set whether or not to return the values in order

### Generate query from a string

It is sometimes necessary to create a query from a string. That is, if the programmer wants to create a query at run time (generate an Oracle query on the fly), based on a particular set of circumstances, etc.
Care should be taken not to insert user-supplied data directly into a dynamic query string, without first vetting the data very strictly for SQL escape characters; otherwise you run a significant risk of enabling data-injection hacks on your code.
Here is a very simple example of how a dynamic query is done. There are, of course, many different ways to do this; this is just an example of the functionality.

### String operations
#### Length

Length returns an integer representing the length of a given string. It can be referred to as: length b, length c, length 2, and length 4.

#### Instr

Instr (in string) returns an integer that specifies the location of a sub-string within a string. The programmer can specify which appearance of the string they want to detect, as well as a starting position. An unsuccessful search returns 0.

#### Replace

Replace looks through a string, replacing one string with another. If no other string is specified, it removes the string specified in the replacement string parameter.

#### Substr

Substr (substring) returns a portion of the given string. The "start_position" is 1-based, not 0-based. If "start_position" is negative, substr counts from the end of the string. If "length" is not given, substr defaults to the remaining length of the string.
substr( string, start_position [, length])
    returns "pl/sql" since the "p" in "pl/sql" is in the 8th position in the string (counting from 1 at the "o" in "oracle")
    returns "cheatsheet" since "c" is in the 15th position in the string and "t" is the last character in the string.
    returns "cheat" since "c" is the 10th character in the string, counting from the end of the string with "t" as position 1.

#### Trim

These functions can be used to filter unwanted characters from strings. By default they remove spaces, but a character set can be specified for removal as well.

## DDL SQL
### Tables
#### Create table

The syntax to create a table is:
For example:

#### Add column

The syntax to add a column is:
For example:

#### Modify column

The syntax to modify a column is:
ALTER table syntax and examples:
For example:

#### Drop column

The syntax to drop a column is:
For example:

#### Constraints
##### Constraint types and codes

| Type Code | Type Description | Acts On Level |
| C | Check on a table | Column |
| O | Read Only on a view | Object |
| P | Primary Key | Object |
| R | Referential AKA Foreign Key | Column |
| U | Unique Key | Column |
| V | Check Option on a view | Object |

##### Displaying constraints

The following statement shows all constraints in the system:

##### Selecting referential constraints

The following statement shows all referential constraints (foreign keys) with both source and destination table/column couples:

##### Setting constraints on a table

The syntax for creating a check constraint using a CREATE TABLE statement is:
For example:

##### Unique Index on a table

The syntax for creating a unique constraint using a CREATE TABLE statement is:
For example:

##### Adding unique constraints

The syntax for a unique constraint is:
For example:

##### Adding foreign constraints

The syntax for a foregin constraint is:
For example:

##### Deleting constraints

The syntax for dropping (removing) a constraint is:
For example:

### INDEXES

An index is a method that retrieves records with greater efficiency. An index creates an entry for each value that appears in the indexed columns. By default, Oracle creates B-tree indexes.

#### Create an index

The syntax for creating an index is:
UNIQUE indicates that the combination of values in the indexed columns must be unique.
COMPUTE STATISTICS tells Oracle to collect statistics during the creation of the index. The statistics are then used by the optimizer to choose an optimal execution plan when the statements are executed.
For example:
In this example, an index has been created on the customer table called customer_idx. It consists of only of the customer_name field.
The following creates an index with more than one field:
The following collects statistics upon creation of the index:

#### Create a function-based index

In Oracle, you are not restricted to creating indexes on only columns. You can create function-based indexes.
The syntax that creates a function-based index is:
For example:
An index, based on the uppercase evaluation of the customer_name field, has been created.
To assure that the Oracle optimizer uses this index when executing your SQL statements, be sure that UPPER(customer_name) does not evaluate to a NULL value. To ensure this, add UPPER(customer_name) IS NOT NULL to your WHERE clause as follows:

#### Rename an Index

The syntax for renaming an index is:
For example:
In this example, customer_id is renamed to new_customer_id.

#### Collect statistics on an index

If you need to collect statistics on the index after it is first created or you want to update the statistics, you can always use the ALTER INDEX command to collect statistics. You collect statistics so that oracle can use the indexes in an effective manner. This recalcultes the table size, number of rows, blocks, segments and update the dictionary tables so that oracle can use the data effectively while choosing the execution plan.
The syntax for collecting statistics on an index is:
For example:
In this example, statistics are collected for the index called customer_idx.

#### Drop an index

The syntax for dropping an index is:
For example:
In this example, the customer_idx is dropped.

## DBA Related
### User Management
#### Creating a user

The syntax for creating a user is:
For example:

#### Granting privileges

The syntax for granting privileges is:
For example:

#### Change password

The syntax for changing user password is:
For example:

### Importing and exporting

There are two methods of backing up and restoring database tables and data. The 'exp' and 'imp' tools are simpler tools geared towards smaller databases. If database structures become more complex or are very large ( > 50 GB for example) then using the RMAN tool is more appropriate.

#### Import a dump file using IMP

This command is used to import Oracle tables and table data from a *.dmp file created by the 'exp' tool. Remember that this a command that is executed from the command line through $ORACLE_HOME/bin and not within SQL*Plus.
The syntax for importing a dump file is:
There are number of parameters you can use for keywords.
To view all the keywords:
An example:

## PL/SQL
### Operators
#### Arithmetic operators

* Addition: +
* Subtraction: -
* Multiplication: *
* Division: /
* Power (PL/SQL only): **

##### Examples

gives all employees from customer id 5 a 5% raise
determines the after tax wage for all employees

#### Comparison operators

* Greater Than: >
* Greater Than or Equal To: >=
* Less Than: <
* Less Than or Equal to: <=
* Equivalence: =
* Inequality: != ^= <> ¬= (depends on platform)

##### Examples
#### String operators

* Concatenate: ||
create or replace procedure addtest( a in varchar2(100), b in varchar2(100), c out varchar2(200) ) IS begin C:=concat(a,'-',b);

#### Date operators

* Addition: +
* Subtraction: -

### Types
#### Basic PL/SQL Types

Scalar type (defined in package STANDARD): NUMBER, CHAR, VARCHAR2, BOOLEAN, BINARY_INTEGER, LONG\LONG RAW, DATE, TIMESTAMP and its family including intervals)
Composite types (user-defined types): TABLE, RECORD, NESTED TABLE and VARRAY
LOB datatypes : used to store an unstructured large amount of data

#### %TYPE – anchored type variable declaration

The syntax for anchored type declarations is
For example
Note:
* Anchored variables allow for the automatic synchronization of the type of anchored variable with the type of <obj> when there is a change to the <obj> type.
* Anchored types are evaluated at compile time, so recompile the program to reflect the change of <obj> type in the anchored variable.

#### Collections

A collection is an ordered group of elements, all of the same type. It is a general concept that encompasses lists, arrays, and other familiar datatypes. Each element has a unique subscript that determines its position in the collection.
There are many good reasons to use collections.
* Dramatically faster execution speed, thanks to transparent performance boosts including a new optimizing compiler, better integrated native compilation, and new datatypes that help out with number-crunching applications.
* The FORALL statement, made even more flexible and useful. For example, FORALL now supports nonconsecutive indexes.
* Regular expressions are available in PL/SQL in the form of three new functions (REGEXP_INSTR, REGEXP_REPLACE, and REGEXP_SUBSTR) and the REGEXP_LIKE operator for comparisons.
* Collections, improved to include such things as collection comparison for equality and support for set operations on nested tables.

## References

* ↑ http://www.psoug.org/reference/constraints.html
* ↑ "First Expressions" by Jonathan Gennick for more information in this issue
* Taking Up Collections
* Oracle Programming with PL/SQL Collections

### Stored logic
#### Functions

A function must return a value to the caller.
The syntax for a function is
For example:

#### Procedures

A procedure differs from a function in that it must not return a value to the caller.
The syntax for a procedure is:
When you create a procedure or function, you may define parameters. There are three types of parameters that can be declared:
* IN – The parameter can be referenced by the procedure or function. The value of the parameter can not be overwritten by the procedure or function.
* OUT – The parameter can not be referenced by the procedure or function, but the value of the parameter can be overwritten by the procedure or function.
* IN OUT – The parameter can be referenced by the procedure or function and the value of the parameter can be overwritten by the procedure or function.
Also you can declare a DEFAULT value;
The following is a simple example of a procedure:

#### Passing parameters to stored logic

There are three basic syntaxes for passing parameters to a stored procedure: positional notation, named notation and mixed notation.
The following examples call this procedure for each of the basic syntaxes for parameter passing:

##### Positional notation

Specify the same parameters in the same order as they are declared in the procedure. This notation is compact, but if you specify the parameters (especially literals) in the wrong order, the bug can be hard to detect. You must change your code if the procedure's parameter list changes.

##### Named notation

Specify the name of each parameter along with its value. An arrow (=>) serves as the association operator. The order of the parameters is not significant. This notation is more verbose, but makes your code easier to read and maintain. You can sometimes avoid changing code if the procedure's parameter list changes, for example if the parameters are reordered or a new optional parameter is added. Named notation is a good practice to use for any code that calls someone else's API, or defines an API for someone else to use.

##### Mixed notation

Specify the first parameters with positional notation, then switch to named notation for the last parameters. You can use this notation to call procedures that have some required parameters, followed by some optional parameters.

#### Table functions
### Flow control
##### Conditional Operators

* and: AND
* or: OR
* not: NOT

##### Example

IF salary > 40000 AND salary <= 70000 THEN ELSE IF salary>70000 AND salary<=100000 THEN ELSE

#### If/then/else
### Arrays
##### Associative arrays

* Strongly typed arrays, useful as in-memory tables

##### Example

* Very simple example, the index is the key to accessing the array so there is no need to loop through the whole table unless you intend to use data from every line of the array.
* The index can also be a numeric value.
* More complex example, using a record

## APEX

Oracle Application Express aka APEX, is a web-based software development environment that runs on an Oracle database.

### String substitution

* In SQL: :VARIABLE
* In PL/SQL: V('VARIABLE') or NV('VARIABLE')
* In text: &VARIABLE.

## References

* http://www.psoug.org/reference/