Chapter -02 Declaring PL/SQL Variables - 01
Objectives
After completing this lesson,you should be able to do the following
- Recognize valid and invalid identifies.
- List the uses of variables.
- Declare and initialize variables.
- List and descible various data types.
- Indentify the benefits of using the %TYPE attribute.
- Declare,use and print bind variables.
Use of Vaiables
Variables can be used for:
- Temporary storage of data
- Manipulation of stored values
- Reusability
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
DECLARE v_fname VARCHAR2(20); v_deptno NUMBER(4); BEGIN SELECT first_name,department_id INTO v_fname,v_deptno FROM employees WHERE employee_id = 100; DBMS_OUTPUT.PUT_LINE('The first_name is ' || v_fname); DBMS_OUTPUT.PUT_LINE('The department_no is ' || v_deptno); END; / SQL> @variables.sql The first_name is Steven The department_no is 90 PL/SQL procedure successfully completed.
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 conatin no more than 30 characters
- Must not include reserverd words
Handing 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
Declaring an Initializing PL/SQL Variables
- Syntax:
identifier [CONSTRANT] datetype [NOT NULL] [:= | DEFATULT expr];
- Examples:
DECLARE v_hiredate DATE; v_deptno NUMBER(2) NOT NULL := 10; v_location VARCHAR2(13) := 'Atlanta'; v_comm CONSTANT NUMBER := 1400;
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
DECLARE v_myName VARCHAR2(20); BEGIN DBMS_OUTPUT.PUT_LINE('My name is :' || v_myName); v_myName := 'ArcerZhang'; DBMS_OUTPUT.PUT_LINE('My name is :' || v_myName); END; /
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
DECLARE v_myName VARCHAR2(20) := 'John'; BEGIN v_myName := 'Steven'; DBMS_OUTPUT.PUT_LINE('My name is : ' || v_myName); END; /