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
Demo
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;
Demo 01
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;
/
Demo 02
DECLARE
v_myName VARCHAR2(20) := 'John';
BEGIN
v_myName := 'Steven';
DBMS_OUTPUT.PUT_LINE('My name is : ' || v_myName);
END;
/