Use of Variables

Variables

1. Variables are used to temporarily store the value.

2. Variables are used to storing and manipulating data.

Identifier

1. Identifiers are names of variables.

2. Identifiers are used to name any PL/SQL object such as variables, types, cursors, and subprograms.

Handling Variables in PL/SQL

1. Declared and initialized in the declarative section.
2. Used and assigned new values in the executable section.
3. Passed as parameters or used to hold the output of a PL/SQL subprogram. (A return value)
4. Forward references are not allowed. (Must be declared before referencing)

Declaring and Initialzing

Syntax:

Identifier    [CONSTANT]    datatype    [NOT NULL]         [:= | DEFAULT expr]

 

Examples:

DECLARE     emp_hiredate        DATE;     emp_deptno        NUMBER(2) NOT NULL := 10;     location        VARCHAR2(13) := 'Atlanta';     c_comm        CONSTANT NUMBER := 1400;

 

CONSTANT : Constraints the variable so that its value cannot change; Constants must be initialized.

SET SERVEROUTPUT ON DECLARE myname varchar2(20); BEGIN DBMS_OUTPUT.PUT_LINE('My name is '||myname); myname := 'John'; DBMS_OUTPUT.PUT_LINE('My name is '||myname); END;

 

DECLARE myname varchar2(20) := 'John'; BEGIN myname := 'Steven'; DBMS_OUTPUT.PUT_LINE('My name is '||myname); END;

 

Delimiters in Strings Literals

DECLARE event varchar2(15); BEGIN event := q'!Father's day!'; DBMS_OUTPUT.PUT_LINE('3rd Sunday in JUne is : '||event); event := q'[Mother's day]'; DBMS_OUTPUT.PUT_LINE('2nd Sunday in May is : '||event); END;

• A single quote : double the quotation mark
  ex) event VARCHAR2(15) := 'Father''s day';
• q' is used to define the delimiter.
  event := q'!Father's day!'
  In this example, ! is used as a delimiter.