Oracle PL/SQL - Assigning Values to Variables with the Assignment Statement

  1. HOME
  2. Oracle PL/SQL
  3. PL SQL Introduction
  4. Variable Declarations

Introduction

To assign the value of an expression to a variable, use this form of the assignment statement:

Here is the syntax of assigning Values to Variables, 

variable_name := expression;

The following code declares several variables and then uses assignment statements to assign the values of expressions to them.

Demo

SQL>
SQL> --create table and use it later
SQL> drop TABLE emp;

Table dropped.-- from  w  w  w  . ja  v a  2 s .  c o m

SQL>
SQL> CREATE TABLE emp(
  2  empid NUMBER(6),
  3  first_name VARCHAR2(20),
  4  last_name VARCHAR2(25)) ;
SQL>
SQL>
SQL> DECLARE
  2    wages          NUMBER;
  3    my_val_two   NUMBER := 40;
  4    my_val  NUMBER := 22.50;
  5    bonus          NUMBER := 150;
  6    country        VARCHAR2(128);
  7    counter        NUMBER := 0;
  8    done           BOOLEAN;
  9    valid_id       BOOLEAN;
 10    emp_rec1       emp%ROWTYPE;
 11    emp_rec2       emp%ROWTYPE;
 12    TYPE commissions IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
 13    comm_tab       commissions;
 14  BEGIN
 15    wages := (my_val_two * my_val) + bonus;
 16    country := 'New value';
 17    country := UPPER('Canada');
 18    done := (counter > 100);
 19    valid_id := TRUE;
 20    emp_rec1.first_name := 'Jack';
 21    emp_rec1.last_name := 'Last Name';
 22    emp_rec1 := emp_rec2;
 23    comm_tab(5) := 20000 * 0.15;
 24    DBMS_OUTPUT.PUT_LINE('wages:' || TO_CHAR(wages));
 25    DBMS_OUTPUT.PUT_LINE('country:' || country);
 26    --
 27    DBMS_OUTPUT.PUT_LINE('valid_id:' || TO_CHAR(valid_id));
 28    DBMS_OUTPUT.PUT_LINE('emp_rec1.first_name:' || TO_CHAR(emp_rec1.first_name));
 29    DBMS_OUTPUT.PUT_LINE('emp_rec1.last_name:' || TO_CHAR(emp_rec1.last_name));
 30
 31  END;
 32  /

SQL>

Related Topic