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> 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.
-- 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:
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:
<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.
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 .
Similarly, programmer can declare a sub-type and its syntax is as follows:<
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 OUT||Attach 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.|
Operators in PL/SQLPL/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
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:-
Execute above PL/SQL program and we get following output on console:(For the time being ignore, syntax of IF-ELSE).
(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