An Example from PL/SQL - Oracle

SQL is just a query language. However, you need more than SQL to take care of  business. Every database vendor has an alternative language set that works well with SQL, but can handle data more flexibly by introducing logic and transactional capabilities to the handled data. PL/SQL for Oracle provides the sophisticated data processing capability. PL/SQL stored procedures can store your business logic, PL/SQL packages can do summarizing data and other chores on a schedule, and PL/SQL can set up triggers to work with the business logic and much more. The object of this example is to show that PL/SQL can be run from SQL commands in the OracleXE interface.

PL/SQL is a block-based programming interface. The PL/SQL block is built from three sections:

declare

execution{between BEGIN and END}

optional exception.

The example shown calculates the man-hours utilized by a job which takes the number of men working multiplied by the number of hours worked. The query is shown in the next picture with the results. The query also shows the PL/SQL's way of printing results using the DBMS_output_PUT_LINE() method. In the code, the number of men is an integer, but the hours_worked is a decimal. The number of man hours is again in integers. If the number of hours is changed to, say, 8.3, the total will come out to 50 instead of 51 man-hours because man_hours is declared to be integer.

Exception Messages

Exceptions are handled in the Exception block. Let's look at how exception messages are displayed. The next picture shows the display when an exception is thrown. The man_hours variable is declared without the data type. Discussion of trouble shooting is outside the scope of this tutorial.