PL/SQL - Constants and Attributes

Complete Tutorial

Constants

A constant number has to declared and initialized in the declare block only using

CONSTANT keyword.   Value cannot be changed

declare

       r CONSTANT number :=100;

 begin

   /*   r := r + 100; Not possible*/

               dbms_output.put_line(r);

end;

   /*Assigning value to variable from a column of a table using select into clause*/

declare

         x number;

begin

              Select sal Into x from emp

                where ename = 'SMITH';

                 dbms_output.put_line('Salary of Smith is '|| x);

 end;

/* Selecting ename,sal from emp

       Use of more than one columns value with Into clause*/

declare

           n varchar2(50);

           s number;

begin

           select ename, sal Into n, s

           from emp

           where ename = 'SMITH';

           dbms_output.put_line(n);

           dbms_output.put_line(s);

end;

Attributes

% Type Attribute –

• Provides datatype of a variable or column
• Useful when declaring a variable that refers to a column in a database
• exact datatype of column need not be known
• if column definition changes, variable datatype changes accordingly at runtime
• Example

oldfare fare.first_fare%TYPE;

newfare oldfare%TYPE;

declare

        a emp.ename%type;

        b emp.sal%type;

        c emp.deptno%type;

    /*Using %TYPE attribute for variable data type*/

begin

         select ename,sal,deptno

         into a,b,c

         from emp

         where ename = 'KING';

         dbms_output.put_line(a ||'-'||  b ||'-' || c);

  end;

%RowType Attribute –

• Useful when declaring a record variable having same structure as a row in a table or view, or as a row fetched from a cursor
• Fields in the record have same names and datatypes as the columns in the table/view
• Example
• emp_rec employee%ROWTYPE;
• A specific field can be referenced using
• emp_rec.emp_num;

declare

         E emp%rowtype;

         /*rowtype attribute holds the datatype of the columns of the

          entire row*/

begin

         select *  INTO  E

         from emp

         where ename = 'MARTIN';

          dbms_output.put_line(E.sal);

        dbms_output.put_line(E.ename);

        dbms_output.put_line(e.deptno);

 end;

<<<Previous                                                                                                                                                                                  Next>>>

Complete Tutorial