Study Brief: Introduction to Oracle: SQL and PL/SQL

by Michael Ritacco, Oraclenotes.com

Writing Basic SQL Statements

• RELATIONSHIPS
  • Solid Line = Must Be
  • Dashed Line = May Be
  • Single Line = One and Only One
  • Crow's foot = One or More
  • UID = #
  • Secondary UID = ( # )
  • Mandatory attribute = ( * )
  • Optional Attribute = ( o )
    
    
• For a BASIC SELECT statement to be successful you must at least SELECT what column(s) you want and identify FROM which table to take the column(s).
  
  
• ( * ) is the wildcard used to select all columns from a table.
  
  
• In a SELECT statement the order in which you specify the columns will be the order in which the query will produce the output.
  
  
• Column headings appear in uppercase by default and number headings cannot be truncated. The only way to have a lowercase or multicase heading is to use an alias.
  
  
• When using an ALIAS to change a column heading the ALIAS should immediately follow the column name. To assign an ALIAS with spaces, a specific case, or special characters use ( " " ) double quotation marks around the ALIAS.
  
  
• Arithmetic Operator Precedence: Multiplication | Division | Addition | Subtraction
  Expressions within parentheses are prioritized otherwise the statement is parsed from left to right.
  
  
• DISTINCT is used in a SELECT statement to restrict duplicate rows from being displayed.
  
  
• To specify a literal use single quotation marks ( ' ' ).
  
  
• Oracle uses the double pipe symbol ( | | ) symbol to concatenate columns.
  
  
• The result of a null value when divided is null. Any arithmetic performed on a null value equals a null value.
  
  
• The number datatype displays its column heading and data right justified.

SQL*Plus COMMANDS
(Do not get entered into the SQL buffer)

• DESCRIBE
• SAVE "file command"
• GET "file command"
• EDIT "file command"
• SPOOL "file command"
• INPUT "editing command"
• LIST "editing command"
• RUN "editing command"
• APPEND "editing command"
• CHANGE "editing command"
• CLEAR BUFFER "editing command"
• DEL "editing command"

Restricting and Sorting Data

• Use the WHERE clause to restrict the rows returned by a query.
  Date searches are format sensitive. DD-MON-YY is Oracle's default display.
  Character value searches are case sensitive.
  
  
• You can use these comparison operators with the WHERE clause:
  
  
  • Equal to ( = )
    
  • Greater than ( > )
    
  • Greater than or equal to ( >= )
    
  • Less than ( < )
    
  • Less than or equal to ( <= )
    
  • Not equal to ( <>)
    
    
  • BETWEEN ...AND...
    Remember to put the lower limit first!
    
    
  • IN( list values here )
    You can use any datatype with this one but use single quotations ( ' ' ) if you are
    using characters or dates.
    
    
• LIKE
  The percent sign ( % ) is the multicharacter wildcard search, underscore ( _ ) denotes a
  single character wildcard search. Wildcards may be escaped by specifing the escape character 'of choice' with the ESCAPE option.
  
  
• IS NULL
  
  
• IS NOT NULL
  
  
• The Logical Operators are:
  AND
  OR
  NOT
  
  
• Rules of Precendence
  Remember that you can always override the rules using parentheses!

1. Arithmetic operators
2. All Comparison operators (NULL cannot be compared)
3. NOT
4. AND
5. OR
   
   
• The ORDER BY clause
  DESC specifies decending order, acending order is the default.
  
  • You may use a column alias with a ORDER BY clause.
  • The sort limit is the number of columns in a the table.
  • It is okay to sort by a column not first specified in a SELECT clause.
  • Proper syntax says that a ORDER BY clause is placed after a WHERE clause.

Single Row Functions

• Functions are used to calculate, modify, manipulate, format, and convert datatypes.
  
  
• There are two types of functions. Single-row and Multiple-row functions.
  Single-row functions work on one row at a time and return one result per row.
  
  
• Single-row functions can be in: SELECT, WHERE, ORDER BY, and NESTED.
  
  
• The Charater Functions are:
  • LOWER('char')
  • UPPER('CHAR')
  • INITCAP('Char')
  • CONCAT - Function has a two parameter limit.
  • LENGTH
  • INSTR
  • LPAD
    
    
• The Number Functions are:
  • ROUND
  • TRUNC
  • MOD
    
    
• The Date Functions are:
  • SYSDATE
  • DUAL
  • MONTHS_BETWEEN (Returns a number)
  • ADD_MONTHS
  • NEXT_DAY
  • LAST_DAY
  • ROUND
  • TRUNC
    
    
• Explict Datatype Conversion Functions
  • TO_CHAR -converts number or date to Varchar2
  • TO_NUMBER
  • TO_DATE
    
    
• Other Functions
  • NVL (expr1, expr2)
  • DECODE This function works like a IF-THEN-ELSE or CASE statement.
    
    
• Nested Functions are parsed from the middle level outwards. There is no limit on depth except SQL*Plus buffer size.

Displaying Data from Mulitple tables

JOINS

• Join conditions are specified in the WHERE clause
  a minimum of (n-1) conditions are required to join (n) tables.
• Must add table prefixes to columns.
• Using table aliases (30 character max) will save keystrokes!
• Table aliases are specified in the FROM clause.

Types of Joins

• Eqijoin
  
  
• Non-Equijoin - uses any other comparision operator other than ( = )
  
  
• Outer Join - (+) can be on either side of the WHERE clause but must be on side of the table without the rows in the condition. Only operators ( = ) & AND can be used.
  
  
• Self Join

Aggregating Data Using Group Functions

Types of Group Functions include:
(Default argument is ALL but you can specify DISTINCT in the syntax
to only consider nonduplicate values)

• AVG - Numeric datatypes only.
  
  
• COUNT - only group function that includes null values.
  
  
• MAX - can use with any datatype.
  
  
• MIN - can use with any datatype.
  
  
• STDDEV - Numeric datatypes only.
  
  
• SUM( n ) - Numeric datatypes only and cannot have other arithmetic operators in the function.
  
  
• VARIANCE - Numeric datatypes only.
  
  *Group functions can be nested only to a depth of two.
  
  

Group Function Clauses:

• GROUP BY: Breaks down the results of group functions from one large table of data into smaller logical groupings.
  
  
• WHERE clause cannot restrict a group use the HAVING clause.
  
  
• Remember that the WHERE clause is evaluated first (restricts the query results), then the GROUP BY clause (groups the results of the WHERE), then the HAVING clause (further restricts the results, by restricting the groups returned).
  
  
• Do not use the column alias in the GROUP BY clause.
  
  
• Items (columns) that do not use a group function in the SELECT clause must be listed in the GROUP BY clause.
  
  
• HAVING: restricts the display of groups to those "having" the specified conditions.
  
  
• The NVL function allows a GROUP BY function to include null values in its calculation.

Subqueries

Placement of Subqueries

• FROM clause
• WHERE clause
• HAVING clause
  
  
• Subqueries are usually parsed first, and those results are then used as the source
  of the main query.
  

Types of Subqueries

• Single-row
• Multiple-row
• Multiple-column

Multiple-Row Operators

• IN
  
  
• ANY
  • <ANY = less then max
  • >ANY = more than min
  • =ANY equivalent to IN
    
    
• ALL
  • <ALL = less than min
  • >ALL = more than max
    
    

  *You may use the NOT operator with IN, ANY, ALL.

Producing Readable Output with SQL*Plus

The login.sql file sets the SQL*Plus environment. Add settings that you want to remain permanent to the login.sql file.

Substitution Variables

• (&)
• (&&)
• DEFINE [ displays all currently defined variables, values, and datatypes ]
• DEFINE variable = value [creates CHAR datatype variable and assigns a value]
• DEFINE variable [ displays the variable, value, and datatype ]
  
• UNDEFINE
• ACCEPT SQL*PLUS command that is used for NUMBER, CHAR, and DATE datatypes. Do not prefix the variable with ( & ) in the ACCEPT command line.

SET Command Variables

• ARRAYSIZE
• COLSEP
• FEEDBACK
• HEADING
• LINESIZE
• LONG
• PAGESIZE
• PAUSE
• TERMOUT

Format Commands

• COLUMN
• TTITLE
• BTITLE
• BREAK

Manipulating Data

Use these SQL keywords to input, update, or delete data.

• INSERT
  VALUES
  
  
• UPDATE
  SET
  
  
• DELETE
  You will get an error if you try to delete a parent record that has children.

Types of Transactions

• Data Maipulation Language (DML)
  UPDATE, DELETE, INSERT- (SELECT is Data Retrieval )
  May contain one or more DML statements that Server will see as one unit.
  
  
• Data Definition Language (DDL)
  CREATE, DROP, ALTER, RENAME, TRUNCATE
  Automatically commits and ends a transaction, consists of one statement.
  
  
• Data Control Language (DCL)
  GRANT, REVOKE, ALTER USER
  Automatically commits and ends a transaction, consists of one statement.
  
  
• Exiting SQL*Plus produces an implicit COMMIT
  
  
• Use these SQL keywords to control transactions.
• COMMIT
• ROLLBACK
• SAVEPOINT

Locking Modes

• Exculsive: prevents read/write sharing, resource cannot be used until it is released.
• Share Lock: allows multiple data readers to share, the share lock just prevents concurrent data writers.

Creating and Managing Tables

Types of objects found in Oracle

• TABLE
  Must begin with a letter, 1-30 characters long.
  A-Z, a-z, 0-9, _, #, and $
  
  
• VIEW
  A logical subset of data from 1+ tables.
  
• SEQUENCE
  Auto generates primary key values.
  
• INDEX
  Think of an index in a book but for a table. Improves query performance.
  
• SYNONYM
  AKA for an object.

Types of Tables

• Data Dictionary Tables
• User Tables

Types of Data Dictionary Views

• USER_ = objects owned by user
• ALL_ = objects owned and objects that user has access rights.
• DBA_ = can access any object
• V$_ = server performance and locking.

Oracle Datatypes

• CHAR
• VARCHAR2
• NUMBER
• DATE
• CLOB
• BLOB
• BFILE
• LONG
• RAW/ LONG RAW

Table Keywords

• DEFAULT
• DROP TABLE
• RENAME
• COMMENT
• TRUNCATE TABLE
• ALTAR TABLE
• CREATE TABLE

Including Contraints

Constraints

• Constraints be created when the table is created or after the table is created.
• Check defined constraints for a table in: USER_CONSTRAINTS
• View the names of columns with constraints in: USER_CONS_COLUMNS
• Only one Primary Key per table allowed, but two or more columns may be concatentated to become a single Primary Key.

Valid Contraints

• PRIMARY KEY (Table or column level)
• FOREIGN KEY (Table or column level)
• NOT NULL (Column level only)
  • You can only define a NOT NULL constraint on table that has no rows, or a table with no null values in the column being specified.
• UNIQUE (Table or column level)
• CHECK (Table or column level)

Dropping Constraints

• Use the ALTER_TABLE with the DROP clause.
• CASCADE can be added to the DROP clause to remove any dependent constraints.

Disable Constraints

• Works just like dropping a constraint except use the DISABLE keyword instead of DROP.
• CASCADE can also be used to deactivate any dependent constraints.
• The DISABLE clause may be used in the CREATE TABLE and ALTER TABLE statements.

Enable Constraints

• To reactivate the disabled constraints use the ENABLE keyword.
• The ENABLE clause may be used in the CREATE TABLE and ALTER TABLE statements.

Creating Views

Keywords

• CREATE VIEW
• CREATE OR REPLACE
• DROP VIEW

Removing of rows is not allowed on views containing DISTINCT, GROUP BY, or GROUP FUNCTIONS.

Other Database Objects

SEQUENCES

• CREATE SEQUENCE
  • INCREMENT BY
  • START WITH
  • MAXVALUE
  • NOMAXVALUE
  • MINVALUE
  • NOMINVALUE
  • NOCYCLE
  • CACHE | NOCACHE

INDEXES

• CREATE INDEX
• DROP INDEX

SYNONYMS

• CREATE SYNONYM
• DROP SYNONYM

Controlling User Access

KEYWORDS

• GRANT
• REVOKE

PRIVILEGES

• System privileges
  • CREATE SESSION
  • CREATE TABLE
  • CREATE VIEW
  • CREATE SEQUENCE
  • CREATE PROCEDURE
    
    
• Basic DBA Privileges
  • CREATE USER
  • DROP USER
  • BACKUP ANY TABLE
    
  • DROP ANY TABLE

   

Checking Privileges

• USER_ROLE_PRIVS
• USER_TAB_PRIVS_MADE
• USER_TAB_PRIVS_RECD
• USER_COL_PRIVS_MADE
• USER_COL_PRIVS_RECD
• ROLE_SYS_PRIVS
• ROLE_TAB_PRIVS
• ALL_ is the prefix used to display the names of all the objects the user has access to.
• USER_ prefix that is used to view information on all of the objects owned by the user
• ALL_OBJECTS- displays all of the objects to which the user has access to.
• ALL_COL_PRIVS_MADE - displays all of the grants on columns that the user owns or that the user has granted.
• USER_OBJECTS- displays only the objects owned by the user.
• USER_TABLES- displays only the tables owned by the user.
• USER_VIEWS -displays only the views owned by the user.
• DICTIONARY this view provides descriptions of the data dictionary tables and views that are accessible to the user.
• TABLE_PRIVILEGES-
  • Displays the grants on objects :
• When role or a PUBLIC is grantee
• The user has granted.
• That have been granted to the user.
• That the user owns.

Declaring Variables

PL/SQL
PL/SQL is Oracle's proprietary extension to SQL that allows for procedural language capabilities.

• Block Structure
  • DECLARE - this section contains variables, constants, cursors, and user-defined exceptions.
    
    
  • BEGIN
    EXECUTABLE - this section contains any SQL statements.
    
    
  • EXCEPTION - this section contains the error handlers.
  • END;
    
    
• Block Types
  • Anonymous
  • Function
  • Procedure
• Two objects in the same block cannot have the same name.

VARIABLES

• Remember that each variable must be declared on a separate line and multiple variables cannot be defined in the same statement.
• %TYPE - allows for unknown column datatypes when assigning a variable.
• BOOLEAN variables can only accept values of TRUE, FALSE, and NULL.
• ( : ) is used to prefix a bind variable.

Interacting with the Oracle Server

The cursor is the area of memory which a command is parsed and executed.

PL/SQL does not support:

• DDL
• DCL

SQL Cursor Attributes

• SQL%ROWCOUNT
• SQL%NOTFOUND
• SQL%FOUND
• SQL%ISOPEN

Writing Control Structures

LOOPS

• BASIC Loop - repetitive actions without conditions. (Can be infinite without exit)
• FOR Loop - based on a count. (Index is declared implicitly)
• WHILE Loop - based on a condition. This loop terminates when condition is false, when condition is NULL the loop is bypassed.
• EXIT - terminates the loop.

Working with Composite Datatypes

COMPOSITE DATATYPES

• TABLE
• Nested TABLE
• RECORD
• VARRAY

MISC.

• %ROWTYPE
• BINARY_INTEGER - PK used to index a PL/SQL table

Writing Explicit Cursors

CURSOR STATUS

• %ISOPEN - Boolean - Cursor open=TRUE
• %NOTFOUND - Boolean - Fetch no row =TRUE
• %ROWCOUNT - Number - Counts total rows returned
• %FOUND - Boolean - Fetch returns a row = TRUE

Advanced Explicit Cursor Concepts

CLAUSES

• FOR UPDATE
• WHERE CURRENT OF

Handling Exceptions

• NO_DATA_FOUND
• TOO_MANY_ROWS
• ACCESS_INTO_NULL
• WHEN OTHERS Clause
• RAISE statement