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;
