Mastering PL/SQL Through Illustrations: From Learning Fundamentals to Developing Efficient PL/SQL Blocks (English Edition)

By B Chandra

This book helps you to improve your employability and job market so that you can sprint towards a rewarding career in the area of databases. . You will learn PL/SQL starting from fundamentals to advanced level. The detailed description of Cursors will enable you to write professional codes using Cursors in PL/SQL. You will learn how to raise exceptions when errors are encountered, create Triggers before/after a DML operation, create Functions /Procedures and packages.. By the end of the book, you will be able to use collections and also write advanced PL/SQL blocks using database tables with ease.

• Language: English
• Publisher: BPB Online LLP
• Release date: Sep 15, 2020
• ISBN: 9789389898491

Book preview

CHAPTER 1

Introduction to PL/SQL

PL/SQL stands for Procedural Language extensions to the Structured Query Language. PL/SQL can be easily integrated with SQL which is the most popular database language. PL/SQL adds procedural constructs to SQL for overcoming the limitations of SQL. It is a highly structured language and the PL/SQL blocks can be executed on systems that have an Oracle database. A block of statements can be directly stored in the database, which reduces traffic between the application and the database. PL/SQL provides an operating system independent programming environment.

List of topics

PL/SQL blocks

Variable declarations

Data types in PL/SQL

Online display statement

Constants in PL/SQL

Records in PL/SQL

Retrieving data from database table and storing in a PL/SQL record

% TYPE, % ROWTYPE and SELECT INTO statement

Illustrative PL/SQL blocks

Objective

This chapter gives the description of data types in PL/SQL, variable declarations, and different forms of online display statement. All these have been illustrated through codes or blocks in PL/SQL. Record declaration has been illustrated through a PL/SQL block. Retrieval of data from a database table and storing in a record with the help of SELECT INTO statement is also given. %TYPE and %ROWTYPE have been vividly described and illustrated through PL/SQL blocks.

1.1 PL/SQL block

PL/SQL is extremely useful since it is developed by adding constructs of procedural languages in SQL. It is a structured procedural language. The basic unit in PL/SQL is a block. All PL/SQL programs are made up of blocks; the block-like structure is depicted below:

Figure 1.1

Variables are declared in the DECLARATION section. EXECUTION section has one or more executable statements and is enclosed between the keywords BEGIN and END. It can also have a section for handling errors called EXCEPTION SECTION which will be discussed in Chapter 5: Exceptions in PL/SQL. A typical PL/SQL block is given below:

DECLARATION SECTION

BEGIN

EXECUTION SECTION

EXCEPTION

EXCEPTION SECTION

END

Code 1.1 illustration of a simple PL/SQL BLOCK:

/*1.1*/

BEGIN

NULL;

END;

There are no declarations in this block. Declaration section is not mandatory in a PL/SQL block. There is only one executable statement NULL in this block. A NULL statement does nothing except that it passes control to the next statement. The output is as follows:

Output 1.1

1.2 Comments in PL/SQL

A single-line comment starts with a double hyphen (--) that can appear anywhere on a line and extends to the end of the line in a PL/SQL block and multiline comments in PL/SQL start with /* and end with */.

1.3 DBMS_OUTPUT statement for online display

DBMS_OUTPUT is a built-in package that provides a way for displaying information from a PL/SQL block to the screen. A package in PL/SQL consists of procedures and functions bundled together. User-defined packages have been explained in detail in Chapter 9: Packages. The most commonly used procedure of the built-in package DBMS_OUTPUT for display is PUT_LINE(). However, PUT() and NEW_LINE is also used.

The entire line of information is placed into the buffer by calling the PUT_LINE() procedure. One can build a line of information part by part by making multiple calls to PUT(). If the procedure PUT() is called, the end of line marker is added by calling NEW_LINE. Prior to all these procedures, it is necessary to mention the name of the package DBMS_OUTPUT.

A limit on the number of characters in the output can be set using the statement:

SET SERVEROUTPUT ON SIZE 10000;

In the above statement, a limit of 10000 bytes is set and if the output size is more than 10000 characters, an exception is raised. This may be given at the beginning of a PL/SQL block.

Code 1.3.1 PL/SQL block to illustrate the use of DBMS_OUTPUT.PUT_LINE()

/*1.3.1*/

DECLARE

BEGIN

--online display

Dbms_output.put_line(‘PL SQL Through Examples’);

END;

In the above block, there are no declarations but there is only one executable statement. The output is shown below:

Output 1.3.1

Code 1.3.2 PL/SQL block to illustrate the usage of DBMS_OUTPUT.PUT(), DBMS_OUTPUT.PUT_LINE() and DBMS_OUTPUT.NEW_LINE.

/*1.3.2*/

BEGIN

DBMS_OUTPUT.PUT(‘PL/’);

DBMS_OUTPUT.PUT(‘SQL’);

DBMS_OUTPUT.NEW_LINE;

DBMS_OUTPUT.PUT_LINE(‘Through’);

DBMS_OUTPUT.PUT_LINE(‘Examples’);

END;

PL/ and SQL are displayed on the same line since DBMS_OUTPUT.PUT()is used for online display of these. DBMS_OUTPUT.NEWLINE enables to pass control to a new line. On the next line, Through and Examples are displayed on separate lines since DBMS_OUTPUT.PUT_LINE() is used for displaying these. The output is displayed below:

Output 1.3.2

1.4 Variable declarations in PL/SQL

Each variable in PL/SQL has a specific data type that defines the size. A variable in PL/SQL must start with a letter and followed by one or more letters, dollar sign, numerals, underscore, etc. Maximum length of a variable name is 30 characters. A variable is declared in the declaration section before its first use in the PL/SQL block. Variables are not case sensitive in PL/SQL. After a PL/SQL variable is declared, memory is allocated to the variable based on the value it is assigned.

1.5 Data types in PL/SQL

PL/SQL data types fall into the following categories:

Numeric

Boolean

Character

Date/time

1.5.1 Numeric data type

1.5.1.1 Number

The number data type is used for storing numeric data. It is used for storing integers, real numbers, or floating-point numbers up to 38 digits of precision. The variable can be declared either with precision and decimal digit details or without these details:

DECLARE

A NUMBER(8);

B NUMBER(8,2);

In the above variable A has been declared as NUMBER type having precision 8 and variable B has also been declared as NUMBER type with a total precision of 8 having 2 places after the decimal.

1.5.1.2 Number subtypes

Number subtypes are given below:

DEC

DECIMAL

NUMERIC

INTEGER

INT

SMALLINT

PLS_INTEGER

REAL

BINARY_FLOAT

BINARY_DOUBLE

DEC, DECIMAL, and NUMERIC are used to declare fixed-point numbers with a precision of a maximum of 38 decimal digits. INTEGER, INT, and SMALLINT declare integers with a maximum precision of 38 digits. PLS_INTEGER subtype of PLS_INTEGER was introduced in Oracle 11g. PLS_INTEGER is specific to PL/SQL data type. It represents signed 32 bits integers that range from -2,147,483,648 to 2,147,483,647. This uses machine arithmetic, making it much faster than the internal datatypes. In addition, PLS_INTEGER values require less storage than NUMBER. From Oracle 10g upward BINARY_INTEGER is identical to PLS_INTEGER.

REAL: Oracle 10g introduced the BINARY_FLOAT and BINARY_DOUBLE data types to handle real numbers. Both new types use machine arithmetic, making them faster than the NUMBER data type.

1.5.2 Character datatypes

1.5.2.1 CHAR

This is used for storing character strings of fixed length. The value is given in single quotes. This utilizes the entire declared size of memory even if space is not utilized by the value. The range is from 1 to 2000 bytes. For example:

DECLARE

A CHAR(10);

Declares variable A of CHAR data type with maximum size of 10. ORACLE will allocate memory of 10 bytes whatever may be the length of the string. If the string length is <10, it will be blank padded to ensure a length of 10.

1.5.2.2 VARCHAR

This datatype is used to store alphanumeric strings of variable length. Its value is quoted in single quotes. In this case, also the range is from 1 to 2000 bytes. For example:

DECLARE

B VARCHAR(10);

Declares a variable B of VARCHAR type with maximum size of 10. If the value assigned is of 5 bytes, ORACLE will allocate only memory of 5 bytes in this case.

1.5.2.3 VARCHAR2

This datatype is used to store alphanumeric strings of variable length. Its value is quoted in single quotes. The range is from 1 to 4000 bytes. It releases the unused space in memory, hence saving the unused space. For example:

DECLARE

C VARCHAR2 (10);

Declares variable C of VARCHAR2 type with maximum size of 10. If the value assigned is of 5 bytes, ORACLE will allocate only memory of 5 bytes. It is always better to use VARCHAR2 instead of CHAR data type to save memory space.

1.5.3 DATE data type

The range for the Date is from 01-Jan-4712 BC to 31-DEC-9999. It stores the data in date format DD-MON-YYYY. The value is written in single quotes. For example:

DECLARE

D1 DATE;

Declares variable D1 of DATE data type.

1.5.4 BOOLEAN data type

BOOLEAN datatype stores logical values and can be either TRUE or FALSE. For example:

DECLARE

A1 BOOLEAN;

Declares variable A1 of Boolean type.

Code 1.5.1 illustrates declaration and initialization of variables in PL/SQL and the use of concatenation symbol in online display DBMS_OUTPUT statement:

/*1.5.1*/

DECLARE

A NUMBER(4,1) := 11.2;

B PLS_INTEGER:=78;

C NUMBER(2) :=11;

D CHAR(1) :=’P’;

E varchar (4):=’GOOD’;

V1 CHAR (1):=’T’;

D1 DATE:=’01-01-2020’;

-- Displays current date

D2 DATE:=SYSDATE;

BEGIN

Dbms_output.put_line(‘A:’||’ ‘|| A );

Dbms_output.put_line(‘B:’||’ ‘|| B);

Dbms_output.NEW_LINE;

Dbms_output.put_line(‘C:’||’ ‘|| C);

Dbms_output.put_line (‘D:’||’ ‘|| D);

Dbms_output.NEW_LINE;

Dbms_output.put_line(‘D1’ ||CHR(9) ||’Today’s DATE ‘);

Dbms_output.put_line(D1|| CHR(9) || D2);

Dbms_output.put_line (‘V1:’||’ ‘|| V1);

END;

In the above block, variable A is declared to be type number with precision 4 and one digit after the decimal, variable C to be of type number with precision 2. Variable B is declared to be of PLS_integer type, variable D declared to be of character type of size 1, variable E declared to be of varchar of size 4. V1 declared to be of character type of size 1. D1 and D2 are declared to be of type DATE. SYSDATE gives the current date.

Concatenation symbol in DBMS_output.put_line() enables you to concatenate the items given inside the parenthesis. For example, the statement:

Dbms_output.put_line (‘V1:’||’ ‘|| V1);

helps in displaying the message V1: followed by two spaces further followed by the value of the variable V1. CHR(9) in the statement:

Dbms_output.put_line(D1|| CHR(9) || D2);

provides a tab space between the values for the variables D1 and D2 during the online display. The output is displayed as follows:

Output 1.5.1

If CHR (10) is used instead of CHR(9) in the above DBMS_OUTPUT statement, values of D1 and D2 will be displayed on separate lines.

1.6 RAW, LONG RAW and LOB datatypes

1.6.1 RAW and LONG RAW data type

RAW is a variable-length data type for storing binary data or byte strings. RAW data cannot be manipulated. RAW data is returned as a hexadecimal character value. In PL/SQL, the size is 32767 bytes. LONG RAW is also used for storing binary data of variable length up to 2 Gigabytes in length. Since it is too long, a table can consist of only one LONGRAW column.

Code 1.6.1 illustrates RAW and LONGRAW data types:

/*1.6.1*/

DECLARE

data1 RAW(1000);

data2 LONG RAW(1000);

BEGIN

/*

Converting string variable to raw data type (binary)

using cast_to_raw utility

*/

data1 := utl_raw.cast_to_raw(‘PL SQL is a programming language’);

data2 := utl_raw.cast_to_raw(‘PL SQL can be easily used with SQL’);

Dbms_output.put_line(‘Data in binary format’);

-- Binary data is printed in hexadecimal format

Dbms_output.put_line(data1);

Dbms_output.put_line(data2);

/*

Converting binary variable to varchar data type (binary)

using cast_to_varchar2 utility

*/

Dbms_output.put_line (‘Binary data converted to VARCHAR’);

Dbms_output.put_line(utl_raw.CAST_TO_VARCHAR2(data1));

Dbms_output.put_line(utl_raw.CAST_TO_VARCHAR2(data2));

END;

In the above block, variable data1 is declared to be of the RAW type with a size of 1000 bytes, and variable data2 is declared to be of type LONG RAW also of 1000 bytes. Utl_raw is an inbuilt package in PL/SQL and utl_raw.cast_to_raw() converts a string variable to binary but the output is displayed in hexadecimal format. utl_raw.CAST_TO_VARCHAR2( ) converts a RAW value to VARCHAR type. Following is the output:

Output 1.6.1

1.6.2 LOB data type

This is yet another data type and it stands for Large Object. It is not a commonly used data type. In this case, a stream of data is stored in a database. ORACLE provides three kinds of LOB data viz. BLOB, CLOB, and NCLOB:

BLOB: Stores binary large objects and can store a maximum of 4 GB of data

CLOB: Stores character large objects. It can store a maximum of 4 GB of character data.

NCLOB: Stores character large objects in multibyte national character set. Same as CLOB, it can also store a maximum of 4 GB of character data.

1.7 Execution of PL/SQL blocks on Oracle APEX

In order to execute a PL/SQL block, Oracle provides a freely available Oracle APEX (Oracle Application Express) platform on Oracle cloud. Brief outline of steps for requesting workspace on Oracle APEX and execution of a PL/SQL block is given below. Detailed steps are given in in Appendix I. In this manuscript, though the codes are executed on Oracle APEX, they can be executed using Oracle SQL*PLUS, Oracle SQL developer or any software that supports execution of PL/SQL blocks.

Go to URL: https://apex.oracle.com/en/

Select Get Started for free

Select Request a free workspace

Enter your details and provide a workspace name

When the workspace is successfully created, login screen appears

Figure 1.2

Username is the mail id of the user. Login with the workspace name already provided, username and password. The following screen appears and in the dropdown menu in SQL workshop, select SQL commands.

Figure 1.3

Type the commands given in code 1.3.1 as shown in Figure 1.4, save and press the run button for executing the PL/SQL block.