SQL VS PLSQL

                                                                       What is SQL?

SQL is the Structured Query Language

SQL is composed of DML and DDL. DML are the keywords you use to access and manipulate data, hence the name Data Manipulation Language. DDL are the keywords you use to create objects such as views, tables and procedures, hence the name Data Definition Language. Examples of DML are SELECT, UPDATE, INSERT, MERGE, DELETE, etc. Examples of DDL are CREATE TABLE, ALTER VIEW, CREATE OR REPLACE PROCEDURE, etc.

Example DDL:

CREATE TABLE whatever ( column1 NUMBER NOT NULL PRIMARY KEY, column2 VARCHAR2(10), column3 DATE );

 

 

Example DML:

INSERT INTO whatever (column1, column2, column3) VALUES (1, 'Text Data', sysdate ); SELECT column1, column2, column3 FROM whatever; COMMIT;

 

 

What are SQL Commands?

SQL commands are commands that aren't actually a part of the SQL standard but are supported by the tools that support SQL. For example, SQL*Plus has been around a long time and many tools that allow scripting also allow SQL*Plus commands. When someone asks me about a SQL command, I first clarify if the mean a SQL keyword or a SQL*Plus (or some other tool) command.

SQL commands are usually meant to help format output: BREAK, BTITLE, COLUMN, PRINT or they are meant to create or store data or scripts: COMPUTE, DEFINE, STORE, SAVE. There are also commands that interact with the database: SHUTDOWN, CONNECT, COPY. And there is at least one that interacts with data: XQUERY.

What is PL/SQL?

PL/SQL, Oracle's procedural extension of SQL, is an advanced fourth-generation programming language (4GL). It offers software-engineering features such as data encapsulation, overloading, collection types, exceptions, and information hiding. PL/SQL also supports rapid prototyping and development through tight integration with SQL and the Oracle database.

But what does that mean? The key here are the words: procedural extension of SQL. PL/SQL is a procedural language like C++, Java, ADA, etc. If has variables and variable declarations, conditional controls like IF and CASE. It has looping structures such as LOOP, FOR LOOP and the WHILE LOOP. PL/SQL uses SQL to use, manipulate and save data to the database.

PL/SQL is the Oracle native programming language that provides database-centric application development. It can natively call static SQL and provides multiple methods of calling dynamic SQL.

 

Example PL/SQL:

BEGIN -- A PL/SQL cursor FOR c1 IN ( SELECT * FROM whatever ) -- This is SQL called by PL/SQL LOOP DBMS_OUTPUT.PUT_LINE( 'Column1 is: ' || TO_CHAR(c1.column1) || ', Column2 is: ' || c1.column2 || ', Column3 is: ' || TO_CHAR(c1.column3 ) ); END LOOP; END;

 

 

 

What is the difference between SQL and PL/SQL?

 

SQL is a data oriented language for selecting and manipulating sets of data. PL/SQL is a procedural language to create applications. You don't normally have a "SQL application". You normally have an application that uses SQL and a relational database on the back-end. PL/SQL can be the application language just like Java or PHP can. SQL may be the source of data for your screens, web pages and reports. PL/SQL might be the language you use to build, format and display those screens, web pages and reports.

The code that makes your program function is PL/SQL. The code that manipulates the data is SQL DML. The code that creates stored database objects is SQL DDL. DDL compiles the code that is written in PL/SQL. PL/SQL may call SQL to perform data manipulation. The commands that format the output of a tool are not related to the SQL standard or to PL/SQL.