Using the PL/SQL Block Structure

Previous

Next

As Example: Using a Simple PL/SQL Block shows, a PL/SQL block has three basic parts: a declarative part (DECLARE), an executable part (BEGIN ... END), and an exception-handling (EXCEPTION) part that handles error conditions. For a discussion about exception handling, see "Handling PL/SQL Errors".

Only the executable part is required. The optional declarative part is written first, where you define types, variables, and similar items. These items are manipulated in the executable part. Errors that occur during execution can be dealt with in the exception-handling part.

Note the comments that are added to the PL/SQL code. See "Using Comments". Also, note the use of DBMS_OUTPUT.PUT_LINE to display output. See "Inputting and Outputting Data with PL/SQL".

-- the following is an optional declarative part
DECLARE
  monthly_salary         NUMBER(6);
  number_of_days_worked  NUMBER(2);
  pay_per_day            NUMBER(6,2);

-- the following is the executable part, from BEGIN to END
BEGIN
  monthly_salary := 2290;
  number_of_days_worked := 21;
  pay_per_day := monthly_salary/number_of_days_worked;

-- the following displays output from the PL/SQL block
  DBMS_OUTPUT.PUT_LINE('The pay per day is ' || TO_CHAR(pay_per_day));

-- the following is an optional exception part that handles errors
EXCEPTION
  WHEN ZERO_DIVIDE THEN
      pay_per_day := 0;

END;
/

For another example of a PL/SQL block structure, see Example: Assigning Values to Variables Using PL/SQL SELECT INTO.