Introduction
I believe that you have successfully completed day 1 of learning basics of PL/SQL Oracle from scratch in seven Days. We will get further into learning more about Oracle PL/SQL.
What we learned last time
On day One we learned the following things:
- Understand the features of PL/SQL
- Understand the PL/SQL Block structure
- Recognize various PL/SQL types, variables and constants
- Create an Anonymous PL/SQL Block
- Printing value of a variable using Anonymous Block
Agenda for Day Two
- Nesting of Anonymous Block into another
- PL/SQL Data Types
Nesting of Anonymous Block into another
In the last session we created a small anonymous block as shown below. Now we will try to nest one more anonymous block inside that.
- DECLARE
- l_count NUMBER;
- Begin
- l_count:=3;
- DECLARE
- i_num NUMBER;
- Begin
- i_num:=2*2;
- DBMS_OUTPUT.PUT_LINE('The value of i_num is ' ||i_num);
- Exception whenothers thennull;
- END;
- DBMS_OUTPUT.PUT_LINE('The value of variable l_count is ' ||l_count);
- EXCEPTION
- WHEN OTHERS THEN
- NULL;
- END;
The reason we want to use nested block is that when we have certain logic which may raise an exception, we want our program to continue irrespective of the Exception.
I have
DBMS_OUTPUT.PUT_LINE in my previous article as well let’s focus on what that actually means.
DBMS_OUTPUT.PUT_LINE is used to display information from a PL/SQL Block,
- DBMS_OUTPUT is a package,
- PUT_LINEis a procedure within that package.
Note: If you are using SQL plus then DBMS_OUTPUT Package needs to be enabled by the following command.
SET SERVEROUTPUT ON;
Let’s try this using SQLPLUS without setting SERVEROUTPUT ON
Figure1: Demonstrating enabling DBMS_OUTPUT in SQLPLUS
We can see the above as I try to execute the anonymous block, it gets executed successfully but no DBMS_OUTPUT.PUT_LINE was shown. Now we will SET SERVEROUTPUT ON and try if now output comes to the screen or not.
Figure 2: DBMS_OUTPUT.PUT_LINE Example
We can clearly see the output shown as 10. So if we want to enable the DBMS_Output package we need to enable it using SET SERVEROUTPUT ON;
BLOCK Label
We can access the block label to access the variables of the block by the help of qualifier i.e. have two variables declared with the same name in the outside block and inside the nested block but we want to access the outside block variable inside the inner and block we can accomplish the same using Qualifier as shown below:
- <<parent>>
- DECLARE
- i_num NUMBER;
- Begin
- i_num:=3;
- DECLARE
- i_num NUMBER;
- Begin
- DBMS_OUTPUT.PUT_LINE('The value of parent i_num is ' ||parent.i_num);
- i_num:=2*2;
- DBMS_OUTPUT.PUT_LINE('The value of inner i_num is ' ||i_num);
- Exception when
- others then
- null;
- END;
- DBMS_OUTPUT.PUT_LINE('The value of variable i_num is ' ||i_num);
- EXCEPTION
- WHEN OTHERS THEN
- NULL;
- END;
By the above example we can see that the scope of the outer block variable is everywhere, whle the scope of the inner variable is just inside the inner block. If I try to access the inner block outside the inner block I get an error as shown below:
Figure 3: Scope of the variable
PL/SQL Data Types The PL/SQL data types are divided into two parts:
- Scalar Data Types: It is used to hold single value number, varchar2, date etc.
- Composite Data Types: It is used to hold one or more item of same type or dissimilar type. PL/SQL Records.
- Reference Types: Reference type is similar to C pointer concepts and can shortly be defined as Cursor in oracle.
- LOB(Large Objects): A large object can either be a binary or character value up to 4gb, for example, BLOB, CLOB, NCLOB and BFILE.
Scalar Data Type
Scalar Data types are the basic data types, these data types can be subdivided into 4 parts,
- Numeric
- Character
- Date & Time
- Boolean
Numeric Data type then can be subdivided into NUMBER data type, PLS/INTEGER OR BINARY INTEGER TYPE OR BINARY FLOAT OR BINDARY DOUBLE Data type.
Number Data type is the most commonly used data type in oracle when we are performing arithmetic operations etc.
Number Data type is portable across all Oracle platforms. Number data type can be defined as fixed point numbers or as floating point numbers.
Fixed point Number: We declare the fixed limit of the type which is:
- Precision: Total number of digits in the number, max value is 38.
- Scale: Round number or decimal places has min value of -84 and max value 127.
- DECLARATION
- NUMBER[(precision,scale)]
Precision is the total number of digits and scale is number of digits to right of the decimal point.
Example: Figure 4: Demonstrating number precision and scale
21.25 can easily be saved inside the v_num which has 4 as precision and 2 as scale. We will get the output as shown below:
Figure 5: Demonstrating number with 4,2
If I try to change the value to 132.25, I will get an exception as v_num is not able to hold 5 precision. Let’s try this practically.
Figure 6: Exception when we try to store more than the declared precision and scale
So we can see the below output of the above test. You would be thinking what is Sqlcode and SQLERM which I will dicuss in the next article.
Floating Points Number
Numbers are also used to represent floating point numbers i.e. we are not defining the precision and scale. Now the number data type we define is capable of holding all these numbers varying the number of total digits or after the decimal points; let us try it practically.
Figure 7: Demonstrating floating point variable
Here in the above figure we can clearly see that a NUMBER data type can easily represent the floating point numbers.
Constant: Data types are used to hold values which remain the same during the execution of PL/SQL.
- Values cannot be changed
- Constant is defined in the declaration section
Figure 8: Demonstrating Constant variable
Let’s try to change the value of constant and see what happens.
Figure 9: Exception while changing the Constant variable
We can clearly see that PL/SQL doesn’t allow us to change the value of the constant datatypes.
Sub Types Data types: Sub type data types are subset of Base data type.
These are of two types:
- Constraint: INTEGER => NUMBER(38,0)
- Unconstraint: NUMERIC(P,S) => NUMBER(P,S)
Number Type Sub Type:
Sub Type | Type | Maximum Precision |
Dec/ Decimal/Numeric | Fixed point(need to specify precision and scale) | 38 digits |
Double/ Float | Floating Points | 38 digits |
REAL | Floating Points | 38 digits |
INT/ INTEGER/ SMALLINT | Fixed Point | 38 digits |
Table 1: Number Type Sub Type
Let’s try them practically.
Here, I created an anonymous block with the subtypes and assigned them a floating value as written below:
- DECLARE
-
- v_dec DEC(10,2);
- v_decimal DECIMAL(10,2);
- v_numeric NUMERIC(10,2);
- v_float FLOAT;
- v_real REAL;
- v_int INT;
- v_integer INTEGER;
- v_smallint SMALLINT;
- Begin
- v_dec:=199.12;
- v_decimal:=199.12;
- v_numeric:=199.12;
- v_float:=199.12;
- v_real:=199.12;
- v_int:=199.12;
- v_integer:=199.12;
- v_smallint:=199.12;
- DBMS_OUTPUT.PUT_LINE(v_dec);
- DBMS_OUTPUT.PUT_LINE(v_decimal);
- DBMS_OUTPUT.PUT_LINE(v_numeric);
- DBMS_OUTPUT.PUT_LINE(v_float);
- DBMS_OUTPUT.PUT_LINE(v_real);
- DBMS_OUTPUT.PUT_LINE(v_int);
- DBMS_OUTPUT.PUT_LINE(v_integer);
- DBMS_OUTPUT.PUT_LINE(v_smallint);
- END;
Figure 10: Output of Subtype variables
% Type Attribute:
Type attribute allows us to declare the data type of the variable the same as the previously declared variable, field, records, nested table or database column. If the reference type changes the declaration automatically changes. The variable declared with %TYPE is the referencing item.
Let’s see practically:
- DECLARE
- v_num NUMBER;
- v_num2_type v_num%TYPE;
- BEGIN
- v_num:=10.2;
- v_num2_type:=100.2;
- DBMS_OUTPUT.PUT_LINE(v_num);
- DBMS_OUTPUT.PUT_LINE(v_num2_type);
- END;
Output: Figure 11: Demonstrating % Type Attribute
Defining variable of type table Column, so that if we change the type of the column we need not worry in the procedure level.
For this first we create a simple table,
- CREATE TABLE TblEmployee (Id NUMBER PRIMARY KEY, NAME VARCHAR2(50));
Now in the anonymous block I will use the TYPE keyword to declare the variable of type table column as shown below:
- DECLARE
- v_nameTblEmployee.NAME%TYPE;
- BEGIN
- v_name:='SAILLESH PAWAR';
- DBMS_OUTPUT.PUT_LINE('THE NAME OF THE EMPLOYEE IS '||v_name);
- END;
Figure 12: Demonstrating % Type Attribute of type table column
When we declare the variable as the table column type, the table constraints are not applicable to the table which means if the table column is not null and we use TblEmployee.NAME%TYPE which is a NOT NULL type it will not be applicable to the variable. Let’s unleash it practically.
- DECLARE
- v_nameTblEmployee.NAME%TYPE;
- BEGIN
- DBMS_OUTPUT.PUT_LINE('THE NAME OF THE EMPLOYEE IS '||v_name);
- END;
Figure 13: Demonstrating % Type Attribute of type table column
Now here we can clearly see as we have not assigned the value to the v_name it’s by default set to null.
PLS_INTEGER / BINARY_INTEGER PLS_INTEGER is a data type used for storing signed integers. PLS_INTEGER are faster as they use machines. That means, they are generally faster than NUMBER and INTEGER. We should use them for integer calculations and loop counters.
SUBTYPE | DESCRIPTION Type |
NATURAL | Non Negative PLS Integer. |
NATURALN | Non Negative PLS Integer with Null constraint. |
POSITIVE | Positive PLS Integer. |
POSITIVEN | Positive PLS Integer with Null constraint. |
SIGNTYPE | -1,0,1 |
Table 2: PLS_INTEGER SUBTYPE
NATURAL:
- DECLARE
- p1 NATURAL := -2147483647;
- BEGIN
- SYS.DBMS_OUTPUT.PUT_LINE(p1);
- END;
- /
Output: Figure 14: Demonstrating NATURAL variable cannot hold –ve values
NATURAL only accepts positive integers so an exception was thrown. Now let’s try it with a positive number.
Figure 15: Demonstrating NATURAL assigning +ve value
Trying with NULL value
Figure 16: Demonstrating NATURAL data type can hold NULL value
If we want NULL Constraint then we have to
NATURALN as shown below:
Figure 17: Demonstrating NATURALNULL constraint
POSITIVE Type - DECLARE
- p1 POSITIVE;
- BEGIN
- p1:=10;
- SYS.DBMS_OUTPUT.PUT_LINE('The value of p1 is '||p1);
- END;
- /
Output: Figure 18: Demonstrating POSITIVE type
If we try to store 0, -ve values in POSITIVE, then it will throw an EXCEPTION as shown below:
Figure 19: POSITIVE type cannot store 0 or –ve values
But if I try to store null into POSITIVE type it will store the same as shown below:
- DECLARE
- p1 POSITIVE;
- BEGIN
- SYS.DBMS_OUTPUT.PUT_LINE('The value of p1 is '||p1);
- END;
Figure 20: POSITIVE can store NULL values
POSITIVEN Type When we want to constrain POSITIVE with NULL then we make use of
POSITIVEN type. Let’s try practically.
Figure 21: POSITIVEN constraints NULL value
SIGN TYPE: Sign Type can only store three values -1, 0, 1. It helps you to restrict an integer variable to the values (-1, 0, 1).
As this topic is vast we will be covering the rest of the data types in the day three article.
Read more articles on Databases: