The Oracle PL/SQL language contains constructs to write procedural language statements but also has hundreds of built-in functions and procedures available to use in your PL/SQL programs. Understanding what these are and how they are used is key to saving hundreds of hours not writing code segments that are already available.
- [Instructor] Oracle PL/SQL gives you all the benefits of a modern programming language and tightly integrates that programming language with the Oracle 19c SQL language itself. It does however require you to write a procedure to call it. If Oracle already provides that functionality, you should use it. I'll tell you all about the provided functions and procedures and how to use them. The built in Oracle 19c functions fall into five basic categories, character, numeric, date and date arithmetic, conversion, and general. Regardless of what category they are in, you need to know about them so you can use them effectively from custom PL/SQL procedures. Single row functions return a single row or column value for every row of the query. These functions can appear in four places, the select clause, where clause, the start with connect by clause, group by and having. On the example on the slide I'm using a single row numeric function in the having clause only because the operand is part of a group by, with an aggregation function in the select statement. What are the actual numeric single row functions? The list is rather short compared to the others and almost half of them are related to trigonometry. Almost all of them should look familiar to you if you have any background in mathematics. All of them return a single number as a result, either as an integer, a decimal or the fractional portion or a floating point number. You can see the full documentation on Oracle's website for all of the single row functions. The sample query shows the sum of the salaries of all employees by department ID, but only if the modulus nine value of the department ID was greater than five. A single row numeric function with two arguments filtering the result set. Any numeric function operating on a column or expression that evaluates to null, returns a null. The example shows all the use cases in one select statement. The square root of a null is null, not zero. Another set of single row functions returns character strings. Some are basic and self-explanatory like a knit cap. Some are a bit more obscure like sound decks. Even one small example shows the value of having these built-in functions. This query shows several columns from the locations table but since the street address is so long and I don't need the whole thing in the report, I use the substring function and truncate it to 20 characters. The list of functions that return a number is quite short. Most of them count or search in a string and return a number. A large number of the single row built in functions return date, timestamp, or time intervals and they fall into three categories. Time intervals are a subset of time values that store intervals, either in terms of years and months or days and seconds and they're used in date arithmetic. Miscellaneous functions include months between, returning a fractional number of months that have elapsed between the two dates you provide. This query will show what the date will be after adding a hundred months to the hire date for employees with the last name of Grant. If you didn't have the add months function, calculating the number of months between dates would be complex and tedious. The list of conversion functions is longer than the rest and most of them are related to string conversion. Many of them starting with two underscore are somewhat obvious, many are not. They can roughly be divided into three categories, character string related, date time conversions, and many miscellaneous functions. Final category is the general functions and includes generic comparison operations, large object operations, collection management, XML manipulation, JSON manipulation and low level encoding and decoding functions to help you find out how your data is stored behind the scenes. Everything so far has been about built-in functions. I would be remiss if I didn't cover the other types of PL/SQL objects. They are a mix of DBA, developer and user procedures. They are typically delivered in a package which is Oracle's way to deliver them and group them. Because their procedures, you'll also call them from other procedures, so you won't see them in a select statement. Here I'm calling a builtin procedure to update statistics on a table. Here are the procedures of the DBMS space package. The space usage procedure for example, returns how the free space in a table is broken down by block. What should you remember about functions and procedures? The built-in functions fall into categories based on what they return. Character strings, numeric values, date and date arithmetic manipulation, conversion, and some miscellaneous ones. And don't forget the PL/SQL built in packages. They're useful for DBAs and developers who will likely call them from custom store procedures or anonymous blocks. They often need to return multiple values, another reason why they are delivered as procedures.