Databases & DBA  

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

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:

  1. Understand the features of PL/SQL
  2. Understand the PL/SQL Block structure
  3. Recognize various PL/SQL types, variables and constants
  4. Create an Anonymous PL/SQL Block
  5. Printing value of a variable using Anonymous Block

Agenda for Day Two

  1. Nesting of Anonymous Block into another
  2. 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.

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

Demonstrating enabling
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:

  1. <<parent>> --label Qualifier  
  2. DECLARE --DECLRATION //OUTER BLOCK  
  3. i_num NUMBER; --DECLARING VARIABLE   
  4. Begin --BEGIN BLOCK  
  5. i_num:=3; --INITIALIZATION  
  6. DECLARE --NESTED BLOCK INNER BLOCK  
  7. i_num NUMBER;  
  8. Begin  
  9. DBMS_OUTPUT.PUT_LINE('The value of parent i_num is ' ||parent.i_num); --accessing outer block variable  
  10. i_num:=2*2;  
  11. DBMS_OUTPUT.PUT_LINE('The value of inner i_num is ' ||i_num);--PRINTING VALUE OF l_count  
  12. Exception when  
  13. others then  
  14. null;  
  15. END;  
  16. DBMS_OUTPUT.PUT_LINE('The value of variable i_num is ' ||i_num);--PRINTING VALUE OF l_count  
  17. EXCEPTION --TRAPPING THE EXCEPTION  
  18. WHEN OTHERS THEN   
  19. NULL;  
  20. 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:

Scope of the variable
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,

  1. Numeric
  2. Character
  3. Date & Time
  4. 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.
  1. DECLARATION  
  2. NUMBER[(precision,scale)]  
Precision is the total number of digits and scale is number of digits to right of the decimal point.

Example:

Demonstrating number precision
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:

Demonstrating number
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.

Exception when we try to store
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.

Demonstrating floating point variable
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

Demonstrating Constant variable
Figure 8: Demonstrating Constant variable

Let’s try to change the value of constant and see what happens.

Exception while changing the Constant variable
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:

  1. DECLARE  
  2.   
  3. v_dec DEC(10,2);  
  4. v_decimal DECIMAL(10,2);  
  5. v_numeric NUMERIC(10,2);  
  6. v_float FLOAT;  
  7. v_real REAL;  
  8. v_int INT;  
  9. v_integer INTEGER;  
  10. v_smallint SMALLINT;  
  11. Begin  
  12. v_dec:=199.12;  
  13. v_decimal:=199.12;  
  14. v_numeric:=199.12;  
  15. v_float:=199.12;  
  16. v_real:=199.12;  
  17. v_int:=199.12;  
  18. v_integer:=199.12;  
  19. v_smallint:=199.12;  
  20. DBMS_OUTPUT.PUT_LINE(v_dec);  
  21. DBMS_OUTPUT.PUT_LINE(v_decimal);  
  22. DBMS_OUTPUT.PUT_LINE(v_numeric);  
  23. DBMS_OUTPUT.PUT_LINE(v_float);  
  24. DBMS_OUTPUT.PUT_LINE(v_real);  
  25. DBMS_OUTPUT.PUT_LINE(v_int);  
  26. DBMS_OUTPUT.PUT_LINE(v_integer);  
  27. DBMS_OUTPUT.PUT_LINE(v_smallint);  
  28. END;  
Output of Subtypes variables
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:
  1. DECLARE  
  2. v_num NUMBER; --Declaring v_num as NUMBER  
  3. v_num2_type v_num%TYPE; --Declaring v_num2_type of type v_num  
  4. BEGIN  
  5. v_num:=10.2; --Assigning value to v_num  
  6. v_num2_type:=100.2;--Assigning value to v_num2_type  
  7. DBMS_OUTPUT.PUT_LINE(v_num);  
  8. DBMS_OUTPUT.PUT_LINE(v_num2_type);  
  9. END;  
Output:

Demonstrating
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,
  1. 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:
  1. DECLARE  
  2. v_nameTblEmployee.NAME%TYPE;  
  3. BEGIN  
  4. v_name:='SAILLESH PAWAR';  
  5. DBMS_OUTPUT.PUT_LINE('THE NAME OF THE EMPLOYEE IS '||v_name);  
  6. END;  
type table column
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.
  1. DECLARE  
  2. v_nameTblEmployee.NAME%TYPE;  
  3. BEGIN  
  4. DBMS_OUTPUT.PUT_LINE('THE NAME OF THE EMPLOYEE IS '||v_name);  
  5. END;  
Type Attribute of type table column
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:

  1. DECLARE  
  2. p1 NATURAL := -2147483647;--trying to hold negative value in NATURAL  
  3. BEGIN  
  4. SYS.DBMS_OUTPUT.PUT_LINE(p1);  
  5. END;  
  6. /  
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.

Demonstrating NATURAL
Figure 15: Demonstrating NATURAL assigning +ve value

Trying with NULL value

Demonstrating NATURAL data type can hold 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
  1. DECLARE  
  2. p1 POSITIVE;--trying to hold value in POSITIVE  
  3. BEGIN  
  4. p1:=10;  
  5. SYS.DBMS_OUTPUT.PUT_LINE('The value of p1 is '||p1);  
  6. END;  
  7. /  
Output:

Demonstrating POSITIVE type
Figure 18: Demonstrating POSITIVE type

If we try to store 0, -ve values in POSITIVE, then it will throw an EXCEPTION as shown below:

POSITIVE type cannot store 0
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:
  1. DECLARE  
  2. p1 POSITIVE;--trying to hold null value in POSTIVE  
  3. BEGIN  
  4. SYS.DBMS_OUTPUT.PUT_LINE('The value of p1 is '||p1);  
  5. END;  
POSITIVE can store NULL values
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.

POSITIVEN constraints NULL value
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:

Membership not found