Declare

Declares a storage location of a specified data type and names the location so that it can be referenced.

You must declare an object before you can reference it. A declaration can appear in the declaration section of any block, subroutine, or package.

NOT NULL constraint

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

The NOT NULL constraint prevents a null value from being assigned to an object. You can implicitly or explicitly specify the NOT NULL constraint for an object.

When you declare a scalar variable, you can implicitly or explicitly specify the NOT NULL constraint. If you specify NOT NULL, you must initialize the variable (because the default initial value of a scalar variable is NULL).

PL considers any zero-length string to be a NULL value, including the return values of character functions and BOOLEAN expressions.

Declare a variable

When you declare a variable, you must specify the name and data type of the variable. You can also specify the initial value for the variable.

The name of the variable must be an effective user-defined identifier. The data type can be any PL data type. PL data types include all SQL data types. The data type can be a scalar type or a composite type.

Declare a constant

A constant is used to store an invariant value.

The information provided in the preceding topic also applies to constant declaration. However, constant declaration requires the CONSTANT keyword and the initial value, which becomes the permanent value, of the constant.

Initial values of variables and constants

In variable declaration, the initial value is optional unless the NOT NULL constraint is specified. In constant declaration, the initial value is mandatory.

If the variable is declared in a block or subprogram, it will be initialized with the initial value each time the block or subprogram is executed. If the variable is declared in the SPECIFICATION section of a package, it will be initialized with the initial value for each session the variable is accessed, whether the variable is a public variable or a private variable.

You can use the assignment operator (:=) or the DEFAULT keyword followed by an expression to specify the initial value. The expression can contain the constants that are declared earlier or the variables that are initialized. If the variable does not have an initial value when it is declared, 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

Declare a variable using ％TYPE

You can use ％TYPE to declare an object that has the same data type as a previously declared variable or column (without knowing the data type).

The advantages of using the %TYPE attribute are as follows:

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

• The variable type will change as the data type of the referenced column changes.

The syntax is as follows:

referencing_item referenced_item%TYPE;

The referenced item will pass its following attributes to the referencing item:

• Data type and size

• Constraints (excluding those of columns)

The referenced item does not pass its initial value to the referencing item. Therefore, if the referencing item specifies or inherits the NOT NULL constraint, you must initialize it.

％TYPE is particularly useful for declaring variables to store database values. The syntax for declaring a variable with the same data type as a column is as follows:

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. Since the lastname variable does not inherit the initial value of the firstname variable, an initial value must be specified in the declaration (no more than 30 characters).

Declare a cursor

A cursor acts as a pointer. It is a mechanism that enables you to retrieve data from a result set one row at a time. You can use a cursor in complex logic to process each row of a result set from an SQL query.

You can execute the CURSOR statement to declare a cursor. A cursor declaration specifies the collection of data to be retrieved and the procedure for traversing the query result.

A cursor declaration typically includes the CURSOR keyword, the cursor name, the IS keyword, and the 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. After a cursor is declared, you can execute the OPEN statement to open it.

   OPEN cursor_name;
   

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

   FETCH cursor_name INTO variable1, variable2;
   

4. After you finish using a cursor, you can execute the CLOSE statement to close it and release the database resources.

   CLOSE cursor_name;
   

   In the example above, cursor_name is the name of the cursor, table_name is the name of the table to be queried, and condition_name is the query condition.