PL/SQL - Variables

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

*/