PL/SQL - Variables

Variables

• To store results of a query for later processing
• To calculate values to be inserted into database tables
• Used anywhere in an expression, either in SQL or PL/SQL statements
• Should be declared before referencing it in other statements, including other declarative statements
• Declared by specifying the name along with the datatype
• Declared to be of any datatype native to Oracle.

Examples

Customer_Number NUMBER(5);

Customer_Name VARCHAR(15);

(Set Serveroutput On has to be given when a session starts for displaying the output statements)

declare

Customer_Name VARCHAR2(100);

begin

Customer_Name := "ASK HAREESH"    

dbms_output.put_line('Customer Name is: '|| Customer_Name);

end;

Declaring variable in declare block:

• Use := to assign value to variable
• Use dbms_output.put_line to print the message in log
• Use || to concatenate variables
• Use ; as end of the line

Declaring and initializing variables together

declare

Customer_Number number := 7890;

begin

dbms_output.put_line('Customer Number is: '|| Customer_Number);

end;

Taking value from the user using &

declare

z number;

a varchar2(10);

begin

z := &z;

a := '&a';

dbms_output.put_line('Z is  '|| z);

dbms_output.put_line('A is '|| a);

end;

/*Cannot declare or initialize more than one variable simultaneously*/

declare

a number;

b number;

c number;

begin

a := 67; b := 90;  c := 87;

dbms_output.put_line(a);

dbms_output.put_line(b);

end;

Complete Tutorial