, , ,

Assignments in PL SQL

Variables and constants are initialized every time a block or subprogram is entered.
By default, variables are initialized to NULL. So, unless you expressly initialize a variable, its value is undefined, as the following example shows:
count INTEGER;

count := count + 1; — assigns a null to count

The expression on the right of the assignment operator yields NULL because count is null. To avoid unexpected results, never reference a variable before you assign it a value.
You can use assignment statements to assign values to a variable. For example, the following statement assigns a new value to the variable bonus, overwriting its old value:
bonus := salary * 0.15;
The expression following the assignment operator can be arbitrarily complex, but it must yield a datatype that is the same as or convertible to the datatype of the variable.
Boolean Values
Only the values TRUE, FALSE, and NULL can be assigned to a Boolean variable. For example, given the declaration
the following statements are legal:
done := FALSE;


When applied to an expression, the relational operators return a Boolean value. So, the following assignment is legal:
done := (count > 500);
Expressions and Comparisons

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:
my_empno emp.empno%TYPE;
my_ename emp.ename%TYPE;
wages NUMBER(7,2);

SELECT ename, sal + comm
INTO last_name, wages FROM emp
WHERE empno = emp_id;

However, you cannot select column values into a Boolean variable.
Quick notes -Assignment
1. := (ASSIGNMENT ) whereas = (VALUE EQUALITY)
2. The datatype of the left and right hand side of an assignment must be the same or implicitly convertible to each other.                    
For ex. , N:=‘7’ is legal because number may be implicitly converted to char.
3. Column or table reference are not allowed on either side of an assignment operator( : = ).
                SCOTT.EMP.EMPNO := 1234;
                  location := dept.loc.;    

Above two are incorrect.
0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *