11.2. Assignments and Statements
The assignment operator is:
":="
The assignment operator is not “=”, but the if-test expression is.
IF (a = b)
We do not code:
IF (a == b)
You can assign literal values, or expressions that include operations and other variables. You can wrap expressions provided the statement does not break in the middle of a variable name.
base_monthly_salary := 10000;
base_salary_increase := 1000;
new_professor_salary := base_monthly_salary * 1.1;
new_profrssor_salary := base_monthly_salary +
base_salary_increase;
You can declare a variable with an initialized value. You can initialize it with NULL. Do not assume any initial value of a variable that is declared with no initial setting. You can declare an initialized variable and constrain it to NOT NULL. The following PL/SQL declares several variables.
DECLARE
total_credits NUMBER(3,1);
total_credits_2 NUMBER(3,1) := NULL;
credit_hour NUMBER(2,1) := 3.0;
no_credit CONSTANT NUMBER(2,1) := 0;
pass_fail_credit NUMBER(2,1) NOT NULL := 0;
BEGIN
PL/SQL code here.
END;
| TOTAL_CREDITS | This is a basic declaration of a NUMBER for three digits: two to the left of the decimal point, one place to the right. |
| TOTAL_CREDITS_2 | Same as TOTAL_CREDITS except the variable is initialized to NULL. |
| CREDIT_HOUR | This variable has a default assignment. The value can change anytime within the code block. |
| NO_CREDIT | This is a constant. Constants are a form of self-documenting code. When you use a constant, you are documenting the fact that this variable is to have no other value. |
| PASS_FAIL_CREDIT | If you declare a variable as NOT NULL, then you must assign a default value in the same declaration. |
You can code a NULL statement. For example, the following is a valid PL/SQL block and when executed, declares variables and exits.
DECLARE
total_credits NUMBER(3,1);
total_credits_2 NUMBER(3,1) := NULL;
credit_hour NUMBER(2,1) := 3.0;
no_credit CONSTANT NUMBER(2,1) := 0;
pass_fail_credit NUMBER(2,1) NOT NULL := 0;
BEGIN
NULL;
END;
A common use of the NULL statement is within an exception handler where you want to catch the exception but intend no action. For example:
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN NULL;
END;