PL/SQL - Constants and Attributes

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

*/