Oracle PL/SQL Tutorial Part  4 - PL/SQL in the real world

This is part 4 of our Oracle PLSQL tutorial showing how you can use PL/SQL to simplify your Oracle systems.  So far we've covered the data types, the language elements and discussed some of its pros and cons . Now let’s look at using PL/SQL in the real world. One of the main reasons for using PL/SQL is to provide some structure to our SQL statements that are retrieving or modifying data in the database. In one sense therefore, you could regard PL/SQL as a wrapper for SQL which means we need to know how to use SQL within PL/SQL. Fortunately for us, Oracle have made this easy and straightforward and provides tow methods for doing so. We can either embed SQL statements directly into PL/SQL blocks (as implicit cursors) or we can define explicit cursors for SQL SELECT statements. Let's look at an example to make things clear. Let's suppose that we want to fetch the result from a query and we know that this query can only ever return one row (this last factor is important as we'll see in a minute). To do this we simply embed the query within our PLSQL code. 1. PROCEDURE check_max_sal (max_sal_limit_breached IN OUT BOOLEAN) IS 2.     max_sal emp.salary%TYPE; 3. BEGIN ... <preceding logic> ...     4. SELECT MAX(salary) INTO max_sal FROM emp;     5. max_sal_limit_breached := max_sal > 2000000 ; ... <more logic> ... 6. END check_max_sal; Now let's dissect this code to explain what's going on and to highlight the important bits. By the way, the line numbers are not part of the PL/SQL block - they are just there to make the discussion easier. Line 1 declares a procedure called check_max_sal which takes one parameter called max_sal_limit_breached which is of type Boolean. This parameter can both be read from and written to by the procedure (this is signified by specifying "IN OUT" between the parameter name and the parameter type). On line 2 we declare a local variable max_sal which we've anchored to the definition of salary in the emp table by use of the construct emp.salary%TYPE. This has two advantages: 1. we don't need to go and find out whether salary is integer or floating point 2. if the definition of salary in the emp table changes from fixed to floating or even to varchar2, we don't have to worry - all we need to do is recompile our procedure and Oracle handles the rest. Line 4 is the next line of interest and this is a single-row query. We know that this query will return only one row because there's no group by clause and we're using the MAX function to find the maximum value of this column in the table. By using the MAX function we ensure that the query will always return exactly one row, even if the table is empty (in which case it returns NULL). The result of the query is stored in the local variable max_sal by use of the INTO clause, thereby making it accessible to the rest of our PLSQL block Note that use of the INTO clause is compulsory with implicit cursors that retrieve data from the database. Line 5 sets the value of the output parameter max_sal_limit_breached by comparing the value of max_sal returned from the query with a constant. If max_sal is higher than 20000 then max_sal_limit_breached is set to TRUE. Note that Boolean variables in Oracle PL/SQL can have 3 values: TRUE, FALSE or NULL (undefined). This means that, in this case, if max_sal is null then max_sal_limit_breached will also be set to null (because if you compare something known with something unknown the result is unknown), Line 6 closes this PL/SQL block, in this case the procedure check_max_sal. Labeling the end of the procedure or function or package is not compulsory but is good practice as it makes your code easier to read and therefore easier to maintain. Now let's go back to the query. This particular query always returns one row (because we're using a group function without a group by clause), and one column into a single variable, but what would happen if we wanted to fetch several columns at once? Or the query returned more than one row? The first situation is easy to resolve - we just need to declare one variable for each column returned from the query and include them in the INTO clause. However if the query returned more than one row, (unless we’re doing a bulk collect which we’ll cover in another tutorial) this would cause an error and Oracle would raise an exception - in this case the error would be ORA-01422 which would generate a TOO_MANY_ROWS exception. Unless we have an exception handler (which we don’t in this case), execution of the code stops at that point and control is returned to the invoking environment. An implicit cursor is also at risk of generating a NO_DATA_FOUND exception which would be caused if the SQL query returned no data (again, which can't happen in this case). These errors and others can be trapped within the Oracle PL/SQL block by the use of an exception handler which will be explored in detail in another tutorial, but for completeness, the exception handler must be the last section before the end of the Oracle PL/SQL block. The general syntax is ... BEGIN /* start of anonymous block or procedure/function */     < PL/SQL statements> EXCEPTION     WHEN <exception> THEN <PL/SQL statements> ; END ; /* of anonymous block or procedure/function */ We've only looked at querying the database in this PL/SQL tutorial because that's the most common type of SQL statement. UPDATE/INSERT/DELETE statements follow the same rules as in SQL but can access PL/SQL variables - they just need to be referenced in the relevant part of the SQL statement. Oracle PLSQL tutorial Part 3                                                                            Oracle PLSQL tutorial Part 5 For more help with PL/SQL why not consider taking one of our formal training courses? We offer instructor-led training at centres across the country. See the Oracle training page for details or contact us  for more information.  --------------------------------------- Looking to sky-rocket productivity, save time and reduce costs? Training is a highly cost-effective and proven method of boosting productivity. Smartsoft offers instructor- led training in Oracle and related technologies on or off site in cities across the UK as well as self-study online training. See our scheduled Oracle training courses, or let us know your requirements. Looking for more Oracle tips and tricks? For no frills, no fluff, just solid, reliable technical information, take a short cut now and subscribe to our newsletter. Jam-packed full of tips and tricks, it will help you make your Oracle systems faster and more reliable and save you hours searching for information. Subscribe today - there's no charge - and your first issue will soon be winging its way to your mailbox. Smartsoft Computing Ltd, Bristol, England Tel: 0845 003 1320 Need help with Oracle? Contact Us View our privacy policy This site uses woopra.com to gather statistical information about our visitors. This data is aggregated to show industry trends (such as browser share). However, this data shall be the average of many thousands of visits and is in no way linked to individuals. View woopra privacy policy.  Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.