Learn Basics Of PL/SQL Oracle From Scratch In 7 Days

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 --DECLRATION //OUTER BLOCK
l_count NUMBER; --DECLARING VARIABLE
Begin --BEGIN BLOCK
l_count:=3; --INITIALIZATION
DECLARE --NESTED BLOCK INNER BLOCK
i_num NUMBER;
Begin
i_num:=2*2;
DBMS_OUTPUT.PUT_LINE('The value of i_num is ' ||i_num);--PRINTING VALUE OF l_count
Exception whenothers thennull;
END;--NESTED INNER BLOCK ENDS HERE
DBMS_OUTPUT.PUT_LINE('The value of variable l_count is ' ||l_count);--PRINTING VALUE OF l_count
EXCEPTION --TRAPPING THE EXCEPTION
WHEN OTHERS THEN
NULL;
END;--END OF ANONYMOYS BLOCK

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.

Example
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>> --label Qualifier
DECLARE --DECLRATION //OUTER BLOCK
i_num NUMBER; --DECLARING VARIABLE
Begin --BEGIN BLOCK
i_num:=3; --INITIALIZATION
DECLARE --NESTED BLOCK INNER BLOCK
i_num NUMBER;
Begin
DBMS_OUTPUT.PUT_LINE('The value of parent i_num is ' ||parent.i_num); --accessing outer block variable
i_num:=2*2;
DBMS_OUTPUT.PUT_LINE('The value of inner i_num is ' ||i_num);--PRINTING VALUE OF l_count
Exception when
others then
null;
END;
DBMS_OUTPUT.PUT_LINE('The value of variable i_num is ' ||i_num);--PRINTING VALUE OF l_count
EXCEPTION --TRAPPING THE EXCEPTION
WHEN OTHERS THEN
NULL;
END;--END OF ANONYMOYS BLOCK

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; --Declaring v_num as NUMBER
v_num2_type v_num%TYPE; --Declaring v_num2_type of type v_num
BEGIN
v_num:=10.2; --Assigning value to v_num
v_num2_type:=100.2;--Assigning value to v_num2_type
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;--trying to hold negative value in NATURAL
BEGIN
SYS.DBMS_OUTPUT.PUT_LINE(p1);
END;
/

Output:

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:
Demonstrating NATURALNNULL constraint

Figure 17: Demonstrating NATURALNULL constraint

POSITIVE Type

DECLARE
p1 POSITIVE;--trying to hold value in 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;--trying to hold null value in POSTIVE
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.