How Is Structured A PL/SQL Block

How Is Structured A PL/SQL Block

October 19, 2020
Oracle DBA
oracle dba oracle database management database management system database oracle performance tuning oracle sql learn sql learn personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

This online PL/SQL programming language tutorial page is about how is structured a PL/SQL block and what is the best practice to write it.

A basic PL/SQL block is has four keywords DECLARE, BEGIN, EXCEPTION, and END. These keywords divide the block into three parts:

  • a declarative part and this is optional – DECLARE
  • an executable part is required – BEGIN and END
  • an exception-handling part is optional – EXCEPTION

It is important to know that only the executable part is required and a small additional feature for a block that it can have a label too. The block syntax is following:

<<a_label_as_your_block_name>>
DECLARE
  <variables and/or sub-programs>
BEGIN
  <executable code>
EXCEPTION
  <what do to when you got an error>
END;

The following PL/SQL tutorial sample block is labelled as “DOES CUSTOMER 13 EXIST“. We do recommend to use only letters to avoid a possible special character error. The PL/SQL block will look for a customer with Id 13 and if it does exist then the code will send a message to output. The text is “Customer 13 does exist“. When the customer is missing from the customers table the select statement raises error “NO_DATA_FOUND” and in the exception-handling part (EXCEPTION) the PL/SQL programming language code will send to output text “Customer 13 does NOT exist“.

<<DOES_CUSTOMER_13_EXIST>>
DECLARE
   -- here we are declaring a variable 
   v_exists   PLS_INTEGER;
BEGIN
   -- here we got a SQL select 
   SELECT id
   INTO   v_exists
   FROM   customers
   WHERE id = 13;

   -- The customer does exist and show the message 
   DBMS_OUTPUT.put_line ('Customer 13 does exist');
EXCEPTION
  WHEN no_data_found THEN
      -- The customer does not exist and show the message 
      DBMS_OUTPUT.put_line ('Customer 13 does NOT exist');
END;

tutorial pl sql tutorial database programming database developer database administrator sql database plsql tutorial plsql sql programming language ORA-00942: table or view does not exist personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

The sample code has quite many keywords that this PL/SQL tutorial has not covered jet. Don’t worry if you didn’t understand all of the PL/SQL code – we will go deeper and will analyze them all plus more in out next chapters.

Important to Know:

When you did execute the example above and it returned error ORA-00942: table or view does not exist instead of the result above then that means the table we are using in the example does not exist and your are not able the see the result as on the following picture.

tutorial pl sql tutorial database programming database developer database administrator sql database plsql tutorial plsql sql programming language ORA-00942: table or view does not exist personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

If you are still keen to try out the basic PL/SQL tutorial example then try the following code. The example is using table Oracle Dual that does exists in all Oracle database versions and you should not face any problems executing it and seeing results.

<<DOES_DUAL_ORACLE_EXIST>>
DECLARE
   -- here we are declaring a variable 
   v_exists   PLS_INTEGER;
BEGIN
   -- here we got a SQL select 
   SELECT 1
   INTO   v_exists
   FROM   dual;

   -- The customer does exist and show the message 
   DBMS_OUTPUT.put_line ('The Dual Oracle does exist');
EXCEPTION
  WHEN no_data_found THEN
      -- The customer does not exist and show the message 
      DBMS_OUTPUT.put_line ('The Dual Oracle does NOT exist');
END;

Your PL/SQL database developer tool should return the same output as on the next picture:

tutorial pl sql tutorial database programming database developer database administrator sql database plsql tutorial plsql sql programming language ORA-00942: table or view does not exist personal injury attorney new york mesothelioma lawyers mesothelioma lawyers new york mesothelioma lawyer the stock market plumbing retirement planning barclays stock broker financial advisor personal retirement financial advisor

In the next chapter we are going to find out how declare a variable in PL/SQL and what type of declarations are available.

Next: What is a PL/SQL variable?

Leave a Reply

Your email address will not be published. Required fields are marked *

*