Assigning values to a variable

Assigning values to a variable

You can assign an initial value to a variable in the declaration section.By default, variables are initialized to NULL.
You can assign values to a variable by using an assignment operator (:=), a colon followed by an equal sign. You place the variable to the left of the operator and an expression to the right. Remember, reserved word cannot be used as a variable.
Eg:
pi number(5,2):=3.14;


Database Values
You can use the SELECT statement to have Oracle assign values to a variable. For each item in the select list, there must be a corresponding, type-compatible variable in the INTO list. An example follows:
DECLARE
my_empno emp.empno%TYPE;
my_ename emp.ename%TYPE;
wages NUMBER(7,2);
BEGIN
...
SELECT ename, sal + comm
INTO last_name, wages FROM emp
WHERE empno = emp_id;

Let us take an example of finding the area of a circle.the result is stored in atable named AREAS,which has two columns to store radius and area values.The area of circle is calculated by squaring the value fot the circle radius nad multiplying that the constant pi.
Before writing the PL/SQL block,you need to create atable named AREAS from the sql prompt.
SQL> Create table areas (radius number(2),area number(7,2));
For writing the PL/SQL block a new file named area.sql is created.
SQL>ed area.sql
PL/SQL block:
Declare
pi constant number(7,2)=3.14;
radius number(5)
area number(14,2)
Begin
Radius:=3
area:=pi*power(radius,2)
insert into areas values(radius,area);
end;
Note:Pl/SQL block always ends with the keyword end
Now save the changes into area.sql file and close the notepad window.
You can execute the PL/SQL block from the SQL command prompt.
SQL>@area.sql
PL/SQl procedure successfully completed..
Now you can verify the result by querying the table area.
SQL>Select * from areas;
Radius Area
3 25.27