Variables and Constants in PL/SQL

Variables and constants must be declared for use in procedural and SQL code, although the datatypes available in SQL are only a subset of those available in PL/SQL.  All variables and constants must be declared before they are referenced. 

The declarations of variables and constants are similar, but constant definitions must contain the CONSTANT keyword and must be assigned a value as part of the definition.  Subsequent attempts to assign a value to a constant will result in an error. 

The following example shows some basic variable and constant definitions, along with a subsequent assignment of a value to a constant resulting in an error.

DECLARE
  l_string  VARCHAR2(20);
  l_number  NUMBER(10); 

  l_con_string  CONSTANT VARCHAR2(20) := 'This is a constant.';
BEGIN
  l_string := 'Variable';
  l_number := 1; 

  l_con_string := 'This will fail';
END;
/
  l_con_string := 'This will fail';
  *
ERROR at line 10:
ORA-06550: line 10, column 3:
PLS-00363: expression 'L_CON_STRING' cannot be used as an assignment target
ORA-06550: line 10, column 3:
PL/SQL: Statement ignored

SQL>

In addition to standard variable declarations used within SQL, PL/SQL allows variable datatypes to match the datatypes of existing columns, rows or cursors using the %TYPE and %ROWTYPE qualifiers.  This makes code maintenance much easier.  The following code shows each of these definitions in practice.

DECLARE
  -- Specific column from table.
  l_username  all_users.username%TYPE;
 
  -- Whole record from table.
  l_all_users_row  all_users%ROWTYPE;
 
  CURSOR c_user_data IS
    SELECT username,
           created
    FROM   all_users
    WHERE  username = 'SYS';   

  -- Record that matches cursor definition.
  l_all_users_cursor_row  c_user_data%ROWTYPE;
BEGIN
  -- Specific column from table.
  SELECT username
  INTO   l_username
  FROM   all_users
  WHERE  username = 'SYS'; 

  DBMS_OUTPUT.put_line('l_username=' || l_username); 

  -- Whole record from table.
  SELECT *
  INTO   l_all_users_row
  FROM   all_users
  WHERE  username = 'SYS'; 

  DBMS_OUTPUT.put_line('l_all_users_row.username=' ||
                        l_all_users_row.username);
  DBMS_OUTPUT.put_line('l_all_users_row.user_id=' ||
                        l_all_users_row.user_id);
  DBMS_OUTPUT.put_line('l_all_users_row.created=' ||
                        l_all_users_row.created);

  -- Record that matches cursor definition.
  OPEN  c_user_data;
  FETCH c_user_data
  INTO  l_all_users_cursor_row;
  CLOSE c_user_data; 

  DBMS_OUTPUT.put_line('l_all_users_cursor_row.username=' ||
                        l_all_users_cursor_row.username);
  DBMS_OUTPUT.put_line('l_all_users_cursor_row.created=' ||
                        l_all_users_cursor_row.created);
END;
/
l_username=SYS
l_all_users_row.username=SYS
l_all_users_row.user_id=0
l_all_users_row.created=18-MAR-2004 08:02:17
l_all_users_cursor_row.username=SYS
l_all_users_cursor_row.created=18-MAR-2004 08:02:17

PL/SQL procedure successfully completed.

The %TYPE qualifier signifies that the variable datatype should match that of the specified table column, while the %ROWTYPE qualifier signifies that the variable datatype should be a record structure that matches the specified table or cursor structure.  Notice that the record structures use the dot notation (variable.column) to reference the individual column data within the record structure.

Values can be assigned to variables directly using the “:=” assignment operator, via a SELECT ... INTO statement or when used as OUT or IN OUT parameter from a procedure.  All three assignment methods are shown in the example below.

DECLARE

  l_number  NUMBER; 

  PROCEDURE add(p1  IN  NUMBER,
                p2  IN  NUMBER,
                p3  OUT  NUMBER) AS
  BEGIN
    p3 := p1 + p2;
  END;
BEGIN
  -- Direct assignment.
  l_number := 1; 

  -- Assignment via a select.
  SELECT 1
  INTO   l_number
  FROM   dual; 

  -- Assignment via a procedure parameter.
  add(1, 2, l_number);
END;
/
SQL inside PL/SQL

The SQL language is fully integrated into PL/SQL, so much so that they are often mistaken as being a single language by newcomers.  It is possible to manually code the retrieval of data using explicit cursors, or to allow Oracle do the hard work and use implicit cursors.  Examples of both explicit and implicit cursors are presented below, all of which rely on the following definition table.

CREATE TABLE sql_test (
  id           NUMBER(10),
  description  VARCHAR2(10)
); 

INSERT INTO sql_test (id, description) VALUES (1, 'One');
INSERT INTO sql_test (id, description) VALUES (2, 'Two');
INSERT INTO sql_test (id, description) VALUES (3, 'Three');
COMMIT;

The SELECT ... INTO statement allows data from one or more columns of a specific row to be retrieved into variables or record structures using an implicit cursor.

SET SERVEROUTPUT ON
DECLARE
  l_description  VARCHAR2(10);
BEGIN
  SELECT description
  INTO   l_description
  FROM   sql_test
  WHERE  id = 1; 

  DBMS_OUTPUT.put_line('l_description=' || l_description);
END;
/
l_description=One

PL/SQL procedure successfully completed.

SQL>

The previous example can be recoded to use an explicit cursor as shown below.  Notice that the cursor is now defined in the declaration section and is explicitly opened and closed, making the code larger and a little ugly.

SET SERVEROUTPUT ON
DECLARE
  l_description  VARCHAR2(10);

  CURSOR c_data (p_id  IN  NUMBER) IS
    SELECT description
    FROM   sql_test
    WHERE  id = p_id;
BEGIN
  OPEN c_data (p_id => 1);
  FETCH c_data
  INTO  l_description;
  CLOSE c_data;

  DBMS_OUTPUT.put_line('l_description=' || l_description);
END;
/
l_description=One
 

PL/SQL procedure successfully completed.

When a query returns multiple rows, it can be processed within a loop.  The following example uses a cursor FOR-LOOP to cycle through multiple rows of an implicit cursor.  Notice there is no need for a variable definition as “cur_rec” acts as a pointer to the current record of the cursor.

SET SERVEROUTPUT ON
BEGIN
  FOR cur_rec IN (SELECT description
                  FROM   sql_test)
  LOOP
    DBMS_OUTPUT.put_line('cur_rec.description=' || cur_rec.description);
  END LOOP;
END;
/
cur_rec.description=One
cur_rec.description=Two
cur_rec.description=Three 

PL/SQL procedure successfully completed.

The explicit cursor version of the previous example is displayed below.  Once again the cursor management is all done manually, but this time the exit from the loop must also be managed manually.

SET SERVEROUTPUT ON
DECLARE
  l_description  VARCHAR2(10); 

  CURSOR c_data IS
    SELECT description
    FROM   sql_test;
BEGIN
  OPEN c_data;
  LOOP
    FETCH c_data
    INTO  l_description;
    EXIT WHEN c_data%NOTFOUND; 

    DBMS_OUTPUT.put_line('l_description=' || l_description);
  END LOOP;
  CLOSE c_data;
END;
/
l_description=One
l_description=Two
l_description=Three

PL/SQL procedure successfully completed.