 |
|
Variables and Constants
in
PL/SQL
Oracle Tips by Burleson Consulting
|
The following Tip is from the
outstanding book "Oracle
PL/SQL Tuning: Expert Secrets for High Performance Programming" by
Dr. Tim Hall, Oracle ACE of the year, 2006:
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.
In most situations the explicit cursors provide a faster and cleaner
solution to data retrieval than their explicit equivalents, a subject
that will be covered in more detail in Chapter 2.
|
