PL/SQL - Declaration and Operators

byNikhil Ranjan •
2
In previous post, we discussed various commonly used data types in PL/SQL and it's characteristics. Here we will discuss about variable declaration, Constants and Operators in PL/SQL.

Variable Declarations:- 

Like many other programming language, in PL/SQL we must declare a variable before referring it. All declaration is made in declaration block of PL/SQL program unit (Procedure, Function,Trigger, Type,Body,etc). Syntax of variable declaration:- 
<variable_name>  <datatype>  [default value assignment(optional )];
<variable_name> is a valid identifier as variable name (Remember , PL/SQL is case insensitive)
<data_type>  refers type of data being stored  in this variable. Based on data type declaration, PL/SQL allocates memory to the variable and validates values assigned to it and
<default value> to a variable can be assigned while declaring it , it is optional.
Example:- 
-- it is Constrained Declarations, since upper limit is 12 
employee_Id  NUMBER(12); 
-- it is unconstrained declaration ,default size is 38.
emp_salary NUMBER;
-- it is mandatory to assign size of varchar2 type, else throw error: PLS-00215: String length constraints must be in range (1 .. 32767)
emp_name VARCHAR2(150);  
Default value to any variable can be assigned in two ways as shown below:
<variable_name> <datatype> := <default_value>;
<variable_name> <datatype> DEFAULT <default_value>;
Example:-
org_id  VARCHAR2(23) DEFAULT 'CAUIO' ;
org_id  VARCHAR2(23) := 'CAUIO' ;

NOT NULL clause may be added with default declaration indicating - value of this variable cannot be NULL. NULL is PL/SQL?
Example:-  locality_info VARCHAR2(60) NOT NULL DEFAULT 'ABC';
It is mandatory to assign a default value when NOT NULL clause is used, otherwise it will give error: "PLS-00218: a variable declared NOT NULL must have an initialization assignment"

%TYPE :- In all above examples, we have specified data type while declaring variables. In PL/SQL we can have type declaration dependent on type of other elements (Table column or other PL/SQL variable data type ),Such declaration is termed as anchored declaration. Syntax of anchored declaration:
<variable name>  <type attribute>%TYPE   [optional default value assignment];
<type attribute> can be : previously declared PL/SQL variable name and Table column name in format - "table.column".
Example:- In following PL/SQL block, variable default_company_id is of  type DEPARTMENT_ID of EMPLOYEES table and company_id is of type default_company_id.

DECLARE 
-- Here EMPLOYEE is table name and DEPARTMENT_ID is column name 
default_company_id EMPLOYEES.DEPARTMENT_ID%TYPE DEFAULT '007' ; 

-- Here company_id inheriting type form previously decalred default_company_id
 company_id default_company_id%TYPE ; 
        
SUBTYPE three_value_logic IS VARCHAR2 IN ('YES', 'NO', 'MAYBE'); -- invalid in 11g
SUBTYPE prefix_type IS CHAR(3);  -- not invalid in 11g 
BEGIN
null;
END;
The use of  %TYPE indicates integration of programming constructs of PL/SQL with underlying database (accessed through SQL). When PL/SQL block is compiled, %TYPE declaration anchors the datatype of one variable to that of another, so any change made to source code must be recompiled.

Advantage of  %TYPE(anchored declaration):-
1.Synchronization with database columns - change in table structure is automatically taken care if we rely on %TYPE instead of explicitly declaring datatype of variable.
2.Normalization of local variables - If possible instead of repeating same datatype each time, it better to use reference of another variable which has concrete datatype declaration.
In PL/SQL, we have another declaration attribute %ROWTYPE, which we will revisit later. What is difference between %TYPE and %ROWTYPE in PL/SQL.

User defined sub-types :- In PL/SQL, programmer can declare sub-type of a datatype and using same set of rules as the original datatype, but might allow only a subset of the data type's values.
The standard PL/SQL package uses it for declaring sub-types, for example: POSITIVE is subtype of BINARY_INTEGER with values greater than 0 and FLOAT is subtype of NUMBER .
SUBTYPE POSITIVE IS BINARY_INTEGER RANGE 1 .. 2147483647; -- constrained declaration SUBTYPE FLOAT IS NUMBER;     ---- unconstrained declaration
Similarly, programmer can declare a sub-type and its syntax is as follows:<
SUBTYPE <subtype_name> IS <base_type> ;
subtype_name is a valid identifier and base_type is the datatype which forms the basis for the subtype (Base type can be any of the following :  PL/SQL datatype, variable_name%TYPE, table_name%ROWTYPE, cursor_name%ROWTYPE, PL/SQL table).
Example:-  SUBTYPE hire_date_type IS DATE;  -- subtype of predefined datatype
--- subtype a table of predefined type
TYPE room_tab IS TABLE OF NUMBER(3) ;
SUBTYPE hotel_room_tab_type IS room_tab;
-- subtype based on datatype of a table's column
SUBTYPE last_name_type IS employee.last_name%TYPE;
Note:- We cannot put restriction on subtype, it is an invalid declaration. Example of invalid declaration : SUBTYPE three_value_logic IS VARCHAR2 IN ('YES', 'NO', 'MAYBE'); 

Naming convention:- As suggested in other languages, it is best practice to follow some predefined pattern to name variable so that readability of program is maintained and any new programmer can understand program unit very easily.Below is the list of variable type and its corresponding naming pattern:
Variable types Naming Convention
Module parameter - IN, OUT, IN OUTAttach the parameter mode as a suffix or prefix: append _in as suffix or prefix for IN parameter
Cursor Append _cur or _cursor to the cursor variable name
Record based on table or cursor name of the table or cursor with a _rec or _record suffix. Suppose the cursor name is company_cur, then a record based on that cursor would be called company_rec.
Named constant prefix the name with a _c  Example : c_last_date CONSTANT DATE := SYSDATE:
PL/SQL table TYPE and PL/SQL table For table TYPE : suffix _tabtype to indicate that this is not actually a PL/SQL table, but a type of table. For Table: suffix variable with _tab/_arrays/_table Note: PL/SQL tables which are similar to one-dimensional arrays, we will revisit again and learn more about it later.
For more detail about naming convention refer this. 

Operators in PL/SQL 

PL/SQL(a procedural database programming language) support various operators such as Arithmetic operators,Relational operators,Comparison operators,Logical operators.
Arithmetic operator:- Addition (+), subtraction(-) , Multiplication (*),  division (/)  are known arithmetic  operators and nothing is new about them. Exponentiation operator (**)  needs special mention here -  it raises one operand to the power of other . Example :  2**3 = 8
We do not have modular division (%) in PL/SQL, however we have REMAINDER( m, n ) function.
Example : REMAINDER( 15, 5 ) outcome = 0
REMAINDER( 12, 5 )  result = 2

Relational operators :-  Relational operators compare two expression/values and returns Boolean (TRUE and FALSE), remember PL/SQL support Boolean datatype which is missing in  by oracle RDBMS. The most commonly relational operators are   =, != , <> , >= , <= .  Following PL/SQL program demonstrate uses of relational operators:-
DECLARE 
  a NUMBER (3) := 134;
  b NUMBER (3) := 234;
  c NUMBER (3) := 234;
BEGIN
  IF (a <> b) then  -- not equal to test 
      dbms_output.put_line('(Operator <>): '||a || ' is not equal to '|| b);
  ELSE
      dbms_output.put_line('(Operator <>):   '||a || ' is equal to'|| b);
  END IF;
   IF (b = c) then -- equal to test 
     dbms_output.put_line('(Operator = ): ' || b || ' is  equal to '|| c);
  ELSE 
      dbms_output.put_line('(Operator = ):' || b || ' is  not equal to '|| c);
  END IF;
   IF (a != c) then --not equal to test
      dbms_output.put_line('(Operator !=): ' || a || ' is  not equal to '|| c);
  ELSE
      dbms_output.put_line('(Operator !=):' || a || ' is not equal to '|| c);
  END IF;
   IF (a >= c) then  -- greater than equal to test 
       dbms_output.put_line('(Operator>=): ' || a || ' is greater equal to '|| c);
  ELSE
      dbms_output.put_line('(Operator>=):  ' || a || ' is less than  ' || c);
  END IF;
END;
Execute above PL/SQL program and we get following output on console:(For the time being ignore, syntax of IF-ELSE).
=======sample output===========
(Operator <>): 134 is not equal to 234
(Operator = ): 234 is  equal to 234
(Operator !=): 134 is  not equal to 234
(Operator>=):  134 is less than  234
============================
Comparison operator :- In PL/SQL "LIKE", "IN", "BETWEEN" , "IS NULL" is know comparison operators. It can be applied on two expression and return TRUE, FALSE and NULL. Following program demonstrate the use of comparison operator.
DECLARE 
  a NUMBER (3) := 134;
  b NUMBER (3) := 234;
  c NUMBER (3) := 345;
  emp_first_name  VARCHAR(25) := 'MIKE JANES';
  income NUMBER(14) := NULL;
BEGIN
  IF (b  BETWEEN a AND c) then  -- BETWEEN test 
      dbms_output.put_line('(Operator BETWEEEN): '|| b || ' is between '|| a ||' and '||c)
  ELSE
      dbms_output.put_line('(Operator <>):   '||a || ' is equal to'|| b);
  END IF;
 IF (emp_first_name LIKE 'MI%ES') then  -- LIKE OPERATOR, % can be any thing 
      dbms_output.put_line('(Operator LIKE): '|| emp_first_name || ' passes 
LIKE Test ');
  ELSE
      dbms_output.put_line('(Operator LIKE): '|| emp_first_name || ' does not');
  END IF;
  IF (a IN (278,134,12)) then  -- IN OPERATOR ,test a is there in that set 
      dbms_output.put_line('(Operator IN): '|| a || ' is part of the set ');
  ELSE
      dbms_output.put_line('(Operator IN): '|| a || ' is not part of the set');
  END IF;
  IF (income IS NULL) then  -- IS NULL OPERATOR ,test income is NULL or not 
      dbms_output.put_line('(Operator IN NULL): '|| 'income  is NULL ');
  ELSE
      dbms_output.put_line('(Operator IS NULL): '|| 'income is not NULL');
  END IF;
END;

Logical operators:- In PL/SQL we have three logical operator known : AND, OR and NOT. Logical operator is applied on booleans, it means operands must be an expression returning boolean type.
IF both operand is TRUE, AND returns true.IF any one operand is true OR returns true and NOT is a complement operator, it changes true to false and vice versa.

This is all about variable declaration and operators in PL/SQL. In next post we will discuss another building blocks of PL/SQL- Control statements in PL/SQL - IF-ELSE, CASE , LOOP, etc.

Previous: PL/SQL- Language fundamentals and Data Types Next: Control statements in PL/SQL
Tags:

2 Comments

Previous Post Next Post