Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

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.

 

This is an excerpt from the bestselling book "Oracle PL/SQL Tuning: Expert Secrets for High Performance Programming" by Dr. Tim Hall, Oracle ACE of the year, 2006.

You can buy the book for only $23.95 (30%-off) when you buy directly from the publisher, and you also get instant access to the code depot of PL/SQL tuning scripts:


 

 
��  
 
 

 
 
 
 
oracle dba poster
 

 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2014

All rights reserved by Burleson

Oracle © is the registered trademark of Oracle Corporation.