Blocks, Functions, and Procedures
In this chapter, as the title suggests, you’ll learn about the foundational structures of PL/SQL: blocks, functions, and procedures. Blocks define the structures upon which functions and procedures are built. Functions and procedures are very similar, but distinct, in that a function returns a value to a calling function or procedure, while a procedure does not. So let’s begin our discussion of PL/SQL by starting with its foundation: the block.
Blocks
The Procedure Language extension for SQL (PL/SQL) has a block structure. Every piece of code executes in a block, similar to Java, JavaScript, and PHP’s try-catch clause; Python’s try-except clause; or Ruby’s begin-rescue-end clause. However, PL/SQL has four keywords:
We’ll start with an anonymous PL/SQL block. It’s called anonymous because it’s not going to be saved in the database, so it will never have a name. In practice, you won’t find yourself using anonymous blocks in production, but you’ll use them throughout the development process. They’re perfect for creating test units that you’ll eventually move into your stored PL/SQL blocks. You’ll learn more about that in Chapter 8. For now, let’s look at an example.
Anonymous Block Example
Listing 2-1 is an example of an anonymous PL/SQL block. Comments describe the different sections.
Listing 2-1. An Anonymous PL/SQL Block, anonymous.sql
01 -- This is an anonymous procedure, so it has no name
02 DECLARE
03 /*
04 You declare local cursors, variables, and methods here,
05 but you don't need to have a declaration section.
06 */
07 BEGIN
08 -- You write your executable code here
09
10 NULL; -- Ahhh, you've got to have at least one command!
11 EXCEPTION
12 when NO_DATA_FOUND then
13 raise_application_error(-20000,
14 'Hey, This is in the exception-handling section!'),
15 END;
16 /
17 -- the forward slash on a line by itself says execute this procedure
Let’s go through Listing 2-1 line by line:
It’s Your Turn to Execute an Anonymous Block
Now it’s time for you to execute an anonymous PL/SQL block/program.
set serveroutput on size 1000000
begin
SYS.DBMS_OUTPUT.put_line('Hi there genius!'),
end;
/
After you type the forward slash on a line by itself, you will have executed your very first PL/SQL procedure, and a very complimentary one at that.
The first line tells SQL*Plus to echo the database’s output for your session to the screen after the PL/SQL procedure is finished executing. Note that set serveroutput on size 1000000 is a SQL*Plus command and is not part of the anonymous block.
The line that starts with begin starts your PL/SQL procedure’s executable code section. The next line calls the put_line procedure in package (library of stored procedures) DBMS_OUTPUT, owned by user SYS:
sys.dbms_output.put_line('Hi there genius!'),
Here, put_line stores your text in a buffer until the PL/SQL procedure ends. Then SQL*Plus displays the text from that buffer on the screen for you to see.
The fourth line, which starts with end, ends the anonymous PL/SQL procedure. Finally, as stated earlier, the forward slash (/) on a line by itself tells SQL*Plus to execute the procedure.
In this example, you didn’t code declaration or exception-handling sections, but they’re still there! If an error occurs between the BEGIN and END keywords, PL/SQL will use the default (invisible) exception handler to raise the error to the enclosing program unit, which, in this case, is SQL*Plus.
Exceptions allow you to catch errors as your PL/SQL program executes, so you have control over what happens in response to those errors. PL/SQL predefines more than 20 named exceptions, but you’ll probably use only a few.
Note After you’ve gained more PL/SQL experience, you may even want to define your own named exceptions. For more information about custom exceptions, see Oracle’s PL/SQL User’s Guide and Reference.
Common Exceptions
As you’ll learn in future chapters, there are a handful of commonly seen exceptions. The rest occur only when there are catastrophic problems with the database, the network, or your PL/SQL code.
Two exceptions are very common:
Catching an Exception
You add the keyword EXCEPTION between the keywords BEGIN and END in order to add an exception-handling section to your PL/SQL block. Once you do that, any error that occurs between the keywords BEGIN and EXCEPTION will be handled by your exception-handling section.
Functions
A FUNCTION is a PL/SQL block or method that returns a value, so it can be used on the right-hand side of an assignment. Here is an example:
n_value := to_number('123.45'),
In this line of PL/SQL code, n_value is a numeric variable (we’ll cover variables in the next chapter). n_value is followed by an assignment operator, which is a colon followed by an equal sign (:=). Next is the PL/SQL built-in function to_number(text in varchar2), which parses a varchar2 data type, and then returns its numeric value—that is, if the varchar2 represents a number; otherwise, the function raises an INVALID_NUMBER exception.
Since a FUNCTION returns a value, you can also use it in a SQL statement, as in this example:
SQL> select to_number('A') from dual;
select to_number('A') from dual
*
ERROR at line 1:
ORA-01722: invalid number
Look, there’s that INVALID_NUMBER exception I was talking about!
Create a Function
Instead of dealing with errors when we try to convert a varchar2 (character string) to a number on the fly as in a SELECT statement, let’s create an errorless to_number() function. Listing 2-2 is the DDL to do just that.
Listing 2-2. An Errorless to_number( ) Function, to_number_or_null.fun
01 CREATE OR REPLACE FUNCTION to_number_or_null (
02 aiv_number IN varchar2 )
03 return number is
04 /*
05 to_number_or_null.fun
06 by Donald J. Bales on 2014-10-20
07 An errorless to_number( ) method
08 */
09 begin
10 return to_number(aiv_number);
11 exception
12 when OTHERS then
13 return NULL;
14 end to_number_or_null;
15 /
16 @fe.sql to_number_or_null;
The DDL syntax used in Listing 2-2 to create the function is as follows:
CREATE [OR REPLACE] FUNCTION <function_name> [(
<parameter_name_1> [IN] [OUT] <parameter_data_type_1>,
<parameter_name_2> [IN] [OUT] <parameter_data_type_2>,...
<parameter_name_N> [IN] [OUT] <parameter_data_type_N> )]
RETURN <return_data_type> IS
--the declaration section
BEGIN
-- the executable section
return <return_data_type>;
EXCEPTION
-- the exception-handling section
END;
/
where <function_name> is the name of the FUNCTION; <parameter_name> is the name of a parameter being passed IN, OUT, or IN and OUT; <parameter_data_type> is the PL/SQL data type of the corresponding parameter; and <return_data_type> is the PL/SQL data type of the value that will be returned by the FUNCTION when it completes its execution. The brackets ([]) around the keywords OR REPLACE denote that they are optional. The brackets around the parameters denote that they are optional, too.
The block structure of a FUNCTION is exactly the same as an anonymous procedure, except for the addition of the Data Definition Language (DDL) CREATE FUNCTION keywords, the optional parameters, and the RETURN clause.
Let’s take a look at Listing 2-2 line by line:
Now let’s try using this errorless FUNCTION in a SELECT statement:
SQL> select to_number_or_null('A') from DUAL;
TO_NUMBER_OR_NULL('A')
----------------------
Ta da! It returned a NULL value since the letter A is not a number. Just in case you’re a skeptic, here’s a second test:
SQL> select to_number_or_null('234.56') from DUAL;
TO_NUMBER_OR_NULL('234.56')
---------------------------
234.56
Yes, indeed, it works correctly!
It’s Your Turn to Create a Function
For this exercise, you’ll create a function that returns a date value. You can use the function in Listing 2-2 as a model. This time, you’ll pass this new function a varchar2 value that represents a date in the form MM/DD/YYYY. Your function will parse the varchar2 value and return a date data type if the varchar2 value is actually a date in the form MM/DD/YYYY; otherwise, it will return NULL.
You can use the PL/SQL built-in function to_date(text in varchar2, format in varchar2) in your function to do the actual parsing of the date. In the following form, to_date( ) will return a date value if it successfully parses the passed varchar2 value, or raise an exception if the passed varchar2 value is not a date in the format MM/DD/YYYY:
return to_date(aiv_date, 'MM/DD/YYYY'),
Using this built-in function, create your function by following these steps.
Listing 2-3 shows my solution for this exercise.
Listing 2-3. An Errorless to_date( ) Function, to_mmsddsyyyy_or_null.fun
01 create or replace FUNCTION to_mmsddsyyyy_or_null (
02 aiv_date in varchar2 )
03 return date is
04 /*
05 to_mmsddsyyyy_or_null.fun
06 by Donald J. Bales on 2014-10-20
07 An errorless to_date( ) method
08 */
09 begin
10 return to_date(aiv_date, 'MM/DD/YYYY'),
11 exception
12 /*
13 There are too many possible errors, for example:
14 ORA-01830: date format picture ends before
15 converting entire input string
16 ORA-01843: not a valid month
17 ORA-01847: day of month must be between 1
18 and last day of month
19 ORA-01858: a non-numeric character was found
20 where a numeric was expected
21 so I used the exception OTHERS
22 */
23 when OTHERS then
24 return NULL;
25 end to_mmsddsyyyy_or_null;
26 /
27 @fe.sql to_mmsddsyyyy_or_null;
First, I’ll test it using a SELECT statement against DUAL:
SQL> select to_mmsddsyyyy_or_null('A') from DUAL;
TO_MMSDDSYYYY_OR
----------------
Well, that worked. The to_mmsddsyyyy_or_null() function returned a NULL because the letter A is not a date in the form MM/DD/YYYY. So let’s try a date string. This time, however, I’ll execute the anonymous PL/SQL block in Listing 2-4 as a test unit in order to test the function.
Listing 2-4. A Test Unit for Function to_mmsddsyyyy_or_null( ), to_mmsddsyyyy_or_null.sql
01 rem to_mmsddsyyyy_or_null.sql
02 rem by Donald J. Bales on 2014-10-20
03 rem FUNCTION to_mmsddsyyyy_or_null() test unit
04
05 begin
06 sys.dbms_output.put_line(to_mmsddsyyyy_or_null('01/01/1980'));
07 sys.dbms_output.put_line(to_mmsddsyyyy_or_null('02/29/1980'));
08 sys.dbms_output.put_line(to_mmsddsyyyy_or_null('02/29/1981'));
09 sys.dbms_output.put_line(to_mmsddsyyyy_or_null('9/9/2006'));
10 sys.dbms_output.put_line(to_mmsddsyyyy_or_null('9/9/9999'));
11 sys.dbms_output.put_line(to_mmsddsyyyy_or_null('1/1/4712 BC'));
12 end;
13 /
The following output from executing function to_mmsddsyyyy_or_null()’s test unit shows that it’s working great. The tests on lines 8 and 11 should have returned NULL because I passed invalid date strings, and they did!
SQL> @to_mmsddsyyyy_or_null.sql
01-JAN-80
29-FEB-80
09-SEP-06
09-SEP-99
PL/SQL procedure successfully completed.
Up to this point, you’ve seen an anonymous PL/SQL block and a function’s PL/SQL block. The differences between the two are that an anonymous block is not permanently stored in the database with a name, nor can it return a value. Now let’s take a look at PL/SQL blocks that do have names, but don’t return values: procedures.
Procedures
PL/SQL procedures don’t return a value. They just perform their instructions and return. Of course, this means that you can’t use procedures on the right-hand side of an assignment statement like a function.
Create a Procedure
Listing 2-5 is a very simple example of a procedure. It’s a wrapper around the Oracle SYS.DBMS_LOCK package’s procedure sleep(seconds in number). This procedure will stop executing (or sleep) without using many CPU cycles for the number of seconds specified. I’ll admit, it’s kind of lame, but we’re just starting out here, so I’m keeping things simple.
Note By default, the SYS.DBMS_LOCK package is not accessible by non-DBA users. The username creation script in this book’s appendix will grant you access, but if you don’t use that script, you may need to ask your DBA to explicitly grant you execute access to package SYS.DBMS_LOCK.
Listing 2-5. A Wrapper Procedure for SYS.DBMS_LOCK.sleep( ), wait.prc
01 CREATE OR REPLACE PROCEDURE wait(
02 ain_seconds IN number) is
03 /*
04 wait.prc
05 by Donald J. Bales on 2014-10-20
06 Wrapper for SYS.DBMS_LOCK.sleep()
07 */
08 begin
09 SYS.DBMS_LOCK.sleep(ain_seconds);
10 end wait;
11 /
12 @pe.sql wait
The DDL syntax used by Listing 2-5 is as follows:
CREATE [OR REPLACE] PROCEDURE <procedure_name> [(
<parameter_name_1> [IN] [OUT] <parameter_data_type_1>,
<parameter_name_2> [IN] [OUT] <parameter_data_type_2>,...
<parameter_name_N> [IN] [OUT] <parameter_data_type_N> )] IS
--the declaration section
BEGIN
-- the executable section
EXCEPTION
-- the exception-handling section
END;
/
where <procedure_name> is the name of the PROCEDURE; <parameter_name> is the name of a parameter being passed IN, OUT, or IN and OUT; and <parameter_data_type> is the PL/SQL data type of the corresponding parameter. The brackets around the keywords OR REPLACE denote that they are optional. In addition, just as with a function, the brackets around the parameters denote that they are optional.
The block structure of a PROCEDURE is exactly the same as an anonymous block, except for the addition of the DDL CREATE PROCEDURE keywords and the optional parameters. A procedure differs from a function in that it does not have a RETURN parameter.
Let’s take a look at Listing 2-5, line by line:
The PL/SQL block structure for the stored procedure in Listing 2-5 was really not that much different from the structure of the anonymous procedure and the stored function. The wait() procedure has a name and parameters, while an anonymous procedure does not. In addition, wait() does not return a value, while a stored function does.
It’s Your Turn to Create a Procedure
Now you’ll create a procedure that wraps the SYS.DBMS_OUTPUT.put_line() procedure, but uses a very short name. You’ll end up using the SYS.DBMS_OUTPUT.put_line() procedure a lot. It gets tiresome to type a 24-character method name every time you want to display a line of text on the screen in SQL*Plus. So, to save keystrokes, you will give your SYS.DBMS_OUTPUT.put_line() wrapper procedure the name pl(), as in p for put and l for line.
You can use the procedure in Listing 2-5 as a model. Just replace the parameter on line 2 with aiv_text in varchar2, and write line 9 with a call to SYS.DBMS_OUTPUT.put_line(aiv_text).
Listing 2-6 shows my solution for this exercise.
Listing 2-6. A Lazy Typist’s SYS.DBMS_OUTPUT.put_line( ), pl.prc
01 create or replace PROCEDURE pl(
02 aiv_text in varchar2 ) is
03 /*
04 pl.prc
05 by Donald J. Bales on 2014-10-20
06 A wrapper procedure for SYS.DBMS_OUTPUT.put_line()
07 for the lazy typist.
08 */
09
10 begin
11 SYS.DBMS_OUTPUT.put_line(aiv_text);
12 end pl;
13 /
14 @pe.sql pl
Listing 2-7 is my test unit for procedure pl().I’ve named it with the same file name as my procedure, except for the extension: I used .prc for the stored procedure and .sql for the anonymous procedure, its test unit. Since this is a wrapper procedure, I’m simply testing the known limits of procedure SYS.DBMS_OUTPUT.put_line() to make sure pl() is working properly.
Listing 2-7. A Test Unit for Procedure pl( ), pl.sql
01 rem pl.sql
02 rem by Donald J. Bales on 2014-10-20
03 rem pl's test unit
04
05 declare
06
07 v_max_line varchar2(32767);
08
09 begin
10 -- The next three lines initialize the
11 -- variable v_max_line with 32,767 spaces.
12 for i in 1..32767 loop
13 v_max_line := v_max_line || ' ';
14 end loop;
15
16 pl('Test a line of text.'),
17
18 pl('Test a number, such as 1?'),
19 pl(1);
20
21 pl('Test a date, such as 01/01/1980?'),
22 pl(to_date('19800101', 'YYYYMMDD'));
23
24 pl('Test a line <= 32767'),
25 pl(v_max_line);
26
27 pl('Test a line > 32767'),
28 pl(v_max_line||' '),
29
30 pl('Test a multi-line'),
31 pl('12345678901234567890123456789012345678901234567890'||
32 '12345678901234567890123456789012345678901234567890'||chr(10)||
33 '12345678901234567890123456789012345678901234567890'||
34 '12345678901234567890123456789012345678901234567890'||chr(10)||
35 '12345678901234567890123456789012345678901234567890'||
36 '12345678901234567890123456789012345678901234567890'),
37 end;
38 /
Here’s the output from my test unit:
SQL> @pl.sql
Test a line of text.
Test a number, such as 1?
1
Test a date, such as 01/01/1980?
01-JAN-80
Test a line <= 32767
Test a line > 32767
declare
*
ERROR at line 1:
ORA-20000: ORU-10028: line length overflow, limit of 32767 bytes per line
ORA-06512: at "SYS.DBMS_OUTPUT", line 32
ORA-06512: at "SYS.DBMS_OUTPUT", line 91
ORA-06512: at "SYS.DBMS_OUTPUT", line 112
ORA-06512: at "RPS.PL", line 11
ORA-06512: at line 24
The line of text, number 1, date 01/01/1980, and line <= 32767 characters tests ran, but the last test, multiline, didn’t because the > 32767 test threw an exception that was not handled. Let’s see how the code can handle this problem.
You can, and should when necessary, nest PL/SQL blocks. To nest a PL/SQL block means to embed one or more PL/SQL blocks inside another PL/SQL block. Nesting PL/SQL blocks allows you greater control over your PL/SQL program’s execution. You can wrap one or more PL/SQL or SQL statements in their own PL/SQL block, so you can catch an exception that is generated within that block. I call this “blocking” code. Let’s look at an example.
An Example of Nesting Blocks
As you saw, the test unit for procedure pl() in Listing 2-7 stopped executing after line 28 because line 28 caused the following error:
ORA-20000: ORU-10028: line length overflow, limit of 32767 bytes per line
In Listing 2-8, I’ve recoded the pl() test unit. This time, I’ve blocked the lines that I expect to fail, so the program will catch any raised exceptions and continue processing the code all the way to the end of the source code listing.
Listing 2-8. The New, Improved pl( ) Test Unit, pl2.sql
01 rem pl2.sql
02 rem by Donald J. Bales on 2014-10-20
03 rem Test unit for procedure pl
04
05 declare
06 v_max_line varchar2(32767);
07
08 begin
09 -- The next three lines initialize a variable v_max_line with 32,767 spaces.
10 for i in 1..32767 loop
11 v_max_line := v_max_line || ' ';
12 end loop;
13
14 pl('Test a line of text.'),
15
16 pl('Test a number, such as 1?'),
17 pl(1);
18
19 pl('Test a date, such as 01/01/1980?'),
20 pl(to_date('19800101', 'YYYYMMDD'));
21
22 pl('Test a line <= 32767'),
23 pl(v_max_line);
24
25 pl('Test a line > 32767'),
26 begin
27 pl(v_max_line||' '),
28 exception
29 when OTHERS then
30 pl(SQLERRM);
31 end;
32
33 pl('Test a multi-line'),
34 begin
35 pl('12345678901234567890123456789012345678901234567890'||
36 '12345678901234567890123456789012345678901234567890'||chr(10)||
37 '12345678901234567890123456789012345678901234567890'||
38 '12345678901234567890123456789012345678901234567890'||chr(10)||
39 '12345678901234567890123456789012345678901234567890'||
40 '12345678901234567890123456789012345678901234567890'),
41 exception
42 when OTHERS then
43 pl(SQLERRM);
44 end;
45 end;
46 /
The changed lines (highlighted in the listing) are as follows:
Let’s see the results of these changes:
SQL> @pl2.sql
Test a line of text.
Test a number, such as 1?
1
Test a date, such as 01/01/1980?
01-JAN-80
Test a line <= 32767
Test a line > 32767
ORA-20000: ORU-10028: line length overflow, limit of 32767 bytes per line
Test a multi-line
1234567890123456789012345678901234567890123456789012345678901234567890...
PL/SQL procedure successfully completed.
Great! Now my test unit completed successfully, and it reported the errors I expected to see. As we move through the chapters ahead, you’ll get plenty of experience nesting PL/SQL blocks.
Rules for Nesting
Here are the rules I employ when it comes to blocking PL/SQL code:
The important point about these nesting rules is that blocking will enable you to identify the reason for and the location of an exception when it is raised. In turn, that will make your software development easier and the resulting programs more robust.
In practice, you’ll rarely create a stand-alone stored function or procedure. Instead, you’ll use a package. What is a package? A package is a means to organize related functions and procedures together, like creating a library, but in PL/SQL jargon the library is called a package.
A PL/SQL package has two parts:
A package specification (spec) is the public face to the package. It lists any globally accessible constants, cursors, functions, procedures, and variables. By “globally accessible,” I mean those procedures, functions, and other items that other PL/SQL programs can access. If you consider a package as a sort of library, then the package spec describes what you can read from that library, while the package body contains the behind-the-scenes code that implements the package spec.
Create a Package Specification
Listing 2-9 is an example of a package spec. It shows the package spec for the package DATE_, which is a utility package for date-related constants and functions.
Listing 2-9. The DATE_ Package Spec, date_.pks
01 create or replace package DATE_ AS
02 /*
03 date_.pks
04 by Donald J. Bales on 2014-10-20
05 Additional DATE data type methods.
06 */
07
08 -- The maximum and minimum date values.
09
10 d_MAX constant date :=
11 to_date('99991231235959', 'YYYYMMDDHH24MISS'),
12 d_MIN constant date :=
13 to_date('-47120101', 'SYYYYMMDD'),
14
15
16 -- Returns the specified date with the time set to 23:59:59, therefore,
17 -- the end of the day.
18
19 FUNCTION end_of_day(
20 aid_date in date )
21 return date;
22
23
24 -- Returns constant d_MAX. This is useful in SQL statements where the
25 -- constant DATE_.d_MAX is not accessible.
26
27 FUNCTION get_max
28 return date;
29
30
31 -- Returns constant d_MIN. This is useful in SQL statements where the
32 -- constant DATE_.d_MIN is not accessible.
33
34 FUNCTION get_min
35 return date;
36
37
38 -- Text-based help for this package. "set serveroutput on" in SQL*Plus.
39
40 PROCEDURE help;
41
42
43 -- Returns a randomly generated date that exists between the years specified.
44
45 FUNCTION random(
46 ain_starting_year in number,
47 ain_ending_year in number )
48 return date;
49
50
51 -- Returns the specified date with the time set to 00:00:00, therefore, the
52 -- start of the day.
53
54 FUNCTION start_of_day(
55 aid_date in date )
56 return date;
57
58
59 -- Test unit for this package.
60
61 PROCEDURE test;
62
63
64 end DATE_;
65 /
66 @se.sql DATE_
The DDL syntax used to create the package spec in Listing 2-9 is as follows:
CREATE [OR REPLACE] PACKAGE <package_name> AS
-- one or more: constant, cursor, function, procedure, or variable declarations
END <package_name>;
where <package_name> is the name of the package you’re creating.
Not much to a package spec? Sure there is. Now, instead of using the DDL CREATE FUNCTION or CREATE PROCEDURE, you’ll use the keywords FUNCTION and PROCEDURE for each PL/SQL method you want to declare globally.
Let’s take a look at the package spec in Listing 2-9:
You may also have noticed that I took the time to document the purpose of every declaration in the package spec. You should do the same. Documenting as you go is one of the major characteristics of a professional PL/SQL programmer.
It’s Your Turn to Create a Package Specification
Do you remember the function to_number_or_null() in Listing 2-2? Now you will create a package called NUMBER_ that has it as one of its methods. Mimic what I just showed you as you follow these steps.
As usual, if you get any errors, figure out what’s wrong so you can compile the script successfully. Remember to use the script in Listing 2-9 as a model. Listing 2-10 shows my solution.
Listing 2-10. The NUMBER_ Package Spec, number_.pks
01 create or replace package NUMBER_ as
02 /*
03 number_.pks
04 by Donald J. Bales on 2014-10-20
05 A utility package for the data type NUMBER
06 */
07
08 /*
09 Returns the passed varchar2 as a number if it represents a number,
10 otherwise, it returns NULL
11 */
12 FUNCTION to_number_or_null (
13 aiv_number in varchar2 )
14 return number;
15
16 end NUMBER_;
17 /
18 @se.sql
There’s no way to test the package spec, because there’s no code yet. The code goes in the package body.
Create a Package Body
A package body is the implementation for a package spec. It must contain the code for any functions or procedures declared in its corresponding package spec. In addition, the body can also contain any constant, cursor, function, procedure, or variable that should be accessible within the package body (that is, not publicly accessible). Let’s take a look at the corresponding package body for package DATE_.
I’m about to show you a package body that has a lot of PL/SQL code that we haven’t covered yet. Don’t worry too much about the code itself. You won’t understand it all now, but you’ll be able to understand it after finishing this book. What you need to take away from this example is the package body structure, which is also a PL/SQL block structure. Let’s look at Listing 2-11.
Listing 2-11. The DATE_ Package Body, date_.pkb
001 create or replace package body DATE_ AS
002 /*
003 date_.pkb
004 by Donald J. Bales on 2014-10-20
005 Additional DATE data type methods
006 */
007
008
009 FUNCTION end_of_day(
010 aid_date in date )
011 return date is
012
013 begin
014 return to_date(to_char(aid_date, 'SYYYYMMDD')||'235959',
015 'SYYYYMMDDHH24MISS'),
016 end end_of_day;
017
018
019 FUNCTION get_max
020 return date is
021
022 begin
023 return d_MAX;
024 end get_max;
025
026
027 FUNCTION get_min
028 return date is
029
030 begin
031 return d_MIN;
032 end get_min;
033
034
035 FUNCTION random(
036 ain_starting_year in number,
037 ain_ending_year in number )
038 return date is
039
040 d_random date;
041 n_day number;
042 n_month number;
043 n_year number;
044
045 begin
046 n_year := round(DBMS_RANDOM.value(
047 ain_starting_year, ain_ending_year), 0);
048 --pl('n_year='||n_year);
049 loop
050 n_month := round(DBMS_RANDOM.value(1, 12), 0);
051 --pl('n_month='||n_month);
052 n_day := round(DBMS_RANDOM.value(1, 31), 0);
053 --pl('n_day='||n_day);
054 begin
055 d_random := to_date(lpad(to_char(n_year), 4, '0')||
056 lpad(to_char(n_month), 2, '0')||
057 lpad(to_char(n_day), 2, '0'),
058 'YYYYMMDD'),
059 exit;
060 exception
061 when OTHERS then
062 if SQLCODE <> -1839 then
063 pl(SQLERRM);
064 --else
065 -- pl('29-31'),
066 end if;
067 end;
068 end loop;
069 return d_random;
070 end random;
071
072
073 FUNCTION start_of_day(
074 aid_date in date )
075 return date is
076
077 begin
078 return trunc(aid_date);
079 end start_of_day;
080
081
082 -- Write up the help text here in this help method
083 PROCEDURE help is
084
085 begin
086 -- 1234567890123456789012345678901234567890123456789012345678901234567890
087 pl('============================== PACKAGE =============================='),
088 pl(chr(9));
089 pl('DATE_'),
090 pl(chr(9));
091 pl('------------------------------ CONSTANTS ----------------------------'),
092 pl(chr(9));
093 pl('d_MAX'),
094 pl(chr(9)||'Represents the maximum value for the DATE data type.'),
095 pl('d_MIN'),
096 pl(chr(9)||'Represents the minimum value for the DATE data type.'),
097 pl(chr(9));
098 pl('------------------------------ FUNCTIONS ----------------------------'),
099 pl(chr(9));
100 pl('DATE_.end_of_day('),
101 pl('aid_date in date)'),
102 pl('return date;'),
103 pl(chr(9)||'Returns the passed date with the time portion set to the end '),
104 pl(chr(9)||'of the day:'),
105 pl(chr(9)||'23:59:59 (HH24:MI:SS).'),
106 pl(chr(9));
107 pl('DATE_.get_max( )'),
108 pl('return date;'),
109 pl(chr(9)||'Returns the constant DATE_.d_MAX.'),
110 pl(chr(9));
111 pl('DATE_.get_mim( )'),
112 pl('return date;'),
113 pl(chr(9)||'Returns the constant DATE_.d_MIN.'),
114 pl(chr(9));
115 pl('DATE_.random('),
116 pl('ain_starting_year in number,'),
117 pl('ain_ending_year in number)'),
118 pl('return date;'),
119 pl(chr(9)||'Returns a random date that exists between the specified years.'),
120 pl(chr(9));
121 pl('DATE_.start_of_day('),
122 pl('aid_date in date)'),
123 pl('return date;'),
124 pl(chr(9)||'Returns the passed date with the time portion set to the start'),
125 pl(chr(9)||'of the day:'),
126 pl(chr(9)||'00:00:00 (HH24:MI:SS).'),
127 pl(chr(9));
128 pl('------------------------------ PROCEDURES ----------------------------'),
129 pl(chr(9));
130 pl('DATE_.help( );'),
131 pl(chr(9)||'Displays this help text if set serveroutput is on.'),
132 pl(chr(9));
133 pl('DATE_.test( );'),
134 pl(chr(9)||'Built-in test unit. It will report success or error for each'),
135 pl(chr(9)||'test if set'),
136 pl(chr(9)||'serveroutput is on.'),
137 pl(chr(9));
138 end help;
139
140
141 PROCEDURE test is
142
143 d_date date;
144
145 begin
146 pl('============================== PACKAGE ==============================='),
147 pl(chr(9));
148 pl('DATE_'),
149 pl(chr(9));
150 pl('1. Testing constants d_MIN and d_MAX'),
151 if d_MIN < d_MAX then
152 pl('SUCCESS'),
153 else
154 pl('ERROR: d_MIN is not less than d_MAX'),
155 end if;
156
157 pl('2. Testing end_of_day()'),
158 if to_char(end_of_day(SYSDATE), 'HH24MISS') = '235959' then
159 pl('SUCCESS'),
160 else
161 pl('ERROR: end_of_day is not 23:59:59'),
162 end if;
163
164 pl('3. Testing get_max()'),
165 if get_max() = d_MAX then
166 pl('SUCCESS'),
167 else
168 pl('ERROR: get_max() is not equal to d_MAX'),
169 end if;
170
171 pl('4. Testing get_min()'),
172 if get_min() = d_MIN then
173 pl('SUCCESS'),
174 else
175 pl('ERROR: get_min() is not equal to d_MIN'),
176 end if;
177
178 pl('5. Testing random() 1000 times'),
179 for i in 1..1000 loop
180 d_date := random(1, 9999);
181 --pl(to_char(d_date, 'YYYY-MM-DD HH24:MI:SS'));
182 end loop;
183 pl('SUCCESS'),
184
185 pl('6. Testing start_of_day()'),
186 if to_char(start_of_day(SYSDATE), 'HH24MISS') = '000000' then
187 pl('SUCCESS'),
188 else
189 pl('ERROR: start_of_day is not 00:00:00'),
190 end if;
191 end test;
192
193
194 end DATE_;
195 /
196 @be.sql DATE_
The DDL syntax used to create the package body in Listing 2-11 is as follows:
CREATE [OR REPLACE] PACKAGE BODY <package_name> AS
-- one or more constant, cursor, or variable declarations
-- one or more function, or procedure implementations
[BEGIN]
-- you can code a PL/SQL block called an initialization section that is
-- executed only once per session, when the package is first instantiated
-- into memory
[EXCEPTION]
-- you can code an exception-handling section for the initialization section
END <package_name>;
where <package_name> is the name of the package body you’re creating.
Did you notice the two optional sections? If you have some initialization code that you want to run the first time a package is loaded into memory, you can use the keyword BEGIN to start an initialization section. And if you want an exception-handling section for your initialization section, you can add it by using the keyword EXCEPTION. This is classic PL/SQL block structure.
Your implementations of functions and procedures are actually embedded functions and procedures in the declaration section of a PL/SQL block! Any constant, cursor, or variable that you declare in the declaration section of the package body is accessible to all the declared functions and procedures in that section, but not globally to other stored procedures. Only the items you declared in the package spec are accessible to other stored procedures.
As I noted at the beginning of this section, Listing 2-11 contains a lot of code that we haven’t yet discussed, so I’ll leave its explanation for later chapters.
Note In Listing 2-11, if I had declared any package body (or instance) functions or procedures—ones not accessible outside the package body—I would take time to document the purpose of the instance declaration in the package body. You should do the same. Once again, I declare that professional PL/SQL programmers document as they go.
It’s Your Turn to Create a Package Body
You should already have a package spec created for the NUMBER_ package, as in Listing 2-10. Now it’s time create the corresponding package body. It’s almost like the package spec, except for the keyword BODY in the DDL and the function to_number_or_null()’s implementation, as in Listing 2-2. Create your package body by following these steps.
Again, if you get any errors, work on your script until you figure out what’s wrong so you can compile the script successfully. Listing 2-12 shows my solution.
Listing 2-12. The NUMBER_ Package Body, number_.pkb
01 create or replace package body NUMBER_ as
02 /*
03 number_.pkb
04 by Donald J. Bales on 2014-10-20
05 A utility package for the data type NUMBER
06 */
07
08 FUNCTION to_number_or_null (
09 aiv_number in varchar2 )
10 return number is
11 begin
12 return to_number(aiv_number);
13 exception
14 when OTHERS then
15 return NULL;
16 end to_number_or_null;
17
18 end NUMBER_;
19 /
20 @be.sql
As I mentioned earlier, anything declared in a package spec can be seen by any username that has execute privileges on the package. Package specs also reduce dependency invalidation issues. What does that mean?
Say that procedure1 calls function1 and is called by procedure2. Then if you change function1, function1 becomes invalid, and so do procedure1 and procedure2. This means you need to recompile all three PL/SQL programs. This chain of dependency can be broken by using packages.
Now suppose that you use packages: package1.procedure1 calls package2.function1, and it is called by package3.procedure2. If you change the package implementation, or body, of package2.function1, it will not cause the invalidation of the function, nor any dependent PL/SQL blocks. You will cause dependent PL/SQL blocks to be invalidated only if you change the package spec.
Summary
Table 2-1 shows a side-by-side comparison of the syntax used for the various types of PL/SQL blocks covered in this chapter. The point here is that PL/SQL code always exists in a PL/SQL block, and that PL/SQL blocks are quite consistent in their structure. Even nested PL/SQL blocks are consistent with the ones shown in Table 2-1.
Table 2-1. A Comparison of PL/SQL Block Syntax
Now that you have a firm understanding of the block structure of PL/SQL, you’re ready to move on to the next step: learning about PL/SQL data types and variables.
3.133.124.21