Enrollment and Billing System

Declaring PL/SQL Variables

Copyright © 2009, Oracle. All rights reserved.

Objectives
After completing this lesson, you should be able to do the following: • Recognize valid and invalid identifiers • List the uses of variables • Declare and initialize variables • List and describe various data types • Identify the benefits of using the %TYPE attribute • Declare, use, and print bind variables

2-2

Copyright © 2009, Oracle. All rights reserved.

Use of Variables
Variables can be used for: • Temporary storage of data • Manipulation of stored values • Reusability

SELECT first_name, department_id INTO v_fname, v_deptno FROM …

Jennifer

v_fname

10

v_deptno

2-3

Copyright © 2009, Oracle. All rights reserved.

Requirements for Variable Names
A variable name: • Must start with a letter • Can include letters or numbers • Can include special characters (such as $, _, and # ) • • Must contain no more than 30 characters Must not include reserved words

2-4

Copyright © 2009, Oracle. All rights reserved.

Handling Variables in PL/SQL
Variables are: • Declared and initialized in the declarative section • Used and assigned new values in the executable section • Passed as parameters to PL/SQL subprograms • Used to hold the output of a PL/SQL subprogram

2-5

Copyright © 2009, Oracle. All rights reserved.

Declaring and Initializing PL/SQL Variables
Syntax:
identifier [CONSTANT] datatype [NOT NULL] [:= | DEFAULT expr];

Examples:
DECLARE v_hiredate v_deptno v_location c_comm DATE; NUMBER(2) NOT NULL := 10; VARCHAR2(13) := 'Atlanta'; CONSTANT NUMBER := 1400;

2-6

Copyright © 2009, Oracle. All rights reserved.

Declaring and Initializing PL/SQL Variables
DECLARE v_myName VARCHAR2(20); BEGIN DBMS_OUTPUT.PUT_LINE('My name is: '|| v_myName); v_myName := 'John'; DBMS_OUTPUT.PUT_LINE('My name is: '|| v_myName); END; / DECLARE v_myName VARCHAR2(20):= 'John'; BEGIN v_myName := 'Steven'; DBMS_OUTPUT.PUT_LINE('My name is: '||