Introduction of constant, variable, cursors and exceptions declarations in PL/SQL

Constants, variables, cursors, and exceptions used in a PL/SQL block must be declared in the
declare section of that block. Variables and constants can be declared as follows:

[constant] [not null] [:= ];

Valid data types are SQL data types (see Section 1.1) and the data type boolean. Boolean data may only be true, false, or null. The not null clause requires that the declared variable must always have a value different from null. is used to initialize a variable. If no expression is specified, the value null is assigned to the variable. The clause constant states that once a value has been assigned to the variable, the value cannot be changed (thus the variable becomes a constant). Example:

declare
hire date date; /* implicit initialization with null */
job title varchar2(80) := ’Salesman’;
emp found boolean; /* implicit initialization with null */
salary incr constant number(3,2) := 1.5; /* constant */
. . .
begin . . . end;

Instead of specifying a data type, one can also refer to the data type of a table column (so-called
anchored declaration). For example, EMP.Empno%TYPE refers to the data type of the column
Empno in the relation EMP. Instead of a single variable, a record can be declared that can store a
complete tuple from a given table (or query result). For example, the data type DEPT%ROWTYPE
specifies a record suitable to store all attribute values of a complete row from the table DEPT.
Such records are typically used in combination with a cursor. A field in a record can be accessed
using ., for example, DEPT.Deptno.

A cursor declaration specifies a set of tuples (as a query result) such that the tuples can be
processed in a tuple-oriented way (i.e., one tuple at a time) using the fetch statement. A cursor
declaration has the form

cursor [()] is ;

The cursor name is an undeclared identifier, not the name of any PL/SQL variable. A parameter
has the form . Possible parameter types are char, varchar2, number, date and boolean as well as corresponding subtypes such as integer. Parameters are used to assign values to the variables that are given in the select statement. Example: We want to retrieve the following attribute values from the table EMP in a tupleoriented way: the job title and name of those employees who have been hired after a given date, and who have a manager working in a given department.

cursor employee cur (start date date, dno number) is
select JOB, ENAME from EMP E where HIREDATE > start date
and exists (select * from EMP
where E.MGR = EMPNO and DEPTNO = dno);

If (some) tuples selected by the cursor will be modified in the PL/SQL block, the clause for
update[()] has to be added at the end of the cursor declaration. In this case selected tuples are locked and cannot be accessed by other users until a commit has been issued. Before a declared cursor can be used in PL/SQL statements, the cursor must be opened, and after processing the selected tuples the cursor must be closed.

Exceptions are used to process errors and warnings that occur during the execution of PL/SQL
statements in a controlled manner. Some exceptions are internally defined, such as ZERO DIVIDE.
Other exceptions can be specified by the user at the end of a PL/SQL block. User defined exceptions need to be declared using exception.