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: '||
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: '||