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

CAP570 · Unit 5: PL/SQL

Basic structure of a PL/SQL block

12 practice questions · in the ETE syllabus

Practise this topicPractise this topic

Key points

  • DECLARE, BEGIN, EXCEPTION, END in that order
  • Only BEGIN ... END is mandatory
  • Statements end with ; and := assigns
  • An exception skips the rest of the block

A PL/SQL block has up to four sections, always in this order:

DECLARE      -- optional: variables, constants, cursors
BEGIN        -- mandatory: executable statements
EXCEPTION    -- optional: error handlers
END;

Only BEGIN ... END; is mandatory. Every statement ends with a semicolon, assignment uses := (total := total + i;), and in SQLPlus a slash* / on its own line executes the block. DBMS_OUTPUT.PUT_LINE(value) prints output when server output is on. Control structures include IF ... THEN ... ELSE ... END IF; and FOR i IN 1..4 LOOP ... END LOOP;, where the range includes both ends, so the loop above adds 1 + 2 + 3 + 4 = 10.

When a statement raises an error such as 10 / 0 (ZERO_DIVIDE), the rest of the executable section is skipped and control moves to the matching WHEN ... THEN handler in the EXCEPTION section. Blocks can be nested, and an inner variable with the same name hides the outer one inside the inner block. column%TYPE declares a variable with the same data type as a table column.

Exam trap: after an exception the statements following the failing line never run, even if they would have succeeded.

Try two questions

Question 1 · Tier 4, Analyse

How are PL/SQL statements and blocks terminated and run in SQL*Plus?

  1. Each statement ends with a period, and the word RUN starts the block
  2. No terminator is needed, and the block starts when Enter is pressed twice
  3. Each statement ends with a colon, and the word GO starts the block
  4. Each statement ends with a semicolon, and a slash on its own line runs the block
Show the answer

D. Use ; after each statement and END;, then / on a line by itself to execute the block.

Why not A: PL/SQL statements end with a semicolon.

Why not B: A semicolon is required, and the slash executes the block.

Why not C: The terminator is a semicolon, not a colon.

Question 2 · Tier 2, Understand

What is the correct order of the sections in a PL/SQL block?

  1. BEGIN, DECLARE, EXCEPTION, END
  2. DECLARE, EXCEPTION, BEGIN, END
  3. BEGIN, EXCEPTION, DECLARE, END
  4. DECLARE, BEGIN, EXCEPTION, END
Show the answer

D. A block is DECLARE (optional), BEGIN, EXCEPTION (optional) and END;.

Why not A: Declarations come before BEGIN.

Why not B: The exception section comes after the executable section.

Why not C: Declarations come first, then the executable section.