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;
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.
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.
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:
In the next chapter we are going to find out how declare a variable in PL/SQL and what type of declarations are available.