PL/SQL - Variables

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;

<<<Previous                                                                                                                                                                                  Next>>>

Complete Tutorial