Complete Tutorial
Variables :
- Used to store results of a query for later processing, or to calculate values to be inserted into database tables
- can be used anywhere in an expression, either in SQL or PL/SQL statements
- must be declared before referencing it in other statements, including other declarative statements
- are declared by specifying the name along with the datatype
- can be declared to be of any datatype native to Oracle.
Examples
oldfare NUMBER(5);
m_name VARCHAR(15);
(Note – Set Serveroutput
On has to be given when a session starts for displaying the output statements_)
declare
x number;
begin
x := 67;
dbms_output.put_line(x);
dbms_output.put_line('The value of x is
'|| x);
end;
Declaring variable in declare block:
- Assigning value in in begin block using := .
- Output statement is dbms_output.put_line
- Concatenation operator is ||
- Command terminator is ; after end
Declaring and initializing variables together
declare
y number := 100;
begin
dbms_output.put_line('The value
of y is '|| y);
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;
