It’s an interesting question, to be sure. Most people who are familiar
with Oracle development wouldn't give it a thought but when you come down to
it, it’s sometimes confusing to define the demarcation between SQL and PL/SQL.
Through this note let us try to understand the differences between SQL and
PL/SQL. You never know, this might just be the starting point of your next oracle hrms interview questions or oracle payroll interview questions :)
SQL is a non-procedural language;
users describe in SQL what they want, and the SQL language compiler
automatically generates a procedure to navigate the database and perform the
desired task. All operations performed on the information in an Oracle
database are executed using SQL statements.
PL/SQL is Oracle's procedural language
extension to SQL. PL/SQL allows you to mix SQL statements with
procedural constructs. PL/SQL provides the capability to define and execute
PL/SQL program units such as procedures, functions, and packages. PL/SQL
program units generally are categorized as anonymous blocks and stored
procedures.
In a nutshell SQL allows you to issue a single query or execute a single
insert/update/delete whereas PL/SQL allows you to write a full program (loops,
variables, etc.) to accomplish multiple selects/inserts/updates/deletes
Some other differences between the two are –
- SQL is a limited language that allows you to directly interact with the database. You can manipulate objects (DDL) and data (DML) with SQL, but SQL doesn't include all the things that normal programming languages have such as loops and IF...THEN statements. That is what PL/SQL is for. PL/SQL is a normal programming language that includes all the features of most other programming languages. But it has one thing that other programming languages don't have, namely the easy ability to integrate with SQL
- PL/SQL can be the application language just like Java or PHP can. PL/SQL might be the language we use to build, format and display those screens, web pages and reports. Whereas SQL may be the source of data for our screens, web pages and reports
- SQL is executed one statement at a time. PL/SQL is executed as a block of code
- SQL tells the database what to do (declarative), not how to do it. In contrast, PL/SQL tells the database how to do things (procedural)
- SQL is used to code queries, DML and DDL statements. PL/SQL is used to code program blocks, triggers, functions, procedures and packages
- We can embed SQL in a PL/SQL program, but we cannot embed PL/SQL within a SQL statement
More about pl/sql and sql will be discussed during my future posts on oracle hrms tables and oracle payroll tables
No comments:
Post a Comment