Oracle PL/SQL Tutorial - PL/SQL Variables Value

Constants

To declare a constant, put the keyword CONSTANT before the type specifier.

A constant must be initialized in its declaration.

Constants are initialized every time a block or subprogram is entered.

The following code shows how to define a constant of type REAL and assigns an unchangeable value of 5 to the constant.


DECLARE 
  n_real      CONSTANT REAL    := 5.00; 
  n_value  CONSTANT INTEGER := 3; 
  n_boolean      CONSTANT BOOLEAN := FALSE; 
BEGIN 
  NULL; 
END; 
/

The code above generates the following result.

DEFAULT

We can use the keyword DEFAULT instead of the assignment operator to initialize variables.

You can use DEFAULT to initialize subprogram parameters, cursor parameters, and fields in a user-defined record.

Use DEFAULT for variables that have a typical value. Use the assignment operator for variables that have no typical value.


DECLARE 
  n_char       CHAR DEFAULT 'O';         -- Same as n_char CHAR := 'O'; 
  n_length     INTEGER DEFAULT 40;  -- Typical value 
  n_count      INTEGER := 0;        -- No typical value 
 
BEGIN 
  NULL; 
END; 
/

The code above generates the following result.

NOT NULL

A declaration can impose the NOT NULL constraint.

NOT NULL prevents you from assigning a null value to the variable.

A declaration that specifies NOT NULL must specify a default value.


DECLARE 
  i_id INTEGER(4) NOT NULL := 9999; 
BEGIN 
  NULL; 
END; 
/

%TYPE

We can use %TYPE to Declare Variables of the Types of Table Columns


CREATE TABLE EMP ( 
  empid  NUMBER(6) NOT NULL PRIMARY KEY, 
  deptid NUMBER(6) CONSTRAINT c_EMP_deptid CHECK (deptid BETWEEN 100 AND 200), 
  deptname VARCHAR2(30) DEFAULT 'Sales' 
); -- from  w w w  . ja  va  2 s  . com
  
DECLARE 
  v_empid    EMP.empid%TYPE; 
  v_deptid   EMP.deptid%TYPE; 
  v_deptname EMP.deptname%TYPE; 
BEGIN 
  v_empid := NULL; 
  v_deptid := 50;  
  DBMS_OUTPUT.PUT_LINE ('v_deptname: ' || v_deptname);
END; 
/

%ROWTYPE Attribute

%ROWTYPE attribute declares a record that represents a row in a table.

To reference a field in the record, use record_name.field_name.

The record fields do not inherit the constraints or default values of the corresponding columns.

If the referenced item table changes, your declaration is automatically updated.


CREATE TABLE EMP ( 
  empid  NUMBER(6) NOT NULL PRIMARY KEY, 
  deptid NUMBER(6) CONSTRAINT c_EMP_deptid CHECK (deptid BETWEEN 100 AND 200), 
  deptname VARCHAR2(30) DEFAULT 'Sales' 
); -- ww w.  j a  va2 s.  co m
  
DECLARE 
  emprec  EMP%ROWTYPE; 
BEGIN 
  emprec.empid := NULL; 
  emprec.deptid := 50;  
  DBMS_OUTPUT.PUT_LINE ('emprec.deptname: ' || emprec.deptname); 
END; 
/

The following code shows how to declare a Record that Represents a Subset of Table Columns.


DECLARE 
      CURSOR c1 IS SELECT id, name FROM departments; 
      dept_rec c1%ROWTYPE;  -- includes subset of columns in table 
BEGIN 
  NULL; 
END; 
/

The following code shows how to declare a Record that Represents a Row from a Join.


DECLARE -- from  ww w .j a  va2  s  .  c o m
      CURSOR c2 IS 
        SELECT employee_id, email, employees.manager_id, location_id 
          FROM employees, departments; 
      join_rec  c2%ROWTYPE;  -- includes columns from two tables 
 
BEGIN 
      NULL; 
END; 
/