SQL AND PL-SQL MATERIALS
Relational database management is done using the programming language SQL (Structured Query Language). By utilising different forms of queries, it enables users to retrieve and alter data that is stored in a database.
A procedural addition to SQL is PL/SQL (Procedural Language/Structured Query Language). It integrates procedural building blocks like loops, conditionals, and exception handling with SQL commands to produce strong and adaptable database applications.
While PL/SQL is used to create stored procedures, functions, triggers, and packages within a database, SQL is mostly used to query and manipulate data. Here are some essential characteristics of SQL and PL/SQL:
SQL:
Using SELECT commands, you can use SQL to obtain data from a database. Conditions, sorting, and grouping can all be specified to filter and arrange the data.
Data Manipulation: To change the data in database tables, SQL offers commands like INSERT, UPDATE, and DELETE.
Data Definition: To define and modify the structure of database objects, SQL supports statements like CREATE TABLE, ALTER TABLE, and DROP TABLE.
Data Control: To enable security and access control, SQL includes procedures for giving and revoking rights on database objects.
SQL stands for “Structured Query Language.” The Structured Query Language is a relational database language. By itself, SQL does not make a DBMS. SQL is a medium which is used to communicate to the DBMS. SQL commands consist of English-like statements which are used to query, insert, update,
and delete data. English-like statements mean that SQL commands resemble English language sentences in their construction and use and therefore are easy to learn and understand. SQL is referred to as nonprocedural database language. Here nonprocedural means that, when we want to retrieve data from the database it is enough to tell SQL what data to be retrieved, rather than how to retrieve it.
The DBMS will take care of locating the information in the database. Commercial database management systems allow SQL to be used in two distinct ways. First, SQL commands can be typed at the command line directly. The DBMS interprets and processes the SQL commands immediately,
and the results are displayed. This method of SQL processing is called interactive SQL. The second method is called programmatic SQL. Here, SQL statements are embedded in a host language such as COBOL, FORTRAN, C, etc. SQL needs a host language because SQL is not a really complete computer programming language as such because it has no statements or constructs that allow branch or loop. The host language provides the necessary looping and branching structures and the interface with the user, while SQL provides the statements to communicate with the DBMS.
Some of the features of SQL are:
– SQL is a language used to interact with the database.
– SQL is a data access language.
– SQL is based on relational tuple calculus.
– SQL is a standard relational database management language.
– The first commercial DBMS that supported SQL was Oracle in 1979.
– SQL is a “nonprocedural” or “declarative” language
PL/SQL:
Procedural Constructs: To build more complicated programmes, PL/SQL extends SQL with procedural constructs such loops, conditionals (IF-THEN-ELSE), and blocks (BEGIN-END).
Exception Handling: To detect and address issues that arise during programme execution, PL/SQL offers exception handling techniques.
Stored Procedures and Functions: Using PL/SQL, you may create reusable procedures and functions that can be invoked from SQL statements or other PL/SQL blocks and stored in the database.
Triggers: PL/SQL triggers are event-driven programmes that run on demand if specific conditions are met, such as the insertion, update, or deletion of data in a table.
Packages are modular structures in PL/SQL that contain associated cursors, functions, variables, and procedures. They offer a method to group and encapsulate code for simpler upkeep and reuse.
In popular database systems like Oracle Database, MySQL, Microsoft SQL Server, and PostgreSQL, both SQL and PL/SQL are utilised extensively. For maintaining and modifying data within these systems, they are crucial tools.
PL/SQL stands for Procedural Language/Structured Query Language, which is provided by Oracle as a procedural extension to SQL. SQL is a declarative language. In SQL, the statements have no control to the program and can be executed in any order. PL/SQL, on the other hand, is a procedural language that makes up for all the missing elements in SQL. PL/SQL arose from the desire of programmers to have a language structure that was more familiar than SQL’s purely declarative nature.
Structure of PL/SQL
PL/SQL is a 4GL (fourth generation) programming language. It offers all features of advanced programming language such as portability, security, data encapsulation, information hiding, etc. A PL/SQL program may consist of more than one SQL statements, while execution of a PL/SQL program makes only one call to Oracle engine, thus it helps in reducing the database overheads. With PL/SQL, one can use the SQL statements together with the control structures (like if . . . then) for data manipulation. Besides this, user can define his/her own error messages to display. Thus we can say that PL/SQL combines the data manipulation power of SQL with data processing power of
procedural language.
PL/SQL is a block structured language. This means a PL/SQL program is made up of blocks, where block is a smallest piece of PL/SQL code having logically related statements and declarations. A block consists of three sections
namely:
Declare, Begin, and Exception followed by an End statement. We will see the different sections of PL/SQL block.
Declare Section
Declare section declares the variables, constants, processes, functions, etc., to be used in the other parts of program. It is an optional section.
Begin Section
It is the executable section. It consists of a set of SQL and PL/SQL statements, which is executed when PL/SQL block runs. It is a compulsory section.
Exception Section
This section handles the errors, which occurs during execution of the PL/SQL block. This section allows the user to define his/her own error messages. This section executes only when an error occurs. It is an optional section.
End Section
This section indicates the end of PL/SQL block. Every PL/SQL program must consist of at least one block, which may consist of any number of nested sub-blocks.
PL/SQL Language Elements
Let us start from the basic elements of PL/SQL language. Like other programming languages PL/SQL also have specific character sets, operators, indicators, punctuations, identifiers, comments, etc. In the following sections we will discuss about various language elements of PL/SQL.
Character Set
A PL/SQL program consists of text having specific set of characters. Character set may include the following characters:
– Alphabets, both in upper case [A–Z] and lower case [a–z]
– Numeric digits [0–9]
– Special characters ( ) + − * /< >= ! ∼ ˆ ; : . @ % , # $ & | { } ? [ ]
– Blank spaces, tabs, and carriage returns.
PL/SQL is not case sensitive, so lowercase letters are equivalent to corresponding uppercase letters except within string and character literals.
Comments