All the programmers must have the basics of SQL because SQL plays a vital role in IT projects. How the heart is the centre of the human body, the same way the database is the heart of the project and SQL is the only way of communicating to it. By using this, you can do all the tasks such as adding data, retrieving the stored data, modification of the existing data & removing the obsolete data, and so on. In this article, I will discuss, what is Oracle SQL & PL/SQL in more detail.
This Oracle tutorial is specially designed for beginners and professionals. I assure you will master Oracle SQL & PL/SQL programming language with this tutorial because we carefully designed the upcoming topics of the PL/SQL tutorial series are for you.
In this tutorial, you will learn completely about Oracle SQL & PL/SQL from a very basic concept to all the advanced topics of the oracle database in an effective and easiest way with more practice questions and real-life scenarios. In the end, you will get a better understanding of the Oracle database concepts and have complete knowledge of doing programming in oracle PL/SQL. Also, if you are looking for a company’s job role as Oracle SQL developer or Oracle PL/SQL Developer or Data Engineer or Database Administrator (DBA), then this oracle tutorial is mainly designed for you. It’s not only for those such roles, but you will also get the complete idea of the Oracle relational database management system.
What is Database?
A database management system (DBMS) is a collection of programs written to manage a database. That is, it acts as an interface between user and database.
A database is an organized collection of structured information or data stored electronically in a computer system.
Databases are often developed using formal design and modeling techniques.
In the beginning, the CSV file has been used to manage the information or store the data in columns which are known as fields, and rows which are known as records. When the number of rows in the file has been increased to many more (> millions) rows, obvious that it is so difficult to manage by humans. Hence the relational model for database management (RDBMS) was invented. In Database, the fields and the records are termed as columns and rows, respectively.
Oracle is a relational database management system (RDBMS). It is widely used in enterprise applications, and nearly half of the RDBMS worldwide market is owned by Oracle. It is the first database that was designed for enterprise grid computing. It manages the information and applications in the most flexible and cost-effective way.
Oracle Database contains two Structures.
- Logical Structure:
Logical Structures are tablespaces, Schema objects, extents, and segments.
- Physical Structure:
Physical Structures are Datafiles, Redo Log Files, Control File.
In Database, we have an object called a table which is the combination of columns and rows. This stores the data in the database. If the employee-related information is stored in columns and rows such as Employee Id, First name, Last name, City, country, then the Table is called an employee table. The table can be referred to as an entity as well.
| Employee Id | First name | Last name | City | Country |
| 102 | Joshua | Green | New York | USA |
The Vertical values (Employee Id, First Name, ...) are referred to as Columns/Fields, and Horizontal values (102, Joshua, …) are referred to as Rows/records.
With the comparison, the relational model is much better than the flat file model because it removes the duplicate data.
The below are the other well-known RDBMS database available in the market apart from Oracle Database.
| Database | Company |
| MySQL | Oracle Corporation |
| DB2 | IBM |
| SQL Server | Microsoft |
| PostgreSQL | PostgreSQL Global Development Group |
What is SQL?
SQL stands for Structured Query Language, and it is pronounced as S-Q-L or sometimes as See-Quell.
It is a standard language for accessing, manipulating, and maintaining databases. We can use SQL to perform all the below tasks in the database.
- Execute SQL queries such as DDL, DML, TCL
- Retrieve data using SELECT statement or command
- Adding entries using INSERT Statement
- Modify entries Using UPDATE Statement
- Remove entries using DELETE statement
- Trigger the SQL command to issue the privileges to user and Database objects
- Create New Databases
- Create New Database objects like tables, Procedures, Views
SQL is one of the effective languages to communicate between the User and database. SQL takes the user request and interacts with the database, and returns the output. It could be anything of retrieving or manipulating the data.
The below diagram is the architecture of SQL.
Here are a few SQL statements for a better understanding.
SELECT Statement Syntax:
SELECT * FROM <Employee_TableName>;
INSERT Statement Syntax:
INSERT INTO <Employee_TableName> values (103,’Michel’, ‘Jackson’, ‘London’, ‘United Kingdom’);
UPDATE Statement Syntax:
UPDATE <Employee_TableName> SET <Employee_CityColumn> = ‘New Jersey’ WHERE < Employee_CityColumn > = ‘New York’;
DELETE Statement Syntax:
DELETE FROM <Employee_TableName> WHERE < Employee_CityColumn > = ‘New York’;
What is PL/SQL?
PL/SQL stands for Procedural Language extensions to the Structured query language used in Oracle and is also called block-structured language, which enables the programmer to combine the power of SQL with procedural statements.
You may have a question that why there is a necessity to use Oracle PL/SQL when we have an Oracle SQL language already in place. The answer is, though we have SQL language to access and manipulate the data in the database, Oracle PL/SQL is constructed to overcome the limitations of SQL language.
PL/SQL is closely integrated with SQL language. It is a highly structured and readable language that covers the area where SQL is lacking in Oracle Database. All the SQL statements can be passed to the oracle engine all at once, which increases processing speed and decreases the traffic. Also, below is the list of limitations of SQL.
- SQL used to perform only DDL and DML operations and there is no room for programmers to use Loops, iteration, and conditional checking techniques
- SQL statements are passed to Oracle engine one at a time which increases traffic and decreases speed.
- SQL doesn’t have an error checking mechanism (Exceptional handling) during the manipulation of data.
- Low performance when compared to PL/SQL
The below diagram is the architecture of Oracle PL/SQL.
PL/SQL units are like procedures, functions, packages, types, and triggers, etc., which are stored in the database for reuse by applications. You can write PL/SQL codes without thinking of Case sensitivity because it is free to use both Lower and Upper case except within the string and the character literals.
Basic Syntax of Oracle PL/SQL Anonymous block
<Declaration section>
BEGIN
<Executable section>
END;
The above is the example of an anonymous block of Oracle PL/SQL because the block doesn’t have any name and it will not be saved in the database server for future use. If you ask me why such an anonymous block, I would say that the PL/SQL anonymous blocks are mainly used by programmers for only testing purposes.
We have a PL/SQL block that has its own name, such as Functions or Procedures. This PL/SQL block will be stored in the Oracle Database server and can be reused later.
SQL vs PLSQL - Difference between SQL and PLSQL
| SQL | PL/SQL |
| Structured Query Language | Procedural Language to the extension of SQL |
| Execute as a single SQL statement | Execute as a whole PLSQL block |
| Used to manipulate data | Used to create an application |
| Cannot contain PL/SQL code in the SQL statements | It is an extension of SQL, so it can contain SQL statements inside of the PLSQL block. |
| SQL is a single query that is used to perform DML and DDL operations | PL/SQL is a block of codes that are used to write the entire program blocks/ procedure/ function, etc. |
To conclude, SQL is a declarative language used to define what data is needed. But PL/SQL is a procedural language. It specifies both what data is needed and how it can be retrieved in the Database.
