Symbols
; Semicolon. Statement terminator
% Percent sign
Attribute indicator (cursor attributes like
%ISOPEN and indirect declaration
attributes like %ROWTYPE). Also used as
multibyte wildcard symbol, as in SQL.
_ Single underscore Single-byte wildcard symbol, as in SQL
: Colon Host variable indicator, such as :block.item
in Oracle Forms
** Double asterisk Exponentiation operator
< > and != Not equals"
|| Double vertical
bar Concatenation operator
<< and >> Label delimiters
:= Assignment operator
=> Association operator for positional notation
-- Double dash: single-line comment
indicator
/* and */ Beginning and ending multiline comment
block delimiters
Data Types.
Database types
NUMBER
CHAR(N),
VARCHAR2(N)
DATE
LONG
LONG RAW
ROWID
MLSLABEL
Definition
Used to store any number
Used for storing text
Oracle system date
Stores large blocks of text
Stores large blocks of binary data
Smaller binary data store
Uesd for row identifier
Security label
Non database types.
DEC, DECIMAL, REAL, DOUBLE-
PRECISION, INTEGER, INT, SMALLINT,
NATURAL, POSITIVE, NUMERIC, BINARY-
INTEGER, CHARACTER, VARCHAR,
BOOLEAN, TABLE, RECORD
PLSQL Module types
Procedure A non-formal function that can accept
paremeters via value or reference. Similar
in form to a function.
Function A classical function that returns one value.
Usually contains declaration, execution
and exception sections.
Package A library, consisting of a specification with
function/prototype signatures, and a body
with actual code. eg
Trigger Code attached to a table that fires on
certian conditions.
Module Sections or Blocks
Variable Declaration DECLARE
employee-id employee.empid%TYPE, pi
Control Flow
IF..THEN..ELSE..ENDIF; As usual.
LOOP ..
IF (condition) THEN
EXIT END IF;
.. END LOOP:
Equivalent to if (conition) then
b
WHILE cond LOOP..END
LOOP; while () {};
FOR var IN n..m LOOP
.. END LOOP; for thing in range(n,m) {}
EXECUTE
function_name; Function call FUNCTION name
(parameter type,..) ..body.. EN
D
Cursor for.
Opens cursor, loops across until
%NOTFOUND.
FOR variables IN cursor LOOP..
E
LOOP;
Explicit Cursor Handling
Implict cursor
named by
developer.
T
hink of it as a select statement th
name.
Implict cusror is
called SQL IF SQL%NOTFOUND THEN ..
Declaring an explicit
cursor.
DECLARE CURSOR employee_crsr
I
SELECT empid, salary FROM empl
o
BEGIN ..
Executing a cursor
OPEN employee_cursor
LOOP
FETCH employee_cursor INTO my
_
my_salary;
EXIT WHEN employee_crsr%NOTF
O
..do stuff..
ENDLOOP;
FETCH Obtains next record from cursor.C
a
into individual variables (as above
)
RECORD.
Declaring an explicit
cursor using a
record.
T
YPE t_emp IS RECORD (T_Salary
t_empid number);
my_emprec t_emp;
CURSOR employee_crsr IS
SELECT empid, salary
FROM employee;
Executing explicit
cursror using record.
OPEN employee_cursr;
LOOP
FETCH emloyee_crsr INTO my_em
p
EXIT WHEN employee_crsr%NOTF
O
IF my_emprec.t_empid ..
Cursor Parameters.
Declarin
g
parameters to be used a
t
time.
DECLARE .. CURSOR
employee_crsr(low_end VARCHAR
2
high_end VARCHAR2) IS
SELECT empid, salary FROM empl
o
