Declaration

A declaration allocates storage space to values of a specified data type and names the storage location for you to reference it.

You must declare objects so that they can be referenced. Declarations can appear in the declarative part of any block, subprogram, or package.

NOT NULL constraint

You can apply the NOT NULL constraint to a scalar variable or constant or to a scalar component of a composite variable or constant.

The NOT NULL constraint can prevent assignment of null values to objects. You can implicitly or explicitly specify the NOT NULL attribute for objects.

You can implicitly or explicitly specify the NOT NULL attribute when you declare a scalar variable. When you specify the NOT NULL attribute, you must assign an initial value to the scalar variable, because the default initial value of a scalar variable is NULL.

PL considers zero-length strings as NULL values, including the return values of character functions and BOOLEAN expressions.

Declare variables

When you declare a variable, you must specify the name and data type of the variable. For most data types, you can specify initial values for the variables declared.

The variable names must be set to a valid user-defined identifier. The data type can be set to any PL data type. PL supports all data types of SQL. You can set the data type to a scalar type or a composite type.

Declare constants

You can use constants to store permanent values.

The information used to declare a variable can also be used in declaring a constant. However, the keyword CONSTANT and the initial value of the constant are required. The initial value of the constant is permanent.

Initial values of variables and constants

When you declare a variable, the initial value is optional, unless you specify the NOT NULL constraint. When you declare a constant, the initial value is required.

If you declare a variable or constraint in a block or subprogram, the initial value is assigned to the variable or constraint each time when the block or subprogram is executed. If you declare a variable or constant in SPECIFICATION of a package, the initial value is assigned to the variable or constant in each session, regardless of whether the variable or constant is public or private.

To specify the initial value, use the value assignment operator := or the DEFAULT keyword followed by an expression. The expression can contain previously declared constants and previously initialized variables. If you do not specify an initial value for a variable, assign a value to the variable before you use it.

Here is an example:

obclient> DECLARE
   pi     CONSTANT NUMBER := 3.14159;
   radius NUMBER NOT NULL := 1;
   area   NUMBER := (pi * radius**2);
BEGIN
   DBMS_OUTPUT.PUT_LINE('The area of the circle is: '||area);
END;
/

Query OK, 0 rows affected

The area of the circle is: 3.14159

Use ％TYPE to declare objects

You can use the ％TYPE attribute to declare an object that has the same data type as the previously declared variable or column, without a need to know the type.

The %TYPE attribute brings the following benefits:

• The data type of the referenced database column can be unknown.

• The data type of a variable changes with the data type of the corresponding column.

The syntax is as follows:

referencing_item referenced_item%TYPE;

The referencing item inherits the following attributes of the referenced item:

• Data types and size

• Constraints, unless the referenced item is a column

The referencing item does not inherit the initial value of the referenced item. Therefore, if the referencing item specifies or inherits the NOT NULL constraint, you must specify an initial value for the referencing item.

％TYPE is especially useful in scenarios where variables are declared to store database values. The following syntax declares a variable that is of the same type as the column:

variable_name table_name.column_name%TYPE;

Here is an example:

obclient> DECLARE
  firstname  VARCHAR(30) NOT NULL := 'San';
  lastname  firstname%TYPE := 'Zhang';
BEGIN
  DBMS_OUTPUT.PUT_LINE('firstname=' || firstname);
  DBMS_OUTPUT.PUT_LINE('lastname=' || lastname);
END;
/
Query OK, 0 rows affected

firstname=San
lastname=Zhang

In this example, the lastname variable inherits the data type, size, and NOT NULL constraint of the firstname variable. The lastname variable does not inherit the initial value of firstname, so you must declare an initial value that does not exceed 30 characters in length for the lastname variable.

Declare a cursor

A cursor serves as a pointer and enables you to fetch one record at a time from a result set that contains multiple records. A cursor can be used to process data in a result set of an SQL query row by row in complex logic.

You can use the CURSOR statement to declare a cursor to retrieve a query result set row by row. The dataset to retrieve is specified in the declaration.

Generally, the declaration of a cursor contains the CURSOR and IS keywords, name of the cursor, and query statement.

Here is an example:

1. Declare a cursor.

   DECLARE
     CURSOR cursor_name IS
       SELECT column1, column2
       FROM table_name
       WHERE condition_name;
   

2. Use the OPEN statement to open the declared cursor.

   OPEN cursor_name;
   

3. Use the FETCH statement to retrieve data row by row. You can use the FETCH statement in a loop to traverse the whole result set.

   FETCH cursor_name INTO variable1, variable2;
   

4. Use the CLOSE statement to close the cursor to release the occupied database resources.

   CLOSE cursor_name;
   

   In this example, cursor_name specifies the cursor name, table_name specifies the table to query, and condition_name specifies the query condition.