Programming Oracle Triggers and Stored Procedures (3rd Edition) (Prentice Hall PTR Oracle Series)

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;

Related

Категории