Basic Syntax

            variable
            =======


assign value in variable
===========================================

identifier datatype :=value;
identifier datatype  default value;
identifier datatype not null value;
identifier constant datatype :=value;

Record type variable
===========================================

TYPE type_name IS RECORD
(field_declaration[, field_declaration]…);

Table type variable
===========================================

TYPE type_name IS TABLE OF
{column_type | variable%TYPE
| table.column%TYPE} [NOT NULL]
| table%ROWTYPE
[INDEX BY PLS_INTEGER | BINARY_INTEGER | VARCHAR2(<size>)];
identifier type_name;

Array
====================================================

TYPE type_name IS TABLE OF{column_type|variable%TYPE
| table.column%TYPE} [NOT NULL]
| table.%ROWTYPE

OR
====

TYPE type_name IS TABLE OF table.column%TYPE;
identifier type_name;


Varry
====

TYPE location_type IS VARRAY(3) OF locations.city%TYPE;
offices location_type;

Ref cursor syntax
=========================================

TYPE type_name IS REF CURSOR;?
TYPE type_name IS REF CURSOR  RETURN table%ROWTYPE;





            control structure
            =============

Conditional statement
===================

IF condition THEN
statements;
[ELSIF condition THEN
statements;]
[ELSE
statements;]
END IF;


Basic loop
=========

LOOP
statement1;
. . .
EXIT [WHEN condition];
END LOOP;



While loop
==========

WHILE condition LOOP
statement1;
statement2;
. . .
END LOOP;


For loop
========

FOR counter IN [REVERSE]
lower_bound..upper_bound LOOP
statement1;
statement2;
. . .
END LOOP;


Case Expression
===============

CASE
WHEN expression1 THEN result1
WHEN expression2 THEN result2
...
WHEN expressionN THEN resultN
[ ELSE result N+1 ]
END;

Cursor syntax
=============

CURSOR cursor_name IS  select_statement;


Exception syntax
===============

EXCEPTION
WHEN exception1 [OR exception2 . . .] THEN
statement1;
statement2;
. . .
[WHEN exception3 [OR exception4 . . .] THEN
statement1;
statement2;
. . .]
[WHEN OTHERS THEN
statement1;
statement2;
. . .]



Procedure syntax
==================

CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter1 [mode] datatype1,
parameter2 [mode] datatype2, ...)]
IS|AS
[local_variable_declarations; …]
BEGIN
--
actions;
END [procedure_name];


Function syntax
=================

CREATE [OR REPLACE] FUNCTION function_name
[(parameter1 [mode1] datatype1, ...)]
RETURN datatype IS|AS
[local_variable_declarations; …]
BEGIN
--
actions;
RETURN expression;
END [function_name];


Package syntax
==============

CREATE [OR REPLACE] PACKAGE package_name IS|AS
public type and variable declarations
subprogram specifications
END [package_name];


CREATE [OR REPLACE] PACKAGE BODY package_name IS|AS
private type and variable declarations
subprogram bodies
[BEGIN initialization statements]
END [package_name];