Gyanrush
Start freeOpen Gyanrush
Start freeOpen Gyanrush
  1. LPU MCA Semester 1
  2. CAP570
  3. Unit 5

CAP570 · Unit 5: PL/SQL

Benefits and features of PL/SQL

10 practice questions · in the ETE syllabus

Practise this topicPractise this topic

Key points

  • Blocks, exceptions and tight SQL integration
  • Fewer network round trips improve performance
  • Procedure acts, function returns a value, trigger fires on change
  • Packages group related subprograms

PL/SQL combines SQL with procedural programming, which gives several benefits.

Benefit or featureWhat it means
Block structureCode is organised in blocks that can be nested
PerformanceA block is sent in one call, reducing network round trips
Exception handlingRuntime errors are caught in the EXCEPTION section
Tight SQL integrationSQL statements and data types are used directly
ReusabilityStored procedures, functions and packages are written once
PortabilityRuns on any platform that runs Oracle
SecurityUsers get EXECUTE rights on a procedure without table access

Main PL/SQL objects: a procedure performs an action, a function returns a value and can be used in an expression, a trigger fires automatically on INSERT, UPDATE or DELETE, a cursor processes the rows of a query one at a time, and a package groups related procedures, functions and variables.

Exam trap: portability means across operating systems running Oracle, not across database vendors.

Try two questions

Question 1 · Tier 2, Understand

Which PL/SQL object runs automatically when a table is changed by an INSERT, UPDATE or DELETE?

  1. Cursor
  2. Package
  3. Trigger
  4. Function
Show the answer

C. A trigger is a stored block that fires on a table event.

Why not A: A cursor processes query rows one at a time.

Why not B: A package groups related subprograms.

Why not D: A function runs only when it is called.

Question 2 · Tier 4, Analyse

How can PL/SQL improve security?

  1. Every PL/SQL block automatically encrypts all columns it reads
  2. Users can be allowed to run a procedure without having direct access to the underlying tables
  3. PL/SQL blocks can run only when no other user is connected
  4. PL/SQL removes the need for user passwords entirely
Show the answer

B. Granting EXECUTE on a procedure exposes only what the procedure does.

Why not A: Blocks do not encrypt data by themselves.

Why not C: Blocks run alongside other users.

Why not D: Passwords remain necessary.