- LPU MCA Semester 1
- CAP570
- Unit 5
CAP570 · Unit 5: PL/SQL
PL/SQL overview and differences between SQL and PL/SQL
9 practice questions · in the ETE syllabus
Key points
- SQL is declarative; PL/SQL is procedural
- PL/SQL adds variables, IF, loops and exceptions
- A block is sent to the server in one call
- SELECT ... INTO stores a result in a variable
SQL is a declarative language: each statement says what data is wanted and the database decides how to get it, and statements run one at a time. PL/SQL (Procedural Language extension to SQL) is Oracle's extension that adds variables, conditions (IF), loops, error handling and subprograms around SQL statements. PL/SQL code is organised in blocks.
| Feature | SQL | PL/SQL |
|---|---|---|
| Style | Declarative | Procedural |
| Unit | One statement | A block of many statements |
| Variables and loops | No | Yes |
| Error handling | No | EXCEPTION section |
| Network use | One call per statement | A whole block in one call |
Inside a block, SQL statements run as usual, and SELECT ... INTO variable stores a query result in a variable. DBMS_OUTPUT.PUT_LINE prints text (enable it with SET SERVEROUTPUT ON in SQLPlus). Because a block is sent to the server as one unit, PL/SQL reduces network round trips*. Other vendors have similar languages, such as Transact-SQL for Microsoft SQL Server.
Exam trap: PL/SQL does not replace SQL. It wraps SQL statements in procedural logic.
Try two questions
In a PL/SQL block, what is the purpose of SELECT ... INTO?
- To create a new table from the query result
- To insert the result into another database
- To store the query result in a variable
- To export the result to a text file
Show the answer
C. SELECT column INTO variable FROM table ... fetches a value into a PL/SQL variable.
Why not A: Creating a table from a query uses CREATE TABLE ... AS SELECT.
Why not B: SELECT INTO does not copy data to another database.
Why not D: SELECT INTO does not write files.
Which task can PL/SQL do but a single plain SQL statement cannot?
- Loop through rows and apply an IF test to each one
- Retrieve rows from a table with a SELECT
- Insert a new row with an INSERT statement
- Delete rows that satisfy a condition
Show the answer
A. Control structures such as IF and loops are procedural features added by PL/SQL.
Why not B: A plain SELECT already retrieves rows.
Why not C: A plain INSERT already adds a row.
Why not D: A plain DELETE already removes rows.