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:
DECLARE
count INTEGER;
…
BEGIN
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
DECLARE
done BOOLEAN;the following statements are legal:
BEGIN
done := FALSE;
WHILE NOT done LOOP
…
END LOOP;
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:
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;
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.
By default, variables are initialized to NULL. So, unless you expressly initialize a variable, its value is undefined, as the following example shows:
DECLARE
count INTEGER;
…
BEGIN
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
DECLARE
done BOOLEAN;the following statements are legal:
BEGIN
done := FALSE;
WHILE NOT done LOOP
…
END LOOP;
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:
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;
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.
Recent Comments